It’s World Statistics Day! To honor the theme of the day, the JMP User Community is having conversations about the importance of trust in statistics and data. And we want to hear from you! Tell us the steps you take to ensure that your data is trustworthy.
Choose Language Hide Translation Bar
Highlighted
ks_SES
Level III

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 <3 sigma, and >3 sigma, the sigma is the standard deviation of the very column.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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.

Learn it once, use it forever!

View solution in original post

3 REPLIES 3
Highlighted

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.

Learn it once, use it forever!

View solution in original post

Highlighted
ks_SES
Level III

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

Thank you! It works.
Highlighted
ks_SES
Level III

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


);

 

Article Labels

    There are no labels assigned to this post.