vendredi 16 octobre 2020

Assign new value to database based on value stored in another database [duplicate]

Here I share with you a simplified version of my issue. Say I have 6 observations (pid) for two variables:

    pid <- c(1,2,3,4,5,6)
    V1 <- c(11,11,33,11,22,33)
    V2 <- c("A", "C", "M", "M", "A", "A")
    data <- data.frame(pid, V1, V2)
# pid V1 V2
# 1   1 11  A
# 2   2 11  C
# 3   3 33  M
# 4   4 11  M
# 5   5 22  A
# 6   6 33  A

I would like to create a new column based on the values associated to the different combinations I have of V1 and V2, that stored in a second database:

V1 <- c(11,11,11,22,22,22,33,33,33)
V2 <- c("A", "C", "M","A", "C", "M","A", "C", "M")
valueA <- c(16,26,36,46,56,66,76,86,96)
valueB <- c(15,25,35,45,55,65,75,85,95)
values <- data.frame(V1, V2, valueA, valueB)
# V1 V2 valueA valueB
# 1 11  A     16     15
# 2 11  C     26     25
# 3 11  M     36     35
# 4 22  A     46     45
# 5 22  C     56     55
# 6 22  M     66     65
# 7 33  A     76     75
# 8 33  C     86     85
# 9 33  M     96     95

I tried this:

data <- mutate (data, 
                valueA = as.integer (ifelse(data$V1 ==  values$V1
                                            & data$V2 == values$V2, values$valueA, NA))
                )

But it compares the raws sequentially, rather than looking for the value corresponding to a certain association in values I also get the following error:

Error: Problem with `mutate()` input `valueA`.
x Input `valueA` can't be recycled to size 6.
ℹ Input `valueA` is `as.integer(...)`.
ℹ Input `valueA` must be size 6 or 1, not 9.
Run `rlang::last_error()` to see where the error occurred.
In addition: Warning messages:
1: In data$V1 == values$V1 :
  longer object length is not a multiple of shorter object length
2: In `==.default`(data$V2, values$V2) :
  longer object length is not a multiple of shorter object length
3: In is.na(e1) | is.na(e2) :
  longer object length is not a multiple of shorter object length

I would like to archive this:

#   pid V1 V2 valueA
# 1   1 11  A     16
# 2   2 11  C     26
# 3   3 33  M     96
# 4   4 11  M     36
# 5   5 22  A     46
# 6   6 33  A     76

Any help would me much appreciated!

Aucun commentaire:

Enregistrer un commentaire