min or max by using filter

user_1024586 0
Viewed 52

A data frame:

mydf <- data.frame(
  date = seq(as.Date('2021-01-01'), as.Date('2021-01-20'), by = 'days'),
  blah = rnorm(20, 0),
  y = LETTERS[1:20]
)

I want to filter the date so that it goes all the way up to the maximum value of blah that's above 0.

Tried:

mydf %>% filter(date <= (. %>% filter(blah > 0) %>% pull(date) %>% max))

Which gave error:

Error: Problem with `filter()` input `..1`.
x comparison (4) is possible only for atomic and list types
ℹ Input `..1` is `date <= (. %>% filter(blah >= 0) %>% pull(date) %>% max)`.

How can I filter in this way?

dplyr r
3 Answers

Instead of piping out, it is easier to subset the 'date' where the 'blah' is greater than 0 and create the logical with date

library(dplyr)
mydf %>%
     filter(date <= max(date[blah >0]))
user_37322710

Using this random data:

set.seed(42)
mydf <- data.frame(
  date = seq(as.Date('2021-01-01'), as.Date('2021-01-20'), by = 'days'),
  blah = rnorm(20, 0),
  y = LETTERS[1:20]
)
mydf
#          date        blah y
# 1  2021-01-01  1.37095845 A
# 2  2021-01-02 -0.56469817 B
# 3  2021-01-03  0.36312841 C
# 4  2021-01-04  0.63286260 D
# 5  2021-01-05  0.40426832 E
# 6  2021-01-06 -0.10612452 F
# 7  2021-01-07  1.51152200 G
# 8  2021-01-08 -0.09465904 H
# 9  2021-01-09  2.01842371 I
# 10 2021-01-10 -0.06271410 J
# 11 2021-01-11  1.30486965 K
# 12 2021-01-12  2.28664539 L
# 13 2021-01-13 -1.38886070 M
# 14 2021-01-14 -0.27878877 N
# 15 2021-01-15 -0.13332134 O
# 16 2021-01-16  0.63595040 P
# 17 2021-01-17 -0.28425292 Q
# 18 2021-01-18 -2.65645542 R
# 19 2021-01-19 -2.44046693 S
# 20 2021-01-20  1.32011335 T

One can do:

mydf %>%
  filter(row_number() <= which.max(blah)) %>%
  pull(date)
#  [1] "2021-01-01" "2021-01-02" "2021-01-03" "2021-01-04" "2021-01-05" "2021-01-06" "2021-01-07" "2021-01-08" "2021-01-09"
# [10] "2021-01-10" "2021-01-11" "2021-01-12"
mydf %>%
  filter(row_number() <= which.max(blah))
#          date        blah y
# 1  2021-01-01  1.37095845 A
# 2  2021-01-02 -0.56469817 B
# 3  2021-01-03  0.36312841 C
# 4  2021-01-04  0.63286260 D
# 5  2021-01-05  0.40426832 E
# 6  2021-01-06 -0.10612452 F
# 7  2021-01-07  1.51152200 G
# 8  2021-01-08 -0.09465904 H
# 9  2021-01-09  2.01842371 I
# 10 2021-01-10 -0.06271410 J
# 11 2021-01-11  1.30486965 K
# 12 2021-01-12  2.28664539 L

That assumes that date is non-decreasing; if that's not a guarantee, then one can do:

mydf %>%
  filter(date <= date[which.max(blah)])
#          date        blah y
# 1  2021-01-01  1.37095845 A
# 2  2021-01-02 -0.56469817 B
# 3  2021-01-03  0.36312841 C
# 4  2021-01-04  0.63286260 D
# 5  2021-01-05  0.40426832 E
# 6  2021-01-06 -0.10612452 F
# 7  2021-01-07  1.51152200 G
# 8  2021-01-08 -0.09465904 H
# 9  2021-01-09  2.01842371 I
# 10 2021-01-10 -0.06271410 J
# 11 2021-01-11  1.30486965 K
# 12 2021-01-12  2.28664539 L
user_33582720

I got it to work by enclosing curly braces around the dot:

mydf %>% filter(date <= ({.} %>% filter(blah > 0) %>% pull(date) %>% max))
user_10245860
Related