BookmarkSubscribe
Choose Language Hide Translation Bar

ColumnRank by group?

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

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

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 nonmissing 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

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

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

Re: ColumnRank by group?

Sorry for the confusion, I apparently wasn’t paying attention to which version was active on my system when I was looking at this. So, as a result, you’ve gotten an “unauthorized” preview of functionality planned for a “future” JMP version.

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

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 )

);

Highlighted

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

Re: ColumnRank by group?

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