CODEWITHSUNDEEP
pythonpandasdataframe
beta
beta

Reputation: 5686

pandas dataframe aggregate calculation

I have a pandas dataframe containing sports matches:

Winner      Loser          
A           B
B           A 
A           C

i want to have win-loss statistics for each player (i.e. A, B, and C). so for A the result should be 2-1. for B it should be 1-1 and for C it should be 0-1.

i know how to calculate this via iterating line-by-line over the dataframe with:

for index, match in df.iterrows():
    //code for calculating win-loss here

but I am sure that there is a more pythonic/pandas-ish way to do this? any hints on this are appreciated.

Upvotes: 2

Views: 729

Answers (3)

Mike Müller
Mike Müller

Reputation: 85482

On way of doing it:

win = df.groupby('Winner').count()
los = df.groupby('Loser').count()
score = pd.DataFrame([win.Loser, los.Winner])
score

gives:

        A   B   C
Loser   2   1   0
Winner  1   1   1

and:

score.T

shows it transposed:

    Loser   Winner
A   2       1
B   1       1
C   0       1

This is the dataframe used above:

df = pd.DataFrame({'Winner': list('ABA'), 
                   'Loser': list('BAC')})  

df

  Loser  Winner
0 B      A
1 A      B
2 C      A

All in one line:

pd.DataFrame([df.groupby('Winner').count().Loser, 
              df.groupby('Loser').count().Winner]).fillna(0).T

results in:

    Loser   Winner
A   2       1
B   1       1
C   0       1

Upvotes: 1

Simon Gibbons
Simon Gibbons

Reputation: 7194

You can use the groupby method with the size aggregate to do this

for example

print df.groupby('Loser').size()

Would yield a dataframe with the counts of number of losses.

Loser
A         1
B         1
C         1
dtype: int64

You can then combine these into the score counts as follows (using the fillna method to set a default value if a team has no wins or losses)

wins = df.groupby('Winner').size()
losses = df.groupby('Loser').size()

scores = pd.DataFrame({'Wins' : wins, 'Losses' : losses}).fillna(0)

Yielding the final score counts as

   Losses  Wins
A       1     2
B       1     1
C       1     0

Upvotes: 2

Pintas
Pintas

Reputation: 268

What format do you want your results in?

A simple manner to count wins and losses would be to use collections.Counter:

import pandas as pd
from collections import Counter

df=pd.DataFrame([['A','B'],['B','C'],['A','C']], columns=['winner','loser'])

win_counts = Counter(df['winner'])

win_counts is a dictionary like the one below:

Counter({'A': 2, 'B': 1})

Still, I prefer Simon Gibbons answer above as it does not require additional modules.

Upvotes: 0

Related Questions