4
$\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'])
Improve this question
$\endgroup$

2 Answers 2

Reset to default
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.

Improve this answer
$\endgroup$
3
  • 1
    $\begingroup$ The second snippet worked perfectly, Didn't know that I could round-off a datetime object. Thanks! $\endgroup$ Commented Nov 11, 2019 at 8:35
  • 1
    $\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$ Commented Mar 8, 2021 at 16:25
  • $\begingroup$ I don't know why, but when reading an xlsx file with pandas, some datetimes were off by a minute. So I had to use df.set_index(df.index.round(freq='h'), inplace=True), which seems really weird. I'd rather parse a CSV file and specify an explicit strftime format. $\endgroup$ Commented Jun 6, 2024 at 12:03
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

Improve this answer
$\endgroup$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.