Time series data with missing dates to remap to calendar days vector?

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

Answer 1

[Peter Perkins]

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]

[Marton Erdei]

the ismember solution is excellent, thanks!

Answer 2

[KSSV]

t0 = 10 ; t1 = 5 ;
a = datenum({'01/01/2015 ';'01/14/2015'},'mm/dd/yyyy');
Out = datestr(datevec(a(1):a(2)),'mm/dd/yyyy') ;
t = zeros(size(Out,1),1) ;
t(1) = t0 ;
t(end) = t1 ;