How to tell readr::read_csv to guess double column correctly

Question

I have runoff data with a lot of zero values and occasionally some non-zero double values.

'readr::read_csv' guesses integer column type because of the many zeros.

How can I make read_csv to guess the correct double column type? I do not know the mapping of the variable names beforehand, hence I cannot give name-type mapping.

Here is a small example

  # create a column of doubles with many zeros (runoff data)
  #dsTmp <- data.frame(x = c(rep(0.0, 2), 0.5)) # this works
  dsTmp <- data.frame(x = c(rep(0.0, 1e5), 0.5))
  write_csv(dsTmp, "tmp/dsTmp.csv")
  # 0.0 is written as 0 
  # read_csv now guesses integer instead of double and reports 
  # a parsing failure. 
  ans <- read_csv("tmp/dsTmp.csv")
  # the last value is NA instead of 0.5
  tail(ans)

Can I tell it to choose a try wider column types instead of issuing a parsing failure?

Issue 645 mentions this problem, but the workaround given there is on the writing side. I have little influence on the writing side.

You could try increasing the `guess_max` parameter so that it looks further into the file to look for values before making a guess. — tblznbits, Oct 22 '18 at 17:13

r2evans · Accepted Answer · 2018-10-22T19:00:37.963

Here's two techniques. (Data prep at the bottom. $hp and $vs and beyond are the integer columns.)

NB: I add cols(.default=col_guess()) to most of the first-time calls so that we don't get the large message of what read_csv found the columns to be. It can be omitted at the cost of a more noisy console.

Force all columns to be double, with the cols(.default=...) setting, works safely as long as you know there are no non-numbers in the file:

read_csv("mtcars.csv", col_types = cols(.default = col_double()))
# Warning in rbind(names(probs), probs_f) :
#   number of columns of result is not a multiple of vector length (arg 1)
# Warning: 32 parsing failures.
### ...snip...
# See problems(...) for more details.
# # A tibble: 32 x 11
#      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#  1  21      NA  160    110  3.9   2.62  16.5     0     1     4     4
#  2  21      NA  160    110  3.9   2.88  17.0     0     1     4     4
#  3  22.8    NA  108     93  3.85  2.32  18.6     1     1     4     1
#  4  21.4    NA  258    110  3.08  3.22  19.4     1     0     3     1
#  5  18.7    NA  360    175  3.15  3.44  17.0     0     0     3     2
#  6  18.1    NA  225    105  2.76  3.46  20.2     1     0     3     1
#  7  14.3    NA  360    245  3.21  3.57  15.8     0     0     3     4
#  8  24.4    NA  147.    62  3.69  3.19  20       1     0     4     2
#  9  22.8    NA  141.    95  3.92  3.15  22.9     1     0     4     2
# 10  19.2    NA  168.   123  3.92  3.44  18.3     1     0     4     4
# # ... with 22 more rows

Change only <int> (col_integer()) columns, taking a little more care. My use of n_max=50 needs to be balanced. Similar to guess_max=, a little more is better. In this case, if I chose n_max=1 then the top couple of mpg values would suggest integers, which is fine. But if you have other fields that are ambiguous with other classes, you'll need more. Since you're talking about not wanting to read in the whole file but are willing to read in "a bit" to get the right guess, I'd think you can go with a reasonable value (100s? 1000s?) here to be robust for chr and lgl.

types <- attr(read_csv("mtcars.csv", n_max=1, col_types = cols(.default = col_guess())), "spec")
(intcols <- sapply(types$cols, identical, col_integer()))
#   mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb 
#  TRUE FALSE  TRUE  TRUE FALSE FALSE FALSE  TRUE  TRUE  TRUE  TRUE 
types$cols[intcols] <- replicate(sum(intcols), col_double())

and the final read, noting that $hp and beyond are now <dbl> (unlike in the data prep read, below).

