An efficient way to indicate multiple indicator variables per row with composite key?

Question

My indicator and value objects have composite keys that map to each other is there an efficient way to aggregate the values into the indicator object?

Given an "empty" indicator dataframe:

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 a dataframe of 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

akrun · Accepted Answer · 2015-05-14T18:02:01.163

You could 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 showed above, you may not need to create a second dataset, but if you need to change the values in one dataset based on the value in other,

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

An efficient way to indicate multiple indicator variables per row with composite key?

1 Answers1

Linked