Add a Rank column to MultiIndex Dataframe

Question

I like to add a RANK column to a MultiIndex DataFrame.

The DataFrame has three levels (latitude, longitude and time) and two columns (FFDI and REF_ID). It looks like:

                                    FFDI        REF_ID
latitude    longitude   time        
-39.20000   140.80000   1991-04-21  4.10000     0
                        1972-04-21  4.00000     0
                        1972-04-09  3.70000     0
                        1972-05-14  3.60000     0
                        1972-03-11  3.50000     0
            140.83786   1972-04-21  4.20000     1
                        1991-04-21  4.10000     1
                        1972-04-09  3.70000     1
                        1972-05-14  3.70000     1
                        1972-03-11  3.50000     1
            140.87572   1972-04-21  4.20000     2
                        1991-04-21  4.10000     2
                        1972-05-14  3.70000     2
                        1972-04-09  3.60000     2
                        1972-03-11  3.50000     2
...     ...     ...     ...     ...
-33.90000   149.92429   1972-12-23  30.80000    36109
                        1972-12-22  25.90000    36109
                        1991-03-19  22.60000    36109
                        1972-12-24  21.40000    36109
                        1972-09-22  20.00000    36109
            149.96214   1972-12-23  31.60000    36110
                        1972-12-22  26.40000    36110
                        1991-03-19  22.20000    36110
                        1972-12-24  22.10000    36110
                        1972-09-22  20.50000    36110
            150.00000   1972-12-23  33.60000    36111
                        1972-12-22  27.90000    36111
                        1972-12-24  23.60000    36111
                        1991-03-19  23.10000    36111
                        1972-09-22  21.20000    36111

180560 rows × 2 columns

I would like to add a new column called RANK and this will show ranking of FFDI for each latitude and longitude pair (e.g. largest FFDI on top). So the result will look like:

                                    FFDI        REF_ID      RANK
latitude    longitude   time        
-39.20000   140.80000   1991-04-21  4.10000     0           1
                        1972-04-21  4.00000     0           2
                        1972-04-09  3.70000     0           3
                        1972-05-14  3.60000     0           4
                        1972-03-11  3.50000     0           5
            140.83786   1972-04-21  4.20000     1           1
                        1991-04-21  4.10000     1           2
                        1972-04-09  3.70000     1           3
                        1972-05-14  3.70000     1           4
                        1972-03-11  3.50000     1           5
            140.87572   1972-04-21  4.20000     2           1
                        1991-04-21  4.10000     2           2
                        1972-05-14  3.70000     2           3
                        1972-04-09  3.60000     2           4
                        1972-03-11  3.50000     2           5
...     ...     ...     ...     ...     ...     ...
-33.90000   149.92429   1972-12-23  30.80000    36109       1
                        1972-12-22  25.90000    36109       2
                        1991-03-19  22.60000    36109       3
                        1972-12-24  21.40000    36109       4
                        1972-09-22  20.00000    36109       5
            149.96214   1972-12-23  31.60000    36110       1
                        1972-12-22  26.40000    36110       2
                        1991-03-19  22.20000    36110       3
                        1972-12-24  22.10000    36110       4
                        1972-09-22  20.50000    36110       5
            150.00000   1972-12-23  33.60000    36111       1
                        1972-12-22  27.90000    36111       2
                        1972-12-24  23.60000    36111       3
                        1991-03-19  23.10000    36111       4
                        1972-09-22  21.20000    36111       5

180560 rows × 2 columns

Edit: where there are same repeated FFDI values on multiple days, ranking will be based on date in ascending order.

user_4238408 · Answer

You can use groupby():

df['RANK'] = df.groupby(['latitude','longitude'])['FFDI'].rank(ascending=False)

Update per comment, you can try:

df['RANK'] = (df.sort_values(['FFDI','Time'], ascending=[False,True])
                .groupby(['latitude','longitude']).cumcount() + 1
             )

You can also try to pass method='first' to rank on the original answer, given the Time is sorted.

Add a Rank column to MultiIndex Dataframe

1 Answers