I have the following pd.DataFrame
match_id player_id round points A B C D E
5890 3750 1 10 0 0 0 3 1
5890 3750 2 10 0 0 0 1 0
5890 3750 3 10 0 8 0 0 1
5890 2366 1 9 0 0 0 5 0
5890 2366 2 9 0 0 0 5 0
5890 2366 3 9 0 0 0 2 0
I want to subtract the values of A, B, C, D and E of the two players and create two new columns that represent the number of points of the two players.
My desired output looks as follows:
match_id round points_home points_away A B C D E
5890 1 10 9 0 0 0 -2 1
5890 2 10 9 0 0 0 -4 0
5890 3 10 9 0 8 0 -2 1
Please advice
Use GroupBy.first with GroupBy.last first, subtract necessary columns with add points columns in concat:
g = df.groupby(['match_id','round'])
df1 = g.first()
df2 = g.last()
cols = ['A','B','C','D','E']
out = pd.concat([df1['points'].rename('points_home'),
df2['points'].rename('points_away'),
df1[cols].sub(df2[cols])], axis=1).reset_index()
print (out)
match_id round points_home points_away A B C D E
0 5890 1 10 9 0 0 0 -2 1
1 5890 2 10 9 0 0 0 -4 0
2 5890 3 10 9 0 8 0 -2 1
Alternative with MultiIndex with GroupBy.agg:
df3 = (df.groupby(['match_id','round'])
.agg(['first','last'])
.rename(columns={'first':'home', 'last':'away'}))
cols = ['A','B','C','D','E']
out = pd.concat([df3['points'].add_prefix('points_'),
df3.xs('home', axis=1, level=1)[cols]
.sub(df3.xs('away', axis=1, level=1)[cols])], axis=1).reset_index()
print (out)
match_id round points_home points_away A B C D E
0 5890 1 10 9 0 0 0 -2 1
1 5890 2 10 9 0 0 0 -4 0
2 5890 3 10 9 0 8 0 -2 1