missing data, time series

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

Answer 1

[Peter Perkins]

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]

thanks very much

used somwe time bugt in thne end I had discovered what you now write about

regards

Answer 2

[Peter Perkins]

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  

Answer 3

[Johannes Deelstra]

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