How To Store Formulas, Instead Of Values, In Pandas Dataframe

Is it possible to work with pandas DataFrame as with an Excel spreadsheet: say, by entering a formula in a column so that when variables in other columns change, the values in this

Solution 1:

This will work in 0.13 (still in development)

In [19]: df = DataFrame(randn(10,2),columns=list('ab'))

In [20]: df
Out[20]: 
          a         b
00.9584650.6791931 -0.7690770.49743620.5980590.45755530.290926 -1.6179274 -0.248910 -0.9478355 -1.352096 -0.56863160.0091250.7115117 -0.993082 -1.4404058 -0.5937040.35246890.523332 -1.544849

This will be possible as 'a + b' (soon)

In [21]: formulas = { 'c' : 'df.a + df.b' }

In [22]: def update(df,formulas):
               for k, v in formulas.items():
                  df[k] = pd.eval(v)


In [23]: update(df,formulas)

In [24]: df
Out[24]: 
          a         b         c
0  0.958465  0.679193  1.637658
1 -0.769077  0.497436 -0.271642
2  0.598059  0.457555  1.055614
3  0.290926 -1.617927 -1.327001
4 -0.248910 -0.947835 -1.196745
5 -1.352096 -0.568631 -1.920726
6  0.009125  0.711511  0.720636
7 -0.993082 -1.440405 -2.433487
8 -0.593704  0.352468 -0.241236
9  0.523332 -1.544849 -1.021517

You could implement a hook into setitem on the data frame to have this type of function called automatically. But pretty tricky. You didn't specify how the frame is updated in the first place. Would probably be easiest to simply call the update function after you change the values

Solution 2:

I don't know it it is what you want, but I accidentally discovered that you can store xlwt.Formula objects in the DataFrame cells, and then, using DataFrame.to_excel method, export the DataFrame to excel and have your formulas in it:

import pandas
import xlwt

formulae=[]
formulae.append(xlwt.Formula('SUM(F1:F5)'))
formulae.append(xlwt.Formula('SUM(G1:G5)'))
formulae.append(xlwt.Formula('SUM(H1:I5)'))
formulae.append(xlwt.Formula('SUM(I1:I5)'))

df=pandas.DataFrame(formula)
df.to_excel('FormulaTest.xls')

Try it...

Solution 3:

There's currently no way to do this exactly in the way that you describe.

In pandas 0.13 there will be a new DataFrame.eval method that will allow you to evaluate an expression in the "context" of a DataFrame. For example, you'll be able to df['c'] = df.eval('a + b').