BUG: DataFrame.to_excel(index=False) misplaces data/columns in MultiIndex case

[aldanor]

Example:

>>> df = pd.DataFrame({'a': [10, 20], 'b': [30, 40], 'c': [50, 60]}, 
...                   index=pd.MultiIndex.from_tuples([(70, 80), (90, 100)]))
>>> writer = pd.ExcelWriter('test.xls')
>>> df.to_excel(writer, sheet_name='1', index=False)
>>> writer.save()
>>> pd.read_excel('test.xls', '1')
        a   b   c
10 30  50 NaN NaN
20 40  60 NaN NaN

The column names are obviously misplaced (erroneously shifted to the right by the number of levels in the MultiIndex). Or is it the data that is misplaced? Wonder why no one noticed this before.

[jtratner]

Thanks for the bug report. Can you post what version of pandas and xlwt you
are using?
On Nov 29, 2013 11:50 AM, "Ivan Smirnov" [email protected] wrote:

Example:

df = pd.DataFrame({'a': [1, 2], 'b': [3, 4], 'c': [5, 6]}, ... index=pd.MultiIndex.from_tuples([(7, 8), (9, 10)]))>>> writer = pd.ExcelWriter('test.xls')>>> df.to_excel(writer, sheet_name='1', index=False)>>> writer.save()>>> pd.read_excel('test.xls', '1')
a b c1 3 5 NaN NaN2 4 6 NaN NaN

The column names are obviously misplaced (erroneously shifted to the right
by the number of levels in the MultiIndex). Or is it the data that is
misplaced?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/5616
.

[aldanor]

@jtratner it's xlwt==0.7.5 and pandas is fetched from git head about a week ago (btw it registers as pandas==0.0.0 on my machine since it's behind a corporate proxy/firewall and git fails to retrieve a proper version... oh well, guess that's another bug).

[jtratner]

Can you run git describe in the pandas directory and post what you see?
Doesn't really make sense to me that it comes out as 0.0.0 - proxy
shouldn't have anything to do with it, but not a big deal.

Regardless, definitely a bug that needs to be fixed, but I'm guessing it
won't be that difficult.

[aldanor]

@jtratner Well, that's the thing - I cannot access git due to SSL restrictions, proxies etc on a remote box at work, hence I had to download a zip of the master branch. Obviously then, git describe returns a fatal error since it's not a git repo in the first place, and pandas ended up installing itself to /site-packages/pandas-0.0.0-py2.7-linux-x86_64.egg/pandas. Bottom line is, is pandas (dev) versioning supposed to be so tightly coupled to git?

[jtratner]

it's supposed to fall back on using the hard-coded major/minor version.
Anyways, not a big deal, going to take a look when I can.

On Mon, Dec 2, 2013 at 12:09 PM, Ivan Smirnov [email protected]:

@jtratner https://github.com/jtratner Well, that's the thing - I cannot
access git due to SSL restrictions, proxies etc on a remote box at work,
hence I had to download a zip of the master branch. Obviously then, git
describe returns a fatal error since it's not a git repo in the first
place, and pandas ended up installing itself to
/site-packages/pandas-0.0.0-py2.7-linux-x86_64.egg/pandas. Bottom line
is, is pandas versioning supposed to be so tightly coupled to git?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/5616#issuecomment-29636624
.

[jmcnamara]

Adding a comment to track this issue.

[jmcnamara]

Mea culpa. My Excel multi-index changes introduced this bug.

I've pushed a fix and test.

[jmcnamara]

Output after fix:

>>> import pandas as pd
>>>
>>> df = pd.DataFrame({'a': [10, 20], 'b': [30, 40], 'c': [50, 60]},
...                   index=pd.MultiIndex.from_tuples([(70, 80), (90, 100)]))
>>> writer = pd.ExcelWriter('test.xls')
>>> df.to_excel(writer, sheet_name='1', index=False)
>>> writer.save()
>>>
>>> pd.read_excel('test.xls', '1')
    a   b   c
0  10  30  50
1  20  40  60