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

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.

Answer 1

[Dave B]

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