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

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

spinup — Sun, 18 Aug 2024 05:37:18 GMT

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.

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

jthi — Sun, 18 Aug 2024 05:45:05 GMT

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.

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

shampton82 — Sun, 18 Aug 2024 19:14:33 GMT

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

dt1=New Table( "Table 1",
	Add Rows( 20 ),
	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",
	Add Rows( 20 ),
	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

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

spinup — Sun, 18 Aug 2024 19:53:17 GMT

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])

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

shampton82 — Sun, 18 Aug 2024 20:43:50 GMT

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

dt1=New Table( "Table 1",
	Add Rows( 20 ),
	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",
	Add Rows( 20 ),
	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

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

spinup — Sun, 18 Aug 2024 21:05:03 GMT

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,
     add columns from update(none)
);

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

hogi — Mon, 19 Aug 2024 12:06:39 GMT

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

dt2=New Table( "Table 2",
	Add Rows( 64 ),
	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 ) )
			)}
		)
	)
);

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

shampton82 — Mon, 19 Aug 2024 00:19:29 GMT

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!

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

spinup — Mon, 19 Aug 2024 00:29:24 GMT

Thanks hogi,

This runs like a champ, however , I had the exact same question as Steve:

Can you explain the line

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

Additionally, when I run my version ( reading the tables with four columns ( x, y, data 1, data 2) on Table 1 and ( x2, y2, data 3, data 4)) I get the below alert . I think it is upset because the Table 1, and Table 2 have more than two columns (x, y) which are not in the matrix. But if you explain the line, I think I can figure out the rest! Thanks for the help

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

shampton82 — Mon, 19 Aug 2024 01:20:27 GMT

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.

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

jthi — Mon, 19 Aug 2024 03:43:59 GMT

Something like this is what I usually end up with

Names Default To Here(1);

dt1 = New Table("Table 1",
	Add Rows(20),
	New Column("x", Numeric, "Continuous", Formula(Random Normal())),
	New Column("y", Numeric, "Continuous", Formula(Random Normal()))
);

dt2 = New Table("Table 2",
	Add Rows(20),
	New Column("x", Numeric, "Continuous", Formula(Random Normal())),
	New Column("y", Numeric, "Continuous", Formula(Random Normal())),
	New Column("Val", Numeric, Continuous, Formula(Random Normal(2, 100)))
);

m1 = dt1[0, {"x", "y"}];
m2 = dt2[0, {"x", "y"}];

tree = VPTree(m2);
{rows, distances} = tree << K Nearest Rows(1, m1);

dt1 << New Column("Nearest", Numeric, Ordinal, Values(rows));
dt1 << New Column("Distance", Numeric, Ordinal, Values(distances));
dt2 << New Column("Row", Numeric, Ordinal, Values(1::N Rows(dt2)));


/* Depending on the wanted result, using Update might be better */
dt_result = dt1 << Join(
	With(dt2),
	By Matching Columns(:Nearest = :Row),
	Drop multiples(0, 0),
	Include Nonmatches(0, 0),
	Preserve main table order(1),
	Output Table("Result")
);

/*
Close(dt1, no save);
Close(dt2, no save);
*/

Join/K Nearest Rows could be done to wrong direction as I didn't verify the results

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

hogi — Mon, 19 Aug 2024 11:01:18 GMT

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"}];

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

hogi — Mon, 19 Aug 2024 11:30:40 GMT

The Documentation of VPTree could be optimized.

KD Table:

KDTable << K nearest rows has two modes:

default: take the input positions as starting points.
-> not suitable for us.
optional: use a single position as starting point.
-> yes!

I calculate this point as pos = Matrix ({:x,:y})for every row and supply it as second argument for K nearest rows

The return values are vectors of rows and distances. With the [1][1], I can first get the rows, and then the first (and only) entry.

Unfortunatley, there is no mode to provide a set of starting point as input.

VPTree:

For K nearest Rows there is no real documentation in the Scripting INdex, but there is an example right in the definition of VPTree:

and there is some documentation in the online help:

sounds like the same 2 options as for KDTable.

From @jthi 's example you can see that VPTree allows a third mode, where a set of positions is provided as a matrix.

This is a bit surprising, but very convenient :)

tree = VPTree(m2);
{rows, distances} = tree << K Nearest Rows(1, m1);
dt1 << New Column("Nearest", Numeric, Ordinal, Values(rows));

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

hogi — Mon, 19 Aug 2024 11:34:08 GMT

VPtree << K Nearest Rows(1, matrix of coordinates);

nice : ):)

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

hogi — Mon, 19 Aug 2024 11:56:09 GMT

Further Ideas:

- related wish for 1D:
I just added a comment to consider higher dimensions.

- In real application cases, there are additinal nominal coodinates like lots, wafers, variants. The user has to split the data accordingly and set up separate VPTrees for each "variant". VPTree with groupBy will be a logical next step.

- On the other hand, instead of just using a neighboring value, the user might prefer to interpolate the input data to get more meaningful values.