Split values of a data.table column and select the lowest number or NA if all are "NA"

I have the following data table in R:

library(data.table)
dt <- data.table(x = c(NA, NA, NA, NA, NA, NA, NA, "1,2", "NA,3", "NA,NA,NA",
                        "NA,NA,NA,NA", "NA,NA", "NA,4,NA,1", "NA,NA,NA", "NA,NA"))

              x
 1:        <NA>
 2:        <NA>
 3:        <NA>
 4:        <NA>
 5:        <NA>
 6:        <NA>
 7:        <NA>
 8:         1,2
 9:        NA,3
10:    NA,NA,NA
11: NA,NA,NA,NA
12:       NA,NA
13:   NA,4,NA,1
14:    NA,NA,NA
15:       NA,NA

Now I would like to transfer the column to numeric with the following conditions:

• If the entry is already NA, keep it as NA
• If all entries separated by commas are "NA", the field should be NA
• If "NA" and numbers are mixed, the field should contain a single number with the lowest value
• If all entries are numbers, select the single number with the lowest value

The result should look like this:

     x
 1: NA
 2: NA
 3: NA
 4: NA
 5: NA
 6: NA
 7: NA
 8:  1
 9:  3
10: NA
11: NA
12: NA
13:  1
14: NA
15: NA

>Solution :

You can achieve this by splitting the column values on the comma, converting the resulting strings to numbers, and then applying the min function.

For this, I’ve wrote custom function like this

# Function to compute the desired transformation
selectMinorNA <- function(x) {
  # If the entry is already NA, keep it as NA
  if (is.na(x))
    return(NA_integer_)
  # If all entries separated by commas are "NA", the field should be NA
  # I've added inf for the purpose of extracting the min value 
  nums <- as.numeric(gsub("NA", "Inf", unlist(strsplit(x, ","))))
  # If "NA" and numbers are mixed, the field should contain a single number with the lowest value
  if (all(is.infinite(nums)))
    return(NA_integer_)
  # If all entries are numbers, select the single number with the lowest value
  return(min(nums, na.rm = TRUE))
}

Applying this function to your data frame returns this

dt[, x := sapply(x, selectMinorNA)]
> dt
     x
 1: NA
 2: NA
 3: NA
 4: NA
 5: NA
 6: NA
 7: NA
 8:  1
 9:  3
10: NA
11: NA
12: NA
13:  1
14: NA
15: NA