MATLAB: Time series data with missing dates to remap to calendar days vector

missing datestime series

Hi,
I have the following time series data:
01/01/2015 10
01/14/2015 5
05/30/2015 100
[…] […]
I would like to convert the above to the following:
01/01/2015 10
01/02/2015 0
01/03/2015 0
[…] 0
01/14/2015 5
[…] 0
05/30/2015 100
Is there any obvious way to do this?
Thanks much,
Martin

Best Answer

If you have R2014b or later, use tables and datetimes. For the specific question you've asked, simplest to use ismember and indexing:
>> Date = datetime({'01/01/2015';'01/14/2015';'05/30/2015'});
>> Value = [10; 5; 10];
>> TSomeDays = table(Date,Value)
TSomeDays =
       Date        Value
    ___________    _____
    01-Jan-2015    10   
    14-Jan-2015     5   
    30-May-2015    10   
>> Date = datetime(2015,1,(1:180)');
>> Value = zeros(size(Date));
>> TAllDays = table(Date,Value);
>> [~,i] = ismember(TSomeDays.Date,TAllDays.Date)
i =
     1
    14
   150
>> TAllDays.Value(i) = TSomeDays.Value
TAllDays =
       Date        Value
    ___________    _____
    01-Jan-2015    10   
    02-Jan-2015     0   
[snip]
    13-Jan-2015     0   
    14-Jan-2015     5   
    15-Jan-2015     0   
[snip]
    29-May-2015     0   
    30-May-2015    10   
    31-May-2015     0   
[snip]
>> Date = datetime(2015,1,(1:365)');
>> TAllDays = table(Date);
Another possibility is to use an outer join, which may be too heavy of a hammer for this specific question, but which provides capabilities that you may yet need. outerjoin fills missing values with NaN, but you can put zeros in their place:
>> TAllDays = outerjoin(TAllDays,TSomeDays,'Key','Date','MergeKeys',true)
TAllDays =
  365×2 table
       Date        Value
    ___________    _____
    01-Jan-2015     10  
    02-Jan-2015    NaN  
    03-Jan-2015    NaN  
[snip]
    13-Jan-2015    NaN  
    14-Jan-2015      5  
    15-Jan-2015    NaN  
[snip]
    29-May-2015    NaN  
    30-May-2015     10  
    31-May-2015    NaN  
[snip]
>> TAllDays.Value(isnan(TAllDays.Value)) = 0
TAllDays =
       Date        Value
    ___________    _____
    01-Jan-2015    10   
    02-Jan-2015     0   
[snip]
    13-Jan-2015     0   
    14-Jan-2015     5   
    15-Jan-2015     0   
[snip]
    29-May-2015     0   
    30-May-2015    10   
    31-May-2015     0   
[snip]
If you have R2016b, this is trivial using a timetable:
>> Date = datetime(2015,1,(1:365)');
>> TTAllDays = retime(TTSomeDays,Date,'FillWithConstant','Constant',0)
TTAllDays =
        Date       Value
    ___________    _____
    01-Jan-2015    10   
    02-Jan-2015     0   
[snip]
    13-Jan-2015     0   
    14-Jan-2015     5   
    15-Jan-2015     0   
[snip]
    29-May-2015     0   
    30-May-2015    10   
    31-May-2015     0   
[snip]
Related Question