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
