Output python dataframe to excel and create a new data validation column in the exported excel sheet

Question

Context: I want to export a dataframe into an Excel spreadsheet. I want the Excel to have a separate, data-validated column in the workbook that only accepts values in the range 1 to 9 and is not included in the dataframe.

dataframe looks something like this:

name	year
trixie	1985
timmy	1990
chester	1993

I want the exported excel sheet to look like this: where the code column only allows a number between 1 and 9 (the excel data validation way) I want to do all of this in python.

name	year	code
trixie	1985
timmy	1990
chester	1993

Answer 1

Use pandas.ExcelWriter with worksheet.data_validation from xlswriter :

df["code"] = None
items = list(range(1,10))
max_row, max_col = df.shape

with pd.ExcelWriter("/tmp/file.xlsx") as writer:
    df.to_excel(writer, index=False, sheet_name="Sheet1", startrow=0)
    
    wb = writer.book
    ws = writer.sheets["Sheet1"]
    
    ws.data_validation(f"C2:C{str(max_row+2)}", {"validate": "list", "source": items})

Output :