我正在尝试使用 groupby 获得累积总和,其中累积总和应用于包含相同值的多个列
import pandas as pd
import numpy as np
df = pd.DataFrame([['Jazz', 'Clippers', 89, 100],
['Clippers' , 'Jazz', 101, 97],
['Bucks' , 'Jazz', 99, 112],
['Jazz' , 'Bucks', 109, 88]],
columns=['home_team', 'away_team', 'home_points', 'away_points'])
print(df)
这将产生一个输出为的数据帧
home_team away_team home_points away_points
0 Jazz Clippers 89 100
1 Clippers Jazz 101 97
2 Bucks Jazz 99 112
3 Jazz Bucks 109 88
我想要做的是获得主客队的累计总分,这将说明每支球队都出现在主客场列中的事实,但我所能弄清楚的只是按以下分组的累计总分球队名称,将每支球队作为主场或客场总计,就像这样
df["home_cumulative_points"]= df.groupby(["home_team"])["home_points"].cumsum()
df["away_cumulative_points"]= df.groupby(["away_team"])["away_points"].cumsum()
print(df)
产生
home_team away_team home_points away_points home_cumulative_points away_cumulative_points
0 Jazz Clippers 89 100 89 100
1 Clippers Jazz 101 97 101 97
2 Bucks Jazz 99 112 99 209
3 Jazz Bucks 109 88 198 88
有没有什么方法可以让我在主客场列中对同一支球队的存在进行累计总和帐户,以使总和加上球队的积分,无论他们是主场还是客场?所以最后一行的理想输出是
home_team away_team home_points away_points home_cumulative_points away_cumulative_points
3 Jazz Bucks 109 88 407 187
我猜我可能需要做一个 for 循环或其他什么,但我不确定如何最好地去做。提前感谢您的任何反馈!
想法是只选择必要的列,由_
for分割,由MultiIndex
reshape by DataFrame.stack
,因此可以同时使用cumsum
每列:
cols = ['home_team', 'away_team', 'home_points', 'away_points']
df1 = df[cols].copy()
df1.columns = df1.columns.str.split('_', expand=True)
df1 = df1.stack(0).rename_axis(['lev1','lev2'])
df1["cumulative_points"]= df1.groupby(["team", 'lev1'])["points"].cumsum()
df2 = df1.unstack()
df2.columns = df2.columns.map(lambda x: f'{x[1]}_{x[0]}')
print(df2)
away_points home_points away_team home_team away_cumulative_points \
lev1
0 100 89 Clippers Jazz 100
1 97 101 Jazz Clippers 97
2 112 99 Jazz Bucks 112
3 88 109 Bucks Jazz 88
home_cumulative_points
lev1
0 89
1 101
2 99
3 109
或者:
df["home_cumulative_points"]= df1.loc['home', 'cumulative_points']
df["away_cumulative_points"]= df1.loc['away', 'cumulative_points']
另一种方法是使用concat
with rename
for reshape:
f = lambda x: x.split('_')[1]
df1 = pd.concat([df[['home_team', 'home_points']].rename(columns=f),
df[['away_team', 'away_points']].rename(columns=f)], keys=('home','away'))
df1 = df1.rename_axis(['lev1','lev2'])
df1["cumulative_points"]= df1.groupby(["team", 'lev1'])["points"].cumsum()
df["home_cumulative_points"]= df1.loc['home', 'cumulative_points']
df["away_cumulative_points"]= df1.loc['away', 'cumulative_points']
print(df)
home_team away_team home_points away_points home_cumulative_points \
0 Jazz Clippers 89 100 89
1 Clippers Jazz 101 97 101
2 Bucks Jazz 99 112 99
3 Jazz Bucks 109 88 198
away_cumulative_points
0 100
1 97
2 209
3 88
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句