An efficient way to specify multiple indicator variables on a string using a compound key?

Question

My indicator objects and values ​​have composite keys that map to each other, is there an effective way to aggregate values ​​into an indicator object?

Given the "empty" indicator frame:

indicator <- data.frame(Id1=c(1,1,2,2,3,3,4,4), Id2=c(10,11,10,12,10,12,10,12),Ind_A=rep(0,8),Ind_B=rep(0,8)) Id1 Id2 Ind_A Ind_B 1 1 10 0 0 2 1 11 0 0 3 2 10 0 0 4 2 12 0 0 5 3 10 0 0 6 3 12 0 0 7 4 10 0 0 8 4 12 0 0 

and dataframe values:

 values <- data.frame(Id1=c(1,1,1,2,2,3,3,4,4,4),Id2=c(10,10,11,10,12,10,12,10,10,12),Indicators=c('Ind_A','Ind_B','Ind_A','Ind_B','Ind_A','Ind_A','Ind_A','Ind_A','Ind_B','Ind_A')); Id1 Id2 Indicators 1 1 10 Ind_A 2 1 10 Ind_B 3 1 11 Ind_A 4 2 10 Ind_B 5 2 12 Ind_A 6 3 10 Ind_A 7 3 12 Ind_A 8 4 10 Ind_A 9 4 10 Ind_B 10 4 12 Ind_A 

I want to end up with:

 Id1 Id2 Ind_A Ind_B 1 10 1 1 1 11 1 0 2 10 0 1 2 12 1 0 3 10 1 0 3 12 1 0 4 10 1 1 4 12 1 0 

Answer 1

You can use dcast to convert the "values" dataset from "long" to "wide" format.

 library(reshape2) dcast(values, Id1+Id2~Indicators, value.var='Indicators', length) # Id1 Id2 Ind_A Ind_B #1 1 10 1 1 #2 1 11 1 0 #3 2 10 0 1 #4 2 12 1 0 #5 3 10 1 0 #6 3 12 1 0 #7 4 10 1 1 #8 4 12 1 0 

As shown above, you may not need to create a second data set, but if you need to change the values ​​in one data set based on the value in another,

 indicator$Ind_A <- (do.call(paste, c(indicator[1:2], 'Ind_A')) %in% do.call(paste, values))+0L indicator$Ind_B <- (do.call(paste, c(indicator[1:2], 'Ind_B')) %in% do.call(paste, values))+0L