Pandas Grouping - Creating a Generic Aggregation Function
<p>I need to do a lot of aggregation on data and I was hoping to write a function that would allow me to pass</p>

<p>1) The string to use for grouping
2) The fields that would constitute the numerator/denominator/ and formula</p>

<p>As I will be doing a lot of cuts on the data using different groupings and different numerators and denominators, it would be easier for me to create a generic group by and pass it what I need</p>

<p>So lets take the following example:</p>

<pre><code>import pandas as pd
df=pd.read_csv("https://raw.githubusercontent.com/wesm/pydata-book/master/ch08/tips.csv", sep=',')
(df.groupby(['sex', 'smoker'])[['total_bill','tip']].sum().apply(lambda r: r.tip/r.total_bill, axis = 1))
</code></pre>

<p>Now, I would want to create a function that would allow me to pass a group by value and a numerator denominator field</p>

<p>So, for example</p>

<pre><code>groupbyvalue=['sex', 'smoker']
fieldstoaggregate=['tip','total_bill']
</code></pre>

<p>And plug them into something like</p>

<pre><code>(df.groupby(groupbyvalue)[fieldstoaggregate].sum().apply(lambda r: r.tip/r.total_bill, axis = 1))
</code></pre>

<p>That works fine, but when I tried to replace the formula with something like:  </p>

<pre><code>dfformula="r.tip/r.total_bill"
</code></pre>

<p>And then placed it in the formula as follows </p>

<pre><code>(df.groupby(groupbyvalue)[fieldstoaggregate].sum().apply(lambda r: dfformula, axis = 1)*10000)
</code></pre>

<p>My output looks as follows:</p>

<pre><code>sex     smoker
Female  No        r.tip/r.total_billr.tip/r.total_billr.tip/r.to...
        Yes       r.tip/r.total_billr.tip/r.total_billr.tip/r.to...
Male    No        r.tip/r.total_billr.tip/r.total_billr.tip/r.to...
       Yes       r.tip/r.total_billr.tip/r.total_billr.tip/r.to...
dtype: object
</code></pre>

<p>Is there any way to create the calculation dynamically then use it in the formula rather than having it interpreted as a string?</p>

<p>Thanks</p>

<p>You can achieve this using <code>eval()</code> function</p>

<pre><code>import pandas as pd

df = pd.read_csv("https://raw.githubusercontent.com/wesm/pydata-book/master/ch08/tips.csv", sep=',')

groupbyvalue = ['sex', 'smoker']
fieldstoaggregate = ['tip','total_bill']
dfformula = "r.tip/r.total_bill"

(df.groupby(groupbyvalue)[fieldstoaggregate].sum().apply(lambda r: eval(dfformula), axis = 1))
</code></pre>

<p>The output would be as follows</p>

<pre><code>sex     smoker
Female  No        0.153189
        Yes       0.163062
Male    No        0.157312
        Yes       0.136919
dtype: float64
</code></pre>

python pandas dataframe aggregate aggregation