How do I read JSON using pandas?

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

Expected Output:

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"
        }
    }
]