I'm building a workflow for creating machine learning models (in my case, using Python's pandas and 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 columnjoin the substitute value table with the original table to assign a substitute value for each row and incomplete columnuse a series of case statements to take the original value if available and the substitute value otherwiseIs 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!
Where in the workflow should we deal with missing data?
machine learning;python;pandas;scikit learn