Enumerate Items in Pandas based on Group and Item Group

Question

I have a pandas dataframe with data from a supermarket cashier system that lists every Customer ("ID") and each individual item ("Item") they purchased. I want to enumerate each item within a customer buy ("Item_e").

ID = [1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3]
Item = ["Apple", "Orange", "Apple", "Apple", "Orange", "Orange", "Banana", "Banana", "Apple", "Pumpkin", "Apple", "Banana"]

Item_e = ["Apple_1", "Orange_1", "Apple_2", "Apple_3", "Orange_1", "Orange_2", "Banana_1", "Banana_2", "Apple_1", "Pumpkin_1", "Apple_1", "Banana_1"]

"ID" and "Item" are the existing columns in the dataframe and I want to generate "Item_e" as a new column.

If customer 1 buys three apples I want the one scanned first to become Apple_1, the second (whenever in the whole purchase it appears) to become Apple_2 and so on. If customer 2 also buys two apples, the first in the dataset becomes Apple_1 again, the second Apple_2.

score 2 · Accepted Answer · answered May 27 '21 at 10:05

Use GroupBy.cumcount with Series.str.cat:

df = pd.DataFrame({"ID":ID,
                   "Item":Item})

s = df.groupby(['ID','Item']).cumcount().add(1).astype(str)
df['Item_e'] = df['Item'].str.cat(s, sep='_')
#alternative
#df['Item_e'] = df['Item'] + '_' + s
print (df)
    ID     Item     Item_e
0    1    Apple    Apple_1
1    1   Orange   Orange_1
2    1    Apple    Apple_2
3    1    Apple    Apple_3
4    2   Orange   Orange_1
5    2   Orange   Orange_2
6    2   Banana   Banana_1
7    2   Banana   Banana_2
8    2    Apple    Apple_1
9    3  Pumpkin  Pumpkin_1
10   3    Apple    Apple_1
11   3   Banana   Banana_1

Enumerate Items in Pandas based on Group and Item Group

1 Answers1