Average time in a Mode

Hello,

I have two columns, tmestamp and Mode(it can take only two vales 0 and 1) - Please find the attched file for sample data for two days

I have two requirements

1) Average time for each day that Mode value is '0'

2) Average number of times Mode value is '0', For example if we have 4 zero's continuosly then we should consider only one, i.e Zero is triggered. In the below image for the first 5 rows mode value is '0' but we consider it as one zero only.

I appreciate your help.

Hello Z1745566,

For your first task I would extract the year and day of year and group by this and Mode. As aggregation you can count. This yields you how long per day it is 0 (assuming the time stamp is in regular steps).
Now Group by nothing and average over the times per day.

For the second task I would take a Java Snippet. There you can define a field, in which you store the last found value. Now you always compare last to current, if last equals 1 and current equals 0, it's a trigger and you output 1, otherwise 0. Now group by nothing and count the 1's.

Hope this pointer helps you, if you have further questions please ask.

Best,
Ferry

2) Average number of times Mode value is '0', For example if we have 4 zero's continuosly then we should consider only one, i.e Zero is triggered. In the below image for the first 5 rows mode value is '0' but we consider it as one zero only.

Instead of using a code snippet, you can also do this with built-in nodes only:

  1. Use the Lag Column node to shift the Mode column by one row.
  2. Use the Math Formula or Rule Engine nodes to create a new column that contains '1' for each transition of Mode from 1 to 0.
  3. Count the occurences of '1' in the newly created column.