Python nested Json to Dataframe (Injixo WFM API)-CodePudding

I'm trying to model a DataFrame from a JSON file, which has a nested list for the values.

Here is the Json i try to get into a DF:

{'curve_values':
 [{'curve_event_type_id': 4,
 'curve_id': 1031, 
'curve_value_type_id': 1005,
 'curve_version_id': 1001, 
'date': '2022-09-01',
 'raster': 1800,
'values': [6.84, 5.09, 4.85, 3.37, 1.98, 1.72, 1.16, 1.62, 1.38, 1.36, 2.68, 3.04, 7.84, 16.07, 30.41, 57.43, 242.71, 378.06, 561.35, 535.3, 543.77, 537.5, 531.69, 499.52, 301.1, 259.38, 314.74, 335.14, 399.23, 375.37, 354.52, 314.45, 241.0, 165.76, 116.24, 90.09, 58.65, 53.2, 46.36, 42.67, 35.97, 25.15, 22.8, 14.76, 14.18, 12.36, 6.39, 0.81]},
{'curve_event_type_id': 5, 
'curve_id': 1031, 
'curve_value_type_id': 1005, 
'curve_version_id': 1001,
 'date': '2022-09-02', 
'raster': 1800,
 'values': [5.37, 4.8, 2.49, 2.06, 2.06, 1.81, 0.99, 1.0, 0.94, 1.85, 3.23, 3.93, 10.0, 16.25, 25.83, 59.06, 235.31, 356.17, 509.17, 496.38, 488.85, 486.59, 469.04, 435.57, 272.8, 236.37, 244.05, 238.08, 286.04, 257.1, 212.75, 176.89, 124.53, 99.72, 75.53, 62.71, 52.27, 40.09, 37.7, 30.44, 29.14, 23.92, 18.14, 14.94, 11.17, 9.52, 8.87, 0.18]}}]}

Here some code and my output so far:

if response.ok:
    dictr = response.json()
    recs = dictr['curve_values']
    df = pd.DataFrame(dictr['curve_values'])

I would like to make a entry for each value in the Column "Values" filled with the values from DF (like date, curve_event_type_id etc.

I just found a way with iterate over the DF and create a new one line by line but this is not very performant and takes ages too finish. I wonder what would be the correct way to do it.

Thank for your help!

I also tried a loop mention above the loop was like:

for val in recs[0]['values']:
    print(val)

instead of printing i was create a pd.Series and add it to the new DF with pd.Concat (this was obviously very slow)

Can't find a solution online, tried it a week.

all solutions i found online was just to get the "values" list to the DF but missing the infos from my screenshot.

 df = pd.json_normalize(response.json(), record_path=['curve_values', 'values'])

Also tested this approach (was suggested as i wrote the question)

    dictr = response.json()
    recs = dictr['curve_values']
    df = pd.DataFrame(dictr['curve_values'])
    df2 = df['values'].apply(lambda x: pd.DataFrame(x))

CodePudding user response：

can you try:

if response.ok:
    dictr = response.json()
    recs = dictr['curve_values']
    df = pd.DataFrame(dictr['curve_values'])
    df=df.explode('values')