6

I already reviewed the following two posts and think they might answer my question, although I'm struggling to see how:

1) Conditional replacement of values in a data.frame 2) Creating a function to replace NAs from one data.frame with values from another

With that said, I'm trying to replace NAs in one data frame by referencing another data frame of a different (shorter) length and pulling in replacement values from column "B" where the values for column "A" in each data frame match.

I've modified the data, below, for simplicity and illustration, although the concept is the same in the actual data. FYI, in the real second data frame, there are also no duplicates in column "A".

Here's the first data frame (df1):

> df1
    B          C  A
1  NA 2012-10-01  0
2  NA 2012-10-01  5
3   4 2012-10-01 10
4  NA 2012-10-01 15
5  NA 2012-10-01 20
6  20 2012-10-01 25
7  NA 2012-10-01  0
8  NA 2012-10-01  5
9   5 2012-10-01 10
10  5 2012-10-01 15

> str(df1)
'data.frame':   10 obs. of  3 variables:
 $ B: num  NA NA 4 NA NA 20 NA NA 5 5
 $ C: Factor w/ 1 level "2012-10-01": 1 1 1 1 1 1 1 1 1 1
 $ A: num  0 5 10 15 20 25 0 5 10 15

And the second data frame (df2).

> df2
   A         B
1  0 1.7169811
2  5 0.3396226
3 10 0.1320755
4 15 0.1509434
5 20 0.0754717
6 25 2.0943396

> str(df2)
'data.frame':   6 obs. of  2 variables:
 $ A: int  0 5 10 15 20 25
 $ B: num  1.717 0.3396 0.1321 0.1509 0.0755 ...

I think I'm pretty close with the following code:

> ifelse(is.na(df1$B) == TRUE, df2$B[df2$A == df1$A], df1$B)
 [1]  1.7169811  0.3396226  4.0000000  0.1509434  0.0754717 20.0000000         NA         NA
 [9]  5.0000000  5.0000000
Warning message:
In df2$A == df1$A :
  longer object length is not a multiple of shorter object length

Obviously, I want the 7th and 8th output elements to be 1.7169811 and 0.3396226, rather than NAs . . .

Thanks, in advance, for any help, and, once again, thanks for your patience!

heLL0
  • 1,357
  • 3
  • 23
  • 30
Daniel Fletcher
  • 1,165
  • 4
  • 13
  • 23
  • 3
    Not an answer, but `== TRUE` is not necessary in `is.na(df1$B) == TRUE`, since the other arguments in `ifelse` act as the statements when `is.na(df1$B)` evaluates to `TRUE` or `FALSE`. Just an fyi – Rich Scriven Jul 20 '14 at 04:21

3 Answers3

7

Try the following code which takes your original statement and makes a small tweak in the TRUE argument of the ifelse function:

> df1$B <- ifelse(is.na(df1$B) == TRUE, df2$B[df2$A %in% df1$A], df1$B)   
#                         Switched '==' to '%in%' ---^
> df1
            B          C  A
1   1.7169811 2012-10-01  0
2   0.3396226 2012-10-01  5
3   4.0000000 2012-10-01 10
4   0.1509434 2012-10-01 15
5   0.0754717 2012-10-01 20
6  20.0000000 2012-10-01 25
7   1.7169811 2012-10-01  0
8   0.3396226 2012-10-01  5
9   5.0000000 2012-10-01 10
10  5.0000000 2012-10-01 15
thelatemail
  • 91,185
  • 12
  • 128
  • 188
ccapizzano
  • 1,556
  • 13
  • 20
  • @ccapizzano Awesome. Thank you, sir! I had a feeling %in% might have had something to do with it. I felt like Voldemort in the 7th book when he's in the shrieking shack: "Soooo, close. Sooo close." Thanks, again. – Daniel Fletcher Jul 20 '14 at 04:48
  • @ccapizzano how would i change this ifelse statement to replace NAs where the values in more than one column are a match i.e. column A and C? – helen.h Oct 28 '19 at 10:43
7

You may also use:

df1$B[is.na(df1$B)] <- df2$B[match(df1$A[is.na(df1$B)],df2$A)]
df1

#             B          C  A
# 1   1.7169811 2012-10-01  0
# 2   0.3396226 2012-10-01  5
# 3   4.0000000 2012-10-01 10
# 4   0.1509434 2012-10-01 15
# 5   0.0754717 2012-10-01 20
# 6  20.0000000 2012-10-01 25
# 7   1.7169811 2012-10-01  0
# 8   0.3396226 2012-10-01  5
# 9   5.0000000 2012-10-01 10
# 10  5.0000000 2012-10-01 15
thelatemail
  • 91,185
  • 12
  • 128
  • 188
akrun
  • 874,273
  • 37
  • 540
  • 662
  • This seems like the most typical R-ish way to me. +1 – thelatemail Jul 20 '14 at 05:12
  • @thelatemail, why would you say this is the most "R-ish" way? – Daniel Fletcher Jul 24 '14 at 01:58
  • @DanielFletcher - just my subjective opinion - the answer uses simple subsetting and the pretty basic building block `match` function. I could see the answer being used in an introductory textbook. – thelatemail Jul 24 '14 at 03:36
  • @thelatemail, thanks. I'm currently taking a bunch of introductory _courses_ via the [Johns Hopkins Data Science Specialization on Coursera](https://www.coursera.org/specialization/jhudatascience/1) What are some introductory textbooks you'd recommend? Thanks, again. – Daniel Fletcher Jul 24 '14 at 03:54
  • @thelatemail. Good deal. Thanks! By the way, how do I say "thanks" to someone without breaking the stackoverflow etiquette of "don't just say 'thanks' in a comment, newb!"? – Daniel Fletcher Jul 24 '14 at 04:41
0
df1[is.na(df1$B), c('A','C','B')] <- merge(df1[is.na(df1$B), -1], df2, by = 'A')
Robert Krzyzanowski
  • 9,294
  • 28
  • 24