1

I thought this should be as simple task, but somehow it seems to be more complicated than expected.

What I want is the following:

  • I have a data frame with a character column
  • This data frame also contains a numeric column where the characters are mapped to a certain numbers
  • I want to append/join/update this numeric column based on an external lookup table for those cases in my data frame where the numeric column is NA.

Example:

x <- data.frame(string = c("A", "B", "C", "D", "D"),
                a1     = c(1, 2, 3, NA, NA))

y <- data.frame(string = c("D", "E", "F"),
                a1     = c("4", "5", "6"))

I tried:

library(tidyverse)
x %>%
  rows_update(., y, by = "string")

But this throws an error:

Error: x key values are not unique.

Expected outcome:

string   a1
     A    1
     B    2
     C    3
     D    4
     D    4

So how could I fill in an existing column based on a a lookup table - preferably in the tidyverse?

deschen
  • 10,012
  • 3
  • 27
  • 50
  • 1
    `x %>% left_join(y, by = 'string') %>% transmute(string, a1 = coalesce(a1.x, as.numeric(a1.y)))` How is this different from your earlier question https://stackoverflow.com/questions/65595893/tidyverse-alternative-to-left-join-rows-update-when-two-data-frames-differ-in ? – Ronak Shah Jan 13 '21 at 09:21
  • 1
    Thanks for your comment. It appears that I'M still struggling in connecting the dots between the different question and see the overall patterns. So the old question was about how to merge differing data frames, but the new one was about fill in missings. But I see how they are similar. So I'm happy to close this one. – deschen Jan 13 '21 at 09:24

0 Answers0