CODEWITHSUNDEEP
pythonjsonpandasdataframe
Abhishek Shankhadhar
Abhishek Shankhadhar

Reputation: 31

JSON data to columns in a DataFrame

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.

Upvotes: 0

Views: 1461

Answers (3)

Merlin
Merlin

Reputation: 25629

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)

Upvotes: 0

jezrael
jezrael

Reputation: 862521

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

Upvotes: 0

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210832

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

Upvotes: 0

Related Questions