Convert json nested dictionary to pandas dataframe

Question

My API generates a JSON file as output with the following structure:

[
{
    "Data": {
        "SensorId": "EF00BFC0",
        "SensorName": "DEVICE 05",
        "TimeStamp": 3877552823,
    },
    "ThresholdValue": {
        "SensorSubId": 1,
        "ThresholdMax": -1,
        "ThresholdMin": -1
    },
    "startUTC": 1668564000,
    "endUTC": 1668663000
},
{
    "Data": {
        "SensorId": "EF00BFC0",
        "SensorName": "DEVICE 05",
        "TimeStamp": 3877553446,
    },
    "ThresholdValue": {
        "SensorSubId": 1,
        "ThresholdMax": -1,
        "ThresholdMin": -1
    },
    "startUTC": 1668564000,
    "endUTC": 1668663000
}]

I want to generate a Pandas DataFrame with SensorID, SensorName, Timestamp, startUTC, and endUTC as columns. I tried it using the below code.

df_nested_list = pd.json_normalize(
    data, 
    record_path =['Data'], 
    meta=['startUTC', 'endUTC']
)

But it gives the column names as rows and generate the dataframe. How can I do this in pandas?

df=pd.json_normalize(data) ?

Bushmaster
– Bushmaster

2022-12-06 12:53:22 +00:00
Commented Dec 6, 2022 at 12:53 — Bushmaster
– Bushmaster, Commented Dec 6, 2022 at 12:53

Marty_C137 · Accepted Answer · 2022-12-06 17:19:17Z

You can use pd.json_normalize() to get your desired output:

import pandas as pd

data = [
{
    "Data": {
        "SensorId": "EF00BFC0",
        "SensorName": "DEVICE 05",
        "TimeStamp": 3877552823,
    },
    "ThresholdValue": {
        "SensorSubId": 1,
        "ThresholdMax": -1,
        "ThresholdMin": -1
    },
    "startUTC": 1668564000,
    "endUTC": 1668663000
},
{
    "Data": {
        "SensorId": "EF00BFC0",
        "SensorName": "DEVICE 05",
        "TimeStamp": 3877553446,
    },
    "ThresholdValue": {
        "SensorSubId": 1,
        "ThresholdMax": -1,
        "ThresholdMin": -1
    },
    "startUTC": 1668564000,
    "endUTC": 1668663000
}]

df = pd.json_normalize(data)
df.columns = [c.replace('Data.', '') for c in df.columns]
cols_to_keep = [c for c in df.columns if 'ThresholdValue' not in c]
df = df[cols_to_keep]
df.head()

Output:

startUTC	endUTC	SensorId	SensorName	TimeStamp
1668564000	1668663000	EF00BFC0	DEVICE 05	3877552823
1668564000	1668663000	EF00BFC0	DEVICE 05	3877553446

Collectives™ on Stack Overflow

Convert json nested dictionary to pandas dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related