1

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?

yash.trojan.25
  • 211
  • 1
  • 3
  • 8
  • 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 Aug 26 '14 at 01:47

2 Answers2

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'))
jay s
  • 531
  • 5
  • 12
  • 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? – yash.trojan.25 Aug 26 '14 at 03:20
  • 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')` – jay s Aug 26 '14 at 03:36
  • Thanks, I will take a look into it and see how it can help me. Sounds interesting to use. – yash.trojan.25 Aug 26 '14 at 05:25
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.)

Community
  • 1
  • 1
HaPsantran
  • 5,581
  • 6
  • 24
  • 39