Fast and friendly file finagler

Similar to read.table but faster and more convenient. All controls such as sep, colClasses and nrows are automatically detected. bit64::integer64 types are also detected and read directly without needing to read as character before converting.

Dates are read as character currently. They can be converted afterwards using the excellent fasttime package or standard base functions.

`fread` is for regular delimited files; i.e., where every row has the same number of columns. In future, secondary separator (sep2) may be specified within each column. Such columns will be read as type list where each cell is itself a vector.

fread(input, file, text, cmd, sep="auto", sep2="auto", dec=".", quote="\"",
nrows=Inf, header="auto",
na.strings=getOption("datatable.na.strings","NA"),  # due to change to ""; see NEWS
stringsAsFactors=FALSE, verbose=getOption("datatable.verbose", FALSE),
skip="__auto__", select=NULL, drop=NULL, colClasses=NULL,
integer64=getOption("datatable.integer64", "integer64"),
col.names,
check.names=FALSE, encoding="unknown",
strip.white=TRUE, fill=FALSE, blank.lines.skip=FALSE,
key=NULL, index=NULL,
showProgress=getOption("datatable.showProgress", interactive()),
data.table=getOption("datatable.fread.datatable", TRUE),
nThread=getDTthreads(verbose),
logical01=getOption("datatable.logical01", FALSE),  # due to change to TRUE; see NEWS
keepLeadingZeros = getOption("datatable.keepLeadingZeros", FALSE),
yaml=FALSE, autostart=NA, tmpdir=tempdir()
)

Arguments

