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.