Filtering by multiple columns at once in `dplyr`

Question

Here is some sample data

library(tidyverse)

data <- matrix(runif(20), ncol = 4) 
colnames(data) <- c("mt100", "cp001", "cp002", "cp003")
data <- as_tibble(data)

The real data set has many more columns but it stands that there are many columns that all start with "cp". In dplyr I can select all these columns

data %>%
  select(starts_with("cp"))

Is there a way in which I can use the starts_with (or similar function) to filter by multiple columns without having to explicitly write them all? I'm thinking something like this

data %>%
  filter(starts_with("cp") > 0.2)

Thanks!

Does this answer your question? https://stackoverflow.com/a/66908277/6619692 — Anil, Jan 27 '22 at 20:09
Does this answer your question? [dplyr filter with condition on multiple columns](https://stackoverflow.com/questions/43938863/dplyr-filter-with-condition-on-multiple-columns) — LMc, Aug 18 '22 at 21:18

TarJae · Accepted Answer · 2022-01-27T21:05:40.200

16

We could use if_all or if_any as Anil is pointing in his comments: For your code this would be:

https://www.tidyverse.org/blog/2021/02/dplyr-1-0-4-if-any/

if_any() and if_all()

"across() is very useful within summarise() and mutate(), but it’s hard to use it with filter() because it is not clear how the results would be combined into one logical vector. So to fill the gap, we’re introducing two new functions if_all() and if_any()."

if_all

data %>% 
  filter(if_all(starts_with("cp"), ~ . > 0.2))

  mt100 cp001 cp002 cp003
  <dbl> <dbl> <dbl> <dbl>
1 0.688 0.402 0.467 0.646
2 0.663 0.757 0.728 0.335
3 0.472 0.533 0.717 0.638

if_any:

data %>% 
  filter(if_any(starts_with("cp"), ~ . > 0.2))

  mt100 cp001   cp002 cp003
  <dbl> <dbl>   <dbl> <dbl>
1 0.554 0.970 0.874   0.187
2 0.688 0.402 0.467   0.646
3 0.658 0.850 0.00813 0.542
4 0.663 0.757 0.728   0.335
5 0.472 0.533 0.717   0.638

edited Jan 27 '22 at 21:05

answered Jan 27 '22 at 21:02

TarJae

72,363
6
19
66

2

I believe op needs `if_all` which will give similar results as `across` – Onyambu Jan 27 '22 at 21:04
Thank you very much Onyambu. I have updated my answer! – TarJae Jan 27 '22 at 21:06
1

Thanks very much! I'm new(ish) to `dplyr` (I used to do everything manually) and it's great that there's a function for almost everything I need to do! – Hugh Warden Jan 27 '22 at 23:10

score 6 · Answer 2 · answered Jan 27 '22 at 20:06

6

You can use dplyr::across() along with a purrr-style anonymous function:

data %>%
  filter(across(starts_with("cp"), ~ . > .2))
# # A tibble: 3 × 4
#   mt100 cp001 cp002 cp003
#   <dbl> <dbl> <dbl> <dbl>
# 1 0.628 0.604 0.802 0.501
# 2 0.744 0.283 0.702 0.493
# 3 0.279 0.372 0.975 0.751

(Note that without a set.seed(), our results will differ due to the RNG.)

answered Jan 27 '22 at 20:06

Gregor Thomas

136,190
20
167
294

1

This will require that any resulting rows satisfy that condition in ALL the columns right? – Dan Adams Jan 27 '22 at 20:08
1

Correct. But don't trust me - try it out on a little sample data! – Gregor Thomas Jan 27 '22 at 20:44

Filtering by multiple columns at once in `dplyr`

2 Answers2

Linked