How best to use R to reshape dataframe from long to wide and combine values

Question

I have a dataframe of about 2000 rows and 3 columns. In essence, I want to reshape this dataframe to be wider than longer. This is an example of my current data:

ID	Procedure	Date
D55	Sedation	01/01/2001
D55	Excision	01/01/2001
D55	Biopsy	01/01/2001
A66	Sedation	02/02/2001
A66	Excision	02/02/2001
T44	Sedation	03/03/2001
T44	Biopsy	03/03/2001
T44	Sedation	04/04/2001
T44	Excision	04/04/2001
G88	Sedation	05/05/2001
G88	Biopsy	05/05/2001
G88	Sedation	06/06/2001
G88	Excision	06/06/2001
G88	Sedation	07/07/2001
G88	Re-excision	07/07/2001

I want the each row to be one line for the ID, so I'd want to create something like this:

ID	Date 1	Procedure(s)	Date 2	Procedure(s)	Date 3	Procedure(s)
D55	01/01/2001	Sedation, Excision, Biopsy
A66	02/02/2001	Sedation, Excision
T44	03/03/2001	Sedation, Biopsy	04/04/2001	Sedation, Excision
G88	05/05/2001	Sedation, Biopsy	06/06/2001	Sedation, Excision	07/07/2001	Sedation, Re-excision

The majority of IDs all have the same date, but different procedures documented. There are a handful that came in for further procedures on subsequent dates. I can't see any that came in for more than 3 different dates, but a way to count the dates documented per ID would be useful.

I've tried using cast and dcast so far, but I'm not really getting anywhere. I'm very new to R, so any help would be greatly appreciated! Thanks for reading.

Jon Spring · Accepted Answer · 2021-10-22T02:28:45.723

library(tidyverse)
df %>%
  group_by(ID, Date) %>%
  summarize(Procedure = paste0(Procedure, collapse = ", ")) %>%
  mutate(col = row_number()) %>%
  ungroup() %>%
  pivot_wider(names_from = col, values_from = c(Date, Procedure))

This currently requires some reordering afterwards, which could be done like in this answer: https://stackoverflow.com/a/60400134/6851825

# A tibble: 4 x 7
  ID    Date_1 Date_2 Date_3 Procedure_1                Procedure_2        Procedure_3          
  <chr> <chr>  <chr>  <chr>  <chr>                      <chr>              <chr>                
1 A66   2/2/01 NA     NA     Sedation, Excision         NA                 NA                   
2 D55   1/1/01 NA     NA     Sedation, Excision, Biopsy NA                 NA                   
3 G88   5/5/01 6/6/01 7/7/01 Sedation, Biopsy           Sedation, Excision Sedation, Re-excision
4 T44   3/3/01 4/4/01 NA     Sedation, Biopsy           Sedation, Excision NA

How best to use R to reshape dataframe from long to wide and combine values

1 Answers1