Subscribe Bookmark RSS Feed

How to write a compact list of numbered column names?

poulravn

Community Trekker

Joined:

Jan 25, 2012

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

4 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

Here's one way to do it.  You build an expression and execute it via eval(parse()).

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

for (i = 1, i <= nitems(col_names), i++,

    if (i == 1,

        col_string = "columns(:" || col_names[i];

        ,

        col_string = col_string || ", :" || col_names[i];

    );

);

col_string = col_string || ")";

stack_expr = evalinsert(

"\[new_dt = old_dt << Stack(

    ^col_string^,

    Source Label Column( "Label" ),

    Stacked Data Column( "Data" ),

    Output Table( "newtable" )

)]\");

eval(parse(stack_expr));

ursula_garczare

Community Trekker

Joined:

May 1, 2012

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

);

pmroz

Super User

Joined:

Jun 23, 2011

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

);

dkeshock

Community Trekker

Joined:

May 28, 2014

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

);