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

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

CodePudding user response：

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