Pandas - split column stored as csv

Question

I have a dataset where each row represents a unique sale. One column represents the items ordered in that sale, as CSV.

I need to separate the values in the "items ordered" column and for each item ordered, create a unique row, tied to the original order ID.

For example:

Order ID   Items Ordered
   127    Item 1, Item 2, Item 3

Should be:

Order ID   Items Ordered
   127         Item 1
   127         Item 2
   127         Item 3

I am relatively new to python/pandas. This code works, but is slow as I'm iterating over all the orders. What's a better way to do this?

temp_item_list = []

for row in raw_data.iterrows():
    for i in range(len(row['Items'])):
        temp_item_list.append((row['ID'], row['Items'][i]))

item_df = pd.DataFrame(temp_item_list)

score 4 · Answer 1 · answered Feb 23 '17 at 03:25

To avoid iterating through all rows, you can use numpy.repeat to make columns Order ID while flattening the Items Ordered and make new data frame from this:

from itertools import chain
import numpy as np
import pandas as pd

# count the number of items in the items order column
items_count = df["Items Ordered"].str.count(",") + 1

# extend the Order ID column with numpy repeat and flatten Items Order column
pd.DataFrame({"Order ID": np.repeat(df["Order ID"], items_count),
              "Items Ordered": list(chain.from_iterable(df["Items Ordered"].str.split(",")))})

score 3 · Accepted Answer · edited May 23 '17 at 12:25

With reference to similar answer you may try this step-by-step guide:

>>> initial_df = pandas.read_csv("your_file_path")
>>> final_df = pandas.concat([Series(row['Order ID'], row['Items Ordered'].split(',')) for _, row in initial_df.iterrows()]).reset_index()
>>> final_df
     index    0
0   Item 1  127
1   Item 2  127
2   Item 3  127
>>> final_df.columns= ['Items Ordered','Order ID']
>>> final_df
  Items Ordered  Order ID
0        Item 1       127
1        Item 2       127
2        Item 3       127
>>> final_df[['Order ID','Items Ordered']]
   Order ID Items Ordered
0       127        Item 1
1       127        Item 2
2       127        Item 3

This will get your job done.

score 0 · Answer 3 · edited May 23 '17 at 12:32

0

Something that might help you is pandas.DataFrame.apply. It allows you to apply a function to every row. You can define a function that grabs the items element in the row, splits it up, and calls the pandas.DataFrame.append to create a new row for each item.

Here is an example on how to use the apply function too.

edited May 23 '17 at 12:32

Community

1
1

answered Feb 23 '17 at 03:17

Chirag

446
2
14

Siraj S. · Answer 4 · 2017-02-25T20:05:54.627

may this is what you are looking for;

df = pd.DataFrame({'order_id':127, 'items_ordered': ['item_1, item_2, item_3']})
df1 = pd.concat([df.order_id,df.items_ordered.str.split(',').apply(pd.Series)],axis=1,ignore_index=True)
df1 = df1.pivot(index = df1.index, columns = df1.columns[0]).stack().stack().to_frame()
df1 = df1.set_index(df1.index.get_level_values(level=1))

or alternatively a more succinct solution combining pivot and set_index steps above is:

df1 = pd.melt(df, id_vars = ['order_id'])

Pandas - split column stored as csv

4 Answers4