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

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

Answer 1

[Andrei Bobrov]

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;

[Ravindu Lokuliyana]

It works perfectly fine.

Thank you very much.

Answer 2

[Cris LaPierre]

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.

[Ravindu Lokuliyana]

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]

This will run in 2015a. It still keeps the 10 minute spacing, but that's easy enough to convert to hourly if you want.

num = xlsread('test.xlsx','','','basic');

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]

Thank you very much.

It works really fine.