Cleaning data from excel file

11 views (last 30 days)
Sonia Lenehan
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

Accepted Answer

Adam Danz
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
Adam Danz
Adam Danz on 27 Feb 2020
Edited: Adam Danz on 27 Feb 2020
Are the elements of dat(:,3) all scalar values (not matrices or vectors, but single values)?
If so,
dat([dat{:,3}].' == 1, [1,2]) = {nan};
BTW, the messages you are receiving are errors, not warnings (unless you're using try/catch).
Sonia Lenehan
Sonia Lenehan on 27 Feb 2020
Ah great! Thank you very much for all your help!

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!