Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- iterate over all columns and do conditional coloring based on standard deviation

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Oct 14, 2019 11:53 AM
(1247 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

2 REPLIES 2

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.