MATLAB: Join 2 tables with different values in the key variable

join;merge tablesouterjoin

I would like to join the tables:
A = 6 x 1 table B = 4 x 2 table
Var 1 Var 1 Var 2
930 930 10
931 935 3
932 938 5
935 940 7
936
939
The key variable (Var 1) do not have exactly the same values in Table A and B. I would like to join using the following rule:
Rule: For each value of Var 1 in Table A, find the value of Var 1 in Table 2 that is the MINIMUM and GREATER THAN OR EQUAL TO the value in Table A. Then join the corresponding row of Var 2 from Table B to Table A.
e.g. for 930, the minimum which is greater than or equal to 930 in Table B is 930, so 10 is joined to Table A.
for 931, the minimum which is greater than or equal to 931 in Table B is 935, so 3 is joined to Table A.
for 932, the minimum which is greater than or equal to 932 in Table B is 935, so 3 is joined to Table A.
for 935, the minimum which is greater than or equal to 935 in Table B is 935, so 3 is joined to Table A.
for 936, the minimum which is greater than or equal to 936 in Table B is 938, so 5 is joined to Table A.
for 939, the minimum which is greater than or equal to 939 in Table B is 940, so 7 is joined to Table A.
So the resulting Table A I want would be:
A = 6 x 2 table
Var 1 Var 2
930 10
931 3
932 3
935 3
936 5
939 7
The outer join would not work for me because I cannot have NaN. What would be a good way to achieve this?
Not sure the text formatting here is clear so I attach a pdf for easy reading.

Best Answer

  • The way I would do it:
    d = B.Var1 - A.var1'; %requires R2016b or later
    d(d < 0) = Inf;
    [~, idx] = min(d);
    A.var2 = B.Var2(idx);
    This will work as long as that there is always a value in B that is smaller or equal than the values in A.