<p>关键是用<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.stack.html" rel="nofollow noreferrer">^{<cd1>}</a>堆栈,然后用<code>groupby.rolling</code>,最后用<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.pivot_table.html#pandas.DataFrame.pivot_table" rel="nofollow noreferrer">^{<cd3>}</a>返回两列</p>
<pre><code>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)
</code></pre>
<hr/>
<p><strong>输出</strong></p>
<pre><code> 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
</code></pre>
<hr/>
<p>熔融溶液</p>
<pre><code>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)
)
</code></pre>