replace na in a dataframe with value in other df r

Question

This is an example of my df:

x<-tibble::tribble(
  ~ID, ~Month, ~Value,
  "A",     1L,   100L,
  "A",     2L,   200L,
  "A",     3L,     NA,
  "A",     4L,   400L,
  "B",     1L,    50L,
  "B",     2L,    20L,
  "B",     3L,    30L,
  "B",     4L,     NA,
  "C",     1L,     NA,
  "C",     2L,    60L,
  "C",     3L,    70L,
  "C",     4L,    60L,
  "D",     1L,    60L,
  "D",     2L,    60L,
  "D",     3L,    60L,
  "D",     4L,    50L
  )

And I have another df with this values:

y<-tibble::tribble(
  ~Month, ~Value,
      1L,    50L,
      2L,   100L,
      3L,    20L,
      4L,    70L
  )

I need to replace the NA's in x with the values of the y df. This is what I expect.

xy<- tibble::tribble(
  ~ID, ~Month, ~Value,
  "A",     1L,   100L,
  "A",     2L,   200L,
  "A",     3L,    20L,
  "A",     4L,   400L,
  "B",     1L,    50L,
  "B",     2L,    20L,
  "B",     3L,    30L,
  "B",     4L,    70L,
  "C",     1L,    50L,
  "C",     2L,    60L,
  "C",     3L,    70L,
  "C",     4L,    60L,
  "D",     1L,    60L,
  "D",     2L,    60L,
  "D",     3L,    60L,
  "D",     4L,    50L
  )

Does anyone know how to do this? Thanks !

score 3 · Accepted Answer · answered Jun 16 '20 at 03:12

You can join the data and use coalesce to select non-NA value.

library(dplyr)

x %>%
  left_join(y, by = 'Month') %>%
  mutate(Value = coalesce(Value.x, Value.y)) %>%
  select(names(x))

In base R, you can merge and use ifelse to select non-NA value

transform(merge(x, y, by = 'Month'), 
          Value = ifelse(is.na(Value.x), Value.y, Value.x))[names(x)]

score 1 · Answer 2 · answered Jun 16 '20 at 04:55

1

Base R using match:

x$Value[which(is.na(x$Value))] <- y$Value[match(x$Month[which(is.na(x$Value))],
                                                y$Month)]

answered Jun 16 '20 at 04:55

hello_friend

5,682
1
11
15

score 0 · Answer 3 · answered Jun 16 '20 at 06:02

As Month in y is a sequence of 1:4 you can use Month directly for subsetting.

i <- is.na(x$Value)
#i <- which(is.na(x$Value)) #Alternative

x$Value[i] <-  y$Value[x$Month[i]]
#x$Value[i] <-  y$Value[match(x$Month[i], y$Month)] #Alternative in case Month is not a sequence

x
#   ID Month Value
#1   A     1   100
#2   A     2   200
#3   A     3    20
#4   A     4   400
#5   B     1    50
#6   B     2    20
#7   B     3    30
#8   B     4    70
#9   C     1    50
#10  C     2    60
#11  C     3    70
#12  C     4    60
#13  D     1    60
#14  D     2    60
#15  D     3    60
#16  D     4    50

replace na in a dataframe with value in other df r

3 Answers3

Linked