Hi I want to select a group of values conditioned on a value in a data table.
Specifically I would like to select all columns grouped by date and id for all Positive values where e == 1
id date e logret
7 2011-07-29 1 -0.0272275211
7 2011-07-29 2 0.0034229025
7 2011-07-29 3 0.0042622177
8 2011-07-29 1 0.0035662770
8 2011-07-29 2 -0.0015268474
8 2011-07-29 3 0.0013333333
7 2011-07-30 1 0.0044444444
7 2011-07-30 2 -0.0001111111
7 2011-07-30 3 0.0013333333
here all elements for id 8 and date 2011-07-29 and all elements of id 7 for date 2011-07-30 will be selected since the logret for e == 1 is > 0 where as all elements of id 7 on 2011-07-29 are ignored since the first logret (where e==1) is < 0
Ans:
8 2011-07-29 1 0.0035662770
8 2011-07-29 2 -0.0015268474
8 2011-07-29 3 0.0013333333
7 2011-07-30 1 0.0044444444
7 2011-07-30 2 -0.0001111111
7 2011-07-30 3 0.0013333333
in sql I'd use some kind of subselect to achieve this. I would :
1) Select the id and date where e=1 and logret > 0
2) Select * join on results of subselect
I think data.table can do this as well, but I'm finding it tricky to express it in data.table terms. Specifically, I can replicate step 1, but can't do the join part in step 2.
pos <- DT[e==1][logret > 0]
But can't join the pos values back into my DT