How to sort a Pandas DataFrame according to multiple criteria?

Question

I have the following DataFrame containing song names, their peak chart positions and the number of weeks they spent at position no 1:

                                          Song            Peak            Weeks 76                            Paperback Writer               1               16 117                               Lady Madonna               1                9 118                                   Hey Jude               1               27 22                           Can't Buy Me Love               1               17 29                          A Hard Day's Night               1               14 48                              Ticket To Ride               1               14 56                                       Help!               1               17 109                       All You Need Is Love               1               16 173                The Ballad Of John And Yoko               1               13 85                               Eleanor Rigby               1               14 87                            Yellow Submarine               1               14 20                    I Want To Hold Your Hand               1               24 45                                 I Feel Fine               1               15 60                                 Day Tripper               1               12 61                          We Can Work It Out               1               12 10                               She Loves You               1               36 155                                   Get Back               1                6 8                               From Me To You               1                7 115                              Hello Goodbye               1                7 2                             Please Please Me               2               20 92                   Strawberry Fields Forever               2               12 93                                  Penny Lane               2               13 107                       Magical Mystery Tour               2               16 176                                  Let It Be               2               14 0                                   Love Me Do               4               26 157                                  Something               4                9 166                              Come Together               4               10 58                                   Yesterday               8               21 135                       Back In The U.S.S.R.              19                3 164                         Here Comes The Sun              58               19 96       Sgt. Pepper's Lonely Hearts Club Band              63               12 105         With A Little Help From My Friends              63                7 

I'd like to rank these songs in order of popularity, so I'd like to sort them according to the following criteria: songs that reached the highest position come first, but if there is a tie, the songs that remained in the charts for the longest come first.

I can't seem to figure out how to do this in Pandas.

Answer 1

On pandas 0.9.1 and higher this should work (this is with 0.10.0b1):

(Edit: As of Pandas 0.19, method sort_index is deprecated. Prefer sort_values)

In [23]: songs.sort_index(by=['Peak', 'Weeks'], ascending=[True, False]) Out[23]:                                        Song  Peak  Weeks 10                           She Loves You     1     36 118                               Hey Jude     1     27 20                I Want To Hold Your Hand     1     24 22                       Can't Buy Me Love     1     17 56                                   Help!     1     17 76                        Paperback Writer     1     16 109                   All You Need Is Love     1     16 45                             I Feel Fine     1     15 29                      A Hard Day's Night     1     14 48                          Ticket To Ride     1     14 85                           Eleanor Rigby     1     14 87                        Yellow Submarine     1     14 173            The Ballad Of John And Yoko     1     13 60                             Day Tripper     1     12 61                      We Can Work It Out     1     12 117                           Lady Madonna     1      9 8                           From Me To You     1      7 115                          Hello Goodbye     1      7 155                               Get Back     1      6 2                         Please Please Me     2     20 107                   Magical Mystery Tour     2     16 176                              Let It Be     2     14 93                              Penny Lane     2     13 92               Strawberry Fields Forever     2     12 0                               Love Me Do     4     26 166                          Come Together     4     10 157                              Something     4      9 58                               Yesterday     8     21 135                   Back In The U.S.S.R.    19      3 164                     Here Comes The Sun    58     19 96   Sgt. Pepper's Lonely Hearts Club Band    63     12 105     With A Little Help From My Friends    63      7