Walde Walde - 1 month ago 10
R Question

Merge two data frames in r (updating and dropping values)

I have two data frames:

data <- data.frame(ID1 = c("D1","D1","D1","D1","D2","D2"),
ID3 = c("A","A","D","D","B","C"),
Attribute = c("KL","KM","KL","KM","AB","AB"),
Value = c(2,2,3,3,5,7))


data_new <- data.frame(ID1 = c("D1","D1","D2","D2"),
ID2 = c("D1-1","D1-1","D2-1","D2-1"),
Attribute = c("KL","TA","AB","AA"))


Now I would like to incorporate the
Values
and
ID3
from data in my new data frame. In addition I would like to drop some attributes and add new ones. The output should look as follows:

expected_output <- data.frame(ID1 = c("D1","D1","D1","D1","D2","D2","D2","D2"),
ID2 = c("D1-1","D1-1","D1-1","D1-1","D2-1","D2-1","D2-1","D2-1"),
ID3 = c("A","A","D","D","B","B","C","C"),
Attribute = c("KL","TA","KL","TA","AB","AA","AB","AA"),
Value = c(2,2,3,3,5,5,7,7))


Can anyone help me on this?

What I tried so far is a simple merge.

mergeddata <- merge(data_new,data,
by.x = c("ID1"),
by.y = c("ID1"))


The outcome looks promising for
ID1 = D2
I would just have to drop the column
Attribute.x
". However
ID = D1
is not what I want. Rows 5-8 should not be in the new table anymore.

any help?

Answer

I think you could try the following:

mergeddata <- merge(data_new ,unique(data[, c("ID1", "ID3", "Value")]), by = "ID1")
Comments