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'))