topic Re: Remove Duplicate rows using Timestamp in Discussions

Remove Duplicate rows using Timestamp

psundar6 — Sat, 10 Jun 2023 23:38:35 GMT

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

Re: Remove Duplicate rows using Timestamp

txnelson — Sun, 17 Oct 2021 04:53:47 GMT

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

Re: Remove Duplicate rows using Timestamp

ron_horne — Sun, 17 Oct 2021 22:49:57 GMT

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.

Re: Remove Duplicate rows using Timestamp

psundar6 — Sun, 17 Oct 2021 20:02:45 GMT

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.

Re: Remove Duplicate rows using Timestamp

psundar6 — Sun, 17 Oct 2021 20:03:14 GMT

This works, Thank you!

Re: Remove Duplicate rows using Timestamp

ron_horne — Sun, 17 Oct 2021 22:52:21 GMT

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