How can I group my data per hour in order to not over count my shipping data?

I have attached a sample excel file with the data I am trying to work on.

Essentially, what I am trying to accomplish is trying to get a single call sign per hour, in order to not double/over count the amount of ships. To further describe the results I want:

I start off with this:

BaseDateTime LAT LON SOG COG Heading VesselName IMO CallSign VesselType

5/18/2018 6:00 27.99027 -89.23283 4.4 22 511 PEACEFUL LADY IMO8885975 WDC8901 30

5/18/2018 6:01 27.59748 -88.72898 12.4 -74.9 338 DARYA CHAND IMO9720328 VRNY9 70

5/18/2018 6:00 27.59379 -88.72702 12.4 -75.1 338 DARYA CHAND IMO9720328 VRNY9 70

End up like this:

BaseDateTime LAT LON SOG COG Heading VesselName IMO CallSign VesselType

5/18/2018 6:00 27.99027 -89.23283 4.4 22 511 PEACEFUL LADY IMO8885975 WDC8901 30

5/18/2018 6:00 27.59379 -88.72702 12.4 -75.1 338 DARYA CHAND IMO9720328 VRNY9 70

I really don't know where to start... Thanks for any advice and help!

[Walter Roberson]

Question: what leads you to choose the 06:00 entry for DARYA CHANG over the newer 06:01 entry for the ship ? Is the rule that you want to take the "last" entry in the file for each hour for each ship? Is the rule that you want to take the "earliest" entry in the file for each hour for each ship

Answer 1

[Mathieu NOE]

hello

have to admit I am not super expert in timetables , but I managed to put some code together and test it

i am not sure there is a solution to your problem in one line of code , or without a for loop.

I took the option to use the last entry (for a given hourly slot) , but it's up to you to decide

T = readtable('allday_data_ex.xlsx');
VesselNames = unique(T.VesselName);
VesselNames =  VesselNames(~cellfun('isempty',VesselNames)); % remove empty cells;
 
output_table = [];

for ci = 1:numel(VesselNames)
    
    idx = find(strcmp(T.VesselName,VesselNames(ci))); % extract table row index per VesselName
    Tind = table2timetable(T(idx,:)); % one table for one vessel
    Tind = sortrows(Tind); % sort in the asdending order of dates (to make next line work ok)
    Tind = retime(Tind,'hourly','lastvalue'); % take last entry in given hour
    Tind = rmmissing(Tind);% remove empty lines created by retime
    
    output_table = [output_table; Tind]; % vertical concatenation of timetables
    
end

% export
writetimetable(output_table,'allday_output.xlsx');

[Naomi Mathew]

Thank you so so much! This was exactly what I needed:)

[Mathieu NOE]

My pleasure !