After pivoting, collapsing new rows by common column values in R

Question

I have a data set containing 9 lab tests conducted for several patients(USUBJID), where each lab (LABCAT) had its own row and its respective day performed (LBDY). I want to transpose this so each lab is its own column and day performed is the value.

I performed a pivot as follows.

library(dplyr)
LB <- lb %>%
   select(USUBJID, LBCAT, LBDY)  %>%
   group_by(USUBJID) %>%
   mutate(visitnum = sequence(n())) %>%
   spread(key= LBCAT, value= LBDY)

This worked fine but now I want to merge the rows as a lot of these labs were conducted on the same days. So I want one row for all the labs conducted on day -21, one row for day -15, -2, etc. Is there an easy way to perform this task as I run into it occasionally and would like a long term solution.

Id like to the data to look like this

       Chemistry   Hematology Immunology   InfectiousDiseases Morphology ...
90301    -21          -21        NA           -21               -21
90301     NA          -15        NA            NA               -15
90301    -2           -2        -2             NA               -2

Thanks!

S Rivero S Rivero · Accepted Answer · 2017-08-16T22:58:47

Your code almost does that, just group by USUBJID and LBCAT.

LB <- lb %>%
   select(USUBJID, LBCAT, LBDY)  %>%
   group_by(USUBJID, LBCAT) %>%
   mutate(visitnum = sequence(n())) %>%
   spread(key= LBCAT, value= LBDY)

After pivoting, collapsing new rows by common column values in R

1 Answers