Question

How to check if a data.table contains a column and if it doesn't, create it and fill it with NA?

I need to check if a column exists in a data.table:

  • if the columns exists, keep data.table as it is;
  • if it doesn't, create it and fill it with NA.

Some example:

DT = data.table(
  ID = c("b","b","b","a","a","c"),
  a = 1:6,
  b = 7:12,
  c = 13:18
)
DT

Tried:

DT[, if(exists('d')) 
  DT
  else DT[, 'd' := NA]]

### it creates a 'd' column and fill it with NA:

> DT
   ID a  b  c  d
1:  b 1  7 13 NA
2:  b 2  8 14 NA
3:  b 3  9 15 NA
4:  a 4 10 16 NA
5:  a 5 11 17 NA
6:  c 6 12 18 NA

### but it fill and existing column with NA also:

DT[, if(exists('a')) 
  DT
  else DT[, 'a' := NA]]

> DT
   ID  a  b  c  d
1:  b NA  7 13 NA
2:  b NA  8 14 NA
3:  b NA  9 15 NA
4:  a NA 10 16 NA
5:  a NA 11 17 NA
6:  c NA 12 18 NA

Also tried:

fifelse(DT %>% pointblank::has_columns(columns = 'd'),
        DT,
        DT[, 'd' := NA])

### but got a length error:

Error in fifelse(DT %>% pointblank::has_columns(columns = "d"), DT, DT[,  : 
  Length of 'yes' is 5 but must be 1 or length of 'test' (1).
 5  97  5
1 Jan 1970

Solution

 5

One way:

if (is.null(DT[["d"]])) set(DT, j = "d", value = NA)

Note NA defaults to NA_logical_. If that's not what you want to it's better to specify

class(DT[["d"]])
# [1] "logical"
2024-07-07
s_baldur

Solution

 3

One solution to your problem:

# way 1
if (!"d" %in% names(DT)) DT[, d := NA]

# way 2
DT[, d := if(exists("d")) d else NA]

       ID     a     b     c      d
   <char> <int> <int> <int> <lgcl>
1:      b     1     7    13     NA
2:      b     2     8    14     NA
3:      b     3     9    15     NA
4:      a     4    10    16     NA
5:      a     5    11    17     NA
6:      c     6    12    18     NA
2024-07-07
B. Christian Kamgang

Solution

 2

I guess this should work

> DT[, d := if (!"d" %in% names(.SD)) NA else d][]
       ID     a     b     c      d
   <char> <int> <int> <int> <lgcl>
1:      b     1     7    13     NA
2:      b     2     8    14     NA
3:      b     3     9    15     NA
4:      a     4    10    16     NA
5:      a     5    11    17     NA
6:      c     6    12    18     NA
2024-07-07
ThomasIsCoding