Function for Reading in Nasty Excel Files

[wfulp]

A function to deal with merged cells and multiple header layer levels. Here is a snippet from a super nasty excel:

library(tidyverse)
library(readxl)
library(janitor)

file_path <- 'N:/cavd/Objective 4/GH-VAP/ID43-Barouch/Data/Moderna_mRNA1273_Paper/nejmoa2024671_appendix_2.xlsx'
tmp_names <- read_excel(file_path,
                        sheet = 'Table S2',
                        range = "C5:CL7",
                        col_names = FALSE)
final_names <- tmp_names %>% 
  t() %>% 
  as_tibble() %>% 
  fill(V1, V2) %>% 
  unite(
    col = "colnames",
    V1, V2, V3,
    sep = "_",
    na.rm = TRUE
  ) %>% 
  mutate(final_names = colnames  %>% 
           str_replace_all("(\\r)|(\\n)"," ") %>% 
           str_replace_all("\\s+"," "))
moderna_data_org <- read_excel(file_path,
                               sheet = 'Table S2',
                               skip = 7,
                               col_names = c("Group",final_names$final_names)) %>% 
  clean_names() %>% 
  fill("group")