How to sort and count the instances of a column value in pandas?

Question

I have this dataframe:

df = pd.DataFrame(data=[[1,1],[1,2],[2,3],[2,4],[2,5],[2,6],[3,7],[3,8]],columns=['alarmId','attemptId'])

Which creates this table :

+---------+-----------+
| alarmId | attemptId |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       2 |         3 |
|       2 |         4 |
|       2 |         5 |
|       2 |         6 |
|       3 |         7 |
|       3 |         8 |
+---------+-----------+

I want to label the instance number of each attempts per alarm, such that it resulted in a table like this:

+---------+-----------+---------------+
| alarmId | attemptId | attemptNumber |
+---------+-----------+---------------+
|       1 |         1 |             1 |
|       1 |         2 |             2 |
|       2 |         3 |             1 |
|       2 |         4 |             2 |
|       2 |         5 |             3 |
|       2 |         6 |             4 |
|       3 |         7 |             1 |
|       3 |         8 |             2 |
+---------+-----------+---------------+

How can I do this?

`df['attemprNumber'] = df.groupby('alarmId').cumcount().add(1)`? — Shubham Sharma, Jun 16 '20 at 06:43

score 2 · Accepted Answer · answered Jun 16 '20 at 06:44

2

df["attemptNumber"] = 1 + df.groupby("alarmId").cumcount()
print(df)

   alarmId  attemptId  attemptNumber
0        1          1              1
1        1          2              2
2        2          3              1
3        2          4              2
4        2          5              3
5        2          6              4
6        3          7              1
7        3          8              2

answered Jun 16 '20 at 06:44

AMH

502
2
10

1

Please refrain from answering clear duplicates. – Ch3steR Jun 16 '20 at 06:46

How to sort and count the instances of a column value in pandas?

1 Answers1