How do I delete cells in a column based on information from another column?

Hi,

I am working on data that I have extracted from an excel sheet. I have extracted the three columns that are necessary but need to do further work until I can use the data. Column three tells me if the data is valid hence I need to write the code that if any cells in column three equals 1 the correspoding cells in column 1 and 2 are equal to nan or 0. I then need that if column 1 equals to zero the corresponding cells in cloumn 2 equals to nan or 0. I then need to sum column 1 and average column 2. Below is the code I have tried. It runs but the output it gives is the sum and average of the columns before the if statements.

X = xlsread('filename.csv');
x = [X(:,2) X(:,3) X(:,9)];
if (x(:,3) == 1), x(:,1)= 0;
end
if (x(:,3) == 1),  x(:,2)= 0;
end
if (x(:,1) == 0), x(:,2)= 0;
end
sum(x(:,1))
mean(x(:,2))

[dpb]

"...the output it gives is the sum and average of the columns before the if statements."

Nope. The output is after the if constructs and looks just fine. The problem will be your input data didn't meet your expectations if would appear.

Couple possibilities...

1. X data are floating point and the values are close to, but not precisely 1 so the test for equality fails. This probably isn't the case here, but is common enough as to mention it and it is at least possible from the information we have available to us
2. There are other data in X that are NaN besides those that your filter has cleaned up so the result of the calculation is still NaN (which, while you don't say what the result returned was, the crystal ball is back from the shop (again!) and it says that's the symptom.

Try

sum(isnan(x))

after the above and report back...

[Sonia Lenehan]

Thank you for your reply.

Apologies for not stating the output

The output returned is ans = 5

ans = 9.1426e+05

But it should be ans = 3 and ans = 589333 as I have calculated it manually.

From the excel sheet I pulled cloumns 2, 3 and 9. 2 and 9 are coded 1 and 0 and 3 is time in mirco seconds.

When I added that line of code I got back

ans =

     5


ans =

   9.1426e+05


ans =

     0     0     0

[dpb]

I guess will have to send the crystal ball back out for repair...it seems to still be murky! :)

That said, I'm pretty confident that you've not uncovered a bug in the ML sum() function so I'll assert the answer in ML is 5 and 9E5.

We'd have to see the input file to see what it is precisely that caused the differences.

I'll try the crystal ball one more time before declaring it broken (yet again)--

Does the input file have a header row, perchance? The difference between 3 and 5 is 2 and two headerlines would be a very common occurrence in a spreadsheet. There being some other value in one of those two lines that is also numeric in the second column is also certainly quite possible.

Answer 1

[Guillaume]

Considering the format of your spreadsheet you would be better off importing the data as a table. If I understood correctly what you're trying to achieve, this would do it:

testresults = readtable('WM TEST.xlsx');  %import data as table. Matlab automatically detect the column headers and use that to name the table variables

isgoodtest = testresults.Correct & ~testresults.FailedTrial;  %a good test is one with correct result and not failed
meangoodsaccade = mean(testresults.SaccadeTimeToChooseCurtain(isgoodtest);  %mean of saccade time for these tests that are correct and not failed
countgoodtest = nnz(isgoodtest);

As you can see, there is no need to remove anything, so if you want to compute some other statistics the source data is still there, and it's much easier to understand what the code is doing and the code is simpler as well. Good variable names help with code clarity.

[Sonia Lenehan]

Thank you so much. This works perfectly!!

Answer 2

[Pujitha Narra]

Hi,

According to the code above, the condition for the ‘if’ would return a column vector, but the ‘if’ requires the entire vetor to contain logical true values. This is causing the unexpected results.

Instead use the following code instead of the ‘if’ statements:

x(find(x(:,3)==1),1) =0; 
x(find(x(:,3)==1),2) =0; 
x(find(x(:,3)==0),2) =0; 

[Sonia Lenehan]

Thank you so much! This worked perfectly.

I have just noticed though that my mean code means the whole column including the zeros. When I run the above code I get that column 1 = 3 which is the answer I was looking for. That means that there are 3 correct times in column 2 and they are the 3 that I need averaged. Column 1 is a correct/not correct column and column two is the amount of time it took to choose the correct/non-correct option. I am interested in teh amount of time it took to choose the correct option. Is there anyway to write code so that column 2 only averages the correct times?

Thank you so much!

mean(x(:,2))

[Guillaume]

Note that the find are completely unnecessary. The whole lot can be simplified to

x(x(:, 3) == 1, [1, 2]) = 0;
x(x(:, 1) == 0, 2) = 0;

"Is there anyway to write code so that column 2 only averages the correct times"

Whatever it is you're trying to do, it will be possible to do it. Most likely, it can be done in just one line in a much more efficient way that what you have now (with no need to replace any value). For us to figure it out, we would need an example file and a better explanation of what the data represent.

Also, what version of matlab are you using that you're still stuck with the deprecated xlsread?

[Stephen23]

"...but the ‘if’ checks only the first element."

Not according to MATLAB'S if documentation, which states "An expression is true when its result is nonempty and contains only nonzero elements (logical or real numeric). Otherwise, the expression is false". It does not mention "first element" anywhere, and makes it clear that if takes into account all elements of the condition expression.

This is easy to confirm too:

>> if [1,0]; disp('only checks first element'); else disp('checks all elements'); end
checks all elements

Note that find is totally superfluous and should be removed: logical indexing is simpler and more efficient.

[Pujitha Narra]

I agree with Guillaume and Stephen. 'if' tries to check if the entire vector has logical true values in this case, not just the first element (as wrongly mentioned previously)

[Sonia Lenehan]

Thank you, I have updated the code to remove the find. I have attached a sample excel sheet of the data I am using. The participants have to pick a side based on information and I am looking at how many times each participant looks at the correct side and how long it takes them on average. Hence the columns I need are correct, saccade time to chosen curtain and failed trials which I have been able to extract. I then need to look at the failed trials and remove them. I then need to look at the correct trials and remove the incorrect ones. I then want to average the times of the correct trials.

I am using R2018a.