cancel
Showing results for
Show  only  | Search instead for
Did you mean:
New to using JMP? Hit the ground running with the Early User Edition of Discovery Summit. Register now, free of charge.
Register for our Discovery Summit 2024 conference, Oct. 21-24, where you’ll learn, connect, and be inspired.
Choose Language Hide Translation Bar
Level II

## How do I add data from one table to another table based on the distance

I have two data tables for two set of measurement on the wafer. There are four columns in each table, X, Y representing the location of measurements, and two columns of measurement data for each table. the measurements  are not collected from the exact same positions (X,Y) of the wafer.  I would like to update ( add extra columns of the other table) either of the tables based on the proximity of measurement locations. To be more clear, I would like to have a combined table ( total 8 columns ) based on matching of  X1,Y1 to the closest point on X2, and Y2. Please see the picture I uploaded. I know Distance function probably can be utilized to make a matrix of all distances from a given X,Y but not sure how to write the jsl code to compute the updated table for me.

1 ACCEPTED SOLUTION

Accepted Solutions
Level XI

## Re: How do I add data from one table to another table based on the distance

``````dt1=New Table( "Table 1",
New Column( "x",
Formula( Random Uniform(0,10) )
),
New Column( "y",
Formula( Random Uniform(0,10) )
)
);

dt2=New Table( "Table 2",
New Column( "x2",
Formula( floor(row()/8)+1 )
),
New Column( "y2",
Formula( mod(row(),8)+1 )
)
);

// M2 = dt2 << GetAsMatrix();
// better - see below
M2 = dt2[0, {"x2", "y2"}];
MD2=kdtable(M2);

dt1 << New Column( "row_orig",Nominal, Formula( Row() ));
dt2 << New Column( "row_2",Nominal,Formula(  Row() ));

dt1 << New Column( "table",Nominal, set each value(1));
dt2 << New Column( "table",Nominal, set each value(2));
wait(0);

// step by step, for each row in dt1, use MD2 to find the closest position in dt2
dt1 << new column("row_2",Nominal, set each value(
pos = Matrix({:x, :y});
(MD2 << K nearest rows( 1, pos))[1][1]));

// concatenate the tables to generate a combined plot
dt2:x2 << set name("x");
dt2:y2 << set name("y");
check = dt1  << Concatenate(dt2);
check << Graph Builder(
Show Control Panel( 0 ),
Variables( X( :x ), Y( :y ), Overlay( :row_2 ), Color( :table ) ),
Elements(
Line( X, Y, Row order( 1 ) ),
Points( X, Y, Overlay( 0 ), Legend( 2 ) )
),
SendToReport(
Dispatch( {}, "400", ScaleBox,
{Legend Model(
2,
Properties( 0, {Marker( "FilledSquare" )}, Item ID( "1", 1 ) ),
Properties( 1, {Marker( "Circle" )}, Item ID( "2", 1 ) )
)}
)
)
);``````

14 REPLIES 14
Super User

## Re: How do I add data from one table to another table based on the distance

Check out following functions in addition to Distance

Then add row based index to your "main" table, utilize one of these to find the closest index (basically row), add it to your second table and then finally use update/join to combine the tables.

-Jarmo
Level VII

## Re: How do I add data from one table to another table based on the distance

Based upon @jthi links I believe this does what you want

``````dt1=New Table( "Table 1",
New Column( "x",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Random Normal() )
),
New Column( "y",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Random Normal() )
),
Set Row States( [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0] )
);

dt2=New Table( "Table 2",
New Column( "x",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Random Normal() ),
Set Display Width( 120 )
),
New Column( "y",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Random Normal() ),
Set Selected,
Set Display Width( 118 )
),
Set Row States( [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0] )
);

M1 = dt1 << GetAsMatrix();
MD1=vptree(M1);

M2 = dt2 << GetAsMatrix();
MD2=kdtable(M2);

//returns the nearest point of Table 1 to a point in table 2
//to reverse the look up, make MD1-> MD2 and M2->M1
{rows, dist} = MD1 << K nearest rows( 1, M2);

dt1<<new column("same as rows",
Numeric,
Ordinal,
Set Values( rows )

);

dt2<<new column("row number",
Numeric,
Ordinal,
Formula(Row())

);``````

Steve

Level II

## Re: How do I add data from one table to another table based on the distance

Hi Steve, and Jarmo,

Thanks for the suggestions. The script was also useful. However, when I run I don't think it finds the closest distance. For instance, in the first row of Table 1 matches to row 8 ( distance of ~1.37) of Table 2 but there are many much closer points such as row number 1 ( distance of 0.69)  ( let distance to be sqrt[(x-x0)^2+(y-y0)^2])

Level VII

## Re: How do I add data from one table to another table based on the distance

I think I mixed up the data tables at the end of the script, try this:

``````dt1=New Table( "Table 1",
New Column( "x",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Random Normal() )
),
New Column( "y",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Random Normal() )
),
Set Row States( [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0] )
);
wait(0.0);
dt1:x << suppress eval( true );
dt1:y << suppress eval( true );

