ENH: Better handling of MultiIndex with Excel

[jtratner]

I'm going to put something together this weekend, even if I can only make it work with one engine. The current MI output for columns is too hard to use. Would be really nice to round-trip it, but I'm less concerned about that.

@jmcnamara if you have any tips they'd certainly be appreciated - I may only do it for Xlsxwriter this weekend. (existing MI summary form can judge where to put whitespace to show that one index covers multiple values. Just need to do the same thing but rather than blank cells, should be a merged cell).

@cancan101 were you interested in this too?

[jmcnamara]

I can sketch something out about how to handle it or even just write it as a preliminary PR.

To get started could you give me a couple of small DataFrame examples with MultiIndexes that the Excel writers would be required to handle.

Maybe something a little simpler that the examples in the docs to start begin.

[jtratner]

In [3]: df = pd.DataFrame(zip([0] * 6, [1] * 6, [2] * 6, [3] * 6, [4] * 6), columns=pd.MultiIndex.from_arrays([['Foo', 'Foo', 'Bar', 'Bar', 'Baz'], ['A', 'B', 'C', 'D', 'E']])
)

In [4]: df
Out[4]:
   Foo     Bar     Baz
     A  B    C  D    E
0    0  1    2  3    4
1    0  1    2  3    4
2    0  1    2  3    4
3    0  1    2  3    4
4    0  1    2  3    4
5    0  1    2  3    4

Here's what the Excel File should look like:

There's something in core/format that I believe handles this (or maybe it's on MI), so I think it should be trivial to convert an MI into a set of spans. (which I think is what the excel writer needs anyways).

[jtratner]

I'm less clear on MI index (as opposed to columns) and I'm inclined to not mess with that for now.

[jtratner]

Here's what it does currently (dots indicate level of nesting).

[jtratner]

I think it's as simple as passing sparsify=True to it's formatter and looking for empty cols.

[cancan101]

@jtratner Yes: #4679 and #4468

[jtratner]

Example 2:

In [7]: df = pd.DataFrame([list('abcde'), list('fghij'), list('klmno')])

In [8]: df
Out[8]:
   0  1  2  3  4
0  a  b  c  d  e
1  f  g  h  i  j
2  k  l  m  n  o

In [11]: df.columns = pd.MultiIndex.from_arrays([['A', 'A', 'A', 'C', 'C'], ['D', 'E', 'E', 'H', 'I'],  ['J', 'L', 'M', 'N', 'N']])

In [12]: df
Out[12]:
   A        C
   D  E     H  I
   J  L  M  N  N
0  a  b  c  d  e
1  f  g  h  i  j
2  k  l  m  n  o

[jtratner]

oops, need to make that last 3 level

[jtratner]

@cancan101 yeah, that's the reverse of this, still useful tho!

[jmcnamara]

I can have a look at that. Probably not before Sunday though.

[jreback]

you guys going to try to throw this in? (no problem with it, esp if its experimental...so can even do after rc1)...

[jtratner]

I really want this, even if it means stepping through code for hours. Especially if we can get it on the reading side.

[jreback]

haha!

np

[jmcnamara]

I had a preliminary look at the Excel writer merge code.

The merge mechanisms in the Excel writers will probably work without modification if the cells ranges for the merges are identified correctly. However, that part is completely missing (you probably already knew that, I'm just catching up).

Of the existing writers the to_html() writer seems to get it's output right in a comprehensive way so I'm trying to steal some ideas from that.