expand json in a pandas columns to the whole dataframe-CodePudding

I have a pandas dataframe that has a column that contains json values. like below:

  json_col 
0     {"name":"John","role":"manager", "age":40}     
1     {"name":"Mary","role":"director", "age":50}

How can I expand the key into the columns of the pandas dataframe with the corresponding values?

Desired result:

  name       role      age 
0 John      manager    40
1 Mary      director   50

CodePudding user response：

Use from_records

import pandas as pd

df = pd.DataFrame(data=[[{"name": "John", "role": "manager", "age": 40}],
                        [{"name": "Mary", "role": "director", "age": 50}]], columns=["json_col"])

res = pd.DataFrame.from_records(df["json_col"])
print(res)

Output

   name      role  age
0  John   manager   40
1  Mary  director   50

CodePudding user response：

Use json.loads:

import json

out = df['json_col'].apply(json.loads).apply(pd.Series)
print(out)

# Output:
   name      role  age
0  John   manager   40
1  Mary  director   50

CodePudding user response：

You can use pd.DataFrame to expand the column into a dataframe, as follows:

df_out = pd.DataFrame(df['json_col'].tolist())

Result:

print(df_out)


   name      role  age
0  John   manager   40
1  Mary  director   50