0

I have a data table which could be reduced to this:

set.seed(1); 
dt<-data.table(form=c(1,1,1,2,3,3,3,4,4,5),
    mx=c("a","b","c","d","e","f","g","e","g","b"),
    vr=runif(10,100,200),
    usr=c("l","l","l","m","o","o","o","l","l","m"),
    type=c("A","A","A","C","C","C","C","C","C","A"))

I can generate a table with:

dt[,
    list(n.form=length(unique(form)),n.mx=length(unique(mx)),tot.vr=sum(vr)),
    by=usr]

What I haven't been able to to is to count the number of formulas of type A (each row is an observation, the form is the formula number). I've tried:

dt[,
    list(n.form=length(unique(form)),n.mx=length(unique(mx)),tot.vr=sum(vr),n.A=sum(type=="A"),
    by=usr]

and also:

dt[,
    list(n.form=length(unique(form)),n.mx=length(unique(mx)),tot.vr=sum(vr),n.A=length(unique(type=="A"))),
    by=usr]

but none of those takes into account the fact that the number of "A" found needs to be related to the unique formula (form) number.

What I'd like to have as a result is:

   usr n.form n.mx   tot.vr n.A
1:   l      2    5 750.0398   1
2:   m      2    2 296.9994   1
3:   o      1    3 504.4747   0

but I can't find a way to achieve it. Any light shed is much appreciated. Thanks,

======= EDIT TO ADD ========

I want to know how many of the formulas (unique numbers in dt$form) are of type "A" (so I can calculate a proportion out of total formulas). The direct number (sum) is the total number of observations of type A, while the existence (any) gives me if there was at least one formula of type "A", but not the number of formulas of that type (which is what I want). Please notice that any given formula will always be either of type "A" or "C" (not mixed types in one formula)

PavoDive
  • 6,322
  • 2
  • 29
  • 55

1 Answers1

6

In the devel version of data.table, you can use uniqueN instead of length(unique(.., 

library(data.table)#v1.9.5+
dt[,list(n.form=uniqueN(form), n.mx=uniqueN(mx),tot.vr=sum(vr),
            n.A=uniqueN(form[type=='A'])) , by = usr]
#    usr n.form n.mx   tot.vr n.A
#1:   l      2    5 750.0398   1
#2:   m      2    2 296.9994   1
#3:   o      1    3 504.4747   0
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Your code runs well on the provided data, but if I introduce this change: `dt$usr<-c(dt$usr[1:9],"l")` then it fails to add an additional formula (`form==5`) to the `n.A`, which should show now a 2. – PavoDive Jun 23 '15 at 21:49
  • @PavoDive I may have misunderstood your question. Sorry. – akrun Jun 23 '15 at 21:51
  • @PavoDive I updated the post. Can you check the results now. Based on the your changed code the `n.A` would be `2 0 0` – akrun Jun 23 '15 at 22:02