topic How do I scale columns by another column in Discussions

How do I scale columns by another column

SeasonalOwl962 — Sun, 11 Jun 2023 00:00:59 GMT

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

Re: How do I scale columns by another column

jthi — Fri, 05 May 2023 13:30:45 GMT

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

SeasonalOwl962 — Fri, 05 May 2023 14:03:52 GMT

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

jthi — Fri, 05 May 2023 16:00:06 GMT

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.