python - Pandas :根据条件扩展均值并排除当前行

Question

需要计算每个参赛队在某个时间点的平均每场比赛进球数 (rolling_avg)。由于我的数据库是高度规范化的，因此这需要花费大量时间(+8k 行)

注意事项:

• 我计算的是一场比赛的总进球数(第 0 列)，而不是每支球队的进球数。

• 这里省略了一些额外的列，但可能是相关的:其中之一是 date_time 列。

示例:在第 1 行(第二行)中，我们可以看到 1249 队的第一场比赛(他们在客场比赛)。在这场比赛中，有3个进球入账。 1249 队的下一场比赛在第 10 行进行，因为这次该队在主场比赛(因为它的 id 出现在 home_team_id)，我希望 home_rolling_avg 等于 3。它不应该考虑当前行。

问题:

我如何根据 total_goals 中的先前值并排除当前行来计算每个团队的目标扩展 average/mean，并将此平均值分配给相关列(取决于球队是主场还是客场)？

    total_goals  home_team_id  away_team_id  home_goals  away_goals  home_rolling_avg  away_rolling_avg
0             2          1277          1241           1           1               NaN               NaN
1             3          1245          1249           2           1               NaN               NaN
2             1          1242          1246           0           1               NaN               NaN
3             4          1261          1248           1           3               NaN               NaN
4             2          1259          1240           2           0               NaN               NaN
5             3          2981          1268           1           2               NaN               NaN
6             1          1244          1255           1           0               NaN               NaN
7             1          1254          1276           1           0               NaN               NaN
8             7          1247         12140           5           2               NaN               NaN
9             3          5681          1270           2           1               NaN               NaN
10            1          1249          5681           0           1               NaN               NaN
# in line 10 above, 'home_rolling_avg' should show 3 (3/1)

更新:

可以找到更大的样本 (n=100) here .应种类要求添加piRSquared (answer here)。

Answer 1

# easy tracking of long column names
hw = ['home_team_id', 'away_team_id']

# I found it easier to melt myself with some numpy help
hw_vals = df[hw].values.ravel()  # flatten 2 columns
idx_rep = df.index.values.repeat(2)  # repeat index [0, 0, 1, 1, ...
tot_rep = df.total_goals.values.repeat(2)  # repeat totals [2, 2, 3, 3, ...

# This is the reshaped series of team ids with total_goals
s = pd.Series(tot_rep, [idx_rep, hw_vals])

# groupby with a combination of expanding().mean() and shift()
e = s.groupby(level=1).apply(lambda x: x.expanding().mean().shift()).dropna()

# style preference of mine to do assignments using index values
# and to get it done in one line
df.set_index(hw[0], append=1).assign(home_rolling_avg=e).reset_index(hw[0]) \
  .set_index(hw[1], append=1).assign(away_rolling_avg=e).reset_index(hw[1])

---

更深入的解释

• 这道题的主要“技巧”之一是将两列中的 ID 识别为公共(public) ID。我们可以使用 pd.melt ，我做到了。但我发现语法比我最终做的更难看。而且我知道 numpy 无论如何都做得更快。
  • 我有两列 ID，我用 ravel 将其展平.这将使长度增加一倍。在此示例中，长度从 10 变为 20。
  • 然后我使用 repeat 创建现有索引的新版本.索引中的每个值都会重复。例如，[1, 2] 变为 [1, 1, 2, 2]。我将结合 ID 本身使用它来创建多索引
  • 重复与上面类似的 total_goals 列
  • 创建一个 pandas 系列，其值为 total_goals 和一个多索引，其中包含作为第一层的先验索引，以及作为第二层的团队 ID。通过将数组列表作为索引参数传递来指定多索引 [idx_rep, hw_vals]
• 现在我有了这个系列，我可以按索引的第二级 level=1 进行分组，然后执行 expanding().mean()。
  • 但是，我需要将其作为 lambda 执行，以便将其延迟或移动一个周期。滞后/偏移是必要的，以仅考虑到当前匹配但不包括当前匹配的扩展平均值。
• 我们可以使用连接或许多其他技术来将相关信息与原始数据集结合起来。但是，这对我来说感觉更自然。
  • 通过将 include 设置为主队 ID，然后我可以 assign将新列包含到副本中。我喜欢这种方法，因为它允许我链接命令。所以我这样做了，通过重置索引并为客队重复该过程。作业之所以有效，是因为索引自然排列。

---

替代方法
使用来自 collections
的 defaultdict + Counter与史蒂文鲁什非常相似

from collections import defaultdict, Counter
c, d = Counter(), defaultdict(int)
home_avgpg = pd.Series(index=df.index)
away_avgpg = pd.Series(index=df.index)

for row in df.itertuples():
    h = row.home_team_id
    a = row.away_team_id
    t = row.total_goals
    if h in c:
        home_avgpg.set_value(i, d[h] / c[h])
    if a in c:
        away_avgpg.set_value(i, d[a] / c[a])
    d[h] += t
    d[a] += t
    c.update([h, a])

---

@StevenRauch 的回答非常快。