'Unsparsify' a pandas multi-index when writing to Excel

Question

I have a pandas dataframe with a multi index, by default when printing to the screen it will "sparsify" the output so that higher levels of the index are not repeated. Eg:

Sparse:

I can change this to "unsparse" as follows:

However, this option is not honoured by df.to_excel(writer) which will always write the index as sparse with merged cells. Is there some way to make this write to excel in the "unsparse" way? Alternatively I can write to a csv and import it into excel, as the csv is always "unsparse", but that is a little annoying.

Answer 1

Try to apply reset_index() before writing to excel.

An example :

first  second
bar    one      -0.008620
       two       1.688653
baz    one      -0.145099
       two       0.870981
foo    one       2.544494
       two       0.935468
qux    one      -1.868521
       two      -0.118242

print(s.reset_index())

  first second         0
0   bar    one -0.008620
1   bar    two  1.688653
2   baz    one -0.145099
3   baz    two  0.870981
4   foo    one  2.544494
5   foo    two  0.935468
6   qux    one -1.868521
7   qux    two -0.118242