0
$\begingroup$

I have the following dataframe df of athletes (indexed by Athlete_ID) and their Rank (indexed by Rank), here is a slide of the dataframe with a particular athlete:

Rank    Athlete_ID  Date
13      143         25/4/2021
1       143         5/4/2021
6       143         24/2/2021
11      143         24/1/2021
4       143         1/1/2021
9       143         13/12/2020
8       143         22/11/2020
1       143         23/9/2020
9       143         6/9/2020
10      143         20/5/2020
1       143         18/3/2020
7       143         26/2/2020
1       143         29/1/2020
1       143         18/12/2019
1       143         20/11/2019
7       143         2/3/2019
4       143         10/2/2019
7       143         27/6/2018
9       143         6/5/2018
2       143         7/1/2018
2       143         17/12/2017
1       143         5/11/2017
3       143         8/10/2017

I want to count the total number of wins (rank number 1) and recent number of wins (number of wins in this year), here is my code:

df['Athle_total_wins']=df.sort_values(['Athlete_ID','Date'],ascending=[True,True])['Rank'].shift(1).eq(1).groupby(df['Athlete_ID']).cumsum()
 
df['Athle_recent_wins']=df.sort_values(['Athlete_ID','Date'],ascending=[True,True])['Rank'].shift(1).eq(1).groupby([df['Athlete_ID'],df['Date'].dt.year]).cumsum()
 

and the output is

Rank    Athlete_ID  Date        Athle_total_wins    Athle_recent_wins
13      143         25/4/2021   8                   1
1       143         5/4/2021    7                   0
6       143         24/2/2021   7                   0
11      143         24/1/2021   7                   0
4       143         1/1/2021    7                   0
9       143         13/12/2020  7                   4
8       143         22/11/2020  7                   4
1       143         23/9/2020   6                   3
9       143         6/9/2020    6                   3
10      143         20/5/2020   6                   3
1       143         18/3/2020   5                   2
7       143         26/2/2020   5                   2
1       143         29/1/2020   4                   1
1       143         18/12/2019  3                   1
1       143         20/11/2019  2                   0
7       143         2/3/2019    2                   0
4       143         10/2/2019   2                   0
7       143         27/6/2018   2                   0
9       143         6/5/2018    2                   0
2       143         7/1/2018    2                   0
2       143         17/12/2017  2                   2
1       143         5/11/2017   1                   1
3       143         8/10/2017   1                   1

which is almost what I want but not exactly because the last row doesn't start at 0 (i.e. something is wrong for the last year in 2017, or the last few rows). The desired output should be

Rank    Athlete_ID  Date        Athle_total_wins    Athle_recent_wins
13      143         25/4/2021   7                   1
1       143         5/4/2021    6                   0
6       143         24/2/2021   6                   0
11      143         24/1/2021   6                   0
4       143         1/1/2021    6                   0
9       143         13/12/2020  6                   4
8       143         22/11/2020  6                   4
1       143         23/9/2020   5                   3
9       143         6/9/2020    5                   3
10      143         20/5/2020   5                   3
1       143         18/3/2020   4                   2
7       143         26/2/2020   4                   2
1       143         29/1/2020   3                   1
1       143         18/12/2019  2                   1
1       143         20/11/2019  1                   0
7       143         2/3/2019    1                   0
4       143         10/2/2019   1                   0
7       143         27/6/2018   1                   0
9       143         6/5/2018    1                   0
2       143         7/1/2018    1                   0
2       143         17/12/2017  1                   1
1       143         5/11/2017   0                   1
3       143         8/10/2017   0                   0
$\endgroup$
1
  • $\begingroup$ When I ran your code on your provided data I got your desired output. Is there something in the data that you haven't provided that could be affecting your results? $\endgroup$
    – Lynn
    Sep 22, 2022 at 13:17

1 Answer 1

0
$\begingroup$

Get the total number of wins per Athlete_ID:

print( df.where(df.Rank == 1).groupby([df.Athlete_ID]).agg({'Rank': 'count'}) )
#            Rank
#Athlete_ID
#143            7

Get the number of wins per Athlete_ID and Date.year:

print( df.where(df.Rank == 1).groupby([df.Athlete_ID, df.Date.dt.year]).agg({'Rank': 'count'}) )
#                 Rank
#Athlete_ID Date
#143        2017     1
#           2018     0
#           2019     2
#           2020     3
#           2021     1
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.