What is the best way to wrangle this data with multiple headers?

Hi all,

I'm working with an untidy excel file with multiple headers and I would love to learn how to parse the data with pivot_longer, if possible.

The data looks something like this:

At first I thought I would be able to load the data and skip the first five rows with read_excel("data.xlsx", skip = 5), though that did not work as the Range eff. information is needed for functions elsewhere. I've used pivot_longer in the past for ggplot2 purposes, but I cannot wrap my head around how to use it here, especially since the sheet has merged cells.

The solution I came up with can be found below, but it'd be great to know if there is a more simple/tidy approach to this. Any insight would be greatly appreciated.

rangeratefunction <- function(nextcoladate) {
  
  #enter date of next cola in YYYY-MM-DD format
  nextcoladate <- as_date(nextcoladate)
  
  rangerates <- read_excel("hourlyrates.xlsx", range = "Ranges!A6:H24")
  
  rangerates <- bind_rows(rangerates, read_excel("hourlyrates.xlsx", range = "Ranges!A27:H74"))
  
  rangecola1 <- bind_rows(read_excel("hourlyrates.xlsx", range = "Ranges!I6:N24"), read_excel("hourlyrates.xlsx", range = "Ranges!I27:N74"))
  rangecola2 <- bind_rows(read_excel("hourlyrates.xlsx", range = "Ranges!O6:T24"), read_excel("hourlyrates.xlsx", range = "Ranges!O27:T74"))
  
  rangerates <- if (today() < nextcoladate) {
    bind_cols(rangerates, rangecola1)
  } else {
    bind_cols(rangerates, rangecola2)
  }
  
  rangerates <- rangerates %>% filter(SetID == "COMMN")
  
  return(rangerates)
}

Hi,

Without a toy dataset to play with, it would be fairly difficult to provide help. I recommend you make your question reproducible:

2 Likes

tidyxl may help when reading the data:

3 Likes

I highly recommend the free, online book by tidyxl's author, Spreadsheet Munging Strategies:

As well as the worked examples here:

2 Likes

Thank you, Martin! I'll check out tidyxl.

Thank you, Mara! I'll check out the examples and documentation.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.