The question is: Can I delete all the rows of a certain id because of its missing data, before the data analysis? To be more specific I will give the context and the specific problem:


I have a daily time series dataframe from 1960 to 2018, where each observation is a measurement of precipitation, temperature, and level related to 133 rivers (the ids are the rivers). So, for each river, I will have all the measurements needed, for a given day, in a given year, between 1960 and 2018. I need to predict the river level.

I did this missing value plot to get some insights (the lower the percentage, the lower is the data for that flow in that year):

enter image description here

The inputs

There are three insights from this plot that concern me in order to delete or not delete missing values (and when):

  1. There is an increasing number of distinct rivers per year since 1960.
  2. 2018 has a lot of missing data (which is because all the data stops at March of that year).
  3. You have some rivers without data for the last 35 years (83 to 87 rivers in the plot, for example). And you have some rivers without data for the last 10, 5, or 2 years (you can see a lot of rivers with 2017 and 2018 data missing in the graph).

The problem

Should I consider all the rivers data for prediction even of those rivers that don't have data for the last X years? Because it doesn't make sense to predict the next year without the data of the last X years (but I do have the data of 1980 to 2000, for example). If I should consider it, what kind of methodology should I use to predict, if is there any?. If I shouldn't consider it, should I delete it? If I should delete it, when should I delete it? Before or after the data analysis for prediction? For example, before or after analyzing the distributions? (in order to group the rivers to impute the missing values as they have different distributions, but I could find a way to group similar distributions). It makes sense to me to delete them before the analysis, as the noise for prediction will be lower as the data for the analysis will be consistent with the data for prediction.

However, if I don't delete them at all I will have more data if I want to see some overall analysis (i.e. the trend from 1960 to 2018 will be a more "representative trend"). Deleting after, in terms of prediction (and insights for feature selection) it will increase the noise as the data for analysis will be different to the data for prediction (as I will have deleted complete data for some rivers).

Besides, I'm thinking of making the assumption that if the river doesn't have data from the last 3 years, the station measuring the river is not operative. But again, if the station is not operative, should I consider its data for overall analysis/insights?.


On the one hand, you should not be deleting anything. Information is power. On the other hand, I am highly skeptical about your ability to perform missing data imputation for the missing river-days. It is not clear how different rivers are related, and missing data imputation adds value only if different variables (rivers) are highly co-dependent. What you can do, however, is writing a parametric model for all the rivers. In this model

  • different rivers are allowed to be correlated,
  • any particular river is described by, say, an ARIMA process with unique parameters.

Then, you can estimate the model using the version of Full Information Maximum Likelihood (FIML) for time series. You would need to look up a standard reference on FIML and read it carefully. In a nutshell, FIML does the following.

  • The log-likelihood function has only terms corresponding to non-missing values.
  • All non-missing values are used in at least one term (of the log-likelihood).
  • Suppose, in time series $X_j$, values $X_j(t)$ and $X_j(t+3)$ are marked but values $X_j(t+1)$ and $X_j(t+2)$ are missing. Then the log-likelihood function contains term

$$ ... + \log(f_{M,3}(X(t+3) | X(t))) + ... $$
where $f_{M,3}(X(t+3) | X(t))$ is the model-implied conditional density of $X_j(t+3)$ given $X_j(t)$. You see that we are skipping missing cells $X_j(t+1)$ and $X_j(t+2)$.

  • 1
    $\begingroup$ Sounds sensible. Kalman filter and expectation-maximization (EM) algorithm may be relevant keywords (in addition to FIML). $\endgroup$ Nov 18 '19 at 13:39
  • 1
    $\begingroup$ @RichardHardy Thank you. Agree: EM algorithm and filtering are other interesting directions. $\endgroup$
    – stans
    Nov 18 '19 at 13:57
  • $\begingroup$ Thanks. I do not think this is all that distinct from what you are suggesting. FIML, ARIMA, state-space models (another relevant keyword) and Kalman filter are all related. $\endgroup$ Nov 18 '19 at 15:12

I would leave all of the data in, but then I would dig through to make sure there were not patterns misleading my data (e.g., that all of the new rivers are smaller because the state has been adding monitoring sites but already had the major rivers covered).

The other caveat is that you may need to use precipitation in the preceding X days as a predictor. In that case, you would need to delete the first X days each time observations start or be able to determine the precipitation from some other source (e.g., if that is from a local weather station that was recording, even though the river monitor was not).


Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.