Pandas: sorting by integer notation value

Question

In this dataframe, column key values correspond to integer notation of each song key.

    df
                                                track    key  
    0                                        Last Resort     4  
    1                                Casimir Pulaski Day     8  
    2                                         Glass Eyes     8   
    3                    Ohio - Live At Massey Hall 1971     7   
    4                               Ballad of a Thin Man    11  
    5                               Can You Forgive Her?    11   
    6                                     The Only Thing     3    
    7                        Goodbye Baby (Baby Goodbye)     4    
    8                                     Heart Of Stone     0   
    9                                               Ohio     0   
    10                                          the gate     2   
    11                                         Clampdown     2    
    12                                     Cry, Cry, Cry     4   
    13                          What's Happening Brother     8   
    14                                       Stupid Girl    11   
    15                          I Don't Wanna Play House     7   
    16           Inner City Blues (Make Me Wanna Holler)    11   
    17              The Lonesome Death of Hattie Carroll     4   
    18  Paint It, Black - (Original Single Mono Version)     5  
    19                                  Let Him Run Wild    11    
    20            Undercover (Of The Night) - Remastered     5    
    21                                  Between the Bars     7   
    22                              Like a Rolling Stone     0   
    23                                              Once     2   
    24                                    Pale Blue Eyes     5   
    25          The Way You Make Me Feel - 2012 Remaster     1   
    26                                            Jeremy     2   
    27                                   The Entertainer     7   
    28                                          Pressure     9   
    29   Play With Fire - Mono Version / Remastered 2002     2   
    30                                     D-I-V-O-R-C-E     9   
    31                                          Big Shot     0   
    32                                   What's Going On     1   
    33                        Folsom Prison Blues - Live     0   
    34                                    American Woman     1  
    35                              Cocaine Blues - Live     8   
    36                                       Jesus, etc.     5

the notation is as follows:

'C' --> 0
'C#'--> 1
'D' --> 2
'Eb'--> 3
'E' --> 4
'F' --> 5
'F#'--> 6
'G' --> 7
'Ab'--> 8
'A' --> 9
'Bb'--> 10
'B' --> 11

what is specific about this notation is that 11 is closer to 0 than 2, for instance.

GOAL:

given an input_notation = 0, I would like to sort according to closeness to key 0, or 'C'.

you can get closest value by doing:

closest_key = (input_notation -1) % 12

so I would like to sort according to this logic, having on top input_notation values and then closest matches, like so:

    8                                     Heart Of Stone     0   
    9                                               Ohio     0 
    22                              Like a Rolling Stone     0   
    31                                          Big Shot     0   
    33                        Folsom Prison Blues - Live     0  
   (...)

I have tried:

v = df[['key']].values

df = df.iloc[np.lexsort(np.abs(v - (input_notation - 1) %12 ).T)]

but this does not work..

any clues?

akuiper · Accepted Answer · 2017-10-14 20:27:15Z

1

You can define the closeness firstly and then use argsort with iloc to sort the data frame:

input_notation = 0

# define the closeness or distance
diff = (df.key - input_notation).abs()
closeness = np.minimum(diff, 12 - diff)

# use argsort to calculate the sorting index, and iloc to reorder the data frame
closest_to_input = df.iloc[closeness.argsort(kind='mergesort')]

closest_to_input.head()
#                        track  key
#8              Heart Of Stone    0
#9                        Ohio    0
#22       Like a Rolling Stone    0
#31                   Big Shot    0
#33 Folsom Prison Blues - Live    0

answered Oct 14, 2017 at 20:27

akuiper

216k33 gold badges362 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas: sorting by integer notation value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related