How to do calculation on pandas dataframe that require processing multiple rows?
<p>I have a dataframe from which I need to calculate a number of features from. The dataframe <code>df</code> looks something like this for a object and an event:</p>

<pre><code>id    event_id    event_date   age    money_spent    rank
1       100       2016-10-01    4     150            2
2       100       2016-09-30    5     10             4    
1       101       2015-12-28    3     350            3
2       102       2015-10-25    5     400            5    
3       102       2015-10-25    7     500            2
1       103       2014-04-15    2     1000           1
2       103       2014-04-15    3     180            6
</code></pre>

<p>From this I need to know for each id and event_id (basically each row), what was the number of days since the last event date, total money spend upto that date, avg. money spent upto that date, rank in last 3 events etc.</p>

<p>What is the best way to work with this kind of problem in pandas where for each row I need information from all rows with the same <code>id</code> before the date of that row, and so the calculations? I want to return a new dataframe with the corresponding calculated features like</p>

<pre><code>id    event_id    event_date   days_last_event    avg_money_spent    total_money_spent
1       100       2016-10-01    278               500                  1500
2       100       2016-09-30    361               196.67               590    
1       101       2015-12-28    622               675                  1350
2       102       2015-10-25    558               290                  580    
3       102       2015-10-25    0                 500                  500
1       103       2014-04-15    0                 1000                 1000
2       103       2014-04-15    0                 180                  180
</code></pre>

<p>Sort the <code>event_date</code> column in ascending order and choose the first date which corresponds to the earliest date and compute the results(in days) after subtracting it with the other dates:</p>

<pre><code>min_date = df.sort_values(by=['event_date'])['event_date'].iloc[0]
df['days_last_event'] = (df['event_date'] - min_date).dt.days
</code></pre>

<p>Sort the <code>DF</code> by the number of days and making sure it starts from 0 at the very top:</p>

<pre><code>df.sort_values(['days_last_event'], inplace=True)
</code></pre>

<p>Grouping with key as the <code>id</code> column and taking <code>money_spent</code> as our selection and taking it's cumulative sum across groups:</p>

<pre><code>grp_1 = df.groupby('id')['money_spent'] 
df['total_money_spent'] = grp_1.apply(lambda x: x.cumsum()).sort_index()
</code></pre>

<p>Grouping with key as the <code>id</code> column and taking <code>total_money_spent</code> as our selection and dividing with the cumulative count of the previous groupby object to compute the cumulative average/mean.</p>

<pre><code>grp_2 = df.groupby(['id'])['total_money_spent']
df['avg_money_spent'] = (grp_2.head()).div(grp_1.cumcount()+1)
</code></pre>

<p>Finally, drop off the unwanted columns to get:</p>

<pre><code>df.sort_index().drop(['age', 'rank', 'money_spent'], axis=1)
</code></pre>

<p><a href="http://i.stack.imgur.com/VMz0V.png" rel="nofollow"><img src="http://i.stack.imgur.com/VMz0V.png" alt="Image"></a></p>

python python-2.7 pandas