CODEWITHSUNDEEP
pythonpandassortingdataframe
rude.boy
rude.boy

Reputation: 97

How to sort the columns of a pandas data frame by the values of an index

Is there a possibility to sort the columns of a pandas df by the values of a particular index?

price / time           load_1 load_2 load_3 load_4
price                    50,    68,    23,    12
2018-01-01 00:00:00      12,    65,    37,     8
2018-01-01 00:15:00      13,    54,   112,     6
2018-01-01 00:30:00      58,     12,   96,     4

(the first index is the price for energy consumption whereas the following rows stand for amounts of energy. The number of columns shall be undefined)

So that it essentially looks like:

price / time           load_2 load_1 load_3 load_4
price                    68,    50,    23,    12
2018-01-01 00:00:00      65,    12,    37,     8
2018-01-01 00:15:00      54,    13,   112,     6
2018-01-01 00:30:00      12,    58,    96,     4

by sorting the columns by the price index in an ascending or descending order.

Upvotes: 1

Views: 86

Answers (1)

jezrael
jezrael

Reputation: 862641

If index name is price then use sort_values with axis=1:

print (df.columns)
Index(['load_1', 'load_2', 'load_3', 'load_4'], dtype='object')

df = df.sort_values('price', axis=1, ascending=False)
print (df)
                     load_2  load_1  load_3  load_4
price / time                                       
price                    68      50      23      12
2018-01-01 00:00:00      65      12      37       8
2018-01-01 00:15:00      54      13     112       6
2018-01-01 00:30:00      12      58      96       4

If MultiIndex in columns use DataFrame.sort_index:

print (df.columns)
MultiIndex(levels=[['load_1', 'load_2', 'load_3', 'load_4'], ['12', '23', '50', '68']],
           labels=[[0, 1, 2, 3], [2, 3, 1, 0]],
           names=['price / time', 'price'])

df = df.sort_index(axis=1, level=1, ascending=False)
print (df)
price / time        load_2 load_1 load_3 load_4
price                   68     50     23     12
2018-01-01 00:00:00     65     12     37      8
2018-01-01 00:15:00     54     13    112      6
2018-01-01 00:30:00     12     58     96      4

Also here should be problem is necessary convert second level to integers:

a = df.columns.get_level_values(0)
b = df.columns.get_level_values(1).astype(int)

df.columns = pd.MultiIndex.from_arrays([a,b], names=df.columns.names)

print (df.columns)
MultiIndex(levels=[['load_1', 'load_2', 'load_3', 'load_4'], [12, 23, 50, 68]],
           labels=[[0, 1, 2, 3], [2, 3, 1, 0]],
           names=['price / time', 'price'])

df = df.sort_index(axis=1, level=1, ascending=False)
print (df)
price / time        load_2 load_1 load_3 load_4
price                   68     50     23     12
2018-01-01 00:00:00     65     12     37      8
2018-01-01 00:15:00     54     13    112      6
2018-01-01 00:30:00     12     58     96      4

Upvotes: 4

Related Questions