Subscribe Bookmark RSS Feed

Best way to script a column formula into many columns?

Laura_Higgins

Joined:

Jun 23, 2011

I need to apply a formula to many columns. What is the best way to script this?

For example, I want to apply this formula to each column of the Football.jmp table:

New Column( "Height Rank from mean",

  Numeric,

  "Continuous",

  Format( "Best", 12 ),

  Formula( Col Rank( Abs( Col Mean( :Height ) - :Height ) ) )

      )

I've tried a few things - none great. I could either select the columns by hand, or use a dialog. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
ms

Super User

Joined:

Jun 23, 2011

Solution

One way is to make a list with the desired column names and loop over that list, making a new formula column with each iteration:

dt = Data Table("Football.jmp");

cols = dt << get column names(Numeric); //List of all numeric columns

// uncomment row below to select columns by hand instead

// cols= dt<<get selected columns;

For(i = 1, i <= N Items(cols), i++,

    F = Eval Expr(Col Rank(Abs(Col Mean(Expr(cols[i])) - Expr(cols[i]))));

    dt << New Column(cols[i] << get name || " Rank from mean",

        Numeric,

        "Continuous",

        Format("Best", 12),

        Formula(Name Expr(F))

    );

);

1 REPLY
ms

Super User

Joined:

Jun 23, 2011

Solution

One way is to make a list with the desired column names and loop over that list, making a new formula column with each iteration:

dt = Data Table("Football.jmp");

cols = dt << get column names(Numeric); //List of all numeric columns

// uncomment row below to select columns by hand instead

// cols= dt<<get selected columns;

For(i = 1, i <= N Items(cols), i++,

    F = Eval Expr(Col Rank(Abs(Col Mean(Expr(cols[i])) - Expr(cols[i]))));

    dt << New Column(cols[i] << get name || " Rank from mean",

        Numeric,

        "Continuous",

        Format("Best", 12),

        Formula(Name Expr(F))

    );

);