How add new colum in existing excel using xlsx writer in python

Question

I need to add a new column in my existing excel sheet which is .xlsx using Excel writer in python.

Name |sub1 |sub2 |sub3.
Ram. |10. |20. |30.
Raja.| 11. | 22. | 33

I need to added new columns for TOTAL and AVERAGE and need to calculate it and show the output in .xlsx file.

Need to do in Excel Writer

score 1 · Accepted Answer · answered Apr 02 '21 at 11:13

You can save the data into a xlsx file and use pandas to do the calculations like:

import pandas as pd 

df = pd.read_excel("test.xlsx")

total = df.sum(axis=1)  #sums all cells in row
average = df.mean(axis=1) #averages all cells in row

df.insert(loc = 4 , column = "Total", value = total )  #inserting sum to dataframe
df.insert(loc = 5 , column = "Average", value = average ) #inserting avg to dataframe

writer = pd.ExcelWriter("test.xlsx")
df.to_excel(writer,"Sheet1")    #Saving to df
writer.save()

You could also use df.to_excel("test.xlsx") to shorten the writing steps

How add new colum in existing excel using xlsx writer in python

1 Answers1