how do you sort column names in Date in descending order in pandas

July 24, 2024

I have this DataFrame:

Node      Interface    Speed   Band_In  carrier     Date            
Server1   wan1         100     80       ATT         2024-05-09
Server1   wan1         100     50       Sprint      2024-06-21
Server1   wan1         100     30       Verizon     2024-07-01  
Server2   wan1         100     90       ATT         2024-05-01
Server2   wan1         100     88       Sprint      2024-06-02
Server2   wan1         100     22       Verizon     2024-07-19

I need to convert Date field to this format 1-May, 2-Jun, 19-July, place them on each column in descending order. In this to look like this:

  Node      Interface    Speed   Band_In  carrier     1-July 9-May  21-Jun          
    Server1   wan1         100     80       ATT        80    50     30

I tried this:

df['Date'] = pd.to_datetime(df['Date']).dt.strftime('%d-%b')
df['is'] = df['Band_In'] / df['Speed'] * 100
df = df.pivot_table(index=['Node', 'Interface', 'carrier'], columns='Date', values='is').reset_index()

I need Date values in the column names to be sorted in descending order 9-May 21-Jun 1-July.

Any ideas how?

>Solution :

Don’t convert your dates to string until after the pivot_table, so can do so easily with rename:

df['Date'] = pd.to_datetime(df['Date'])
df['is'] = df['Band_In'] / df['Speed'] * 100

out = (df.pivot_table(index=['Node', 'Interface', 'carrier'],
                      columns='Date', values='is')
         .rename(columns=lambda x: x.strftime('%-d-%b'))
         .reset_index().rename_axis(columns=None)
      )

Output:

      Node Interface  carrier  1-May  9-May  2-Jun  21-Jun  1-Jul  19-Jul
0  Server1      wan1      ATT    NaN   80.0    NaN     NaN    NaN     NaN
1  Server1      wan1   Sprint    NaN    NaN    NaN    50.0    NaN     NaN
2  Server1      wan1  Verizon    NaN    NaN    NaN     NaN   30.0     NaN
3  Server2      wan1      ATT   90.0    NaN    NaN     NaN    NaN     NaN
4  Server2      wan1   Sprint    NaN    NaN   88.0     NaN    NaN     NaN
5  Server2      wan1  Verizon    NaN    NaN    NaN     NaN    NaN    22.0