228651_en-US

liliconcom

conditional coloring of a column's cells based on standard deviation

I need to color the cells in a column based on whether the value of a certain cell is: within 1 sigma, > 1 sigma but <2 sigma, > 2 sigma but sigma, and >3 sigma, the sigma is the standard deviation of the very column.

Re: conditional coloring of a column's cells based on standard deviation

This script is not complete or that efficient, but it gives you an idea.

 

Names Default to Here( 1 );

dt = Open( "$SAMPLE_DATA/Fitness.jmp" );

mu = Col Mean( :weight );
sd = Col Std Dev( :weight );

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

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

lo = mu - sd;
hi = mu + sd;
rows = dt << Get Rows Where( lo <= :weight <= hi );
:weight << Color Cells( "Green", rows );

 

You could improve the Boolean tests for efficiency if you have a large number of rows.

Re: conditional coloring of a column's cells based on standard deviationThank you! It works.Re: conditional coloring of a column's cells based on standard deviation

Hi, Mark, I have one more related question from this post. In my dataframe, I have more than 10 columns, editing and pasting the code you provided is painful. I tried to create a for loop myself, but it does not work. Would you please provide some advice on this? Thank you! 

 

 

col=dt<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 );


);