Keeping the Excel drop down list through pandas

Question

I have an excel file where the headers have drop downs which can be used to select rows based on specific column values (exactly what WHERE statement does). I import this file into pandas and perform some operation. Let's say I drop duplicate values based on "emp_id" column"

data = data.drop_duplicates(['emp_id'])

Then I save this dataframe to an excel,

data.to_excel("new_data.xlsx")

However, this new data does not have any drop down on header. Is there way to retain the drop down or python/pandas does not support it?

Answer 1

If I understand you correctly, this can be done quite easily with XlsxWriter:

import pandas as pd

df = pd.DataFrame({
    'Numbers': [1, 2, 3, 4, 5],
    'Letters': ['a', 'b', 'c', 'd', 'e']
})

with pd.ExcelWriter('new_data.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(excel_writer=writer, sheet_name='Filter', index=False)

    worksheet = writer.sheets['Filter']

    # set up autofilter
    worksheet.autofilter(0, 0, len(df.index) - 1, len(df.columns) - 1)