CODEWITHSUNDEEP
pythonpandaspandas-groupby
Brandon
Brandon

Reputation: 11

Creating a cummulative count based on value of other columns

I have data set very close to the one below:

Letter   Year  ID 
Z        2019  872 
A        2020  182  
A        2020  182 
B        2019  112  
C        2020  112 
A        2019  182  
C        2019  112

I would like to create a column where within the id, grade year, and letter, a count is kept track the one below. The idea us that we would like to keep track of the cumulative count of when, say, an employee in a certain year gets the same letter.

Letter   Year  ID   Count  
Z        2019  872    1   
A        2020  182    1  
A        2020  182    2  
B        2019  112    1  
C        2020  112    1  
A        2019  182    1  
C        2019  112    1

Upvotes: 1

Views: 48

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

Try:

df['Count'] = df.groupby(['Letter','Year']).cumcount() + 1

OR

df['Count'] =  df.groupby(['Letter', 'Year', 'ID']).cumcount() + 1

Output:

  Letter  Year   ID  Count
0      Z  2019  872      1
1      A  2020  182      1
2      A  2020  182      2
3      B  2019  112      1
4      C  2020  112      1
5      A  2019  182      1
6      C  2019  112      1

Upvotes: 1

Related Questions