Subscribe Bookmark RSS Feed

Re: Stacking columns by group

masum111bd

Occasional Contributor

Joined:

Nov 11, 2017

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

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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

Jim
2 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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

Jim
Highlighted
masum111bd

Occasional Contributor

Joined:

Nov 11, 2017

Thanks you very much.