Using sep = "." in `fread` from "data.table"

Question

Can fread from "data.table" be forced to successfully use "." as a sep value?

I'm trying to use fread to speed up my concat.split functions in "splitstackshape". See this Gist for the general approach I'm taking, and this question for why I want to make the switch.

The problem I'm running into is treating a dot (".") as a value for sep. Whenever I do so, I get an "unexpected character" error.

The following simplified example demonstrates the problem.

library(data.table)

y <- paste("192.168.1.", 1:10, sep = "")

x1 <- tempfile()
writeLines(y, x1)
fread(x1, sep = ".", header = FALSE)
# Error in fread(x1, sep = ".", header = FALSE) : Unexpected character (
# 192) ending field 2 of line 1

The workaround I have in my current function is to substitute "." with another character that is hopefully not present in the original data, say "|", but that seems risky to me since I can't predict what is in someone else's dataset. Here's the workaround in action.

x2 <- tempfile()
z <- gsub(".", "|", y, fixed=TRUE)
writeLines(z, x2)
fread(x2, sep = "|", header = FALSE)
#      V1  V2 V3 V4
#  1: 192 168  1  1
#  2: 192 168  1  2
#  3: 192 168  1  3
#  4: 192 168  1  4
#  5: 192 168  1  5
#  6: 192 168  1  6
#  7: 192 168  1  7
#  8: 192 168  1  8
#  9: 192 168  1  9
# 10: 192 168  1 10

For the purposes of this question, assume that the data are balanced (each line will have the same number of "sep" characters). I'm aware that using a "." as a separator is not the best idea, but I'm just trying to account for what other users might have in their datasets, based on other questions I've answered here on SO.

Mcmaster

I haven't looked much at the source for fread, so not to ask the obvious, but have you tried escaping the \\. ? – Tributary

@RicardoSaporta, yes. You'll get an error: Error in fread(x1, sep = "\\.", header = FALSE) : 'sep' must be 'auto' or a single character. – Mcmaster

I just noticed that after my comment. hmmm... I have no idea. Maybe @MattDowle can chime in? – Tributary

@RicardoSaporta, that's what I'm hoping--then I can also ask him whether fread would support a text argument like read.table does :) – Mcmaster

it's unclear to me whether this should be read as 4 columns or 2 columns (of doubles), but either way seems like a bug - file a bug report? – Rounders

