Intersection of two tables

Hi,

I'm trying to intersect two tables. They share same column with strings, and I want strings that are in both of these tables (intersection).

Example tables:

A = table();
A.user = ["user1";"user2";"user3";"user4"];
A.value = [10;20;30;40]

A = 4×2 table
     user      value
    _______    _____

    "user1"     10  
    "user2"     20  
    "user3"     30  
    "user4"     40  

B = table();
B.user = ["user3";"user4";"user5"];
B.value = [300;400;500]

B = 3×2 table
     user      value
    _______    _____

    "user3"     300 
    "user4"     400 
    "user5"     500 

I would like to get a logical vector (index) which would tell me positions of users, that are in both tables, which are user3 and user4 in this example. Values of the users are not important here, i need only those indexes.

Although this seems simple in my head, I wasn't able to do it in Matlab. I tried using outerjoin(), ismember() and intersect(), but always some errors and empty logical vectors as results.

Answer 1

[Les Beckham]

A = table();
A.user = ["user1";"user2";"user3";"user4"];
A.value = [10;20;30;40]

A = 4×2 table
     user      value
    _______    _____

    "user1"     10  
    "user2"     20  
    "user3"     30  
    "user4"     40  

B = table();
B.user = ["user3";"user4";"user5"];
B.value = [300;400;500]

B = 3×2 table
     user      value
    _______    _____

    "user3"     300 
    "user4"     400 
    "user5"     500 

Since you say you only care about the indices but you didn't say which table you wanted them for, here are both.

idxBinA = ismember(B.user, A.user) % where members of A.user are found in B.user

idxBinA = 3×1 logical array
   1
   1
   0

idxAinB = ismember(A.user, B.user) % where members of B.user are found in A.user

idxAinB = 4×1 logical array
   0
   0
   1
   1

Does that get you what you need?

[Vilém Frynta]

Yes, it looks similar to what I've tried.

But I do fail when applying this solution into my code. My logical vectors are full of zeros. It appears that strings in my table are also inside a cell, which is the issue..

Answer 2

[Askic V]

Maybe this code can help you:

A = table();
A.user = {'user1';'user2';'user3';'user4'};
A.value = [10;20;30;40]

A = 4×2 table
      user       value
    _________    _____

    {'user1'}     10  
    {'user2'}     20  
    {'user3'}     30  
    {'user4'}     40  

B = table();
B.user = {'user3';'user4';'user5'};
B.value = [300;400;500]

B = 3×2 table
      user       value
    _________    _____

    {'user3'}     300 
    {'user4'}     400 
    {'user5'}     500 

ind = ismember(B.user, A.user)

ind = 3×1 logical array
   1
   1
   0

Or you can slightly modified what @Les Beckham suggested:

A = table();
A.user = {"user1";"user2";"user3";"user4"};
A.value = [10;20;30;40]

A = 4×2 table
       user        value
    ___________    _____

    {["user1"]}     10  
    {["user2"]}     20  
    {["user3"]}     30  
    {["user4"]}     40  

B = table();
B.user = {"user3";"user4";"user5"};
B.value = [300;400;500]

B = 3×2 table
       user        value
    ___________    _____

    {["user3"]}     300 
    {["user4"]}     400 
    {["user5"]}     500 

idxBinA = ismember([B.user{:}], [A.user{:}])

idxBinA = 1×3 logical array
   1   1   0

[Vilém Frynta]

idxBinA = ismember([B.user{:}], [A.user{:}])

This version doesn't seem to work. It creates very long logical vector, that is full of 1's.

[Vilém Frynta]

I have even converted the table column into a string, to get rid of the cell.

Still not possible. I'm trying to intersect 21x1 string and 28x1 string and still it doesn't work. Not with ismember and and interesct. :(

[Vilém Frynta]

Oh I found out the problem, thanks for help!