identify respective ranks of two columns in matrix and match them

Hi everyone,

So I want to do something a bit complicated... I have a data matrix like this :

data = [1	0	0	0.0669378275070259
2	12.1056041717529	2	12.0026400602382
3	134.371655941010	9	134.704097205379
4	146.752244949341	10	139.481143043580
5	44.1991274356842	10	60.5235459177957
6	31.6353983879089	4	29.4212902375806
7	84.0974118709564	10	84.1452872130029
8	102.127936840057	7	57.8711186921884
9	104.799964427948	11	143.620002324739
10	28.8503270149232	3	15.5766566556635
11	10.8649168014526	1	10.8318218201762
12	0	0	0
13	22.3535099029541	2	22.3384987376591
14	36.9738082885742	4	36.9579155488247
15	68.8769569396973	6	68.9962084938198
16	68.3138140439987	8	64.6903763947714
17	178.282091140747	11	110.323777721892
18	52.1753841638566	6	51.2907121486635
19	120.173310279846	11	160.508708898427
20	117.115953445435	8	126.973352459287
21	79.8789970874788	12	90.5488463321847
22	113.134207248688	10	125.785059554141
23	39.8428988456726	2	66.6292778475760
24	0	0	0];

The first column is month ID (here I copied 2 months data for the example), 2nd column total rainfall (RF) observed in the month, 3rd column is the number of wet days (i.e. over how many days the RF amount of col2 was observed), and column 4 is the total rainfall amount predicted in the month according to some future climate scenario.

What I want to do is identify the rank of the value in col2, find the corresponding rank of col4, and attribute to it the same # wet days.

For example, month with ID 17 in the observations has the rank 24 (because it is the month with most RF). Its corresponding # wet days is 11. Now I want to attribute (say in a new column 5) this value 11 to month with ID 19 of the scenario (col4) because in this case this is the one with rank 24 in its respective column.

I would like to avoid sorting because in the end I need the values in the right order.

Any idea ?

Thanks

Answer 1

[Dyuman Joshi]

You can perform sorting without changing the original data.

data = [1	0	0	0.0669378275070259
    2	12.1056041717529	2	12.0026400602382
    3	134.371655941010	9	134.704097205379
    4	146.752244949341	10	139.481143043580
    5	44.1991274356842	10	60.5235459177957
    6	31.6353983879089	4	29.4212902375806
    7	84.0974118709564	10	84.1452872130029
    8	102.127936840057	7	57.8711186921884
    9	104.799964427948	11	143.620002324739
    10	28.8503270149232	3	15.5766566556635
    11	10.8649168014526	1	10.8318218201762
    12	0	0	0
    13	22.3535099029541	2	22.3384987376591
    14	36.9738082885742	4	36.9579155488247
    15	68.8769569396973	6	68.9962084938198
    16	68.3138140439987	8	64.6903763947714
    17	178.282091140747	11	110.323777721892
    18	52.1753841638566	6	51.2907121486635
    19	120.173310279846	11	160.508708898427
    20	117.115953445435	8	126.973352459287
    21	79.8789970874788	12	90.5488463321847
    22	113.134207248688	10	125.785059554141
    23	39.8428988456726	2	66.6292778475760
    24	0	0	0];

%actual rainfall
[~,iarf]=sort(data(:,2),'descend');
%predicted rainfall
[~,iprf]=sort(data(:,4),'descend');
data(iprf,5)=data(iarf,3);
data

data = 24×5

1.0000 0 0 0.0669 0 2.0000 12.1056 2.0000 12.0026 2.0000 3.0000 134.3717 9.0000 134.7041 11.0000 4.0000 146.7522 10.0000 139.4811 9.0000 5.0000 44.1991 10.0000 60.5235 6.0000 6.0000 31.6354 4.0000 29.4213 4.0000 7.0000 84.0974 10.0000 84.1453 10.0000 8.0000 102.1279 7.0000 57.8711 10.0000 9.0000 104.8000 11.0000 143.6200 10.0000 10.0000 28.8503 3.0000 15.5767 2.0000

%Not sure why MATLAB Answers is not showing full matrix

[Loriann Chevalier]

Thanks a lot, that works great, exactly like I wanted

[Dyuman Joshi]

You are welcome!