CODEWITHSUNDEEP
pythonpython-3.xloopsdataframeboolean
MisterButter
MisterButter

Reputation: 807

Determine the winner of a game based on a score column with pandas

I'm setting up a dataset of hockey games and need to decide whether a team won or lost based on the 'Game_id' and 'Goals' columns. Each game has its own ID and spans two rows, so 1000 games are stored in 2000 rows.

My dataframe looks like this:

Team  Home/Away  Goals  Game_id
CAL   Home       7      2017020001
PHY   Away       4      2017020001
CAP   Home       7      2017020002
WILD  Away       4      2017020002

I need a fith column 'Won/Lost' that is based on goals for the specific 'Game_id'. I'm struggling to create a loop that does that for me.

The result I'm looking for is this: 

Team  Home/Away  Goals  Game_id      Won/Lost
CAL   Home       7      2017020001   Won
PHY   Away       4      2017020001   Lost
CAP   Home       7      2017020002   Won
WILD  Away       4      2017020002   Lost

Upvotes: 1

Views: 842

Answers (1)

timgeb
timgeb

Reputation: 78770

Given 

>>> df                                                                                                                  
   Team Home/Away  Goals     Game_id
0   CAL      Home      7  2017020001
1   PHY      Away      4  2017020001
2   CAP      Home      7  2017020002
3  WILD      Away      4  2017020002
4  WILD      Away      1  2017020003
5   CAP      Home      1  2017020003

I'd write the following function:

def win_loss_draw(group): 
     group = group == group.max() 
     if group.all(): 
         group[:] = 'Draw' 
     else: 
         group = group.map({True: 'Won', False: 'Lost'}) 
     return group

... and apply it like this:

>>> df['Won/Lost'] = df.groupby('Game_id')['Goals'].apply(win_loss_draw)                                                
>>> df                                                                                                                  
   Team Home/Away  Goals     Game_id Won/Lost
0   CAL      Home      7  2017020001      Won
1   PHY      Away      4  2017020001     Lost
2   CAP      Home      7  2017020002      Won
3  WILD      Away      4  2017020002     Lost
4  WILD      Away      1  2017020003     Draw
5   CAP      Home      1  2017020003     Draw

I exclude draws given that a hockey game can only end in a draw in regular time, but my data is with over time so there is only win and loss

In this specific case it is enough to issue

df['Won/Lost'] = df.groupby('Game_id')['Goals'].apply(lambda g: (g == g.max()).map({True: 'Won', False: 'Lost'}))

(this is Version 1)

~edit~

Performance improvements!

Version 2:

is_winner = df.groupby('Game_id')['Goals'].transform('max') == df['Goals']
df['Won/Lost'] = is_winner.map({True: 'Won', False: 'Lost'})

Version 3:

is_winner = df.groupby('Game_id')['Goals'].transform('max') == df['Goals']                                          
df['Won/Lost'] = np.where(is_winner.values, 'Won', 'Lost')

Timings:

# Setup
>>> df = pd.concat([df]*1000, ignore_index=True)                                                                        
>>> df['Game_id'] = np.arange(len(df)//2).repeat(2)                                                                     
>>>                                                                                                                     
>>> df                                                                                                                  
      Team Home/Away  Goals  Game_id
0      CAL      Home      7        0
1      PHY      Away      4        0
2      CAP      Home      7        1
3     WILD      Away      4        1
4      CAL      Home      7        2
...    ...       ...    ...      ...
3995  WILD      Away      4     1997
3996   CAL      Home      7     1998
3997   PHY      Away      4     1998
3998   CAP      Home      7     1999
3999  WILD      Away      4     1999

# Timings (i5-6200U CPU @ 2.30GHz, only relative times are important though)
>>> %timeit df.groupby('Game_id')['Goals'].apply(lambda g: (g == g.max()).map({True: 'Won', False: 'Lost'})) # Version 1            
1.73 s ± 13.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
>>> %timeit (df.groupby('Game_id')['Goals'].transform('max') == df['Goals']).map({True: 'Won', False: 'Lost'}) # Version 2          
2.38 ms ± 37.5 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
>>> %timeit np.where((df.groupby('Game_id')['Goals'].transform('max') == df['Goals']).values, 'Won', 'Lost') # Version 3            
1.53 ms ± 6.19 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Upvotes: 3

Related Questions