# How to add rank based on another column for tie breaker?

10 views (last 30 days)
Yean Lim on 3 Nov 2020
Answered: Dave B on 4 Nov 2020
Hi, I have an orignal table without rank column.
So I added the rank in the table based on column pred_proba by using
[~,I] = sort(table, 2 'descend');
Then I append variable I to the table & get a table below:
Factor pred_proba rank
1 0.870 1
2 0.869 2
3 0.665 3
4 0.832 4
5 0.587 5
6 0.785 6
7 0.538 7
10 0.536 8
9 0.536 9
8 0.536 10
I copy the table above to Excel & sort the table by Factor column in ascending order like below.
Factor pred_proba rank
1 0.870 1
2 0.869 2
3 0.665 3
4 0.832 4
5 0.587 5
6 0.785 6
7 0.538 7
8 0.536 10
9 0.536 9
10 0.536 8
The rank value for row with Factor 8, 9, 10 is wrong.
I expect the rank to take the ordered Factor value when there is a tie value in pred_proba like below:
Factor pred_proba rank
1 0.870 1
2 0.869 2
3 0.665 3
4 0.832 4
5 0.587 5
6 0.785 6
7 0.538 7
8 0.536 8
9 0.536 9
10 0.536 10
How do I get the rank correctly, especially when there is a tie value in column pred_proba?
If not, is there any built-in function in Matlab library to do the job?
Thank you.

Dave B on 4 Nov 2020
Hi Yean -
I'm not sure I understand the problem, but here's my take. It looks like you're expecting rank to match factor in this case, because when there's a tie you are expecting rank to preserve the original order. However, in the original dataset you have Factor in non-increasing order. When you moved to excel and sorted by factor, you changed this order.
It seems then like you want to sort by BOTH Factor and pred_proba. To be more explicit: you want to sort so that pred_proba is in descending order, but for ties in pred_proba you want to sort by Factor in ascending order.
You can sort by multiple columns using the sortrows function. Note that the syntax is a bit different with regard to ascending/descending: you use negative values for descending and positive values for ascending, which allows you to specify the direction for each column in the table:
% Sort t by the second column (descending) and
% sort ties in the second column by the first
% column (ascending):
[~,ind]=sortrows(t,[-2 1])

R2020b

### Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!