Reshape data with multiple results

Question

I'm trying to reshape my dataframe, but some of the fields have multiple results:

input = structure(list(Employee = structure(c(4L, 5L, 7L, 2L, 2L, 4L, 
6L, 2L, 3L, 8L, 8L, 1L, 7L, 2L), .Label = c("Arty", "Chumlee", 
"Francis", "John", "Randy", "Sara", "Tania", "Tony"), class = "factor"), 
    Workday = structure(c(2L, 2L, 2L, 2L, 2L, 7L, 8L, 6L, 1L, 
    4L, 4L, 3L, 5L, 5L), .Label = c("Friday", "Monday", "Satuday", 
    "Saturday", "Sunday", "Thursday", "Tuesday", "Wednesday"), class = "factor"), 
    Start = structure(c(6L, 1L, 6L, 2L, 4L, 6L, 1L, 2L, 5L, 7L, 
    3L, 1L, 6L, 1L), .Label = c("10:00", "10:30", "12:30", "15:00", 
    "8:30", "9:00", "9:30"), class = "factor"), Finish = structure(c(4L, 
    5L, 4L, 2L, 7L, 4L, 5L, 2L, 6L, 1L, 8L, 3L, 4L, 5L), .Label = c("12:00", 
    "12:30", "14:45", "15:00", "16:00", "16:30", "17:00", "20:00"
    ), class = "factor")), class = "data.frame", row.names = c(NA, 
-14L))

and I'm trying to make an output that looks like this:

output = structure(list(Employee = structure(c(5L, 6L, 3L, 7L, 4L, 9L, 
2L, 8L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", "Arty", "Chumlee", 
"Francis", "John", "Randy", "Sara", "Tania", "Tony"), class = "factor"), 
    Monday = structure(c(4L, 2L, 3L, 1L, 1L, 1L, 1L, 4L, 1L, 
    1L, 1L, 1L, 1L, 1L), .Label = c("", "10:00 - 16:00", "10:30 - 12:30; 15:00 - 17:00", 
    "9:00 - 15:00"), class = "factor"), Tuesday = structure(c(2L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", 
    "9:00 - 15:00"), class = "factor"), Wednesday = structure(c(1L, 
    1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", 
    "10:00 - 16:00"), class = "factor"), Thursday = structure(c(1L, 
    1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", 
    "10:30 - 12:30"), class = "factor"), Friday = structure(c(1L, 
    1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", 
    "8:30 - 16:30"), class = "factor"), Saturday = structure(c(1L, 
    1L, 1L, 1L, 1L, 3L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", 
    "10:00 - 14:45", "9:30 - 12:00; 12:30 - 20:00"), class = "factor"), 
    Sunday = structure(c(1L, 1L, 2L, 1L, 1L, 1L, 1L, 3L, 1L, 
    1L, 1L, 1L, 1L, 1L), .Label = c("", "10:00 - 16:00", "9:00 - 15:00"
    ), class = "factor")), class = "data.frame", row.names = c(NA, 
-14L))

The difficultly I'm having is that some employees do two shifts a day (eg. Chumlee), and I'd like to truncate that into one field.

I've no idea how to keep the data, and don't know if it's best to join the open and close first, and then try and reshape it?

score 0 · Answer 1 · answered Jul 21 '20 at 09:57

You can combine Start and Finish columns using unite, and summarise only one row for each Employee on each day and then convert the data to wide format.

library(dplyr)
library(tidyr)

input %>%
  unite(Time, Start, Finish, sep = " - ") %>%
  group_by(Employee, Workday) %>%
  summarise(Time = paste(Time, collapse = ";")) %>%
  pivot_wider(names_from = Workday, values_from = Time)

# Employee Satuday  Monday    Sunday  Thursday  Friday  Tuesday Wednesday Saturday  
#  <fct>    <chr>    <chr>     <chr>   <chr>     <chr>   <chr>   <chr>     <chr>     
#1 Arty     10:00 -… NA        NA      NA        NA      NA      NA        NA        
#2 Chumlee  NA       10:30 - … 10:00 … 10:30 - … NA      NA      NA        NA        
#3 Francis  NA       NA        NA      NA        8:30 -… NA      NA        NA        
#4 John     NA       9:00 - 1… NA      NA        NA      9:00 -… NA        NA        
#5 Randy    NA       10:00 - … NA      NA        NA      NA      NA        NA        
#6 Sara     NA       NA        NA      NA        NA      NA      10:00 - … NA        
#7 Tania    NA       9:00 - 1… 9:00 -… NA        NA      NA      NA        NA        
#8 Tony     NA       NA        NA      NA        NA      NA      NA        9:30 - 12…

score 0 · Answer 2 · answered Jul 21 '20 at 09:57

Using the tidyverse:

library(tidyverse)

input %>% 
  mutate(shift = str_glue("{Start} - {Finish}")) %>% 
  select(-Start, -Finish) %>% 
  pivot_wider(names_from = "Workday", values_from = "shift",
              values_fn = function(x) str_c(x, collapse = "; "),
              values_fill = "")

which gives

# A tibble: 8 x 9
  Employee Monday                Tuesday      Wednesday     Thursday      Friday      Saturday              Satuday     Sunday     
  <fct>    <chr>                 <chr>        <chr>         <chr>         <chr>       <chr>                 <chr>       <chr>      
1 John     "9:00 - 15:00"        "9:00 - 15:… ""            ""            ""          ""                    ""          ""         
2 Randy    "10:00 - 16:00"       ""           ""            ""            ""          ""                    ""          ""         
3 Tania    "9:00 - 15:00"        ""           ""            ""            ""          ""                    ""          "9:00 - 15…
4 Chumlee  "10:30 - 12:30; 15:0… ""           ""            "10:30 - 12:… ""          ""                    ""          "10:00 - 1…
5 Sara     ""                    ""           "10:00 - 16:… ""            ""          ""                    ""          ""         
6 Francis  ""                    ""           ""            ""            "8:30 - 16… ""                    ""          ""         
7 Tony     ""                    ""           ""            ""            ""          "9:30 - 12:00; 12:30… ""          ""         
8 Arty     ""                    ""           ""            ""            ""          ""                    "10:00 - 1… ""

Reshape data with multiple results

2 Answers2