ERR: raise on mutli-index columns as its not implemented in to_excel

[ghost]

Try to the following code, and you will find column labels mismatched with the data in the Excel file :

cols = pd.MultiIndex.from_tuples([('site',''),('2014','height'),('2014','weight')])
df = pd.DataFrame(np.random.randn(10,3), columns=cols)
df.to_excel('test.xlsx', index=False)

I am using pandas v0.16.0.

[jreback]

this is not implemented for a column multi-index, see #4679
for either reading or writing

So, going to change this issue to one for error reporting to raise until this is implemented.

[hsperr]

I'm starting on this one now

[sidoki]

Hi guys,

i'm just asking about when pandas will support exporting excel with multiindex column dataframe? is it already included for the next sprint?
thanks

[shoyer]

@dyngts This hasn't been implemented yet and I don't think it's on anyone's immediate agenda -- pandas is all volunteers. But we're certainly open to pull requests if you're interested in making a contribution.

[onesandzeroes]

Just ran into this after upgrading to v0.16.1 from v0.15.2. I have a bunch of multi-indexed df's being written to Excel in some analyses I'm doing, as far as I know they've all been working fine in v0.15.2. The misalignment described here only seems to happen when you set index=False, doesn't it? Removing that arg from OP's example code produces sensible output.
I realize that reading of multi-index columns doesn't work so you can't roundtrip, but it seems like overkill to disable writes completely if it's only the index=False case that fails.

[onesandzeroes]

OK, I've just hacked my v0.16.1 install so that it raises if self.columns.nlevels > 1 and not index instead of just if self.columns.nlevels > 1. My dataframes continue to be written successfully to Excel with nicely merged cells in the column headers, and no misalignment of data. Happy to submit a PR to re-enable excel writes for the index=True case if people are OK with this.

[jreback]

@onesandzeroes these are not readable back in, correct? (as we don't support reading the multi-line headers ATM). So this is pretty hard to tests. That said, I suppose if index=True works, then it should be able to write these.

[RobinFiveWords]

Would someone mind explaining why 0.15.2 was able to handle this but 0.16.x can't yet? This is my first time trying to look under the hood at how pandas does what it does. I think I understand what the error is, but I think I would understand so much more if I could explain why it was a non-issue before.
Edit: It's not as pretty but df.T.reset_index().T.to_excel(...) is the workaround I'm using.

[flamingbear]

Can we have @onesandzeroes solution? I've done the same, but I can't do this on my production machines.

[jreback]

issue at #10564 though I think we need a argument to allow (so it can be turned on/off)

[Majeed-Sahebzadha]

+1 would be useful to have.