3
$\begingroup$

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'])
$\endgroup$

2 Answers 2

4
$\begingroup$

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.

$\endgroup$
2
  • 1
    $\begingroup$ The second snippet worked perfectly, Didn't know that I could round-off a datetime object. Thanks! $\endgroup$
    – Uday T
    Nov 11, 2019 at 8:35
  • $\begingroup$ Is there a way to do this automatically for all columns using datetimes (without specifying them one by one), when doing read_excel()? $\endgroup$
    – Basj
    Mar 8, 2021 at 16:25
0
$\begingroup$

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

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.