迭代最新点

Question

我有这个数据框，我想用最后3场比赛中每个球员的平均分来完成最后一栏

data = {
    'Player1' : ['John','Charles','Alexander','Michael','John','John','Michael','Alexander','Charles','Michael','John','Michael','Alexander'],
    'Player2' : ['Charles','Alexander','Michael','John','Chales','Alexander','Alexander','Charles','John','Alexander','Charles','Charles','John'],
    'P1 Points': [10,8,10,5,10,9,6,7,10,8,10,10,8],
    'P2 Points': [6,10,7,10,7,10,10,10,7,10,7,6,10]
}

新列：

          Date       Player1    Player2      P1_Points  P2_Points    P1last3gamesPTs  \
    0  2019-12-21       John    Charles         10          6              NaN   
    1  2019-12-21    Charles  Alexander          8         10              NaN   
    2  2019-12-21  Alexander    Michael         10          7              NaN   
    3  2019-12-21    Michael       John          5         10              NaN   
    4  2019-12-22       John     Chales         10          7              NaN   
    5  2019-12-22       John  Alexander          9         10              NaN   
    6  2019-12-22    Michael  Alexander          6         10              NaN   
    7  2019-12-22  Alexander    Charles          7         10              NaN   
    8  2019-12-23    Charles       John         10          7              NaN   
    9  2019-12-23    Michael  Alexander          8         10              NaN   
    10 2019-12-23       John    Charles         10          7              NaN   
    11 2019-12-23    Michael    Charles         10          6              NaN   
    12 2019-12-24  Alexander       John          8         10              NaN   

        P2last3gamesPTs  
    0               NaN  
    1               NaN  
    2               NaN  
    3               NaN  
    4               NaN  
    5               NaN  
    6               NaN  
    7               NaN  
    8               NaN  
    9               NaN  
    10              NaN  
    11              NaN  
    12              NaN  

可能需要一些迭代，但我不知道怎么做，有人能帮我吗？你知道吗

Answer 1

你可以试试

>>> df['P2last3gamesPTs'] = df['P2 Points'].rolling(window=3).mean()
>>> df['P1last3gamesPTs'] = df['P1 Points'].rolling(window=3).mean()
>>> df
      Player1    Player2  P1 Points  P2 Points  P2last3gamesPTs  P1last3gamesPTs
0        John    Charles         10          6              NaN              NaN
1     Charles  Alexander          8         10              NaN              NaN
2   Alexander    Michael         10          7         7.666667         9.333333
3     Michael       John          5         10         9.000000         7.666667
4        John     Chales         10          7         8.000000         8.333333
5        John  Alexander          9         10         9.000000         8.000000
6     Michael  Alexander          6         10         9.000000         8.333333
7   Alexander    Charles          7         10        10.000000         7.333333
8     Charles       John         10          7         9.000000         7.666667
9     Michael  Alexander          8         10         9.000000         8.333333
10       John    Charles         10          7         8.000000         9.333333
11    Michael    Charles         10          6         7.666667         9.333333
12  Alexander       John          8         10         7.666667         9.333333

Answer 2

关键是用^{}堆栈，然后用groupby.rolling，最后用^{}返回两列

df[['P1last3gamesPTs','P2last3gamesPTs']] = (

  df.set_index(['Player1','Player2'])
    .stack()
    .rename('Value')
    .rename_axis(index =['Player1','Player2','Points'])
    .reset_index()
    .assign(Player=lambda x:x['Player1'].mask(x['Points'].eq('P2 Points'),x['Player2']))
    .reset_index()
    .assign(index = lambda x: x['index']//2)
    # you need to comment the next line if you want compute the actual value in the mean
    .assign(Value = lambda x: x.groupby('Player').Value.shift())
    .assign(mean_3_last = lambda x: x.groupby('Player')
                                     .Value
                                     .rolling(3)
                                     .mean()
                                     .reset_index(level=0,drop='Player'))
    .pivot_table(columns = 'Points',
                 values = 'mean_3_last',
                 index='index',
                 dropna = False)
)

print(df)

---

输出

      Player1    Player2  P1 Points  P2 Points  P1last3gamesPTs  \
0        John    Charles         10          6              NaN   
1     Charles  Alexander          8         10              NaN   
2   Alexander    Michael         10          7              NaN   
3     Michael       John          5         10              NaN   
4        John     Chales         10          7              NaN   
5        John  Alexander          9         10        10.000000   
6     Michael  Alexander          6         10              NaN   
7   Alexander    Charles          7         10        10.000000   
8     Charles       John         10          7         8.000000   
9     Michael  Alexander          8         10         6.000000   
10       John    Charles         10          7         8.666667   
11    Michael    Charles         10          6         6.333333   
12  Alexander       John          8         10         9.000000   

    P2last3gamesPTs  
0               NaN  
1               NaN  
2               NaN  
3               NaN  
4               NaN  
5               NaN  
6         10.000000  
7               NaN  
8          9.666667  
9          9.000000  
10         9.333333  
11         9.000000  
12         8.666667

---

熔融溶液

df[['P1last3gamesPTs','P2last3gamesPTs']] = (
    df.melt(['Player1','Player2'])
      .assign(index = lambda x: x.groupby('variable').cumcount())
      .assign(Player = lambda x: x.Player1.mask(x.variable.eq('P2 Points'),x.Player2))
      .sort_values('index')
      .assign(value = lambda x: x.groupby('Player').value.shift())
      .assign(mean_3_last = lambda x: x.groupby('Player')
                                       .value
                                       .rolling(3)
                                       .mean()
                                       .reset_index(level=0,drop='Player'))
      .pivot_table(columns = 'variable',
                   index = 'index',
                   values = 'mean_3_last',
                   dropna = False)
)