How to make pandas.read_excel with engine='openpyxl' behave like it did with xlrd, not showing nanoseconds by default?

user_4925853 0
Viewed 21679

We have a process that reads data in from an Excel .xlsx spreadsheet into a pandas DataFrame. While trying to upgrade to the latest version (1.2.1) of pandas, I saw the following in the doc for pandas read_excel function for the engine argument:

  • “openpyxl” supports newer Excel file formats.

Changed in version 1.2.0: The engine xlrd now only supports old-style .xls files.

So, I added engine='openpyxl' to my read_excel function call and started to see strange, new behavior, whereby datetime values now were showing nanoseconds by default, which wasn't the case with xlrd. On top of that, I was seeing datetimes a bit off from the expected value seen in Excel by a few nanoseconds. I saw the same thing with pandas 1.2.1 and also 1.1.4.

For the following Excel data (the raw values show as 44098.0416666667 for the 9/24 date and 44083.6847222222 for both 9/9 dates)

enter image description here

I'm seeing the following behavior:

>>> import pandas as pd
>>> pd.read_excel('~/testDatetimeNanos.xlsx')
             TestDate
0 2020-09-24 01:00:00
1 2020-09-09 16:26:00
2 2020-09-09 16:26:00
>>> pd.read_excel('~/testDatetimeNanos.xlsx', engine='openpyxl')
                    TestDate
0 2020-09-24 01:00:00.000003
1 2020-09-09 16:25:59.999998
2 2020-09-09 16:26:00.000000

I'm wondering if there's a way to use the new openpyxl engine so that its behavior is consistent with the old xlrd engine...?
Also, wondering if I may have stumbled onto a bug (update: submitted bug report).

As of openpyxl ≥3.0.7, the bug has been fixed (Aug 2021)

openpyxl pandas excel python
1 Answers

Regardless of the engine and the version of openpyxl, you can simply remove the nanoseconds like this:

df['testDate'] = df['testDate'].str[:-7]

#              testDate
#0  2020-09-24 01:00:00
#1  2020-09-09 16:25:59
#2  2020-09-09 16:26:00
user_17795320
Related