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

7 次查看（过去 30 天）
Tim Moriarty
Tim Moriarty 2016-1-6
编辑： Walter Roberson 2016-1-17
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????
  1 个评论
Peter Perkins
Peter Perkins 2016-1-6
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?).

请先登录，再进行评论。

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

采纳的回答

Peter Perkins
Peter Perkins 2016-1-16
编辑：Walter Roberson 2016-1-16
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.
  1 个评论
Peter Perkins
Peter Perkins 2016-1-17
编辑：Walter Roberson 2016-1-17
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);

请先登录，再进行评论。

更多回答（1 个）

Tim Moriarty
Tim Moriarty 2016-1-6
please see attached

类别

在 Help Center 和 File Exchange 中查找有关 Calendar 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by