How to convert JSON string in data frame column into multiple columns

Question

I have a data frame with a JSON string in a column:

ID	Data
11	{'Name': 'Sam', 'Age': 21}
22	{'Name': 'Nam', 'Age': 22}
33	{'Name': 'Pam', 'Age': 21, 'Salary': 10000}

How can I convert the above JSON string in to columns?

Desired result:

ID	Name	Age	Salary
11	Sam	21
22	Nam	22
33	Pam	21	10000

so it must be present in a string format, right? not in json format — Himanshu Poddar
– Himanshu Poddar, Commented Jul 12, 2022 at 4:55

Himanshu Poddar · Accepted Answer · 2022-07-12 05:38:34Z

6

You can use pandas.Series to read your column of dictionaries values into columns.

Creating the data

data = {
    'Id' : [11, 22, 33],
    'Data': ["{'Name': 'Sam', 'Age': 21}", "{'Name': 'Nam', 'Age': 22}", "{'Name': 'Pam', 'Age': 21, 'Salary': 10000}"],
    }
df = pd.DataFrame(data)

Converting dictionary to column

df['Data'] = df['Data'].map(lambda x: eval(x) if pd.notnull(x) else x)
df = pd.concat([df, df.pop("Data").apply(pd.Series)], axis=1)

Output :

   Id Name  Age   Salary
0  11  Sam   21      NaN
1  22  Nam   22      NaN
2  33  Pam   21  10000.0

Alternate solution

You can also use json_normalize to unravel the dictionary column to column from dictionary keys.

df['Data'] = df['Data'].map(lambda x: eval(x) if pd.notnull(x) else x)
df = pd.concat([df, pd.json_normalize(df.pop("Data"))], axis=1)

which gives you same output

edited Jul 12, 2022 at 5:38

answered Jul 12, 2022 at 5:15

Himanshu Poddar

7,90116 gold badges60 silver badges120 bronze badges

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

2 Comments

DEs Over a year ago

Is there a way we can get the column list out of it which are added by the json string? col = {'Name','Age','Salary'}

Himanshu Poddar Over a year ago

yes, we can use df.pop("Data").apply(pd.Series).columns this gives you ['Name', 'Age', 'Salary'] there are various other ways of doing this

Collectives™ on Stack Overflow

How to convert JSON string in data frame column into multiple columns

1 Answer 1

Creating the data

Converting dictionary to column

Output :

Alternate solution

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Creating the data

Converting dictionary to column

Output :

Alternate solution

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related