How to filter excel dataset into specific time range and scan for missing values?

6 ビュー (過去 30 日間)
Hi there,
I got a large set of measured data in a excel file with 10 min intervals and some measurements are not recorded.
I need to filter them into 1 hour time intervals and then add some value (e.g. -1 or 100..etc) for missing time intervals.
Can anyone suggest me an appropriate method to solve this?
Hereby attached the sample excel file.
In this example, 9/25/2013 20:00:00 data measurements are not recorded. After filtering 1 hour interval I need to add some value to that specfic time, which I can use later to compare with another model results.
9/25/2013 19:00:00 1.61 10.9
9/25/2013 20:00:00 -100 -100
9/25/2013 21:00:00 1.70 11.5


Andrei Bobrov
Andrei Bobrov 2018 年 11 月 20 日
T = readtable('test.xlsx','ReadV',0,'Range','A3:C217');
a = datevec(T.Var1);
[ymd,ii] = unique(a(:,1:4),'rows','first');
Data = T{ii,2:3};
date1 = datetime([ymd, zeros(size(ymd,1),2)]);
new_Date = (datetime([ymd(1,:),0,0]):hours(1):datetime([ymd(end,:),0,0]))';
T1 = table(date1,Data(:,1),Data(:,2));
T2 = table(new_Date,'v',{'date1'});
T12 = outerjoin(T1,T2,'MergeKeys',true);
T12{isnan(T12.Var2),2:3} = -100;
  1 件のコメント
Ravindu Lokuliyana
Ravindu Lokuliyana 2018 年 11 月 20 日
It works perfectly fine.
Thank you very much.


その他の回答 (1 件)

Cris LaPierre
Cris LaPierre 2018 年 11 月 20 日
編集済み: Cris LaPierre 2018 年 11 月 20 日
I have a couple thoughts. First, do you know about timetables? Or are you familiar with the datetime data type? They can make this problem trivial.
Load your data and create a timetable. You can then retime the table, add missing values, etc. It looks like your sample time is 10 minutes. If I wanted to create an entry every 10 minutes and, if not present, assign a value of -100 to the column, I would do the following
ImportOptions = detectImportOptions('test.xlsx');
data = readtable('test.xlsx',ImportOptions)
data = table2timetable(data);
sampleTime = [data.Date(1):minutes(10):data.Date(end)];
fixed = retime(data,sampleTime,'fillwithconstant','Constant',-100)
If you read the spreadsheet in as a table using readtable, this page will explain options for cleaning the data.
  3 件のコメント
Ravindu Lokuliyana
Ravindu Lokuliyana 2018 年 11 月 25 日
Thank you very much.
It works really fine.



Help Center および File ExchangeData Import from MATLAB についてさらに検索

Community Treasure Hunt

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

Start Hunting!

Translated by