JSON data to columns in a DataFrame

Question

I have data in the form of a pandas dataframe, which looks like this:

A      B                               C
ab     {"app":"x","wid":10,"a42":5}    e
y      {"hair":"x","wid":10,"a42":2}   r

and I want to convert my dataframe like this:

A    C    app    wid    a42    hair
ab   e    x      10     5      -
y    r    -      10     2      r

These questions were not helpful:
JSON to pandas DataFrame
Convert Json data to Python DataFrame
How to convert a parsed json file to pandas data frame?

And I have also seen many other links but stuck with this problem.

Duplicate? stackoverflow.com/questions/38231591/…

Merlin
– Merlin

2016-07-07 12:23:45 +00:00
Commented Jul 7, 2016 at 12:23 — Merlin
– Merlin, Commented Jul 7, 2016 at 12:23

MaxU - stand with Ukraine · Accepted Answer · 2016-07-07 11:28:34Z

0

try this:

In [143]: df.B.apply(pd.Series)
Out[143]:
   a42  app hair  wid
0    5    x  NaN   10
1    2  NaN    x   10

answered Jul 7, 2016 at 11:28

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

Comments

jezrael · Accepted Answer · 2016-07-07 11:29:28Z

0

You can use DataFrame.from_records and concat:

import pandas as pd

df = pd.DataFrame({'A':['ab','y'],
                   'B':[{"app":"x","wid":10,"a42":5},{"hair":"x","wid":10,"a42":2}],
                   'C':['e','r']})
print (df)
    A                                   B  C
0  ab   {'a42': 5, 'wid': 10, 'app': 'x'}  e
1   y  {'a42': 2, 'wid': 10, 'hair': 'x'}  r

print (pd.DataFrame.from_records(df.B))
   a42  app hair  wid
0    5    x  NaN   10
1    2  NaN    x   10

print (pd.concat([df[['A','C']], pd.DataFrame.from_records(df.B)], axis=1))
    A  C  a42  app hair  wid
0  ab  e    5    x  NaN   10
1   y  r    2  NaN    x   10

answered Jul 7, 2016 at 11:29

jezrael

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

2 Comments

Abhishek Shankhadhar Over a year ago

I don't have data in this form : {'A':['ab','y'], 'B':[{"app":"x","wid":10,"a42":5},{"hair":"x","wid":10,"a42":2}], 'C':['e','r']}. I have it in the form of dataframe and when I am doing the process "pd.DataFrame.from_records(df.B)" result comes wrong.

jezrael Over a year ago

yes, I know. You have data in form df, right? I add creating df for better testing.

Merlin · Accepted Answer · 2016-07-07 12:18:04Z

0

Try this;

df["B"] = df["B"].apply(lambda x : dict(eval(x)) )
or 
df["B"] = df["B"].map(eval)

df2 = df["B"].apply(pd.Series )
result = pd.concat([df, df2], axis=1).drop('B', axis=1)

answered Jul 7, 2016 at 12:18

Merlin

25.9k44 gold badges141 silver badges213 bronze badges

1 Comment

Merlin Over a year ago

If this answer helped, please upvote and correct if it is.

Collectives™ on Stack Overflow

JSON data to columns in a DataFrame

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related