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 in Others by Kithuzzz
• 34,260 points
115 views

1 answer to this question.

0 votes

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 by narikkadan
• 59,740 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 in Others by narikkadan
• 59,740 points
93 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 in Others by narikkadan
• 59,740 points
78 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 5 days ago in Others by Kithuzzz
• 34,260 points
32 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 3,455 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 in Others by narikkadan
• 59,740 points
335 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
• 59,740 points
580 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