how to use sum and isnan across two different matrices

Hi all,

How does one use sum and isnan to compare values in rows located in different matrices?

sum(isnan(data(i,2:end)),matfor2(:,1))>=1

doesn't work- data and matfor are the two matrices. I'm simply trying to add, for each iteration, the row in the columns of matrix data (from the 2nd onwards), with the 1st (non-zero) column of matfor2. The point of that line is to truncate the two columns at the point where both have non NaN values.

if I did this:

%if sum(isnan(data(i,:)))>=1

I wouldn't get an error message. ALL the data would be truncated to where the series with the most amount of NaNs starts getting values. But I don't want that. I want the loop to extract the largest possible span of data for combinations of two series.

I'm doing this in a loop and what I will be doing in the loop (won't present it here for clarity) requires stuff done to combinations of two columns, one -the 1st column in data- that doesn't change, and the other that changes at each iteration. The issue is that each time series may have different starts and ends in terms of data availability. So the span of the data, for each combination of column 1 and column ii, varies. For that reason, I have to adjust the length of the data every time. The way I've approached this is as follows:

    [n,l] = size(data); %find length of data (raw, including NaN rows)
    
     B = cell(1,nCols-1); %preallocate a cell to store matrices of different row length
     matfor2=zeros(n,2);
     matfor2(:,1) = data(:,1);
     
    for ii= 2:nCols
       %loop to pair match-wise rows without adjacent NaNs (two columns at a
       %time)
       
         for i=1:n; 
        if sum(isnan(data(i,2:end)),matfor2(:,1))>=1
       i=i+1;
       else
           break
        
       end
         end
 B{ii}=matfor2(i:n,ii)
    end

[Rik]

This time I edited your question for you. Next time, please use the tools explained on this page to make your question more readable.

It isn't clear to me what you want to do. If you want to add a row and a column you need to decide what you want to happen. If you use +, Matlab will expand both to a matrix for you.

Can you give a small example about what exactly you would like that line to do?

[Jan]

Some hints:

• Do not use "l" (lower case L) as variable, because it is confused with 1 and I (uppercase i) too easily.
• A proper code indenation improves the readability of the code: ctrl-a ctrl-i
• Do not increase the counter of a FOR loop inside the loop:

    for i=1:n; 
       i = i+1;  % Nope, this is done by FOR already
    end    

• Explain, what the expression sum(isnan(data(i,2:end)),matfor2(:,1))>=1 should do. This is no valid Matlab code, so how can the readers guess, what you want to achieve? The 2nd input of sum() is the dimension to operate on, so matfor(:,1) is not likely to work. I do not understand this explanation:

"I'm simply trying to add, for each iteration, the row in the columns of matrix data (from the 2nd onwards), with the 1st (non-zero) column of matfor2. The point of that line is to truncate the two columns at the point where both have non NaN values."

[Fede C 2018 London]

Thank you.

Ok, when I wrote a few lines with a simpler problem (simpler in that I hadn't start tackling the issue of setting up the problem so I can work with combinations of column 1 and column whatever), and I had the following:

[n,l] = size(data); %find length of data (raw, including NaN rows)
for i=1:n; 
  if sum(isnan(data(i,:)))>=1
    i=i+1;
  else
    break
  end
end
B=data(i:n,:);

I would get in B my dataset, but now without all the rows for which in ANY of the columns there were NaN values. So the sum(isnan etc) line would look across all columns - if there was one istance of a row with NaN, it would return at least 1, more if NaN values appeared in more than one column, matlab would then look at the row below, repeat the operation, until it found a row in which there are no NaN value, at that point the new dataset would start.

Now, I'm attempting to find a way to do this for combinations of the 1st column in matrix data with all other columns-because what I will need to do next concerns the relationship of column 1 with all other columns, one by one. So my probably ham-fisted attempt at this was to take column 1, shove it in a new matrix called matfor2, and use sum(isnan etc) to compare it row by row with all other data (that's why I start ii at 2).

[Jan]

Again: to not increase i=i+1 inside a for loop, it i is the loop counter:

for i = 1:5
   disp(i)
end

This is working already.

A simplified version of your code:

n = size(data, 1); %find length of data (raw, including NaN rows)
k = 1;
for i = 1:n
   if ~any(isnan(data(i, :)))
      k = i;  % Do not rely on the loop counter outside the loop
      break
   end
