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

0 votes

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
Jan 24, 2023 in Others by Kithuzzz
• 38,000 points
1,378 views

1 answer to this question.

+1 vote

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 :

enter image description here

answered Jan 24, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Sum the total of a column in excel and paste the sum to a different workbook

Get Column Sum From Closed Workbook Option Explicit Sub ...READ MORE

answered Jan 26, 2023 in Others by narikkadan
• 63,600 points
596 views
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,600 points
942 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,000 points
1,798 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,466 views
0 votes
1 answer
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,600 points
2,594 views
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,600 points
2,361 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP