Create dummy variables in dataframe using for loop and apply lambda

Question

I like to compute dummy variables based on the existing values in a dataframe and a dictionary with certain "cut off" points. I wonder whether I need to get rid of the for loop in the example below. And if so, how?

The second answer in this Stackoverflow post compellingly urges to avoid looping through dataframes. In my real dataset (about 20.000 rows and 50 columns/variables) the "apply lambda" for the rows indeed performs much better than a for loop (which after a few hours still wasn't finished). Yet, the for loop for the columns doesn't really give a problem with time and it is handy to automatically name the variables.

Here is an example dataset

import pandas as pd
# create df
dict={'file':['a', 'b', 'c'],'t_0':[1.5, 2.5, 3.5], 't_1':[0.5, 1.5, 0]} 
df=pd.DataFrame(dict,index=['0', '1', '3'])
print(df)
# create dictionary with cut off points for dummy variable
d = dict={'t_0_cut_off':1, 't_1_cut_off':2}
print(d)

And here the for loop and apply lambda function which I use to compute the dummy variables.

for column in df.columns[-2:]:
    df[f'{column}_result']=df[f'{column}'].apply(lambda x: 1 if x> d[f'{column}_cut_off'] else 0)
df 

The results look like this (and appear as I expect)

  file  t_0  t_1  t_0_result  t_1_result
0    a  1.5  0.5           1           0
1    b  2.5  1.5           1           0
3    c  3.5  0.0           1           0

Answer 1

df.apply is essentially a loop and can be slower than vectorized methods. Here is a proposal without using apply:

First, we can rename the keys in the dictionary to match the column names in the dataframe:

d1 = {k.rsplit('_',2)[0]: v for k, v in d.items()}
#{'t_0': 1, 't_1': 2}

Next step is make a subset of your dataframe, since you are interested in second and subsequent columns; for this, use df.iloc. Then, using df.assign, we can assign the values from the dictionary in the columns we are interested in and compare them with the original values using df.gt, then convert the boolean value to int using df.astype . Finally, we can df.join it back to the original dataframe and use rsuffix argument to rename the columns to add "_result"

sub_df = df.iloc[:,1:] #subsets the dataframe leaving the first column out
out = df.join(sub_df.gt(sub_df.assign(**d1)).astype(int) , rsuffix='_result')

---

Final code looks like:

d1 = {k.rsplit('_',2)[0]: v for k, v in d.items()}
sub_df = df.iloc[:,1:]
out = df.join(sub_df.gt(sub_df.assign(**d1)).astype(int) , rsuffix='_result')
print(out)

  file  t_0  t_1  t_0_result  t_1_result
0    a  1.5  0.5           1           0
1    b  2.5  1.5           1           0
3    c  3.5  0.0           1           0

Answer 2

The concept of broadcasting is very important for vectorized computation in both numpy and pandas, in which a lower-dimentional array can be viewed as a higher-dimentional one for computation.

A direct improvement of your apply method is based on broadcasting of scalar values:

for column in df.columns[-2:]:
    df[f'{column}_result'] = (df[f'{column}'] > d[f'{column}_cut_off']).astype(int)

Instead of comparing row by row in Python, all comparisons are performed at once.

The solution can be further improved using broadcasting of 1D arrays:

col_names = [k.rstrip("_cut_off") for k in d.keys()]
df[list(d.keys())] = df[col_names].gt(list(d.values())).astype(int)

Side Remarks:

1. dict is a built-in class in Python and should not be used as user variable names.
2. The SO post you refer to provides several links related to the performance topic, such as this one. You may want to read more of those.