Writing A Multi-index Dataframe To Excel File

The DataFrame MultiIndex is kicking my butt. After struggling for quite a while, I was able to create a MutliIndex DataFrame with this code columns = pd.MultiIndex.from_tuples([('Z

Solution 1:

This was a bug that will be fixed in version 0.17.1, or you can use engine='xlsxwriter'

https://github.com/pydata/pandas/pull/11328

Solution 2:

This is a great use for itertools.product. Try this instead in your multiindex creation:

from itertools importproductcols= product(
    ['All Properties', '3 Bedroom', '2 Bedroom', '1 Bedroom'],
    ['Avg List Price', 'Median List Price']
)
columns = pd.MultiIndex.from_tuples(list(cols))
ind = pd.Index(['11111'], name='zip')
vals = ['Val1', 'Val2', 'Val3', 'Val4', 'Val5', 'Val6', 'Val7', 'Val8']
df = pd.DataFrame(
    vals, index=ind, columns=columns
)

The issue is: you included zip (which names your index) in the construction of your MultiIndex for your columns (tragically, nothing called MultiColumns exists to clear up that confusion). You need to create your Index (which is a single-level normal pandas.Index) and your columns (which are a two-level pandas.MultiIndex) separately, as above, and you should get expected behavior when you write to excel.