ENH: read_excel MultiIndex #4679
State: closed

closes #4679
xref #10564

Output of `to_excel` should now be fully round-trippable with `read_excel` with the
right combination of `index_col` and `header`.

To make the semantics match `read_csv`, an index column name (`has_index_names=True`) is
always assumed if something is passed to `index_col` - this should be non-breaking;
if there are no names, it will be just filled to `None` as before.

```
In [7]: df = pd.DataFrame([[1,2,3,4], [5,6,7,8]],
...:                   columns = pd.MultiIndex.from_product([['foo','bar'],['a','b']],
...:                                                        names = ['col1', 'col2']),
...:                   index = pd.MultiIndex.from_product([['j'], ['l', 'k']],
...:                                                      names = ['i1', 'i2']))

In [8]: df
Out[8]: 
col1    foo    bar   
col2    a  b   a  b
i1 i2              
j  l    1  2   3  4
   k    5  6   7  8

In [9]: df.to_excel('test.xlsx')

In [10]: df = pd.read_excel('test.xlsx', header=[0,1], index_col=[0,1])

In [11]: df
Out[11]: 
col1    foo    bar   
col2    a  b   a  b
i1 i2              
j  l    1  2   3  4
   k    5  6   7  8
```


Labels:
name: API Design
name: IO Excel
description: read_excel, to_excel
name: MultiIndex

Comments:
So my "non-breaking" change (always trying to parse index names) does have a corner case, if all values in the first row of the DataFrame are missing.

I could change that back easily enough, but I thought it may make sense to slightly change the default  output format of `to_excel` to remove this ambiguity and further match the `to_csv` format.  

What I would propose is if the index has a name, it is placed at the column level, as shown below.  This is also a much easier format to work with in Excel - at least in my workflow, I usually end up manually reshaping the data to look like this anyways.

Thoughts? @jreback @jorisvandenbossche 

### Current

![current](https://cloud.githubusercontent.com/assets/1924092/9630718/b8d76cf2-5141-11e5-9f7e-3a55b6ed91f8.png)

### Proposed

![proposed](https://cloud.githubusercontent.com/assets/1924092/9630722/bf0ff81e-5141-11e5-8b5d-971bcff491ec.png)

can u show a picture when the index does not have a name (in current and proposed) - iow is the blank line their?

If there isn't a name, both current and proposed have no blank line, like this:
![image](https://cloud.githubusercontent.com/assets/1924092/9650219/bda7b7f8-51c5-11e5-8f13-66169bb9c3fa.png)

@chris-b1 this change seems reasonable. let me open for a few comments

cc @jtratner 
cc @hayd 
cc @cancan101 
cc @flamingbear 
cc @onesandzeroes  

Thanks, I'll note my current branch still has a couple failing edge cases around mixes of names/no names (more tests coming), but those are fixable.  Just to restate the goal clearly:
- Any output of `to_excel` can be read with `read_excel` by specifying only `index_col` and `header`
- Deprecate `has_index_names`
- Generally match `to/from_csv` semantics/format

@chris-b1 awsome!

Alright, my latest commit fully represents the new behavior.

I'll note that this does pick up two quirks from the base parser
1. In the case of column `MultiIndex` a blank row ALWAYS has to be inserted so the format is unambiguous.  I believe this is unavoidable (csv does it too) - but the output looks a little odd if the index doesn't have names. xref #6618, picture below.
2.  In the case of a index `MultiIndex` without names, the level names will be read back in as `[None, "Unnamed 1:", Unnamed 2:", etc]`  This is in the base parsing logic (i.e. also happens with csv) so I think it's outside the scope of this PR, added issue #10984

![image](https://cloud.githubusercontent.com/assets/1924092/9673598/8c998744-526b-11e5-8558-3802e412737a.png)

In principle +1 on the change. (and your goals sound very good)

If the columns have a name (`df.columns.name`), then the current behaviour is kept, I assume? (EDIT: this is still about your question a bit higher https://github.com/pydata/pandas/pull/10967#issuecomment-137051139, but, I just noted that `to_csv` ignores the columns name in such a case (if it is not a Multi-Indexed columns))

About your points 1 above, do you think the blank row is unavoidable? Given #6618 it seems we would want to change this for csv, and if that is the case, it would make sense to do the change here already.

@jorisvandenbossche - column names in the single index case are ignored, just like csv.  The old export worked that way too.

For the blank row, this is the ambiguous case if you don't have it (or another kwarg).  Is "a" the index name, or is the first row of data all missing?

![image](https://cloud.githubusercontent.com/assets/1924092/9682823/d3a77dee-52cf-11e5-943d-dd4fab7af48f.png)

Good point. But `read_csv` at the moment interprets it as an empty row? So that seems not consistent with the output? How do you roundtrip that correctly?

You roundtrip by assuming 'a' is the index name, (because there would be an all blank row if it wasn't), which is what csv does too.

```
In [199]: df = pd.read_csv(
    ...: StringIO(""",foo,foo,bar,bar
    ...: ,a,b,a,b,
    ...: a,,,,
    ...: b,1,2,3,4
    ...: a,5,6,7,8"""), index_col=0, header=[0,1])

In [200]: df.index
Out[200]: Index([u'b', u'a'], dtype='object', name=u'a'
```

@chris-b1 yep this is the convoluted logic in `read_csv` to handle this (as well as the case where no blank line exists).

@chris-b1 yes, indeed, I forgot the `index_col=0` in my test case ..

Excellent, should probably get rid of warnings and docs I put in for  #10564.

Let me see if I can get @chris-b1 a PR on your repo.

@flamingbear, I think I've got them cleaned up, but if I missed anything, definitely appreciate a PR.  I'm going to push more changes in a few minutes, so I would wait just a second before looking.

@jreback - cleaned up the things you noted and rebased on the new testing code.

gr8. travis is borking atm. for some reason not tagging the versions correctly.....so may fail :<

I couldn't see how to mention you @jreback on this pr https://github.com/chris-b1/pandas/pull/2  Seems reasonable if we're not warning anymore because round trips are ok?  

@flamingbear - I didn't realize the verbose keyword was only used for that warning, I'll merge it in.  Thanks!

@chris-b1 travis is fixed, can you rebase and update

@jreback - rebased

pls squash as well

@jreback  - this is squashed.  Open to suggestions on how to make the backwards compat cleaner (see note above) - having to pass a deprecated argument is a bit awkward.

@jreback - pushed changes addressing your latest notes

minor doc fixes. ping when pushed (as only docs its already green)

@jreback - doc changes pushed, thanks.

some minor comments. ping when green (travis is way behind, FYI)

@jreback - green.  I went ahead and changed the docstring of `to_excel` and `parse` to a common template.

@chris-b1 nice change!