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

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

kaoticllama — Mon, 13 May 2024 15:48:03 GMT

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

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

txnelson — Mon, 13 May 2024 15:59:13 GMT

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 = {};
);

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

kaoticllama — Mon, 13 May 2024 17:30:29 GMT

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.

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

txnelson — Mon, 13 May 2024 17:36:07 GMT

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.

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

kaoticllama — Mon, 13 May 2024 17:52:08 GMT

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

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

txnelson — Mon, 13 May 2024 19:01:57 GMT

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" );
);

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

kaoticllama — Tue, 14 May 2024 15:32:57 GMT

Jim,

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

Best,

kaoticllama

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

jthi — Tue, 14 May 2024 16:28:02 GMT

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