ENH: Better handling of MultiIndex with Excel
State: closed

Allow optional formatting of MultiIndex and Hierarchical Rows
as merged cells. closes #5254.

Some notes on this PR:
- The required xlsxwriter version was up-revved in `ci/requirements*.txt`to pick up a fix in that module that makes working with charts from pandas easier.
- Added a comment to `release.rst`.
- Modified `format.py` to format MultiIndex and Hierarchical Rows as merged cells in Excel. The old code path/option is still available. The new formatting must be explicitly invoked via the `merge_cells` option in `to_excel`:

``` python
df.to_excel('file.xlsx', merge_cells=True)
```
- The `merge_cells` option can be renamed if required. During development I also used `multi_index` and `merge_range`.
- Updated the API and docs in `frame.py` to reflect the new option.
- Fixed openpyxl merge handling in `excel.py`.
- Modified the `test_excel.py` test cases so that they could be used to test the existing `dot notation` or the new `merge_cells` options for MultiIndex and Hierarchical Rows handling.
- Did some minor PEP8 formatting to the `test_excel.py`.


Labels:

Comments:
I added your notes to the top of the PR for ease of review.

so it still can't read in the (simpler) merged cells versions, right? Or can it?

> I added your notes to the top of the PR for ease of review.

Okay, thanks. I'm never sure if that gets converted to a commit message on merge or not. I'm guessing it doesn't?

> so it still can't read in the (simpler) merged cells versions, right? Or can it?

Yes, it can still read the old dot format. It can also read the new merge format for simple single row MIs.

so then it's not even necessary to have the keyword argument, right? Or we could at least flip it to be the other direction? (default merge_cells, existing behavior under kwarg)?

Previously it couldn't even roundtrip an MI...

> Previously it couldn't even roundtrip an MI...

Hmm, perhaps I'm wrong then. I thought that is what the existing tests with `mulitiindex` in the name were doing that. But maybe not. The tests are like this:

``` python
import pandas as pd

df1 = pd.DataFrame([[7] *3, [8] *3, [9] *3])
df1.index.name = 'Foo'

df1.to_excel('merge30.xlsx', merge_cells=True)

xf = pd.ExcelFile('merge30.xlsx')
df2 = xf.parse(xf.sheet_names[0], has_index_names=True)

df1 == df2

>>> df1 == df2
        0     1     2
Foo
0    True  True  True
1    True  True  True
2    True  True  True

```

So that isn't round-tripping the MI, just the index names.

