CODEWITHSUNDEEP
pythonpandas
user15368200
user15368200

Reputation: 97

Exploding a list of dictionaries in pandas to multiple rows and columns

I'm working with Shopify's Order API and I receive the following responses, which currently sit in a dataframe (very simplified).

+----------+-----------+-----------------------------------------------------------------------------+
| order_id | name      | item_order                                                                  |
+----------+-----------+-----------------------------------------------------------------------------+
| 123      |  Jon Doe  | [{'id':789,'price':40.0,'sku':'HAT'},{'id':790,'price':41.0,'sku':'SHIRT'}] |
+----------+-----------+-----------------------------------------------------------------------------+
| 122      | Jonah Doe |                    [{'id':553,'price':20.0,'sku':'CAP'}]                    |
+----------+-----------+-----------------------------------------------------------------------------+
|          |           |                                                                             |
+----------+-----------+-----------------------------------------------------------------------------+

I'd like to explode item_order, so we get something closer to the below:

+----------+-----------+---------------+------------------+----------------+
| order_id | name      | item_order.id | item_order.price | item_order.sku |
+----------+-----------+---------------+------------------+----------------+
| 123      |  Jon Doe  | 789           | 40.0             | HAT            |
+----------+-----------+---------------+------------------+----------------+
| 123      | Jon Doe   | 790           | 41.0             | SHIRT          |
+----------+-----------+---------------+------------------+----------------+
| 122      | Jonah Doe | 553           | 20               | CAP            |
+----------+-----------+---------------+------------------+----------------+

Upvotes: 2

Views: 846

Answers (2)

jezrael
jezrael

Reputation: 862591

Use DataFrame.explode with json_normalize:

df = df.explode('item_order').reset_index(drop=True)
df = df.join(pd.json_normalize(df.pop('item_order')))

Upvotes: 5

Nuri Taş
Nuri Taş

Reputation: 3845

First explode and then agg(pd.Series):

new_df = df.explode('item_order')
new_df[['item_order.id', 'item_order.price', 'item_order.sku']] = ( new_df['item_order'].
                                                              agg(pd.Series) )

Upvotes: 1

Related Questions