Replace values from rows with specific values from another dataframe in R

Question

I have a dataframe df df dataframe with three columns: ID, Range, and Min_Range (this last column is the result I'm looking for). I want to obtain the df$Min_Range column by replacing non-NA values in df$Range with their corresponding (to ID) Min_Range_df$Min_Range value from the Min_Range_df dataframe Min_Range_df dataset. Another way to look at it is that I want to get the minimum df$Range by group (ID) and replace the Range values with the minimum.

df <- matrix(data=c(1,1,1,2,2,3,3,3,3,4,4,10,15,20,30,35,40,45,50,NA,NA,NA,10,10,10,30,30,40,40,40,NA,NA,NA), ncol = 3)
colnames(df) <- c("ID", "Range", "Min_Range")

Min_Range_df <- matrix(data= c(1,2,3,10,30,40), ncol=2)
colnames(Min_Range_df) <- c("ID", "Min_Range")

Thank you!!

Answer 1

You could use dplyr:

df %>%
  group_by(ID) %>%
  mutate(Min_Range_New = ifelse(is.na(Range), NA, min(Range, na.rm=TRUE)))

which returns

      ID Range Min_Range Min_Range_New
   <dbl> <dbl>     <dbl>         <dbl>
 1     1    10        10            10
 2     1    15        10            10
 3     1    20        10            10
 4     2    30        30            30
 5     2    35        30            30
 6     3    40        40            40
 7     3    45        40            40
 8     3    50        40            40
 9     3    NA        NA            NA
10     4    NA        NA            NA
11     4    NA        NA            NA