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

6 views (last 30 days)
Ravindu Lokuliyana on 20 Nov 2018
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 on 20 Nov 2018
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 Comment

Ravindu Lokuliyana on 20 Nov 2018
It works perfectly fine.
Thank you very much.

Cris LaPierre on 20 Nov 2018
Edited: Cris LaPierre on 20 Nov 2018
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 = table2timetable(data);
sampleTime = [data.Date(1):minutes(10):data.Date(end)];
fixed = retime(data,sampleTime,'fillwithconstant','Constant',-100)

Ravindu Lokuliyana on 20 Nov 2018
Thank you very much for quick response.
Unfortunately, I'm working with Matlab 2015a package, which don't have these functions.
I'm still at my early stages on Matlab programming and I would greatly appreciate if you could provide any other method to solve this problem.
Thank you.
Cris LaPierre on 20 Nov 2018
This will run in 2015a. It still keeps the 10 minute spacing, but that's easy enough to convert to hourly if you want.
Date = datetime(num(:,1),'ConvertFrom','excel');
Date.Format = 'M/d/yyyy H:mm:ss';
Hs = num(:,2);
Ts = num(:,3);
data = table(Date,Hs,Ts);
data2 = table((Date(1):minutes(10):Date(end))','VariableNames',{'Date'});
newData = outerjoin(data2,data,'Keys',1,'MergeKeys',true);
newData{isnan(newData.Hs),2:3} = -100;
Ravindu Lokuliyana on 25 Nov 2018
Thank you very much.
It works really fine.