CODEWITHSUNDEEP
pythonexcelpandassaveformula
yash.trojan.25
yash.trojan.25

Reputation: 211

python pandas formula to dataframe

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?

Upvotes: 1

Views: 1547

Answers (2)

HaPsantran
HaPsantran

Reputation: 6247

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.)

Upvotes: 0

jay s
jay s

Reputation: 531

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'))

Upvotes: 1

Related Questions