# How to approach memory issues with up/down-sampling problem across millions of rows in database that can't be loaded locally? (class imbalance)

I'm faced with fairly typical class imbalance problem across a dataset with nearly 9MM rows (hard drive failures) that's not stored locally (it's in Postgres table; downloading a .csv of it is not possible). I want to build a classification model that predicts failure [0,1] (limited to the Top-10 models by frequency) using a tree-based method across 11 features. Here's what my R code looks like, so far:

rm(list=ls()); gc()  # Cleanup

library(tidyverse)
library(lubridate)
library(caret)

options(tibble.width = Inf)  # Prints all columns in a tibble in the console

#' Create connection to SQL db:
cxn <- DBI::dbConnect(RPostgreSQL::PostgreSQL(),
host = "...",
port = 5432,
dbname = "...",
user = "...",

hard_drive_stats <- tbl(cxn, "hard_drive_stats")

#' Get Top-10 most common hard drives:
top_10_drives <- hard_drive_stats %>%
group_by(model) %>%
summarise(n = n()) %>%
arrange(desc(n)) %>%
top_n(10) %>%
collect()

# A tibble: 10 x 2
model                         n
<chr>                     <dbl>
1 ST4000DM000             2822282
2 HGST HMS5C4040BLE640    1363173
3 ST12000NM0007           1296465
4 ST8000NM0055            1293557
5 ST8000DM002              888774
6 HGST HMS5C4040ALE640     505045
7 ST6000DX000              169017
8 Hitachi HDS5C4040ALE630  115984
9 ST10000NM0086            109738
10 HGST HUH728080ALE600      94024

#' Get a count, by model, of failures within dataset:
failed_drives <- hard_drive_stats %>%
filter(failure == 1) %>%
group_by(model) %>%
summarise(n_failure = n()) %>%
arrange(desc(n_failure)) %>%
collect()  # sum(failed_drives[["n_failure"]])  # Note: we only have 336 instances of failed drives in the entire dataset (8.9MM records)

#' After, JOIN-ing the above together, we confirm that we're dealing with a class imbalance problem (e.g. there are *very* few failures across millions of drives).
#' Thus, we need to make a decision between over- or under-sampling and do k-fold cross-validation.
top_drives_with_failures <- inner_join(top_10_drives, failed_drives, by = "model") %>%
mutate(pct_failure = n_failure/n) %>%
arrange(desc(n))  # sum(top_drives_with_failures[["n_failure"]])/sum(top_drives_with_failures[["n"]])  # about 0.003% failure
#' Note: two drive models in the top-10 were excluded from this because they didn't have any failures

> top_drives_with_failures
# A tibble: 8 x 4
model                      n n_failure pct_failure
<chr>                  <dbl>     <dbl>       <dbl>
1 ST4000DM000          2822282       178  0.0000631
2 HGST HMS5C4040BLE640 1363173        16  0.0000117
3 ST12000NM0007        1296465        32  0.0000247
4 ST8000NM0055         1293557        28  0.0000216
5 ST8000DM002           888774        21  0.0000236
6 HGST HMS5C4040ALE640  505045         8  0.0000158
7 ST6000DX000           169017         1  0.00000592
8 HGST HUH728080ALE600   94024         3  0.0000319


In theory, I understand the trade-offs between over/under-sampling, but how would you approach this problem in the context of memory constraints?

It's not feasible to load all 8+ million rows (combined across all top models) into local memory, partition between test/training set, and run the analysis. So instead my ideas were to either:

1. Build/train/cross-validate the model on 1 model only, then test it on others. Here's a plot of the log-density of failure rate (across those drives that had at least 1 failure):

1. Initially down-sample the "good" drives (where failure == 0) to 10-100K each and keeping all the "bad" ones

The second seems like it would introduce bias, but when the class imbalance is so high, does this become trivial? From a high level, which of these (if any) would be the preferred method? Or, alternatively, is there a technical way to solve this w/o blowing up the memory usage on my local machine?

• How many covariates do you have and what kind of model would you like to fit? – whuber Jan 16 at 16:10
• @whuber There's 14-15 features (all numeric). As for which type of model, I was going to start with a tree-based method. – Ray Jan 16 at 16:16
• That looks like important information to include prominently in your post. – whuber Jan 16 at 17:37
• "Good" vs. "not good" are classical ill-conditioned problems. Not only becaus failures are rare, but also because failures are probably not a well-defined group but can be due to various causes. You may want to look into one-class classification (which doesn't stop you from modeling, say, "good" as well as a bunch of classes for specific failure groups). – cbeleites Jan 17 at 13:28