How to remove or subset samples based upon two different columns in R

user_5566568 0
Viewed 97

I have a df with 1000s of rows. Simplified it looks like this:

df1 <- data.frame(Sample = c('A1', 'B2', 'C3', 'A1', 'B2', 'C3','A1', 'B2', 'C3'),
                  Day = c('1', '3', '5', '4', '2', '6','5', '4', '2' ),
                  Value = c('0.1', '0.8', '0.7', '0.6', '0.3', '0.4', '0.4', '0.2', '0.3'))

I want to remove samples which occur on days after the max value was measured.

I can look at the max value and which days they fall on for each sample:

library(dplyr)
df2 <- df1 %>% 
             group_by(Value, Day, Sample) %>%
             slice(which.max(Value))

Then if I could remove rows for each sample which occurs on days after the maximum was measured I'd have the desired df:

df3 <- data.frame(Sample = c('A1', 'B2', 'C3', 'A1', 'B2', 'C3'),
                                    Day = c('1', '3', '5', '4', '2','2' ),
                                    Value = c('0.1', '0.8', '0.7', '0.6', '0.3', '0.3'))

This seems like it needs a for-loop, but I'm not firing on many cylinders today, so any help is much appreciated. TIA.

r for-loop
2 Answers

The example data was created with numeric columns as character (note the '0.1' instead of just 0.1), so we use type.convert to automatically convert the column types, arrange the data by 'Sample', 'Day', then grouped by 'Sample', get the index of max value of 'Value', add 1 and remove (-) the row with slice

library(dplyr)
df1 %>%
     type.convert(as.is = TRUE) %>%
     arrange(Sample, Day) %>% 
     group_by(Sample) %>%
     slice(-(which.max(Value) + 1)) %>%
     ungroup

-output

# A tibble: 6 x 3
  Sample   Day Value
  <chr>  <int> <dbl>
1 A1         1   0.1
2 A1         4   0.6
3 B2         2   0.3
4 B2         3   0.8
5 C3         2   0.3
6 C3         5   0.7

If we want to get rid of all rows after max (after arrangeing by 'Day', 'Sample')

df1 %>% 
    type.convert(as.is = TRUE) %>%
     arrange(Sample, Day) %>%
     group_by(Sample) %>% 
     filter(cumsum(cumsum(Value == max(Value, na.rm = TRUE))) <= 1) %>%
     ungroup

Or use slice with seq

df1 %>%
     type.convert(as.is = TRUE) %>%
     arrange(Sample, Day) %>% 
     group_by(Sample) %>%
     slice(seq(which.max(Value))) %>%
     ungroup
user_37322710

A base R option using ave -

df1 <- type.convert(df1, as.is = TRUE)
df1 <- df1[with(df1, order(Sample, Day)), ]

subset(df1, as.logical(ave(Value, Sample, 
            FUN = function(x) seq_along(x) <= match(max(x), x))))

#  Sample Day Value
#1     A1   1   0.1
#4     A1   4   0.6
#5     B2   2   0.3
#2     B2   3   0.8
#9     C3   2   0.3
#3     C3   5   0.7
user_39629140
Related