R: assign values to a column when two other columns match-CodePudding

Here is my sample data:

samp_df <- tibble(id= c("A", "A", "B", "B"),
              event= c(111, 112, 113, 114),
              values = c(23, 12, 45, 60),
              min_value = c(12, 12, 113, 113))

I would like to create a column in the dataframe that has the event of the min value. So in the example the column would look like: c(112, 112, 113, 113). So the idea is that I want to take the value from event whenever values and min_value match. It is important that this is also grouped by the id variable.

Here is what I tried, but it's not exactly right as it add NA's instead of the event:

samp_df <- samp_df %>% group_by(id) %>%
  mutate(event_with_min = if_else(min_value == value,
                                  event, NA_integer_)

A dplyr solution would also be optimal!

CodePudding user response：

library(tibble)
library(dplyr)
library(tidyr)

samp_df <- tibble(id= c("A", "A", "B", "B"),
                  event= c(111, 112, 113, 114),
                  values = c(23, 12, 45, 60))

samp_df %>% 
  group_by(id) %>%
  mutate(min_value = min(values),
         event_with_min = if_else(min_value == values, event, NA_real_)) %>% 
  fill(event_with_min, .direction = "downup")
#> # A tibble: 4 x 5
#> # Groups:   id [2]
#>   id    event values min_value event_with_min
#>   <chr> <dbl>  <dbl>     <dbl>          <dbl>
#> 1 A       111     23        12            112
#> 2 A       112     12        12            112
#> 3 B       113     45        45            113
#> 4 B       114     60        45            113

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

I've had to sign up as a guest as I am working on a train. Will revert to own user once back at home! Peter