topic Re: Identifying and labeling events in a time series in Discussions

Identifying and labeling events in a time series

scott1588 — Tue, 24 Mar 2020 20:07:31 GMT

I have a large time series(~250,000) of minute data analyzer status values (character values). The values are NORMAL for when the analyzer is operating normally and CALIB during calibration. The calibrations occur daily but at various times (not the same time every day) and last for various durations usually around 30 minutes. Each one minute data point is timestamped. I need to assign each calibration period (from the beginning of cal status CALIB to the end of cal status CALIB for a given day) a unique identifier and generate a table with the start and end times for each calibration period.

I'm a complete novice at JSL. Can anyone provide an example of how I might accomplish this with JSL?

Thanks in advance for any help.

Re: Identifying and labeling events in a time series

ian_jmp — Wed, 25 Mar 2020 15:51:48 GMT

Please find one approach (no doubt of many). Using the summary table allows linking to the source table, which might be useful. Do 'File > New > New Script', cut and paste the code below, then 'Edit > Run Script'.

NamesDefaultToHere(1);

// Make some fake data to play with
n = 1000;
startDate = Date DMY( 01, 1, 2016 );
endDate = Date DMY( 12, 10, 2016 );
dates = Sort Ascending(J( n, 1, Random Integer(startDate, endDate) ) );
status = J( n, 1, Random Integer(1, 2) );
dt1 = New Table( "Callibrations",
			New Column( "Date", Numeric, "Continuous", Format( "ddMonyyyy h:m", 35 ), Set Values( dates )),
			New Column( "Status", Numeric, "Nominal", Set Values( status ), Value Labels( {1 = "NORMAL", 2 = "CALIB"} ) )
			);

/************************************************************************************************/
/* Start here . . .                                                                             */
/************************************************************************************************/
dt1 = CurrentDataTable();

// Define a column that increments when the 'Status' changes:
// https://community.jmp.com/t5/Discussions/Formula-for-automatic-counter-by-group-of-rows/td-p/20120
dt1 << New Column( "Counter", Numeric, "Ordinal", Formula( If( Row() == 1, 1, Lag( :Counter, 1 ) + (Lag( :Status, 1 ) != :Status)) ) );

// Get the start dates 
dt1 << New Column( "Start Date", Numeric, "Continuous", Format( "ddMonyyyy h:m", 35 ), Formula( Col Min( :Date, :Counter ) ));

// Get the end dates 
dt1 << New Column( "End Date", Numeric, "Continuous", Format( "ddMonyyyy h:m", 35 ), Formula( Col Max( :Date, :Counter ) ));

// Make the required summary table
dt2 = dt1 << Summary(
					Group( :Counter, :Status ),
					Min( :Start Date ),
					Max( :End Date ),
					statistics column name format( "column" ),
					output table name( "Calibration Periods" )
					);
dt2 << deleteProperty("Source");

Re: Identifying and labeling events in a time series

scott1588 — Wed, 25 Mar 2020 19:15:58 GMT

This is perfect.Thanks, Ian. The support JMP and the dedicated users provides for the JMP community is awesome. It provides incredible value.