nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Remove Duplicate rows using Timestamp

psundar6

Hello,

I have a sample data table attached wherein I am looking to remove duplicate rows within the data set using the TimeStamp,Tool columns.

Criteria for duplication:

Timestamp that are within 6mins on the same tool are considered as duplicates and those rows have to be removed.

Can someone help how this can be achieved in JSL?

Thanks!

@txnelson

sample_data.jmp

Accepted answers

txnelson

I believe this will give you an example on how to proceed with your issue

Names Default To Here( 1 );
dt = Current Data Table();

// Loop through the data table deleting all duplicate rows
i = 1;
While( i <= N Rows( dt ),
	
	// Find the upper and lower bounds to match on
	lower = :timeStamp[i] - In Minutes( 6 );
	upper = :timeStamp[i] + In Minutes( 6 );
	
	// Find the rows that match
	theRows = dt << get rows where( :timeStamp > lower & :timeStamp < upper );
	
	// Remove the current row from the list.  We do not want to delete it
	Try( theRows[1] = [] );
	
	// Delete the remaining rows
	Try( dt << delete rows( theRows ) );
	
	// Increment the row to work on
	i++;
);

ron_horne

Hi @psundar6 ,

@txnelson provides a very good solution.

An alternative approach using table sort and column formula could be done interactively or by scripting as follows:

dt = current data table();

dt << Sort(
	By( :TimeStamp, :Tool ),
	Replace Table,
	Order( Ascending, Ascending )
);

dt << New Column( "to delete",
	Numeric,
	"Ordinal",
	Format( "Best", 12 ),
	Formula( If( :Tool == Lag( :Tool, 1 ) & Date Difference( Lag( :TimeStamp, 1 ), :TimeStamp, "Minute" ) < 6, 1, 0 ), eval formula )
);
dt:to delete << suppress eval(true);

dt<< delete rows (dt<< get rows where (:to delete == 1));

let us know if it works for you.

All comments

txnelson

I believe this will give you an example on how to proceed with your issue

Names Default To Here( 1 );
dt = Current Data Table();

// Loop through the data table deleting all duplicate rows
i = 1;
While( i <= N Rows( dt ),
	
	// Find the upper and lower bounds to match on
	lower = :timeStamp[i] - In Minutes( 6 );
	upper = :timeStamp[i] + In Minutes( 6 );
	
	// Find the rows that match
	theRows = dt << get rows where( :timeStamp > lower & :timeStamp < upper );
	
	// Remove the current row from the list.  We do not want to delete it
	Try( theRows[1] = [] );
	
	// Delete the remaining rows
	Try( dt << delete rows( theRows ) );
	
	// Increment the row to work on
	i++;
);

ron_horne

Hi @psundar6 ,

@txnelson provides a very good solution.

An alternative approach using table sort and column formula could be done interactively or by scripting as follows:

dt = current data table();

dt << Sort(
	By( :TimeStamp, :Tool ),
	Replace Table,
	Order( Ascending, Ascending )
);

dt << New Column( "to delete",
	Numeric,
	"Ordinal",
	Format( "Best", 12 ),
	Formula( If( :Tool == Lag( :Tool, 1 ) & Date Difference( Lag( :TimeStamp, 1 ), :TimeStamp, "Minute" ) < 6, 1, 0 ), eval formula )
);
dt:to delete << suppress eval(true);

dt<< delete rows (dt<< get rows where (:to delete == 1));

let us know if it works for you.

psundar6

Hello Ron,

I tried your solution and I see an issue with the result.

I have attached the result from running your code. It seems like it is not catching a couple of duplicate entries for some reason.

Thanks.

sample_data_ron.jmp

psundar6

This works, Thank you!

ron_horne

Thanks @psundar6 for letting me know. there was a typo now I have edited the script..