topic Re: Associate Columns By Name in Discussions

Associate Columns By Name

SpannerHead — Thu, 20 Mar 2025 14:42:16 GMT

I got something to work here

Solved: Automatically plot associated columns - JMP User Community

but I'm pretty sure it's unnecessarily cumbersome. I have column names ending with the letter H (High) that I need to associate with column names that otherwise match but end with the letter L (Low). I can create an associative array with the column names having the final character removed. Snookered as to what to do next?

names default to here(1);
dt = Current Data Table();

colList = dt << get column names( string );

for each({col,index}, colList,
	//colList[index] = substr(col,1,3)
	colList[index] = Left(col,Length(col)-1)
);

grps = associative array(colList)<<get keys;

For instance, VT123ABCH needs to plot against VT123ABCL and so forth.

Re: Associate Columns By Name

jthi — Thu, 20 Mar 2025 15:04:24 GMT

Do the pairs always exist? Are there other columns in the table than these pairs? Are they in specific order?

Edit:

Can most likely be made much better depending on the real use case

names default to here(1);

dt = New Table("",
	New Column("VT123ABCH"),
	New Column("VT123ABCL")
);

collist = dt << get column names(string);

hcols = Filter Each({colname}, colList,
	Ends With(colname, "H");
);

aa = Associative Array();

For Each({hcolname}, hcols,
	lcolname = Left(hcolname, Length(hcolname) - 1) || "L";
	If(dt << Has Column(lcolname),
		aa[hcolname] = lcolname
	);
);

Show(aa);

Re: Associate Columns By Name

txnelson — Thu, 20 Mar 2025 15:07:12 GMT

Here is an example of how you might do what you want

Names Default To Here( 1 );
dt = 
// Open Data Table: semiconductor capability.jmp
// → Data Table( "semiconductor capability" )
Open( "$SAMPLE_DATA/semiconductor capability.jmp" );

colNames = dt << get column names( continuous, string );

For Each( {col}, colNames,
	If( Ends With( col, "1" ) & Contains( colNames, Substr( col, 1, Length( col ) - 1 ) || "2" ),
		Graph Builder(
			Size( 534, 456 ),
			Show Control Panel( 0 ),
			Variables( X( As Column( col ) ), Y( As Column( Substr( col, 1, Length( col ) - 1 ) || "2" ) ) ),
			Elements( Points( X, Y, Legend( 3 ) ), Smoother( X, Y, Legend( 4 ) ) )
		);
	
	)
);

Re: Associate Columns By Name

SpannerHead — Thu, 20 Mar 2025 19:58:05 GMT

Jarmo

Looks much tidier that what I had, however, I'm getting this error.

"Object 'Data Table' does not recognize the message 'Has Column'"

Re: Associate Columns By Name

jthi — Thu, 20 Mar 2025 20:03:00 GMT

That was added in JMP18

You can change it to Contains() and it should be fine most of the time

Contains(collist, lcolname)

Re: Associate Columns By Name

SpannerHead — Thu, 20 Mar 2025 20:33:30 GMT

Jarmo

This is close but I notice that hcolname is empty, whereas lcolname has a value. Do I need to establish hcolname as an object or list?

Re: Associate Columns By Name

jthi — Thu, 20 Mar 2025 20:46:02 GMT

hcols should contain a list of all columns which end with "H". This is then used in For Each and each if the items will be one by one used as hcolname. You can add debug prints to understand what is going on. Also, this definitely isn't the only way of doing this you could for example easily in a single loop.

Re: Associate Columns By Name

SpannerHead — Thu, 20 Mar 2025 21:23:03 GMT

Jarmo

This gets me a little closer but the lcolname does not populate until the final value.

For Each({hcolname, index}, hcols,
	{lcolname, index}, hcols = Left(hcolname, Length(hcolname) - 1) || "L";
	If(dt << Contains(collist, lcolname),
		aa[hcolname] = lcolname
	);
);

Re: Associate Columns By Name

jthi — Thu, 20 Mar 2025 21:40:07 GMT

For me it seems to be working fine. You can change the example table to semiconductor capability and check for some numbers at the end

Names default to here(1);

dt = Open("$SAMPLE_DATA/Semiconductor Capability.jmp");

collist = dt << get column names(string);

hcols = Filter Each({colname}, colList,
	Ends With(colname, "1");
);

aa = Associative Array();

For Each({hcolname}, hcols,
	lcolname = Left(hcolname, Length(hcolname) - 1) || "2";
	If(dt << Has Column(lcolname),
		aa[hcolname] = lcolname
	);
);

Show(aa);

aa = ["ESM1" => "ESM2", "FST1" => "FST2", "INM1" => "INM2", "IVP1" => "IVP2", "NPN1" => "NPN2", "PBA1" => "PBA2", "PLG1" => "PLG2", "PLY1" => "PLY2", "PNP1" => "PNP2", "RES1" => "RES2", "RSP1" => "RSP2", "SIT1" => "SIT2", "VDP1" => "VDP2", "VPM1" => "VPM2"];

But you could also go a bit different route with a bit more configuration options

Names Default To Here(1);

endchar1 = "1";
endchar2 = "2";

rgx_endchar1 = Eval Insert("(.+?)^endchar1^$");

dt = Open("$SAMPLE_DATA/Semiconductor Capability.jmp");


collist = dt << get column names(string);

aa = Associative Array();
For Each({colname}, collist,
	col_ptrn = Regex(colname, rgx_endchar1, "\1");
	If(!IsMissing(col_ptrn), // ends with H
		If(Contains(collist, col_ptrn || endchar2),
			aa[col_ptrn || endchar1] = col_ptrn || endchar2;
		);
	);
);

show(aa);

Both of these do return same results for me

Re: Associate Columns By Name

SpannerHead — Thu, 20 Mar 2025 21:46:23 GMT

Jim

Great script and a lot simpler. I tweaked it a bit to have all the plots in a "Compilation" window .

Names Default To Here( 1 );
dt = Current Data Table();
colNames = dt << get column names( continuous, string );
New Window( "Compilation",
	For Each( {col}, colNames,
		If(
			Starts With( col, "VT" ) & Contains(
				colNames,
				Substr( col, 1, Length( col ) - 1 ) || "H"
			),
			Graph Builder(
				Size( 534, 456 ),
				Show Control Panel( 0 ),
				Variables(
					X( As Column( col ) ),
					Y( As Column( Substr( col, 1, Length( col ) - 1 ) || "L" ) )
				),
				Wrap( :LOT ),
				Elements(
					Points( X, Y, Legend( 1 ) ),
					Line Of Fit( X, Y, Legend( 3 ) )
				)
			)
		)
	)
);

Re: Associate Columns By Name

SpannerHead — Thu, 20 Mar 2025 22:07:03 GMT

Jarmo

Oddness of JMP. First one doesn't work, second one does, JMP 17 maybe? Thanks for putting up with me.