[datatable-help] Basic join question

[Yang Zhang]

Thanks, edited the question.

On Wed, Aug 17, 2011 at 3:53 AM, Matthew Dowle <mdowle at mdowle.plus.com> wrote:
> 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/
>
>
>
> _______________________________________________
> datatable-help mailing list
> datatable-help at lists.r-forge.r-project.org
> https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help
>



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