topic Re: Stacking columns by group in Discussions

Stacking columns by group

masum111bd — Fri, 09 Jun 2023 23:24:31 GMT

Hi,

I am new to JSL scripting. I want to stack columns coming from same common name (upto certain length). Such as I have column name "66_CORR_B1_W00", "66_CORR_B1_W01" and so on upto "66_CORR_B1_W12". They should be in single column named""66_CORR_B1". And this will repeat for another column group name starring with "66_CORR_B2". There could be 20 groups of these columns. Finally I want 20 columns that comes from 20 groups of columns.

Here I attach a sample data table.

Thanks in advance.

Muhammad Rahman

Re: Stacking columns by group

txnelson — Sun, 12 Nov 2017 13:08:01 GMT

Below is a script that works for the sample data you encluded in your initial Discussion Entry.

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

ColList = dt << get column names( string, numeric );
// Loop across the column names and find only the 
// columns desired
For( i = N Items( ColList ), i >= 1, i--,
	If( Contains( ColList[i], "66_CORR_B" ) == 0,
		ColList = Remove( ColList, i, 1 )
	)
);
// Sort the columns to ensure they are in the correct order
// for the contiguous stacking
ColList = Sort List( ColList );
// Stack the data
Data Table( "Sample_data" ) << Stack(
	columns( ColList ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Number of Series( 2 ),
	Contiguous
);

The script is very basic, in that it does not check for situations where there are not equal number of columns for each grouping, the columns to be selected do not follow a very good set of names.

But it should give you an idea of how to procede

Re: Stacking columns by group

masum111bd — Sun, 12 Nov 2017 15:39:51 GMT

Thanks you very much.