Check out roundtrip in hemstring (the test that's actually checking for MI-things):

``` python
        def roundtrip(df, header=True, parser_hdr=0):

            with ensure_clean(self.ext) as path:
                df.to_excel(path, header=header)
                xf = pd.ExcelFile(path)
                res = xf.parse(xf.sheet_names[0], header=parser_hdr)
                return res

```

doesn't even check that it's reading it in the same way, and later it just checks that none of it is nan.

bottom line is that this behavior wasn't well-specified before, so if `read_excel` can now always read in `to_excel`'s output we should go with that by default. (and maybe just warn that it will strip out MI column names?)

Rebased Excel MultiIndex PR to the latest master and fixed conflicts.

Why do you need to use `__getattribute__` and `__setattr__` explicitly? Surprised to see that? 

> Why do you need to use **getattribute** and **setattr** explicitly? Surprised to see that? 

``` python
                           xcell = wks.cell("%s%s" % (colletter, row))
                           for field in style.__fields__:
                               xcell.style.__setattr__(field, \
                                   style.__getattribute__(field))
```

Yes, that is kind of janky. I copied that from the existing code above those lines. You would imagine that it should just be a function like:

``` python
    xcell.style = _convert_cell_style(cell.style)
```

I might look at refactoring the style/format handling as a separate PR in the 1.4 milestone and re-enable the commented out testcase for cell formats in test_`excel.py`. It probably isn't worth it for this PR though.

Ah okay, I get why you'd want to match nearby code.

If if need be there is always the functions `getattr` and `setattr`.

This looks fine overall - I think the default should be merge_cells=True though.

I think it would be nice to have an example of this in io.rst (and maybe fix up the excel section a bit to have a bit more organization, and several sub-headings). could do in this PR or another, and can use the same example in v0.13.0

> I think the default should be merge_cells=True though.

Ok. I'll change that around.

In terms of docs, I think you could take one of the straightforward examples from the issue and use that in the docs to demonstrate what's going on (and then just add a `.. note::` that it may not always be possible to effectively round trip DataFrames with named Index and named columns).

> I think it would be nice to have an example of this in io.rst (and maybe fix up the excel section a bit to have a bit more organization, and several sub-headings). could do in this PR or another, and can use the same example in v0.13.0

If you like, I could write a document section on Excel handling from Pandas: how to read and write files, explanations of some of the options with some examples and screenshots.

That would be better as a separate PR though.

Also, here is a related document that I've been working on for after the 0.13 release: [Using Pandas and XlsxWriter to create Excel charts](http://pandas-xlsxwriter-charts.readthedocs.org/en/latest/index.html)

okay, so is this ready to go? (and then I'll open an issue about docs)

btw - those docs look nice - when you say that you can use xlsxwriter to create excel charts, do you mean that you can create charts that you can open and edit in Excel?

Yes. PR is ready to go. 

And yes the charts are in Excel rather than, say, PNGs produced by matplotlib. Give it a try. There are some examples in the latter part. It would be good to get some feedback. 

That is really amazing... I would like to get to that at some point (going to PyData this weekend but maybe next weekend or something). That would be _so_ useful at work.  Anyways, that's off topic.

thanks for this!

@jmcnamara just tested this out, I get hierarchical rows from this example, but a phantom nan - can you take a look at some point?

this code generates the screenshot below:

``` python
In [1]: import pandas as pd

In [2]: df = pd.DataFrame({"A": range(10)}, index=[['a', 'a', 'a', 'a', 'b', 'b', 'b', 'c', 'c', 'c'], [1, 2, 3, 4, 1, 2, 3, 1, 2, 3]])

In [3]: df.to_excel('output.xlsx')

In [7]: df
Out[7]:
     A
a 1  0
  2  1
  3  2
  4  3
b 1  4
  2  5
  3  6
c 1  7
  2  8
  3  9
```

![screen shot 2013-11-05 at 9 01 16 pm](https://f.cloud.github.com/assets/1598379/1479562/5f56f2ea-4687-11e3-85fb-b888ac7ba5c8.png)

When I read it back in, I get this:

``` python
In [5]: pd.read_excel('output.xlsx', 'Sheet1')
Out[5]:
       A
a   1  0
NaN 2  1
    3  2
    4  3
b   1  4
NaN 2  5
    3  6
c   1  7
NaN 2  8
    3  9
```

``` python
In [8]: _5.index
Out[8]:
MultiIndex(levels=[[u'a', u'b', u'c'], [1, 2, 3, 4]],
           labels=[[0, -1, -1, -1, 1, -1, -1, 2, -1, -1], [0, 1, 2, 3, 0, 1, 2, 0, 1, 2]])
```

And that is specifically being read in as 'a', followed by 3 nans, then 'b', 2 nans, then 'c', and 2 nans.

To be clear, the MI should be:

``` python
MultiIndex(levels=[[u'a', u'b', u'c'], [1, 2, 3, 4]],
           labels=[[0, 0, 0, 0, 1, 1, 1, 2, 2, 2], [0, 1, 2, 3, 0, 1, 2, 0, 1, 2]])
```

>  I get hierarchical rows from this example, but a phantom nan - can you take a look at some point?

Hmm, that is a little odd. I seeing it on one test system but not another. I'll look into it.

I'm guessing it's a Mac excel thing?

My bad. I was on a branch that didn't have the MI patch merged.

So unfortunately, reading HRs in the new merged format is broken. It is fixable but it will need some work. I'll address it in the 0.14 timeframe.

Honestly, that's fine, it wasn't working before and MIs weren't
roundtripping correctly anyways. Would you mind adding a warning to the
docs?

If you get to a fix earlier, feel free to submit it...bugfixes are good.

On Wed, Nov 6, 2013 at 7:23 PM, Jeffrey Tratner
jeffrey.tratner@gmail.comwrote:

> Honestly, that's fine, it wasn't working before and MIs weren't
> roundtripping correctly anyways. Would you mind adding a warning to the
> docs?