CODEWITHSUNDEEP
r
Simplytif
Simplytif

Reputation: 51

merging two data frames with conditions

I'd like to merge 2 data.frames named df1 and df2, so I want to take rows from df2 and add them in df1 if the values from the 2 specific columns match, otherwise I'd keep the rows from df1. In other words, I don't want to have duplicated data. Here's an example from a long data.frames:

df1 <- data.frame(
    X = c(18,20,21,27,50),
    Y = c(4,5,6,9,8),
    Z = c(1,0.3,0.4,0.7,0.9)
)

df2 <- data.frame(
    X = c(20,40,50,),
    Y = c(1,4,8),
    Z = c(2.2,0.3,0.6)
)

Since the row 5 from df1 and the row 3 from df2 match in X and Y, I replace it, so I don't have to take Z in consideration.

So my df would be like this:

> mergedf
   X Y   Z
1 18 4 1.0
2 20 5 0.3
3 21 6 0.4
4 27 9 0.7
5 50 8 0.9 #The row from df1 instead of df2
6 20 1 2.2
7 40 4 0.3

Any help would be appreciated.

Upvotes: 0

Views: 1371

Answers (2)

MrFlick
MrFlick

Reputation: 206546

This should work

mm<-merge(df1, df2, all=T, by=c("X","Y"))
mm<-transform(mm,
    Z=ifelse(is.na(Z.x), Z.y, Z.x),
    Z.x=NULL,
    Z.y=NULL)
mm
#    X Y   Z
# 1 18 4 1.0
# 2 20 1 2.2
# 3 20 5 0.3
# 4 21 6 0.4
# 5 27 9 0.7
# 6 40 4 0.3
# 7 50 8 0.9

Here we merge all values from both data sets, then we fill in the missing Z values from the first table with those of the second table. Then we clean up the temp columns.

Upvotes: 1

Tyler Rinker
Tyler Rinker

Reputation: 110034

I think you just want rbind or rbind.data.frame, then unique on the desired columns, and use those rownames to index:

df3 <- rbind.data.frame(df1, df2)
df3[rownames(unique(df3[, -3])), ]

##    X Y   Z
## 1 18 4 1.0
## 2 20 5 0.3
## 3 21 6 0.4
## 4 27 9 0.7
## 5 50 8 0.9
## 6 20 1 2.2
## 7 40 4 0.3

Upvotes: 2

Related Questions