topic Re: JSL: how to group all columns by common initial term? in Discussions

JSL: how to group all columns by common initial term?

LambdaMarmoset7 — Wed, 27 Sep 2023 23:58:45 GMT

I've got a dt (70k cols) that I'd like to group into common bundles, based on the initial delineated terms.

For example, 10K cols each that all start with "ChamberID", or "RecipeID", or "SlotNo".

How can I script this to churn through the dt to find all common cols, group them into a collapsible bundle?

Re: JSL: how to group all columns by common initial term?

txnelson — Thu, 28 Sep 2023 00:42:15 GMT

Here is a little script that is an example of creating Column Groups in a data table.

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( string, continuous );

group1 = {};
group2 = {};
For Each( {name}, colNames,
	/*If(
		Left( name, 9 ) == "ChamberID",
			Insert Into( group1, name ),
		Left( name, 8 ) == "RecipeID",
			Insert Into( group2, name )
	)*/
	If(
		Left( name, 3 ) == "NPN",
			Insert Into( group1, name ),
		Left( name, 3 ) == "PNP",
			Insert Into( group2, name )
	)
);

dt << group columns( "NPN", group1);
dt << group columns( "PNP", group2);

Re: JSL: how to group all columns by common initial term?

jthi — Thu, 28 Sep 2023 05:43:06 GMT

There are many ways of doing this. I have used something similar as I demonstrate in below script (using associative array)

Names Default To Here(1);

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

aa_groups = ["DELL" => {}, "DELW" => {}, "RCON" => {}];
col_names = dt << Get Column Names("String");

For Each({col_name}, col_names,
	For Each({{key, value}}, aa_groups,
		If(Starts With(col_name, key ||"_"), // "_" is used as additional separator
			Insert Into(aa_groups[key], col_name);
			Break();
		)
	);
);

For Each({{groupname, groupcols}}, aa_groups,
	dt << Group Columns(groupname, groupcols);
);

If you need to go more dynamic you can also do that

Names Default To Here(1);

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

aa_groups = Associative Array();
col_names = dt << Get Column Names("String");

For Each({col_name}, col_names,
	nameparts = Words(col_name, "_"); // depending ion your columns, this might need to change
	If(N Items(nameparts) > 1,
		If(!Contains(aa_groups, nameparts[1]),
			aa_groups[nameparts[1]] = {}; // add new key
		);
		Insert Into(aa_groups[nameparts[1]], col_name);
	);
);

For Each({{groupname, groupcols}}, aa_groups,
	dt << Group Columns(groupname, groupcols);
);

Idea is the same in both. Create associative array where they keys are column group names and the values are list of columns which belong to that group.