How to consolidate a vector of time stamps and perform interpolation to calculate the missing items?

I have several matrices containing recording of a physical quantity and a timestamp of when the recording was taken (technically at a fixed frequency). The problem is that even though the observation time is the same for all the different sensors, the total number of elements in each matrix is different. Since we are talking about hundreds of thousands of recordings, I can only guess some recordings are not there and therefore I end up with fewer elements than I should. I would like to scan the times, detect if any is missing and in that case perform interpolation in-between the two closest values to fill the gap. The first problem I am encountering is converting the time stamps into a format that is easily manipulated in Matlab. The format I have in the database is MM/DD/YYYY HH:MM:SS AM. Matlab will not recognize this as a date and time and therefore I am not able to perform any operations on the vector. The second problem would be to find the missing items and perform oversampling based on the values I have. The easiest way would be to simply interpolate between the prior and subsequent value found in the time stamp. My date vector would look something like this:

A = ['01/01/2017 00:00:00 AM'; '01/01/2017 00:15:00 AM'; '01/01/2017 00:30:00 AM'; '01/01/2017 01:00:00 AM'];

and obviously the missing item would be 00:45:00AM on 01/01/2017.

Answer 1

[jonas]

Your first problem is a non-issue. Just read your times like this

   A = datetime(A,'format','MM/dd/yyyy hh:mm:ss aa')

A =

4×1 datetime array

   01/01/2017 12:00:00 AM
   01/01/2017 12:15:00 AM
   01/01/2017 12:30:00 AM
   01/01/2017 01:00:00 AM

Put these times, together with your data, into a timetable, TT. For each sensor, you will then have a timetable.

Now, onto the interpolation part. I would suggest that you build a complete time-vector and use retime with your entire dataset. You can then use synchronize to obtain the exact same sampling times on all your sensors and concatenate your data into one single timetable. The first step is to build a complete time-vector:

t_new=datetime('01/01/2017 00:00:00 AM','format','MM/dd/yyyy hh:mm:ss aa'):minutes(15):datetime('01/01/2017 01:00:00 AM','format','MM/dd/yyyy hh:mm:ss aa')

Then just retime

out=retime(TT,t_new);

then synchronize

TT_new = synchronize(TT1,TT2)

This should give you an idea of how to proceed. If you want help with the details, please upload some sample data.

[Alessandro Fascetti]

This worked flawlessly, thank you so much. I was not aware of the Timetable feature, it is absolutely extraordinary, saved me hours of work. I just want to add for future reference that I used the readtable command to import all the data from a .csv file and it imported both timestamps and recordings automatically. Amazing feature.

[jonas]

Happy to help! Yes both timetable and readtable are great! readtable also has a ton of import options (see detectimportoptions ) for when it fails to import automatically