Fixing NAs represented by 9s (ex: 99, 999, 9999) matching column width in data.table (or in fread)

user_3609265 0
Viewed 69

It is common for data in text files to have variable-length sequences of 9s representing NAs. That is, the number of 9s that represent the NA depends on the number of characters in each variable. For instance:

  • a 2 digit state code will have 99 representing NAs
  • a 3 digit variable will have 999 representing NAs. Note that in this case 99 could be a legal (non-NA) value.

What is the best way to clean these values?

Note that, in fread, na.values=c('99','999') is not an ideal option because it will destroy the legal 99 values in 3 digit variables.

Let's say I have data.table d, and two sets of numeric columns

cols_2digit <- c('a','b')
cols_3digit <- c('c','d')

How can I replace sequences of 9s by NAs in all columns of each set at once? The number of sets is limited, so one command per set is fine.

OBS: these variable-length NA codes are reminiscent of fixed-width files (fwf), even if modern files are provided in csv (which could take a standard "999999" value for NA across columns).

data.table na fread r
1 Answers

We can use set by looping over the columns specified in 'cols_2digit', or 'cols_3digit' and change the values in the columns in place

for(j in cols_2digit) set(d, i = which(d[[j]] == '99'), j = j, value = NA_character_)
for(j in cols_3digit) set(d, i = which(d[[j]] == '999'), j = j, value = NA_character_)

Or another option is Map

d[, c(cols_2digit, col2_3digit) := 
     Map(function(dat, y) lapply(dat, function(x) 
         fifelse(x, x == y, NA_character_)), list(.SD[, ..cols_2digit],
                             .SD[, ..cols_3digit]), list('99', '999')) ]

Also, instead of doing this on different sets, another option is to find the column width based on the max frequency

Mode <- function(x) {
   ux <- unique(x)
   ux[which.max(tabulate(match(x, ux)))]
   }

d[, lapply(.SD, function(x) {
                # get the most frequent column width
                colwidth <-  Mode(nchar(x))
                # if it is max 
                # colwidth <- max(nchar(x))
                # get the elements that are only 9 from start (`^`) to end (`$`)
                i1 <- grepl('^9+$', x) 
                # do the assignment based on the index
                x[i1][nchar(x[i1]) == colwidth] <- NA_character_
                x
              })]
user_37322710
Related