Blank space not recognised as NA in fread

Question

I have a large file which has to be imported in R. I used fread for this purpose. fread is recognizing blank spaces from numeric fields as NA but it is not recognizing blank spaces from character and integer64 fields as NA.

fread recognises blank space as an empty cell for character fields and it recognises blank space as 0 for integer64 fields.

When I imported the same data using read.table, it recognizes all blank spaces as NA.

Please find a reproducible example,

library(data.table)
x1 <- c("","","")
x2 <- c("1006678566","","1011160152")
x3 <- c("","ac","")
x4 <- c("","2","3")
df <- cbind.data.frame(x1,x2,x3,x4)
write.csv(df,"tr.csv")

tr1 <- fread("tr.csv", header=T, fill = T,
             sep= ",", na.strings = c("",NA), data.table = F,
             stringsAsFactors = FALSE)

tr2 <- read.table("tr.csv", fill = TRUE, header=T, 
                  sep= ",", na.strings = c(""," ", NA), 
                  stringsAsFactors = FALSE)

Verbose output :

Input contains no \n. Taking this to be a filename to open
[01] Check arguments
  Using 4 threads (omp_get_max_threads()=4, nth=4)
  NAstrings = [<<>>, <<NA>>]
  None of the NAstrings look like numbers.
  show progress = 1
  0/1 column will be read as integer
[02] Opening the file
  Opening file tr.csv
  File opened, size = 409 bytes.
  Memory mapped ok
[03] Detect and skip BOM
[04] Arrange mmap to be \0 terminated
  \n has been found in the input and different lines can end with different line endings (e.g. mixed \n and \r\n in one file). This is common and ideal.
[05] Skipping initial rows if needed
  Positioned on line 1 starting: <<"","x1","x2","x3","x4","x5","x>>
[06] Detect separator, quoting rule, and ncolumns
  Using supplied sep ','
  sep=','  with 7 fields using quote rule 0
  Detected 7 columns on line 1. This line is either column names or first data row. Line starts as: <<"","x1","x2","x3","x4","x5","x>>
  Quote rule picked = 0
  fill=true and the most number of columns found is 7
[07] Detect column types, good nrow estimate and whether first row is column names
  'header' changed by user from 'auto' to true
  Number of sampling jump points = 1 because (407 bytes from row 1 to eof) / (2 * 407 jump0size) == 0
  Type codes (jump 000)    : 56A255A  Quote rule 0
  All rows were sampled since file is small so we know nrow=16 exactly
[08] Assign column names
[09] Apply user overrides on column types
  After 0 type and 0 drop user overrides : 56A255A
[10] Allocate memory for the datatable
  Allocating 7 column slots (7 - 0 dropped) with 16 rows
[11] Read the data
  jumps=[0..1), chunk_size=1048576, total_size=373
Read 16 rows x 7 columns from 409 bytes file in 00:00.042 wall clock time
[12] Finalizing the datatable
  Type counts:
         1 : bool8     '2'
         3 : int32     '5'
         1 : int64     '6'
         2 : string    'A'
=============================
   0.009s ( 22%) Memory map 0.000GB file
   0.029s ( 68%) sep=',' ncol=7 and header detection
   0.002s (  5%) Column type detection using 16 sample rows
   0.001s (  2%) Allocation of 16 rows x 7 cols (0.000GB) of which 16 (100%) rows used
   0.001s (  2%) Reading 1 chunks (0 swept) of 1.000MB (each chunk 16 rows) using 1 threads
   +    0.000s (  0%) Parse to row-major thread buffers (grown 0 times)
   +    0.000s (  0%) Transpose
   +    0.001s (  2%) Waiting
   0.000s (  0%) Rereading 0 columns due to out-of-sample type exceptions
   0.042s        Total

Please help me solve this issue.

Thanks!

Anaheim

The example that you gave results in data tables as you desired, when ran it – Heliochrome

@Heliochrome please check the updated question with screenshot. values in x3 not recognised as NA – Anaheim

