Repository navigation
Irreversible empty string handling by fread() and fwrite() #2214
Description
Activity
- I believe you simply want to set the `na` argument to `fwrite` to, e.g., `NA`.…On Jun 22, 2017 7:14 AM, "Ethan Welty" ***@***.***> wrote: First off, thank you for this fantastic package. It effortlessly powers many of my data caving adventures. Sometimes, it's necessary to distinguish between null (NA) and empty ("") strings, and I'm trying to establish a pipeline that preserves this distinction with minimal markup. This doesn't currently work. To work, fread() would need to distinguish between and "", and fwrite() ideally would quote empty strings when quote = "auto". Consider this data.table: dt <- data.table::data.table(chr = c(NA, "", "a"), num = c(NA, NA, 1)) Here is the fwrite output with quote="auto": csv <- paste( capture.output( data.table::fwrite(dt, quote = "auto") ), collapse = "\n" )) cat(csv) chr,num , , a,1 The empty string is not quoted, and thus indistinguishable from the null string. They are both read back in as empty strings: data.table::fread(csv) chr num 1: NA 2: NA 3: a 1 If instead we force quotes, the distinction is kept between the null and empty strings: csv_quoted <- paste( capture.output( data.table::fwrite(dt, quote = TRUE) ), collapse = "\n" ) cat(csv_quoted) "chr","num" , "", "a",1 However, there is no way to read them back in as such. Either they are both empty: data.table::fread(csv_quoted) chr num 1: NA 2: NA 3: a 1 Or both null: data.table::fread(csv_quoted, na.strings = "") chr num 1: NA NA 2: NA NA 3: a 1 — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#2214>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AHQQdUIgpHW4ViIgENl5HTR9vhKNqThvks5sGnaqgaJpZM4OCWza> .
@MichaelChirico Sorry, I should have added that:
- Unfortunately
"NA"(or any other stand-in) needs to be a valid character string. - The files aren't necessarily produced by me.
For context, this is in an attempt to implement the Tabular Data Package specification, which requires a distinction to be made between empty and null strings. This is easily done in JSON with
null, but seems less standard in CSV.So can I read this file:
cat("chr,num\n,\n\"\",\na,1")chr,num , "", a,1in as:
data.table::data.table(chr = c(NA, "", "a"), num = c(NA, NA, 1))and back out again without data loss? At least not currently, although
fwrite()withquote=TRUEclearly knows the difference between null and empty strings.Is this a current limitation or somehow anti-csv and thus by-design?
- Unfortunately
So assuming that the correct representation of the file is
chr,num↵ ,↵ "",↵ "a",1↵-- there are 2 issues here: one with
freadand another withfwrite.In order for
fwriteto create a file like this withquote="auto", it would first need to check whether there are any NAs in each string column, and if there are then quote every field in the column. Such check is possible, but would increase the run time. Alternatively, we could do no checks up-front, and then only force-quote empty fields. This would impose no runtime overhead and would be sufficient forfreadto understand the purpose, but is not guaranteed to be well-understood by other csv readers.In order for
freadto correctly read a file like this, there should be two separate "types" of string fields: one which treats void fields as empty strings, and another that treats them as NAs. Then upon seeing first quoted empty string""the first type would bump to the second. This seems like a relatively simple change, however not much can be done while Matt is on vacation.Since we need a separate Issue per each feature request, I'm splitting this into two.
- added a commit that references this issue
on Jun 29, 2017 - added this to the milestone
on Mar 3, 2018
First off, thank you for this fantastic package. It effortlessly powers many of my data caving adventures.
Sometimes, it's necessary to distinguish between null (
NA) and empty ("") strings, and I'm trying to establish a pipeline that preserves this distinction with minimal markup. This doesn't currently work. To work,fread()would need to distinguish between"", andfwrite()ideally would quote empty strings whenquote = "auto".Consider this data.table:
Here is the fwrite output with
quote="auto":The empty string is not quoted, and thus indistinguishable from the null string. They are both read back in as empty strings:
If instead we force quotes, the distinction is kept between the null and empty strings:
However, there is no way to read them back in as such. Either they are both empty:
Or both null: