Where in the workflow should we deal with missing data?

Question

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:

1. create a table of substitute values (e.g., mean/median/mode, either overall or by group) for each incomplete column
2. join the substitute value table with the original table to assign a substitute value for each row and incomplete column
3. 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!

Answer 1

My strong opinion regarding automated tasks like imputation (but, here I can include also scaling, centering, feature selection, etc) is to avoid in any way do such things without carefully inspecting your data.

Of course, after deciding what kind of imputation to apply it can be automated (under the assumption that the new data has the same shape/problems).

So, before anything, take a wise decision. I often wasted time trying to automate this things, destroying my data. I will give you some examples: - a marketplace encoded as N/A, which I missed and considered to be North/America - numbers like -999.0, because the data producer could not find a better replacement for missing data - number like 0 for blood pressure or body temperature, instead of missing data (it is hard to imagine a living human with 0 blood pressure) - multiple placeholders for missing data, due to the fact that the data was collected from various sources

After that you need to understand what kind of imputation would resemble better the information from your data for a given task. This is often much harder to do it right than it seems.

After all those things, my advice is to delay your imputation task to an upper layer where you have tools to reproduce on new data and to inspect if the assumptions for the new data are not violated (if it is possible).

Answer 2

Therriault, really happy to hear you are using Vertica! Full disclosure, I am the chief data scientist there :) . The workflow you describe is exactly what I encounter quite frequently and I am a true believer in preprocessing those very large datasets in the database prior to any pyODBC and pandas work. I'd suggest creating a view or table via a file based query just to ensure reproducible work. Good Luck