Read CSV into R based on where header begins

Question

I have a large number of CSV files. Some have the header beginning on the first row, others have the header beginning on the 3rd row, others the 7th and so on.

The headers all look the same, they just start on different rows across different files. Is there a way to conditionally read.csv a file to start where the header begins?

For example, if I know the headers all have the first column names "office#", could I somehow instruct R to start reading the csv file when it first runs into the field "office#" and treat that row as the header?

`fread` from the `data.table` package is pretty good a picking these things up. Otherwise use `readLines` and then `grep` to select the header row - you can then use `skip` in `read.csv` — user20650, May 09 '16 at 23:11

Cybernetic · Answer 1 · 2019-09-26T23:45:38.843

I have 4 CSV files:

One table with a header beginning on row 1 (iris.csv)

And 3 tables with headers beginning on rows 3, 1, and 5 (sales_1, sales_2, sales_3)

As long as I know the first column names of each table, I can use the smart_csv_reader function to determine where each header begins, and read each CSV file at the correct row number:

first_columns <- c('sepal.length', 'month', 'month', 'month')

smart_csv_reader <- function(directory) {
    header_begins <- NULL
    file_names <- list.files(directory, pattern=".csv$")
    for(i in 1:length(file_names)) {
        path <- paste(directory, file_names[i], sep='', col='')
        lines_read <- readLines(path, warn=F)
        header_begins[i] <- grep(first_columns[i], lines_read)
    } 
    print('headers detected on rows:')
    print(header_begins)
    l <- list()
    for(i in 1:length(header_begins)) {
        path <- paste(directory, file_names[i], sep='', col='')
        l[i] <- list(read.csv(path, skip=header_begins[i]-1))   
    }
    return(l)
}

Just pass in the directory where all your CSVs are.

Usage:

smart_csv_reader('some_csvs/')

[1] "headers detected on rows:"
[1] 1 3 1 5

As you can see the function returns the correct row numbers for each table. It also returns a list of each table read correctly:

Read CSV into R based on where header begins

1 Answers1

Linked