Calculate the product of a column with a vector using for loop

Question

I am trying to create the product of columns of a dataset and a vector. The dataset is this:

col1<-rep(c(1:4),3)
col2<-rep(c(1:4),3)
col3<-rep(c(1:4),3)
col4<-rep(c(1:4),3)
df<-data.table(col1,col2,col3,col4)

and the vector this

w<-data.table(w<-c(100,0.1,0.2))



for (i in 1:nrow(w))
  {
new[i]<-df[,as.integer(i+1)]*w[as.integer(i)]}

The dataset I want is this

col1 col2 col3  col4
1    100   0.1  0.2
2    200   0.2  0.4
3    300   0.3  0.6
4    400   0.4  0.8
1    100   0.1  0.2
2    200   0.2  0.4
3    300   0.3  0.6
4    400   0.4  0.8
1    100   0.1  0.2
2    200   0.2  0.4
3    300   0.3  0.6
4    400   0.4  0.8

what i get instead is this

 col1 col2  col3  col4
  200    200  200  200
    0    0    0     0
    1    1    1     1  
    4    4    4     4
    1    1     1    1
    2    2     2    2
    3    3     3    3
    4    4     4    4
    1    1     1    1
    2    2     2    2
    3    3     3    3
    4    4     4    4

What am I missing????

data.tables do not behave in the same manner as data.frames in many cases. You should probably review the data.table introductory documentation including the [faq](https://rawgit.com/wiki/Rdatatable/data.table/vignettes/datatable-faq.html). 1.1 through 1.3 cover this issue I think. — lmo, Apr 04 '17 at 12:54
Use `data.table(w = c(100,0.1,0.2))` not `data.table(w<-c(100,0.1,0.2))`. The latter creates an object named `w` in the global workspace and doesn't give a name to the column in the data.table. — Frank, Apr 04 '17 at 13:57

score 2 · Answer 1 · edited May 23 '17 at 12:25

As all columns of df are of the same type, the data structure can be treated as a matrix. Thus, the methods described here can be utilized:

# create weight vector spanning all four columns, i.e., also col1
w1 <- c(1, 100, 0.1, 0.2)

# method 1
t(t(as.matrix(df)) * w1)

# method 2
sweep(as.matrix(df), MARGIN = 2, w1, `*`)

Both methods return a matrix as follows

#      col1 col2 col3 col4
# [1,]    1  100  0.1  0.2
# [2,]    2  200  0.2  0.4
# [3,]    3  300  0.3  0.6
# [4,]    4  400  0.4  0.8
# [5,]    1  100  0.1  0.2
# [6,]    2  200  0.2  0.4
# [7,]    3  300  0.3  0.6
# [8,]    4  400  0.4  0.8
# [9,]    1  100  0.1  0.2
#[10,]    2  200  0.2  0.4
#[11,]    3  300  0.3  0.6
#[12,]    4  400  0.4  0.8

which can be converted back to a data.table object if required, e.g.,

as.data.table(t(t(as.matrix(df)) * w1))

akrun · Answer 2 · 2017-04-04T12:58:27.417

We need to specify the .SDcol and multiply by replicating the 'w$V1'

df[, (2:4) := .SD*rep(w$V1, each = .N), .SDcols = 2:4]

If we need for loop, then use the set function from data.table to update the columns by multiplying with the corresponding elements in 'V1' column of 'w'

for(j in 2:ncol(df)){
    set(df, i = NULL, j = j, value = df[[j]] * w$V1[j-1])
 }

df
#    col1 col2 col3 col4
# 1:    1  100  0.1  0.2
# 2:    2  200  0.2  0.4
# 3:    3  300  0.3  0.6
# 4:    4  400  0.4  0.8
# 5:    1  100  0.1  0.2
# 6:    2  200  0.2  0.4
# 7:    3  300  0.3  0.6
# 8:    4  400  0.4  0.8
# 9:    1  100  0.1  0.2
#10:    2  200  0.2  0.4
#11:    3  300  0.3  0.6
#12:    4  400  0.4  0.8

Calculate the product of a column with a vector using for loop

2 Answers2