read_csv("mtcars.csv", col_types = types)
# # A tibble: 32 x 11
#      mpg cyl    disp    hp  drat    wt  qsec    vs    am  gear  carb
#    <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#  1  21   c6     160    110  3.9   2.62  16.5     0     1     4     4
#  2  21   c6     160    110  3.9   2.88  17.0     0     1     4     4
#  3  22.8 c4     108     93  3.85  2.32  18.6     1     1     4     1
#  4  21.4 c6     258    110  3.08  3.22  19.4     1     0     3     1
#  5  18.7 c8     360    175  3.15  3.44  17.0     0     0     3     2
#  6  18.1 c6     225    105  2.76  3.46  20.2     1     0     3     1
#  7  14.3 c8     360    245  3.21  3.57  15.8     0     0     3     4
#  8  24.4 c4     147.    62  3.69  3.19  20       1     0     4     2
#  9  22.8 c4     141.    95  3.92  3.15  22.9     1     0     4     2
# 10  19.2 c6     168.   123  3.92  3.44  18.3     1     0     4     4
# # ... with 22 more rows

Data:

library(readr)
mt <- mtcars
mt$cyl <- paste0("c", mt$cyl) # for fun
write_csv(mt, path = "mtcars.csv")
read_csv("mtcars.csv", col_types = cols(.default = col_guess()))
# # A tibble: 32 x 11
#      mpg cyl    disp    hp  drat    wt  qsec    vs    am  gear  carb
#    <dbl> <chr> <dbl> <int> <dbl> <dbl> <dbl> <int> <int> <int> <int>
#  1  21   c6     160    110  3.9   2.62  16.5     0     1     4     4
#  2  21   c6     160    110  3.9   2.88  17.0     0     1     4     4
#  3  22.8 c4     108     93  3.85  2.32  18.6     1     1     4     1
#  4  21.4 c6     258    110  3.08  3.22  19.4     1     0     3     1
#  5  18.7 c8     360    175  3.15  3.44  17.0     0     0     3     2
#  6  18.1 c6     225    105  2.76  3.46  20.2     1     0     3     1
#  7  14.3 c8     360    245  3.21  3.57  15.8     0     0     3     4
#  8  24.4 c4     147.    62  3.69  3.19  20       1     0     4     2
#  9  22.8 c4     141.    95  3.92  3.15  22.9     1     0     4     2
# 10  19.2 c6     168.   123  3.92  3.44  18.3     1     0     4     4
# # ... with 22 more rows

Thanks, r2evans. Your solution 2 solves my problem. I transformed your code to a small function: — Thomas Wutzler, Oct 23 '18 at 08:51

score 1 · Answer 2 · answered Oct 23 '18 at 08:57

I transferred the code of the solution of r2evans to a small function:

read_csvDouble <- function(
  ### read_csv but read guessed integer columns as double
  ... ##<< further arguments to \code{\link{read_csv}}
  , n_max = Inf        ##<< see \code{\link{read_csv}}
  , col_types = cols(.default = col_guess()) ##<< see \code{\link{read_csv}}
  ## the default suppresses the type guessing messages
){
  ##details<< Sometimes, double columns are guessed as integer,  e.g. with
  ## runoff data where there are many zeros, an only occasionally 
  ## positive values that can be recognized as double.
  ## This functions modifies \code{read_csv} by changing guessed integer 
  ## columns to double columns.
  #https://stackoverflow.com/questions/52934467/how-to-tell-readrread-csv-to-guess-double-column-correctly
  colTypes <- read_csv(..., n_max = 3, col_types = col_types) %>% attr("spec")
  isIntCol <- map_lgl(colTypes$cols, identical, col_integer())
  colTypes$cols[isIntCol] <- replicate(sum(isIntCol), col_double())
  ##value<< tibble as returned by \code{\link{read_csv}}
  ans <- read_csv(..., n_max = n_max, col_types = colTypes)
  ans
}

score 0 · Answer 3 · answered Oct 22 '18 at 18:41

data.table::fread seems to work fine for this.

write_csv(dsTmp, ttfile <- tempfile())
ans <- fread(ttfile)
tail(ans)
#      x
# 1: 0.0
# 2: 0.0
# 3: 0.0
# 4: 0.0
# 5: 0.0
# 6: 0.5

From the ?fread help page

Rarely, the file may contain data of a higher type in rows outside the sample (referred to as an out-of-sample type exception). In this event fread will automatically reread just those columns from the beginning so that you don't have the inconvenience of having to set colClasses yourself;

`data.table::fread` indeed works nicely. But I prefer not add more package dependencies, and `read_csv` is already used at many places in the project. — Thomas Wutzler, Oct 23 '18 at 09:00

How to tell readr::read_csv to guess double column correctly

3 Answers3