cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
masum111bd
Level II

Stacking columns by group

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

Re: Stacking columns by group

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

View solution in original post

2 REPLIES 2
txnelson
Super User

Re: Stacking columns by group

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
masum111bd
Level II

Re: Stacking columns by group

Thanks you very much.