412907_en-US

liliconcom

Cumulative sum of time point data

I am a low level user in JMP and trying to learn how to use it for time series data analysis. 
Currently, my data are collected in every 2 seconds, but how do I transform the data  to every 3 minutes into another column or table? 

 

Data TableScriptingRe: Cumulative sum of time point data

I assume that what you really want, is a new data table, where each row represents the mean of 3 minute intervals from the original data table.

I would do it this way.

Create a new column in the original data table.  Set the formula for the new column to

if(row()==1, start = :time);
floor((:time - start) / in minutes(3));

This will create a new column where the values are in 3 minute bins.

Now all you need to do, is to use the Summary Platform

     Tables=>Summary

to create the new data table.  Just specify your new bin column as the Group column and the means for the measurement columns.

Re: Cumulative sum of time point data

Hi there, I tried the formula but the values shown are all 0 on the new column.

However, I used a similar concept creating a new bin column by rounding the original Time column (2 second interval, 1mL/2second measurement) to 3.33 minutes interval (100mL/200seconds measurement)

 

Round( :Time / (60 * 3.33) ) * (60 * 3.33)

 

Then, I used your Tables > Summary suggestion to create a new data table, specifying my new bin column as Group and the sum of the measurement columns.

 

It seems to work. But do you think this is just luck or this makes sense?

Re: Cumulative sum of time point data

If you attach your data table, I will take a look at both your formula and figure out why my formula isn't giving you the results you want.

Re: Cumulative sum of time point data

Sure. Please see attached.

 

Now my problem with the formula I found is that my bin start with the first recorded time (from the original data table) rather than the time after 3 minutes. Ideally, I would like the time in the bin column to start with 12:58 (3 minutes after 12:55) and not 12:55.

Re: Cumulative sum of time point data

I opened the data table you attached, and found that my formula worked as I expected.  It created values of 0, 1, .....etc.  It also created negative binning values, since your times occurred on different dates, and some of the time values were before the starting time value.  i.e. 8:38:55 AM on 04/17/2021 has a value less than the time value of 12:55:03 PM on 04/16/2021.  Time values start every day at midnight as 0 seconds.  Also, I was generating just a binning number, not a time value.  Because you have data that spans more than one day, you need to use a JMP date/time value for the calculations, and for the binning value.  Attached is your data table with a new Bin Time, starting at 12:58 on April 16th, and increasing after that.  The formula does generate a bin value before your 12:58 starting time, so you will need to delete those rows.

The formula is:

If( Row() == 1,
 start = Informat( "04/16/2021 12:58:00" )
);
Floor( ((:Date + :Time) - start) / In Minutes( 3 ) ) * In Minutes( 3 ) + start;