R: Return the date from a row if it falls between two dates and meets another criteria (using dplyr-CodePudding

I'm not sure why this has been so difficult, but i've exhausted my R knowledge. I'm trying to return the date from a column if it falls between two dates into a new column in R. it must be done through sql-friendly verbs (i.e. dplyr).

sample <- data.frame(
  id = c(1, 1, 2, 3, 4),
  paint = c('zwbc',
               'zbbb',
               'zwbs',
               'aass',
               'zwbc')
  date = c('2020-03-01',
                  '2020-04-01',
                  '2019-01-01',
                  '2019-12-31',
                  '2020-05-01',))

I've tried the following:

sam2 <- sample %>%
  group_by(id) %>% 
  mutate(flag = if_else(paint == 'zwbc', 1, 0)) %>%
  mutate(paint_date = if_else(flag == 1   (date > '2020-1-1' & date < '2020-1-1'), date, NULL)) %>%
  ungroup()

CodePudding user response：

Does this solve your problem?

library(tidyverse)

sample <- data.frame(
  id = c(1, 1, 2, 3, 4),
  paint = c('zwbc',
            'zbbb',
            'zwbs',
            'aass',
            'zwbc'),
  date = c('2020-03-01',
           '2020-04-01',
           '2019-01-01',
           '2019-12-31',
           '2020-05-01'))

sample %>%
  group_by(id) %>% 
  mutate(flag = if_else(paint == 'zwbc', 1, 0))
#> # A tibble: 5 × 4
#> # Groups:   id [4]
#>      id paint date        flag
#>   <dbl> <chr> <chr>      <dbl>
#> 1     1 zwbc  2020-03-01     1
#> 2     1 zbbb  2020-04-01     0
#> 3     2 zwbs  2019-01-01     0
#> 4     3 aass  2019-12-31     0
#> 5     4 zwbc  2020-05-01     1

# To exclude the bottom row (date > specified date cutoff)
sample %>%
  group_by(id) %>% 
  mutate(flag = if_else(paint == 'zwbc', 1, 0)) %>%
  mutate(paint_date = if_else(flag == 1 & date > '2020-01-01' & date < '2020-04-01', date, NULL)) %>%
  ungroup()
#> # A tibble: 5 × 5
#>      id paint date        flag paint_date
#>   <dbl> <chr> <chr>      <dbl> <chr>     
#> 1     1 zwbc  2020-03-01     1 2020-03-01
#> 2     1 zbbb  2020-04-01     0 <NA>      
#> 3     2 zwbs  2019-01-01     0 <NA>      
#> 4     3 aass  2019-12-31     0 <NA>      
#> 5     4 zwbc  2020-05-01     1 <NA>

^{Created on 2022-10-12 by the reprex package (v2.0.1)}