Partially collapse a long dataset into a wider dataset

user_2913622 0
Viewed 40

The data frame 'dat' below is a subset of a larger dataset composed of outcomes for multiple studies. Studies are indexed by 'srdr_id'. Each study may have more than one 'outcome'. For each 'outcome', studies have two treatment arms (arm_number 1 and 2 with arm names in 'arm') with outcome assessed at two time points, e.g. timepoint 0 or 3.

 dat <- structure(list(srdr_id = c("172600", "172600", "172600", "172600", 
    "172600", "172600", "172600", "172600"), arm_number = c(1L, 2L, 
    1L, 2L, 1L, 2L, 1L, 2L), arm = c("Fluoxetine_CBT_MI", "Placebo_CBT_MI", 
    "Fluoxetine_CBT_MI", "Placebo_CBT_MI", "Fluoxetine_CBT_MI", "Placebo_CBT_MI", 
    "Fluoxetine_CBT_MI", "Placebo_CBT_MI"), outcome = c("alcohol use days", 
    "alcohol use days", "alcohol use days", "alcohol use days", "BDI", 
    "BDI", "BDI", "BDI"), timepoint = c("0", "0", "3", "3", "0", 
    "0", "3", "3"), timepoint_units = c("months", "months", "months", 
    "months", "months", "months", "months", "months"), n = c(24, 
    26, 24, 26, 24, 26, 24, 26), mean = c(3.01, 3.19, 1.88, 1.87, 
    17.25, 22.12, 6.79, 10.46), sd = c(1.75, 1.35, 1.52, 1.43, 8.87, 
    7.5, 7.49, 10.8)), row.names = c(NA, -8L), class = c("tbl_df", 
    "tbl", "data.frame"))

My goal is to create a "wide" data set as shown below, where "pre" are the timepoint = 0 values of mean, sd and n, and "post" are the corresponding timepoint = 3 values.

I considered creating list column using group_split(srdr_ooutcome), then use pivot_wider() on each element of the list column. Appreciate suggestions regarding tidyverse or base R approaches.

enter image description here

tidyr r
1 Answers

We could group then pivot_wider

library(tidyverse)
dat %>%
  group_by(srdr_id, timepoint) %>% 
  pivot_wider(
    names_from= timepoint,
    values_from = c(mean, sd, n)
  ) %>% 
  setNames(gsub("_0", "_pre", names(.))) %>% 
  setNames(gsub("_3", "_post", names(.))) %>% 
  select(srdr_id, outcome, arm_number, arm, mean_pre, 
         mean_post, sd_pre, sd_post, n_pre, n_post)

Output:

  srdr_id outcome          arm_number arm               mean_pre mean_post sd_pre sd_post n_pre n_post
  <chr>   <chr>                 <int> <chr>                <dbl>     <dbl>  <dbl>   <dbl> <dbl>  <dbl>
1 172600  alcohol use days          1 Fluoxetine_CBT_MI     3.01      1.88   1.75    1.52    24     24
2 172600  alcohol use days          2 Placebo_CBT_MI        3.19      1.87   1.35    1.43    26     26
3 172600  BDI                       1 Fluoxetine_CBT_MI    17.2       6.79   8.87    7.49    24     24
4 172600  BDI                       2 Placebo_CBT_MI       22.1      10.5    7.5    10.8     26     26
user_133216470
Related