I am trying to find the occurrence and add the occurrence if it is repeating. I tried groupby.cumcount but it doesn't increment when a new value is there. I need the output as mentioned below if the description is confusing.
| col1 | col2 | |
|---|---|---|
| 0 | 1200 | 1 |
| 1 | 1300 | 2 |
| 2 | 1400 | 3 |
| 3 | 1400 | 3 |
| 4 | 1400 | 3 |
| 5 | 1300 | 2 |
| 6 | 1500 | 4 |
| 7 | 1500 | 4 |
You are looking for Groupby.ngroup. It numbers each group from 0 to groups-1. You can add 1 to the resulting Series, so that the numbering starts at 1
df['col2'] = df.groupby('col1').ngroup().add(1)
Output:
>>> df
col1
0 1200
1 1300
2 1400
3 1400
4 1400
5 1300
6 1500
7 1500
>>> df.groupby('col1').ngroup()
0 0
1 1
2 2
3 2
4 2
5 1
6 3
7 3
dtype: int64
>>> df.groupby('col1').ngroup().add(1)
0 1
1 2
2 3
3 3
4 3
5 2
6 4
7 4
dtype: int64