If the sep "character" is allowed be a string of multiple characters then you can make your workaround more robust by z <- gsub(".", "|||||", y, fixed=TRUE) `fread(x2, sep = "|||||", header = FALSE) – Prut

Answer 1

Now implemented in v1.9.5 on GitHub.

> input = paste( paste("192.168.1.", 1:5, sep=""), collapse="\n")
> cat(input,"\n")
192.168.1.1
192.168.1.2
192.168.1.3
192.168.1.4
192.168.1.5 

Setting sep='.' results in ambiguity with the new argument dec (by default '.') :

> fread(input,sep=".")
Error in fread(input, sep = ".") : 
  The two arguments to fread 'dec' and 'sep' are equal ('.')

Therefore choose something else for dec :

> fread(input,sep=".",dec=",")
    V1  V2 V3 V4
1: 192 168  1  1
2: 192 168  1  2
3: 192 168  1  3
4: 192 168  1  4
5: 192 168  1  5

You may get a warning :

> fread(input,sep=".",dec=",")
     V1  V2 V3 V4
 1: 192 168  1  1
 2: 192 168  1  2
 3: 192 168  1  3
 4: 192 168  1  4
 5: 192 168  1  5
Warning message:
In fread(input, sep = ".", dec = ",") :
  Run again with verbose=TRUE to inspect... Unable to change to a locale
  which provides the desired dec. You will need to add a valid locale name
  to getOption("datatable.fread.dec.locale"). See the paragraph in ?fread.

Either ignore or suppress the warning, or read the paragraph and set the option :

options(datatable.fread.dec.locale = "fr_FR.utf8")

This ensures there can be no ambiguity.

Luke

Answer 2

The issue seams to be related to the numeric value of the text itself:

library(data.table)

y <- paste("Hz.BB.GHG.", 1:10, sep = "")

xChar <- tempfile()
writeLines(y, xChar)
fread(xChar, sep = ".", header = FALSE)
#     V1 V2  V3 V4
#  1: Hz BB GHG  1
#  2: Hz BB GHG  2
#  3: Hz BB GHG  3
#  4: Hz BB GHG  4
#  5: Hz BB GHG  5
#  6: Hz BB GHG  6
#  7: Hz BB GHG  7
#  8: Hz BB GHG  8
#  9: Hz BB GHG  9
# 10: Hz BB GHG 10

However, trying with the original value, again gives the same error:

fread(x1, sep = ".", header = FALSE, colClasses="numeric", verbose=TRUE)
fread(x1, sep = ".", header = FALSE, colClasses="character", verbose=TRUE)

 Detected eol as \n only (no \r afterwards), the UNIX and Mac standard.
 Looking for supplied sep '.' on line 10 (the last non blank line in the first 'autostart') ... found ok
 Found 4 columns
 First row with 4 fields occurs on line 1 (either column names or first row of data)
 Error in fread(x1, sep = ".", header = FALSE, colClasses = "character",  : 
   Unexpected character (192.) ending field 2 of line 1

---

This however, does work:

read.table(x1, sep=".")
#     V1  V2 V3 V4
# 1  192 168  1  1
# 2  192 168  1  2
# 3  192 168  1  3
# 4  192 168  1  4
# ... <cropped>

Tributary

Hmmm. This is interesting. By extension, if we had y <- paste("Hz.BB.GHG.", 1:10, 11:20, sep = ""), again we would get an error. Any idea why? – Mcmaster

Regarding your edit (read.table), that is what I presently use in one of the versions of concat.split. See splitstackshape:::read.concat. – Mcmaster

It's almost 7am in London, I don't know what Matt is doing not being on stackoverflow ;) Good luck with this, I am off to bed (i'll delete this answer in the morning) – Tributary

I'm guessing that the problem with numeric values is that it assumes the . is a decimal. – Mcmaster

Using any of %+- as separators instead of . works fine. Using y <- paste(".A.168.1.", 1:10, sep = "", collapse="\n"), i.e. with an extra . at the beginning, also produces the same error, so it might not be related to numeric treatment at all. In my error message there is a line break after Unexpected character (, so there could be some non-printing character creeping in from somewhere? – Ludwog

@AnandaMahto fread(x1, sep = ".", header = FALSE, colClasses="numeric", verbose=TRUE) works fine with me (data.table 1.8.10). Also (sorry prob it's stupid) if fread(x1, sep = ".", header = FALSE) is already working for both of you what is the real problem? – Khiva

@Michele, with which x1? The one from Ricardo's answer or the one from my question? Ricardo, I'm editing your answer to avoid confusion. – Mcmaster

data.table 1.8.10 here as well, 1006 tests (latest on CRAN). Using y <- paste("192.168.1.", 1:10, sep = "") fails on both R 2.15.3 (Revolution) and 3.0.1 with the error message as above. Using y <- paste(".A.168.1.", 1:10, sep = "", collapse="\n") and passing to fread() as a text rather than file, gives ... Unexpected character ( .A.1) ending field 4 of line 1, (with the line break before .A.1, hence my comment about not being related to . as decimal (two decimals in my error message). – Ludwog

@AnandaMahto oh sorry, I mean the Ricardo's. (all of them work). In particular: in the one where colClasses="numeric" it says normally Column 1 ('V1') has been detected as type 'character'. Ignoring request from colClasses to read as 'numeric' (a lower type) since NAs would result. and where colClasses="character" it says: Column 4 ('V4') was detected as type 'integer' but bumped to 'character' as requested by colClasses. – Khiva