count by levels on multiple columns

Question

This is the extension here.
Data looks like:

ID   Type    Problem1    Value1     Problem2    Value2    Problem3    Value3
1    A       X           500        Y           1000      Z           400
2    A       X           600        Z           700       
3    B       Y           700        Z           100
4    B       W           200        V           200
5    C       Z           500        V           500       
6    C       X           1000       W           100       V           900

The result I want is:

Type    X     Y     Z     W     V
A       2     1     2     0     0  
B       0     1     1     1     1
C       1     0     1     1     2

I want to count the number under each groups, how can I do?

# data
dt <- fread("
ID   Type    Problem1    Value1     Problem2    Value2    Problem3    Value3
1    A       X           500        Y           1000      Z           400
2    A       X           600        Z           700       
3    B       Y           700        Z           100
4    B       W           200        V           200
5    C       Z           500        V           500       
6    C       X           1000       W           100       V           900", fill = T)

I try this:

dcast(melt(dt, measure = patterns("^Value", "^Problem"), 
        value.name = c("Value", "Problem"))[Problem != ""
      ][, Problem := factor(Problem, levels = c("X", "Y", "Z", "W", "V"))], 
       Type ~Problem, value.var = "Value", sum / mean, na.rm = TRUE)

However, it got error.

Frank · Accepted Answer · 2017-10-11T11:12:15.660

I'd store it in long form (similar to the OP's attempt)...

dt[Problem3 == "", Problem3 := NA]
mDT = melt(dt, 
  id = c("ID", "Type"), 
  meas = patterns("Problem", "Value"), 
  variable.name = "Item",
  value.name = c("Problem", "Value"),
  na.rm = TRUE
)

    ID Type Item Problem Value
 1:  1    A    1       X   500
 2:  2    A    1       X   600
 3:  3    B    1       Y   700
 4:  4    B    1       W   200
 5:  5    C    1       Z   500
 6:  6    C    1       X  1000
 7:  1    A    2       Y  1000
 8:  2    A    2       Z   700
 9:  3    B    2       Z   100
10:  4    B    2       V   200
11:  5    C    2       V   500
12:  6    C    2       W   100
13:  1    A    3       Z   400
14:  6    C    3       V   900

Then it's just casting to wide:

dcast(mDT, Type ~ Problem, fun.agg = length)

   Type V W X Y Z
1:    A 0 0 2 1 2
2:    B 1 1 0 1 1
3:    C 2 1 1 0 1

If you want the columns in a particular order or want to include unobserved levels of Problem, you can use a factor (as the OP did):

dcast(mDT, Type ~ factor(Problem, levels=c("X","Y","Z","W","V")), fun.agg = length)

# or more permanently

mDT[, Problem := factor(Problem, levels=c("X","Y","Z","W","V"))]
dcast(mDT, Type ~ Problem, fun.agg = length)

I got this `Using 'Problem' as value column. Use 'value.var' to override Error in make.unique(sapply(unlist(lvars), all.vars, max.names = 1L), : 'sep' must be a character string` — Peter Chen, Oct 12 '17 at 01:35
@PeterChen Hm, I'm not sure why that would happen. Is it for this example or a different one? Since someone upvoted your comment, I'm thinking it may be a data.table-version issue. I'm testing on 1.10.4 (which was the latest version on CRAN until last week) and seeing it work. — Frank, Oct 12 '17 at 07:01

score 1 · Answer 2 · answered Oct 11 '17 at 08:41

You can try a dplyr solution:

library(tidyverse)
dt %>% 
  select(Type, starts_with("Problem")) %>% 
  gather(key, value, -Type) %>% 
  group_by(Type) %>% 
  filter(!value=="") %>% 
  count(value) %>% 
  spread(value, n, fill = 0)
# A tibble: 3 x 6
# Groups:   Type [3]
   Type     V     W     X     Y     Z
* <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1     A     0     0     2     1     2
2     B     1     1     0     1     1
3     C     2     1     1     0     1

score 1 · Answer 3 · answered Oct 11 '17 at 08:48

# data
dt <- fread("
            ID   Type    Problem1    Value1     Problem2    Value2    Problem3    Value3
            1    A       X           500        Y           1000      Z           400
            2    A       X           600        Z           700       
            3    B       Y           700        Z           100
            4    B       W           200        V           200
            5    C       Z           500        V           500       
            6    C       X           1000       W           100       V           900", fill = T) 

dt <- dt[,c("Type", "Problem1", "Problem2", "Problem3")]
dt <- melt(dt, id = "Type")
cnt <- dt[,.(freq = .N), by = .(Type, value)]

mat <- reshape(cnt, idvar = "Type", timevar = "value", direction = "wide")
> mat
    Type freq.X freq.Y freq.W freq.Z freq.V freq.
1:    A      2      1     NA      2     NA     1
2:    B     NA      1      1      1      1     2
3:    C      1     NA      1      1      2     1

count by levels on multiple columns

3 Answers3