Encoding json response to csv in python

Solution 1:

Here's an example of something that processes the values column.

data = {'energy': {'timeUnit': 'DAY', 'unit': 'Wh', 'measuredBy': 'INVERTER', 'values': [{'date': '2022-01-01 00:00:00', 'value': 322.0}, {'date': '2022-01-02 00:00:00', 'value': 12.0}, {'date': '2022-01-03 00:00:00', 'value': 0.0}]}}
import pandas as pd
energy = pd.DataFrame(data['energy'])
pd.concat((energy.drop('values', axis=1), energy['values'].apply(pd.Series)), axis=1)

I am dropping the first level by making the DataFrame from the 'energy' key within the dictionary. This produces a frame with timeUnit, unit and measuredBy value repeated for each date, value dictionary.

Next, by applying pd.Series we can create a new table with two columns date and value. Finally, drop the old values column and replace it by date and value columns. This is all done by pd.concat, drop and apply(pd.Series)

It should look like below:

    timeUnit    unit    measuredBy  date                    value
0   DAY         Wh      INVERTER    2022-01-01 00:00:00     322.0
1   DAY         Wh      INVERTER    2022-01-02 00:00:00     12.0
2   DAY         Wh      INVERTER    2022-01-03 00:00:00     0.0