How to apply Filter in header row with pandas

Question

Let say I have dataframe like this:

     col1    col2          col3
0    A        A_1          pass
1    A        A_2          pass
2    A        A_1          fail
3    A        A_1          fail
4    A        A_1          pass
5    A        A_2          fail

I want to save this dataframe to an excel file and apply Filter in the header "col1" "col2" "col3", so when I open the excel file the filter should be already applied in header.

I use this line to save it as an excel file but not with Filter applied:

df.to_excel('data.xlsx',index=False)

How can I do this?

Answer 1

Try this:

import pandas as pd
from openpyxl import load_workbook

path = r'D:\temp\you.xlsx'
df.to_excel(path,index=False)

wb = load_workbook(path)
ws = wb.active
ws.auto_filter.ref = ws.dimensions
wb.save(path)
wb.close()