Cleaning data from excel file
11 views (last 30 days)
Show older comments
Sonia Lenehan
on 27 Feb 2020
Commented: Sonia Lenehan
on 27 Feb 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 Comments
Accepted Answer
Adam Danz
on 27 Feb 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 Comments
More Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!