Replace values between two data frames in R
I am trying to solve a small question related to two data frames in R. I have two data frames D1
and D2
:
D1
ID I1 I2 I3
1 001 1 2 9
2 002 3 5 9
3 003 8 7 9
D2
ID I1 I2 I3
1 001 NA 1 NA
2 002 1 1 NA
3 003 NA NA 1
This data is generated by the following code:
D1=data.frame(ID=c("001","002","003"),I1=c(1,3,8),I2=c(2,5,7),I3=c(9,9,9),stringsAsFactors=FALSE)
D2=data.frame(ID=c("001","002","003"),I1=c(NA,1,NA),I2=c(1,1,NA),I3=c(NA,NA,1),stringsAsFactors=FALSE)
There D2
are rows with NA
and not NA
values in the Dataframe . So I want to replace the non values NA
in D2
my corresponding value in D1
. For example, in the first row, the D2
second column is not NA
, so this value should be replaced with 2
from D2
. I tried to plot a matrix with values not NA
in D2
with this code:
mm=!is.na(D2[-1])
I1 I2 I3
[1,] FALSE TRUE FALSE
[2,] TRUE TRUE FALSE
[3,] FALSE FALSE TRUE
But when I try to replace with code like this D1[mm]
, I don't get the expected result. I would like to get something like this:
ID I1 I2 I3
1 001 NA 2 NA
2 002 3 5 NA
3 003 NA NA 9
Thank!
source to share
@ DatamineR's solution is the first one I thought of, but it has the unfortunate effect of indexing data.frames as vectors, which promotes the atomic type to a character (due to the ID column) which you may not want.
Here's an alternative that preserves the numeric values of columns I1-3:
aggregate(.~ID,rbind(D1,D2),function(a) if (is.na(a[2])) NA else a[1],na.action=na.pass);
## ID I1 I2 I3
## 1 001 NA 2 NA
## 2 002 3 5 NA
## 3 003 NA NA 9
source to share