Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Laura2
Level I

How to summarize clumps of data separated by zeros

Hey all,

 

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

 

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:

 

Time StampValue
12/1/2018 1:00 AM0
12/1/2018 1:15 AM0.2
12/1/2018 1:30 AM

0.3

12/1/2018 1:45 AM0.6
12/1/2018 2:00 AM0.5
12/1/2018 2:15 AM0.1
12/1/2018 2:30 AM0
12/1/2018 2:45 AM0
12/1/2018 3:00 AM0
12/1/2018 3:15 AM0
12/1/2018 3:30 AM0
12/1/2018 3:45 AM0
12/1/2018 4:00 AM0
12/1/2018 4:15 AM0
12/1/2018 4:30 AM

0.2

12/1/2018 4:45 AM0.3
12/1/2018 5:00 AM0.5
12/1/2018 5:15 AM0

 

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

 

Time FrameDurationMaxAverage
12/1/2018 1:15 AM12/1/2018 2:15 AM75min.6.34
12/1/2018 4:30 AM - 12/1/2018 5:00 AM45min.5.33

 

I'm pretty inexperienced with jmp and this is my first time creating scripts/formulas that do the following for all the data:

 

col maximum (value)
col mean(value)

Like I said, I'm really knew to this.

 

Any help would be greatly appreciated.

 

Laura

Laura
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
gzmorgan0
Super User

Re: How to summarize clumps of data separated by zeros

This can be done with a for-loop in a script, but I will walk you through a point and click method.  It is broken down into small chunks:

  • Create a column of Zero Counts
  • Create a column called Span ID, the start of the span is the row number and that row number is copied down via the Lag function, until another zero is found
  • Create a column called Valid, it ony has values when Span ID == Row() the row number. 

The attached table has these column formulas.

Now you can create a table summary you might want to uncheck Link to original table. See the dialog and resulting table below.

   image.png

image.png

  • Delete the row 1 
  • Span ID is where the span started
  • Change column titles to Start Time and End Time, etc.
  • Create a Duration column End Time - Start Time

The trick here is to find the spans.  Look up the functions used in the attached table. Note the summary is still calculated if less than 8 zeros. Valid will tell you which ones to look at.

View solution in original post

2 REPLIES 2
Highlighted
gzmorgan0
Super User

Re: How to summarize clumps of data separated by zeros

This can be done with a for-loop in a script, but I will walk you through a point and click method.  It is broken down into small chunks:

  • Create a column of Zero Counts
  • Create a column called Span ID, the start of the span is the row number and that row number is copied down via the Lag function, until another zero is found
  • Create a column called Valid, it ony has values when Span ID == Row() the row number. 

The attached table has these column formulas.

Now you can create a table summary you might want to uncheck Link to original table. See the dialog and resulting table below.

   image.png

image.png

  • Delete the row 1 
  • Span ID is where the span started
  • Change column titles to Start Time and End Time, etc.
  • Create a Duration column End Time - Start Time

The trick here is to find the spans.  Look up the functions used in the attached table. Note the summary is still calculated if less than 8 zeros. Valid will tell you which ones to look at.

View solution in original post

Highlighted
Laura2
Level I

Re: How to summarize clumps of data separated by zeros

Thank you! This worked perfectly. I didn't really think about this, but a lot of the zeros are just missing values.  I had to add to the formulas so that it treated all the missnig values as zeros. 

 

Thank you again!

Laura
Article Labels

    There are no labels assigned to this post.