R, data.table: How to prevent fread from inferring NAs for one column?

Viewed 42

I have a csv that has a column with string values. One of these values is a the literal string NA, which I want to treat as a string. Other columns have actual NAs that I would like to treat as NAs. Is there a way for me to specify that fread should not automatically parse NAs for one column? I have tried using the colClasses argument, but this doesn't seem to work. (I suspect it runs the conversion after the automatic parsing of NAs happens, while I'm looking for something that works like pandas converters argument that seems to perform the casting while reading.)

MWE:

test.csv:

strings,other.values
NA,blah
blah,NA

I want to read the NA in strings as the character sequence NA, but the NA in other.values as the value NA.

> library(data.table)
> df <- fread('test.csv', header=TRUE) # adding colClasses=c('strings'='character') doesn't help
> df
    strings other.values                                                                                                       
1:    <NA>         blah                                                                                                       
2:    blah         <NA> 
> which(is.na(df), arr.ind=TRUE)
     row col                                                                                                                  
[1,]   1   1                                                                                                                  
[2,]   2   2  

desired output:

> df
   strings other.values                                                                                                       
1:      NA         blah                                                                                                       
2:    blah         <NA> 
> which(is.na(df), arr.ind=TRUE)
     row col                                                                                                                
[2,]   2   2

In case it's relevant, here's a comparison with Python's pandas, which does what I want in this case with its converters argument (though of course I want to do other stuff in R).

>>> import pandas as pd
>>> df = pd.read_csv('test.csv')
>>> df
  strings other.values                                                                                                        
0     NaN         blah                                                                                                        
1    blah          NaN 
>>> df.isnull()
   strings  other.values                                                                                                      
0     True         False                                                                                                      
1    False          True
>>> df = pd.read_csv('test.csv', converters={'strings':str})
>>> df
  strings other.values                                                                                                        
0      NA         blah                                                                                                        
1    blah          NaN
>>> df.isnull()
   strings  other.values                                                                                                      
0    False         False                                                                                                      
1    False          True

Edit: of course, it's possible in this simple case to just do something like this:

> library(dplyr)
> df <- df |> mutate(strings = case_when(is.na(strings) ~ 'NA', TRUE ~ strings))

But this seems like a hack and not generalizable in the way I'm hoping for (for instance, if there are blank spaces in the strings column that would get parsed as NAs, this would collapse the difference).

1 Answers

One potential option is to add quotation marks to values in the "strings" column with a preprocessing step, i.e.

Contents of "test.csv":

strings,other.values
NA,blah
blah,NA
library(data.table)

df <- fread(cmd = "sed 's/^/\"/;s/,/\",/' ~/Desktop/test.csv", header = TRUE)
df
#>    strings other.values
#> 1:      NA         blah
#> 2:    blah         <NA>

If the "strings" column is at another position you can tweak the sed command to suit, e.g. Contents of "test2.csv":

xx,yy,strings,other.values
1,string,NA,blah
2,test,blah,NA
df2 <- fread(cmd = "sed 's/,/,\"/2;s/,/\",/3' ~/Desktop/test2.csv", header = TRUE)
df2
#>    xx     yy strings other.values
#> 1:  1 string      NA         blah
#> 2:  2   test    blah         <NA>
Related