I am using python to parse a CSV which contains JSON type:
| cast | id |
|---|---|
| {"user":"a","character":"AA"},{"user":"b","character":"BB"} | 1 |
| {"user":"c","character":"CC"} | 2 |
How can I make this CSV be:
| cast | id |
|---|---|
| ["AA","BB"] | 1 |
| ["CC"] | 2 |
Add a comment
|
2 Answers
import pandas as pd
import json
df = pd.read_csv('your_file.csv')
df.cast = df.cast.apply(lambda r: [x.get('character') for x in json.loads('[' + r + ']')])
The apply method is used to apply a function on a column. You can use the json module to parse the JSON string. json.loads() returns a dictionary, I had to add the brackets to make the string a valid JSON string (list of mappings).
1 Comment
Yihao Mao
Could you tell me what does
json.loads('['+r+']') mean? why do not put df['cast'] inside?use regexp to extract the character content,
if you only want to use that field.
df['cast_list'] = df['cast'].str.findall('"character":"(.*?)"')
