3
$\begingroup$

Question Description

We are performing a lot of timeseries queries, these queries sometimes result in issues, they are usually performed through an API (Python) and sometimes result in complete failure due to data missing.

Due to this situation we are not sure where to educate ourselves and get the answer to this specific question on, how to deal with missing data in our timeseries (influxdb) database

Example

To describe a problem in an example..

We have some timeseries data, let's say we measure the temperature of the room, now we have many rooms and sometimes sensors die or stop working for a week or two, then we replace them and so on, in that timeframe the data is missing.

Now we try to perform certain calculations, they fail, let's say we want to calculate the temperature average per each day, now this will fail because some days we have no measurement input on the sensors.

One approach that we thought of is that we just interpolate the data for that day. Use the last and the first available and just place that value for the days that there is no data available.

This has many downsides, major one being due to fake data, you can't trust it and for our processes that are a bit more serious we would prefer to not store fake data (or interpolated).

We were wondering what the possible alternatives were to this question and where can we find the resource to educate ourselves on such topic.

$\endgroup$

2 Answers 2

3
$\begingroup$

Here is an excellent resource for handling missing values:

Missing Data? How to handle missing values in Python

In my experience, you should forecast the values for the specific time series scenario. Say that you have a time series $(x_1,x_2,\ldots,x_n,NA,NA,\ldots,NA,x_m,x_{m+1},\ldots)$ where $x_i \in \mathbb{R}$ are observed (real number) values and $NA$ are "not available" ones (i.e., missing). For simplicity and without loss of generality, assume that you have a forecasting model $f$ able to take $n$-length long time series to predict the next value, namely, $f : \mathbb{R}^n \to \mathbb R$. Now, you can use $f$ to predict the $(n+1)$th value, that is, $f(x_1,x_2,\ldots,x_n) = \hat x_{n+1}$. You can repeat the process as $f(x_2,x_3,\ldots,x_n,\hat x_{n+1}) = \hat x_{n+2}$, and so on, until you get $\hat x_{m-1}$ (which is before $x_m$).

The approach is not flawless because each (predicted) $\hat x$ contains an amount of error, which will eventually diverge. Nevertheless, it is a widespread methodology in time series analysis.

If the technique appeals to you, try darts for time series forecasting (and anomaly detection), which is open-sourced and in Python.

$\endgroup$
3
  • $\begingroup$ The question is also not only on analysis, does it make sense to do it whenever we are storing data that we are pulling. $\endgroup$
    – innicoder
    Feb 6, 2023 at 20:02
  • $\begingroup$ Indeed, I assume you have access to such data, which can be pulled, processed, and then stored for downstream tasks. I am not an expert on InfluxDB, but (IMHO) the proposed method is general enough to address your issue. As I understand it, "fake data" is a must in your setting; if there were no missing values, there would be no problem. Moreover, I feel that you want to handle missing values, so dropping such information is not a good strategy. I want to add more to my answer if there are more details that I have missed. Cheers! $\endgroup$
    – Eduard
    Feb 6, 2023 at 20:37
  • $\begingroup$ yes, you explained the process exactly, indeed how do I handle missing data, it is only a must for analysis, not the data itself, just because we have a lot of queries and the easiest would be to fill missing data but at the other hand is it a good move since it is fake data and these systems are quite important for us, ideally we would not want fake data (interpolated). It is not a must just the analysis (queries) have to be performed and work, without data they don't work. Maybe they shouldn't work but those are the questions. $\endgroup$
    – innicoder
    Feb 7, 2023 at 8:09
3
$\begingroup$

Answer

The idea is that we fill the missing values, the gaps, with data that is null or None. This way we can use influxdb built-in fill. https://docs.influxdata.com/influxdb/cloud/query-data/flux/fill/

enter image description here

Like in this example, we are able to fill null values and thereby perform any additional queries and actions on the data on analysis.

The link reference above contains all of the methodologies that we can use to resolve and fill in the missing data values.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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