python pandas formula to dataframe

Question

I am creating a dataframe with a bunch of calculations and adding new columns using these formulas (calculations). Then I am saving the dataframe to an Excel file. I lose the formula after I save the file and open the file again. For example, I am using something like: total = 16

for s in range(total):
    df_summary['Slopes(avg)' + str(s)]= df_summary[['Slope_S' + str(s)]].mean(axis=1)*df_summary['Correction1']/df_summary['Correction2'].mean(axis=1)

How can I make sure this formula appears in my excel file I write to, similar to how we have a formula in an excel worksheet?

I have my doubts there is a way to do exactly what you want, but there is a python package xlwt that may be your best option. — JohnE
– JohnE, Commented Aug 26, 2014 at 1:47

jay s · Accepted Answer · 2014-08-26 03:43:41Z

1

You can write formulas to an excel file using the XlsxWriter module. Use .write_formula() https://xlsxwriter.readthedocs.org/worksheet.html#worksheet-write-formula. If you're not attached to using an excel file to store your dataframe you might want to look into using the pickle module.

import pickle
# to save
pickle.dump(df,open('saved_df.p','wb'))
# to load
df = pickle.load(open('saved_df.p','rb'))

edited Aug 26, 2014 at 3:43

answered Aug 26, 2014 at 2:33

jay s

5315 silver badges13 bronze badges

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

3 Comments

yash.trojan.25 Over a year ago

I am not attached to using an excel file, but is a more convenient way to go if I can get the formulas in there. This is the first time I have heard about the pickle module. How does this help?

jay s Over a year ago

Pickle allows you to store python objects like a list or a dataframe in a file. This way you can easily reload the dataframe in the state that you left it without writing it to excel and reading it when you want to use it again. import pickle # to save pickle.dump(df, open('saved_df.p','wb') # to load df = pickle.load(open('saved_df.p','rb')

yash.trojan.25 Over a year ago

Thanks, I will take a look into it and see how it can help me. Sounds interesting to use.

Community · Accepted Answer · 2017-05-23 12:20:34Z

0

I think my answer here may be responsive. The short of it is you need to use openpyxl (or possibly xlrd if they've added support for it) to extract the formula, and then xlsxwriter to write the formula back in. It can definitely be done.

This assumes, of course, as @jay s pointed out, that you first write Excel formulas into the DataFrame. (This solution is an alternative to pickling.)

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jan 8, 2015 at 2:41

HaPsantran

6,3077 gold badges26 silver badges42 bronze badges

Collectives™ on Stack Overflow

python pandas formula to dataframe

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related