Transform json data into a dataframe-CodePudding

I tried to transform JSON data into a dataframe with the following code:

import json
l = []
for line in open('data.json', 'r'):
    l.append(json.loads(line))

df = pd.DataFrame(l)
df.parameters.head(1))

l looks like this :

{"user_id":0,"client":"35.181","parameters":[{"key":"from","value":"9.204444;45.486389"},{"key":"to","value":"200"},{"key":"with","value":"Train"},

And df looks like this :

user_id  client    ...   parameters
0        30112     ...   [{'key': 'from', 'value': '9.29''}, {'key': 'to', 'value': '200'}, {'key': 'with', 'value': 'Train'}]
1        30113     ...   [{'key': 'from', 'value': '9.20''}, {'key': 'to', 'value': '30'}, {'key': 'with', 'value': 'Car'}]

And I would like to be able to break the parameters column into 3 distinct columns which would be: from, to, with.

user_id  client  error  ...   from   to   with
0        30112    yes   ...   9.29   200  Train
1        30113    NaN   ...   9.20   30   Car

Could you help me, please?

CodePudding user response：

Use list comprehension with DataFrame constructor:

m = df['parameters'].notna()
df1 = pd.DataFrame([{y['key']: y['value'] for y in x} for x in df.pop('parameters').dropna()], 
                    index=df.index[m])

df = df.join(df1)