Conditional dummy variables in Pandas

Question

df.head()

Player  Tourn   Score
Tom      a       65 
Henry    a       72 
Johno    a       69 
Ingram   a       79 
Ben      a       76 
Harry    a       66 
Nick     b       70
Ingram   b       79 
Johno    b       69

I have a dataframe of player scores in a variety of tournaments('a' to 'm'). Some players played in multiple tournaments, some players played in only one tournament. I wish to create an additional column for every player with a 1 if the player played in that tournament and a 0 if he didn't (so basically a dummy variable).

To look something like this (repeated for every player):

Player  Tourn   Score  Tom(Dummy)
Tom      a       65       1
Henry    a       72       1
Johno    a       69       1
Ingram   a       79       1
Ben      a       76       1
Harry    a       66       1
Nick     b       70       0
Ingram   b       79       0
Johno    b       69       0

What is the best way to achieve this in code? (Ideally I need something that scales well across large dataframes!)

Interested to hear your replies.

I know this has nothing to do with the question, but I initially read the question as "Continental Veteran Pandas"...i need coffee — Stephan, Feb 22 '18 at 14:08

jezrael · Answer 1 · 2018-02-22T15:15:19.273

First use get_dummies and then groupby by column Tourn with transform of any, cast to int and last join to original:

df1 = pd.get_dummies(df['Player'])
df2 = df.join(df1.groupby(df['Tourn']).transform('any').astype(int))

Another faster solution (for each tournament play each player only once):

df.join(df.groupby(['Tourn','Player']).size().unstack(fill_value=0), on='Tourn')

print (df2)
   Player Tourn  Score  Ben  Harry  Henry  Ingram  Johno  Nick  Tom
0     Tom     a     65    1      1      1       1      1     0    1
1   Henry     a     72    1      1      1       1      1     0    1
2   Johno     a     69    1      1      1       1      1     0    1
3  Ingram     a     79    1      1      1       1      1     0    1
4     Ben     a     76    1      1      1       1      1     0    1
5   Harry     a     66    1      1      1       1      1     0    1
6    Nick     b     70    0      0      0       1      1     1    0
7  Ingram     b     79    0      0      0       1      1     1    0
8   Johno     b     69    0      0      0       1      1     1    0

Timings:

N = 10000
a = ['Tom', 'Henry', 'Johno', 'Ingram', 'Ben', 'Harry', 'Nick', 'Ingram', 'Johno']
a = ['{}{}'.format(i, j) for i in range(5) for j in a]

df = pd.DataFrame({'Player':np.random.choice(a, size=N), 
                   'Tourn':np.random.randint(1000, size=N).astype(str)})

df = df.sort_values('Tourn')
#print (df.head())

In [486]: %%timeit
     ...: df.join(df.groupby(['Tourn','Player']).size().unstack(fill_value=0), on='Tourn')
     ...: 
100 loops, best of 3: 12.6 ms per loop

In [487]: %%timeit 
     ...: df.join(pd.crosstab(df.Tourn, df.Player), on='Tourn')
10 loops, best of 3: 60.9 ms per loop

In [488]: %%timeit
     ...: df1 = pd.get_dummies(df['Player'])
     ...: df2 = df.join(df1.groupby(df['Tourn']).transform('any').astype(int))
     ...: 
10 loops, best of 3: 120 ms per loop

In [489]: %%timeit
     ...: df.join(pd.get_dummies(df.Tourn).T.dot(pd.get_dummies(df.Player)), on='Tourn')
     ...: 
1 loop, best of 3: 895 ms per loop

In [490]: %%timeit
     ...: dd = df.Tourn.str.get_dummies()
     ...: df.assign(**{x.Player: dd[x.Tourn] for x in df.itertuples()})
     ...: 
1 loop, best of 3: 7.02 s per loop

In [491]: %%timeit
     ...: df.assign(**{x.Player:df.Tourn.eq(x.Tourn).astype(int) for x in df.itertuples()})
     ...: 
1 loop, best of 3: 13.7 s per loop

Caveat

The results do not address performance given the number of group and length of DataFrame, which will affect timings for some of these solutions.

piRSquared · Answer 2 · 2018-02-22T14:31:34.880

pd.get_dummies, pd.DataFrame.dot, and pd.DataFrame.join

