Return all rows when a condition is met for other columns [R]-CodePudding

Say I wanted to ask this question:

Which individuals are from planets and genders where there are any individuals that have blond hair and blue eyes?

I can do this easily enough in two steps.

create subset of individuals with blond hair and blue eyes
use the planets and genders present there to do another filter

If there a way that I can do this in one step using a dplyr-like approach?

library(dplyr, warn.conflicts = FALSE)
any_blond_blue <- starwars %>%
  group_by(homeworld, gender) %>%
  filter(eye_color == "blue", hair_color == "blond")


starwars %>%
  filter(
    homeworld %in% any_blond_blue$homeworld,
    gender %in% any_blond_blue$gender
  )
#> # A tibble: 9 × 14
#>   name      height  mass hair_color skin_color eye_color birth_year sex   gender
#>   <chr>      <int> <dbl> <chr>      <chr>      <chr>          <dbl> <chr> <chr> 
#> 1 Luke Sky…    172    77 blond      fair       blue            19   male  mascu…
#> 2 C-3PO        167    75 <NA>       gold       yellow         112   none  mascu…
#> 3 Darth Va…    202   136 none       white      yellow          41.9 male  mascu…
#> 4 Owen Lars    178   120 brown, gr… light      blue            52   male  mascu…
#> 5 R5-D4         97    32 <NA>       white, red red             NA   none  mascu…
#> 6 Biggs Da…    183    84 black      light      brown           24   male  mascu…
#> 7 Anakin S…    188    84 blond      fair       blue            41.9 male  mascu…
#> 8 Finis Va…    170    NA blond      fair       blue            91   male  mascu…
#> 9 Cliegg L…    183    NA brown      fair       blue            82   male  mascu…
#> # … with 5 more variables: homeworld <chr>, species <chr>, films <list>,
#> #   vehicles <list>, starships <list>

CodePudding user response：

Instead of filtering to create a new dataset, do the filter within the filter itself i.e. subset the values of 'homeworld', 'gender' based on the logical expression (eye_color == 'blue' & hair_color == 'blond'), then use %in% to create the logical expression for 'homeworld' and 'gender' to be used in filter

library(dplyr)
starwars %>% 
  filter(homeworld %in% homeworld[eye_color == 'blue' & hair_color == 'blond'], 
   gender %in% gender[eye_color == 'blue' & hair_color == 'blond'])

-output

# A tibble: 9 × 14
  name  height  mass hair_color skin_color eye_color birth_year sex   gender homeworld species films vehicles starships
  <chr>  <int> <dbl> <chr>      <chr>      <chr>          <dbl> <chr> <chr>  <chr>     <chr>   <lis> <list>   <list>   
1 Luke…    172    77 blond      fair       blue            19   male  mascu… Tatooine  Human   <chr> <chr>    <chr [2]>
2 C-3PO    167    75 <NA>       gold       yellow         112   none  mascu… Tatooine  Droid   <chr> <chr>    <chr [0]>
3 Dart…    202   136 none       white      yellow          41.9 male  mascu… Tatooine  Human   <chr> <chr>    <chr [1]>
4 Owen…    178   120 brown, gr… light      blue            52   male  mascu… Tatooine  Human   <chr> <chr>    <chr [0]>
5 R5-D4     97    32 <NA>       white, red red             NA   none  mascu… Tatooine  Droid   <chr> <chr>    <chr [0]>
6 Bigg…    183    84 black      light      brown           24   male  mascu… Tatooine  Human   <chr> <chr>    <chr [1]>
7 Anak…    188    84 blond      fair       blue            41.9 male  mascu… Tatooine  Human   <chr> <chr>    <chr [3]>
8 Fini…    170    NA blond      fair       blue            91   male  mascu… Coruscant Human   <chr> <chr>    <chr [0]>
9 Clie…    183    NA brown      fair       blue            82   male  mascu… Tatooine  Human   <chr> <chr>    <chr [0]>

Or slightly more compact and general approach would be using if_all/if_any as it can be applied to multiple columns. Here, we use if_all so that it returns TRUE only when both column conditions are TRUE

starwars %>%
   filter(if_all(c(homeworld, gender),
    ~ .x %in% .x[eye_color == 'blue' & hair_color == 'blond']))

-output

# A tibble: 9 × 14
  name  height  mass hair_color skin_color eye_color birth_year sex   gender homeworld species films vehicles starships
  <chr>  <int> <dbl> <chr>      <chr>      <chr>          <dbl> <chr> <chr>  <chr>     <chr>   <lis> <list>   <list>   
1 Luke…    172    77 blond      fair       blue            19   male  mascu… Tatooine  Human   <chr> <chr>    <chr [2]>
2 C-3PO    167    75 <NA>       gold       yellow         112   none  mascu… Tatooine  Droid   <chr> <chr>    <chr [0]>
3 Dart…    202   136 none       white      yellow          41.9 male  mascu… Tatooine  Human   <chr> <chr>    <chr [1]>
4 Owen…    178   120 brown, gr… light      blue            52   male  mascu… Tatooine  Human   <chr> <chr>    <chr [0]>
5 R5-D4     97    32 <NA>       white, red red             NA   none  mascu… Tatooine  Droid   <chr> <chr>    <chr [0]>
6 Bigg…    183    84 black      light      brown           24   male  mascu… Tatooine  Human   <chr> <chr>    <chr [1]>
7 Anak…    188    84 blond      fair       blue            41.9 male  mascu… Tatooine  Human   <chr> <chr>    <chr [3]>
8 Fini…    170    NA blond      fair       blue            91   male  mascu… Coruscant Human   <chr> <chr>    <chr [0]>
9 Clie…    183    NA brown      fair       blue            82   male  mascu… Tatooine  Human   <chr> <chr>    <chr [0]>