Repository navigation
fread(fill=TRUE) fills character fields with empty strings instead of NAs #2524
Description
Activity
- added this to the milestone
on Dec 14, 2017 Have been discussing with Pasha about this. He'd like filled character columns (
fill=TRUE) to be filled withNAalways, regardless ofna.strings. Since there is no doubt they are missing. Or, at least controllable so user could choose that behavior. Whereas I see no difference between,,in character columns, and a filled character columns and would like whether""appears inna.stringsor not, to affect the treatment of both,,and filled character columns the same way.Any views out there?
I tend to agree with Pasha, but I don't understand this sentence:
Whereas I see no difference between ,, in character columns, and a filled character columns and would like whether "" appears in na.strings or not, to affect the treatment of both ,, and filled character columns the same way.
Would you mind paraphrasing?
Consider this file:
A,B,C 1,foo,bar 2 3,,bazIf
na.strings="", then both Matt and me agree that the file should be understood as> fread("A,B,C\n1,foo,bar\n2\n3,,baz", fill=TRUE, na.strings="") A B C 1: 1 foo bar 2: 2 NA NA 3: 3 NA bazHowever when
na.stringsis anything different, then we have different opinions on the matter:> # Matt's opinion > fread("A,B,C\n1,foo,bar\n2\n3,,baz", fill=TRUE, na.strings="?") A B C 1: 1 foo bar 2: 2 3: 3 baz > # my opinion > fread("A,B,C\n1,foo,bar\n2\n3,,baz", fill=TRUE, na.strings="?") A B C 1: 1 foo bar 2: 2 NA NA 3: 3 bazInteresting. I think I agree with Pasha.
To me it seems immediately clear that the second row should be
NA, pretty much by definition.The ambiguity comes from how to treat
,,; in the case whenna.strings = ''it's clear that this should also beNA. Given that this is an option, the user, by not using''inna.strings, is asserting that''is not missing, and hence must be treated as''.Maybe we can add some output to
verboseto signal this to the user when it happens, and they can adjust accordingly (e.g., add''back tona.strings)Reacted by Pasha StetsenkoAt the risk of adding complexity, but a way to avoid this decision would be to let
fillaccept values to replace when filling.fillandna.stringsdo appear to have slightly different purposes.> fread("A,B,C,D\n1,foo,bar,5\n2\n3,,baz", fill=list(logical = NA, integer = 7L, character = ""), na.strings="?") A B C D 1: 1 foo bar 5 2: 2 7 3: 3 baz 7
On the broader point, how important is it that
freadandfwritebe inverses of each other?Great points. I'm not so sure either on the importance of fread/fwrite being inverses of each other, by default. If saving from R and back into R needs to be preserving, wouldn't you use a binary format for that?
When I was doing analysis when importing data I preferred to collapse all things similar to missing, to missing. So I'd change,[ ]+,,,"",,,,all toNA. Then I could useis.naconsistently for all types including character, rather than have to remember that""wasn'tNA. In onwards analysis if there was a non-<NA>I could be sure it had at least one character, and that if that was"NA"then it was surely the string literal, such as the stock ticker. If that is whyreadrhas chosen the defaultna.strings=c("","NA")with data analysis in mind, then I see why.I like the
fill=expansion idea; using the same argumentfill=with a list is nice. However, if its default for filling character columns no longer comes fromna.strings, that is a backwards incompatible default change that would not be covered by thegetOption("datatable.na.string")plan. Can we get away with calling this a bug fix (as this issue is labelled) and just going ahead? Users would have to change their code to passfill=list(character="")to get back to old behaviour.In short, as long as by default :
fread("A,B,C\n1,foo,bar\n2\n3,,baz", fill=TRUE) A B C 1: 1 foo bar 2: 2 NA NA 3: 3 NA bazThat's the main thing to me. Think everyone agrees on that. That's not the case now and needs PR #2652 to make that happen. Since Pasha has approved that one now, I'll request @MichaelChirico, @HughParsonage, @arunsrinivasan and @jangorecki to add your approvals too please since it's major one at the top of NEWS (but it doesn't actually change any default behaviour yet).
Reacted by Jan Gorecki and Ethan SmithFWIW, I would support Matt's view -- if the user has supplied explicit
na.strings, they should be respected.Reason: This is a broader issue, not just when the file has
"". Some software (e.g. SAS/SPSS) support multiple types of missing values, and the user might be interested only in a subset of them for a concrete analysis (thereby supplyingna.stringsexplicitly). Modifying the definition of 'missing value' behind the scenes would change user intent.This also has an implication re
freadandfwritebeing inverses of each other -- this would be possible only within the domain of data structures supported by R. If the file originated in another system (e.g. with support of multiple missing values), that extra/unsupported info would be lost irrevocably when importing into R.I like MichaelChirico's suggestion for notifying the user that
""s were not converted toNAs -- that would be useful for some users.Reacted by Jan Gorecki, A. Domingues, Federico Molina and Ethan Smithit might be beneficial to explicitly state somewhere that fread and fwrite are not intended to support data-type stable round-tripping persistent storage. these functions are so so fast and convenient, that I fell into the trap of trying to do this without thinking about the suitability of this solution
Unless of course schema is carried together, either as function arguments or CSVY header.
Hi @MichaelChirico @jangorecki
If this issue is in the bucket list of bugs to solve and if I can work on this. On reading this, my understanding is whenfill=TRUEby default numeric and logical columns pad missing fields as NA while character columns pad them as "", and users who want the old behavior for character columns can pass a fill list via fill=list(character="").
Please let me know if I’ve missed any important aspect.
Test case:
Expected output: