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>I came up with the following solution:</p>

<pre><code>df1= df.sort_values(by="event_date",ascending = False)
g = df1.groupby(by=["id"])
df1["total_money_spent","count"]= g.agg({"money_spent":["cumsum","cumcount"]})
df1["avg_money_spent"]=df1["total_money_spent"]/(df1["count"]+1)
</code></pre>

python python-2.7 pandas