Replace missing data with zero

[Curious George]

I have gaps in my signal with missing data and would like to replace the gaps with zero.  Man with the yellow hat is not around and I could use some help. 

[Sanman Mehta]

There are many ways to accomplish this task.  See below for one example formula that you can paste into the formula tool and apply to input signal.  You can also checkout the comments to see how it works and ways to tweak it.  The transform function is very powerful and it allows the user to evaluate expressions from input signals to create new derived signals with applied logic.

$boolean = $signal.transform($s ->sample($s.getkey(), 1 * $s.getValue().isValid()), 1 d).toStep()
//in the above formula, $signal is the input signal that has missing data gaps.
//boolean variable is created and will return either 1 or 0 based on whether "isValid()" is true or not respectively.  When there is gaps in the data the //resulting signal is zero and when there is valid value, the result is 1.

  
$boolean * $signal.setMaxInterpolation(1wk).validValues()
//the next step is to use the calculated $boolean signal above and multiply it (0 or 1) with original input signal to get actual values and gaps will be //replaced with zeros.
//$signal again is the input signal with missing data.
//setMaxInterpolation (1wk) can be increased as needed to fill the data gap with zero.

 

[dkuecker]

Would there be any way to do this for a signal that has no values after or before a certain date?  For example:  I have a signal that has values from  01/01/2012 until 10/22/2014.  I want to insert zeros from when it stops at 10/22/2014 to the current time.  I also have signals that have values from 12/31/2016 to the current time that I want to insert zeros from 01/01/2012 to 12/31/2016.

I tried the formulas posted by @Sanman Mehta, but it doesn't insert zeros after the signal stops.

Example Screenshot:

 

Edited October 24, 2018 by dkuecker

[Ben Johnson]

Here's another approach to replacing empty regions of the $choppySignal with 0 . The quick answer:

$invalidCondition = days().minus($choppySignal.toCapsules())
$choppySignal.splice(0.toSignal(), $invalidCondition)

How does that work? The goal is get to the last line using splice, replacing the gaps with 0. The trick is defining $invalidCondition. The straightforward way to create it is with the inverse of the valid regions of the signal:

$invalidCondition = inverse( $a.validityCapsules(1wk), 2wk)

But that suffers from performance problems because you need to choose the maxDuration for both when $choppySignal is good and when it's not (1wk and 2wk in this case).

We don't really care about creating 1 capsule per gap because splice works just fine during adjacent capsules. So we can get rid of the inverse() by using minus(). We start with days() to make capsules that are always adjacent. Then we use minus() operator to remove the portion of those days where data is present. 

$invalidCondition = days().minus($choppySignal.validityCapsules(1wk))

But we still have the maxDuration of the validity capsules. Again, we don't care about getting a single capsule of validity; minus can handle adjacent capsules. The toCapsules() will create a condition with lots of short adjacent capsules. So now we have a performant condition of when the data doesn't exist.

$invalidCondition = days().minus($a.toCapsules())

You can see where the adjacent days() comes into play when you change the display style of the resulting signal to show the samples. You can clearly see the daily sample during the zero portions

Edited October 25, 2018 by Ben Johnson

[Ben Johnson]

To @dkuecker's question, I believe my example will work for all time without requiring any specific dates to be chosen. The shortcoming is everything after the signal ends is going to be considered "uncertain" (the zero values will show as a dotted line). The system is simply waiting for more data to arrive, and not making any assumptions that if data hasn't arrived since 2016, there's probably not any more coming.

[Allison Buenemann]

In newer versions of Seeq, we have a formula operator designed to do exactly this task. 

In the example below, I have a signal with gaps where valid data does not exist. 

I can use formula to replace the gaps with 0. The formula is the following:

$signalwithmissingdata.replaceNotValid(0)

The output of this formula is:

Content Verified FEB2024

Edited February 16 by Kin How
Adding Content Verified text at the end of the answer

[Ross Wilson]

What is the equivalent when dealing with String / Boolean values?  I have an "ON" / "OFF" signal that is intermittently bad.  Using .replaceNotValid("ON") works in Trend view, but the Treemap view generates an error.  Any advice how to resolve this for the Treemap?

[John Cox]

Hi Ross,

The same approach should work when dealing with string signals. This sounds like a potential bug that I would suggest you create a support ticket for by going to https://support.seeq.com/, then clicking on Support Portal in the lower right, then creating an "Analytics Help" support ticket. When investigating the support ticket, Seeq support will also try to determine if there is an alternate approach that will eliminate the Treemap error.