I'm trying to do in pandas what I would do with a correlated subquery in SQL, and have not been able to figure out a non-iterative and/or speedier solution. Consider a (much larger) dataframe of players, games, and stats for those games, which could be defined as such:

```py
import pandas as pd

def make_mock_df():
    mock_data = {
        "player": ["Bob", "Bob", "Bob", "Bob", "Bob", "Jim", "Jim", "Jim", "Jim", "Jim"],
        "game_id": ["red", "red", "green", "green", "blue", "red", "yellow", "yellow", "blue", "blue"],
        "stat_id": [1, 2, 1, 2, 1, 1, 1, 2, 1, 2],
        "value": [1, 3, 2, 4, 3, 1, 1, 2, 4, 2],
    }
    return pd.DataFrame(mock_data)

```

In reality, the game ID would be a UUID, and the data would already be sorted first by player and then by game date, recent-first.

What I want to do is create a dataframe that averages each stat type over the last N games per player. This gets tricky, as not every player plays every game (mocked in the data), and thus is why I'd do the correlated subquery in SQL. This is the iterative solution so far, which breaks down the dataframe by player, then concats each limited player dataframe to rebuild the whole:

```py
def filter_last_n_games(df, n_games):
    last_n_games_per_player = pd.DataFrame()

    for _, player_group in df.groupby("player"):
        # this works because games are sorted already
        # and appear sorted in the unique() series as well
        last_n_games = player_group.game_id.unique()[0:n_games]
        # can't do nlargest because games have string IDs
        last_n_mask = player_group.game_id.isin(last_n_games)
        player_last_n = player_group[last_n_mask]
        # build up last N games df piece by piece...
        last_n_games_per_player = pd.concat(
            [last_n_games_per_player, player_last_n]
        )

    return last_n_games_per_player

def avg_stats_last_n_games(df, n_games):
    last_n_games_per_player = filter_last_n_games(df, n_games)

    # calculate averages per game (easy part)
    last_n_avgs = (
        last_n_games_per_player
        .groupby(["player", "stat_id"])
        .mean()
        .filter(["player", "stats_id", "value"])
        .rename(columns={"value": "avg_value"})
    )

    return last_n_avgs

```

And results in output as such:

```
stats_df = make_mock_df()
avg_stats_last_n_games(stats_df, 2)
>>>                 avg_value
>>> player stat_id           
>>> Bob    1              1.5
>>>        2              3.5
>>> Jim    1              1.0
>>>        2              2.0

```

Is there a better substitute for the `filter_last_n_games()` function that would fully take advantage of pandas' capabilities and allow me to ditch the for-loop?