Intersect - extraction of row data based on matched results

Hi everyone, new to matlab codes and need help. Please see attached image. I have 2 datasets, Data1 and Data2. I want to compare each date/time in Data2 to the first column of Data1 and if a match if found, extract all the corresponding row data in Data2 for those matched date/time. In the attached image, comparing the date/time in Data2 to Data1, two matches were found, row 1 and 4 in Data1. The centire corresponding row data for those matched date/time in Data1 are extracted into another matrix called Result.

Any hint or code to go about this?

Thank you!

Answer 1

[Arif Hoq]

date1=[44562.00;44563.00;44564.00];
date2=[44562.00;44564.00];
a={'t1';'t2';'t3'};
b=[1;1;1];
c=[2;3;4];
T1=table(a,b,c);
[Lia,Locb]=ismember(date2,date1);
result=T1(Locb,:)

result = 2×3 table
      a       b    c
    ______    _    _

    {'t1'}    1    2
    {'t3'}    1    4

[Learning]

Thank you for your prompt response! What about if there are duplicates date/time? Can this code be modified such that once a particular date/time is selected, its row index cannot be selected again? That is even if there are duplicate date/time, their unique row numbers will be selected in order to avoid a situation where the row index of a particular date/time is given to both duplicates.

[Arif Hoq]

you can use unique function for unique data, for example

date1=[44562.00;44563.00;44564.00;44565.00;44566.00;44562.00;44564.00];
out=unique(date1)

out = 5×1

44562 44563 44564 44565 44566

here, 44562.00 and 44564.00 are repeated 2 times. but unique function can handles this.

[Learning]

Thanks again for this. I believe I did not do a good job explaining what I want the code to do in the case of duplicates. In your original code, [Lia,Locb]=ismember(date2,date1); Locb contains the row indexes. However if there are duplicates, say for date/time at row 2 and 5, Locb assigns 2 for both rows 2 and 5. I want the extracted rows in Locb to be unique…that is even if a particular number appears twice, I want their actual row numbers to be preserved, both duplicates cannot have the same row numbers assigned to them in Locb.

[Arif Hoq]

date1=[44562.00;44563.00;44564.00;44565.00;44566.00;44562.00;44564.00];
[out ia ic]=unique(date1)

out = 5×1

44562 44563 44564 44565 44566

ia = 5×1

1 2 3 4 5

ic = 7×1

1 2 3 4 5 1 3

i think you are looking for ic value. see here, 44562.00 is repeated 2 times in index 1 and index 6. so ic returns 1 in index 1 and index 6. 44564.00 also repeated 2 times. check the ic value.

you can check more here

https://www.mathworks.com/help/releases/R2019b/matlab/ref/unique.html#d117e1553931

[Learning]

Hi Arif, I think I'm very close. please see image below, As you can see, both 44562 and 44563 appears twice. Looking at the result table, I want the idices/row numbers to be unique...that is the rows for both 44562 appears are displayed under index as 1 and 5. The previous code would have listed the rows or indices for both 44562 as 1 and 1. Is there a way of making sure that once a particular value is selected, it's index cannot be selected again? that is once the first 44562 which is in row 1 is selected, row 1 cannot be selected again The next time 44562 is encounted, it would be row 5 and hence 5 will be the result for the other 44562. Thanks for not making things clear and I appreciate your help!

[Arif Hoq]

You can swap the input arguments to ismember. check the value in Locb

date1=[44562	44563	44564	44563	44562	44565	44566	44567	44568	44569	44570	44571]';
date2=[44562	44563	44564	44563	44562];
[Lia,Locb]=ismember(date1,date2)

Lia = 12×1 logical array
   1
   1
   1
   1
   1
   0
   0
   0
   0
   0
   0
   0

Locb = 12×1

1 2 3 2 1 0 0 0 0 0