I'm building a workflow for creating machine learning models (in my case, using Python's
sklearn packages) from data pulled from a very large database (here, Vertica by way of SQL and
pyodbc), and a critical step in that process involves imputing missing values of the predictors. This is straightforward within a single analytics or stats platform---be it Python, R, Stata, etc.---but I'm curious where best to locate this step in a multi-platform workflow.
It's simple enough to do this in Python, either with the
sklearn.preprocessing.Imputer class, using the
pandas.DataFrame.fillna method, or by hand (depending upon the complexity of the imputation method used). But since I'm going to be using this for dozens or hundreds of columns across hundreds of millions of records, I wonder if there's a more efficient way to do this directly through SQL ahead of time. Aside from the potential efficiencies of doing this in a distributed platform like Vertica, this would have the added benefit of allowing us to create an automated pipeline for building "complete" versions of tables, so we don't need to fill in a new set of missing values from scratch every time we want to run a model.
I haven't been able to find much guidance about this, but I imagine that we could:
- create a table of substitute values (e.g., mean/median/mode, either overall or by group) for each incomplete column
- join the substitute value table with the original table to assign a substitute value for each row and incomplete column
- use a series of case statements to take the original value if available and the substitute value otherwise
Is this a reasonable thing to do in Vertica/SQL, or is there a good reason not to bother and just handle it in Python instead? And if the latter, is there a strong case for doing this in pandas rather than sklearn or vice-versa? Thanks!