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

2 次查看（过去 30 天）
Ravindu Lokuliyana
Ravindu Lokuliyana 2018-11-20
评论： Ravindu Lokuliyana 2018-11-25
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 个）

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 个评论
显示 1更早的评论
Cris LaPierre
Cris LaPierre 2018-11-20
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;

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by