Create a new dataframe column by applying Excel formula using Python

Question

I have an excel spreadsheet which I am reading into a dataframe using pandas. I have a second excel file which contains some formulas which need to be applied on the first excel in order to create a new column. My dataframe of first excel looks somewhat like this:

RPA Rej    RPA Acc
  1          0
  5          3
  0          0

As per the second excel, I want to subtract the values of RPA Rej and RPA Acc and create a new column Total with their result and write the whole dataframe into an excel file, which I very much achieved by this:

df['Total'] = df['RPA Rej'] - df['RPA Acc']

and this:

df.to_excel('data.xlsx', index = False)

but I want to see the excel formula when I click on the values in Total column , something like this:

I don't know how to do arithmetic operations on python using excel formulas. If you guys could help me achieve this, I'd really appreciate that

are you saying you want to write excel formulas into columns then export that ? - if so, why not just do it in excel? — Umar.H
– Umar.H, Commented Jan 23, 2020 at 10:41
Actually, it was being done in excel only, but I am creating sort of an automation tool which with a press of a button will create a new excel applying the formulas of the previous excel hence, reducing the hassle of doing it manually — vesuvius
– vesuvius, Commented Jan 23, 2020 at 10:46

Dimitris Thomas · Accepted Answer · 2020-01-25 15:32:16Z

7

you can write formulas to each row using a for loop.

import pandas as pd
import numpy as np

# Create a test dataframe
df = pd.DataFrame({'RPA Rej': [1,5,0],
                    'RPA Acc': [0,3,0]})

# Insert an empty column to write the formulas
df.insert(2, 'TOTAL', np.nan)

# Start the xlsxwriter
writer = pd.ExcelWriter('Test Formulas.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Create a for loop to start writing the formulas to each row
for row in range(2,df.shape[0]+2):
    formula = f'=A{row}-B{row}'
    worksheet.write_formula(f"C{row}", formula)

writer.save()

Output:

answered Jan 25, 2020 at 15:32

Dimitris Thomas

1,40312 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create a new dataframe column by applying Excel formula using Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related