9

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" that is the ratio of gamma/theta. How do I add another column to an existing excel document using python? This code creates an excel document with the 4 elements printed into one cell

Improve this question
1
  • is there a reason for SSA2.csv to be a csv and not xlsx? Commented Jun 28, 2017 at 20:33

3 Answers 3

Reset to default
15

It is 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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you so much! This is my first time seeing the code "df.to_excel("path to save")", can you explain this line please?
Here is what the code does: It reads the Excel file as a DataFrame, which is a data structure that represents tabular data. After reading the Excel File as a DataFrame, there is a lot of things that you can do, including transformations, joins, merges, mathematical operations etc. df.to_excel() simply writes the DataFrame back to disk as an Excel File
While Pandas is a great library it's not the best tool for editing Excel files.
2

From your updated question I have rewrote my answer.

You don't need to use another library to accomplish what you are trying to do. Here is another option for accomplishing 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.')
Improve this answer

2 Comments

typo! I meant column, Thanks though!
@SarahPorgess: I have updated my answer to use your current imports.
1

It's not clear from your code whether you want to print the result or edit the existing file. If you're editing an Excel file then you might want 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(…)
Improve this answer

8 Comments

he is adding the results to another file.
In which case the question is misleading.
Sometimes people are not able to articulate their questions well. That is why I review the code and from the question and the content of the code decide on what their question really is. This approach has proven more accurate than not.
Saving to another file is merely the filename you pass to the save() method. More important is to make as use of the openpyxl API for this kind of thing: no need to create a loop, no need to use pprint, use of the offset method.
I imaging the loop is there for multiple rows of date that need the same process done to them. I don't think they are only performing this function one one row of data. Also I agree pprint does not appear to be needed however because the OP is using pprint I answer with the use of pprint as well.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.