fastest way to extend a dataframe to match rownames with a list of names

user_13854241 0
Viewed 110

My goal is to extend a large dataframe with 200'000 columns in the following way:

I have a list of names in a specific order and I want to extend my original dataframe that has some rownames in common with this list of names such that the extended dataframe has a column with entries exactly as in the list of names and wherever values are missing in the dataframe it should have zeroes.

Minimal reproducible example:

entities<-c("C1","C3","C4","C5","E1","E2")
list_of_names<-data.frame(entities)
df<-data.frame(matrix(runif(30), nrow = 3, ncol = 10))
rownames(df)<-c("E1", "C1","C4")


> df
          X1        X2         X3        X4        X5           X6        X7        X8         X9        X10
E1 0.6228159 0.0744723 0.34010018 0.5720812 0.3179460 0.2405663696 0.6198333 0.9367545 0.95729614 0.10671495
C1 0.2435715 0.4046881 0.01714992 0.7806331 0.1113826 0.0003340805 0.1747583 0.5234170 0.07591773 0.76162701
C4 0.3804787 0.8364844 0.85255672 0.8909652 0.0665782 0.7447360640 0.3022620 0.2111203 0.72338528 0.04510692

My current way to do it is the following:

df_extended<-merge(list_of_names,df,by="entities", all.x=TRUE)
df_extended[is.na(df_extended)] <- 0 

> df_extended
  entities        X1        X2         X3        X4        X5           X6        X7        X8         X9        X10
1       C1 0.2435715 0.4046881 0.01714992 0.7806331 0.1113826 0.0003340805 0.1747583 0.5234170 0.07591773 0.76162701
2       C3 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000 0.0000000000 0.0000000 0.0000000 0.00000000 0.00000000
3       C4 0.3804787 0.8364844 0.85255672 0.8909652 0.0665782 0.7447360640 0.3022620 0.2111203 0.72338528 0.04510692
4       C5 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000 0.0000000000 0.0000000 0.0000000 0.00000000 0.00000000
5       E1 0.6228159 0.0744723 0.34010018 0.5720812 0.3179460 0.2405663696 0.6198333 0.9367545 0.95729614 0.10671495
6       E2 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000 0.0000000000 0.0000000 0.0000000 0.00000000 0.00000000

My problem is, that this approach takes too much time to run it for a dataframe having 200'000 columns. So I would appreciate ideas on alternative approaches to speed it up. Thanks for any help!

dataframe r performance
4 Answers

Maybe you can try to initialize a matrix v first and than assign rows in df to the matrix based on matched row names, e.g.,

v <- matrix(0,nrow = nrow(list_of_names),ncol = ncol(df))
inds <- match(row.names(df),list_of_names$entities)
v[inds,] <- as.matrix(df)
df_extended <- cbind(list_of_names,`colnames<-`(v,names(df)))

such that

> df_extended
  entities         X1          X2        X3        X4        X5        X6
1       C1 0.51539985 0.008607194 0.1990575 0.4867223 0.4218548 0.2930240
2       C3 0.00000000 0.000000000 0.0000000 0.0000000 0.0000000 0.0000000
3       C4 0.01436587 0.907402197 0.7287643 0.7181379 0.1040645 0.0180306
4       C5 0.00000000 0.000000000 0.0000000 0.0000000 0.0000000 0.0000000
5       E1 0.32819899 0.630265406 0.3515301 0.2034940 0.2318559 0.1730483
6       E2 0.00000000 0.000000000 0.0000000 0.0000000 0.0000000 0.0000000
           X7        X8        X9       X10
1 0.934944232 0.4179499 0.9071613 0.3573136
2 0.000000000 0.0000000 0.0000000 0.0000000
3 0.004799636 0.4632679 0.2883067 0.9171526
4 0.000000000 0.0000000 0.0000000 0.0000000
5 0.470242031 0.0498446 0.8202716 0.2161722
6 0.000000000 0.0000000 0.0000000 0.0000000

