MATLAB Answers

Finding the percentage of NaN cells in columns

6 views (last 30 days)
I have an excel file. I want to know how much (%) of my data are NaN.
I want to found that for columns 9 to 12 separately, and have the results something like this:
I attached my excel file. this is just a sample and I have more than 125 excel files which I gonna do this for all. any help is appreciated.
thank you all

  0 Comments

Sign in to comment.

Accepted Answer

Star Strider
Star Strider on 15 Jan 2020
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.

  2 Comments

Behzad Navidi
Behzad Navidi on 15 Jan 2020
Dear Star Strider
Really thank you, You identified what I want. I don't know why after running for loop part of your code successfully, the second part gives me an error:
Error using cell2table (line 57)
'File name' is not a valid variable name.
I have tried some edits but unfortunately, I was unable to fix the error.
Thank you again.
Star Strider
Star Strider on 15 Jan 2020
Dear Behzad Navidi —
My pleasure!
R2019b allows spaces and other characters in table variable names.
This should work in every release:
filnam = cell2table(fn,'VariableNames',{'File_name'}); % File Names Table
It was two separate words in the example you posted, so I assumed your version of MATLAB supported it.
My apologies.

Sign in to comment.

More Answers (2)

Jakob B. Nielsen
Jakob B. Nielsen on 15 Jan 2020
Lets say you have a vector A. isnan(A) gives you a vector of same dimension of A, with 0 on all indexes with a value, and 1 in all indexes with NaN. Therefore,
sum(isnan(A));
gives you the amount of NaN entries. And
(sum(isnan(A))/numel(A))*100;
gives the percentage.
Now, for doing it "easily" with excel, here is what you do. Put all excel files in the same folder, and then implement this. (This is a present from me to you, please look it through and understand it if you want to learn how to use it for other stuff :) )
[Name,Path]=uigetfile({'*.xls*'},'MultiSelect','on');
entirefile =fullfile(Path,Name);
filecount=size(Name,2);
% Now do a for loop to read all the selected files.
for i=1:filecount
Excel = actxserver('Excel.Application');
Excel.visible = false;
Excel.DisplayAlerts = false;
Excel.EnableSound = false;
Excel.Workbooks.Open(entirefile);
Workbook = Excel.ActiveWorkbook;
sheetIndex=1;
Worksheets.Item(sheetIndex).Activate;
Datainfo = get(Excel.ActiveSheet, 'Range', 'I2', 'L361'); %this is the range in the example file you attached... See comment below***
Data(j).yourdata=cell2mat(Datainfo.value);
Excel.Quit;
Excel.delete;
clear Excel;
end
You will end up with a data structure where, if you have 125 files, the Data structure will contain 125 entries of 360x4.
Data(1).yourdata will display all data from the first excel file, corresponding to Name{1}, and so on.
Then, you simply run the isnan function on all your data. You can even do that inside the above loop if you want.
*** IF your various excel files are not all of the same dimension, it gets a little hairy. I refer you to this post, which gives a code for finding the next empty row. Save the script as a sub function, and then call it this way:
lastrow = GoToNextRowInColumn(Excel,'A'); %DONT call it with column I or some such, because if your very last entry is a NaN you will skip it!
%%% and then change your Datainfo line above to this:
endrange=strcat('L',num2str(lastrow-1));
Datainfo = get(Excel.ActiveSheet, 'Range', 'I2', endrange);

  1 Comment

Behzad Navidi
Behzad Navidi on 15 Jan 2020
Dear Jakob B. Nielsen
I really appreciate your time and your answer. I was read it carefully and learned useful points. Thank you

Sign in to comment.


Adam Danz
Adam Danz on 15 Jan 2020
Edited: Adam Danz on 15 Jan 2020
Using the C.mat file from your previous questions, here's a simple way to produce the table you're describing.
load('C.mat');
% Identify columns to analyze
col = [9 10 11 12];
% Compute percent-nan for each table & chosen column
nanPercent = cell2mat(cellfun(@(x)mean(isnan(x{:,col}),1),C,'UniformOutput',false)');
% If you want those values to be percentages rather than decimals (ie, 10.5 instead of 0.105)
% nanPercent = nanPercent * 100;
% Get the station_name from each table
stationNames = cellfun(@(x)unique(x.station_name(~ismissing(x.station_name))),C)';
% Summarize results in table
varNames = C{1}.Properties.VariableNames(col); % Get var names from first table
T = [table(stationNames), array2table(nanPercent,'VariableNames',varNames)];
Result
head(T) %show first few rows of table
stationNames tmax_m tmin_m rrr24 tm_m
__________________ ________ ________ ________ ________
{'Abadan' } 0.098684 0.13377 0.14254 0.12719
{'Abadeh' } 0.10965 0.12939 0.125 0.19079
{'Abali' } 0.14474 0.14474 0.17544 0.17982
{'Abumusa Island'} 0.16886 0.17105 0.13816 0.19079
{'Ahar' } 0.23246 0.22368 0.23465 0.26096
{'Ahvaz' } 0.059211 0.065789 0.065789 0.063596
{'Aligudarz' } 0.2193 0.24561 0.25219 0.25877
{'Anar' } 0.24342 0.21053 0.21491 0.25658
Note that percentages in this answer and Star Strider's answer differ only because we're using different inputs (apparently the C.mat files we're using are not the same file). When I test his code using my C.mat file, the outputs are the same.

  0 Comments

Sign in to comment.

Sign in to answer this question.

Tags