CODEWITHSUNDEEP
rdataframemerge
User23
User23

Reputation: 153

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?

Upvotes: 1

Views: 241

Answers (2)

M. Siwik
M. Siwik

Reputation: 497

dplyr have nice option for left_join. Code of it it's easy to read and explain.

X<-data.frame(V1 = c(1,2,3,4,5,6,7,8,9,10),
              KEY = c("a","b","c","d","e","f","g","h","i","j"),
              stringsAsFactors = F)


Y<-data.frame(V2 = c(20,30,40),
              KEY = c("d","f","h"),
              stringsAsFactors = F)

library(dplyr)

XandY <- X %>%
  left_join(Y, by = "KEY") %>%
  mutate(Z = ifelse(is.na(V2), 0, V2))

I lefted all of columns in XandY to show you all computation and results.

> XandY

   V1 KEY V2  Z
1   1   a NA  0
2   2   b NA  0
3   3   c NA  0
4   4   d 20 20
5   5   e NA  0
6   6   f 30 30
7   7   g NA  0
8   8   h 40 40
9   9   i NA  0
10 10   j NA  0

Upvotes: 1

lmo
lmo

Reputation: 38500

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)

Upvotes: 1

Related Questions