[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