Tim, there are bunch of ways to do this. Here's two versions using tables, datetime, cell array "curly brace expansion", and rowfun.
Read the data into a table.
>> t = readtable('matlab question data.xlsx','Range','A1:F6');
>>
>>
>>
>> t
Warning: Variable names were modified to make them valid MATLAB identifiers.
t =
StartDate EndDate Event Value Source Type
____________________ ____________________ _____ _____ ______ ____
14-Jul-2009 00:00:00 12-Jan-2010 00:00:00 'x' 30 'SA' 'XX'
11-Sep-2009 00:00:00 27-Feb-2010 00:00:00 'y' 150 'SB' 'XX'
12-Sep-2009 00:00:00 14-Jan-2012 00:00:00 'z' 45 'SC' 'XX'
09-Oct-2009 00:00:00 14-Jan-2012 00:00:00 'a' 250 'SD' 'YY'
10-Oct-2009 00:00:00 14-Jan-2012 00:00:00 'b' 900 'SE' 'YY'
For each row in the original table, create a table of dates and values. Save those in a cell array (each cell containing a table), then join the first two tables together.
>> c = rowfun(@fun1,t,'OutputFormat','cell');
>> tt = outerjoin(c{1},c{2},'Key','Date','MergeKeys',true);
>> tt(1:5,:)
ans =
Date x y
____________________ __ ___
14-Jul-2009 00:00:00 30 NaN
14-Jul-2009 01:00:00 30 NaN
14-Jul-2009 02:00:00 30 NaN
14-Jul-2009 03:00:00 30 NaN
14-Jul-2009 04:00:00 30 NaN
>> tt(end-4:end,:)
ans =
Date x y
____________________ ___ ___
26-Feb-2010 20:00:00 NaN 150
26-Feb-2010 21:00:00 NaN 150
26-Feb-2010 22:00:00 NaN 150
26-Feb-2010 23:00:00 NaN 150
27-Feb-2010 00:00:00 NaN 150
Here's the function that rowfun calls:
function t = fun1(startDate,endDate,event,value,~,~)
d = (startDate:hours(1):endDate)';
v = repmat(value,size(d));
t = table(d,v,'VariableNames',[{'Date'} event]);
That gets tedious for more than two tables. Another approach:
Create the full list of dates/times.
>> dates = ( min(t.StartDate):hours(1):max(t.EndDate) )';
For each row in the original table, broadcast the value out to a vector the same size as the full list of dates/times values. Save those in a cell array (each cell containing a vector), then combine the vectors into one table.
>> fun2Wrapper = @(startDate,endDate,event,value,~,~) fun2(dates,startDate,endDate,event,value);
>> c = rowfun(fun2Wrapper,t,'OutputFormat','cell');
>> tt = table(dates,c{:},'VariableNames',[{'Date'} t.Event']);
>> tt(1:5,:)
ans =
Date x y z a b
____________________ __ ___ ___ ___ ___
14-Jul-2009 00:00:00 30 NaN NaN NaN NaN
14-Jul-2009 01:00:00 30 NaN NaN NaN NaN
14-Jul-2009 02:00:00 30 NaN NaN NaN NaN
14-Jul-2009 03:00:00 30 NaN NaN NaN NaN
14-Jul-2009 04:00:00 30 NaN NaN NaN NaN
>> tt(end-4:end,:)
ans =
Date x y z a b
____________________ ___ ___ __ ___ ___
13-Jan-2012 20:00:00 NaN NaN 45 250 900
13-Jan-2012 21:00:00 NaN NaN 45 250 900
13-Jan-2012 22:00:00 NaN NaN 45 250 900
13-Jan-2012 23:00:00 NaN NaN 45 250 900
14-Jan-2012 00:00:00 NaN NaN 45 250 900
And here's the function that rowfun calls in this version:
function v = fun2(dates,startDate,endDate,event,value)
i = find(dates==startDate):find(dates==endDate);
v = NaN(size(dates));
v(i) = value;
Couldn't tell what Source and Type in the first table were supposed to be used for, I ignored them.
Hope this helps.
Best Answer