0

I have a dataframe of this format

enter image description here

When I export it to excel and use filters on the basis of SITE-ID, it will only show the first row of that group. For example

enter image description here

I do understand, that's how excel works and there are alternatives to this from within excel. But is there something I could do to solve this problem from within pandas when it exports to excel so that when I filter on Site-ID, I will see everything under that Site-ID? I don't want to do any VBA or macros for this.

Thanks

Community
  • 1
  • 1
Gurpreet Kochar
  • 43
  • 1
  • 8
  • Filter and merged cells don't work together. You'll need each data element repeated row-by-row. – BigBen Jan 22 '20 at 15:28

1 Answers1

0

If you are agree to lose the merge, you can try

df.to_excel('filename', merge_cells=False)

Else, You can try to reset_index of your data frame before exporting.

df.reset_index(inplace=True)
df.to_excel('filename', index=False )

Or you want to keep others columns merged (because you will not use filters you can reset only index on site-id:

df.reset_index('SITE-ID', inplace=True)
df.to_excel('filename' )

Else you have the only solution is the workaround: Excel filtering for merged cells But I don't think that you are agree with that

Renaud
  • 2,709
  • 2
  • 9
  • 24
  • I have tried doing all of the above solutions already before posting but the there is an issue with every method. 1. removes merge that i want to keep 2. doesn't give me the nicely formatted output as merge gives 3. This is what i want but i want the site-id column to be the first one. Is there a way you can append a column in front of index columns so that index columns preserve the formatting but still able to add "Site-ID" as first column as a non index column. Or even if site-id is an index column, i don't want it to get merged but the following columns should still retain merge? – Gurpreet Kochar Jan 22 '20 at 19:38
  • 1
    I fortunately, I don't think than it is possible to put a non index column before indexes. The best I can submit you is to create a normal column which is a copy of SITE-ID index with: df.assign(SITE-ID_copy=df.index.get_level_values('SITE-ID')) – Renaud Jan 22 '20 at 21:46