[datatable-help] Basic join question

Yang Zhang yanghatespam at gmail.com
Wed Aug 17 11:28:15 CEST 2011


How do I do the equivalent to the following?

with dt as (select 1 as a, 0 as b union select 1, 0 union select 2, 0
union select 2, 1 union select 3, 1 union select 3, 1),
  above as (select a, b from dt where b > .5),
  below as (select a, b from dt where b < .5)
select above.a, count(below.a) from above left outer join below on
(above.a = below.a) group by above.a;
 a | count
---+-------
 3 |     0
 2 |     1
(2 rows)

How do I accomplish the same thing with data.tables?  This is what I
have so far:

DT = data.table(a=as.integer(c(1,1,2,2,3,3)), b=c(0,0,0,1,1,1))
above = DT[DT$b > .5]
below = DT[DT$b < .5, list(a=a)]
key(below) = 'a'
below[above, list(count=length(a)), by=a]

but this gives me:

      a count
[1,]  2 1
[2,] NA 1

Thanks in advance for any tips.

-- 
Yang Zhang
http://yz.mit.edu/


More information about the datatable-help mailing list