Nested dictionary to pandas DataFrame --CodePudding

Need help on the below nested dictionary and I want to convert this to a pandas Data Frame

Structure type :

DS = [{ 'Outer_key1.0' : [{ 'key1.0': 'data' , 'key2.0': 'data' , 'key3.0': 'data } ,
                          { 'key1.1': 'data' , 'key2.1': 'data' , 'key3.1': 'data } ,
                      { 'key1.2': 'data' , 'key2.2': 'data' , 'key3.3': 'data } ,]
         'Outer key2.0': 'data' , 
     'Outer Key3.0': 'data' }]

     [{ 'Outer_key1.1' : [{ 'key1.0': 'data' , 'key2.0': 'data' , 'key3.0': 'data } ,
                  { 'key1.1': 'data' , 'key2.1': 'data' , 'key3.1': 'data } ,
                      { 'key1.2': 'data' , 'key2.2': 'data' , 'key3.3': 'data } ,]
          'Outer key2.1': 'data' , 
      'Outer Key3.1': 'data' }]

Actual data model as mentioned below

[{'datapoints': [{'statistic': 'Minimum', 'timestamp': '2021-08-31 06:50:00.000000', 'value': 59.03},{'statistic': 'Minimum', 'timestamp': '2021-08-18 02:50:00.000000', 'value': 59.37}, {'statistic': 'Minimum', 'timestamp': '2021-08-24 16:50:00.000000', 'value': 58.84},...],'metric': 'VolumeIdleTime', 'unit': 'Seconds'}]


cc= pd.Series(DS).apply(lambda x  : pd.Series({ k: v for y in x for k, v in y.items() }))

CodePudding user response：

IIUC what you need is json_normalize. Set datapointsas record_path and metric and unit as meta:

data = [{'datapoints': [{'statistic': 'Minimum', 'timestamp': '2021-08-31 06:50:00.000000', 'value': 59.03},{'statistic': 'Minimum', 'timestamp': '2021-08-18 02:50:00.000000', 'value': 59.37}, {'statistic': 'Minimum', 'timestamp': '2021-08-24 16:50:00.000000', 'value': 58.84}],'metric': 'VolumeIdleTime', 'unit': 'Seconds'}]
df = pd.json_normalize(data, record_path="datapoints", meta=["metric", "unit"])
print(df)

Output:

  statistic                   timestamp  value          metric     unit
0   Minimum  2021-08-31 06:50:00.000000  59.03  VolumeIdleTime  Seconds
1   Minimum  2021-08-18 02:50:00.000000  59.37  VolumeIdleTime  Seconds
2   Minimum  2021-08-24 16:50:00.000000  58.84  VolumeIdleTime  Seconds