topic Re: How to look up data and create new column based on nearest match? in Discussions

How to look up data and create new column based on nearest match?

jojmp — Fri, 09 Jun 2023 23:25:50 GMT

I have sample data in table sample1 and the look up table efficiency( please find attached both the tables). I want to find the closest match of data column in table sample1 with resplect to IOUT column in look up table efficiency. Based on the nearest match I want to populate efficiency column in table sample1 in accordance with look up table:

Below is the code I am trying

/* Open a sample data table */
dt = Open( "C:\Users\Test\Sample data\Sample1.jmp" );

/* Add a column for the assignment */
dt << New Column( "Efficiency", Numeric );

/* Open a lookup table */
lt = Open( "C:\Users\Test\Sample data\efficiency_0.7.jmp" );
/* Loop through each row of the original data table */
For( i = 1, i <= N Rows( dt ), i++, 
	/* Obtain a matrix contining the row number of the lookup table
	   where the current falls within the range */
	lowRange = lt << Get Rows Where(
		Column( lt, "IOUT" )[] < Column( dt, "data" )[i] & 
		Column( dt, "data" )[i] <= Column( lt, "IOUT" )[]
	);
	/* Extract the row number from the matrix */
	r = lowRange[1];
	/* Assign the efficiency value to the original data table */
	Column( dt, "Efficiency" )[i] = Column( lt, "low" )[r];
);

I want to find nearest match of IOUT with data and populate the efficiency accordingly

Re: How to look up data and create new column based on nearest match?

Mark_Bailey — Thu, 27 Jun 2019 09:32:22 GMT

This reply is not a solution but a suggestion about a direction you might take. See the JSL matrix functions for distance metrics. Similarly, you might use a JMP platform that is based on distance for agglomeration, such as clustering or discrimination.

Re: How to look up data and create new column based on nearest match?

txnelson — Thu, 27 Jun 2019 13:05:37 GMT

Assuming the Lookup Table(efficiency) is sorted from low to high on column IOUT
Assuming the Lookup Table is not real large. The methodology being used is not very efficient.

This code should work well

/* Open a sample data table */
//dt = Open( "C:\Users\Test\Sample data\Sample1.jmp" );
dt = Data Table( "Sample1" );

/* Add a column for the assignment */
dt << New Column( "Efficiency", Numeric );

/* Open a lookup table */
//lt = Open( "C:\Users\Test\Sample data\efficiency_0.7.jmp" );
lt = Data Table( "efficiency" );


/* Loop through each row of the original data table */
For( i = 1, i <= N Rows( dt ), i++, 
	// Find the row with the closest IOUT value below the data value
	theLowRow = Max( lt << get rows where( lt:IOUT <= dt:data[i] ) );
	// Get the IOUT value for theLowRow
	low = lt:IOUT[theLowRow];
	
	// Find the row with the closest IOUT value above the data value
	theHighRow = Min( lt << get rows where( lt:IOUT >= dt:data[i] ) );
	// Get the IOUT value for theLowRow
	high = lt:IOUT[theHighRow];
	
	// Find the closest of these two values and write the Efficiency value
	If( Abs( low - dt:data[i] ) < Abs( high - dt:data[i] ),
		dt:Efficiency[i] = lt:low[theLowRow],
		dt:Efficiency[i] = lt:low[theHighRow]
	);
	
	/*/* Obtain a matrix contining the row number of the lookup table
	   where the current falls within the range */
	lowRange = lt << Get Rows Where(
		Column( lt, "IOUT" )[] < Column( dt, "data" )[i] & 
		Column( dt, "data" )[i] <= Column( lt, "IOUT" )[]
	);
	/* Extract the row number from the matrix */
	r = lowRange[1];
	/* Assign the efficiency value to the original data table */
	Column( dt, "Efficiency" )[i] = Column( lt, "low" )[r];*/
);