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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.