User23 User23 - 3 months ago 10
R Question

Merge data frames while keeping length of one and values of other in R

I want to merge two data frames: X with length 10 (for example) and Y with length 3 but with similar row names. I only want to retain the length of X but with the values of B in the 3 corresponding rows and the other 10 - 3 = 7 values set to zero.

For example,

X<-data.frame(c(1,2,3,4,5,6,7,8,9,10))
rownames(X)<-c("a","b","c","d","e","f","g","h","i","j")
Y<-data.frame(c(20,30,40))
rownames(Y)<-c("d","f","h")


gives me these data frames

X Y
a 1 d 20
b 2 f 30
c 3 h 40
d 4
e 5
f 6
g 7
h 8
i 9
j 10


and I want this now

Z
a 0
b 0
c 0
d 20
e 0
f 30
g 0
h 40
i 0
j 0


Can this be done easily?

lmo lmo
Answer

We can use match to find the positions of the row names of Y that are found in X. The values of Y are put into a vector and concatenated with 0. We use the nomatch argument to fill in 0 when there is no match. This returns z as a vector:

Z <- c(unlist(Y, use.names=FALSE), 0)[match(row.names(X), row.names(Y), nomatch=4L)]
Z
[1]  0  0  0 20  0 30  0 40  0  0

To get a data.frame

Z <- data.frame(Z)