Create Sub Headers in R

Question

I'd like to create a df that has a header and a subheader. Example below:

I want to take the following input:

input <- tibble::tribble(
             ~Region,  ~Day_1, ~Day_2,  ~Day_3,
           "Central",  "Boat",     NA,      NA,
           "Central",      NA, "Shoe",      NA,
           "Central",      NA,     NA, "Table",
           "Pacific",      NA, "Boat",      NA,
           "Pacific", "Table",     NA,      NA,
           "Pacific",      NA,     NA,  "Shoe",
           "Eastern",  "Boat",     NA,      NA,
           "Eastern",      NA, "Shoe",      NA,
           "Eastern",      NA,     NA, "Table",
           "Eastern", "Watch",     NA,      NA,
           "Eastern", "Chair",     NA,      NA,
           "Eastern",      NA,  "Cup",      NA
           )

and convert it to the following output:

  Central Central Central Pacific Pacific Pacific Eastern Eastern Eastern
  <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>  
1 Day_1   Day_2   Day_3   Day_1   Day_2   Day_3   Day_1   Day_2   Day_3  
2 Boat    Shoe    Table   Table   Boat    Shoe    Boat    Shoe    Table  
3 NA      NA      NA      NA      NA      NA      Watch   Cup     NA     
4 NA      NA      NA      NA      NA      NA      Chair   NA      NA 

Any thoughts? Thank you in advance!

Answer 1

library(tidyverse)

input %>%
  pivot_longer(-Region) %>%
  na.omit() %>%
  group_by(Region, name) %>%
  mutate(id = row_number()) %>%
  pivot_wider(names_from = id, values_from = value) %>%
  data.table::transpose(make.names = TRUE)
  
  Central Central Central Pacific Pacific Pacific Eastern Eastern Eastern
1   Day_1   Day_2   Day_3   Day_2   Day_1   Day_3   Day_1   Day_2   Day_3
2    Boat    Shoe   Table    Boat   Table    Shoe    Boat    Shoe   Table
3    <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   Watch     Cup    <NA>
4    <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   Chair    <NA>    <NA>