Pulling the last observed value in a table.

Good Morning/Evening/Night MATLAB wizards.

I have the following code:

clear;
clc;
%--------------------------------------------------------------------------
% Table 1
Color1 = ["Blue"; "Orange";"Purple";"Blue";"Fuschia";"Red"];
LicensePlate1 = [12569;12897;56783;45231;78999;99999];
t1first = datetime(2021,1,1,0,0,0);
t2first = datetime(2021,1,1,3,0,0);
tfirst = t1first:minutes(35):t2first;
tfirst=tfirst';
cars1 = table(Color1,LicensePlate1,tfirst);

%--------------------------------------------------------------------------
% Table 2
Color2 = ["Blue"; "Orange";"Purple";"Blue";"Fuschia";"Orange"];
LicensePlate2 = [88888;12897;77777;45231;88999;12897];
t1second = datetime(2022,1,1,2,0,0);
t2second = datetime(2022,1,1,5,0,0);
tsecond = t1second:minutes(35):t2second;
tsecond=tsecond';
cars2 = table(Color2,LicensePlate2,tsecond);

%--------------------------------------------------------------------------
% Find Matching Values
[match, index] = ismember(cars1.LicensePlate1, cars2.LicensePlate2);
x = cars1(match, :)
y = cars2(index(match), :)

The scenerio is that cars are driving past my location and I take down some information. At first, I wanted to know how to find the matching values to compare times. Big shout out to Jan whom previously helped me with the last three lines of code. I am helpless though and am turning back to this forum.

I made a slight adjustment and now I see the Orange car with License Plate 12897 twice on my second day. (They left and came back for example). When I compare the tables, only the first occurance for the orange car shows up. Lets say I only want the last occurance for the orange car to show up in the table but wish to keep the other values as well, how would I go about coding this?

For example, the table "y" currently outputs:

Orange 12897 1-Jan-2022 02:35:00

Blue 56331 1-Jan-2022 03:45:00

But the last time we saw the orange car was actually at 1-Jan-2022 at 04:55:00. So I would like the table to ignore all the other times we saw the orange car and read (order of appearance between any of the entries does not matter):

Orange 12897 1-Jan-2022 04:55:00

Blue 56331 1-Jan-2022 03:45:00

Thank you in advance! I appreciate all of the help! If anything needs further explination, please let me know!

[John Doe]

I attempted to make the tables small for ease but is there anyway to extrapolotate this in the code to work even if we see the orange car n times on day 1 and m times on day 2? Hopefully this isn't asking too much! Thank you again!

Answer 1

[Aman Banthia]

Hi John,

I understand that you want to have the latest timestamp of a car which passed through regardless of how many times before it may have passed.

% Find Matching Values
[match, index] = ismember(cars1.LicensePlate1, cars2.LicensePlate2);
% Filter out duplicate matches for Orange car
unique_match = unique(cars1.LicensePlate1(match));
last_occurrenceX = [];
last_occurrenceY = [];
for i = 1:length(unique_match)
    idxX =  find(cars1.LicensePlate1 == unique_match(i));
    idxY = find(cars2.LicensePlate2 == unique_match(i));
    last_idxX = idxX(end);
    last_idxY = idxY(end);
    last_occurrenceX = [last_occurrenceX; last_idxX];
    last_occurrenceY = [last_occurrenceY; last_idxY];
end
% Filter rows in cars2 using last_occurrence indices
x = cars1(match(last_occurrenceX), :);
y = cars2(last_occurrenceY, :);

The above code iterates for both day1 and day2 and not just through day2 which was the problem with the past code.

Hope this solution helps.

Best regards,

Aman Banthia