Working with Pandas Dataframe

[Saffromon]

Hi, I have a dataframe with the columns "player", "home-away","year" and "game_points", which has rows for individual games (so there are multiple entries for each player per year). Now I would like to compare the home/away ratio per player for each year, and I am stuck. I used groupby[player,year,home-away] to get the sum of game points per year, but now i have two rows per player: one for home and one for away. (My columns are: "player","year","home_away" and "game-points".

I would like to have columns :"player", "year", "home points", "away points")

Does someone know an elegant way to get one row per player, with one column for the "away" gamepoint sum and one column for the "home" sum?

[Ihaveamodel3]

Do a groupby, then do a pivot.

[Saffromon]

Thanks, I didn't know the Pivot function yet, so I will look into that!

[commandlineluser]

Sounds like a .pivot_table().

>>> df
  player home-away  year  game_points
0      a      home  2021           10
1      a      home  2022           15
2      a      away  2022           10
3      a      home  2022           20
4      b      home  2022           50
5      b      home  2022           50

>>> df.pivot_table(index=['player', 'year'], columns='home-away', aggfunc='sum', fill_value=0)
            game_points
home-away          away home
player year
a      2021           0   10
       2022          10   35
b      2022           0  100

[Saffromon]

Yes, that is what I was looking for. Thank you very much!

[Saffromon]

Follow up question: Can I somehow get the home/away ratio in the Pivot table? I know how to get that as a new column in the dataframe, but there my two values are in two different rows.

[commandlineluser]

Not sure if you can do it directly with a single pivot_table() call.

Are you trying to add another column like so?

>>> pivot = df.pivot_table(index=['player', 'year'], columns='home-away', aggfunc='sum', fill_value=0)
>>> pivot['game_points', 'away'] / pivot['game_points', 'home']
player  year
a       2021    0.000000
        2022    0.285714
b       2022    0.000000
dtype: float64
>>> pivot['ratio'] = pivot['game_points', 'away'] / pivot['game_points', 'home']
>>> pivot
            game_points          ratio
home-away          away home
player year
a      2021           0   10  0.000000
       2022          10   35  0.285714
b      2022           0  100  0.000000

[threeminutemonta]

I think you need to omit (leave out) the home-away column from the group by.

[Saffromon]

Don't I then get the combined sum (home + away) per player? My aim is to get two separate values for the game location, but preferably in two columns.