strftime is not recognizing the real datetime-CodePudding

I have a dataframe like this:

df = pd.DataFrame({"DateTime":["26/06/2014 22:05:16",
                               "25/06/2014 22:05:56",
                               "01/07/2014 22:05:30",
                               "01/08/2014 19:04:23"],
                   "Data":[20, 31, 25, 44]})

df
Out[9]: 
              DateTime  Data
0  26/06/2014 22:05:16    20
1  25/06/2014 22:05:56    31
2  01/07/2014 22:05:30    25
3  01/08/2014 19:04:23    44

I would like to convert my DateTime column to datetime64 and specify a format. The original data is like DAY/MONTH/YEAR and then I would like to put them as YEAR-MONTH-DAY. I tried this:

df["DateTime"] = pd.to_datetime(df["DateTime"])
df["DateTime"] = df["DateTime"].dt.strftime('%Y-%m-%d %H:%M:%S')

df
Out[11]: 
              DateTime  Data
0  2014-06-26 22:05:16    20
1  2014-06-25 22:05:56    31
2  2014-01-07 22:05:30    25
3  2014-01-08 19:04:23    44

The first two dates are ok, although the last two didn't convert correctly. The month became day...it should be like this:

2  2014-07-01 22:05:30    25
3  2014-08-01 19:04:23    44

Anyone could show me the correct way to convert this datetime?

CodePudding user response：

The default format for pd.to_datetime is MM/DD. Since your data is DD/MM, you should tell to_datetime to parse day first with dayfirst=True:

df['DateTime'] = pd.to_datetime(df["DateTime"], dayfirst=True).dt.strftime('%Y-%m-%d %H:%M:%S')

CodePudding user response：

in converting datetime, specify dayfirst as True

df["DateTime"] = pd.to_datetime(df["DateTime"], dayfirst=True)
df["DateTime"] = df["DateTime"].dt.strftime('%Y-%m-%d %H:%M:%S')
df

DateTime    Data
0   2014-06-26 22:05:16     20
1   2014-06-25 22:05:56     31
2   2014-07-01 22:05:30     25
3   2014-08-01 19:04:23     44