how to Interpolate hourly data

Question

Osnofa 2019-3-28

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/453113-how-to-interpolate-hourly-data

评论： Osnofa 2019-4-2

test.txt

Hello everyone,

I have a large hourly data set (~15 years of data) for several measuring points. for the purpose I'll add an example data file with 4 locations.

the format is [year month day hour data1 data2 data3 data4]

Missing records are registered as NaN.

I need to interpolate if the amount of missing records is less or equal to 3 (for each day). If the amount of missing records is greater than 3 (for each day) then the day remains untouched.

What would be the best way to do this? I'm stuck on this, probably is not that hard but I'm not a regular matlab user and therefore that might be the reason I'm struggling to find a way to do this.

thank you for the attention.

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

请先登录，再进行评论。

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

Answer 1

Akira Agata 2019-4-1

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/453113-how-to-interpolate-hourly-data#answer_368430

在 MATLAB Online 中打开

Hmm, interesting problem.

I think the following is one possible straight-forward solution.

% Read data file
D = dlmread('test.txt');
Time = datetime(D(:,1),D(:,2),D(:,3)) + hours(D(:,4));
% Convert to timetable
T = [table(Time),...
  array2table(D(:,5:end),'VariableNames',{'Data1','Data2','Data3','Data4'})];
TT = table2timetable(T);
% Num of NaN for each day, each column
TT2 = retime(TT,'daily',@(x) nnz(isnan(x)));
% Apply linear interpolation if num of NaN is (0,3] per day.
for nDay = 1:height(TT2)
  for nVar = 1:4
    if TT2{nDay,nVar} > 0 && TT2{nDay,nVar} <= 3
      idx = isbetween(TT.Time,TT2.Time(nDay),TT2.Time(nDay)+days(1));
      TTseg = fillmissing(TT(idx,nVar),'linear');
      TT(idx,nVar) = TTseg;
    end
  end
end

3 个评论
显示 1更早的评论隐藏 1更早的评论

Osnofa 2019-4-1

Yes, the timestamp has that format. I'll take a look to your solution later in the night.

thanks in advance.

Osnofa 2019-4-2

It works fine, thanks for the help!

请先登录，再进行评论。

Answer 2

Andrei Bobrov 2019-4-1

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/453113-how-to-interpolate-hourly-data#answer_368452

编辑：Andrei Bobrov 2019-4-1

T = readtable('test.txt','ReadVariableNames',false,...
                           'Format','%d %d %d %f %f %f %f %f');
time1 = datetime(T{:,1:3}) + hours(T{:,4});
TT = table2timetable(T(:,5:end),'RowTime',time1);
TT.Properties.VariableNames = sprintfc('data%d',1:4);
TT_out = varfun(@fun,TT);
function out = fun(x)
    bw = isnan(x);
    N = accumarray(bwlabel(bw)+1,1);
    N = N(2:end);
    lo = ismember(bw,find(N > 3));
    x(lo) = 0;
    out = fillmissing(x,'linear');
    out(lo) = nan;
end