Choose Language Hide Translation Bar
Highlighted
Level III

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

• 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
2 REPLIES 2
Highlighted
Staff

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

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.

Learn it once, use it forever!
Highlighted
Super User

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

1. Assuming the Lookup Table(efficiency) is sorted from low to high on column IOUT
2. 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];*/
);``````
Jim
Article Labels

There are no labels assigned to this post.