## Issue

I am trying to create a lookup reference table in Python that calculates the `cumulative mean`

of a Player’s previous (by `datetime`

) games scores, grouped by venue. However, for my specific need, a player should have previously played a minimum of 2 times at the relevant Venue for a `'Venue Preference'`

`cumulative mean`

calculation.

`df`

format looks like the following:

DateTime | Player | Venue | Score |
---|---|---|---|

2021-09-25 17:15:00 | Tim | Stadium A | 20 |

2021-09-27 10:00:00 | Blake | Stadium B | 30 |

My existing code that works perfectly, but unfortunately is very slow, is as follows:

```
import numpy as np
import pandas as pd
VenueSum = pd.DataFrame(df.groupby(['DateTime', 'Player', 'Venue'])['Score'].sum().reset_index(name = 'Sum'))
VenueSum['Cumulative Sum'] = VenueSum.sort_values('DateTime').groupby(['Player', 'Venue'])['Sum'].cumsum()
VenueCount = pd.DataFrame(df.groupby(['DateTime', 'Player', 'Venue'])['Score'].count().reset_index(name = 'Count'))
VenueCount['Cumulative Count'] = VenueCount.sort_values('DateTime').groupby(['Player', 'Venue'])['Count'].cumsum()
VenueLookup = VenueSum.merge(VenueCount, how = 'outer', on = ['DateTime', 'Player', 'Venue'])
VenueLookup['Venue Preference'] = np.where(VenueLookup['Cumulative Count'] >= 2, VenueLookup['Cumulative Sum'] / VenueLookup['Cumulative Count'], np.nan)
VenueLookup = VenueLookup.drop(['Sum', 'Cumulative Sum', 'Count', 'Cumulative Count'], axis = 1)
```

I am sure there is a way to calculate the `cumulative mean`

in one step without first calculating the `cumulative sum`

and `cumulative count`

, but unfortunately I couldn’t get that to work.

## Solution

IIUC remove 2 groupby by aggregate by `sum`

and `size`

first and then cumulative sum by both columns:

```
df1 = df.groupby(['DateTime', 'Player', 'Venue'])['Score'].agg(['sum','count'])
df1 = df1.groupby(['Player', 'Venue'])[['sum', 'count']].cumsum().reset_index()
df1['Venue Preference'] = np.where(df1['count'] >= 2, df1['sum'] / df1['count'], np.nan)
df1 = df1.drop(['sum', 'count'], axis=1)
print (df1)
DateTime Player Venue Venue Preference
0 2021-09-25 17:15:00 Tim Stadium A NaN
1 2021-09-27 10:00:00 Blake Stadium B NaN
```

Answered By – jezrael

Answer Checked By – Timothy Miller (BugsFixing Admin)