How can I add a column to an existing excel file using python?
here is my code:
import openpyxl, pprint
wb = openpyxl.load_workbook('/Users/sarahporgess/Desktop/SSA1.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
data = {}
for row in range(1,sheet.max_row+1):
date = sheet['A' +str(row)].value
gamma = sheet['B' +str(row)].value
theta = sheet['C' +str(row)].value
ratio = float(gamma)/float(theta)
resultFile = open('SSA2.csv' , 'w')
resultFile.write( pprint.pformat(date))
resultFile.write( pprint.pformat(gamma))
resultFile.write( pprint.pformat(theta))
resultFile.write( pprint.pformat(ratio))
print(ratio)
sheet['D1']=ratio
resultFile.close()
print('Done.')
my existing excel file currently has three columns: "date, gamma, theta". I want to add a fourth column called "ratio" which is the gamma / theta ratio. How can I add another column to an existing excel document using python? This code creates an excel document with 4 items printed in one cell
+3
source to share
3 answers
Easier to use the Pandas package
import pandas as pd
file_name = #Path to your file
df = pd.read_excel(file_name) #Read Excel file as a DataFrame
df['Ratio'] = df['Gamma']/df['Theta']
#Display top 5 rows to check if everything looks good
df.head(5)
#To save it back as Excel
df.to_excel("path to save") #Write DateFrame back as Excel file
0
source to share
From your updated question, I rewrote my answer.
You don't need to use another library to accomplish what you are trying to do. Here's another option for doing what you want.
import openpyxl
import pprint
wb = openpyxl.load_workbook('/Users/sarahporgess/Desktop/SSA1.xlsx')
sheet = wb.active
# you may also use the wb.get_sheet_by_name('Sheet1') method here.
data = {}
for row in range(1,sheet.max_row+1):
date = sheet.cell(row = row, column = 1) # use .cell() to get values of cells
gamma = sheet.cell(row = row, column = 2)
theta = sheet.cell(row = row, column = 3)
print(date, gamma, theta)
ratio = float(gamma)/float(theta)
new_wb = openpyxl.Workbook() # creates new workbook to be saved as results
# you can also open a wookbook here instead but I wrote it to create a results workbook as I didnt already have one.
new_sheet = new_wb.active
new_sheet['A1'] = pprint.pformat(date)
new_sheet['B1'] = pprint.pformat(gamma)
new_sheet['C1'] = pprint.pformat(theta)
new_sheet['D1'] = pprint.pformat(ratio)
print(ratio)
# save new workbook as SSA2
new_wb.save('/Users/sarahporgess/Desktop/SSA2.xlsx')
print('Done.')
+2
source to share
It is not clear from your code whether you want to print the result or edit an existing file. If you are editing an Excel file, you may need to create a formula and let Excel do the calculation for you.
import openpyxl
wb = openpyxl.load_workbook('/Users/sarahporgess/Desktop/SSA1.xlsx')
sheet = wb['Sheet1']
for row in sheet:
date, gamma, theta = row
ratio = theta.offset(column=1)
ratio.value = "=B{0}/C{0}".format(theta.row) # if you want the formula
# ratio.value = gamma/theta # if you just want the calculation
wb.save(β¦)
+1
source to share