Thanks, edited the question. On Wed, Aug 17, 2011 at 3:53 AM, Matthew Dowle <[email protected]> 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" <[email protected]> wrote in message > news:cakxbdu_o3i_+xujsca0cmukdizctx6fnzbidoyzw9co9w9i...@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 > [email protected] > https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help >
-- Yang Zhang http://yz.mit.edu/ _______________________________________________ datatable-help mailing list [email protected] https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help
