Cleaning data from excel file
4 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Sonia Lenehan
le 27 Fév 2020
Commenté : Sonia Lenehan
le 27 Fév 2020
Hi,
I need to clean data from an excel sheet which has is 11 x 78.
There are 3 columns that I am interested in 2, 3, and 9.
The data from column 2 is dependent on the data from column 9. If any of the cells in column 9 is equal to 1 then I need the corresponding cells in columns 2 and 3 to be deleted/left empty/marked as NaN.
Then if any of the cells left in column 2 are equal to zero I need the corresponing cells in column 3 to be deleted/left empty/marked as NaN. I then need the remaining cells in column 2 to be summed and the remaining cells in column 3 to be averaged.
Is this possible?
So far I have tried the below code to try and remove the unwated cells from column 2 but I am not having much look. I am reletaviely new to MATLAB and just cant seem to get this right.
[~, ~,dat] = xlsread('filename');
dat = [dat(:,2) dat(:,3) dat(:,9)];
if (dat(:,2) == 1 && dat(:,9) == 1)
dat(:,2) = [];
dat(:,9) = [];
end
0 commentaires
Réponse acceptée
Adam Danz
le 27 Fév 2020
If any of the cells in column 9 is equal to 1 then I need the corresponding cells in columns 2 and 3 to be deleted/left empty/marked as NaN.
dat(dat(:,9) == 1, [2,3]) = nan;
Then if any of the cells left in column 2 are equal to zero I need the corresponing cells in column 3 to be deleted/left empty/marked as NaN.
Apply the same logic as above.
I then need the remaining cells in column 2 to be summed and the remaining cells in column 3 to be averaged.
sum(dat(:,2),'omitnan')
mean(dat(:,3),'omitnan')
5 commentaires
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!