Transforming dataframe into expanded matrix in r -

Transforming dataframe into expanded matrix in r -

say have next dataframe:

dfx <- data.frame(var1=c("a", "b", "c", "d", "b", "c", "d", "c", "d", "d"), var2=c("e", "e", "e", "e", "a", "a", "a", "b", "b", "c"), var1out = c(1,-1,-1,-1,1,-1,-1,1,-1,-1), var2out= c(-1,1,1,1,-1,1,1,-1,1,1)) dfx var1 var2 var1out var2out 1 e 1 -1 2 b e -1 1 3 c e -1 1 4 d e -1 1 5 b 1 -1 6 c -1 1 7 d -1 1 8 c b 1 -1 9 d b -1 1 10 d c -1 1

what see here 10 rows correspond match-ups between players a, b, c, d , e. play each other 1 time , winner of each match-up denoted +1 , loser of each match-up denoted -1 (put respective column player var1 result in var1out, player var2 result in var2out).

desired output.

i wish transform dataframe output matrix (the order of rows not of import me, can see each row refers unique match-up):

b c d e 1 1 0 0 0 -1 2 0 -1 0 0 1 3 0 0 -1 0 1 4 0 0 0 -1 1 5 -1 1 0 0 0 6 1 0 -1 0 0 7 1 0 0 -1 0 8 0 -1 1 0 0 9 0 1 0 -1 0 10 0 0 1 -1 0

what i've done:

i managed create matrix in roundabout way. roundabout ways tend slow , less satisfactory, wondering if can spot improve way.

i first made sure 2 columns containing players had factor levels contained every possible player ever occurs (you'll note instance player e never occurs in var1).

# making sure var1 , var2 have same factor levels levs <- unique(c(levels(dfx$var1), levels(dfx$var2))) #get possible levels of factors dfx$var1 <- factor(dfx$var1, levels=levs) dfx$var2 <- factor(dfx$var2, levels=levs)

i next split dataframe 2 - 1 var1 , var1out, , 1 var2 , var2out:

library(dplyr) temp.var1 <- dfx %>% select(var1, var1out) temp.var2 <- dfx %>% select(var2, var2out)

here utilize model.matrix expand columns factor level:

mat.var1<-with(temp.var1, data.frame(model.matrix(~var1+0))) mat.var2<-with(temp.var2, data.frame(model.matrix(~var2+0)))

i replace each row column '1' indicating presence of factor, right result , add together these matrices:

mat1 <- apply(mat.var1, 2, function(x) ifelse(x==1, x<-temp.var1$var1out, x<-0) ) mat2 <- apply(mat.var2, 2, function(x) ifelse(x==1, x<-temp.var2$var2out, x<-0) ) matx <- mat1+mat2 matx var1a var1b var1c var1d var1e 1 1 0 0 0 -1 2 0 -1 0 0 1 3 0 0 -1 0 1 4 0 0 0 -1 1 5 -1 1 0 0 0 6 1 0 -1 0 0 7 1 0 0 -1 0 8 0 -1 1 0 0 9 0 1 0 -1 0 10 0 0 1 -1 0

although works, have sense missing simpler solutions problem. thanks.

create empty matrix , utilize matrix indexing fill relevant values in:

cols <- unique(unlist(dfx[1:2])) m <- matrix(0, nrow = nrow(dfx), ncol = length(cols), dimnames = list(null, cols)) m[cbind(sequence(nrow(dfx)), match(dfx$var1, cols))] <- dfx$var1out m[cbind(sequence(nrow(dfx)), match(dfx$var2, cols))] <- dfx$var2out m # b c d e # [1,] 1 0 0 0 -1 # [2,] 0 -1 0 0 1 # [3,] 0 0 -1 0 1 # [4,] 0 0 0 -1 1 # [5,] -1 1 0 0 0 # [6,] 1 0 -1 0 0 # [7,] 1 0 0 -1 0 # [8,] 0 -1 1 0 0 # [9,] 0 1 0 -1 0 # [10,] 0 0 1 -1 0

r matrix