end
B = data(k:n, :);

Without a loop:

k = find(~any(isnan(data), 2), 1);
B = data(k:n, :);

So far it is clear.

What does "combinations of the 1st column in matrix data with all other columns" mean? What is the "relationship of column 1 with all other columns"? I'm lost with "compare it row by row with all other data (that's why I start ii at 2)" also.

"Combination" and "relation" can mean a variety of things. Can you provide a small working example, which explains uniquely, what you need?

[Fede C 2018 London]

Hi Jan,

thank you for your suggestions-I'm at the toddler stage of matlab-speak.

Some context: my data is a lot of time series, the 1st icolumn s GDP, the other columns are a large number of other variables. So I will be filtering everything and getting business cycle fluctuations (the filter doesn't work on series with NaNs). Once the data is transformed that way, I will be taking correlations of leads and lags of GDP with all the other variables. I should have probably stated that more clearly from the outset. My talk of starting the loop at 2 was in reference to that- that whatever I do will always be analysing GDP and something else, so GDP is column 1. That is, in my question, I had the idea of creating nby2 matrices (n changing at each iteration) and storing these matrices of different row length in a cell.

[Jan]

Okay. I do not know, what a "GDP" is, but for Matlab they are all "numbers".

The line of code I've shown finds the first row, which does not contain an NaN:

k = find(~any(isnan(data), 2), 1);

How does matfor2(:,1) come into play now? This important detail is still not explained. With other words:

sum(isnan(data(i,2:end)), matfor2(:,1))
%                         ^^^^^^^^^^^^

What does this mean? SUM(A, B) is no defined function in Matlab and therefore I do not understand, what you want to achieve.

"That is, in my question, I had the idea of creating nby2 matrices (n changing at each iteration) and storing these matrices of different row length in a cell."

This is not a question. I have the impression, that a small addition solves your problem, maybe just an operator:

sum(isnan(data(i,2:end)) & isnan(matfor2(:,1))
%                        ^

(This does not work and it is pure guessing only...)

[Fede C 2018 London]

Let me be as concrete as possible. Let's consider only three time series (see spreadsheet attached). RGDP is my column 1, it has numbers starting in row 1 (Q4 1950). Column 2 is 'unemployment', NaN until you get to row 50 (Q1 1963), and column 3 is 'inflation', where the 1st row is NaN, with numbers starting at Q1 1951. Now, I know how to turn this dataset into one where there are no rows with NaN- it would start at Q1 1963 and it isn't hard to do. My problem is that if I want the correlation between column 1 and column 2 (RGDP and unemployment), then I need to discard the first 49 rows of both columns, but when I want to find the correlation between column 1 and column 3 (RGDP and inflation), then I want to retain as much information (data) as possible, so I just need to discard the 1st row because if you look at this pair of columns, there's only the first row of column 3 that has a NaN.

Because the correlation is always RGDP and something else (meaning, I don't care about the correlation of column 2 with column3), I thought, what if I create an empty matrix, fill it with my column 1 (RGDP) and then I loop over my matrix of all other data (matrix data) with the loop starting at 2 (so ignoring column 1) and I use isnan etc to compare each pair, truncate it on the basis of where there's numbers for both series, and store each pair somewhere (a cell?) so I can use the filter, take correlations, etc.

[dpb]

You're making it hard on yourself by overthinking the problem...MATLAB has already solved it for you... :)

R=corrcoef(X,'Rows','complete');

[Jan]

@Fede C 2018 London: The problem is getting less clear from comment to comment. Do not try to be as explicit as possible, but you need a much more abstract view on the data to convert the idea to code. I have no idea what "RGDP" is an Matlab does not know it also. See the data as numerical matrices and the meaning does not matter. Now explain, what you want to do with these numbers.

Compare:

1. I have an income of the person John Doe, living in Liverpool, born 1998, and a pile of money stolen from an elderly women, while she took in nap in the garden. When I put both amounts of money in a bag, how much do I have?
2. Variables: x, y. Get: x + y.

Let this inspire you :-) Break down your idea until they can be solved with variables and operators.

[Fede C 2018 London]

thank you all for your comments. I finally worked it out.