CODEWITHSUNDEEP
rdplyrtranspose
Kristine
Kristine

Reputation: 173

Transpose Rows into Corresponding Columns

I have a sample dataset with corresponding events like a "start" and "end" date by ID. However, the data isn't very clean. What is the best way to reshape this table in r with corresponding events? I tried doing this in dplyr and reshape but it didn't work so I thought taking the "next" row's value might work. I'm not sure that is the right approach though. For example, here's how the data currently looks:

  ID Event      Date
  1 Enter  1/1/2021
  1 Leave  1/5/2021
  2 Enter  5/4/2021
  2 Leave 5/30/2021
  2 Enter  6/2/2021
  2 Leave  6/5/2021

Desired output:

  ID Enter_Date   Leave_Date
  1 1/1/2021      1/5/2021
  2 5/4/2021      5/30/2021
  2 6/2/2021      6/5/2021

Here is a sample dataset:

ID <- c(1,1,2,2,2,2)
Event <- c("Enter", "Leave", "Enter", "Leave", "Enter", "Leave")
Date <- c("1/1/2021", "1/5/2021", "5/4/2021", "5/30/2021", 
          "6/2/2021", "6/5/2021")
df <- data.frame(ID, Event, Date)

Upvotes: 0

Views: 275

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388817

You need to create a column to distinguish each row in the final output. If your data follows the same pattern as your example you can create a new row for every two rows or for every value where Event == 'Enter'.

library(dplyr)
library(tidyr)

df %>%
  mutate(index_row = cumsum(Event == 'Enter')) %>%
  pivot_wider(names_from = Event, values_from = Date) %>%
  select(-index_row)

#     ID Enter    Leave    
#  <dbl> <chr>    <chr>    
#1     1 1/1/2021 1/5/2021 
#2     2 5/4/2021 5/30/2021
#3     2 6/2/2021 6/5/2021

Upvotes: 3

Related Questions