Remove more than two consecutive NaNs and delete the same rows from the date column in a table

Hello,

I am going through a bit complicated problem, as I have some in-situ data that needs to be cleaned, and one of the things I have to do is to delete more than two consecutive NaN values. I managed to do that by applying the code below.

The second problem that I faced is that to apply that code, I had to separate my data column from the table (contains date + data columns). And what I want is a way to include the date column, so when I delete the NaNs, the relative date rows should be gone too.

In the example below, I had to select the second column which represents the data, while the first column is the date.

Thank you

workData = readtable('station_2015.xlsx');
a = workData{:,2};
idx1 = isnan(a);
idx1 = idx1'
idr1 = diff(find([1 diff(idx1) 1]));
D1 = mat2cell(a,idr1,size(a',1));

for i = 1:length(D1)
    if any(isnan(D1{i})) && length(D1{i})>2
        D1{i} = [] ;
    end
end
iwant1 = cell2mat(D1)

Answer 1

[Voss]

% a table with 2 columns, containing some NaNs in the second column:
data = (1:20).';
data([2:3 7:9 13 15:19]) = NaN;
workData = table(rand(20,1),data,'VariableNames',{'Date','Data'});
disp(workData);

     Date      Data
    _______    ____

    0.36148      1 
    0.34608    NaN 
    0.43743    NaN 
    0.78196      4 
    0.90405      5 
    0.46612      6 
    0.96308    NaN 
    0.57548    NaN 
    0.76747    NaN 
    0.26333     10 
    0.89622     11 
    0.28484     12 
    0.40249    NaN 
    0.29187     14 
    0.43846    NaN 
    0.51367    NaN 
    0.37457    NaN 
    0.51091    NaN 
    0.86945    NaN 
    0.26653     20 

% use regexp to find sequences of consecutive NaNs of length at least 3:
[start_idx,end_idx] = regexp(char('0'+isnan(workData{:,2}).'),'1{3,}','start','end')

start_idx = 1×2

7 15

end_idx = 1×2

9 19

% get the indices of all elements within those length-3+ sequences of NaNs:
to_remove = arrayfun(@(s,e)colon(s,e),start_idx,end_idx,'UniformOutput',false);
to_remove = [to_remove{:}]

to_remove = 1×8

7 8 9 15 16 17 18 19

% remove the rows at those indices from the table:
workData(to_remove,:) = []

workData = 12×2 table
     Date      Data
    _______    ____

    0.36148      1 
    0.34608    NaN 
    0.43743    NaN 
    0.78196      4 
    0.90405      5 
    0.46612      6 
    0.26333     10 
    0.89622     11 
    0.28484     12 
    0.40249    NaN 
    0.29187     14 
    0.26653     20 

[YEMY]

It works perfectly, thank you.

I have a small question in regard of making a moving window that will take for example 10 rows at a time to see if there are any consecutive Nans and delete them. Is there a practical solution for that ?

[Voss]

You're welcome!

Yes, you can remove consecutive NaNs within a moving window, but removing them all at once is easier.

[YEMY]

Thank you. I will try to figure out how to make this moving window as it will help me more.