I was able to reproduce the behaviour. Clearly it happens only in the character vector. – Graphy

What is your data.table version – Starwort

please be sure to update to the development version of data.table, then include the output from setting verbose = TRUE – Starwort

@Starwort why dev version and not the latest CRAN version? – Floatation

I guess this was introduced with data.table version 1.11.0 (see github.com/Rdatatable/data.table/blob/master/NEWS.md, section 1) quote: "We would like ,,=>NA for consistency with numeric types, and ,"",=>empty string to be the standard default for fwrite/fread character columns so that fread(fwrite(DT))==DT without needing any change to any parameters." – Floatation

@docendodiscimus yes there's that and a few other outstanding issues related to this e.g. github.com/Rdatatable/data.table/issues/2857 and github.com/Rdatatable/data.table/issues/2714 – Starwort

@Starwort ..Thanks for your reply. Please check my verbose output. – Anaheim

This issue stil exists in the latest version of data.table 1.11.8... – Casilde

Still can't find a workaround in 1.12.8. Is there no way to read in both ,, and ,"", as NA? – Sucker

I had the same problem. As a workaround, I use fread for its import speed then replace blank fields with NA with the following code : lapply(tr1, function(x) {ifelse(x == "", NA, x)}) – Grind

This may have to do with the types of the variables. x3 is character, for which an empty string is a valid value. All other variables are assumed numeric (or logical for the all-NA column), for which an empty string is an invalid value. – Soriano

Unfortunately there does not seem to be a fix for this in fread as of yet. The quickest way I can think of while doing it in 1 line would be sapply(fread("tr.csv", quote = ""), function(x) gsub("\"", NA, x)). When sing more than 1 line: dt <- fread("tr.csv") dt[dt==""] <- NA – Toulouse

Answer 1

@SJB Use na.strings = c(NA_character_, "") as argument in fread() and blank spaces/cells will be read as NA.

There are forms of NA for various data types. See help(NA): NA_character_ NA_real_ NA_integer_ etc.

Fibrosis

Answer 2

In case you want to avoid the additional manipulation after reading the file, you could try using

quote = FALSE

when writing to csv. This prevents the use of quotations " " around the values and all missing values should now be read as NAs. It should look like this -

# also turned off row names to prevent an additional column when reading the file.
write.csv(df, "tr.csv", quote = FALSE, row.names = FALSE) 

Output -

tr1 <- fread("tr.csv", header=T, fill = T,
             sep= ",", na.strings = c("",NA), data.table = F,
             stringsAsFactors = FALSE)
tr1

 x1         x2   x3 x4
1 NA 1006678566 <NA> NA
2 NA         NA   ac  2
3 NA 1011160152 <NA>  3

tr2 <- read.table("tr.csv", fill = TRUE, header=T, 
                  sep= ",", na.strings = c(""," ", NA), 
                  stringsAsFactors = FALSE)
tr2

  x1         x2   x3 x4
1 NA 1006678566 <NA> NA
2 NA         NA   ac  2
3 NA 1011160152 <NA>  3

Detritus

that worked perfectly, it should be the right answer. As a side question, is there any downside (risk) of using fill=TRUE ? – Evolutionary

@hunter_git not really. it fills missing columns for row (if any) with NA instead of raising a Stopped early warning. So, unless you're sure about the length of rows, fill should be kept FALSE to raise the warning when there is any discrepancy in row length. – Detritus

Answer 3

One thing that I found was the way data gets saved when we do a write.csv().

Open the csv file and hit delete for blank cells in X4 and save . If you import it now, the NA would show up in R.

To check:

apply(tr1, 2, function(x) length(which(is.na(x))))

V1 x1 x2 x3 x4

0 3 1 2 1

If there is a csv file with blanks and we do fread using

na.strings("", NA)

The character data types also show up as "NA" for blanks.

Stalwart

@ Deb: na.strings = c("", NA) works for fread() and would be the correct solution if colSums(is.na(DT)) had counted the <NA> s as NA. But it does not. – Fibrosis