CODEWITHSUNDEEP
rdata.table
Strickland
Strickland

Reputation: 610

replace values in data-table based on column number and separate index vector

I am given a large data-table and I need to set cells to a fixed value (e.g. 0) based on the column number and an index dependent on the row number.

As an example, I am given a data-table 'dt' full of ones. Additionally, I have a column vector, giving the number of columns (per row) that shall remain unchanged and the remaining ones shall be set to 0.

dt <- setnames(data.table(matrix(1,nrow=100, ncol=11)),as.character(c(0:10)))

set.seed(1)
index <- sample(c(0:11),100, replace=TRUE)

> dput(index)
c(3L, 4L, 6L, 10L, 2L, 10L, 11L, 7L, 7L, 0L, 2L, 2L, 8L, 4L, 
9L, 5L, 8L, 11L, 4L, 9L, 11L, 2L, 7L, 1L, 3L, 4L, 0L, 4L, 10L, 
4L, 5L, 7L, 5L, 2L, 9L, 8L, 9L, 1L, 8L, 4L, 9L, 7L, 9L, 6L, 6L, 
9L, 0L, 5L, 8L, 8L, 5L, 10L, 5L, 2L, 0L, 1L, 3L, 6L, 7L, 4L, 
10L, 3L, 5L, 3L, 7L, 3L, 5L, 9L, 1L, 10L, 4L, 10L, 4L, 4L, 5L, 
10L, 10L, 4L, 9L, 11L, 5L, 8L, 4L, 3L, 9L, 2L, 8L, 1L, 2L, 1L, 
2L, 0L, 7L, 10L, 9L, 9L, 5L, 4L, 9L, 7L)

For example, in the first row, the first three cells remain unchanged and the other ones are set to 0. As it is a large data-table, I look for an efficient way to do this

Upvotes: 1

Views: 858

Answers (4)

Ronak Shah
Ronak Shah

Reputation: 389325

Since you have dt full of 1's you can recreate the entire data.table by

library(data.table)

cols <- ncol(dt)
data.table(t(sapply(seq_len(nrow(dt)), function(i) 
                   rep(c(1, 0), c(index[i], cols - index[i])))))


#     V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11
# 1:  1  1  1  0  0  0  0  0  0   0   0
# 2:  1  1  1  1  0  0  0  0  0   0   0
# 3:  1  1  1  1  1  1  0  0  0   0   0
# 4:  1  1  1  1  1  1  1  1  1   1   0
# 5:  1  1  0  0  0  0  0  0  0   0   0
# 6:  1  1  1  1  1  1  1  1  1   1   0
# 7:  1  1  1  1  1  1  1  1  1   1   1
# 8:  1  1  1  1  1  1  1  0  0   0   0
# 9:  1  1  1  1  1  1  1  0  0   0   0
#10:  0  0  0  0  0  0  0  0  0   0   0
#....

compare it with first 10 index values

index[1:10]
# [1]  3  4  6 10  2 10 11  7  7  0

Upvotes: 0

s_baldur
s_baldur

Reputation: 33743

last_col <- names(dt)[ncol(dt)]
for (r in seq_len(nrow(dt))) {
  zero_from <- max(index[r]-1L, 0L)
  set(dt, i = r, j = as.character(zero_from:last_col), value = 0)
}

Upvotes: 1

chinsoon12
chinsoon12

Reputation: 25223

An option using Matrix package:

library(Matrix)
mat <- as.matrix(dt)
mat * as.matrix(sparseMatrix(
    i=rep(seq_along(index), index),
    j=unlist(sapply(index, seq_len)), 
    x=1))

Or using data.table::set:

for (j in seq_along(names(dt)))
    set(dt, which(j>index), j, 0)

Upvotes: 2

Sven
Sven

Reputation: 1263

In order to avoid complexity, I've taken the reverse approach and first changed all the 1s to 0s. Then it's a double for loop to change the amount of columns indicated in index, to 1s:

library(data.table)

dt <- setnames(data.table(matrix(0,nrow=100, ncol=11)),as.character(c(0:10)))

index <- sample(c(0:11),100, replace=TRUE)

for(i in 1:length(index)) {
  if (index[i] > 0) {
    for(j in 1:index[i]) {
      dt[i,j] <- 1
    }
  }
}

Upvotes: 2

Related Questions