dynamic column names in data.table correlation

Question

I've combined the outputs for each user and item (for a recommendation system) into this all x all R data.table. For each row in this table, I need to calculate the correlation between user scores 1,2,3 & item scores 1,2,3 (e.g. for the first row what is the correlation between 0.5,0.6,-0.2 and 0.2,0.8,-0.3) to see how well the user and the item match.

user item user_score_1 user_score_2 user_score_3 item_score_1 item_score_2 item_score_3
 A     1       0.5          0.6         -0.2          0.2          0.8         -0.3
 A     2       0.5          0.6         -0.2          0.4          0.1         -0.8
 A     3       0.5          0.6         -0.2         -0.2         -0.4         -0.1
 B     1      -0.6         -0.1          0.9          0.2          0.8         -0.3
 B     2      -0.6         -0.1          0.9          0.4          0.1         -0.8
 B     3      -0.6         -0.1          0.9         -0.2         -0.4         -0.1

I have a solution that works - which is:

    scoresDT[, cor(c(user_score_1,user_score_2,user_score_3), c(item_score_1,item_score_2,item_score_3)), by= .(user, item)]

...where scoresDT is my data.table.

This is all well and good, and it works...but I can't get it to work with dynamic variables instead of hard coding in the variable names.

Normally in a data.frame I could create a list and just input that, but as it's character format, the data.table doesn't like it. I've tried using a list with "with=FALSE" and have had some success when trying basic subsetting of the data.table but not with the correlation syntax that I need...

Any help is much, much appreciated!

Thanks, Andrew

I've looked through ?melt and there is potentially something useful in there, but I still can't get the syntax right. I can get a single piece of logic to work, but the correlation has two sets of variables and I can't figure out where the with=FALSE should go in this case — andrewjones54

Frank Frank · Accepted Answer · 2015-11-03T12:36:39

Here's what I would do:

mDT = melt(scoresDT, 
   id.vars      = c("user","item"), 
   measure.vars = patterns("item_score_", "user_score_"), 
   value.name   = c("item_score", "user_score")
)

mDT[, cor(item_score, user_score), by=.(user,item)]


   user item         V1
1:    A    1  0.8955742
2:    A    2  0.9367659
3:    A    3 -0.8260332
4:    B    1 -0.6141324
5:    B    2 -0.9958706
6:    B    3  0.5000000

I'd keep the data in its molten/long form, which fits more naturally with R and data.table functionality.

dynamic column names in data.table correlation

1 Answers