In R can I filter across all columns where at least 1 value meets the threshold

user_14903035 0
Viewed 166

Hello this is my first post- (not sure if I was meant to post here or github). I'm quite new to all this.

I want to keep rows where at least one value is above my set cutoff (2 in this case). I've achieved this in my example (molecule d gets removed) but I imagine there's a better way especially if I many samples. Is there a way to select all the but the first column and apply to the rest of the table?

I'm trying to clean up my data by removing things which were low across all my samples. I think the same result could be achieved by removing rows where everything is below 2?

> marker = c('a','b','c','d','e')
> sample1 = c(100,55,43,1.5,89)
> sample2 = c(98,45,22,1,0.5)
> sample3 = c(89,45,65,0.5,67)
> data = data.frame( 'Molecule' = marker, 'sam1' = sample1, 'sam2'= sample2, 'sam3' = sample3)
> data
  Molecule  sam1 sam2 sam3
1        a 100.0 98.0 89.0
2        b  55.0 45.0 45.0
3        c  43.0 22.0 65.0
4        d   1.5  1.0  0.5
5        e  89.0  0.5 67.0

> limit = 2
> data %>% filter(.[2] >limit | .[3] > limit | .[4] > limit)
  Molecule sam1 sam2 sam3
1        a  100 98.0   89
2        b   55 45.0   45
3        c   43 22.0   65
4        e   89  0.5   67

Thanks

dplyr r
2 Answers

With filter_at, this can be done. In the new version of dplyr, there is across, but it will look for all instead of any (|). Specify the columns of interest, if the column name patterns are all that starts_with 'sam', loop over those, wrap the logical expression in any_vars

library(dplyr)
data %>% 
  filter_at(vars(starts_with('sam')),  any_vars(. > limit))

-output

#    Molecule sam1 sam2 sam3
#1        a  100 98.0   89
#2        b   55 45.0   45
#3        c   43 22.0   65
#4        e   89  0.5   67

Or create a logical vector with rowSums in base R

data[rowSums(data[startsWith(names(data), 'sam')] > limit) > 0,]
user_37322710

You can use rowwise with c_across to replace filter_at:

library(dplyr)

data %>% 
  rowwise() %>% 
  filter(any(c_across(starts_with("sam")) > limit))

Output

# A tibble: 4 x 4
# Rowwise: 
  Molecule  sam1  sam2  sam3
  <fct>    <dbl> <dbl> <dbl>
1 a          100  98      89
2 b           55  45      45
3 c           43  22      65
4 e           89   0.5    67
user_27999410
Related