Turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- ColumnRank by group?

Topic Options

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

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

ColumnRank by group?

Jun 14, 2016 8:27 AM
(3160 views)

Hello,

Is there a way to build a formula equivalent to «ColumnRank by…» (group). I normally use the distribution platform (with «by group» option) and save ranks for that purpose in a static column. But this time, I need a dynamic column (calculated).

Thanks,

Claude

10 REPLIES 10

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

Re: ColumnRank by group?

Hi Claude,

The Col Rank(column, ) function does what you’re looking for. The default for breaking ties is row order, but there’s an optional third argument that allows for some other choices. Hope this helps.

-Jerry

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

Re: ColumnRank by group?

Hi Jerry,

Can you explain more please. The manual is very minimalist about this third argument.

Col Rank

Ranks each row’s value, from 1 for the lowest value to the number of non‐missing columns for the highest value. **Ties are broken arbitrarily**.

I don’t understand what it might mean and how to use it.

Thank you for your help!

Claude

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

Re: ColumnRank by group?

I'm afraid the optional arguments for Col Rank() is not supported by the current JMP version.

I do not have a good workaround that would work truly dynamically. The column formula in the example below should work but only if the variable is sorted within groups.

dt = Open**(**"$SAMPLE_DATA/Big Class.jmp"**)**;

dt << **sort****(**by**(**:age, weight**)**, Replace table**(****1****))**;

F = Expr**(**

Eval**(**Eval Expr**(**Col Number**(**:weight, Row**()** <= Expr**(**Row**())**,:age**)))**

**)**;

dt << **New Column****(**"Rank of weight by age", numeric, formula**(**Name Expr**(**F**)))**;

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

Re: ColumnRank by group?

Hi MS,

Well, this is a very interesting workaround. It’s open to me a completely new area (Eval, Formula, etc.).

I'm crossing my fingers for a future JMP version with this new function "ColRank by…".

Thanks a lot for taking time to answer my question.

Claude

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

Re: ColumnRank by group?

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

Re: ColumnRank by group?

Hi Jerry,

No problem. I'm happy to see that one day my wishes will come true!

Thanks a lot again.

Claude

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

Re: ColumnRank by group?

I am also dissatisfied with the idea of "**Ties are broken arbitrarily"**. there should at least be an option to change that to ties get same rank like would happen in a regular ordinal variable.

I have another way to "rank by" without sorting the table - in my case sorting is a nuisance. it gives equal values to the ties.

best,

ron

Names Default To Here**(** **1** **)**;

// define the data source

dt = Open**(** "$SAMPLE_DATA/Big Class.jmp" **)**;

// add a column for the rank

dt << **New Column(** "Rank" , numeric, ordinal**)**;

//// option 1: Rank by Column

// extract the list of the by variable levels

byvarlist = associative array **(**dt:age**)** << **get keys**;

// create seperate rank lists of height for each age group

for **(**ib = **1**, ib<= nitems**(**byvarlist**)**, ib++,

eval **(**parse**(** "heightlist"||char**(**byvarlist**[**ib**])**||" = Associative Array( "|| char**(** dt:height**[**dt << **get rows where** **(**:age == byvarlist**[**ib**])])**||" ) << get keys"**))**;

**)**;

for **(**ic = **1**, ic<=nrows**(**dt**)**, ic++,

:Rank **[**ic**]** = eval**(** parse **(** "loc( heightlist "||char**(**dt:age**[**ic**]** **)**||" ,"||char**(** dt:height**[**ic**])**||" ) " **))**;

**)**;

//see if it worked

dt<< **Sort(**

By**(** :age, :height **)**,

Order**(** Ascending, Ascending **)**

**)**;

////

//// Option 2: Rank (without a by column)

// get the unique values for height as a list

heightlist = Associative Array**(** dt:height **)** << **get keys**;

For**(** i = **1**, i <= N Rows**(** dt **)**, i++,

dt:Rank**[**i**]** = Loc**(** heightlist, :height**[**i**]** **)**;

**)**;

//see if it worked

dt<< **Sort(**

By**(** :height **)**,

Order**(** Ascending **)**

**)**;

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

Re: ColumnRank by group?

Thanks a lot Ron. It is an ingenious way to solve the problem.

Unfortunately, I cannot use script because I have to go through a computed column. In fact, if I add columns with hard data, the update to my table with my database (via DNS/ODBC) will create a new table instead of simply replace data.

Claude

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

Re: ColumnRank by group?

hi claude.bellavance,

if there is an issue with an update, all you need to do is delete the hard coded columns just before the update and re-introduce them immediately after.

such as:

delete hard coded columns;

update;

re-calculate the hard coded columns;

best,

ron