Fix Issue 34748 - read in datetime as MultiIndex for column headers
State: closed

- [x] closes #34748
- [x] tests added / passed
  - `pandas/tests/io/excel/test_readers.py`, method `test_read_datetime_multiindex`
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Labels:
name: Bug
name: IO Excel
description: read_excel, to_excel

Comments:
> looks good - can you test for the other file extensions?

Just pushed a change. Couldn't figure out how to get an ODS file that would successfully read, so I put in xfail for that
@Dr-Irv added on ODS file to this if you want to pull locally
@WillAyd Thanks.  Latest push merges with master and removes the xfail on ods.  Passes on my Windows box....
@WillAyd I'm currently failing because the XLSB file I created via Excel can't be read by pandas using `xlrd` or `openpyxl`.  Is there a way to create xlsb files for testing that pandas can read?
pyxlsb is the only engine that can read that format. IIRC there’s also a limitation in that tool where it can’t distinguish datetime types which may be problematic for this (can skip until that gets patched if needed)

Sent from my iPhone

> On Jun 24, 2020, at 10:28 AM, Irv Lustig <notifications@github.com> wrote:
> 
> ﻿
> @WillAyd I'm currently failing because the XLSB file I created via Excel can't be read by pandas using xlrd or openpyxl. Is there a way to create xlsb files for testing that pandas can read?
> 
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub, or unsubscribe.

> pyxlsb is the only engine that can read that format. IIRC there’s also a limitation in that tool where it can’t distinguish datetime types which may be problematic for this (can skip until that gets patched if needed)

Thanks @WillAyd .  pyxlsb not supporting datetimes was the issue.


ref #31215 and https://github.com/willtrnr/pyxlsb/issues/10
@jreback Summarizing the discussion above.  There seem to be 2 separate things raised above:

1. In current master, if you use `TimeStamp` as a `MultiIndex` for the columns, there is a failure:
```python
In [1]: import pandas as pd

In [2]: pd.__version__
Out[2]: '1.1.0.dev0+1952.gc744e35d0.dirty'

In [3]: expected_column_index = pd.MultiIndex.from_tuples(
   ...:     [(pd.to_datetime("02/29/2020"), pd.to_datetime("03/01/2020"))],
   ...:     names=[
   ...:         'a', 'b'])

In [4]: pd.DataFrame([], columns=expected_column_index)
---------------------------------------------------------------------------
InvalidIndexError
```
2. The Excel reader (and in fact all constructors) allow the name of the column index to be a `datetime.datetime`:
```python
In [1]: import pandas as pd

In [2]: import datetime as dt

In [3]: df=pd.DataFrame([], columns=pd.Index(["a"], name=dt.datetime(2020,6,25)
   ...: ))

In [4]: df.columns
Out[4]: Index(['a'], dtype='object', name=2020-06-25 00:00:00)

In [5]: type(df.columns.name)
Out[5]: datetime.datetime
```

It seems to me you are suggesting that I create an issue for (1) and we fix that before merging in this PR.

For (2), I suggest that we don't worry about that, or we could create an issue that says that pandas needs to coerce the names of indexes that are `datetime.datetime` into `Timestamp`

Let me know if you'd like to proceed in a different manner.

<samp>
Commenter does not have sufficient privileges for PR 34954 in repo pandas-dev/pandas<br>

</samp>
/azp run
<samp>
Azure Pipelines successfully started running 1 pipeline(s).<br>

</samp>
@jreback pinging you here, as now that I fixed the other bug, this PR should be ready for merge
> @jreback pinging you here, as now that I fixed the other bug, this PR should be ready for merge

don't we need to change to use Timestamps?
> > @jreback pinging you here, as now that I fixed the other bug, this PR should be ready for merge
> 
> don't we need to change to use Timestamps?

See discussion above (https://github.com/pandas-dev/pandas/pull/34954#issuecomment-649614583).   That's a separate issue (# 2 in that comment, which I could create if you want), which is whether or not we want to allow the _names_ of a `MultiIndex` to be `datetime` objects, which would also entail a change to the Excel reader (and maybe elsewhere), so that a name can't be a `datetime` but is coerced to a `Timestamp`

thanks @Dr-Irv ok i get it