topic Re: Full Outer Join of Two Tables by Timestamp within 5 Minutes of Each Other in Discussions

Full Outer Join of Two Tables by Timestamp within 5 Minutes of Each Other

twillkickers — Tue, 24 Jul 2018 13:53:21 GMT

I have two tables that I want to join by a timestamp, however, the timestamps are not recorded at the exact same time. Therefore, I wish to join any timestamps that are within 5 minutes of each other. If the timestamp does not have a matching timestamp in the other table, I still wish to keep the row data. Is there an easy way to do this in JSL?

Re: Full Outer Join of Two Tables by Timestamp within 5 Minutes of Each Other

txnelson — Tue, 24 Jul 2018 20:00:24 GMT

There is not a builtin platform that will do a join with fuzzy matching as you have stated what you need. However, JSL can be used to get what you need.

Here is a rough example of one way to script your request

Names Default To Here( 1 );
// Create sample data tables
dt1 = New Table( "Data Table 1",
	Add Rows( 100 ),
	New Column( "DateTime",
		Numeric,
		"Continuous",
		Format( "m/d/y h:m", 19 ),
		Input Format( "m/d/y h:m" ),
		Formula( Today() + Row() * In Minutes( 5 ) ),
		Set Selected
	)
);
dt1 << run formulas;
dt1:DateTime << delete formula;
dt2 = dt1 << Subset(
	Sampling Rate( 0.25 ),
	Selected columns only( 0 ),
	output table name( "Data Table 2" ),
	link to original table( 0 )
);
Current Data Table( dt2 );
For Each Row( :DateTime = :DateTime + In Minutes( Random Uniform( -100, 100 ) ) );
dt2 << sort( by( :DateTime ), order( ascending ), replace table( 1 ) );

// Create a column in data table 1 for reference
dt1 << New Column( "RowNum", formula( Row() ) );
dt1:RowNum << delete formula;

// Create in data table 2 a column that will hold the row to join to
dt2 << New Column( "Match Row" );

// Loop through data table 2 and find the rows in the first table that match
For( i = 1, i <= N Rows( dt2 ), i++,
	checkVal = dt2:DateTime[i];
	theRows = dt1 << get rows where(
		:Datetime > checkVal - In Minutes( 5 ) & :DateTime < checkVal + In Minutes( 5 )
	);
	If( N Rows( theRows ) > 0,
		minList = {};
		For( k = 1, k <= N Rows( theRows ), k++,
			Insert Into( minList, Abs( dt1:DateTime[theRows[k]] - checkVal ) )
		);
		dt2:Match Row[i] = theRows[Loc( minList, Min( minList ) )];
	,
		dt2:Match Row[i] = 0
	);
);

// Join the tables
dt3 = dt1 << Join(
	With( dt2 ),
	Merge Same Name Columns,
	By Matching Columns( :RowNum = :Match Row ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 ),
	output table name("Final Table")
);

Re: Full Outer Join of Two Tables by Timestamp within 5 Minutes of Each Other

jerry_cooper — Tue, 24 Jul 2018 19:11:22 GMT

You could create a formula column in each data table that rounds your timestamp column to the nearest 5 minutes and then use those columns for the join. In JMP 14 if you right-click the header of your timestamp column, then select New Formula Column->Transform->Round..., you'll be presented with a dialog box giving you the option to round up, down, or to the nearest time increment that you want. If you don't have JMP 14, the formula to round to the nearest 5 minute interval would look something like this:

Round( :Timestamp / 300 ) * 300

(Note that 300 is the number of seconds in 5 minutes).

The Tables->JMP Query Builder (or Tables->Join) dialog has options for keeping non-matches and creating a Match Flag column.

Hope this helps.