628941_en-US

liliconcom

How do I scale columns by another column

Hi there, I have a table that looks like this, a few nominal columns and then lots of numerical columns

 

And I want to make a new "table_scaled" that has the nominal columns copied over and the N(#) columns divided by the "N rows" column and *100 to get a %. I'm trying this code but I'm only getting an empty new table with one blank column, any suggestions?

 

dt = Current Data Table();

NewTable("HOT_COLD_summary_scaled",
    For( i = 1, i <= N Cols( HOT_COLD_summary ), i++,
        dt << New Column(
            Column( i ) << get name,
            Numeric,
            "Continuous",
            Formula( (:column( i )*100) /(HOT_COLD_summary:N rows)  )
        )
    )
);

 

 

 

Automation and ScriptingData AccessMass CustomizationQuality and Process EngineeringRe: How do I scale columns by another column

Could you provide an example with correct values on how you want the final table to look like? Also can provide the data table which could be used for developing the script (remove source script and anonymize if needed) as picture of table cannot be used for that.

Re: How do I scale columns by another column

Hi Jarmo,

Thanks, sure thing. I've attached summary which is the starting table and summary_scaled is what I'd like to script.

Re: How do I scale columns by another column

If you have JMP16+ here is one option

Names Default To Here(1);

dt = Open("$DOWNLOADS/summary.jmp");
dt_copy = dt << Subset(All Rows, Selected Columns(0), Output table("summary_scaled"));
close(dt, no save);

col_list = dt_copy << Get Column Names("String");
// first col isn't needed, second col is expected to be N Rows -> skip those

cols_of_interest = Remove(col_list, 1,2);

For Each({ncol}, cols_of_interest,
 new_col = Eval(EvalExpr(
  dt_copy << New Column(ncol ||"_scaled%", Numeric, Continuous, Formula(
   100*Expr(Name Expr(AsColumn(ncol))) / :"N Rows"n
  ))
 ));
 dt_copy << Move Selected Columns({new_col}, after(Eval(ncol)))
);

If you have older JMP you will have to replace For Each with For loop.