I have a data frame with multiple columns. How do I group the columns by year, and add a new column-CodePudding

I have the following data frame:

            Names      Counts  Year
0           Jordan        1043  2000
1            Steve         204  2000
2             Mike          88  2000
3            Steve          33  2000
4            Brock           3  2000
...           ...         ...   ...
20001        Penny          24  2015
20002        Steve          15  2015
20003        Steve          11  2015
20004        Ryan            5  2015    
20005        Bryce           2  2015

I want to add a column called 'Standings' which ranks each row based on 'Counts'. I want this column called 'Standings' to be grouped by 'Year'.

Example output might look like:

            Names      Counts  Year   Standings
0           Jordan        1043  2000      1
1            Steve         204  2000      2
2             Mike          88  2000      3
3            Steve          88  2000      3
4            Brock           3  2000      4
...           ...         ...   ...
20001        Penny          24  2015      1
20002        Steve          24  2015      1
20003        Steve          11  2015      2
20004        Ryan            2  2015      3
20005        Bryce           2  2015      3

I've tried simply creating a list for each year which goes from 1 to the number of values in a group, but that did not account for ties in 'Counts'.

CodePudding user response：

One way using pandas.DataFrame.groupby.rank with method=="min"

df["Standings"] = df.groupby("Year")["Counts"].rank(method="min", ascending=False)
print(df)

Output:

        Names  Counts  Year  Standings
0      Jordan    1043  2000        1.0
1       Steve     204  2000        2.0
2        Mike      88  2000        3.0
3       Steve      88  2000        3.0
4       Brock       3  2000        5.0
20001   Penny      24  2015        1.0
20002   Steve      24  2015        1.0
20003   Steve      11  2015        3.0
20004    Ryan       2  2015        4.0
20005   Bryce       2  2015        4.0