CODEWITHSUNDEEP
rmatrixsparse-matrix
Nate Thompson
Nate Thompson

Reputation: 635

R - Update Columns in Very Large Sparse Matrix

I need to update certain columns in a sparse matrix, but the operation is taking so insanely long to complete.

I have a sparse matrix with a little less than 3M rows and around 1500 columns. I also have a data frame with the same amount of rows, but only has 10 columns. I want to update certain column indices in the matrix with the values from the data.frame.

I have no problem doing this with a normal matrix, but when trying it with a sparse matrix it takes eons to do even a single column.

Below is the code that I am working with, what needs to be changed so that this can run efficiently?

library(Matrix)

x <- Matrix(0, nrow = 2678748, ncol = 1559, sparse = TRUE)
df <- data.frame(replicate(5,sample(0:1,2678748,rep = TRUE)))

var_nums <- sample(1:1559,size = 5)

for (i in 1:5){
  x[,var_nums[i]] <- df[,i]
}

Upvotes: 2

Views: 547

Answers (3)

Hack-R
Hack-R

Reputation: 23231

I was able to get it to complete in under 1 second using the Matrix::cBind function and by eliminating the for loop.

library(Matrix)

x  <- Matrix(0, nrow = 2678748, ncol = 1559, sparse = TRUE)
df <- data.frame(replicate(5,sample(0:1,2678748,rep = TRUE)))

var_nums <- sample(1:1559,size = 5)

t <- Sys.time()
x            <- x[,-var_nums]
x            <- Matrix::cBind(x, Matrix::as.matrix(df))
Sys.time()-t

Time difference of 0.541054 secs

WITH ORDER PRESERVED (Still under 1 second!)

library(Matrix)

x  <- Matrix(0, nrow = 2678748, ncol = 1559, sparse = TRUE)
df <- data.frame(replicate(5,sample(0:1,2678748,rep = TRUE)))

colnames(x) <- paste("col", 1:ncol(x))
col.order   <- colnames(x)

cols <- sample(colnames(x),size = 5)
colnames(df) <- cols

t <- Sys.time()
x            <- x[,-which(colnames(x) %in% cols)]
x            <- Matrix::cBind(x, Matrix::as.matrix(df) )
x            <- x[,col.order]
Sys.time()-t
>     Time difference of 0.550012 secs

# Proof that order is preserved:
identical(colnames(x), col.order)

TRUE

Upvotes: 3

dww
dww

Reputation: 31454

This is slightly cumbersome, but you can bind the required columns together like this

Nc = NCOL(x)

  Matrix(cbind(
  x[, 1:(var_nums[1]-1)], 
  df[, 1],
  x[, (var_nums[1]+1):(var_nums[2]-1)],
  df[, 2],
  x[, (var_nums[2]+1):(var_nums[3]-1)],
  df[, 3],
  x[, (var_nums[3]+1):(var_nums[4]-1)],
  df[, 4],
  x[, (var_nums[4]+1):(var_nums[5]-1)],
  df[, 5],
  x[, (var_nums[5]+1):Nc]),
  sparse = TRUE)

This is not too bad when df has only 5 columns to insert. If df has more, or a varying number of columns, then a different syntax may be more appropriate. In any case, binding columns is relatively fast.

Upvotes: 0

user20650
user20650

Reputation: 25914

Yuo can use the i, j, x notation of sparseMatrix

library(Matrix)

# data
set.seed(1)
# Changed the dim size to fit in my laptop memory
nc=10
nr=100
n=5

df <- data.frame(replicate(n,sample(0:1,nr,rep = TRUE))) 
var_nums <- sample(1:nc,size = n)

#Yours    
x <- Matrix(0, nrow = nr, ncol = nc, sparse = TRUE)
for (i in 1:n){
  x[,var_nums[i]] <- df[,i]
}

# new version
i = ((which(df==1)-1) %% nr) +1
j = rep(var_nums, times=colSums(df))
y = sparseMatrix(i=i, j=j, x=1, dims=c(nrow(df), nc))

all.equal(x, y, check.attributes=FALSE)

Compare speed

f1 <- function(){     
    for (i in 1:n){
      x[,var_nums[i]] <- df[,i]
    }
    x
}

f2 <- function(){
    i = ((which(df==1)-1) %% nr) +1  
    j = rep(var_nums, times=colSums(df))
    y = sparseMatrix(i=i, j=j, x=1, dims=c(nrow(df), nc))
    y
}

microbenchmark::microbenchmark(f1(), f2())

Unit: milliseconds
 expr      min       lq     mean   median       uq       max neval cld
 f1() 4.594229 4.694205 5.010071 4.770475 4.891649 12.666554   100   b
 f2() 1.274745 1.298663 1.464237 1.329534 1.392146  7.153076   100  a

Try bigger

nc=100
nr=10000
n=50
set.seed(1)
df <- data.frame(replicate(n,sample(0:1,nr,rep = TRUE)))
var_nums <- sample(1:nc,size = n)
x <- Matrix(0, nrow = nr, ncol = nc, sparse = TRUE)

all.equal(f1(), f2(), check.attributes=FALSE)

microbenchmark::microbenchmark(f1(), f2(), times=1)
Unit: milliseconds
 expr         min          lq        mean      median          uq         max neval
 f1() 21605.60251 21605.60251 21605.60251 21605.60251 21605.60251 21605.60251     1
 f2()    60.87275    60.87275    60.87275    60.87275    60.87275    60.87275     1

Upvotes: 1

Related Questions