topic Re: How to look up data and create new column based on nearest match? in Discussions
https://community.jmp.com/t5/Discussions/How-to-look-up-data-and-create-new-column-based-on-nearest-match/m-p/214966#M42976
<P>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.</P>Thu, 27 Jun 2019 09:32:22 GMTmarkbailey2019-06-27T09:32:22ZHow to look up data and create new column based on nearest match?
https://community.jmp.com/t5/Discussions/How-to-look-up-data-and-create-new-column-based-on-nearest-match/m-p/214944#M42973
<UL><LI>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:</LI><LI>Below is the code I am trying<BR /><PRE><CODE class=" language-jsl">/* 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];
);</CODE></PRE></LI><LI>I want to find nearest match of IOUT with data and populate the efficiency accordingly</LI></UL>Thu, 27 Jun 2019 07:21:15 GMThttps://community.jmp.com/t5/Discussions/How-to-look-up-data-and-create-new-column-based-on-nearest-match/m-p/214944#M42973jojmp2019-06-27T07:21:15ZRe: How to look up data and create new column based on nearest match?
https://community.jmp.com/t5/Discussions/How-to-look-up-data-and-create-new-column-based-on-nearest-match/m-p/214966#M42976
<P>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.</P>Thu, 27 Jun 2019 09:32:22 GMThttps://community.jmp.com/t5/Discussions/How-to-look-up-data-and-create-new-column-based-on-nearest-match/m-p/214966#M42976markbailey2019-06-27T09:32:22ZRe: How to look up data and create new column based on nearest match?
https://community.jmp.com/t5/Discussions/How-to-look-up-data-and-create-new-column-based-on-nearest-match/m-p/214997#M42984
<OL>
<LI>Assuming the Lookup Table(efficiency) is sorted from low to high on column IOUT</LI>
<LI>Assuming the Lookup Table is not real large. The methodology being used is not very efficient.</LI>
</OL>
<P>This code should work well</P>
<PRE><CODE class=" language-jsl">/* 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];*/
);</CODE></PRE>Thu, 27 Jun 2019 13:05:37 GMThttps://community.jmp.com/t5/Discussions/How-to-look-up-data-and-create-new-column-based-on-nearest-match/m-p/214997#M42984txnelson2019-06-27T13:05:37Z