access columns by position plus conditional filter

user_8453123 0
Viewed 42

I have the following data frame:

df = structure(list(age = c("F", "F", "M", "M", "M", "F", "M", "M", 
"F", "F", "M", "M", "F", "F", "F", "F", "F", "M", "M", "F", "F"
), gender = c(52.8547945205479, 70.617475870193, 47.6986301369863, 
85.4876712328767, 56.0288204261033, 27.0219178082192, 40.8583963494959, 
24.6553462722298, 80.4027397260274, 55.6684931506849, 70.6904109589041, 
64.5095890410959, 45.5397260273973, 78.5909038861022, 42.4219178082192, 
44.0712328767123, 77.7068493150685, 70.5199279905761, 43.7178082191781, 
77.7205479452055, 74.972602739726)), row.names = c(NA, -21L), class = c("tbl_df", 
"tbl", "data.frame"))

I would like to filter, by gender, the ages which are greater than the mean age for that gender. But I would like to do this by column number rather than name.

So I tried:

df %>% group_by_at(1) %>% filter_at(vars(2) > mean(vars(2))

But this didn't work.

Any suggestions?

dplyr r symbols
2 Answers

when it is grouped by on one column, it excludes that from filter. try this

df %>% group_by(across(1)) %>%
  filter(across(1, ~ mean(.) <= .))

# A tibble: 11 x 2
# Groups:   age [2]
   age   gender
   <chr>  <dbl>
 1 F       70.6
 2 M       85.5
 3 M       56.0
 4 F       80.4
 5 M       70.7
 6 M       64.5
 7 F       78.6
 8 F       77.7
 9 M       70.5
10 F       77.7
11 F       75.0

In view of comments added i.e. scenario when it has more than one column, see it on one added dummy column.

df = structure(list(age = c("F", "F", "M", "M", "M", "F", "M", "M", 
                            "F", "F", "M", "M", "F", "F", "F", "F", "F", "M", "M", "F", "F"
), gender = c(52.8547945205479, 70.617475870193, 47.6986301369863, 
              85.4876712328767, 56.0288204261033, 27.0219178082192, 40.8583963494959, 
              24.6553462722298, 80.4027397260274, 55.6684931506849, 70.6904109589041, 
              64.5095890410959, 45.5397260273973, 78.5909038861022, 42.4219178082192, 
              44.0712328767123, 77.7068493150685, 70.5199279905761, 43.7178082191781, 
              77.7205479452055, 74.972602739726),
dummy = runif(21)), row.names = c(NA, -21L), class = c("tbl_df", 
                                                                                      "tbl", "data.frame"))

df
# A tibble: 21 x 3
   age   gender  dummy
   <chr>  <dbl>  <dbl>
 1 F       52.9 0.703 
 2 F       70.6 0.892 
 3 M       47.7 0.170 
 4 M       85.5 0.269 
 5 M       56.0 0.158 
 6 F       27.0 0.260 
 7 M       40.9 0.0818
 8 M       24.7 0.586 
 9 F       80.4 0.563 
10 F       55.7 0.995 
# ... with 11 more rows

df %>% group_by(across(1)) %>%
  filter(across(1, ~ mean(.) <= .))

# A tibble: 11 x 3
# Groups:   age [2]
   age   gender dummy
   <chr>  <dbl> <dbl>
 1 F       70.6 0.892
 2 M       85.5 0.269
 3 M       56.0 0.158
 4 F       80.4 0.563
 5 M       70.7 0.120
 6 M       64.5 0.980
 7 F       78.6 0.720
 8 F       77.7 0.342
 9 M       70.5 0.944
10 F       77.7 0.308
11 F       75.0 0.710
user_28848590

The _at/_all variants in dplyr have been superseded with across. Here's one way how you can do it.

library(dplyr)

df %>% 
   group_by(across(1)) %>% 
   filter(cur_data()[[1]] > mean(cur_data()[[1]])) %>% 
   ungroup

#   age   gender
#   <chr>  <dbl>
# 1 F       70.6
# 2 M       85.5
# 3 M       56.0
# 4 F       80.4
# 5 M       70.7
# 6 M       64.5
# 7 F       78.6
# 8 F       77.7
# 9 M       70.5
#10 F       77.7
#11 F       75.0

In filter, [[1]] is used because the grouped column is not included in cur_data().

user_39629140
Related