Is Creating Multiple Stacked Tables From One Original Table in a Nested For Loop Possible?

[#1]

I would like to automate creating multiple stacked tables from one original data table. I plan on doing this by creating a list of keywords, and if any column in my original table contains that keyword then it would be stacked in a new data table. Essentially I have a list of 3 keywords, so I would be creating 3 stacked data tables. My script has a nested for loop to iterate through all the columns in the original data table and see if they contain the keyword being indexed. See below:

groupNames = {"abc", "def", "ghi"};


for (i = 1, i <= nitems(groupNames), i++,
for (j = 1, j <= ncols(Data Table("Original Data")), j++,
if ((contains(Column Name(j), groupNames[i])),
Insert Into(tempStack, Column Name(j));
);
);
tempTable = Stack(
Columns(tempStack),
Source Label Column("Label"),
Stacked Data Column("Data"),
"Non-stacked columns"n (Keep(:tst_temp, :stdf_file)),
);
tempStack = {};
tempTable << set name(char(groupNames[i]) || " Stacked");
);

The problem that arises when executing this code is that the first group gets stacked just fine, but when the for loop indexes to i=2 to look for the next group it fails to look at the original data table and instead looks at the table that was just stacked (Data table "abc Stacked" in this case).

How can I make it so that my for loop looks at my original data table instead of the newly created table. Thanks!

Best,

kaoticllama

[#2]

You just need to specify to use the Original Data to the Stack input

groupNames = {"abc", "def", "ghi"};


For( i = 1, i <= N Items( groupNames ), i++,
	For( j = 1, j <= N Cols( Data Table( "Original Data" ) ), j++,
		If( (Contains( Column Name( j ), groupNames[i] )),
			Insert Into( tempStack, Column Name( j ) )
		)
	);
	tempTable = data table("Original Data") << Stack(
		Columns( tempStack ),
		Source Label Column( "Label" ),
		Stacked Data Column( "Data" ),
		"Non-stacked columns"n( Keep( :tst_temp, :stdf_file ) ),
		Output Table( Char( groupNames[i] ) || " Stacked" )
	);
	tempStack = {};
);

[#3]

Hey Jim,

Unfortunately I tried this already and it doesn't seem to work. It still gets confused and thinks I am referring to the newly stacked table when executing the i=2 iteration. I can tell because my newly stacked table has 4 columns. The error I receive is a j indexing error that fails when j=5.

For reference, I am using JMP Pro 17.2.

[#4]

I may have confused you with making 2 changes to your code, but only talking about one.   When you made your most recent test, did you use the line

tempTable = data table("Original Data") << Stack(

If you did and then got the error you specified, I will ask you to supply a sample data table, and I will explore the issue more.

[#5]

Hey Jim,

No worries, here is the updated code and sample data. It still does not work with the change you suggested.

tempStack = {};

GroupNames = {"abc", "def"};

for (i = 1, i <= nitems(GroupNames), i++,
	for (j = 1, j <= ncols(Data Table("Sample")), j++,
		if ((contains(Column Name(j), GroupNames[i])),
			Insert Into(tempStack, Column Name(j));
		);
	);
	colNum = ncols(Data Table("Sample"));
	tempTable = Data Table("Sample") << Stack(
	Columns(tempStack),
	Source Label Column("Label"),
	Stacked Data Column("Data"),
	"Non-stacked columns"n (Keep(:tst_temp, :stdf_file)),
	Invisible(1)
	);
	tempStack = {};
	tempTable << set name(char(GroupNames[i]) || " Stacked");
);

Let me know what you find. Thanks

[#6 · Accepted solution]

It was the Column Name() function that was messing the script up.  It points to the current active data table.  Thus, I added the setting of the Current Data Table back to the original Sample data table.

names default to here(1);
tempStack = {};

GroupNames = {"abc", "def"};

for (i = 1, i <= nitems(GroupNames), i++,
	Current data table(data table("Sample"));
	for (j = 1, j <= ncols(Data Table("Sample")), j++,
		if ((contains(Column Name(j), GroupNames[i])),
			Insert Into(tempStack, Column Name(j));
		);
	);
	colNum = ncols(Data Table("Sample"));
	tempTable = Data Table("Sample") << Stack(
	Columns(tempStack),
	Source Label Column("Label"),
	Stacked Data Column("Data"),
	"Non-stacked columns"n (Keep(:tst_temp, :stdf_file)),
	Invisible(1)
	);
	tempStack = {};
	tempTable << set name(char(GroupNames[i]) || " Stacked");
);

Using a variable as a pointer to a data table is sometimes very convenient.  In the script below, the variable dt is set to the Current Data table and then used in the script to make sure the code is using the correct table.

Names Default To Here( 1 );
tempStack = {};

GroupNames = {"abc", "def"};
dt = Current Data Table();

For( i = 1, i <= N Items( GroupNames ), i++,
	For( j = 1, j <= N Cols( dt ), j++,
		If( (Contains( Column( dt, j ) << get name, GroupNames[i] )),
			Insert Into( tempStack, Column( dt, j ) << get name )
		)
	);
	colNum = N Cols( dt );
	tempTable = dt << Stack(
		Columns( tempStack ),
		Source Label Column( "Label" ),
		Stacked Data Column( "Data" ),
		"Non-stacked columns"n( Keep( :tst_temp, :stdf_file ) ),
		Invisible( 1 )
	);
	tempStack = {};
	tempTable << set name( Char( GroupNames[i] ) || " Stacked" );
);

[#7]

Jim,

Thanks a lot for your help on this one! Very well explained and works perfectly.

Best,

kaoticllama

[#8]

Here is other suggestion which modified your script quite heavily. It uses a bit different approach using For Each() (easier to read (in my opinion) than For()) and associative array to collect the column groups.

Names Default To Here(1);

// Initialize Associative Array with groups and empty lists
groups = ["abc" => {}, "def" => {}];

dt = Open("$DOWNLOADS/Sample.jmp");
colnames = dt << Get Column Names("String");

// Collect columns to each key in associative array
For Each({colname}, colnames,
	For Each({{group, val}}, groups,
		If(Contains(colname, group),
			Insert Into(groups[group], colname);
		);
	);
);

// Loop over the associative array and create the stacked tables
stacked_tables = {};
For Each({{groupname, groupcols}}, groups,
	stacked_dt = dt << Stack(
		Columns(Eval(groupcols)),
		Source Label Column("Label"),
		Stacked Data Column("Data"),
		"Non-stacked columns"n(Keep(:tst_temp, :stdf_file)),
		Output Table(groupname || " Stacked"),
		Invisible(1)
	);
	Insert Into(stacked_tables, stacked_dt);
);

show(stacked_tables);