Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- How to look up data and create new column based on nearest match?

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jun 27, 2019 12:11 AM
(334 views)

- 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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- 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];*/
);
```

Jim