topic Re: Atuomate assigning columns to groups in Discussions

Atuomate assigning columns to groups

MathStatChem — Thu, 31 Oct 2024 23:26:59 GMT

I've been struggling to figure this out. I have a table (Table1) that has columns that I want grouped. Table2 has the column names and group labels I want to use. I want to script and automate creating the grouped table, as shown in the screenshot below. Any suggestions on how to do this would be appreciated.

here are scripts to create Table1 and Table2 shown above

dt1 = New Table( "Table1",
	Add Rows( 3 ),
	New Column( "A", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [35, 10, 88] ) ),
	New Column( "B", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 3, 2] ) ),
	New Column( "C", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 3, 1] ) ),
	New Column( "D", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 2, 2] ) )
);

dt2 = New Table( "Table2",
	Add Rows( 4 ),
	New Column( "Name", Character, "Nominal", Set Values( {"A", "B", "C", "D"} ) ),
	New Column( "GroupName", Character, "Nominal", Set Values( {"G1", "G1", "G2", "G2"} ) )
);

Re: Atuomate assigning columns to groups

txnelson — Fri, 01 Nov 2024 02:17:49 GMT

Here is one way to handle the issue:

Names Default To Here( 1 ); 

dt1 = New Table( "Table1",
	Add Rows( 3 ),
	New Column( "A", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [35, 10, 88] ) ),
	New Column( "B", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 3, 2] ) ),
	New Column( "C", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 3, 1] ) ),
	New Column( "D", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 2, 2] ) )
);

dt2 = New Table( "Table2",
	Add Rows( 4 ),
	New Column( "Name", Character, "Nominal", Set Values( {"A", "B", "C", "D"} ) ),
	New Column( "GroupName", Character, "Nominal", Set Values( {"G1", "G1", "G2", "G2"} ) )
);

GroupNames = Associative Array( dt2:GroupName ) << get keys;
For Each( {theGroup}, GroupNames, 
	// Create a list for a group
	theRows = dt2 << get rows where( :GroupName == theGroup );
	grpList = {};
	For Each( {rowValue}, theRows, Insert Into( grpList, dt2:Name[rowValue] ) );
	dt1 << Group Columns( theGroup, grpList );
)
;

Re: Atuomate assigning columns to groups

jthi — Fri, 01 Nov 2024 04:57:04 GMT

I use almost the same idea as Jim, but I like separating the group collection and creation into their own loops

Names Default To Here(1);


dt1 = New Table("Table1",
	Add Rows(3),
	New Column("A", Numeric, "Continuous", Format("Best", 12), Set Values([35, 10, 88])),
	New Column("B", Numeric, "Continuous", Format("Best", 12), Set Values([2, 3, 2])),
	New Column("C", Numeric, "Continuous", Format("Best", 12), Set Values([1, 3, 1])),
	New Column("D", Numeric, "Continuous", Format("Best", 12), Set Values([2, 2, 2]))
);

dt2 = New Table("Table2",
	Add Rows(4),
	New Column("Name", Character, "Nominal", Set Values({"A", "B", "C", "D"})),
	New Column("GroupName", Character, "Nominal", Set Values({"G1", "G1", "G2", "G2"}))
);

aa_groups = Associative Array(Column(dt2, "GroupName"));

For Each({key}, aa_groups << get keys,
	aa_groups[key] = dt2 << Get Rows Where(:GroupName == key);
);

For Each({{name, cols}}, aa_groups,
	dt1 << Group Columns(name, cols);
);