is it possible to assign multiple column/values using SET with data.table in R?

Viewed 124

Is it possible to assign values to multiple columns using "set?"

Here is an example. For context, I want to create two new sets of columns-- one that imputes missing/NA values to 0, and another that indicates whether missing values were imputed. The first set of columns will duplicate an existing set but have 0 instead of NA and carry the suffix "_M0." The second set will be 0/100 and carry the suffix "_MISS."

I will use the iris data frame as a starting point.

## create a copy of the iris data frame that i can modify
local_iris <- copy(iris)
## make the local iris copy a data.table
iris.dt <- setDT(local_iris)

There isn't missing data, so I will add some for testing.

## make some parts of these columns missing, i.e., set to NA
iris.dt[1:5, Sepal.Width := NA][6:10, Sepal.Length := NA]

I'm using only the "Sepal" columns here, so I want to save those names and create new column names based on it.

## 'grep' returns a list of the positions that meet the criteria; 'grepl' returns a logic vector of the same length as the argument
## using the result of grep as the index/columns of a list seems to do the trick, even if it seems a tiny bit repetitive/clunky
bert <- names(iris.dt)[grep("^Sepal", names(iris.dt))]

## create lists like the original list with new suffixes
bert_M0 <- paste0(bert, "_M0")
bert_MISS <- paste0(bert, "_MISS")

This part seemed clear to me, and went pretty well, but I'm open to suggestions if there are obvious (or not-so-obvious!) ways to streamline it.

Regarding my data.table and other object names-- i try to pick unusual names when testing to ensure I'm not duplicating another name.

## the best way to go about this is unclear
## i will settle for 'a' way and worry about 'best' way later
## one approach is to extend the data.table to have the new columns added, and then modify their respective values in place later

## create a copy of the relevant columns
M0<-iris.dt[, .SD, .SDcols = bert]

## rename the columns
setnames(M0, old = bert, new = bert_M0)

## create a new data.table with the copied columns
opus<-cbind(iris.dt, M0)

## this creates a set of indicators and sets all the _MISS columns equal to 0
opus[, (bert_MISS) := 0L]

Then I'm going to use set and loop through my columns to recode missings and set the flags/dummy vars.

BUT, and here is my main question-- is is possible to do this with only ONE set? Or do I need to have one set per column?

## try using "set"
for (j in seq_len(length(bert))) { # seq_len(arg) is an alternative way of writing 1:arg
   set(opus, ## the data.table we are operating on
       which(is.na(opus[[bert[j]]])), ## the values of i
       bert_M0[j], ## the column
       0 ## the value
       )
   set(opus, ## the data.table we are operating on
       which(is.na(opus[[bert[j]]])), ## the values of i
       bert_MISS[j], ## the column
       100 ## the value
   )
}

Thanks!

1 Answers

I think this addresses your question

for (j in seq_len(length(bert))) set(
  opus,
  which(is.na(opus[[bert[j]]])),
  c(bert_M0[j], bert_MISS[j])
  list(0, 100)
)

you basically provide column names as character vector, and values as a list

Related