How can I interpolate a datetime data series to have an interval of 1 minute in MATLAB?

Hi everyone,

I'm analyzing a sample dataset from a .csv file (Attached). The columns are: "Ref number, MMSI, Latitude, Longitude, Timestamp (DateTime), Speed, Course". But the data is not consistent with time and, the interval between data points is not the same. Also, the data have to be grouped by the MMSI (the ID). So each ID has a set of data points but they have different time intervals.

How can I take each group of data and check their consistency, and then interpolate all the data such that, the time interval between two consecutive data points is one minute?

What I want to achieve is basically:

Data should be grouped based on MMSI - which is basically the ID. (Thanks to this community, I can get this part done without a problem)

Each group should have a consistent data series (of Latitude, Longitude, Timestamp (DateTime), Speed, Course) with a time interval of one minute between two points.

How can I approach this?

Answer 1

[Ameer Hamza]

Try something like this

T = readtable('sample.xlsx');

[grps, unique_mmsi] = findgroups(T.mmsi);
tts = splitapply(@(la,lo,da,so,co) {timetable(da, [la lo so co])}, T(:,3:end), grps);
out = cellfun(@(tt) retime(tt, 'minutely'), tts, 'uni', 0)

The issue here is that some rows having the same mmsi have duplicate timestamps with slightly different data values. This prevents linear interpolation. For example, check row 295 and 296.

[Jake]

Yes, that is an issue. I should delete the rows with the same MMSI and Time-stamp! Thanks for the heads up :)

Also, could you please break-down what happens here, specially in the last two rows? Or a reference link would suffice. Because if I wanted to change the time interval between points, I'm not sure how to achieve that. Thanks again for the quick response!

[Ameer Hamza]

This line

tts = splitapply(@(la,lo,da,so,co) {timetable(da, [la lo so co])}, T(:,3:end), grps);

is just creating timetables for each unique mmsi value: https://www.mathworks.com/help/matlab/timetables.html. The reason I created timetables is to use retime() function. It can interpolate a timetable in several ways as described in documentation: https://www.mathworks.com/help/matlab/ref/timetable.retime.html. In my case, I am using minutely retiming

retime(tt, 'minutely')

and I put it inside cellfun() which is just a compact way to write a for-loop. The code is equivalent to following

..
tts = splitapply(@(la,lo,da,so,co) {timetable(da, [la lo so co])}, T(:,3:end), grps);
out = cell(size(tts));
for i = 1:numel(out)
    out{i} = retime(tts{i}, 'minutely');
end

Also, note that you can do also specify interpolation in retime

retime(tts{i}, 'minutely', 'linear')

or several other ways as described in documentation.

[Jake]

This is really helpful! Thank you, Ameer!

[Jake]

I just noticed that you're creating a time table with tts that considers [lat lon sog cog] as one unit. As a result, the out gives a similar output and time is interpolated correctly, however the other values are NaN. I'm commenting this only as a slight concern. Thanks again!

[Ameer Hamza]

Are you using linear interpolation after removing duplicates? I think if you use interpolation it will remove the NaN values.

[Jake]

No, I just ran the same code you posted, without making any changes.(I didn't remove duplicates) I'm currently working on the changes, actually :)

This is the NaN that I mentioned.

[Ameer Hamza]

Yes. Currently you will get NaN. Linear interpolation can give good result but that is only possible after duplicates are removed. Currently, you can use something like the following to avoid NaN

out = cellfun(@(tt) retime(tt, 'minutely', 'fillwithconstant'), tts, 'uni', 0)

Following shows one way to automatically remove the duplicates and then using linear interpolation

T = readtable('sample.xlsx');
[grps, unique_mmsi] = findgroups(T.mmsi);
tts = splitapply(@(la,lo,da,so,co) {timetable(da, [la lo so co])}, T(:,3:end), grps);
[~, idxs] = cellfun(@(tt) unique(tt.da), tts, 'uni', 0);
tts = cellfun(@(tt, idx) tt(idx,:), tts, idxs, 'uni', 0);
out = cellfun(@(tt) retime(tt, 'minutely', 'linear'), tts, 'uni', 0)

[Jake]

I was following the same approach that you included in line 4!! You saved the day. Thanks a lot!

[Ameer Hamza]

I am glad to be of help! :)