Finding the percentage of NaN cells in columns
20 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
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 Commenti
Risposta accettata
Star Strider
il 15 Gen 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 Commenti
Star Strider
il 15 Gen 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.
Più risposte (2)
Jakob B. Nielsen
il 15 Gen 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);
Adam Danz
il 15 Gen 2020
Modificato: Adam Danz
il 15 Gen 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 Commenti
Vedere anche
Categorie
Scopri di più su Spreadsheets in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!