CODEWITHSUNDEEP
pythonjsonpandasdataframe
abc196998
abc196998

Reputation: 47

Expand a json column of item details into new rows with Python pandas

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

Answers (2)

Cameron Riddell
Cameron Riddell

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

BENY
BENY

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

Related Questions