Read an Excel Sheet Whose Header Spans Several Rows
Source:R/read_positional_excel_headers.R
read_positional_excel_headers.RdInstrument and lab exports often have two or three header rows (a group title over several columns, then the column names, sometimes units). This reads the sheet by position, combines the header rows into one name per column and returns the data below them.
Usage
read_positional_excel_headers(
path,
sheet = 1,
header_rows = 1:2,
clean_names = TRUE,
convert = TRUE
)Arguments
- path
Path to an `.xlsx` or `.xls` file.
- sheet
Sheet name or number (default 1).
- header_rows
Integer rows (of the sheet, counting from 1) that form the header; consecutive, for example `1:2`. The data start on the row after the last one.
- clean_names
If `TRUE` (default) tidy the combined names with [janitor::make_clean_names()].
- convert
Logical; see Data types.
Value
A tibble of the data rows with the combined names. The attribute `"header"` has the raw header cells (one row per header row).
How names are built
For each column the non-blank cells of `header_rows` are joined with `"_"`, from the top row down. A blank cell in a row **above the last header row** takes the value of the nearest non-blank cell to its left in that row (a merged title that spans columns); a blank in the last header row does not. A column with no header text at all is named by its Excel column letters (`"C"`, `"AB"`). With `clean_names = TRUE` the names go through [janitor::make_clean_names()]; otherwise they are made unique with a numeric suffix. All header cells are read as text.
Limitations
A title above the last header row is carried to every following column whose cell in that row is blank, not only across a merged range (the merge ranges are not read), so a standalone column placed after a group can pick up the group's title. Excel dates are read as their serial numbers (text such as `"45296"`), not as dates; convert them yourself.