Data with start and end date only converted to consecutive data for each hour between start and end date

I have Data in an excel file that shows the start and end date and there is an associated value with each of these events. I need to bring this data into matlab and show the associated values for each hour from the start and end date for each event. The start dates are all in one column in excel and the end dates are all in one column of excel. The event names are all in one column of excel and the values for each event are all in one column of excel.

Start Date:01/01/2015 End date: 01/15/2015 EventName: "x" Value:50

Start Date:01/02/2015 End date: 02/25/2015 EventName: "y" Value:500

What it needs to be in Matlab: This needs to be a table/matrix in matlab where there is every hour for a given period in one column ( have the code for this already) and then each event has a column with the value associated to it filled in to every hour from the start to the end date.

The event names need to be transposed across with unstack and at the same time the values need to be filled in consecutively for all dates between the start and end date. I am very new to matlab and any help would be greatly appreciated. I am thinking this will require a for loop of some type????

[Peter Perkins]

Tim, I think we need a short example of exactly what you are starting with (what does the spreadsheet look like?) and what you want to end up with (what does the table look like?).

Answer 1

[Peter Perkins]

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');
>> % need this if on linux/mac, or don't have Excel
>> % t.StartDate = datetime(t.StartDate,'ConvertFrom','excel');
>> % t.EndDate = datetime(t.EndDate,'ConvertFrom','excel');
>> 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.

[Peter Perkins]

Actually I realized that even under Windows with Excel installed, you still need this, following the call to readtable, to convert strings to datetimes:

>> t.StartDate = datetime(t.StartDate);
>> t.EndDate = datetime(t.EndDate);

Answer 2

[Tim Moriarty]

please see attached