R: Subset Large Data Frame with Multiple Conditions

Question

I have a large data frame with 12 million rows and 5 columns. I want to subset the large data frame with multiple conditions. I need to do this multiple times with different criteria, so I created a Look-Up Table and a for loop.

The code below loops through and subsets the large data frame, saving each iteration as an list within a list. After the loop completes, I combined the lists into a data frame.

My current set-up functions, but it is painfully slow (about 15 minutes for 8 loops). Subsetting is actually taking more time than it took to calculate the mean and SD for the 12 million-row table!

Any advice on how to speed this up?

>scaled
| chr  | site | Average_CPMn | SD_CPMn |
|------|------|--------------|---------|
| chrI | 1    | 0.071        | 0.070   |
| chrI | 2    | 0.120        | 0.111   |
| chrI | 3    | 0.000        | 0.000   |
| chrI | 4    | 0.000        | 0.000   |
| chrI | 5    | 0.000        | 0.000   |
| chrI | 6    | 0.156        | 0.056   |
...12,000,000 rows

>genes.df
| Gene    | Chromosome | Meta_Start | Meta_Stop |
|---------|------------|------------|-----------|
| YGL234W | chrVII     | 55982      | 59390     |
| YGR061C | chrVII     | 611389     | 616465    |
| YMR120C | chrXIII    | 507002     | 509780    |
| YLR359W | chrXII     | 843782     | 846230    |

scaled <- read_rds("~/Desktop/scaled.rds")
subset_list = list()
for (i in 1:nrow(genes.df)) {
  subset <- scaled %>%
    dplyr::filter(chr == genes.df$Chromosome[i] & site >= genes.df$Meta_Start[i] & site <= genes.df$Meta_Stop[i]) %>%
    dplyr::mutate(Gene = genes.df$Gene[i])
  subset_list[[i]] <- subset

#combine gene-list into single dataframe
counts_subset <- as.data.frame(do.call(rbind, subset_list)) %>%
  left_join(genes.df, by = "Gene")

When it comes to speed, data.table or dtplyr are always worth a try — Alex

AnilGoyal AnilGoyal · Accepted Answer · 2021-05-11T13:53:33

You havne't shared the data/sample so it is difficult to demostrate, however, it is suggested to use semi_join (if you want to subset only) or left_join (if want to mutate instead) somewhat like this in tidyverse

scaled %>% semi_join(genes.df %>% pivot_longer(c(Meta_start, Meta_stop)) %>% 
                                  group_by(Gene, Chromosome) %>%
                                  complete(value = seq(min(value), max(value), 1)) %>%
                                  ungroup %>% select(-name), by = c('chr' = 'Chromosome', 'site' = 'value'))

R: Subset Large Data Frame with Multiple Conditions

1 Answers