How to find NaN in a matrix and delete it?

Hello,

I've got a large matrix and for two columns I need to find the NaNs and to delete them. I need to delete them in the MarketCapY column and the DebtEquityY column.

Below, you find the code that is already written.

for i=1:28
    EquityY= equity (:,i);
    TotalDebtY= TotalDebt (:,i);
    MarketCapY= MarketCap (:,12*i+1:12*(i+1)+1);
    TotalReturnY = TotalReturn (:,12*i+1:12*(i+1)+1);
    DebtEquityY= TotalDebtY./EquityY;
    DataY= [DebtEquityY MarketCapY TotalReturnY];
    [row,col] = find(isnan(MarketCapY));
    out = DataY(any(~isnan(MarketCapY),2),:)
    [row, col] = find(isnan(DebtEquityY));
    out = DataY(any(~isnan(DebtEquityY),2),:);

You can see that the first two columns of DataY must be eliminated. I already tried something, but I don't know if it's correct.

[Image Analyst]

Can you attach your data (equity, TotalDebt, MarketCap, TotalReturn) in a .mat file so we can visualize what you need.

Keep in mind that a matrix must remain rectangular. If there are Nans scattered around the matrix, you cannot delete them. You can only

1. set the nan value to some other valid value
2. remove the whole row with the nan in it, or
3. remove the whole column with the nan in it.

Sounds like you're wanting option 3 but seeing the matrices would help.

[Alyssa Gailliaert]

The file is to big to attach. The purpose of my code is to determine whether or not a debt/equity ratio influences the height of stock returns of Belgian companies. So I still need the two columns to determine that. I tried this: a(isnan(a))=[] and I think it worked!

[Image Analyst]

That wouldn't work for a matrix, but would for a vector. If DebtEquityY, MarketCapY, and TotalReturnY are all vectors, and they all have nan's in the same position, that would work. If they have nan's in different locations then you'll have to use any() on DataY matrix, like Star Strider showed below. Maybe you could at least vote for the answers below or accept the best one to give them reputation points.

Answer 1

[Star Strider]

You have the logical negation ‘~’ in the incorrect position.

Example —

A = rand(4);
A(2,3) = NaN;
A(3,4) = NaN
Q1 = ~(any(isnan(A),1))
Q2 = ~(any(isnan(A),2))
Out1 = A(:,~any(isnan(A),1))                        % Select Columns
Out2 = A(~any(isnan(A),2),:)                        % Select Rows

You also need to be certain that you are selecting columns or rows.

[Alyssa Gailliaert]

Can you implement it with my example? I don't get it quite clear.

[Star Strider]

Without your data I cannot be certain.

Try this:

out = DataY(~any(isnan(MarketCapY),2),:)

and

out = DataY(~any(isnan(DebtEquityY),2),:);

However your Question referenced columns, although your code appears to indicate rows. You may need to change your code (the order of the ‘:’ and ‘~any()’ references) if you want to select columns.

For example, to select columns:

out = DataY(:,~any(isnan(MarketCapY),1));

and

out = DataY(:,~any(isnan(DebtEquityY),1));

I apologise for the delay — my ISP was down in a U.S. nation-wide fail for 14 hours on 27 December 2018. It only came back up a few minutes ago.

[Alyssa Gailliaert]

So this code would delete all the NaN in the columns MarketCapY and DebtEquityY? Or do I have to do something else to delete it?

[Alyssa Gailliaert]

The matrix looks like this

[DebtEquityY MarketCapY TotalreturnY]

DebtEquityY and MarketCapY are both one column with NaN

TotalReturnY contains 13 columns in the matrix

The purpose is that I have to get out the NaNs in DebtEquityY and MarketCapY in order to calculate the stock returns of my Belgian dataset.

[Star Strider]

I am slightly confused with respect to your data set, and what you want to do with it.

I assume this is your ‘DataY’ matrix:

DataY = [DebtEquityY MarketCapY TotalreturnY];

and ‘DataY’ is therefore an (Nx15) matrix, and you apparently want to delete the rows where either the first or second columns have NaN elements. If so, this will probably work:

out = DataY(~any(isnan(DataY(:,[1 2])),2),:)

Answer 2

[madhan ravi]

a=[1 2 3 NaN]; %an example
a(isnan(a))=[]