[Python] How to flatten a nested json strings dataframe columns

Here is my example data with two fields where the last one [outbreak] is a pandas series.

Start:



Goal (Excel mock-up):



Reproduction Code:

import pandas as pd
import json

d = {'report_id': [100, 101], 'outbreak': [
'{"outbreak_100":{"name":"Chris","disease":"A-Pox"},"outbreak_101":{"name":"Stacy","disease": "H-Pox"}}',
'{"outbreak_200":{"name":"Brandon","disease":"C-Pox"},"outbreak_201":{"name":"Karen","disease": "G-Pox"},"outbreak_202":{"name":"Tim","disease": "Z-Pox"}}']}

df = pd.DataFrame(data=d)
print(type(df['outbreak']))
display(df)

#Ignore
df = pd.json_normalize(df['outbreak'].apply(json.loads), max_level=0)
display(df)


Attempts: I thought about using json_normalize() which would convert every [outbreak_id] to its own field and then use pandas.wide_to_long() to get my final output. It works in testing but my concern is that my actual production data is so long and nested that it ends up generating hundred of thousands of fields before pivoting. That does not sounds good to me and why I also hope to avoid loop iterations.

I also thought about using df = df.explode('outbreak') but I am getting a KeyError: 0.

Perhaps someone has a better idea than I do?

Continue reading...