Replacing dataframe value given multiple condition from another dataframe with R

Question

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)

(1) Your example data is missing a jan column. (2) The values for jan are inconsistent, since it's 0.9 and 1 both in row 3. — Martin Gal
– Martin Gal, Commented Apr 14, 2022 at 15:34

Martin Gal · Accepted Answer · 2022-04-14 15:35:19Z

1

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))

answered Apr 14, 2022 at 15:35

Martin Gal

17k5 gold badges25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Gal Over a year ago

@akrun It's is.null. Subsetting the data.frame with non-existent columns returns NULL. But I actually don't understand, why this works here since we basically do df2[3, NA] which fails.

Wimpel · Accepted Answer · 2022-04-14 14:50:23Z

0

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>

answered Apr 14, 2022 at 14:50

Wimpel

27.9k1 gold badge25 silver badges40 bronze badges

Comments

akrun · Accepted Answer · 2022-04-14 16:16:40Z

0

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]))]

edited Apr 14, 2022 at 16:16

answered Apr 14, 2022 at 15:34

akrun

891k38 gold badges590 silver badges700 bronze badges

Collectives™ on Stack Overflow

Replacing dataframe value given multiple condition from another dataframe with R

3 Answers 3

Data

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Data

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related