Counting all NaN and Sequence NaN in tables

Hey all,

I have x.mat which includes 71 tables. for all tables, I want to count the number of NaN cells in 9, 10, 11, and 12 columns (separately). Also, I want to know the maximum sequence NaN in each column.

at the end I would like something like this for each column:

... ... ...

here what I was done so far, but I am not sure about the accuracy:

tmax_i = 0;
tmin_i = 0;
rrr24_i = 0;
tm_m = 0;
for y = 1:height(x)
    
    if(x.tmax_m(y) = nan)
        tmax_i = tmax_i + 1;
    end
    
    if(x.tmin_m(y) = nan)
        tmin_i = tmin_i + 1;
    end
    
    if(x.rrr24(y) = nan)
        rrr24_i = rrr24_i + 1;
    end
    
    if(x.tm_m(y) = nan)
        tm_m = tm_m + 1;
    end
end

I don't sure about this. I don't know how to do this for every table in the x.mat and make a final output like what I said above.

Thank you in advance

[Allen]

I rarely work with tables so this may not be of great help since I do not know the correct syntax to use, but you may consider incorporating something similar to sum(isnan(TableX(:,[9,10,11,12]))) into this process and repeating it for every table (say in a for-loop). It should return a summation for each of the specified columns as a 1x4 vector. This should at least give you a total, but would still need additional steps to solve for a maximum sequence of NaN values.

Answer 1

[Guillaume]

tables are at their most useful if they're not split into multiple tables. Splitting a table into multiple ones often complicates the code. So first, let's merge all these tables:

stations = vertcat(x{:});

Your calculation can be done in just one line, using groupsummary. First, you'll need to create a m file for the calculation of the longest nan sequence as it can't be done with an anonymous function:

function maxlength = longestnanseq(v)
    %takes a COLUMN vector v and returns the length of the longest NAN sequence in the vector
    transitions = find(diff([false; isnan(v); false]));  %guaranteed to have an even number of elements
    maxlength = max(transitions(2:2:end) - transitions(1:2:end));
end

And then:

station_summary = groupsummary(stations, 'station_name', {@(var) nnz(isnan(var)), @longestnanseq}, 10:12);

Optionally, rename the variables in the new tables, as it's not obvious what fun1 and fun2 are:

station_summary.Properties.VariableNames(3:8) = compose(["NanCount_%s"; "LongestNanSeq_%s"], string(stations.Properties.VariableNames(10:12)));

[BN]

Dear Guillaume,

Thank you so much

Best regards