Reputation: 6574
I have a column consists of JSON structured data. My df
looks like this:
ClientToken Data
7a9ee887-8a09-ff9592e08245 [{"summaryId":"4814223456","duration":952,"startTime":1587442919}]
bac49563-2cf0-cb08e69daa48 [{"summaryId":"4814239586","duration":132,"startTime":1587443876}]
I want to expand it to:
ClientToken summaryId duration startTime
7a9ee887-8a09-ff9592e08245 4814223456 952 1587442919
bac49563-2cf0-cb08e69daa48 4814239586 132 1587443876`
Any ideas?
Upvotes: 2
Views: 2394
Reputation: 5500
You can try:
df[["ClientToken"]].join(df.Data.apply(lambda x: pd.Series(json.loads(x[1:-1]))))
Explanations:
Data
column and apply the following steps:
Data
" content is wrapped in a list and this is a string, we can remove []
manually using x[1:-1]
(remove first and last character)."Data"
column is a string
and we actually want a JSON
, we need to convert it. One solution is to use the json.loads()
function from the json
module. The code becomes json.loads(x[1:-1])
dict
to a pd.Series
using pd.Series(json.loads(x[1:-1]))
join
. Also, you will notice I used double []
to select the "ClientToken"
column as a dataframe.Code + illustration:
import pandas as pd
import json
# step 1.1
print(df.Data.apply(lambda x: x[1:-1]))
# 0 {"summaryId":"4814223456","duration":952,"star...
# 1 {"summaryId":"4814239586","duration":132,"star...
# Name: Data, dtype: object
# step 1.2
print(df.Data.apply(lambda x: json.loads(x[1:-1])))
# 0 {'summaryId': '4814223456', 'duration': 952, '...
# 1 {'summaryId': '4814239586', 'duration': 132, '...
# Name: Data, dtype: object
# step 1.3
print(df.Data.apply(lambda x: pd.Series(json.loads(x[1:-1]))))
# summaryId duration startTime
# 0 4814223456 952 1587442919
# 1 4814239586 132 1587443876
# step 2
print(df[["ClientToken"]].join(df.Data.apply(lambda x: pd.Series(json.loads(x[1:-1])))))
# ClientToken summaryId duration startTime
# 0 7a9ee887-8a09-ff9592e08245 4814223456 952 1587442919
# 1 bac49563-2cf0-cb08e69daa48 4814239586 132 1587443876
Edit 1:
As it seems that there are some rows where the list
in Data
has multiple dicts
, you can try:
df[["ClientToken"]].join(df.Data.apply(lambda x: [pd.Series(y)
for y in json.loads(x)]) \
.explode() \
.apply(pd.Series))
Upvotes: 2
Reputation: 28729
An alternative, using defaultdict and ast literal eval:
from collections import defaultdict
import ast
d = defaultdict(list)
#iterate through the Data column and append to dictionary for each key
for ent in df.Data:
for entry in ast.literal_eval(ent):
for k, v in entry.items():
d[k].append(v)
#concat to ClientToken column
pd.concat([df.ClientToken,pd.DataFrame(d)],axis=1)
ClientToken summaryId duration startTime
0 7a9ee887-8a09-ff9592e08245 4814223456 952 1587442919
1 bac49563-2cf0-cb08e69daa48 4814239586 132 1587443876
Upvotes: 0