Pandas datetime error when reading from excel file

Question

I am trying to read an excel file that has two columns using pandas.

This is how the data looks in excel file:

DT                    Values
2019-11-11 10:00      28.9
2019-11-11 10:01      56.25
2019-11-11 10:02      2.45
2019-11-11 10:03      96.3
2019-11-11 10:04      18.4
2019-11-11 10:05      78.9

This is how it looks when I read using pandas:

DT                         Values
2019-11-11 10:00:00.000    28.9
2019-11-11 10:01:00.000    56.25
2019-11-11 10:01:59:995    2.45
2019-11-11 10:02:59:995    96.3
2019-11-11 10:03:59:995    18.4
2019-11-11 10:04:59:995    78.9

I have tried creating a new DateTime column, putting the data in a new excel file, converting the DT column to DateTime format in both pandas and excel. Nothing has worked yet!

Why does this happen?

EDIT - 1

I already tried the following code but forgot to mention the snippet,

df= pd.read_excel('data.xlsx', parse_dates = ['DT'])
df.head()

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

Answer 1

Using pandas, first make sure you have a datetime column:

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

To remove the milliseconds, a possible solution is to use round to obtain a specified frequency (in this case seconds).

df['DT'] = df['DT'].dt.round(freq='s')

Depending on the wanted final result, ceil (to always round up) or floor (always round down) could be more suitable.

Answer 2

Too low of rank to comment. Could you check the data type of the 2019-11-11 10:00:00.000? Then look up how to convert type(obj) to date-time format. Maybe this will help Documentation