Sorting DataFrame by datetime64[ns]-dtype column values not working as expected. Why?

Question

I have a chronological-disordered list stored in a excel sheet called 'Compilado' that I want to reorder correctly to prepare for data analysis.

I parsed it to a Pandas Dataframe running:

df = pandas.read_excel(r'C:\Users\KMBGSI\Downloads\Historico de Alertas.xlsx',sheet_name='Compilado', header=None, names= header_list, index_col=None, parse_dates=[0])

Dataframe preview:

df.head()

df.info() returns:

return from df.info()

so dtypes are okay.

Data from column 'Data' seems to have been parsed right and is shown in the format dd/mm/YYYY.

However, when I run the code below data from column 'Data' seems to have its format changed:

df.sort_values(by=['Data'], inplace=True)
df.head()

dataframe preview after sorting by 'Data' column values

I know '2021-01-12' is actually '2021-12-01' wrongly formatted, because my dataset begins in 01/09/2021 (2021-09-01).

Why does it happen? How can I reorder this dataset keeping datetime64[ns] values correclty formatted?

Thanks! Kind regards,

Full code for reference:

import os, sys, pandas, numpy, matplotlib, seaborn

header_list = ['Data', 'Hora', 'Status']

df = pandas.read_excel(r'C:\Users\KMBGSI\Downloads\Historico de Alertas.xlsx',sheet_name='Compilado', header=None, names= header_list, index_col=None, parse_dates=[0])

"comment after checking dataframe is okay before proceeding"
#df.info()
#df.head()
#df.tail()

df.sort_values(by=['Data'], inplace=True)

df.head()

Answer 1

You are right df.sort_values() converts the format from yyyy-mm-dd to yyyy-dd-mm

You can change the format after df.sort_values by this command:

df['Data'] = df['Data'].dt.strftime('%m-%d-%Y')

However, the datatype of the column will change to object

To convert back to datetime64[ns]

df['Data']=pd.to_datetime(df['Data'])

You can check the documentation of df.sort_values() here:

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.sort_values.html