Join 2 tables with different values in the key variable

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.

[Guillaume]

I've adjusted the formatting of your post. There is a help button with explanation of the formatting options.

Answer 1

[Guillaume]

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.

[Jonathan Chan]

How can I work around if I only have a version earlier than R2016b?

[Guillaume]

d = bsxfun(@minus, B.Var1, A.Var1');

will work in any version

[Jonathan Chan]

Thanks so much for your help.

Answer 2

[the cyclist]

I am not that familiar with table manipulations. This will work, but I'm guessing there might be some more elegant ways:

A = table([930; 931; 932; 935; 936; 939]);
B = table([930; 935; 938; 940],[10;3;5;7]);

bigEnoughArrayIndex = A.Var1'<=B.Var1;

[row,col] = find(bigEnoughArrayIndex);
minBigEnoughColumnIndex = accumarray(col,row,[],@min);

joinedTable = table(A.Var1,B.Var2(minBigEnoughColumnIndex))

[Jonathan Chan]

Thanks. A.Var1' and B.Var1 have different sizes. May I know how can they be compared using the <= operator?

[the cyclist]

That line will work if you have a relatively new version of MATLAB, because it will explicitly expand the vectors.

Use

bsxfun(@le,A.Var1',B.Var1)

if you have an older version.

[Jonathan Chan]

Thanks so much for your help.

Answer 3

[Peter Perkins]

The three join functions don't do inequality joins. In any case, I think what you want is a many-to-one correspondence. In a recent MATLAB, try this:

>> x1 = [930 931 932 935 936 939];
>> x2 = [930 935 938 940];
>> y2 = [10 3 5 7];
>> y1 = discretize(x1,x2([1 1:end]),y2,'IncludedEdge','right')
y1 =
    10     3     3     3     5     7

You'll have to patch it up for values in x1 outside the range of x2. Hope it helps.

[Jonathan Chan]

Thanks so much for your help.