[datatable-help] Basic join question

[Matthew Dowle]

Yang,
Since you also asked on SO, suggest we answer there (after your edit please) 
:
http://stackoverflow.com/questions/7090621/how-to-do-a-basic-left-outer-join-with-data-table-in-r
Matthew


"Yang Zhang" <yanghatespam at gmail.com> wrote in message 
news:CAKxBDU_o3i_+xujsCa0CmukDizctx6fnzbidOYZW9Co9w9iTvw at mail.gmail.com...
> 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/