CODEWITHSUNDEEP
rperformancedataframe
neuron
neuron

Reputation: 2059

Fastest way to replace multiple values in large data.frame

I have an extremely large dataframe, and I need to replace different values in the dataframe. Over time I have written a couple of different ways to replace the values that I need to change. Here is a subset of data so you can see what I am talking about 

df <- structure(list(CHROM = c("chr1", "chr1", "chr1", "chr1", "chr1", 
"chr1", "chr1", "chr1", "chr1", "chr1", "chr1", "chr1", "chr1", 
"chr1", "chr1", "chr1", "chr1", "chr1", "chr1", "chr1"), POS = c(619L, 
668L, 744L, 745L, 1064L, 1099L, 1121L, 1123L, 1126L, 1193L, 1208L, 
1214L, 1250L, 1265L, 1274L, 1277L, 1283L, 1307L, 1314L, 1325L
), `GEN[D86396].GT` = c("0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0"), `GEN[D86397].GT` = c("0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0"), `GEN[D00105].GT` = c("0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0"), `GEN[D00151].GT` = c("0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0"), `GEN[D00188].GT` = c("0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0"), `GEN[D00220].GT` = c("0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0"), `GEN[D00257].GT` = c("0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0"), `GEN[D00258].GT` = c("0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0"), `GEN[D00264].GT` = c("0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0"), `GEN[D00268].GT` = c("0/0", 
"0/0", "0/0", "0/0", "0/1", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/1", 
"0/0"), `GEN[D00269].GT` = c("0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0"), `GEN[D00270].GT` = c("0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0"), `GEN[D00271].GT` = c("0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0"), `GEN[D00276].GT` = c("0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0"), `GEN[D00280].GT` = c("0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0"), `GEN[D00282].GT` = c("0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/1", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0"), `GEN[D00285].GT` = c("0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0"), `GEN[D00315].GT` = c("0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0"), `GEN[D00316].GT` = c("0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0"), `GEN[D00319].GT` = c("0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0"), `GEN[D00339].GT` = c("0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", "0/0", 
"0/0", "0/0", "0/0", "0/0", "0/0", "0/0")), row.names = c(NA, 
20L), class = "data.frame")

So what I need to do is convert the "0/0" into 0, "0/1" into 1, "1/1" into 2, and "./." into 0.1 (which I don't think there are any in this example).

In the past, I have used the following 

replacement<-function(x){
  x=replace(x,which(x=='./.'),0.01) 
  x=replace(x,which(x=='0/0'),0)
  x=replace(x,which(x=='0/1'),1)
  x=replace(x,which(x=='1/1'),2)
}
df=apply(df,2,replacement)
df <- as.data.frame(df)

Which is okay, but it still takes hours to run. I have also used this.

df <- df %>% mutate_at(
  vars(- CHROM, - POS),
  funs(case_when(
    . == "0/0" ~ 0,
    . == "0/1" ~ 1,
    . == "1/1" ~ 2,
    . == "./." ~ 0.01
  ))
)

Which is also just okay. I realize, with large datasets, somethings will take a long time to run. I am just curious what the fastest method out there for replacing values. I have seen a lot of other posts asking a similar question regarding NAs, but I haven't been able to find any questions related to mine. I think using data.table might be the fastest method? Or maybe converting the dataframe to a matrix? I am sure what you think.

Thanks in advance!

Upvotes: 2

Views: 851

Answers (2)

F. Priv&#233;
F. Priv&#233;

Reputation: 11728

A fast and easy solution is to use a lookup table:

lookup_table <- c("0/0" = 0, "0/1" = 1, "1/1" = 2, "./." = 0.1)
df[-(1:2)] <- lapply(df[-(1:2)], function(x) lookup_table[x])

Equivalent (might use less max memory):

for (j in 3:length(df)) df[[j]] <- lookup_table[df[[j]]]

Benchmark

N <- 100e3
M <- 340
df <- data.frame(CHROM = 1, POS = seq_len(N))
for (j in 3:M) df[[j]] <- sample(c("0/0", "0/1", "1/1", "./."), N, TRUE)

system.time({
  lookup_table <- c("0/0" = 0, "0/1" = 1, "1/1" = 2, "./." = 0.01)
  df2 <- df
  df2[-(1:2)] <- lapply(df2[-(1:2)], function(x) lookup_table[x])
})
# 1.5 sec

system.time({
  replacement <- function(x) {
    x = replace(x, which(x == './.'), 0.01)
    x = replace(x, which(x == '0/0'), 0)
    x = replace(x, which(x == '0/1'), 1)
    x = replace(x, which(x == '1/1'), 2)
  }
  df3 <- as.data.frame(apply(df, 2, replacement), stringsAsFactors = FALSE)
})
# 4.5 sec

library(dplyr)
system.time({
  df4 <- df %>% mutate_at(
    -(1:2),
    ~ case_when(
      . == "0/0" ~ 0,
      . == "0/1" ~ 1,
      . == "1/1" ~ 2,
      . == "./." ~ 0.01
    )
  )
})
# 5.2 sec

Upvotes: 1

neuron
neuron

Reputation: 2059

I looked at some sed commands and I figured I should post what I found just in case someone has a similar issue.

The sed commands that I found to work in terminal are (This creates a new file, but you don't have to create new files)

sed -e 's+0/0+0+g' -e 's+0/1+1+g' -e 's+1/1+2+g' -e 's+./.+0.01+g R.test.txt > R.test.edit.txt

or this works as well in R

system(paste(sed -e 's+0/0+0+g' -e 's+0/1+1+g' -e 's+1/1+2+g' -e 's+./.+0.01+g R.test.txt > R.test.edit.txt))

You can also use the data.table::fread method mentioned by IceCreamToucan 

df <- fread("sed -e 's+0/0+0+g' -e 's+0/1+1+g' -e 's+1/1+2+g' -e 's+./.+0.01+g' /R/R.test.txt")

It interesting to note that typically the sed command you use is 

sed 's/old text/new text/g' file > new.file

but since what I needed to replace had a forward slash already / I had to use the + plus sign so sed doesn't get confused.

I am going to do a performance test using my two older methods (posted above), the new sed method, and F. Prive's method that he posted as an answer. I am going to make a smaller subset of the full dataset because it would take too long to test the four methods.

EDIT

So I tested the four different methods out to see which one was fastest. I created a smaller file to test the four methods out. The file I created had 1000000 rows and 340 columns.

METHOD 1 

lookup_table <- c("0/0" = 0, "0/1" = 1, "1/1" = 2, "./." = 0.1)
df[-(1:2)] <- lapply(df[-(1:2)], function(x) lookup_table[x])

Runtime - 8 minutes

METHOD 2 

replacement<-function(x){
  x=replace(x,which(x=='./.'),0.01) 
  x=replace(x,which(x=='0/0'),0)
  x=replace(x,which(x=='0/1'),1)
  x=replace(x,which(x=='1/1'),2)
}
df=apply(df,2,replacement)
df <- as.data.frame(df)

Runtime - 46 seconds

METHOD 3 

df <- df %>% mutate_at(
  vars(- CHROM, - POS),
  funs(case_when(
    . == "0/0" ~ 0,
    . == "0/1" ~ 1,
    . == "1/1" ~ 2,
    . == "./." ~ 0.01
  ))
)

Runtime - 42 seconds

METHOD 4

df <- fread("sed -e 's+0/0+0+g' -e 's+0/1+1+g' -e 's+1/1+2+g' -e 's+./.+0.01+g' /R/R.test.txt")

Runtime - 2 min 34 seconds, which was surprising

Conclusion - I wasted my time

Upvotes: 1

Related Questions