CODEWITHSUNDEEP
rmergeduplicates
lmcshane
lmcshane

Reputation: 1114

R: Merge duplicate matches horizontally

I am trying to merge 2 dataframes and have multiple matches append horizontally:

dataset1:

id
1 email1
1 email1b
2 email2
3 email3

dataset2:

id name
1 bob
2 rob
3 kat

I would like to use merge to combine these dataframes on id. When there are duplicate matches with id 1, I would like merge by "id" to return both results horizontally:

id name email 
1 bob email1 email1b
2 rob email2
3 kat email3

It doesn't seem like merge can do this, it creates multiple rows for duplicate values. Any other ideas?

Thanks! -R newbie

Upvotes: 0

Views: 430

Answers (2)

akrun
akrun

Reputation: 887501

UpdateNew

Assuming that the first dataset have two columns,

dat1 <- read.table(text="id email
1 email1
1 email1b
2 email2
3 email3",sep="",header=T,stringsAsFactors=F)

dat2 <- read.table(text="id name
1 bob
2 rob
3 kat",sep="",header=T, stringsAsFactors=F)

res <- aggregate(email~., data=merge(dat1, dat2, by="id"), FUN=paste, collapse=" ")
res[order(res$id),]
#   id name          email
#  1  1  bob email1 email1b
#  3  2  rob         email2
#  2  3  kat         email3
  • merge the two datasets by id
  • aggregate on the merged dataset so the emails belonging to the same ids are pasted horizontally in a row.

Upvotes: 1

J&#246;rg M&#228;der
J&#246;rg M&#228;der

Reputation: 677

This may work

id <- c(1, 1, 2, 3)
email <- c("email1", "email1b", "email2", "email3")
uid <- unique(id) 
data.frame(id=uid,email=unlist(lapply(uid,function(x) paste(email[x==id],collapse=' '))))

Upvotes: 0

Related Questions