Pandas exporting to Excel (xls, xlsx) with multilevel columns

[dinya]

Hello all,

I want collect few tables into the one with pandas. I use the code presented below:

import pandas as pd
import itertools
import types

df = None
for frame in ['x', 'y']:
    df_ = pd.read_excel(u'%s.xlsx' % frame)
    df_ = df_.set_index([u'time'])

    parameters = list(df_.columns)
    tuples = []
    for tup in itertools.product([frame,], parameters):
        tuples.append(tup)

    columns = pd.MultiIndex.from_tuples(tuples, names=[u'Frames',u'Parameters'])

    df_new = pd.DataFrame(columns=columns, index=df_.index)
    for par in parameters:
        df_new[frame, par] = df_[par]
    del df_

    if df is None:
        df = df_new
    else:
        df = pd.concat([df, df_new], axis=1)

df.to_excel('merged_xlsx.xlsx')
df.to_excel('merged_xls.xls')

Source data is x.xlsx

and y.xlsx files.

XLS engine works well (merged_xls.xls):

But something is wrong (cells merging) with XLSX engine (merged_xlsx.xlsx):

Manual cells unmerging works in Excel:

Is it bug in XLSX pandas engine (openpyxl)? Or what is wrong in my code?

Versions: python-2.7.10, pandas-0.17.0, openpyxl-2.3.0.

P.S. This issue is copy of my question on stackoverflow. It was suggested as bug and was adviced to post here.

[jorisvandenbossche]

@dinya Thanks for the report!

It is always easier if you provide an easier reproducible example (as we cannot run your code above), but I made a small one:

df = pd.DataFrame(np.random.randn(5, 6), 
                  columns=pd.MultiIndex.from_product([['x', 'y'], ['a', 'b', 'c']], names=[u'Frames',u'Parameters']), 
                  index=pd.Index(range(5), name='time'))
df.to_excel('test.xls')
df.to_excel('test.xlsx', engine='xlsxwriter')
df.to_excel('test2.xlsx', engine='openpyxl')

Can you see if this reproduces your issue?

I can't reproduce it with 0.17.0, but I was using openpyxl 1.6.1

[dinya]

@jorisvandenbossche,
Ok, thanks for advice, I'll be more careful next time

I run your code and get the following:

• test.xls looks ok
• test.xlsx looks ok (I get xlsxwriter==0.7.7 from pip)
• test2.xlsx looks the same bad with cells merging troubles

[jorisvandenbossche]

Yes, I can confirm it with a more recent version of openpyxl

[jorisvandenbossche]

cc @chris-b1 another multi-index one

[Dr-Irv]

I just tested this with Python 3.4.3, pandas 0.17.0, xlsxwriter 0.7.3, and openpyxl 1.8.5 and cannot replicate. I tested with Python 3.4.3, a dev copy of pandas, xlsxwriter 0.7.6, and openpyxl 2.0.2 and cannot replicate. I tested with Python 2.7.10, a dev copy of pandas, xlsxwriter 0.7.6, and openpyxl 1.6.2, and cannot replicate.

However, I tested with Python 2.7.10, a dev copy of pandas, xlsxwriter 0.7.6, and openpyxl 2.3, and I do replicate.

I wonder it this relates to my pull request and different behaviors based on python/openpyxl combinations detailed here (scroll to the bottom): #11328

@chris-b1 would like your opinion

[Dr-Irv]

I found the bug in pandas/io/excel.py:_Openpyxl22Writer.write_cells and have fixed it in my branch that is in the pull request #11328 . So if one of you guys (@chris-b1, @jreback ) will answer my question there, I'll do a commit and fire off the tests and this bug can be put to rest.

[Dr-Irv]

@dinya If you use a version of openpyxl earlier than 2.2, then your problem will disappear with pandas 0.17.0 .

[jreback]

closed by #11328