Identifying and labeling events in a time series

scott1588

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.

ian_jmp

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");