CODEWITHSUNDEEP
rjoin
user10000933
user10000933

Reputation: 13

How do you merge tables by ID, then combine cells with the same ID

Table1: 

      ID Sex
1: 12345   M
2: 23456   M
3: 34567   F
4: 45678   F
5: 56789   F

Table2:

      ID  name
1: 12345   sam
2: 23456  jack
3: 23456   tom
4: 56789 steve
5: 56789   rob

I need to merge these two tables to get:

   ID Sex      name
1: 12345   M       sam
2: 23456   M  jack,tom
3: 34567   F          
4: 45678   F          
5: 56789   F steve,rob

I tried to use the Join function but it doesn't allow me to combine those names, is there a good way to solve this?

Upvotes: 0

Views: 91

Answers (2)

divibisan
divibisan

Reputation: 12165

This is a combination of three separate operations that have been covered in detail in other answers on this site:

  1. You want to join the tables together:

  2. then spread from long to wide format

  3. and finally paste those multiple 'name' columns together into a single name column

Upvotes: 0

akrun
akrun

Reputation: 887531

If these are data.tables, one option would be to paste the 'name' by 'ID' in the second dataset 'dt2' and join with the first ('dt1') on 'ID' and create the column 'name' in the first dataset

library(data.table)
dt1[dt2[, .(name = toString(name)), ID], name := name, on = .(ID)]
dt1
#      ID Sex       name
#1: 12345   M        sam
#2: 23456   M  jack, tom
#3: 34567   F       <NA>
#4: 45678   F       <NA>
#5: 56789   F steve, rob

Upvotes: 2

Related Questions