pd.read_excel parse_dates not implemented

[AlbertDeFusco]

Code Sample, a copy-pastable example if possible

conda create -n pandas18 python=3.5 pandas xlrd ipython

In [1]: import pandas as pd

In [2]: pd.__version__
Out[2]: '0.18.0'

In [3]: url='https://github.com/chris1610/pbpython/raw/master/data/sales-feb-2014.xlsx'

In [4]: pd.read_excel(url, parse_dates=True, index_col='date').info()
---------------------------------------------------------------------------
NotImplementedError                       Traceback (most recent call last)
<ipython-input-4-636dbf8d885a> in <module>()
----> 1 pd.read_excel(url, parse_dates=True, index_col='date').info()

/Users/adefusco/Applications/anaconda3/envs/p18/lib/python3.5/site-packages/pandas/io/excel.py in read_excel(io, sheetname, header, skiprows, skip_footer, index_col, names, parse_cols, parse_dates, date_parser, na_values, thousands, convert_float, has_index_names, converters, engine, squeeze, **kwds)
    175         convert_float=convert_float, has_index_names=has_index_names,
    176         skip_footer=skip_footer, converters=converters,
--> 177         squeeze=squeeze, **kwds)
    178 
    179 

/Users/adefusco/Applications/anaconda3/envs/p18/lib/python3.5/site-packages/pandas/io/excel.py in _parse_excel(self, sheetname, header, skiprows, skip_footer, index_col, has_index_names, parse_cols, parse_dates, date_parser, na_values, thousands, convert_float, verbose, squeeze, **kwds)
    308                                       "is not implemented")
    309         if parse_dates:
--> 310             raise NotImplementedError("parse_dates keyword of read_excel "
    311                                       "is not implemented")
    312 

NotImplementedError: parse_dates keyword of read_excel is not implemented

Expected Output

conda install pandas=0.17

In [1]: import pandas as pd

In [2]: pd.__version__
Out[2]: '0.17.1'

In [3]: url='https://github.com/chris1610/pbpython/raw/master/data/sales-feb-2014.xlsx'

In [4]: pd.read_excel(url, parse_dates=True, index_col='date').info()
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 108 entries, 2014-02-01 09:04:59 to 2014-02-28 23:47:32
Data columns (total 6 columns):
account number    108 non-null int64
name              108 non-null object
sku               108 non-null object
quantity          108 non-null int64
unit price        108 non-null float64
ext price         108 non-null float64
dtypes: float64(2), int64(2), object(2)
memory usage: 5.9+ KB

output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.5.1.final.0
python-bits: 64
OS: Darwin
OS-release: 14.5.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8

pandas: 0.18.0
nose: None
pip: 8.1.1
setuptools: 20.3
Cython: None
numpy: 1.10.4
scipy: None
statsmodels: None
xarray: None
IPython: 4.1.2
sphinx: None
patsy: None
dateutil: 2.5.0
pytz: 2016.1
blosc: None
bottleneck: None
tables: None
numexpr: None
matplotlib: None
openpyxl: None
xlrd: 0.9.4
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
boto: None

[jreback]

dupe of #11544 ; fixed in #12051

[jorisvandenbossche]

@AlbertDeFusco Note that if your data are formatted as dates in excel (which will be the majority of the cases), you do not need to specify the parse_dates=True, as you will automatically get datetime values in pandas.
It's only when your dates are strings in excel, you need parse_dates, and that this now raises NotImplementedError is a regression in pandas 0.18.0.

[pcattori]

@jorisvandenbossche is there a way to disable the automatic parsing for columns formatted as dates in excel? In my excel file I have 3/22/2016 but my dataframe displays this as 2016-03-22 after I create it with read_excel

[YDD9]

@pcattori

• Using converters{'Date': str} option in the pandas.read_excel which helps me.
• you can try convert your timestamp back to the original format df['Date'][0].strftime('%Y/%m/%d')

[minhouse]

Hi, I'd like to have support to fix this error.
I got this error below when i load data from a Excel file that contains dates in column A and tried to use it as x axis for a graph by matplotlib. I didn't have this issue with Pandas0.16
Could anyone give me an advise how can i fix this error?

Error I got with parse_dates=['date'] option：

File "C:\Program Files\Anaconda3\lib\site-packages\pandas\io\excel.py", line 339, in _parse_excel
raise NotImplementedError("parse_dates keyword of read_excel "
NotImplementedError: parse_dates keyword of read_excel is not implemented

If i remove parse_dates=['date'] option, i will get a different error below:

File "C:\Program Files\Anaconda3\lib\site-packages\numpy\core\numeric.py", line 531, in asarray
return array(a, dtype, copy=False, order=order)
ValueError: could not convert string to float: '2016/02/25'

Excel Source File(Column A)：

date
2016-03-11
2016-03-11
2016-03-11
2016-03-11
2016-03-11
2016-03-11
2016-03-11
2016-03-11
2016-03-11

Code:

for i, j in enumerate(dflist):
df = pd.read_excel('C:/Users/Desktop/Python_Scripts/%s' %excel, sheetname ='%s' % j, na_values=['NA'], parse_dates=['date'])

df.sort_values(by = ["date"])
i = i + 1
x = df[["date"]]
y = df[["errors"]]
--A line below is to create subplots
ax = fig.add_subplot(int(n), 3, i)
ax.plot(x, y, "-o")

[jorisvandenbossche]

@minhouse you can easily fix your problem by doing the date conversion after reading in the excel

df['date'] = pd.to_datetime(df['date'])

That said, this problem should also be solved in pandas 0.20 (the NotImplementedError should be removed). Can you test if this is the case?

[minhouse]

hi, @jorisvandenbossche thank you very much! i was able to fix this problem with your solution.
may i ask you when pandas0.20 will be released?

thank you again

[jorisvandenbossche]

Pandas 0.20.1 was released about 3 weeks ago, so if you update your installation, you should already get it

[AlbertDeFusco]

Confirmed that parse_dates= now works with pd.read_excel in version 0.20.1 when datetimes are strings in the Excel file.

Thanks, @jorisvandenbossche!

[jorisvandenbossche]

Thanks for confirming! (and you have to thank Jeff for getting it in 0.20.1 :-))

[minhouse]

Thank you for Pandas0.20.1, ive just upgraded and confirmed it is working too.! I love Pandas. Thanks for Jeff.