MATLAB: Eliminate tables with more than 15% NaN data

MATLABnantable

Hey all,

I have C.mat which has 125 tables. I want to remove tables if any of tmax_m or tmin_m or rrr24 or tmin_m columns have more than 15% NaN cells.

C.mat is attached.

Thank you all

Best Answer

This cellfun is a bit obnoxious but it produces an output select that is a logical vector the same size as C where true values identify cells that will be eliminated due to having at least 1 column listed in cols that has more than maxNaN percent NaN values.

load('C.mat')
cols ={'tmax_m', 'in_m' 'rrr24' 'tmin_m'};
maxNan = 0.15; 
select = cellfun(@(T)any(mean(isnan(T{:,ismember(T.Properties.VariableNames,cols)}),1) > maxNan), C); 
C(select) = []; % eliminate selected cells

Related Solutions

MATLAB: Finding the percentage of NaN cells in columns

This does everything for all the tables in ‘C’:

D = load('C.mat');
C = D.C;
for k = 1:numel(C)
    C{k}(:,1:4) = fillmissing(C{k}(:,1:4),'nearest');                           % Fills Missing Data
    filename = C{k}{1,2};                                                       % Input Table Name
    fn{k,:} = sprintf('%s.xlsx',filename{:});                                   % Output File Name
    writetable(C{k},sprintf('%s.xlsx',filename{:}))                             % Write Each Table To Separate File
    rowlen{k,:} = size(C{k}(:,9:12),1);                                         % Row Lengtth Of Each Table
    NaN912{k,:} = [varfun(@isnan,C{k}(:,9:12))];                                % Number Of ‘NaN’ Values In Each Table
    PctNaN(k,:) = table2array(varfun(@(x)sum(x)./size(x,1),NaN912{k,:}));       % Percent ‘NaN’ Values In Selected Variables (Columns)
end
VarNms = compose('NAN in %s', string(C{1}.Properties.VariableNames(:,9:12)));   % Variable Names For ‘NaNPercent’ Table
filnam = cell2table(fn,'VariableNames',{'File name'});                          % File Names Table
NaNPercent = array2table(PctNaN, 'VariableNames',VarNms);                       % ‘PctNaN’ Initial Table
NaNPercent = [filnam NaNPercent];                                               % ‘PctNaN’ Final (Output) Table
FirstTen = NaNPercent(1:10,:)                                                   % Display Sample (Delete)

Producing:

FirstTen =
  10×5 table
           File name           NAN in tmax_m    NAN in tmin_m    NAN in rrr24    NAN in tm_m
    _______________________    _____________    _____________    ____________    ___________
    {'Abadan.xlsx'        }      0.088235           0.1299          0.13971         0.1152  
    {'Abadeh.xlsx'        }       0.10539          0.13235           0.1299        0.19118  
    {'Abali.xlsx'         }       0.12162          0.12162          0.15315        0.15766  
    {'Abumusa Island.xlsx'}        0.1464          0.14865          0.11486        0.16892  
    {'Ahar.xlsx'          }       0.21171           0.2027          0.21396        0.24099  
    {'Ahvaz.xlsx'         }      0.051471         0.058824         0.058824       0.056373  
    {'Aligudarz.xlsx'     }        0.1982          0.22523          0.23198        0.23874  
    {'Anar.xlsx'          }       0.22297          0.18919          0.19369        0.23649  
    {'Arak.xlsx'          }      0.068627         0.078431         0.088235       0.093137  
    {'Ardakan (Yazd).xlsx'}       0.21114          0.20882          0.34339         0.2065

The code retains the original data in ‘rowlen’ and ‘NaN912’ as well as the percent NaN results in the ‘NaNPercent’ table.

MATLAB: I want fill NaN in one column using average of two other column but I got an error.

The comparison operator for equality is == not =, which is exclusively for assignment.

However, you need to be aware that a NaN is never equal to anything, or greater or smaller than anything meaning that:

NaN >= NaN
NaN <= NaN
NaN == NaN

are always false.

The proper way to check for NaN is with isnan.

Overall, I'd write your code as this:

for cidx = 1:numel(C)
    if ismember('tm_m', C{cidx}.Properties.VariableNames)
        toreplace = isnan(C{cidx}.tm_m);
        C{cidx}.tm_m(toreplace) = mean(C{cidx}{toreplace, {'tmax_m', tmin_m}});
    end
end

Assuming I've undestood your code correctly. In your snippet C appears to be first a cell array of tables, then a structure.

Best Answer

Related Solutions

MATLAB: Finding the percentage of NaN cells in columns

MATLAB: I want fill NaN in one column using average of two other column but I got an error.

Related Question