readr read_csv specify col_types default but override some columns by location (not by name)

jmuhlenkamp 0
Viewed 18

I have data across many .csv files that has the following attributes:

  • The vast majority of the columns are numeric.
  • The number of numeric columns across the files varies.
  • A few of the columns are character. The number and location of these character columns is known and constant across the files, but the names are not known and can vary.

For example, the first 2 columns are character, followed by an unknown number of double. columns. The names of the first 2 columns varies across the files (thus I cannot specify the type by name).

I would like to use read_csv and do the following:

  • Specify double() as the default column type
  • Specify certain columns as character() by location instead of by name.
  • Have read_csv parse the names as they exist in the files for me.
  • Not allow read_csv to guess at any of the column types. I'd like to explicitly enforce the knowledge I have laid out above directly in the read_csv call.

Popular answer(s) found elsewhere like this one: https://stackoverflow.com/a/37835620/6850554 will not work for because that answer assumes you know the name of the character columns, which I do not.

Here is a simple reprex. First, create some dummy .csv files.

suppressPackageStartupMessages(library(dplyr))
library(readr)

write_csv(iris, 'iris1.csv')
write_csv(rename(iris, species = Species), 'iris2.csv')
write_csv(rename(iris, Sp = Species), 'iris3.csv')

I know this works...

df1 <- read_csv('iris1.csv', col_types = cols(Species = 'c', .default = 'd'))
df2 <- read_csv('iris2.csv', col_types = cols(species = 'c', .default = 'd'))
df3 <- read_csv('iris3.csv', col_types = cols(Sp = 'c', .default = 'd'))

However, suppose I do not know the names of the 4th column in the files, I only know that the 4th column is character and all others are numeric, and I wish to enforce this knowledge in my ingestion of the files.

Also, please note that this is a simple reprex and the real world application has dozen of columns and the number of columns varies across files. Thus, something like col_types = 'dddc' does not accomplish my goals because readr will guess at any columns that I did not specify.

Here is a sample of things I have been trying that are not accomplishing my goal here.

# I want to specify the 4th column, whose name I do not know as character
# This is not correct syntax and will raise an error
#df1 <- read_csv('iris1.csv', col_types = cols('4' = 'c', .default = 'd'))
#df2 <- read_csv('iris2.csv', col_types = cols('4' = 'c', .default = 'd'))
#df3 <- read_csv('iris3.csv', col_types = cols('4' = 'c', .default = 'd'))

# This runs but guesses instead of using a default
df1 <- read_csv('iris1.csv', col_types = '???c')
df2 <- read_csv('iris2.csv', col_types = '???c')
df3 <- read_csv('iris3.csv', col_types = '???c')
readr dplyr csv file-io r
1 Answers

(Reposting my comment as an answer in case nothing better comes along, as per this post, though I would also be interested in a neater answer if anyone has one.)

In the call to col_types() you can exceed the number of columns in the CSV without any problems that I can see. Using the reprex and the first example (we want the first two columns to be character, the rest to double, no guessing from readr) we can use:

df1 <- read_csv('iris1.csv', col_types = 'ccdddddddd')
df2 <- read_csv('iris2.csv', col_types = 'ccdddddddd')
df3 <- read_csv('iris3.csv', col_types = 'ccdddddddd')

As noted in the comments by OP this gets a bit messy when dozens of columns need to be covered, but it should get the job done as far as I can tell.

KSkoczek0
Related