How to sort columns and delete repeated rows

evanchen870

New to scripting in JMP and want to filter the table of tool errors. I have a set of data and want to filter by alarm date next delete the repeated rows of errors. For example, if an error occurs at 1:00 and it repeats itself 4 times within 1 hour I want to delete the repeats and only catches when the error first appears.

My script is below and I want to resort the script after I do the delete of repeated rows by alarm date (descending).

dt = open ("SR_Loop_LAM_002_Alarm_Log.csv");

dt << sort (by (:ENTITY), order (ascending), replace table (0));

for (i = nrows (dt), i>0, i--,

  if(:ENTITY[i-1]==:ENTITY & :ALARM_ID[i-1] == :ALARM_ID & date difference(:ALARM_DATE[i-1], :ALARM_DATE, "hour", "fractional") < 1,

  dt << delete rows (i))

);

dt << save ("SR_Loop_LAM_002_Alarm_Log.csv");

close (dt, no save)

Nate_Riordan

Hi Evan,

There are a number of ways you could go around it.  There is a specific challenge with your duplicate removal in that you want to remove 'duplicates' that occur within an hour of the same alarm.  This requires lag function logic, which in turn requires the data be appropriately ordered before execution.  Furthermore if you delete a row before execution of the next logic then you could end up with out of bounds errors in looping.  One workaround is to set a flag for duplicate removal, this can be done by creating a boolean column or by using some built in features.  Below I've modified your code to use row selection as that flag.  After the logic is performed to determine if the alarm is a duplicate, all rows are deleted at once.  Further speed is added by opening the table in invisible mode so that screen I/O is avoided.

/* data table made invisible to speed up execution */

dt = Open( "SR_Loop_LAM_002_Alarm_Log.csv" , invisible );

/* updated for multiple sort */

dt << Sort( By( :ENTITY, :ALARM_DATE ), Order( Ascending, Ascending ), Replace Table( 0 ) );

/* clear row and column selection first (just good coding habit) */

dt << Clear Select;

dt << Clear Column Selection;

/* select rows that meet the specified conditions

    because this is going from bottom to top the loop should stop at row 1 (there is no row 0)

*/

For( i = N Rows( dt ), i > 1, i--,

     If(

          :ENTITY[i - 1] == :ENTITY[i] &

          :ALARM_ID[i - 1] == :ALARM_ID[i] &

          Date Difference( :ALARM_DATE[i - 1], :ALARM_DATE[i], "hour", "fractional" ) < 1,

          dt << Select Rows( i )

     )

);

/* delete the selected rows */

dt << Delete Rows;

/* final sort */

dt << Sort( By( \:ALARM_DATE ), Order( Descending ), Replace Table( 0 ) );

dt << save( "SR_Loop_LAM_002_Alarm_Log_cleansed.csv" );

   

Close( dt, no save );