Unable to print multiindex dataframe to excel with merged cells

Question

I have a dataframe df which looks as following:

Date        ConstraintType  Col1    Col2
2020-07-15  N-S             w1      521133
2020-07-15  N-S             w2      550260
2020-07-15  CSD             d1      522417
2020-07-15  CSD             d2      534542
2020-07-15  A               d4      534905
2020-07-15  B               d5      534904

The index of dataframe is:

df.index
Out[6]: 
MultiIndex([('2020-07-15',  'N-S'),
            ('2020-07-15',  'N-S'),
            ('2020-07-15',  'CSD'),
            ('2020-07-15',  'CSD'),
            ('2020-07-15', 'A'),
            ('2020-07-15', 'B')],
           names=['Date', 'ConstraintType'])

But when I print it to excel it appears as following:

I was expecting the following:

I am using the following code:

df.to_excel(r'C:\Users\ram\Desktop\z1.xlsx', merge_cells=True)

Answer 1

From the provided DataFrame:

1. Use .reset_index() to remove the index columns from the index
2. Use where, and .shift to make all of the cell values blank except for the first occurrence of those values in the columns Date and ConstraintType.
3. Finally, use .set_index to put them back on the index, this time with only one unrepeated value and write to_excel. Now, merge_cells=True should work.

code:

df=df.reset_index()
df['Date'] = df['Date'].where(df['Date'] != df['Date'].shift(), '')
df['ConstraintType'] = df['ConstraintType'].where(df['ConstraintType'] != df['ConstraintType'].shift(), '')
df = df.set_index(['Date', 'ConstraintType'])
df.to_excel(r'C:\Users\ram\Desktop\z1.xlsx', merge_cells=True)

excel output: