0

I am trying to merge two databases: one has a list of patients that underwent surgery with their details and the other with the materials used. However, the second database has a new row for each material, which means there are several rows with the same patient ID. I would like to merge the databases to create one row per patient while automatically adding columns for the second database, so that no information is lost. Here is an example of my data:

Database 1:
PatientID Gender Age
1         Male   65
2         Male   77
3         Female 88
4         Female 83
5         Female 81
5         Male   88

Database 2:
PatientID  Material
1          A
1          B
1          C
2          A
2          B
3          A
3          B
3          C

Goal for merged database:

PatientID Gender Age Material1 Material2 Material3
1         Male   65  A         B         C
etc etc.

Is there a way in R to merge databases in this way so it automatically creates new columns for the duplicates in database 2?

Thanks!

I tried a simple merge function with all.x = TRUE but it did not create new columns.

Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
A McK
  • 1
  • You need to pivot database 2 from long format to wide format, then merge. You can see the FAQ on pivoting data from long to wide [here](https://stackoverflow.com/q/5890584/903061). – Gregor Thomas Jun 26 '23 at 20:01
  • With `dplyr` and `tidyr` you can do `db2 |> mutate(mat_id = row_number(), .by = PatientID) |> pivot_wider(names_from = mat_id, values_from = Material, names_prefix = "Material") |> full_join(db1, by = "PatientID")` – Gregor Thomas Jun 26 '23 at 20:03

0 Answers0