dt2=New Table( "Table 2",
New Column( "x",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Random Normal() ),
Set Display Width( 120 )
),
New Column( "y",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Random Normal() ),
Set Selected,
Set Display Width( 118 )
),
Set Row States( [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0] )
);
wait(0.0);
dt2:x << suppress eval( true );
dt2:y << suppress eval( true );

M1 = dt1 << GetAsMatrix();
MD1=vptree(M1);

M2 = dt2 << GetAsMatrix();
MD2=kdtable(M2);

//returns the nearest point of Table 1 to a point in table 2
//to reverse the look up, make MD1-> MD2 and M2->M1
{rows, dist} = MD1 << K nearest rows( 1, M2);

dt2<<new column("same as rows",
Numeric,
Ordinal,
Set Values( rows )

);

dt1<<new column("row number",
Numeric,
Ordinal,
Formula(Row())

);
``````

I virtual linked them and did some visual checks so I believe we are good now

Level II

## Re: How do I add data from one table to another table based on the distance

``````I still see it is not picking up the closest points: Names Default To Here( 1 );
Close All(data tables);
Clear Log();
Clear Symbols();
dt1= Open(
"C:\Users\Table 1.jmp");
wait(0.0);
dt1:X1 << suppress eval( true );
dt1:Y1 << suppress eval( true );
dt2= Open(
"C:\Users\Table 2.jmp");

wait(0.0);
dt2:X2 << suppress eval( true );
dt2:Y2 << suppress eval( true );

M1 = dt1 << GetAsMatrix();
MD1=vptree(M1);

M2 = dt2 << GetAsMatrix();
MD2=kdtable(M2);

//returns the nearest point of Table 1 to a point in table 2
//to reverse the look up, make MD1-> MD2 and M2->M1
{rows, dist} = MD1 << K nearest rows( 1, M2);

dt1<<new column("same as rows",
Numeric,
Ordinal,
Set Values( rows )

);

dt2<<new column("row number",
Numeric,
Ordinal,
Formula(Row())

);
dt1 << update(
with(dt2),
match(:same as rows=:row number),
ignore missing,
);``````
Level VII

## Re: How do I add data from one table to another table based on the distance

Hey @spinup

I think the issue is my code assumed you only had the x and Y values in the table.  If you start with your tables that have extra numeric columns then k nearest neighbors is trying to find the closest point for X,Y, A, B vs X, Y, C, D.

I think that's why @hogi codes works so well if it is only looking at X and Y.

If you made the non-X/Y columns character and ran the my second code it would work, you'd just need to change them back to numeric once you joined them.

Level XI

## Re: How do I add data from one table to another table based on the distance

To get the matrix with the coordinates, you can use data table subscripting, like Jarmo does in his example:

row = 0 -> all rows
columns: x and y

``coordinates as matrix = dt1[0, {"x", "y"}];``
Level XI

## Re: How do I add data from one table to another table based on the distance

``````dt1=New Table( "Table 1",
New Column( "x",
Formula( Random Uniform(0,10) )
),
New Column( "y",
Formula( Random Uniform(0,10) )
)
);

dt2=New Table( "Table 2",
New Column( "x2",
Formula( floor(row()/8)+1 )
),
New Column( "y2",
Formula( mod(row(),8)+1 )
)
);

// M2 = dt2 << GetAsMatrix();
// better - see below
M2 = dt2[0, {"x2", "y2"}];
MD2=kdtable(M2);

dt1 << New Column( "row_orig",Nominal, Formula( Row() ));
dt2 << New Column( "row_2",Nominal,Formula(  Row() ));

dt1 << New Column( "table",Nominal, set each value(1));
dt2 << New Column( "table",Nominal, set each value(2));
wait(0);

// step by step, for each row in dt1, use MD2 to find the closest position in dt2
dt1 << new column("row_2",Nominal, set each value(
pos = Matrix({:x, :y});
(MD2 << K nearest rows( 1, pos))[1][1]));

// concatenate the tables to generate a combined plot
dt2:x2 << set name("x");
dt2:y2 << set name("y");
check = dt1  << Concatenate(dt2);
check << Graph Builder(
Show Control Panel( 0 ),
Variables( X( :x ), Y( :y ), Overlay( :row_2 ), Color( :table ) ),
Elements(
Line( X, Y, Row order( 1 ) ),
Points( X, Y, Overlay( 0 ), Legend( 2 ) )
),
SendToReport(
Dispatch( {}, "400", ScaleBox,
{Legend Model(
2,
Properties( 0, {Marker( "FilledSquare" )}, Item ID( "1", 1 ) ),
Properties( 1, {Marker( "Circle" )}, Item ID( "2", 1 ) )
)}
)
)
);``````

Level VII

## Re: How do I add data from one table to another table based on the distance

Thanks for cleaning this up for me @hogi !

Question, can you walk me though what this line of code is doing?

``dt1 << new column("row_2",Nominal, set each value(pos = Matrix({:x, :y});(MD2 << K nearest rows( 1, pos))[1][1]));``

Thank!