# missing data, time series

17 views (last 30 days)
Johannes Deelstra on 3 May 2019
Commented: Johannes Deelstra on 15 May 2019
I am recording groundwater level at hourly intervalls using a data logger. I have data from 31 May 2017 09:00 hrs until 3 May 2019 03:00 hrs.
I noticed there are missing data as the total number of records should be 16843.
I generate a timeseries bb=[a1:ap:ae]; covering the indicated period where a1=datenum(2017,5,31,9,0,0), ap=datenum(0,0,0,1,0,0) and ae=datenum(2019,5,3,3,0,0) which gives the length of the time series being 16843
I read the logger data from excel into matlab with date in first column aa(:,1) while the second column (aa(:,2) contains the recorded logger data. I convert the Excel data to matlab data.
How can I now merge the data from aa with the dates in bb so that a second column (bb(:,2) has the reccorded logger data merged/combined with the correct date.
thanks before hand
Johannes

Peter Perkins on 14 May 2019
All you need to do is call retime and tell it what you want the new time v ecgtor to be. Sometimes it's as simple as 'hourly', other times you'll need to create a time vector yourself. It sounds like you want every hour represented. 'hourly' does that.
Johannes Deelstra on 15 May 2019
thanks very much
used somwe time bugt in thne end I had discovered what you now write about
regards

Peter Perkins on 3 May 2019
Johannes, If I understand your question corre3ctly, you may want to consider using datetimes and a timetable. Not sure what form your data are in, but given a vector of values and a vector of timetstamps, both with a hole in them, this fills in the hole, in two different ways:
>> tt = timetable([1;2;4;5],'RowTimes',datetime(2019,5,3,[15 16 18 19],0,0))
tt =
4×1 timetable
Time Var1
____________________ ____
03-May-2019 15:00:00 1
03-May-2019 16:00:00 2
03-May-2019 18:00:00 4
03-May-2019 19:00:00 5
>> retime(tt,'hourly')
ans =
5×1 timetable
Time Var1
____________________ ____
03-May-2019 15:00:00 1
03-May-2019 16:00:00 2
03-May-2019 17:00:00 NaN
03-May-2019 18:00:00 4
03-May-2019 19:00:00 5
>> retime(tt,'hourly','spline')
ans =
5×1 timetable
Time Var1
____________________ ____
03-May-2019 15:00:00 1
03-May-2019 16:00:00 2
03-May-2019 17:00:00 3
03-May-2019 18:00:00 4
03-May-2019 19:00:00 5

Johannes Deelstra on 6 May 2019
Dear Peter, thanks very much for your answer and sorry for my late respons. The solution you propose is interesting, but can it be applied to my time series? I am to attach an excel file containing the data. In first column the data and in second column the recorded groundwater level .
There are several periods in this time series with missing data, often with a length of 24 hours (fe from 11.7.17 03:00 - 12.7.17 04:00, or 26.10.17 03:00 - 27.20.17 04:00). In total there are 6 periods with this many hours missing. But in some cases up to three days are missing (22.12.18 03:00 - 25.12.18 04:00). In total there are 3 periods with this many hours missing . I would like to have a complete timeseries but then for those periods with no data an empty cell, a NaN for groundwater recording. The question is now how do I find in MATLAB the missing hours and how do I automatically fill these in/add these.
After having a complete time series including periods with no data. After that I can do a data analysis on the data and cvompare with other recorded growundwater levels (having by the way the same problem with missing data.
Hope you have time to have a look at it a hopefully know how to solve the problem.
Regards
Johannes