Convert the event-level dataset to the patient-level data in r

Question

I need to convert the event-level dataset to the patient-level data i.e. convert a long dataset to a wider one based on deidnum as a key variable. Additionally, I want to create columns for each produced event and its event time. In case the same patient has more than one event, consider the earliest event time.

Here is a similar data sample and my code:

df <- read.table(text = "deidnum,eventc,EVENTDT,MI_COMPLICATED
325107,MI,21,1
325107,New Rose Dyspnea Scale 2 or more,1468,NA
418351,New Rose Dyspnea Scale 2 or more,207,NA
839172,New Rose Dyspnea Scale 2 or more,1060,NA
839172,New Rose Dyspnea Scale 2 or more,1718,NA
1487422,MI,990,0
1487422,DEATH,1113,NA
1511165,MI,424,0
1511165,MI,608,1
1511165,New Rose Dyspnea Scale 2 or more,721,NA
", sep = ",", header = TRUE)

library(reshape2)
wide.df <- dcast(df, deidnum ~ eventc)
wide.df

The current output

  deidnum DEATH MI New Rose Dyspnea Scale 2 or more
1  325107     0  1                                1
2  418351     0  0                                1
3  839172     0  0                                2
4 1487422     1  1                                0
5 1511165     0  2                                1

The expected output:

Any advice will be greatly appreciated.

Answer 1

A tidyverse workflow:

library(tidyr)
library(dplyr)

df %>%
  slice_min(EVENTDT, by = c(deidnum, eventc)) %>%
  pivot_wider(id_cols = deidnum, names_from = eventc,
              values_from = c(eventc, EVENTDT),
              values_fn = list(eventc = length),
              values_fill = list(eventc = 0),
              unused_fn = first) %>%
  rename_with(~ sub("eventc_", "", .x), starts_with("eventc"))

# # A tibble: 5 × 8
#   deidnum    MI `New Rose Dyspnea Scale 2 or more` DEATH EVENTDT_MI `EVENTDT_New Rose Dyspnea Scale 2 or more` EVENTDT_DEATH MI_COMPLICATED
#     <int> <int>                              <int> <int>      <int>                                      <int>         <int>          <int>
# 1  325107     1                                  1     0         21                                       1468            NA              1
# 2  418351     0                                  1     0         NA                                        207            NA             NA
# 3  839172     0                                  1     0         NA                                       1060            NA             NA
# 4 1487422     1                                  0     1        990                                         NA          1113              0
# 5 1511165     1                                  1     0        424                                        721            NA              0

Note: unused_fn = first is for grouping by the id_cols columns (deidnum) then summarizing the unused columns (MI_COMPLICATED) using first() (assuming it has been ordered by EVENTDT).

Answer 2

merge it with a base reshape call.

reshape2::dcast(df, deidnum ~ eventc, value.var='MI_COMPLICATED', fun=length) |>
  merge(reshape(df, idvar='deidnum', timevar='eventc', direction='wide')) |>
  suppressWarnings()  ## warns for more than one event which is acc. to OP fine
#   deidnum DEATH MI New Rose Dyspnea Scale 2 or more EVENTDT.MI MI_COMPLICATED.MI
# 1  325107     0  1                                1         21                 1
# 2  418351     0  0                                1         NA                NA
# 3  839172     0  0                                2         NA                NA
# 4 1487422     1  1                                0        990                 0
# 5 1511165     0  2                                1        424                 0
#   EVENTDT.New Rose Dyspnea Scale 2 or more MI_COMPLICATED.New Rose Dyspnea Scale 2 or more
# 1                                     1468                                              NA
# 2                                      207                                              NA
# 3                                     1060                                              NA
# 4                                       NA                                              NA
# 5                                      721                                              NA
#   EVENTDT.DEATH MI_COMPLICATED.DEATH
# 1            NA                   NA
# 2            NA                   NA
# 3            NA                   NA
# 4          1113                   NA
# 5            NA                   NA

Answer 3

Here is one more tidyverse approach:

library(dplyr)
library(tidyr)

df %>%
  select(deidnum, eventc) %>%
  summarise(n = n(), .by = c(deidnum, eventc)) %>%
  pivot_wider(names_from = eventc, values_from = n, names_prefix = "", values_fill = 0) %>% 
  left_join(df %>%
              group_by(deidnum, eventc) %>%
              filter(EVENTDT == min(EVENTDT)) %>%
              ungroup() %>%
              pivot_wider(names_from = eventc, 
                          values_from = c(EVENTDT, MI_COMPLICATED), 
                          names_sep = "_") %>%
              arrange(deidnum) %>% 
              select(1:5), by = "deidnum"
            )

  deidnum    MI `New Rose Dyspnea Scale 2 or more` DEATH EVENTDT_MI `EVENTDT_New Rose Dyspnea Scale 2 or more` EVENTDT_DEATH MI_COMPLICATED_MI
    <int> <int>                              <int> <int>      <int>                                      <int>         <int>             <int>
1  325107     1                                  1     0         21                                       1468            NA                 1
2  418351     0                                  1     0         NA                                        207            NA                NA
3  839172     0                                  2     0         NA                                       1060            NA                NA
4 1487422     1                                  0     1        990                                         NA          1113                 0
5 1511165     2                                  1     0        424                                        721            NA                 0