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

Question

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?

See [ask] and [mcve]. Show your research and your code, and the results you get. No one will write this for you. — , Jun 27 '18 at 16:44

score 2 · Accepted Answer · answered Jun 27 '18 at 16:37

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

divibisan · Answer 2 · 2018-06-27T18:22:25.493

0

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

You want to join the tables together:
- How to join (merge) data frames (inner, outer, left, right)?
then spread from long to wide format
- How to reshape data from long to wide format?
and finally paste those multiple 'name' columns together into a single name column
- How to combine two or more columns in a dataframe into a new column with a new name?

edited Jun 27 '18 at 18:22

answered Jun 27 '18 at 16:12

divibisan

11,659
11
40
58

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/20142834) – Matt Phillips Jun 27 '18 at 18:12
My understanding is that only applies when the links are to outside resources, not to other StackOverflow questions. – divibisan Jun 27 '18 at 18:24
On top of that you did extract the core of what needs to be done then linked to a resource which explains the details. I think that's fair. – Don Jun 27 '18 at 20:41

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

2 Answers2