Find and insert missing dates in a table with irregular timestamp

Question

Ymkje Lize Neuteboom 2019-11-22

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/492550-find-and-insert-missing-dates-in-a-table-with-irregular-timestamp

评论： dpb 2019-12-3

I have a data file with irregular dates, which looks like this:

DATUM        STA      ComNr      FIN  
    _________    _______    _____    _______
    5/21/2017    '00:00'     ''      '24:00'
    5/22/2017    '00:00'     ''      '24:00'
    5/23/2017    '00:00'     ''      '24:00'
    5/24/2017    '00:00'     ''      '24:00'
    5/25/2017    '00:00'     ''      '24:00'
    5/26/2017    '00:00'     ''      '24:00'
    5/27/2017    '00:00'     ''      '24:00'
    5/27/2017    '02:00'     ''      '01:00'
    5/28/2017    '00:00'     ''      '24:00'
    5/29/2017    '00:00'     ''      '24:00'
    5/30/2017    '00:00'     ''      '24:00'
    5/31/2017    '00:00'     ''      '24:00'
     6/1/2017    '00:00'     ''      '07:00'
     6/3/2017    '00:00'     ''      '24:00'
     6/4/2017    '00:00'     ''      '24:00'
     6/5/2017    '00:00'     ''      '24:00'
     6/6/2017    '00:00'     ''      '24:00'

I would like to find and insert the missing dates (in this case the 2nd of June), but since the dates are irregular the retime command does not work.

2 个评论
显示无隐藏无

dpb 2019-11-22

So, how does one know what to insert besides just a date?

Ymkje Lize Neuteboom 2019-11-22

I'm sorry for the incomplete question. It doesn't really matter, just the missing dates and zeros would be fine. The problem is that the rest of my code is based on continous data. I only found out when I put in the data from 15 years, hence the results won't really change.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Steven Lord 2019-11-22

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/492550-find-and-insert-missing-dates-in-a-table-with-irregular-timestamp#answer_402822

在 MATLAB Online 中打开

retime should work. Let's make a sample timetable with a few pieces of data.

dt = datetime(2017, [5; 5; 5; 6; 6; 6; 6], [29; 30; 31; 1; 3; 4; 5]);
tt = timetable(dt, (1:7).')

To make the time vector of the timetable tt a daily vector by filling the missing value in Var1 with NaN (which is the missing value for double precision data) use retime and specify 'daily' as the NEWTIMESTEP input.

tt2 = retime(tt, 'daily')

To interpolate the value of Var1 for June 2nd using linear interpolation:

tt3 = retime(tt, 'daily', 'linear')

The help text and documentation for retime list other methods for filling in the data for new rows.

If you've tried retime and it hasn't worked, can you show us a small example of your timetable and how you call retime?

4 个评论
显示 2更早的评论隐藏 2更早的评论

Ymkje Lize Neuteboom 2019-11-25

  16×3 timetable
     DATUM        STA      ComNr      FIN  
    ________    _______    _____    _______
    1/1/2004    '00:00'     ''      '04:00'
    1/1/2004    '04:00'     ''      '14:15'
    1/1/2004    '14:15'     ''      '24:00'
    1/3/2004    '00:00'     ''      '13:31'
    1/3/2004    '13:31'     ''      '21:23'
    1/3/2004    '21:23'     ''      '24:00'
    1/4/2004    '00:00'     ''      '24:00'
    1/5/2004    '00:00'     ''      '24:00'
    1/6/2004    '00:00'     ''      '02:37'
    1/6/2004    '02:37'     ''      '04:36'
    1/6/2004    '04:36'     ''      '24:00'
    1/7/2004    '00:00'     ''      '11:00'
    1/7/2004    '11:00'     ''      '13:02'
    1/7/2004    '13:02'     ''      '24:00'
    1/8/2004    '00:00'     ''      '24:00'
    1/9/2004    '00:00'     ''      '24:00'

Hi, thanks for your answer, however, if I use your approach, I get one row for every date. In my data, not all dates exist the same number of times. For example the piece of the timetable above, the second of January is missing, most of the other dates are present multiple times, exept for the 8th and 9th of January, which only appear one time.

I only want to insert the missing 2nd of January.

Ymkje Lize Neuteboom 2019-12-3

It took me some time, but it worked! Thank you!

dpb 2019-12-3

Glad you managed it...sorry didn't have time to try to code it or work on a more elegant route...

请先登录，再进行评论。

Find and insert missing dates in a table with irregular timestamp

2 个评论
显示无隐藏无

采纳的回答

4 个评论
显示 2更早的评论隐藏 2更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

Find and insert missing dates in a table with irregular timestamp

2 个评论 显示 无隐藏 无

采纳的回答

4 个评论 显示 2更早的评论隐藏 2更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

2 个评论
显示无隐藏无

4 个评论
显示 2更早的评论隐藏 2更早的评论