Performance Test

Given df of 2e6 columns

entities<-c("C1","C3","C4","C5","E1","E2")
list_of_names<-data.frame(entities)
df<-data.frame(matrix(runif(30), nrow = 3, ncol = 2e6))
row.names(df)<-c("E1", "C1","C4")

we wrap the code to a function f,

f <- function() {
  v <- matrix(0, nrow = nrow(list_of_names), ncol = ncol(df))
  inds <- match(row.names(df), list_of_names$entities)
  v[inds, ] <- as.matrix(df)
  df_extended <- cbind(list_of_names, `colnames<-`(v, names(df)))
}

and then we will see

> system.time(f())
   user  system elapsed 
  13.67    1.25   14.93
user_121587570

It is very easy to do an assignment with setdiff

df[setdiff(entities, row.names(df)),] <- 0
df
#          X1         X2        X3        X4         X5        X6        X7         X8        X9       X10
#E1 0.4820426 0.67437639 0.3518886 0.9188573 0.72839443 0.3952201 0.6982616 0.42842151 0.2608569 0.8319276
#C1 0.2529649 0.04766363 0.4089440 0.2825283 0.68637508 0.4778454 0.9156835 0.54208037 0.3971520 0.1528872
#C4 0.2162548 0.70085309 0.8209513 0.9611048 0.05284394 0.5602533 0.6183512 0.05847849 0.1977447 0.8034185
#C3 0.0000000 0.00000000 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000
#C5 0.0000000 0.00000000 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000
#E2 0.0000000 0.00000000 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000
user_37322710

I would suggest next dplyr approach. It is known that merge() can be slow but dplyr functions for merging are faster. Here the code:

library(dplyr)
#Data
entities<-c("C1","C3","C4","C5","E1","E2")
list_of_names<-data.frame(entities)
df<-data.frame(matrix(runif(30), nrow = 3, ncol = 10))
df$entities<-c("E1", "C1","C4")
#Dplyr
df_extended <- list_of_names %>% left_join(df) %>% replace(is.na(.),0)

Output:

  entities        X1        X2        X3         X4        X5        X6        X7        X8
1       C1 0.2408607 0.2157208 0.5725859 0.03198733 0.0177955 0.8019684 0.5763417 0.6669142
2       C3 0.0000000 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000 0.0000000 0.0000000
3       C4 0.1108980 0.7621460 0.7364038 0.78398394 0.1731782 0.3135231 0.3868382 0.8729648
4       C5 0.0000000 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000 0.0000000 0.0000000
5       E1 0.7463177 0.7176220 0.8183764 0.03917483 0.1713210 0.3941307 0.1493993 0.3036486
6       E2 0.0000000 0.0000000 0.0000000 0.00000000 0.0000000 0.0000000 0.0000000 0.0000000
         X9       X10
1 0.6231661 0.4644523
2 0.0000000 0.0000000
3 0.8008057 0.5163706
4 0.0000000 0.0000000
5 0.2136563 0.1320652
6 0.0000000 0.0000000

Also, testing with a larger dataframe (10.000 columns) this is the performance (including the filling zero stage):

system.time({list_of_names %>% left_join(df) %>% replace(is.na(.),0)})
Joining, by = "entities"
   user  system elapsed 
   8.10    0.29    8.96
user_20808480

Could you keep things as matrices and fill a shell container? Takes about 8.6ms for 200k cols on my machine.

entities<-c("C1","C3","C4","C5","E1","E2")

# Zero filled container
shell <- matrix(rep(0,length(entities)*10), length(entities),10)
rownames(shell) <- entities

# Data to fill container based on row
m <- matrix(runif(30), nrow = 3, ncol = 10)
rownames(m)<-c("E1", "C1","C4")

# Fill the container
shell[rownames(m),] <- m

shell
user_92165120
Related