CODEWITHSUNDEEP
rexcelreadxl
Matthew
Matthew

Reputation: 4339

How to skip the second row using readxl

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.

Upvotes: 7

Views: 12973

Answers (2)

JamesR
JamesR

Reputation: 643

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

Upvotes: 11

Maurits Evers
Maurits Evers

Reputation: 50738

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

Upvotes: 11

Related Questions