rotate pandas DataFrame with rows of json to plain DataFrame

Question

I have a pandas dataframe that looks like this

df_in = pd.DataFrame(data = {'another_col': ['a', 'x', '4'], 'json': [
    [{"Key":"firstkey", "Value": 1.4}, {"Key": "secondkey", "Value": 6}],
    [{"Key":"firstkey", "Value": 5.4}, {"Key": "secondkey", "Value": 11}],
    [{"Key":"firstkey", "Value": 1.6}, {"Key": "secondkey", "Value": 9}]]}
)

which when printed looks like

  another_col                                               json
0           a  [{'Key': 'firstkey', 'Value': 1.4}, {'Key': 's...
1           x  [{'Key': 'firstkey', 'Value': 5.4}, {'Key': 's...
2           4  [{'Key': 'firstkey', 'Value': 1.6}, {'Key': 's...

I need to transform it and parse each row of json into columns. I want the resulting dataframe to look like

  another_col  firstkey  secondkey
0           a       1.4          6
1           x       5.4         11
2           4       1.6          9

How do I do this? I have been trying with pd.json_normalize with no success. A secondary concern is speed... I have to apply this on ~5mm rows...but first let's get it working. :-)

anky · Accepted Answer · 2020-07-23 16:37:00Z

3

You can convert to dataframe and unstack , then join:

u = df_in['json'].explode()
out = df_in[['another_col']].join(pd.DataFrame(u.tolist(),index=u.index)
                        .set_index('Key',append=True)['Value'].unstack())

print(out)

  another_col  firstkey  secondkey
0           a       1.4        6.0
1           x       5.4       11.0
2           4       1.6        9.0

answered Jul 23, 2020 at 16:37

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

2 Comments

Jonathan Over a year ago

Wow! Great. Thank you!

Shubham Sharma Over a year ago

Nice answer @anky. Liked it.

Collectives™ on Stack Overflow

rotate pandas DataFrame with rows of json to plain DataFrame

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related