topic Re: Cumulative sum of time point data in Discussions

Cumulative sum of time point data

fliew0410 — Sat, 10 Jun 2023 23:36:13 GMT

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?

Re: Cumulative sum of time point data

txnelson — Thu, 26 Aug 2021 21:02:41 GMT

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

fliew0410 — Fri, 27 Aug 2021 15:15:58 GMT

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

txnelson — Fri, 27 Aug 2021 16:16:09 GMT

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

fliew0410 — Fri, 27 Aug 2021 16:35:11 GMT

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

txnelson — Fri, 27 Aug 2021 17:37:07 GMT

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;

Re: Cumulative sum of time point data

fliew0410 — Fri, 27 Aug 2021 17:59:49 GMT

Thank you for the help.

The answer is very close. I changed the start time to 12:55:00 as the time that the first data pointed is collected on the table, and I also edited the floor to ceiling so that the new bin column will show time greater than 12:55 only (12:58 as that is the time 3 minutes after 12:55). Then, the summary on new data table. Voila, the table looks like the one we wanted.

I am still learning from the scripting index but from a closer to perfect answer that you have provided, it is easier for me to understand. Thank you very much for that!