So I am working with some data on water wells and time series of chemical pollutant tests on those wells. There are 10 chemicals and 10 years in the data. My goal is to do some clustering on the wells to see if there are certain wells that have very similar chemical pollutant profiles. So I want to first perform Principal Components to reduce the dimensionality and then use UMAP to detect clusters. Of course PCA for time-series might not be the best approach, but for a preliminary analysis it seems to be just fine.
This question is about identifying the right imputation versus interpolation strategy for this dataset. I was not sure what the current best practices were given the type of issues I encounter below. So let me set the scene and then detail the imputation/interpolation problem.
The data is rather tricky though, because there are 1000s of wells spread across California, so not all of them are tested at the same time, meaning that while one well might have measurements on a chemical in years 2015, 2018, and 2019, a different well might have measurements for 2016, 2018. Even more than that, the dates of the measurements are different, so there are a very large number of day/month/year combinations. In some cases there are multiple measurements in the same year. Here is a sample of the data.
s_wellid f_results s_chemical d_datestamp 0103039-002 14 NO3N 2010-07-26 0103039-004 5.7 NO3N 2017-11-22 0103041-001 4.3 AS 2011-12-15 0103041-001 2.3 AS 2015-07-14 0103041-001 0 AS 2019-02-26 0103041-001 0 CR6 2014-11-19 0103041-001 0 DBCP 2015-07-14 0103041-001 0 DBCP 2019-02-26 0103041-001 3.4 NO3N 2009-11-23 0103041-001 2.5 NO3N 2011-10-18 0103041-001 2.3 NO3N 2011-12-15 0103041-001 4.3 NO3N 2012-11-14 0103041-001 2.2 NO3N 2013-12-18 0103041-001 1.9 NO3N 2014-12-22 0104010-003 4 PCATE 2012-06-22 0104010-003 0.5 PCE 2012-06-22 0104010-003 0.5 TCE 2012-06-22
So for PCA I was thinking that I would pivot this data so that each well was the index for the rows and then each chemical-year combination would be the columns. I would of course take the average of measurements in a year. That looks like this.
s_wellid NO3N_2017 AS_2015 CR6_2014 NO3N_2014 NO3N_2015 NO3N_2016 NO3N_2019 PCATE_2015 PCE_2015 ... TCE_2014 TCE_2017 TCE_2018 TDS_2016 TDS_2017 f_latitude f_longitude s_welltype s_sourcename s_othernames 0 0103039-004 5.7 NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN 37.639940 -122.117632 MUNICIPAL 0103039-004 WELL 04 1 0103041-001 NaN 2.3 0.0 1.9000 1.8500 4.00 3.8 0.0 0.0 ... NaN NaN NaN NaN NaN 37.726859 -122.157248 MUNICIPAL 0103041-001 WELL 01 2 0104010-003 NaN NaN NaN 0.2000 0.0000 NaN NaN NaN NaN ... NaN NaN NaN NaN
So of course when I do this, I end up with a lot of
NaNs for wells that had no measurements in given years. Now the usual strategy here is to impute or interpolate the missing data. But the challenge is that some of the missingness is quite high, like many columns have 70-85% missing. If I chop out the highly missing columns I only have very few columns left and PCA is almost not necessary.
First, I think that a lot of the missingness might be due to some peculiarities in the testing. For example the
NO3N Nitrate chemical might have been very selectively sampled in some small regions or years, and hence when I expand that out to the full range of years I end up with a lot of missing data because not all wells were covered. So I need to dig in and remove wells that are lacking the full profile in all 10 chemicals.
But besides my digging into the data a little more, I wanted to understand the best way to approach dealing with the missing data from a time-series perspective. I think that there is a combination of interpolation and extrapolation here. For example, if I sample from 2010-2018 and a well only has data for 2012 and 2017 then I can interpolate between 2012 and 2017. But outside of that range involves extrapolation--which is of course dangerous to do.
Would the best strategy be to interpolate where possible, and then use something like EM algorithm to impute values outside of the range? Like I said, I was not sure that the current best practice might be.
Sorry for the very long post,but thanks for any insights or suggestions.