Cleaning data from excel file

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

Answer 1

[Adam Danz]

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')

[Sonia Lenehan]

Thank you for your response.

I have tried the below code however I am not getting a warning saying index in position 2 exceeds the array and that their is an error in line 3. What am I doing wring?

[~, ~,dat] = xlsread('Filename.csv');
dat = [dat(:,2) dat(:,3) dat(:,9)];
dat(dat(:,9) == 1, [2,3]) = nan; 
dat(dat(:,2) == 0, (3)) = nan;
sum(dat(:,2),'omitnan')
mean(dat(:,3),'omitnan')    

[Adam Danz]

Which line is given you the warning (is best to supply the full copy-pasted error messages)?

Does dat have at least 9 columns?

[Sonia Lenehan]

I corrected for 3, as I realsied after I pull the columns from excel I only have 3 columns. Even after that I get the warning:

Undefined operator '==' for input arguments of type 'cell'.

Error in wmcleaning (line 3)

dat(dat(:,3) == 1, [1,2]) = nan;

This is the code I used this time

[~, ~,dat] = xlsread('Filename.csv');
dat = [dat(:,2) dat(:,3) dat(:,9)];
dat(dat(:,3) == 1, [1,2]) = nan; 
dat(dat(:,1) == 0, (2)) = nan;
sum(dat(:,1),'omitnan')
mean(dat(:,2),'omitnan')

[Adam Danz]

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]

Ah great! Thank you very much for all your help!