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

2 views (last 30 days)
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

Accepted Answer

Andrei Bobrov
Andrei Bobrov on 20 Nov 2018
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;

More Answers (1)

Cris LaPierre
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 = 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 Comments
Cris LaPierre
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.
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;

Sign in to comment.

Categories

Find more on Data Import from MATLAB in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!