Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
GatorDoc03
Level I

With JMP 14.3 how can one match two date-time columns within =/- xh

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Craige_Hales
Staff (Retired)

Re: With JMP 14.3 how can one match two date-time columns within =/- xh

dt = New Table( "Untitled 3",
	Add Rows( 20 ),
	New Column( "atime",
		Numeric,
		"Continuous",
		Format( "ddMonyyyy h:m", 19 ),
		Input Format( "ddMonyyyy h:m" ),
		Formula( As Date( Random Uniform( Today() - 100000, Today() + 100000 ) ) ),
		Set Display Width( 250 )
	),
	New Column( "closest b time",
		Numeric,
		"Continuous",
		Format( "ddMonyyyy h:m", 19 ),
		Input Format( "ddMonyyyy h:m" ),
		Set Display Width( 250 )
	),
	New Column( "btime",
		Numeric,
		"Continuous",
		Format( "ddMonyyyy h:m", 19 ),
		Input Format( "ddMonyyyy h:m" ),
		Formula( As Date( Random Uniform( Today() - 100000, Today() + 100000 ) ) ),
		Set Display Width( 250 )
	),
	New Column( "closest a time",
		Numeric,
		"Continuous",
		Format( "ddMonyyyy h:m", 19 ),
		Input Format( "ddMonyyyy h:m" ),
		Set Display Width( 250 )
	)
);
dt << runformulas;
atime << deleteformula;
btime << deleteformula;
// here it is: find both backwards and forwards nearest neighbor
kdtA = KDTable( dt:atime << getvalues );
kdtB = KDTable( dt:btime << getvalues );
For( irow = 1, irow <= N Rows( dt ), irow += 1,
	// look up the row of the nearest A time to this row's B time
	closeA = (kdtA << knearestrows( 1, Matrix( dt:btime[irow] ) ))[1][1];
	// get the actual A time from that row and keep it in this row.
	dt:closest a time[irow] = dt:atime[closeA];
	// flag times that are too far away
	if(abs(dt:closest a time[irow]-btime[irow])>inhours(2),dt:closest a time<<color cells("red",irow));
	// repeat for the other direction. These are NOT going to be "the same, but backwards!"
	// because you might not be the nearest neighbor of your nearest neighbor.
	closeB = (kdtB << knearestrows( 1, Matrix( dt:atime[irow] ) ))[1][1];
	dt:closest b time[irow] = dt:btime[closeB];
	if(abs(dt:closest b time[irow]-atime[irow])>inhours(2),dt:closest b time<<color cells("red",irow));
);

red flags near neighbors in time that are more than 2 hours apartred flags near neighbors in time that are more than 2 hours apart

the [1][1] in the knearest rows call is getting the first item from the returned list, which is a matrix of one element, then getting that element as a scalar value. It is the row number.

This technique will not work if you need an asymmetric window (like B time must be within 2 hours after A time). You could adapt it by getting all the rows within 2 hours, then picking the first one that works.

Craige

View solution in original post

3 REPLIES 3
Highlighted
txnelson
Super User

Re: With JMP 14.3 how can one match two date-time columns within =/- xh

Let me state, what I believe you are asking. 

You want to know, how to match 2 date columns within a range of plus or minus X number of Hours.

Is this correct?

 

Are the columns in the same data table, or are you looking to match across data tables, so the data can be merged together?

If more than one row is found within the +/- hour range, do you want the closest value chosen?  What if there is a tie?

Jim
Highlighted
GatorDoc03
Level I

Re: With JMP 14.3 how can one match two date-time columns within =/- xh

Same data table. Trying to match serum lactate level in septic patients to a creatinine level at least within 6h

Highlighted
Craige_Hales
Staff (Retired)

Re: With JMP 14.3 how can one match two date-time columns within =/- xh

dt = New Table( "Untitled 3",
	Add Rows( 20 ),
	New Column( "atime",
		Numeric,
		"Continuous",
		Format( "ddMonyyyy h:m", 19 ),
		Input Format( "ddMonyyyy h:m" ),
		Formula( As Date( Random Uniform( Today() - 100000, Today() + 100000 ) ) ),
		Set Display Width( 250 )
	),
	New Column( "closest b time",
		Numeric,
		"Continuous",
		Format( "ddMonyyyy h:m", 19 ),
		Input Format( "ddMonyyyy h:m" ),
		Set Display Width( 250 )
	),
	New Column( "btime",
		Numeric,
		"Continuous",
		Format( "ddMonyyyy h:m", 19 ),
		Input Format( "ddMonyyyy h:m" ),
		Formula( As Date( Random Uniform( Today() - 100000, Today() + 100000 ) ) ),
		Set Display Width( 250 )
	),
	New Column( "closest a time",
		Numeric,
		"Continuous",
		Format( "ddMonyyyy h:m", 19 ),
		Input Format( "ddMonyyyy h:m" ),
		Set Display Width( 250 )
	)
);
dt << runformulas;
atime << deleteformula;
btime << deleteformula;
// here it is: find both backwards and forwards nearest neighbor
kdtA = KDTable( dt:atime << getvalues );
kdtB = KDTable( dt:btime << getvalues );
For( irow = 1, irow <= N Rows( dt ), irow += 1,
	// look up the row of the nearest A time to this row's B time
	closeA = (kdtA << knearestrows( 1, Matrix( dt:btime[irow] ) ))[1][1];
	// get the actual A time from that row and keep it in this row.
	dt:closest a time[irow] = dt:atime[closeA];
	// flag times that are too far away
	if(abs(dt:closest a time[irow]-btime[irow])>inhours(2),dt:closest a time<<color cells("red",irow));
	// repeat for the other direction. These are NOT going to be "the same, but backwards!"
	// because you might not be the nearest neighbor of your nearest neighbor.
	closeB = (kdtB << knearestrows( 1, Matrix( dt:atime[irow] ) ))[1][1];
	dt:closest b time[irow] = dt:btime[closeB];
	if(abs(dt:closest b time[irow]-atime[irow])>inhours(2),dt:closest b time<<color cells("red",irow));
);

red flags near neighbors in time that are more than 2 hours apartred flags near neighbors in time that are more than 2 hours apart

the [1][1] in the knearest rows call is getting the first item from the returned list, which is a matrix of one element, then getting that element as a scalar value. It is the row number.

This technique will not work if you need an asymmetric window (like B time must be within 2 hours after A time). You could adapt it by getting all the rows within 2 hours, then picking the first one that works.

Craige

View solution in original post

Article Labels

    There are no labels assigned to this post.