Search code examples
pandasmachine-learningdummy-variable

Creating dummy variables by grouping

I have a DataFrame representing player, team and win. What I would like to do is create a new DataFrame where team is the index and whether player x was in that team is represented, and if that team won.

pd.DataFrame(data=[['Team A', 1], ['Team B', 0], ['Team B', 0], ['Team A', 1]], columns=['TEAM', 'WIN'], index=['Player 1', 'Player 2', 'Player 3', 'Player 4'])

            TEAM  WIN
Player 1  Team A    1
Player 2  Team B    0
Player 3  Team B    0
Player 4  Team A    1

desired result:

pd.DataFrame(data=[[1, 1, 0, 0, 1], [0, 0, 1, 1, 0]], columns=['Player 1', 'Player 2', 'Player 3', 'Player 4', 'WIN'], index=['Team A', 'Team B'])

        Player 1  Player 2  Player 3  Player 4  WIN
Team A         1         1         0         0    1
Team B         0         0         1         1    0
Solution

  • IIUC you can use pivot_table() method:

    In [96]: df.reset_index().pivot_table(index='TEAM', columns='index', values='WIN', fill_value=0)
Out[96]:
index   Player 1  Player 2  Player 3  Player 4
TEAM
Team A         1         0         0         1
Team B         0         0         0         0