timetable for average value

Question

karim bio gassi 2020-5-18

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/526402-timetable-for-average-value

编辑： Adam Danz 2020-5-26

ausdata.xlsx

Hi everyone

I want to create a timetable for my data imported from excel as numerical matrix, the data has the date and time column. I try by creating the datetime in excel by concatenation, But I get an error. below is the code,

dateSamples = ausdata(:,1); 
dateSamples = x2mdate(dateSamples); 
timeSamples = datestr(ausdata(:,2),'HH:MM:SS')%

Here I don't how to do build the datetime.

datetimeSample =
load = ausdata(:,9)
TT = timetable(datetimeSamples,load);
daily= retime(TT,'daily','mean');
weekly = retime(TT, 'monthly', 'mean')

Please assist me....I attached the excel file

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Adam Danz 2020-5-18

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/526402-timetable-for-average-value#answer_433183

编辑：Adam Danz 2020-5-18

x2mdate() converts from excel serial date number to Matlab serial date number. An optional 3rd input allows you to convert the excel date to Matlab's datetime format.

MATLABDate = x2mdate(dateSamples, 0,'datetime')

---[Update]---

Use readtable() to read in the excel table.

opts = spreadsheetImportOptions('NumVariables',9, ...
    'VariableTypes', {'char','char','datetime','double','double','double','double','double','double'},...
    'VariableNamesRange', 'A1', 'DataRange', 'A2'); 
T = readtable('ausdata.xlsx', opts);
% Look at first few rows
head(T)
  
%  8×9 table
%          Date              Hour        Var3    DryBulb    DewPnt    WetBulb    Humidity    ElecPrice    SYSLoad
%     _______________    ____________    ____    _______    ______    _______    ________    _________    _______
%     {'01-Jan-2006'}    {'0.020833'}    NaT      23.9      21.65       22.4       87.5        19.67      8013.3 
%     {'01-Jan-2006'}    {'0.041667'}    NaT      23.9       21.7       22.4         88        18.56      7726.9 
%     {'01-Jan-2006'}    {'0.0625'  }    NaT      23.8      21.65      22.35         88        19.09      7372.9 
%     {'01-Jan-2006'}    {'0.083333'}    NaT      23.7       21.6       22.3         88         17.4      7071.8 
%     {'01-Jan-2006'}    {'0.10417' }    NaT      23.7       21.6       22.3         88           17      6865.4 
%     {'01-Jan-2006'}    {'0.125'   }    NaT      23.7       21.6       22.3         88           17      6685.9 
%     {'01-Jan-2006'}    {'0.14583' }    NaT      23.6      21.65       22.3         89           17      6548.6 
%     {'01-Jan-2006'}    {'0.16667' }    NaT      23.5       21.7       22.3         90        16.92      6487.8 

The NaT values in column 3 in my example may be due to a difference in the version of Excel I'm currently using.

If you're also getting NaT values in column 3 you can recreate the datetime values using this,

T.Properties.VariableNames{3} = 'datetime'; 
T.datetime = dateshift(T.Date,'start','day') + T.Hour;
% head(T)
%        Date          Hour             datetime          DryBulb    DewPnt    WetBulb    Humidity    ElecPrice    SYSLoad
%     ___________    _________    ____________________    _______    ______    _______    ________    _________    _______
%     01-Jan-2006     1800 sec    01-Jan-2006 00:30:00     23.9      21.65       22.4       87.5        19.67      8013.3 
%     01-Jan-2006     3600 sec    01-Jan-2006 01:00:00     23.9       21.7       22.4         88        18.56      7726.9 
%     01-Jan-2006     5400 sec    01-Jan-2006 01:30:00     23.8      21.65      22.35         88        19.09      7372.9 
%     01-Jan-2006     7200 sec    01-Jan-2006 01:59:59     23.7       21.6       22.3         88         17.4      7071.8 
%     01-Jan-2006     9000 sec    01-Jan-2006 02:30:00     23.7       21.6       22.3         88           17      6865.4 
%     01-Jan-2006    10800 sec    01-Jan-2006 03:00:00     23.7       21.6       22.3         88           17      6685.9 
%     01-Jan-2006    12600 sec    01-Jan-2006 03:29:59     23.6      21.65       22.3         89           17      6548.6 
%     01-Jan-2006    14400 sec    01-Jan-2006 03:59:59     23.5       21.7       22.3         90        16.92      6487.8 