input	A single character string. The value is inspected and deferred to either `file=` (if no \n present), `text=` (if at least one \n is present) or `cmd=` (if no \n is present, at least one space is present, and it isn't a file name). Exactly one of `input=`, `file=`, `text=`, or `cmd=` should be used in the same call.
file	File name in working directory, path to file (passed through `path.expand` for convenience), or a URL starting http://, file://, etc. Compressed files ending `.gz` and `.bz2` are supported if the `R.utils` package is installed.
text	The input data itself as a character vector of one or more lines, for example as returned by `readLines()`.
cmd	A shell command that pre-processes the file; e.g. `fread(cmd=paste("grep",word,"filename")`. See Details.
sep	The separator between columns. Defaults to the character in the set `[,\t \|;:]` that separates the sample of rows into the most number of lines with the same number of fields. Use `NULL` or `""` to specify no separator; i.e. each line a single character column like `base::readLines` does.
sep2	The separator within columns. A `list` column will be returned where each cell is a vector of values. This is much faster using less working memory than `strsplit` afterwards or similar techniques. For each column `sep2` can be different and is the first character in the same set above [`,\t \|;`], other than `sep`, that exists inside each field outside quoted regions in the sample. NB: `sep2` is not yet implemented.
nrows	The maximum number of rows to read. Unlike `read.table`, you do not need to set this to an estimate of the number of rows in the file for better speed because that is already automatically determined by `fread` almost instantly using the large sample of lines. `nrows=0` returns the column names and typed empty columns determined by the large sample; useful for a dry run of a large file or to quickly check format consistency of a set of files before starting to read any of them.
header	Does the first data line contain column names? Defaults according to whether every non-empty field on the first data line is type character. If so, or TRUE is supplied, any empty column names are given a default name.
na.strings	A character vector of strings which are to be interpreted as `NA` values. By default, `",,"` for columns of all types, including type `character` is read as `NA` for consistency. `,"",` is unambiguous and read as an empty string. To read `,NA,` as `NA`, set `na.strings="NA"`. To read `,,` as blank string `""`, set `na.strings=NULL`. When they occur in the file, the strings in `na.strings` should not appear quoted since that is how the string literal `,"NA",` is distinguished from `,NA,`, for example, when `na.strings="NA"`.
stringsAsFactors	Convert all character columns to factors?
verbose	Be chatty and report timings?
skip	If 0 (default) start on the first line and from there finds the first row with a consistent number of columns. This automatically avoids irregular header information before the column names row. `skip>0` means ignore the first `skip` rows manually. `skip="string"` searches for `"string"` in the file (e.g. a substring of the column names row) and starts on that line (inspired by read.xls in package gdata).
select	A vector of column names or numbers to keep, drop the rest. `select` may specify types too in the same way as `colClasses`; i.e., a vector of `colname=type` pairs, or a `list` of `type=col(s)` pairs. In all forms of `select`, the order that the columns are specified determines the order of the columns in the result.
drop	Vector of column names or numbers to drop, keep the rest.
colClasses	As in `read.csv`; i.e., an unnamed vector of types corresponding to the columns in the file, or a named vector specifying types for a subset of the columns by name. The default, `NULL` means types are inferred from the data in the file. Further, `data.table` supports a named `list` of vectors of column names or numbers where the `list` names are the class names; see examples. The `list` form makes it easier to set a batch of columns to be a particular class. When column numbers are used in the `list` form, they refer to the column number in the file not the column number after `select` or `drop` has been applied. If type coercion results in an error, introduces `NA`s, or would result in loss of accuracy, the coercion attempt is aborted for that column with warning and the column's type is left unchanged. If you really desire data loss (e.g. reading `3.14` as `integer`) you have to truncate such columns afterwards yourself explicitly so that this is clear to future readers of your code.
integer64	"integer64" (default) reads columns detected as containing integers larger than 2^31 as type `bit64::integer64`. Alternatively, `"double"\|"numeric"` reads as `base::read.csv` does; i.e., possibly with loss of precision and if so silently. Or, "character".
dec	The decimal separator as in `base::read.csv`. If not "." (default) then usually ",". See details.
col.names	A vector of optional names for the variables (columns). The default is to use the header column if present or detected, or if not "V" followed by the column number. This is applied after `check.names` and before `key` and `index`.
check.names	default is `FALSE`. If `TRUE` then the names of the variables in the `data.table` are checked to ensure that they are syntactically valid variable names. If necessary they are adjusted (by `make.names`) so that they are, and also to ensure that there are no duplicates.
encoding	default is `"unknown"`. Other possible options are `"UTF-8"` and `"Latin-1"`. Note: it is not used to re-encode the input, rather enables handling of encoded strings in their native encoding.
quote	By default (`"\""`), if a field starts with a double quote, `fread` handles embedded quotes robustly as explained under `Details`. If it fails, then another attempt is made to read the field as is, i.e., as if quotes are disabled. By setting `quote=""`, the field is always read as if quotes are disabled. It is not expected to ever need to pass anything other than \"\" to quote; i.e., to turn it off.
strip.white	default is `TRUE`. Strips leading and trailing whitespaces of unquoted fields. If `FALSE`, only header trailing spaces are removed.
fill	logical (default is `FALSE`). If `TRUE` then in case the rows have unequal length, blank fields are implicitly filled.
blank.lines.skip	`logical`, default is `FALSE`. If `TRUE` blank lines in the input are ignored.
key	Character vector of one or more column names which is passed to `setkey`. It may be a single comma separated string such as `key="x,y,z"`, or a vector of names such as `key=c("x","y","z")`. Only valid when argument `data.table=TRUE`. Where applicable, this should refer to column names given in `col.names`.
index	Character vector or list of character vectors of one or more column names which is passed to `setindexv`. As with `key`, comma-separated notation like `index="x,y,z"` is accepted for convenience. Only valid when argument `data.table=TRUE`. Where applicable, this should refer to column names given in `col.names`.
showProgress	`TRUE` displays progress on the console if the ETA is greater than 3 seconds. It is produced in fread's C code where the very nice (but R level) txtProgressBar and tkProgressBar are not easily available.
data.table	TRUE returns a `data.table`. FALSE returns a `data.frame`. The default for this argument can be changed with `options(datatable.fread.datatable=FALSE)`.
nThread	The number of threads to use. Experiment to see what works best for your data on your hardware.
logical01	If TRUE a column containing only 0s and 1s will be read as logical, otherwise as integer.
keepLeadingZeros	If TRUE a column containing numeric data with leading zeros will be read as character, otherwise leading zeros will be removed and converted to numeric.
yaml	If `TRUE`, `fread` will attempt to parse (using `yaml.load`) the top of the input as YAML, and further to glean parameters relevant to improving the performance of `fread` on the data itself. The entire YAML section is returned as parsed into a `list` in the `yaml_metadata` attribute. See `Details`.
autostart	Deprecated and ignored with warning. Please use `skip` instead.
tmpdir	Directory to use as the `tmpdir` argument for any `tempfile` calls, e.g. when the input is a URL or a shell command. The default is `tempdir()` which can be controlled by setting `TMPDIR` before starting the R session; see `tempdir`.

Details

A sample of 10,000 rows is used for a very good estimate of column types. 100 contiguous rows are read from 100 equally spaced points throughout the file including the beginning, middle and the very end. This results in a better guess when a column changes type later in the file (e.g. blank at the beginning/only populated near the end, or 001 at the start but 0A0 later on). This very good type guess enables a single allocation of the correct type up front once for speed, memory efficiency and convenience of avoiding the need to set colClasses after an error. Even though the sample is large and jumping over the file, it is almost instant regardless of the size of the file because a lazy on-demand memory map is used. If a jump lands inside a quoted field containing newlines, each newline is tested until 5 lines are found following it with the expected number of fields. The lowest type for each column is chosen from the ordered list: logical, integer, integer64, double, character. 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; particularly helpful if you have a lot of columns. Such columns must be read from the beginning to correctly distinguish "00" from "000" when those have both been interpreted as integer 0 due to the sample but 00A occurs out of sample. Set verbose=TRUE to see a detailed report of the logic deployed to read your file.

There is no line length limit, not even a very large one. Since we are encouraging list columns (i.e. sep2) this has the potential to encourage longer line lengths. So the approach of scanning each line into a buffer first and then rescanning that buffer is not used. There are no buffers used in fread's C code at all. The field width limit is limited by R itself: the maximum width of a character string (currently 2^31-1 bytes, 2GB).

The filename extension (such as .csv) is irrelevant for "auto" sep and sep2. Separator detection is entirely driven by the file contents. This can be useful when loading a set of different files which may not be named consistently, or may not have the extension .csv despite being csv. Some datasets have been collected over many years, one file per day for example. Sometimes the file name format has changed at some point in the past or even the format of the file itself. So the idea is that you can loop fread through a set of files and as long as each file is regular and delimited, fread can read them all. Whether they all stack is another matter but at least each one is read quickly without you needing to vary colClasses in read.table or read.csv.

If an empty line is encountered then reading stops there with warning if any text exists after the empty line such as a footer. The first line of any text discarded is included in the warning message. Unless, it is single-column input. In that case blank lines are significant (even at the very end) and represent NA in the single column. So that fread(fwrite(DT))==DT. This default behaviour can be controlled using blank.lines.skip=TRUE|FALSE.

Line endings: All known line endings are detected automatically: \n (*NIX including Mac), \r\n (Windows CRLF), \r (old Mac) and \n\r (just in case). There is no need to convert input files first. fread running on any architecture will read a file from any architecture. Both \r and \n may be embedded in character strings (including column names) provided the field is quoted.

Decimal separator and locale: fread(...,dec=",") should just work. fread uses C function strtod to read numeric data; e.g., 1.23 or 1,23. strtod retrieves the decimal separator (. or , usually) from the locale of the R session rather than as an argument passed to the strtod function. So for fread(...,dec=",") to work, fread changes this (and only this) R session's locale temporarily to a locale which provides the desired decimal separator.

On Windows, "French_France.1252" is tried which should be available as standard (any locale with comma decimal separator would suffice) and on unix "fr_FR.utf8" (you may need to install this locale on unix). fread() is very careful to set the locale back again afterwards, even if the function fails with an error. The choice of locale is determined by options()$datatable.fread.dec.locale. This may be a vector of locale names and if so they will be tried in turn until the desired dec is obtained; thus allowing more than two different decimal separators to be selected. This is a new feature in v1.9.6 and is experimental. In case of problems, turn it off with options(datatable.fread.dec.experiment=FALSE).

Quotes:

When quote is a single character,

Spaces and other whitespace (other than sep and \n) may appear in unquoted character fields, e.g., ...,2,Joe Bloggs,3.14,....
When character columns are quoted, they must start and end with that quoting character immediately followed by sep or \n, e.g., ...,2,"Joe Bloggs",3.14,.... In essence quoting character fields are required only if sep or \n appears in the string value. Quoting may be used to signify that numeric data should be read as text. Unescaped quotes may be present in a quoted field, e.g., ...,2,"Joe, "Bloggs"",3.14,..., as well as escaped quotes, e.g., ...,2,"Joe \",Bloggs\"",3.14,.... If an embedded quote is followed by the separator inside a quoted field, the embedded quotes up to that point in that field must be balanced; e.g. ...,2,"www.blah?x="one",y="two"",3.14,.... On those fields that do not satisfy these conditions, e.g., fields with unbalanced quotes, fread re-attempts that field as if it isn't quoted. This is quite useful in reading files that contains fields with unbalanced quotes as well, automatically.

To read fields as is instead, use quote = "".

CSVY Support:

Currently, the yaml setting is somewhat inflexible with respect to incorporating metadata to facilitate file reading. Information on column classes should be stored at the top level under the heading schema and subheading fields; those with both a type and a name sub-heading will be merged into colClasses. Other supported elements are as follows:

sep (or alias delimiter)
header
quote (or aliases quoteChar, quote_char)
dec (or alias decimal)
na.strings

File Download:

When input begins with http://, https://, ftp://, ftps://, or file://, fread detects this and downloads the target to a temporary file (at tempfile()) before proceeding to read the file as usual. Secure URLS (ftps:// and https://) are downloaded with curl::curl_download; ftp:// and http:// paths are downloaded with download.file and method set to getOption("download.file.method"), defaulting to "auto"; and file:// is downloaded with download.file with method="internal". NB: this implies that for file://, even files found on the current machine will be "downloaded" (i.e., hard-copied) to a temporary file. See download.file for more details.

Shell commands:

fread accepts shell commands for convenience. The input command is run and its output written to a file in tmpdir (link{tempdir}() by default) to which fread is applied "as normal". The details are platform dependent -- system is used on UNIX environments, shell otherwise; see system.

Value

A data.table by default, otherwise a data.frame when argument data.table=FALSE.

References

finagler = "to get or achieve by guile or manipulation" http://dictionary.reference.com/browse/finagler

On YAML, see http://yaml.org/; on csvy, see http://csvy.org/.

Examples

# Reads text input directly :
fread("A,B\n1,2\n3,4")
#>    A B
#> 1: 1 2
#> 2: 3 4

# Reads pasted input directly :
fread("A,B
1,2
3,4
")
#>    A B
#> 1: 1 2
#> 2: 3 4

# Finds the first data line automatically :
fread("
This is perhaps a banner line or two or ten.
A,B
1,2
3,4
")
#>    A B
#> 1: 1 2
#> 2: 3 4

# Detects whether column names are present automatically :
fread("
1,2
3,4
")
#>    V1 V2
#> 1:  1  2
#> 2:  3  4

# Numerical precision :

DT = fread("A\n1.010203040506070809010203040506\n")
# TODO: add numerals=c("allow.loss", "warn.loss", "no.loss") from base::read.table, +"use.Rmpfr"
typeof(DT$A)=="double"   # currently "allow.loss" with no option
#> [1] TRUE

DT = fread("A\n1.46761e-313\n")   # read as 'numeric'
DT[,sprintf("%.15E",A)]   # beyond what double precision can store accurately to 15 digits
#> [1] "1.467610000018072E-313"
# For greater accuracy use colClasses to read as character, then package Rmpfr.

# colClasses
data = "A,B,C,D\n1,3,5,7\n2,4,6,8\n"
fread(data, colClasses=c(B="character",C="character",D="character"))  # as read.csv
#>    A B C D
#> 1: 1 3 5 7
#> 2: 2 4 6 8
fread(data, colClasses=list(character=c("B","C","D")))    # saves typing
#>    A B C D
#> 1: 1 3 5 7
#> 2: 2 4 6 8
fread(data, colClasses=list(character=2:4))     # same using column numbers
#>    A B C D
#> 1: 1 3 5 7
#> 2: 2 4 6 8

# drop
fread(data, colClasses=c("B"="NULL","C"="NULL"))   # as read.csv
#>    A D
#> 1: 1 7
#> 2: 2 8
fread(data, colClasses=list(NULL=c("B","C")))      #
#>    A D
#> 1: 1 7
#> 2: 2 8
fread(data, drop=c("B","C"))      # same but less typing, easier to read
#>    A D
#> 1: 1 7
#> 2: 2 8
fread(data, drop=2:3)             # same using column numbers
#>    A D
#> 1: 1 7
#> 2: 2 8

# select
# (in read.csv you need to work out which to drop)
fread(data, select=c("A","D"))    # less typing, easier to read
#>    A D
#> 1: 1 7
#> 2: 2 8
fread(data, select=c(1,4))        # same using column numbers
#>    A D
#> 1: 1 7
#> 2: 2 8

# select and types combined
fread(data, select=c(A="numeric", D="character"))
#>    A D
#> 1: 1 7
#> 2: 2 8
fread(data, select=list(numeric="A", character="D"))
#>    A D
#> 1: 1 7
#> 2: 2 8

# skip blank lines
fread("a,b\n1,a\n2,b\n\n\n3,c\n", blank.lines.skip=TRUE)
#>    a b
#> 1: 1 a
#> 2: 2 b
#> 3: 3 c
# fill
fread("a,b\n1,a\n2\n3,c\n", fill=TRUE)
#>    a b
#> 1: 1 a
#> 2: 2  
#> 3: 3 c
fread("a,b\n\n1,a\n2\n\n3,c\n\n", fill=TRUE)
#>     a b
#> 1: NA  
#> 2:  1 a
#> 3:  2  
#> 4: NA  
#> 5:  3 c
#> 6: NA  

# fill with skip blank lines
fread("a,b\n\n1,a\n2\n\n3,c\n\n", fill=TRUE, blank.lines.skip=TRUE)
#>    a b
#> 1: 1 a
#> 2: 2  
#> 3: 3 c

# check.names usage
fread("a b,a b\n1,2\n")
#>    a b a b
#> 1:   1   2
fread("a b,a b\n1,2\n", check.names=TRUE) # no duplicates + syntactically valid names
#>    a.b a.b.1
#> 1:   1     2

if (FALSE) {
# Demo speed-up
n = 1e6
DT = data.table( a=sample(1:1000,n,replace=TRUE),
                 b=sample(1:1000,n,replace=TRUE),
                 c=rnorm(n),
                 d=sample(c("foo","bar","baz","qux","quux"),n,replace=TRUE),
                 e=rnorm(n),
                 f=sample(1:1000,n,replace=TRUE) )
DT[2,b:=NA_integer_]
DT[4,c:=NA_real_]
DT[3,d:=NA_character_]
DT[5,d:=""]
DT[2,e:=+Inf]
DT[3,e:=-Inf]

write.table(DT,"test.csv",sep=",",row.names=FALSE,quote=FALSE)
cat("File size (MB):", round(file.info("test.csv")$size/1024^2),"\n")
# 50 MB (1e6 rows x 6 columns)

system.time(DF1 <-read.csv("test.csv",stringsAsFactors=FALSE))
# 5.4 sec (first time in fresh R session)

system.time(DF1 <- read.csv("test.csv",stringsAsFactors=FALSE))
# 3.9 sec (immediate repeat is faster, varies)

system.time(DF2 <- read.table("test.csv",header=TRUE,sep=",",quote="",
    stringsAsFactors=FALSE,comment.char="",nrows=n,
    colClasses=c("integer","integer","numeric",
                 "character","numeric","integer")))
# 1.2 sec (consistently). All known tricks and known nrows, see references.

system.time(DT <- fread("test.csv"))
# 0.1 sec (faster and friendlier)

identical(DF1, DF2)
all.equal(as.data.table(DF1), DT)

# Scaling up ...
l = vector("list",10)
for (i in 1:10) l[[i]] = DT
DTbig = rbindlist(l)
tables()
write.table(DTbig,"testbig.csv",sep=",",row.names=FALSE,quote=FALSE)
# 500MB csv (10 million rows x 6 columns)

system.time(DF <- read.table("testbig.csv",header=TRUE,sep=",",
    quote="",stringsAsFactors=FALSE,comment.char="",nrows=1e7,
    colClasses=c("integer","integer","numeric",
                 "character","numeric","integer")))
# 17.0 sec (varies)

system.time(DT <- fread("testbig.csv"))
#  0.8 sec

all(mapply(all.equal, DF, DT))


# Real data example (Airline data)
# http://stat-computing.org/dataexpo/2009/the-data.html

download.file("http://stat-computing.org/dataexpo/2009/2008.csv.bz2",
              destfile="2008.csv.bz2")
# 109MB (compressed)

system("bunzip2 2008.csv.bz2")
# 658MB (7,009,728 rows x 29 columns)

colClasses = sapply(read.csv("2008.csv",nrows=100,stringsAsFactors=FALSE),class)
# 4 character, 24 integer, 1 logical. Incorrect.

colClasses = sapply(read.csv("2008.csv",nrows=200,stringsAsFactors=FALSE),class)
# 5 character, 24 integer. Correct. Might have missed data only using 100 rows
# since read.table assumes colClasses is correct.

system.time(DF <- read.table("2008.csv", header=TRUE, sep=",",
    quote="",stringsAsFactors=FALSE,comment.char="",nrows=7009730,
    colClasses=colClasses))
# 24.4 secs

system.time(DT <- fread("2008.csv"))
#  1.9 secs

table(sapply(DT,class))
# 5 character and 24 integer columns. Correct without needing to worry about colClasses
# issue above.


# Reads URLs directly :
fread("http://www.stats.ox.ac.uk/pub/datasets/csb/ch11b.dat")

# Decompresses .gz and .bz2 automatically :
fread("http://stat-computing.org/dataexpo/2009/1987.csv.bz2")

}

Arguments

Details

Value

References

See also

Examples

Contents