I use dot to perform a cross tabulation. I engineer it such that Tourn values end up in the index and allow me to use join on that column.

df.join(pd.get_dummies(df.Tourn).T.dot(pd.get_dummies(df.Player)), on='Tourn')

   Player Tourn  Score  Ben  Harry  Henry  Ingram  Johno  Nick  Tom
0     Tom     a     65    1      1      1       1      1     0    1
1   Henry     a     72    1      1      1       1      1     0    1
2   Johno     a     69    1      1      1       1      1     0    1
3  Ingram     a     79    1      1      1       1      1     0    1
4     Ben     a     76    1      1      1       1      1     0    1
5   Harry     a     66    1      1      1       1      1     0    1
6    Nick     b     70    0      0      0       1      1     1    0
7  Ingram     b     79    0      0      0       1      1     1    0
8   Johno     b     69    0      0      0       1      1     1    0

Shameless Plug

See Post on pivoting for additional ways to crosstab

Nice idea, math guy think a bit differently I like it ;) – jezrael Feb 22 '18 at 15:19 — jezrael, Feb 22 '18 at 15:19

Zero · Answer 3 · 2018-02-22T14:25:07.450

You could do

Option 1 -- derived from piRSquared's dot

In [990]: df.join(pd.crosstab(df.Tourn, df.Player), on='Tourn')
Out[990]:
   Player Tourn  Score  Ben  Harry  Henry  Ingram  Johno  Nick  Tom
0     Tom     a     65    1      1      1       1      1     0    1
1   Henry     a     72    1      1      1       1      1     0    1
2   Johno     a     69    1      1      1       1      1     0    1
3  Ingram     a     79    1      1      1       1      1     0    1
4     Ben     a     76    1      1      1       1      1     0    1
5   Harry     a     66    1      1      1       1      1     0    1
6    Nick     b     70    0      0      0       1      1     1    0
7  Ingram     b     79    0      0      0       1      1     1    0
8   Johno     b     69    0      0      0       1      1     1    0

Option 2

In [976]: df.assign(**{x.Player:df.Tourn.eq(x.Tourn).astype(int) for x in df.itertuples()})
Out[976]:
   Player Tourn  Score  Ben  Harry  Henry  Ingram  Johno  Nick  Tom
0     Tom     a     65    1      1      1       0      0     0    1
1   Henry     a     72    1      1      1       0      0     0    1
2   Johno     a     69    1      1      1       0      0     0    1
3  Ingram     a     79    1      1      1       0      0     0    1
4     Ben     a     76    1      1      1       0      0     0    1
5   Harry     a     66    1      1      1       0      0     0    1
6    Nick     b     70    0      0      0       1      1     1    0
7  Ingram     b     79    0      0      0       1      1     1    0
8   Johno     b     69    0      0      0       1      1     1    0

Option 3

In [979]: dd = df.Tourn.str.get_dummies()

In [980]: df.assign(**{x.Player: dd[x.Tourn] for x in df.itertuples()})
Out[980]:
   Player Tourn  Score  Ben  Harry  Henry  Ingram  Johno  Nick  Tom
0     Tom     a     65    1      1      1       0      0     0    1
1   Henry     a     72    1      1      1       0      0     0    1
2   Johno     a     69    1      1      1       0      0     0    1
3  Ingram     a     79    1      1      1       0      0     0    1
4     Ben     a     76    1      1      1       0      0     0    1
5   Harry     a     66    1      1      1       0      0     0    1
6    Nick     b     70    0      0      0       1      1     1    0
7  Ingram     b     79    0      0      0       1      1     1    0
8   Johno     b     69    0      0      0       1      1     1    0

I was really curious if loopy solutions are fast. And it seems not. — jezrael, Feb 22 '18 at 15:28

score 0 · Answer 4 · answered Nov 02 '18 at 18:43

Came across a similar problem and found the best solution. Thanks to https://www.ritchieng.com/pandas-creating-dummy-variables/

In your case, the answer should be:

df['Tom(Dummy)'] = df.Tourn.map({'b':0, 'a':1})

Read it as:

# using .map to create dummy variables
# df['category_name  or new Dummy var. name '] = df.Category.map({'unique_term':0, 'unique_term2':1})

Hope it helps!

Conditional dummy variables in Pandas

4 Answers4