Sort by index and column

Question

I am trying to sort by an index and column but to no avail.

Partial dataset

            ID         Element  Data_Value
Date            
2005-01-01  USW00004848 TMIN    0
2005-01-01  USC00207320 TMAX    150
2005-01-01  USC00207320 TMIN    -11
2005-01-01  USW00014833 TMIN    -44
2005-01-01  USW00014833 TMAX    33

index column

DatetimeIndex(['2005-01-01', '2005-01-01', '2005-01-01', '2005-01-01',
               '2005-01-01', '2005-01-01', '2005-01-01', '2005-01-01',
               '2005-01-01', '2005-01-01',
               ...
               '2015-12-31', '2015-12-31', '2015-12-31', '2015-12-31',
               '2015-12-31', '2015-12-31', '2015-12-31', '2015-12-31',
               '2015-12-31', '2015-12-31'],
              dtype='datetime64[ns]', name='Date', length=165002, freq=None)

My attempt

df2 = df2.rename_axis(df2.index).sort_values(by = [df2.index, 'ID'], ascending = [False, True])

Output from above: ValueError: Length of new names must be 1, got 165002

df2 = df2.rename_axis("Date").sort_values(by = ["Date", "ID"], ascending = [False, True])

Output from above: KeyError: 'Date'

df2 = df2.sort_values(by = [df2.index, 'ID'], ascending = [False, True])

Output from above: KeyError: "DatetimeIndex(['2005-01-01', '2005-01-01', '2005-01-01', '2005-01-01',\n '2005-01-01', '2005-01-01', '2005-01-01', '2005-01-01',\n '2005-01-01', '2005-01-01',\n ...\n '2015-12-31', '2015-12-31', '2015-12-31', '2015-12-31',\n '2015-12-31', '2015-12-31', '2015-12-31', '2015-12-31',\n '2015-12-31', '2015-12-31'],\n dtype='datetime64[ns]', name='Date', length=165002, freq=None) not in index"

df2 = df2.sort_values(by = ["Date", "ID"], ascending = [False, True])

Output from above: KeyError: 'Date'

df2 = df2.sort_values(by = [df2.index.Date, 'ID'], ascending = [False, True])

Output from above: AttributeError: 'DatetimeIndex' object has no attribute 'Date'

What is your pandas version? Because in last it working nice. — jezrael, Oct 10 '19 at 08:36
@jezrael '0.19.2' it is a course assignment and is done on their appointed server environment. — Organic Heart, Oct 10 '19 at 08:38

jezrael · Accepted Answer · 2019-10-10T09:02:45.463

In last pandas version 0.23+ this working nice:

print (df2.index)
DatetimeIndex(['2005-01-01', '2005-01-01', '2005-01-01', '2005-01-01',
               '2005-01-01'],
              dtype='datetime64[ns]', name='Date', freq=None)


df2 = df2.sort_values(by = ["Date", "ID"], ascending = [False, True])
print (df2)
                     ID Element  Data_Value
Date                                       
2005-01-01  USC00207320    TMAX         150
2005-01-01  USC00207320    TMIN         -11
2005-01-01  USW00004848    TMIN           0
2005-01-01  USW00014833    TMIN         -44
2005-01-01  USW00014833    TMAX          33

Another solution working also in some oldier pandas versions is convert DatetimeIndex to column first, sorting and convert back:

df2 = (df2.reset_index()
          .sort_values(by = ["Date", "ID"], ascending = [False, True])
          .set_index('Date'))

Thanks @Alexander for alternative:

df2 = (df.set_index('ID', append=True)
         .sort_index(ascending=[False, True])
         .reset_index('ID'))

print (df2)
                     ID Element  Data_Value
Date                                       
2005-01-01  USC00207320    TMAX         150
2005-01-01  USC00207320    TMIN         -11
2005-01-01  USW00004848    TMIN           0
2005-01-01  USW00014833    TMIN         -44
2005-01-01  USW00014833    TMAX          33

Alternatively, `df.set_index('ID', append=True).sort_index(ascending=[False, True]).reset_index('ID')`. — Alexander, Oct 10 '19 at 08:50

Sort by index and column

1 Answers1