R Combine Multiple Rows of DataFrame by creating new columns and union values

Question

I have a dataframe in R that looks like this

 ID    APPROVAL_STEP   APPROVAL_STATUS   APPROVAL_DATE     APPROVER
1234      STEP_A          APPROVED        23-Jan-2019     John Smith
1234      STEP_B          APPROVED        21-Jan-2019      Jane Doe

I need it to look like this

 ID    STEP_A_STATUS  STEP_A_APPROVAL_DATE  STEP_A_APPROVER  STEP_B_STATUS  STEP_B_APPROVAL_DATE  STEP_B_APPROVER
1234     APPROVED         23-Jan-2019         John Smith       APPROVED        21-Jan-2019            Jane Doe

And of course, with the original dataframe, any of APPROVAL_STATUS, APPROVAL_DATE, or APPROVER can be NA.

What is the most elegant way to do this? I know how to do it by looping through the unique IDs, grabbing each row, creating new columns, etc.; but is there any way to do this in a more elegant way (e.g., using tidyverse)?

Answer 1

What you are asking is how to convert data from "long" form to "wide" form.

You can just use the reshape() function which is in the stats package.

#' I constructed your data frame here
x<-data.frame(ID=c(1234,1234),
          APPROVAL_STEP=c("STEP_A","STEP_B"),
          APPROVAL_STATUS=c("APPROVED","APPROVED"),
          APPROVAL_DATE=c("23-Jan-2019","21-Jan-2019"),
          APPROVER=c("John Smith","Jane Doe"))




#' Now to reshape the data

library(stats)
reshape(x, 
    timevar="APPROVAL_STEP",
    idvar="ID",
    sep="_", direction = "wide")

Your output should look like.

  ID APPROVAL_STATUS_STEP_A APPROVAL_DATE_STEP_A APPROVER_STEP_A APPROVAL_STATUS_STEP_B APPROVAL_DATE_STEP_B
1 1234               APPROVED          23-Jan-2019      John Smith               APPROVED          21-Jan-2019
  APPROVER_STEP_B
1        Jane Doe

The column names is a cosmetic issue, but that's the gist of it.

Hope this is helpful!