0

I have a data frame with one string column and I'd like to split it into multiple columns by seperate with ','. I want to name the column as same as the string in the column before ':'.

The column looks like this:

0  {"ID":"AP001","Name":"Anderson","Age":"23"}
1  {"ID":"AP002","Name":"Jasmine","Age":"36"} 
2  {"ID":"AP003","Name":"Zack","Age":"28"}
3  {"ID":"AP004","Name":"Chole","Age":"39"}

And I want to split to this:

ID Name Age
AP001 Anderson 23
AP002 Jasmine 36
AP003 Zack 28
AP004 Chole 39

I have tried to split it by ',', but im not sure how to remove the string before ':' and put it as the column name.

data1 = data['demographic'].str.split(',',expand=True)

This is what I get after splitting it:

0 1 2
"ID":"AP001" "Name":"Anderson" "Age":"23"
"ID":"AP002" "Name":"Jasmine" "Age":"36"
"ID":"AP003" "Name":"Zack" "Age":"28"
"ID":"AP004" "Name":"Chole" "Age":"39"

Anyone knows how to do it?

sssyyy
  • 13
  • 2

1 Answers1

0

You can use ast.literal_eval:

import ast

data1 = pd.json_normalize(data['demographic'].apply(ast.literal_eval))
print(data1)

# Output
      ID      Name Age
0  AP001  Anderson  23
1  AP002   Jasmine  36
2  AP003      Zack  28
3  AP004     Chole  39
Corralien
  • 109,409
  • 8
  • 28
  • 52