Reputation: 47
I imported a json file to pandas, similar to
data = [
{
'order_id': 1 ,
'line_item': [{'id': 11, 'price':34.1},{'id': 22, 'price':53.1}]
},
{
'order_id': 2,
'line_item': [{'id': 11, 'price':34.1},{'id': 33, 'price':64.1}]
},
]
I want to split the line_item detail to new rows, my target dataframe would be
order_id item_id price
1 11 34.1
1 22 53.1
2 11 34.1
2 33 64.1
Thanks for any suggestions.
Upvotes: 0
Views: 694
Reputation: 13537
pandas.json_normalize
is the perfect thing for the job:
df = pd.json_normalize(data, "line_item", meta="order_id")
print(df)
id price order_id
0 11 34.1 1
1 22 53.1 1
2 11 34.1 2
3 33 64.1 2
Upvotes: 1
Reputation: 323396
Try with explode
then 'explode
' the row
s = df.explode('line_item')
out = pd.DataFrame(s.line_item.tolist()).assign(order_id = s.order_id.values)
out
id price order_id
0 11 34.1 1
1 22 53.1 1
2 11 34.1 2
3 33 64.1 2
Upvotes: 3