How do I pivot columns?

Question

I have found this dataframe in an Excel file, very disorganized. This is just a sample of a bigger dataset, with many jobs.

df <- data.frame(
  Job = c("Frequency", "Driver", "Operator"),
  Gloves = c("Daily", 1,2),
  Aprons = c("Weekly", 2,0),
)

Visually it's

I need it to be in this format, something that I can work in a database:

df <- data.frame(
  Job = c("Driver", "Driver", "Operator", "Operator"),
  Frequency= c("Daily", "Weekly", "Daily", "Weekly"),
  Item= c("Gloves", "Aprons", "Gloves", "Aprons"),
  Quantity= c(1,2,2,0)
)

Visually it's

Any thoughts in how do we have to manipulate the data? I have tried without any luck.

Answer 1

We could use tidyverse methods by doing this in three steps

1. Remove the first row - slice(-1), reshape to 'long' format (pivot_longer)
2. Keep only the first row - slice(1), reshape to 'long' format (pivot_longer)
3. Do a join with both of the reshaped datasets

library(dplyr)
library(tidyr)
df %>% 
   slice(-1) %>%
   pivot_longer(cols = -Job, names_to = 'Item', 
       values_to = 'Quantity') %>%
    left_join(df %>%
         slice(1) %>% 
         pivot_longer(cols= -Job, values_to = 'Frequency',
            names_to = 'Item') %>% 
        select(-Job) )

-output

# A tibble: 4 x 4
  Job      Item   Quantity Frequency
  <chr>    <chr>  <chr>    <chr>    
1 Driver   Gloves 1        Daily    
2 Driver   Aprons 2        Weekly   
3 Operator Gloves 2        Daily    
4 Operator Aprons 0        Weekly   

data

df <- data.frame(
  Job = c("Frequency", "Driver", "Operator"),
  Gloves = c("Daily", 1,2),
  Aprons = c("Weekly", 2,0))