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

Question

Ravindu Lokuliyana 2018-11-20

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/430884-how-to-filter-excel-dataset-into-specific-time-range-and-scan-for-missing-values

评论： Ravindu Lokuliyana 2018-11-25

采纳的回答： Andrei Bobrov

test.xlsx

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Andrei Bobrov 2018-11-20

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/430884-how-to-filter-excel-dataset-into-specific-time-range-and-scan-for-missing-values#answer_348003

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 个评论
显示 -1更早的评论隐藏 -1更早的评论

Ravindu Lokuliyana 2018-11-20

It works perfectly fine.

Thank you very much.

请先登录，再进行评论。

Answer 2

Cris LaPierre 2018-11-20

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/430884-how-to-filter-excel-dataset-into-specific-time-range-and-scan-for-missing-values#answer_347929

编辑：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更早的评论隐藏 1更早的评论

Cris LaPierre 2018-11-20

在 MATLAB Online 中打开

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;