J. Doe. J. Doe. - 3 months ago 14
R Question

R Count of strings by two factors

I need some help. I have the following table:

country_code=c(1,1,1,1,1,1,2,2,2,2,2,2)
target=c('V1','V1','V2','V2','V3','V3','V1','V1','V2','V2','V3','V3')
M1=c('X7','X7','X14','X14','X8','X8','X29','X22','X2','X22','X22','X22')
M2=c('X1','X1','X17','X11','X21','X21','X1','X29','X8','X18','X24','X24')
M3=c('NA','NA','NA','X1','NA','NA','NA','NA','NA','NA','NA','NA')
CountofRun=c(1,2,1,2,1,2,1,2,1,2,1,2)
df<-data.frame(country_code,target,M1,M2,M3,CountofRun)


and I would like to get a frequency table for each country_code and target combination. So for instance if X7 appears in all three runs for country_code=1 and target=V1, X7 needs to be summed to 3. As you will see, I am only interested in counting the number of times each of the X1 to X30 appears in those 3 runs for each of 6 combinations of country_code and target. I cannot convert to numeric.

The ultimate table, hopefully will look like this

enter image description here

Answer

Maybe

library(dplyr)
library(tidyr)

df %>%
  select(-CountofRun) %>%
  gather(key, value, -(country_code:target)) %>%
  select(-key) %>%
  ftable(xtabs(~ country_code + target + value, data = .))

Which gives:

#                    value NA X1 X11 X14 X17 X18 X2 X21 X22 X24 X29 X7 X8
#country_code target                                                     
#1            V1            2  2   0   0   0   0  0   0   0   0   0  2  0
#             V2            1  1   1   2   1   0  0   0   0   0   0  0  0
#             V3            2  0   0   0   0   0  0   2   0   0   0  0  2
#2            V1            2  1   0   0   0   0  0   0   1   0   2  0  0
#             V2            2  0   0   0   0   1  1   0   1   0   0  0  1
#             V3            2  0   0   0   0   0  0   0   2   2   0  0  0