Jump to content

Replace Gaps in Data with an Average Value from Previous Time Frame


Recommended Posts

  • Seeq Team

FAQ: I have a signal with a gap in the data from a system outage. I want to replace the gap with a constant value, ideally the average of the time period immediately before the data.

image.png

 

Solution:

1. Once you've identified your data gaps, extend the capsules backwards by the amount over which time you want to take the average. In this example, we want to fill in the gap with the average of the 10 minutes before the signal dropped, so we will extend the start of the data gap capsule 10 minutes in the past. This is done using the move function in Formula:

$conditionForDataGaps.move(-10min,0min)

image.png

2. Use Signal from Condition to calculate the average of the gappy signal during the condition created in step 1. Make sure to select "Duration" for the timestamp of the statistic. 

image.png

3. Stitch the two signals together using the splice function. The validvalues() function at the end ensures a continuous output signal. 

$gappysignal.splice($replacementsignal,$gaps).validvalues()

image.png

image.png

 

  • Like 3
Link to comment
Share on other sites

  • 2 years later...
  • Seeq Team

Sometimes it's beneficial to fill these gaps with a prior average that is dynamic. The above post details how to fill the gap with a static timeframe, such as the 10 minutes before the gap. But what if we wanted to do something different, such as take the gap duration, a dynamic value, and fill the gap with the prior average based on the gap duration?

Below details how to do this. There is a similar approach that leverages the .transform() Seeq function here, but I've provided an alternative method that avoids the usage of .transform(). Of course, this all can be input to a single formula, but below details each step broken out.

 

Solution:

1. Identify data gaps & calculate gap durations:

Notes:

  • .intersect(past()) guarantees we are not considering the future for data validity
  • The maximum capsule duration should be carefully considered depending on the maximum gap duration you want to fill
  • Placing the timestamp across the capsule durations is important

 

image.pngimage.png

image.png

 

 

2. Create an arbitrary flat signal & move this signal backwards (negatively) based on the gap durations signal

Notes:

  • The timeframe specified in .toSignal(1min) influences the resolution of representing the gap duration. 1min should suffice for most cases.
  • It's important to include a minus in front of the the gap duration to indicate we are moving backwards in time. The 24h dictates the maximum duration allowed to move, which is dependent on the expected gap durations.

image.pngimage.png

image.png

 

3. Identify the new, moved signal and join the capsules from the new condition to the original signal gaps condition

Notes:

  • Again, the maximum capsule duration needs to be carefully considered when joining the two conditions.

image.pngimage.png

image.png

 

4. Calculate the average of the gappy signal across the joined condition, then splice this average into the original gappy signal

Notes:

  • Again, specifying the timestamp to be placed across the capsule durations is important here.
  • Be sure to splice the average across the original signal gaps. Including .validValues() ensures we interpolate across our original gappy signal and the replacement average signal.

image.pngimage.png

image.png

  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...