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

Solution 1:

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
A1B1
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
A12B11
C       10

Solution 2:

On way of doing it:

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

gives:

AB   C
Loser   210
Winner  111

and:

score.T

shows it transposed:

    Loser   Winner
A21B11
C   01

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
A21B11
C   01

Solution 3:

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 importCounter

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.