How do I select a subset of rows after group by a specific column in R Data table

Question

I want to select a subset of rows based upon a condition after grouping by a specific column in R data table . Take Mtcars data for an example .

dt_mtcars <- as.data.table(mtcars)


dt_mtcars[,.N,by=.(hp)]

    hp  N
 1: 110 3
 2:  93 1
 3: 175 3
 4: 105 1
 5: 245 2
 6:  62 1
 7:  95 1
 8: 123 2
 9: 180 3
10: 205 1
11: 215 1
12: 230 1
13:  66 2
14:  52 1
15:  65 1
16:  97 1
17: 150 2
18:  91 1
19: 113 1
20: 264 1
21: 335 1
22: 109 1
     hp N

Now what I am trying to achieve is like selecting a sub set of data based on the count for e.g in here I want to have all the data in dt_mtcars where N >1 if I try something like this (which is conceptually what I am trying to achieve) it shows all the rows rather than getting the rows where N is greater than 1 .

dt_mtcars[.N>1,.SD,by=.(hp)]

Please assist .

score 3 · Accepted Answer · answered Dec 12 '15 at 04:44

3

We can do this using if

dt_mtcars[, if(.N>1) .SD ,hp]

answered Dec 12 '15 at 04:44

akrun

874,273
37
540
662

Shape · Answer 2 · 2015-12-14T18:50:39.410

2

You can do it in two steps:

dt_mtcars[,Count:=.N, by=.(hp)][Count>1, .SD, .SDcols = rev(rev(1:length(names(dt_mtcars)))[-1])]

EDIT: realized mtcars does not have just those two rows, updated SDcols

EDIT: Fixed to remove count

edited Dec 14 '15 at 18:50

answered Dec 11 '15 at 22:16

Shape

2,892
19
31

I dont want to create an extra column :( – Bg1850 Dec 11 '15 at 22:17
then just filter out the columns you want, edited the above – Shape Dec 11 '15 at 22:19
in anyway I am creating a column first and here mtcars I have used as an example ,in the actual data i have it has millions of rows where I want to avoid creating columns,unless thats my only option – Bg1850 Dec 11 '15 at 22:21
Do you care if you remove data from the original dataset? – black_sheep07 Dec 11 '15 at 22:24
3

Even with millions of rows, the creation of a new column by reference is very fast – Shape Dec 11 '15 at 22:24
You can also drop the added column in the last step. – Clayton Stanley Dec 12 '15 at 19:06
@Shape, not sure why the need to specify SDcols since `dt_mtcars[,Count:=.N, by=.(hp)][Count>1]` generates the same output. – fishtank Dec 14 '15 at 18:37
You're right, I assumed that it was using the pre-modified length of names(dt_mtcars), in order to exclude Count, but it is evaluating it afterwards – Shape Dec 14 '15 at 18:49

How do I select a subset of rows after group by a specific column in R Data table

2 Answers2

Linked

Related