How to add a sequential suffix to a pandas Series of strings for matching strings

Question

I want to create a new column that appends a sequential numeric suffix to matching values.

If I begin with the following...

d = {'item': ['A', 'A', 'B', 'C', 'C', 'A'],
     'year': [2019, 2019, 2019, 2019, 2020, 2020]}
df0 = pd.DataFrame(d)

    item    year
0   A       2019
1   A       2019
2   B       2019
3   C       2019
4   C       2020
5   A       2020

I want this intermediate step,

    item    item_number year
0   A       1           2019
1   A       2           2019
2   B       1           2019
3   C       1           2019
4   C       2           2020
5   A       3           2020

then this final form.

    numbered_item   item    year
0   A 1             A       2019
1   A 2             A       2019
2   B 1             B       2019
3   C 1             C       2019
4   C 2             C       2020
5   A 3             A       2020

Check https://stackoverflow.com/questions/23435270/how-to-add-sequential-counter-column-on-groups-using-pandas-groupby — Vaishali, May 15 '19 at 17:51

score 3 · Answer 1 · answered May 15 '19 at 17:48

Use df.insert() to insert the column in a specific pos and use df.groupby().cumcount():

df0.insert(0,'numbered_item',df0.item+df0.groupby('item').cumcount().add(1).astype(str))
print(df0)

  numbered_item item  year
0            A1    A  2019
1            A2    A  2019
2            B1    B  2019
3            C1    C  2019
4            C2    C  2020
5            A3    A  2020

score 0 · Answer 2 · answered May 15 '19 at 17:48

0

Try:

df0['item_number'] = df0.groupby('item').cumcount()+1

df0['numbered_item'] = df0['item'].astype(str) + ' ' + df0['item_number'].astype(str)

Output:

item    year    item_number numbered_item
0   A   2019    1           A 1
1   A   2019    2           A 2
2   B   2019    1           B 1
3   C   2019    1           C 1
4   C   2020    2           C 2
5   A   2020    3           A 3

answered May 15 '19 at 17:48

Quang Hoang

146,074
10
56
74

2

would like to see the explanation for downvote please. – anky May 15 '19 at 17:52

How to add a sequential suffix to a pandas Series of strings for matching strings

2 Answers2