Is there a way to extract json string to multiple rows as shown below
Example,
This is the dataframe I have with json string
Col1 Col2
1 [{'name': 'TypeScript1', 'size': 10}, {'name': 'TypeScript2', 'size': 20}]
2 [{'name': 'TypeScript3', 'size': 30}, {'name': 'TypeScript4', 'size': 40}]
Is there a way to convert to below with reference key(Col1)
Col1 name size
1 TypeScript1 10
1 TypeScript2 20
2 TypeScript3 30
2 TypeScript4 40
We need to transform your dataFrame (the one with nested data) to a flat structure. Try the following.
df = pd.DataFrame({'Col1': [1, 2],
'Col2': [[{'name': 'TypeScript1', 'size': 10}, {'name': 'TypeScript2', 'size': 20}],
[{'name': 'TypeScript3', 'size': 30}, {'name': 'TypeScript4', 'size': 40}]]})
df = df.explode('Col2').reset_index(drop=True)
df[['name', 'size']] = pd.DataFrame(df['Col2'].tolist())
df.drop(columns=['Col2'], inplace=True)
print(df)
