topic Re: By groups in column formula in for loop in Discussions

By groups in column formula in for loop

olekh — Mon, 16 Dec 2019 13:55:49 GMT

Hi,

I am looping through columns to make new columns of means, std dev and CV for each column. I have the below script, which works fine for global column statistics, but when I try to add by groups, like I have done in the mean column, the formula fails.

I realize that I could accomplish the same using a summary table updated back into the main table.

Thanks!

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp");

New Column("Old",
			Character,
			Formula(If(:age>=14,"Old","Young"))
			);

colnames = dt << get column names( numeric,string );
For( i = 1, i <= N Items( colnames ), i++,
	Eval(
		Substitute(
			Expr(
				New Column( Char( colnames[i] ) || " mean",
				Numeric,
				Formula(
					Col Mean(Column(__colname__),:old)
				) 
				);
				New Column( Char( colnames[i] ) || " STD DEV",
				Numeric,
				Formula(
					Col Std Dev(Column(__colname__))
				) 
				);
				New Column( Char( colnames[i] ) || " CV%",
				Numeric,
				Format( "Percent", 2 ),
				Formula(
					Col Std Dev(Column(__colname__))/
					Col Mean(Column(__colname__))
				) 
				);
			),
		Expr(__colname__),colnames[i]
		)
	)
);

Re: By groups in column formula in for loop

txnelson — Mon, 16 Dec 2019 15:40:49 GMT

JMP appears to be having an issue with using just the Column() function. However, the As Column() function corrects the issue.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

New Column( "Old", Character, Formula( If( :age >= 14, "Old", "Young" ) ) );

colnames = dt << get column names( numeric, string );
For( i = 1, i <= N Items( colnames ), i++,
	Eval(
		Substitute(
				Expr(
					New Column( Char( colnames[i] ) || " mean",
						Numeric,
						Formula( Col Mean( as Column( __colname__ ), :old ) )
					);
					New Column( Char( colnames[i] ) || " STD DEV",
						Numeric,
						Formula( Col Std Dev( as Column( __colname__ ) ) )
					);
					New Column( Char( colnames[i] ) || " CV%",
						Numeric,
						Format( "Percent", 2 ),
						Formula( Col Std Dev( as Column( __colname__ ) ) / Col Mean( as Column( __colname__ ) ) )
					);
				),
			Expr( __colname__ ), colnames[i]
		)
	)
);

Since you are using the Substitute() function, you could also remove the As Column() or Column() functions, and replace the __colname__ with the actual column reference of :theactualcolumnname. See below

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

New Column( "Old", Character, Formula( If( :age >= 14, "Old", "Young" ) ) );

colnames = dt << get column names( numeric, string );
For( i = 1, i <= N Items( colnames ), i++,
	Eval(
		Substitute(
				Expr(
					New Column( Char( colnames[i] ) || " mean",
						Numeric,
						Formula( Col Mean(  __colname__ , :old ) )
					);
					New Column( Char( colnames[i] ) || " STD DEV",
						Numeric,
						Formula( Col Std Dev(  __colname__  ) )
					);
					New Column( Char( colnames[i] ) || " CV%",
						Numeric,
						Format( "Percent", 2 ),
						Formula( Col Std Dev( __colname__  ) / Col Mean(  __colname__  ) )
					);
				),
			Expr( __colname__ ), Parse( ":" || colnames[i] )
		)
	)
);

Re: By groups in column formula in for loop

olekh — Wed, 18 Dec 2019 09:23:33 GMT

Thank you Jim, works perfectly!