Using R data.table to transform categorical columns by using the mean of a numeric column

0
votes

I am trying to use r data.table to perform the following calculation:

I have a table with categorical columns and one numerical column, such as

           cat1         cat2         cat3      target       
0           x            xy          xxx          1      
1           x            xx          xxx          1
2           x            xx          yyy          0
3           y            yx          yyy          1
4           y            yy          yyy          0
5           y            yy          yyy          1

I would like to calculate a table of the same shape, where, for each categorical variable, the level has been changed to the mean of the target column for that level.

i.e. the result for the above data.table would be

           cat1         cat2         cat3      target       
0          0.66         1            1          1      
1          0.66         0.5          1          1
2          0.66         0.5          0.5        0
3          0.66         1            0.5        1
4          0.66         0.5          0.5        0
5          0.66         0.5          0.5        1

Please, no solutions using regular r dataframes, only data.table, as I am doing this as an exercise to get better in using data.tables, thanks!!

2
rdata.table
"I am doing this as an exercise" - but you want us to serve you the entire solution? ;) - Henrik
Hahah fair point @Henrik. I mean I can do it in a for loop by calling dt[,mean(target),by=catx], and merging the result back to this data.table for each variable. Just wondering if data.table wizards out there have a more elegant solution. - SolidSnake

2 Answers

2
votes

Here's a way :

library(data.table)
vars <- names(dt)[names(dt)!='target']
setDT(dt)
dt[, (vars) := lapply(.SD, function(x) ave(target, x)), .SDcols = vars]

#    cat1 cat2 cat3 target
#1: 0.667  1.0  1.0      1
#2: 0.667  0.5  1.0      1
#3: 0.667  0.5  0.5      0
#4: 0.667  1.0  0.5      1
#5: 0.667  0.5  0.5      0
#6: 0.667  0.5  0.5      1

Note that default function in ave is mean which I don't apply here explicitly.

0
votes

Currently the solution I have has to rely on a for loop, but I guess its ok..

vars_to_change <- names(dt)[names(dt)!='target']
for (v in vars_to_change) {
 d_sub<-dt[,mean(target),by=v]
 setnames(d_sub,'V1',paste0(v,'_num'))

 dt <- merge(dt,d_sub,by=v)
}

dt=dt[,-..vars_to_change]