CODEWITHSUNDEEP
r
KT_1
KT_1

Reputation: 8494

Subset a dataframe based on identifying max and min values in a column (in R)

For a sample dataframe:

df1 <- structure(list(id = 1:21, region = structure(c(1L, 1L, 1L, 1L, 
                                                  2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 
                                                  4L), .Label = c("a", "b", "c", "d"), class = "factor"), weight = c(0.35, 
                                                                                                                     0.65, 0.99, 1.5, 3.2, 2.1, 1.3, 3.2, 1.3, 2, 0.6, 0.6, 0.6, 0.45, 
                                                                                                                     1, 1.2, 1.4, 2, 1.3, 1, 2), condition = c(0L, 1L, 0L, 1L, 0L, 
                                                                                                                                                               0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 1L, 1L, 1L, 0L, 0L
                                                                                                                     )), .Names = c("id", "region", "weight", "condition"), class = "data.frame", row.names = c(NA, 
                                                                                                                                                                                                                -21L))

I wish to exclude the regions which do not have either the highest or lowest number of 1s in the result variable by region. For example, I would normally do:

summary <- setDT(df)[,.(.result = weighted.mean((condition==1),
       w = weight)*100), by = region]

Which would give me: summary

   region  .result
1:      a 61.60458
2:      b 39.69466
3:      c 50.56180
4:      d 61.03896

Therefore I would subset regions c and d from the dataframe df.

Is it possible to do this in one step without having to manually look at a summary dataframe?

Upvotes: 0

Views: 741

Answers (1)

sebastian-c
sebastian-c

Reputation: 15395

My understanding is that you wish to exclude all values that are not the highest and lowest values. It can't be done as a one liner, but if you add the following, you should get what you want:

incl <- summary[c(which.min(.result), which.max(.result)),region]
newdf <- df1[region %in% incl,]
newdf

   id region weight condition
 1:  5      b   3.20         0
 2:  6      b   2.10         0
 3:  7      b   1.30         0
 4:  8      b   3.20         1
 5:  9      b   1.30         0
 6: 10      b   2.00         1
 7:  1      a   0.35         0
 8:  2      a   0.65         1
 9:  3      a   0.99         0
10:  4      a   1.50         1

Upvotes: 3

Related Questions