How to write a compact list of numbered column names?

Question

Hi,

I have a data table, which has a large number of column with names: v1, v2, v3, v4, v5 etc. Going up to more than v200. I want to do a 'stack' of these colums:

newtable= Data Table( "oldtable" ) << Stack(Output Table("newtable")

,columns(:v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8,:v9,:v10 etc. . . .

Is there a simple notation for listing all variables with the same prefix? Like v1-v10? So that I can avoid writing all the individual names?

Regards

Poul

dkeshock · Answer

Do you know if the original table contains only 'v#' columns?

Col_Names_Full = old_dt<<Get Column Names(string);

Cols = N Cols(old_dt);

Col_Names_Stack = List();

Counter = 1;

For(i=1, i<=Cols, i++,

If(Starts With(Col_Names_Full[i], "v"),

Col_Names_Stack[Counter]= Col_Names_Full[i];

Counter= Counter + 1,

empty(),

);

new_dt = old_dt << Stack(

columns(Col_Names_Stack),

Source Label Column( "Label" ),

Stacked Data Column( "Data" ),

Output Table( "newtable" )

);

pmroz · Answer

Hmm nice solution.  Turns out you can remove the 1..length as well.  This works:

new_dt = old_dt << Stack(

columns(col_names),

Source Label Column( "Label" ),

Stacked Data Column( "Data" ),

Output Table( "newtable" )

);

ursula_garczare · Answer

Actually, I just had a rather similar problem and it is not necessary to go via the parsing route. I  just was very stubborn in wanting to avoid that and was lucky in finding an alternative:

Building up on the example above, the statement below shoul also work (JMP Pro 11.1). The trick is the [1::length(col_names)] in the stack statement!

old_dt = New Table( "oldtable",

Add Rows( 2 ),

New Column( "v1", Numeric, Continuous, Format( "Best", 12 ), Set Values( [1, 6] )),

New Column( "v2", Numeric, Continuous, Format( "Best", 12 ), Set Values( [2, 7] )),

New Column( "v3", Numeric, Continuous, Format( "Best", 12 ), Set Values( [3, 8] )),

New Column( "v4", Numeric, Continuous, Format( "Best", 12 ), Set Values( [4, 9] )),

New Column( "v5", Numeric, Continuous, Format( "Best", 12 ), Set Values( [5, 10] ))

);

col_names = old_dt << get column names(string);

new_dt = old_dt << Stack(

columns(col_names[1::length(col_names)]),

Source Label Column( "Label" ),

Stacked Data Column( "Data" ),

Output Table( "newtable" )

);