[FIXED] How do I read JSON using pandas?

Issue

How do I read the json below in pandas:

[
    {
        "DataVal": {
            "sales": "0.00000",
            "cost": "926.00000",
            "os": "And",
            "hr": "0",
            "_id": "34358",
            "month": "10",
            "date": "2021-10-11"
        }
    },
    {
        "DataVal": {
            "sales": "0.00000",
            "cost": "830.00000",
            "os": "And",
            "hr": "0",
            "_id": "24158",
            "month": "10",
            "date": "2021-01-02"
        }
    },
    {
        "DataVal": {
            "sales": "0.00000",
            "cost": "128.00000",
            "os": "And",
            "hr": "0",
            "_id": "23358",
            "month": "10",
            "date": "2021-10-03"
        }
    }

Current Output

DataVal
{'sales': '0.00000', 'cost': '96.00000', 'os': 'And', 'hr': '0', '_id': '24358', 'month': '10', 'date': '2021-10-01'}
{'sales': '0.00000', 'cost': '96.00000', 'os': 'And', 'hr': '0', '_id': '24358', 'month': '10', 'date': '2021-10-01'}

Expected Output:

Sales	Cost	os
0.000	234	And

Above table with other additional columns like os,hr, _id, month

Solution

Read JSON file into data using json

import json 

with open('path/to/json.json') as f:
    data = json.load(f)

Or if it's from an API call (using requests)

data = response.json()

Then you just need

df = pd.DataFrame(rec['DataVal'] for rec in data)

Output:

>>> df 

     sales       cost   os hr    _id month        date
0  0.00000  926.00000  And  0  34358    10  2021-10-11
1  0.00000  830.00000  And  0  24158    10  2021-01-02
2  0.00000  128.00000  And  0  23358    10  2021-10-03

Setup:

data = [
    {
        "DataVal": {
            "sales": "0.00000",
            "cost": "926.00000",
            "os": "And",
            "hr": "0",
            "_id": "34358",
            "month": "10",
            "date": "2021-10-11"
        }
    },
    {
        "DataVal": {
            "sales": "0.00000",
            "cost": "830.00000",
            "os": "And",
            "hr": "0",
            "_id": "24158",
            "month": "10",
            "date": "2021-01-02"
        }
    },
    {
        "DataVal": {
            "sales": "0.00000",
            "cost": "128.00000",
            "os": "And",
            "hr": "0",
            "_id": "23358",
            "month": "10",
            "date": "2021-10-03"
        }
    }
]

Answered By - Rodalm

Answer Checked By - Katrina (PHPFixing Volunteer)