How to skip the second row using readxl

Question

I have been given an excel spreadsheet: column names are in the first row, garbage text is in the second row, and the actual data begins in the third row. I want to use the readxl package to read this into a dataframe, keeping the column names from the first row but discarding the second row.

Simply reading all the rows into a dataframe and then deleting the first row won't work, because the garbage that's in the second row of the excel file won't match the data type of the column.

I'd like a way to do this without manually editing the excel file.

Answer 1

I would suggest reading the whole file, and then manually removing row 2.

As an example, here is a screenshot of a sample Excel file

We read the complete file, and remove row 1 (which corresponds to the second row in the Excel sheet)

library(readxl)
library(tidyverse)
df <- read_excel("Workbook1.xlsx")[-1, ] %>%
    map_df(~parse_guess(.))
df
## A tibble: 2 x 4
#      A     B     C     D
#  <int> <int> <int> <int>
#1    20    30    40    50
#2    30    40    50    60

Answer 2

Here's another solution:

First, read in the first row using readxl and save as an array (as it only imports the first row, this is fast):

col_names <- array(read_excel('C:/spreadsheet.xlsx', sheet = 'Sheet1', n_max = 1, col_names = FALSE))

Second, read in the same spreadsheet but start at your data:

df <- data.frame(read_excel('C:/spreadsheet.xlsx', sheet = 'Sheet1', skip = 2, col_names = FALSE))

Finally, rename the dataframe columns using the first step:

colnames(df) <- col_names