pythonpandassortingformatdatetime

How can I format the date on a pandas dataframe index column without messing the order of my index?

I'm having problems on formating my dataframe index without messing its order. Given the dataframe:

Reference_Date  2023    2024    2025    2026    2027    2028
Data_Date                       
2023-01-02  12.2500 9.0000  8.0000  8.0000  8.0000  NaN
2023-01-03  12.2500 9.0000  8.0000  8.0000  8.0000  NaN
2023-01-04  12.2500 9.0000  8.0000  8.0000  8.0000  NaN
2023-01-05  12.2500 9.0000  8.0000  8.0000  8.0000  NaN
2023-01-06  12.2500 9.2500  8.0000  8.0000  8.0000  NaN
... ... ... ... ... ... ...
2024-03-22  NaN 9.0000  8.5000  8.5000  8.5000  8.2500
2024-03-25  NaN 9.0000  8.5000  8.5000  8.5000  8.2500
2024-03-26  NaN 9.0000  8.5000  8.5000  8.5000  8.250

Have in mind that I did: df_pivot = df.pivot_table(index='Data', columns='Reference_Date', values='Value'), and the result was the table above.

I'm trying to format the Data_Date from '%Y-%m-%d' to '%d/%m/%Y'.

I've tried before pivoting the table:

df['Data_Date'] = pd.to_datetime(df['Data_Date'], format='%Y-%m-%d') 
df['Data_Date']= df['Data_Date'].dt.strftime('%d/%m/%Y')

It did format as I wanted, but the order got messed up:

DataReferencia  2023    2024    2025    2026    2027    2028
Data                        
01/02/2023  12.5000 9.5000  8.7500  8.5000  8.5000  NaN
01/02/2024  NaN 9.0000  8.5000  8.5000  8.5000  8.5000
01/03/2023  12.7500 10.0000 9.0000  8.5000  8.5000  NaN
01/03/2024  NaN 9.0000  8.5000  8.5000  8.5000  8.5000
01/06/2023  12.5000 10.0000 9.0000  9.0000  9.0000  NaN
... ... ... ... ... ... ...
31/03/2023  12.7500 10.0000 9.0000  8.7500  9.0000  NaN
31/05/2023  12.5000 10.0000 9.0000  9.0000  9.0000  NaN
31/07/2023  12.0000 9.2500  8.7500  8.5000  8.7500  NaN

What should I do? Is there a way to do this? Sorry for my bad english.

Solution

  • sort by index in your last result (sort_index func)

    out = df_pivot.sort_index(key=lambda x: pd.to_datetime(x, format='%d/%m/%Y'))