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

8 ビュー (過去 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.


Community Treasure Hunt

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

Start Hunting!

Translated by