Replacing dataframe value given multiple condition from another dataframe with R-CodePudding

I have two dataframes one with the dates (converted in months) of multiple survey replicates for a given grid cell and the other one with snow data for each month for the same grid cell, they have a matching ID column to identify the cells. What I would like to do is to replace in the first dataframe, the one with months of survey replicates, the month value with the snow value for that month considering the grid cell ID. Thank you

CellID <- c(1,2,3,4,5,6)
sampl1 <- c("oct", "oct", "oct", "nov", NA, NA)
sampl2 <- c("nov", "nov", "jan", NA, NA, NA)
sampl3 <- c("dec", "dec", "jan", NA, NA, NA)
df1 <- data.frame(CellID, sampl1, sampl2, sampl3)
print(df1)

CellID <- c(1,2,3,4,5,6)
oct <- c(0.1, 0.1, 0.1, 0.1, 0.1, 0.1)
nov <- c(0.4, 0.5, 0.4, 0.5, 0.6, 0.5)
dec <- c(0.6, 0.7, 0.8, 0.7, 0.6, 0.8)
df2 <- data.frame(CellID, oct, nov, dec)
print(df2)

CellID <- c(1,2,3,4,5,6)
sampl1_snow <- c(0.1, 0.1, 0.1, 0.5, NA, NA)
sampl2_snow <- c(0.4, 0.5, 0.9, NA, NA, NA)
sampl3_snow <- c(0.6, 0.7, 1, NA, NA, NA)
df3 <- data.frame(CellID, sampl1_snow, sampl2_snow, sampl3_snow)
print(df3)

CodePudding user response：

You could use

library(purrr)
library(dplyr)

df1 %>% 
  mutate(
    across(
      starts_with("sampl"),
      ~imap_dbl(.x, ~ifelse(is.null(df2[.y, .x]), NA_real_, df2[.y, .x])),
      .names = "{.col}_snow"
      ),
    .keep = "unused"
    )

to get

  CellID sampl1_snow sampl2_snow sampl3_snow
1      1         0.1         0.4         0.6
2      2         0.1         0.5         0.7
3      3         0.1         0.9         0.9
4      4         0.5          NA          NA
5      5          NA          NA          NA
6      6          NA          NA          NA

Data

For df2 I used

structure(list(CellID = c(1, 2, 3, 4, 5, 6), oct = c(0.1, 0.1, 
0.1, 0.1, 0.1, 0.1), nov = c(0.4, 0.5, 0.4, 0.5, 0.6, 0.5), dec = c(0.6, 
0.7, 0.8, 0.7, 0.6, 0.8), jan = c(0, 0, 0.9, 0, 0, 0)), class = "data.frame", row.names = c(NA, 
-6L))

CodePudding user response：

df3 <- df1
df3[!is.na(df1)] <- df2[!is.na(df1)]
#   CellID sampl1 sampl2 sampl3
# 1      1    0.1    0.4    0.6
# 2      2    0.1    0.5    0.7
# 3      3    0.1    0.4    0.8
# 4      4    0.1   <NA>   <NA>
# 5      5   <NA>   <NA>   <NA>
# 6      6   <NA>   <NA>   <NA>

CodePudding user response：

In base R, we can use match

df1[-1] <- df2[-1][cbind(df1$CellID[col(df1[-1])], 
         match(as.matrix(df1[-1]), names(df2)[-1]))]