166578_it-IT

liliconcom

How do I create a formula or script that summarizes Data Separated by Zeros

Hey all,

 

I have a formula to create and I don't know how to do.  

 

The data fluctuates between zeros and positive numbers.  The positive numbers signifying an event and the zeros are between events.

 

I want to compute calculation on each event, but there are over 300 events in over 250,000 lines of data.  It would be quite time consuming to do by hand.  So, I need to create a code that searches for collections of positive numbers and computes calculations based on them, like maximum of the event, averages, and other calculations.

 

Here is another complication. To ensure the events are truly seperate, we want to see 8 zeros between events.  If there are only say, two zero between events, we aren't convinced they are far enough appart to conclude these are separate events.   We don't, however, want these zeros as part of the calculations - to find say the average.

 

The numbers are time stamped, so we would like a way to say summarize an event, but also include in the summary when the event stopped and started and how long it took.  Here is an example of what the data looks like:

 

12/1/2018 1:00 AM	0
12/1/2018 1:15 AM	0.2
12/1/2018 1:30 AM	0.3
12/1/2018 1:45 AM	0.6
12/1/2018 2:00 AM	0.5
12/1/2018 2:15 AM	0.1
12/1/2018 2:30 AM	0
12/1/2018 2:45 AM	0
12/1/2018 3:00 AM	0
12/1/2018 3:15 AM	0
12/1/2018 3:30 AM	0
12/1/2018 3:45 AM	0
12/1/2018 4:00 AM	0
12/1/2018 4:15 AM	0
12/1/2018 4:30 AM	0.2
12/1/2018 4:45 AM	0.3
12/1/2018 5:00 AM	0.5
12/1/2018 5:15 AM	0

 

What we would want a summary that went something like this:

 

Time Frame	Duration	Max	Average
12/1/2018 1:15 AM - 12/1/2018 2:15 AM	75min	.6	.34
12/1/2018 4:30 AM - 12/1/2018 5:00 AM	45min	.5	.33

 

I'm pretty inexperienced with jmp (this is my first time creating formulas/scripts). 

 

Any help would be greatly appreciated.

 

Laura