Transform json data into a dataframe

Question

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?

This question lacks clarity. Show l, show the df you get, show the df you want. If l is large reduce its size as much as possible. — timgeb
– timgeb, Commented Feb 28, 2022 at 11:03
Thanks for your comment, I made the changes to make it clearer — Alex
– Alex, Commented Feb 28, 2022 at 11:30

jezrael · Accepted Answer · 2022-02-28 13:00:15Z

3

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)

edited Feb 28, 2022 at 13:00

answered Feb 28, 2022 at 11:33

jezrael

868k102 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

Alex Over a year ago

Thanks for your answer, but it gives the error "'float' object is not iterable", while the type of the parameters column is not float but object

jezrael Over a year ago

@Alex - seems some missing values, can you add .dropna() ?

Hansen Marcelino Azali · Accepted Answer · 2022-03-01 02:55:03Z

2

    import json
    import pandas as pd
    with open('refactory.json') as f:
    data = json.load(f)
    print(data)
    df = pd.read_json('refactory.json')
    df

answered Mar 1, 2022 at 2:55

Hansen Marcelino Azali

2091 silver badge7 bronze badges

Collectives™ on Stack Overflow

Transform json data into a dataframe

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related