Create an summarizing variable for multiple columns in data.table r

user_14029910 0
Viewed 45

I have the following data.table

dt <- data.table(id=c(1,2,2,2,3,3,4),
                 date=c("2019-09-13", "2018-12-06", "2017-12-14", "2018-02-08", "2015-12-06", "2012-12-14", "2011-02-08"),
                 variable_1=c("a","b",NA,NA,"b","c",NA),
                 variable_2=c(NA,NA,"a",NA,"a","c",NA),
                 variable_3=c(NA,NA,NA,"b","c","c",NA))
dt
id       date variable_1 variable_2 variable_3
1:  1 2019-09-13          a       <NA>       <NA>
2:  2 2018-12-06          b       <NA>       <NA>
3:  2 2017-12-14       <NA>          a       <NA>
4:  2 2018-02-08       <NA>       <NA>          b
5:  3 2015-12-06          b          a          c
6:  3 2012-12-14          c          c          c
7:  4 2011-02-08       <NA>       <NA>       <NA>

I want to create a variable y that is summarizing all the columns. Everything that has one !is.na() among the variable should be 0 . Every row that has only is.na among all the variables should be 1. Like this:

   id       date variable_1 variable_2 variable_3 y
1:  1 2019-09-13          a       <NA>       <NA> 0
2:  2 2018-12-06          b       <NA>       <NA> 0
3:  2 2017-12-14       <NA>          a       <NA> 0
4:  2 2018-02-08       <NA>       <NA>          b 0
5:  3 2015-12-06          b          a          c 0
6:  3 2012-12-14          c          c          c 0
7:  4 2011-02-08       <NA>       <NA>       <NA> 1

In the original data.table I have 22 variables that I am looking at among 830 total variables. So I would prefer not to look for every Variable with _1 to _22 separately. Is there a way in data.table?

data.table na r
1 Answers
dt[, y := +(rowSums(!is.na(.SD)) == 0L), .SDcols = patterns("^variable_")]
#    id       date variable_1 variable_2 variable_3 y
# 1:  1 2019-09-13          a       <NA>       <NA> 0
# 2:  2 2018-12-06          b       <NA>       <NA> 0
# 3:  2 2017-12-14       <NA>          a       <NA> 0
# 4:  2 2018-02-08       <NA>       <NA>          b 0
# 5:  3 2015-12-06          b          a          c 0
# 6:  3 2012-12-14          c          c          c 0
# 7:  4 2011-02-08       <NA>       <NA>       <NA> 1

Walk-through:

  • .SDcols=patterns(...) defines the columns to be processed as .SD in the j component. This doesn't involve removing/selecting columns for the output, just the ones that will be referenced internally.
  • !is.na(.SD) returns a logical matrix, same dims as .SD, indicating if its value is NA.
  • rowSums(...) returns the count of non-NAs in the row.
  • using the inverted logic of "count the number of non-NA values in a row", we're able to not care about the number of columns being processed; this is what allows me to use == 0L.
  • +(...) is a shorthand trick for converting logical to 0:1
user_33582720
Related