Subscribe Bookmark RSS Feed

stack columns with variable suffix

hungchih65

New Contributor

Joined:

Oct 12, 2017

Dear all:

 

I have a huge table with ~30000 parameters(columns) some with the suffix _W00~_W53 and other columns not.

I  want to stack the table with  total columns/54 with a new column W with value 0~53 and keep the other columns without the suffix originally.

 

I know how to used the JMP stack table manually, but it takes very long time and easily to make mistake, especially for the columns without the suffix. 

 

Does any one know how to write the script to stack the colomns based on the columns with the suffix?

 

Thank you,

 

 

 

4 REPLIES
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

@hungchih65 :
Can you kindly paste a subset of your table or a sample data set ?
Best
Uday
hungchih65

New Contributor

Joined:

Oct 12, 2017

The following is an example. The actual table is far much larger than that.

To Stack the table based on the suffix _W00~W53

and generate a new column based on the suffix.

However, there are some columns without the suffix _W00~W53 between them.

I still need them after table stack so I can group the data in the future.

So I cannot just use JMP stack function

 

DieAA_A_W00AA_A_W01AA_A_W02AA_A_W03AA_A_W04AA_A_W05AA_A_W06AA_A_W07AA_A_W08AA_A_W09AA_A_W10AA_A_W11AA_A_W12AB_A_W00AB_A_W01AB_A_W02AB_A_W03AB_A_W04AB_A_W05AB_A_W06AB_A_W07AB_A_W08AB_A_W09AB_A_W10AB_A_W11….A1:AA31 
1                           
2                           
3                           
4                           
5                           
6                           
7                           
8                           
9                           
                            
DieWAA_AAB_AAC_A…..ZZ_Z..                   ….
10                          
20                          
30                          
40                          
50                          
60                          
70                          
80                          
90                          
11                          
21                          
31                          
41                          
51                          
61                          
71                          
81                          
91                          
….….                          
hungchih65

New Contributor

Joined:

Oct 12, 2017

The attachment is an example. The actual table is far much larger than that.

To Stack the table based on the suffix _W00~W53

and generate a new column based on the suffix.

However, there are some columns without the suffix _W00~W53 between them.

I still need them after table stack so I can group the data in the future.

So I cannot just use JMP stack function

 

txnelson

Super User

Joined:

Jun 22, 2012

Here is an example of a method that you may find you can use

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\semiconductor capability.jmp" );

// Get all of the numeric columns
colList = dt << get column names( numeric );

// Find only the columns that match what column name segments I want
// to filter on.  I only want the columns that end in "N0"~"N8"
For( i = N Items( colList ), i >= 1, i--,
	If(
		Substr( Char( colList[i] ), -2, 1 ) != "N" | ((Try( Num( Right( Char( colList[i] ), 1 ) ), -1 ) < 0 |
		Is Missing( Num( Right( Char( colList[i] ), 1 ) ) ) == 1) | (Try( Num( Right( Char( colList[i] ), 1 ) ), 10 ) >= 9 |
		Is Missing( Num( Right( Char( colList[i] ), 1 ) ) ) == 1)),
		colList = Remove( colList, i, 1 )
	);
);

// Stack the data using the filtered list of columns
dtStacked = dt << Stack(
	columns( colList ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);
Jim