Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
ks_SES
Level III

iterate over all columns and do conditional coloring based on standard deviation

My original goal was to  color the cells in a single column based on whether the value of a certain cell is: within 1 sigma, > 1 sigma but <2 sigma, > 2 sigma but <3 sigma, and >3 sigma, the sigma is the standard deviation of the very column. Now I hope to iterate over all columns and execute the conditional coloring on all of them, but my for loop code as follows does not work. 

col=dt<<get column names(string);
nc=nitems(col);

for(i=2,i<=nc,i++,

mu = Col Mean( :Name(col[i]) );
sd = Col Std Dev( :Name(col[i]) );

lo = mu - 3*sd;
hi = mu + 3*sd;
rows = dt << Get Rows Where( lo <= :Name(col[i]) <= hi );
:Name(col[i]) << Color Cells( "Red", rows );

rows = dt << Get Rows Where( :Name(col[i])< lo | :Name(col[i])> hi );
:Name(col[i]) << Color Cells( "Purple", rows );

 

lo = mu - 2*sd;
hi = mu + 2*sd;
rows = dt << Get Rows Where( lo <= :Name(col[i]) <= hi );
:Name(col[i]) << Color Cells( "Yellow", rows );

lo = mu - sd;
hi = mu + sd;
rows = dt << Get Rows Where( lo <= :Name(col[i]) <= hi );
:Name(col[i]) << Color Cells( "Green", rows );


);

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: iterate over all columns and do conditional coloring based on standard deviation

I modified your script a little, by changing your references from using the :Name() function, to using the Column() and As Column() functions.  I also changed the << get column names call slightly.  But other than that, you were on the right path.  My script appears to work as you wanted

Names Default To Here( 1 );
dt = Current Data Table();
col = dt << get column names( string, continuous );
nc = N Items( col );

For( i = 1, i <= nc, i++, 

	mu = Col Mean( Column( col[i] ) );
	sd = Col Std Dev( Column( col[i] ) );

	lo = mu - 3 * sd;
	hi = mu + 3 * sd;
	rows = dt << Get Rows Where( lo <= as Column( col[i] ) <= hi );
	Column( col[i] ) << Color Cells( "Red", rows );

	rows = dt << Get Rows Where( as Column( col[i] ) < lo | as Column( col[i] ) > hi );
	Column( col[i] ) << Color Cells( "Purple", rows );

 

	lo = mu - 2 * sd;
	hi = mu + 2 * sd;
	rows = dt << Get Rows Where( lo <= as Column( col[i] ) <= hi );
	Column( col[i] ) << Color Cells( "Yellow", rows );

	lo = mu - sd;
	hi = mu + sd;
	rows = dt << Get Rows Where( lo <= as Column( col[i] ) <= hi );
	Column( col[i] ) << Color Cells( "Green", rows );


);
Jim

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

Re: iterate over all columns and do conditional coloring based on standard deviation

I modified your script a little, by changing your references from using the :Name() function, to using the Column() and As Column() functions.  I also changed the << get column names call slightly.  But other than that, you were on the right path.  My script appears to work as you wanted

Names Default To Here( 1 );
dt = Current Data Table();
col = dt << get column names( string, continuous );
nc = N Items( col );

For( i = 1, i <= nc, i++, 

	mu = Col Mean( Column( col[i] ) );
	sd = Col Std Dev( Column( col[i] ) );

	lo = mu - 3 * sd;
	hi = mu + 3 * sd;
	rows = dt << Get Rows Where( lo <= as Column( col[i] ) <= hi );
	Column( col[i] ) << Color Cells( "Red", rows );

	rows = dt << Get Rows Where( as Column( col[i] ) < lo | as Column( col[i] ) > hi );
	Column( col[i] ) << Color Cells( "Purple", rows );

 

	lo = mu - 2 * sd;
	hi = mu + 2 * sd;
	rows = dt << Get Rows Where( lo <= as Column( col[i] ) <= hi );
	Column( col[i] ) << Color Cells( "Yellow", rows );

	lo = mu - sd;
	hi = mu + sd;
	rows = dt << Get Rows Where( lo <= as Column( col[i] ) <= hi );
	Column( col[i] ) << Color Cells( "Green", rows );


);
Jim

View solution in original post

Highlighted
ks_SES
Level III

Re: iterate over all columns and do conditional coloring based on standard deviation

Thank you! It works. 

Article Labels

    There are no labels assigned to this post.