How to skip the second row using readxl

4
votes

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.

2
rexcelreadxl
You'll need to read in the whole file (which will give you the column names), and then manually remove row 2, and potentially rearrange column entries in the rows below row 2 to match column names. I found read_excel quite robust when it comes to "nonsense" lines: it will still read the file, but any potential cleaning is up to you. – Maurits Evers

2 Answers

8
votes

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

enter image description here

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
5
votes

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