Finding the percentage of NaN cells in columns

7 次查看（过去 30 天）
BN
BN 2020-1-15
评论： Star Strider 2020-1-15
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

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

采纳的回答

Star Strider
Star Strider 2020-1-15
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 个评论
BN
BN 2020-1-15
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 2020-1-15
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.

请先登录，再进行评论。

更多回答（2 个）

Jakob B. Nielsen
Jakob B. Nielsen 2020-1-15
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);
https://se.mathworks.com/matlabcentral/answers/285463-how-find-last-row-in-excel#answer_223169
  1 个评论
BN
BN 2020-1-15
Dear Jakob B. Nielsen
I really appreciate your time and your answer. I was read it carefully and learned useful points. Thank you

请先登录，再进行评论。

Adam Danz
Adam Danz 2020-1-15
编辑：Adam Danz 2020-1-15
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.

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by