Converting / transposing a crosstab of nominal variables to a dataframe with single cases in r

Question

I have a cross table saved in Excel with information about employees and how many times they were on a given shift.

> my_data
   ...1 night day
1  jeff     2   3
2  stan     3   2
3 annie     1   4

here's dput()

structure(list(...1 = c("jeff", "stan", "annie"), night = c(2, 
3, 1), day = c(3, 2, 4)), row.names = c(NA, -3L), class = "data.frame")

I need to make it a classic dataframe (conver? transpose?), where each case in the crosstable will be a separate case in the dataframe, to add information about working hours:

> my_data2
  worker shift hours
1   jeff night    NA
2   jeff night    NA
3   jeff   day    NA
4   jeff   day    NA
5   jeff   day    NA
6   stan night    NA
7      …     …    NA

Is there any simple way in R to do this?

Answer 1

Probably you can try

library(tidyverse)

my_data %>%
  rename(worker = ...1) %>%
  pivot_longer(cols = -worker, names_to = "shift") %>%
  uncount(value) %>%
  mutate(hours = NA_POSIXct_)

which gives

# A tibble: 15 × 3
   worker shift hours
   <chr>  <chr> <dttm>
 1 jeff   night NA
 2 jeff   night NA
 3 jeff   day   NA
 4 jeff   day   NA
 5 jeff   day   NA
 6 stan   night NA
 7 stan   night NA    
 8 stan   night NA
 9 stan   day   NA
10 stan   day   NA
11 annie  night NA
12 annie  day   NA
13 annie  day   NA
14 annie  day   NA
15 annie  day   NA