CODEWITHSUNDEEP
pythonpandas
Demetri Pananos
Demetri Pananos

Reputation: 7404

How can I use `pivot` to track wins and loses?

Suppose I have some team data as a dataframe df.

home_team   home_score  away_team   away_score
A               3         C            1
B               1         A             0
C               3         B             2

I'd like to a dataframe indicating how many times one team has beat another. So for instance the entry in [1,3] would be the number of times team 1 has beat team 3, but the number in [3,1] would be the number of times team 3 as beat team 1.

This sounds like something df.pivot should be able to do, but I can't seem to get it to do what I would like.

How can I accomplish this using pandas?

Here is a desired output

    A B C

A   0 0 1

B   1 0 0

C   0 1 0

Upvotes: 2

Views: 53

Answers (1)

James
James

Reputation: 36721

This will create a new dataframe with just the winners and loosers. It can be pivoted to created what you are looking for.

I made some additional data to fill in some of the pivot table values

import pandas as pd

data = {'home_team':['A','B','C','A','B','C','A','B','C'], 
        'home_score':[3,1,3,0,1,2,0,4,0], 
        'away_team':['C','A','B','B','C','B','C','A','A'], 
        'away_score':[1,0,2,2,0,3,1,7,1]}
df = pd.DataFrame(d)

# create new dataframe
WL = pd.DataFrame()
WL['winner'] = pd.concat([df.home_team[df.home_score>df.away_score],
                          df.away_team[df.home_score<df.away_score]], axis=0)
WL['loser'] = pd.concat([df.home_team[df.home_score<df.away_score],
                         df.away_team[df.home_score>df.away_score]], axis=0)
WL['game'] = 1

# groupby to count the number of win/lose pairs
WL_gb = WL.groupby(['winner','loser']).count().reset_index()

# pivot the data
WL_piv = WL_gb.pivot(index='winner', columns='loser', values='game')

enter image description here

Upvotes: 4

Related Questions