stack columns with variable suffix

Question

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,

txnelson · Answer

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" ) );

hungchih65 · Answer

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

example.xlsx

hungchih65 · Answer

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                          ….….