R Count of strings by two factors

Question

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

Is there a reason no to use `table(df$country_code, df$target)` ? — jakub, Sep 09 '16 at 12:31
Hello Steven and thanks for taking the time. I have tried data.table and the simple aggregate() summary() and even tried to use compare() from another package. I believe the solution might reside with dplyr or a version of apply but I am lost. — J. Doe., Sep 09 '16 at 12:35
@jakub this code does not give the output I am after. In fact the information I need is stripped away from the output of this code. I need to count how many time each of the X1 to X30 appear for each combination of country_code and target. — J. Doe., Sep 09 '16 at 12:44
With the data you gave, the `1-V1-X7` element which value should have? What if another row is added in which `X7` is in column `M2` rather than `M1`? The runs you are talking about are represented by the M1-M2-M3 columns? — nicola, Sep 09 '16 at 12:47

score 1 · Accepted Answer · answered Sep 09 '16 at 12:44

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

That's amazing. This works. The only thing I need to do is assign it to a name and export. — J. Doe., Sep 09 '16 at 13:01

score 1 · Answer 2 · answered Sep 09 '16 at 17:16

A data.table solution (similar structure to the dplyr + tidyr just with different syntax)

setDT(df)
df[, .SD
   ][, CountofRun := NULL
   ][, melt(.SD, id.vars=c('country_code', 'target'))
   ][, .N, .(country_code, target, value)
   ][, dcast(.SD, country_code + target ~ value, value.var='N', fill=0)
   ]

score -1 · Answer 3 · answered Sep 09 '16 at 12:48

This will get you part way there; you have the counts now it is just formatting:

> library(data.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.table(country_code,target,M1,M2,M3,CountofRun)
> 
> # melt the data for easier processing
> df_m <- melt(df, id.vars = c('country_code', 'target', 'CountofRun'))
> 
> # count
> df_count <- df_m[, 
+             .(count = sum(CountofRun)),
+             keyby = .(country_code, target, value)
+             ][value != "NA"]  # remove 'NA's
>             
> df_count
    country_code target value count
 1:            1     V1    X1     3
 2:            1     V1    X7     3
 3:            1     V2    X1     2
 4:            1     V2   X11     2
 5:            1     V2   X14     3
 6:            1     V2   X17     1
 7:            1     V3   X21     3
 8:            1     V3    X8     3
 9:            2     V1    X1     1
10:            2     V1   X22     2
11:            2     V1   X29     3
12:            2     V2   X18     2
13:            2     V2    X2     1
14:            2     V2   X22     2
15:            2     V2    X8     1
16:            2     V3   X22     3
17:            2     V3   X24     3
>

Thank you for this. For some reason the df_count fails for me with the error "unused argument (keyby = .(country_code, target, value))". I have all libs loaded. — J. Doe., Sep 09 '16 at 13:03

R Count of strings by two factors

3 Answers3

Linked