jsl :group and stack columns with variable suffixes

[#1]

Hi,

I am new to scripting and would greatly appreciate any help with the following:

I am trying to write a script that will allow me to group and stack columns with variable name suffixes. My table consists of a few columns with names that dont change (i.e. source, frame) and many others (up to hundreds) that begin with x, y, area, perimeter, AR and have the suffix 1, 2, 3, etc . so normally it would look like this...

source, frame, x1, y1, ar1, area 1, angle1, perimeter1, x2, y2, ar, area2, angle2, perimeter2, ....and so on...

I would appreciate greatly any help with putting together a script that would group all the exes, all the ys, etc, regardless of the number. After this I would like to stack them with one column indicating the suffix and the other columns with the data for x, y, area, perimeter.

Hopefully I made myself clear.

I am also attaching a small table to make it easier to follow.

Thanks for any help,

Andres

[#2 · Accepted solution]

Here is a script that I think will do what you want:

Names Default To Here( 1 );
dt = Current Data Table();

// Get all of the column names so we can get a count of the number of
// X and Y columns found
All Columns = dt << get column names;
// Loop through and find the number of Xnn columns & Ynn columns

XList = YList = {};
For( i = 1, i <= N Items( All Columns ), i++,
	If( Uppercase( Substr( All Columns[i], 1, 1 ) ) == "X" & Is Missing( Num( Substr( All Columns[i], 2 ) ) ) == 0,
		Insert Into( XList, Num( Substr( All Columns[i], 2 ) ) )
	);
	If( Uppercase( Substr( All Columns[i], 1, 1 ) ) == "Y" & Is Missing( Num( Substr( All Columns[i], 2 ) ) ) == 0,
		Insert Into( YList, Num( Substr( All Columns[i], 2 ) ) )
	);
);
start = min(XList);
end = max(XList);

// Error checking
If(end!=max(YList) | start!=min(YList) | N items(XList)!=N Items(YList),
	dialog("errors in column matching found"); throw());

// Create Column Lists
For(i=1,i<=nItems(XList),i++,
	XList[i]=column("X" || char(XList[i]));
	YList[i]=column("Y" || char(YList[i]));
);

// Combine the lists
Stack Columns = XList || YList;

// Stack the data
dtStacked =dt << Stack(
	columns( Stack Columns ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Eliminate missing rows( 1 ),
	Drop All Other Columns( 1 ),
	Number of Series( 2 ),
	Contiguous
);

// Create the Suffix column
dtStacked << New Column("Suffix",character,formula(substr(:Label,2)));
// Remove the formula and leave the calculated value
dtStacked:Suffix << delete property("formula");

// Rename the columns, and delete unneeded columns
dtStacked:Data << Set Name("X");
dtStacked:Data 2 << Set Name("Y");
dtStacked << delete columns({"Label","Label 2"});