python - 无法使用 read_excel 从 pandas 中的 xlsx 文件读取日期列？

Question

我在 Excel 中的单元格格式为日期(见下文):

我无法读取它们(它们是 NaN)，因此当从 Pandas read_excel 方法读取时，我使用转换器尝试将它们转换为 to_datetime :

   cols_A8_J2007[i] = pd.read_excel(
                      i, 
                     ('sheet'+str(j)), 
                     headers = 1, skiprows = 6, nrows=2000, 
                     usecols = 'A:J', 
                     converters = {
                       'Expired': lambda x: pd.to_datetime(x, errors='coerce') , 
                   'Valid Until': lambda x: pd.to_datetime(x, errors='coerce')})

这导致它们全部被加载为 NaT。

因此，在查阅文档后，我尝试了这种方式:

    cols_A8_J2007[i] = pd.read_excel(i, ('sheet'+str(j)), headers = 1, parse_dates=True, skiprows = 6, nrows=2000, usecols = 'A:J' )

这再次导致NaN。

最后我尝试了这样的方法，并且再次得到了 NaN :

    cols_A8_J2007[i] = pd.read_excel(i, ('sheet'+str(j)), headers = 1, parse_dates=True, date_parser=lambda x: pd.to_datetime(x, errors='coerce'), skiprows = 6, nrows=2000, usecols = 'A:J' )

上面的方法不起作用，因为它尝试根据索引进行解析(请参阅下面的评论)。

cols_A8_J2007[i] = pd.read_excel(i, ('sheet'+str(j)), headers = 1, parse_dates=['Expired', 'Valid Until'], skiprows = 6, nrows=2000, usecols = 'A:J' )

cols_A8_J2007[i] = pd.read_excel(i, ('sheet'+str(j)), headers = 1, parse_dates=['Expired', 'Valid Until'], skiprows = 6, nrows=2000, usecols = 'A:J' )

cols_A8_J2007[i] = pd.read_excel(i, ('sheet'+str(j)), headers = 1, parse_dates=['Expired', 'Valid Until'], dateparser=lambda x: pd.to_datetime(x, errors='coerce'), skiprows = 6, nrows=2000, usecols = 'A:J' )

这两个结果都是NaT(不是一次？)

我还需要做什么才能读取日期？我意识到没有时间附加，但是Excel stores dates and times的方式，这应该不重要，因为时间存储为小数。

for i in glob.iglob(((str(xls_folder) + '\somesheets*.xlsx'))):
    cols_A8_J2007[i] = pd.read_excel(i, ('sheet'+str(j)), headers = 1, skiprows = 6, nrows=2000, usecols = 'A:J', converters = {'Expired': lambda x: pd.to_datetime(x, errors='coerce') , 'Valid Until': lambda x: pd.to_datetime(x, errors='coerce')})

for w in cols_A8_J2007:
    print(cols_A8_J2007[w].dtypes)

Type                      object
Currency                  object
Initial Credit           float64
Credits                  float64
Debits                   float64
Balance                  float64
Reserved                   int64
Valid Until       datetime64[ns] <-  <- These I believe are what you are looking for..
Expired           datetime64[ns] <- These I believe are what you are looking for..
dtype: object

如果这有帮助，这里是我的版本:

pd.versions()
INSTALLED VERSIONS
------------------
commit: None
python: 3.7.3.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: Intel64 Family 6 Model 158 Stepping 9, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None

pandas: 0.24.2
pytest: 4.5.0
pip: 19.1.1
setuptools: 41.0.1
Cython: 0.29.8
numpy: 1.16.4
scipy: 1.2.1
pyarrow: None
xarray: None
IPython: 7.5.0
sphinx: 2.0.1
patsy: 0.5.1
dateutil: 2.8.0
pytz: 2019.1
blosc: None
bottleneck: 1.2.1
tables: 3.5.1
numexpr: 2.6.9
feather: None
matplotlib: 3.0.3
openpyxl: 2.6.2
xlrd: 1.2.0
xlwt: 1.3.0
xlsxwriter: 1.1.8
lxml.etree: 4.3.3
bs4: 4.7.1
html5lib: 1.0.1
sqlalchemy: 1.3.3
pymysql: None
psycopg2: None
jinja2: 2.10.1
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

Answer 1

确定问题是我需要使用 pd.isnull 检查该值是否为 null。该文件有太多空值，我无法在结果集中看到它们。在这里找到答案:how to test if a variable is pd.NaT?

me_df = pd.read_excel(i, ('iCareAcctListing'+str(j)), headers = 1, skiprows = 6, nrows=2000, usecols = 'A:J', converters = {'Expired': lambda x: pd.to_datetime(x, errors='coerce') , 'Valid Until': lambda x: pd.to_datetime(x, errors='coerce')})

# Ran this and ended up with just the dates that were 
# filled in with actual values.
#
# There were so many nulls before and after that I couldn't see any of them in the dataset!
me_df[pd.isnull(me_df['Valid Until']) != True]