CODEWITHSUNDEEP
pythonexcelpandas
phil_20686
phil_20686

Reputation: 4080

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

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:

enter image description here

I can change this to "unsparse" as follows: enter image description here

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.

Upvotes: 9

Views: 6736

Answers (2)

mfastudillo
mfastudillo

Reputation: 1631

Now you can specify merge_cells=False

an example:

arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']]

df=pd.DataFrame(np.random.randn(8, 4), index=arrays)

df.to_excel('example.xlsx',merge_cells=False)

Upvotes: 8

DavidK
DavidK

Reputation: 2564

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

Upvotes: 11

Related Questions