Conversion of double nested dictionary to dataframe

Question

Could anyone tell me how to get the data from the below nested dict into a table with following columns [unit, siteId, date, value,]

data = {'sitesEnergy': {'timeUnit': 'DAY',
  'unit': 'Wh',
  'count': 5,
  'siteEnergyList': [{'siteId': 2248407,
    'energyValues': {'measuredBy': 'METER',
     'values': 
      [
      {'date': '2022-08-01 00:00:00', 'value': 1084070.0},
      {'date': '2022-08-02 00:00:00', 'value': 1420093.0},
      {'date': '2022-08-03 00:00:00', 'value': 1757618.0},
      {'date': '2022-08-04 00:00:00', 'value': 1685625.0},
      {'date': '2022-08-05 00:00:00', 'value': 1043790.0},
      {'date': '2022-08-06 00:00:00', 'value': 1340688.0},
      {'date': '2022-08-07 00:00:00', 'value': 1555515.0},
      {'date': '2022-08-08 00:00:00', 'value': 1573906.0}]}},
   {'siteId': 1485192,
    'energyValues': {'measuredBy': 'METER',
     'values': 
      [
      {'date': '2022-08-01 00:00:00', 'value': 230484.0},
      {'date': '2022-08-02 00:00:00', 'value': 272969.0},
      {'date': '2022-08-03 00:00:00', 'value': 302500.0},
      {'date': '2022-08-04 00:00:00', 'value': 300594.0},
      {'date': '2022-08-05 00:00:00', 'value': 220641.0},
      {'date': '2022-08-06 00:00:00', 'value': 255484.0},
      {'date': '2022-08-07 00:00:00', 'value': 244516.0},
      {'date': '2022-08-08 00:00:00', 'value': 266532.0}]}}]}}

MoRe · Accepted Answer · 2022-08-15 22:09:02Z

2

if exactly this dict:

import pandas as pd
df = pd.DataFrame(data['sitesEnergy']['siteEnergyList'][0]['energyValues']['values'])
df['unit'] = data['sitesEnergy']['unit']
df['siteId'] = data['sitesEnergy']['siteEnergyList'][0]['siteId']

UPDATE:

import pandas as pd
df = pd.DataFrame([[d['siteId'], d['energyValues']['values']] for d in data['sitesEnergy']['siteEnergyList']]).explode(1)
df = pd.concat([df[[0]].rename(columns={0: "siteId"}).reset_index(drop=True), pd.DataFrame(df[1].tolist())], axis=1)
df['unit'] =  data['sitesEnergy']['unit']

edited Aug 15, 2022 at 22:09

answered Aug 15, 2022 at 20:57

MoRe

2,3942 gold badges5 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GKV Over a year ago

Got it this works. But if had more repeated data, could you tell me how to use this is in a loop to search within that data?

MoRe Over a year ago

@GKV please provide a more comprehensive data to check that which part is repeated? although it is better to ask it in another post...

GKV Over a year ago

I have added to the existing data. Please check it.

Collectives™ on Stack Overflow

Conversion of double nested dictionary to dataframe

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related