Extract data from dataset with no nans and loop over columns

Hi Matlab community,

I have run into a problem in my code and would very much appreciate your help.

I have three matrices of size 1825*52 with annual data. I want to extract the months January to june from the dataset when none of these three matrices have nan values and save it in three new matrices. I am able to make it work for one column, but then the code fails to loop for 52 columns. Rows are annual data from 2015 to 2019(no 31st dec data, so it has 1825 values) and columns are 52 different simulations.

Here is my code:

obs=readmatrix('cms.xlsx', 'Range','B367:BA2191');
sim1=readmatrix('avg.csv', 'Range','B367:BA2191');
sim2=readmatrix('scaled.csv','Range','B367:BA2191');
%Read only january to June with non nans for all years and all columns
startdate='2015-01-01';
enddate='2019-12-30';
t1=datetime(startdate,'InputFormat','yyyy-MM-dd');
t2=datetime(enddate,'InputFormat','yyyy-MM-dd');
time=t1:t2;
time=time';
    m=0;
    for n=1:52
     for i=1:length(obs) %2015 to 2019 there is no 31st dec 2019 data so 1 value less
         if month(time(i))>=1 && month(time(i))<=6 && isnan(obs(i,n))==0 && isnan(sim1(i,n))==0 && isnan(sim2(i,n))==0  
             m=m+1;
         obsnew(m,n)=obs(m,n); 
         sim1_new(m,n)=sim1(m,n)
         sim2_new(m,n)=sim2(m,n);
      end
      end
     %n=n+1;
     end 

Thanks in advance for your suggestions and help!

Answer 1

[Voss]

I think, at least, these three lines:

obsnew(m,n)=obs(m,n); 
sim1_new(m,n)=sim1(m,n);
sim2_new(m,n)=sim2(m,n);

should be modified to this:

obsnew(m,n)=obs(i,n); 
sim1_new(m,n)=sim1(i,n);
sim2_new(m,n)=sim2(i,n);

since i is the row index you're checking and m is the row index of the good values only.

I'm not sure if this change is sufficient to make it work.

[J A]

Thanks for your comment. I did change these lines; that did fix some indexing issues but it is still not looping over columns correctly.

The first column in the matrice is 906 rows long, then 2nd column starts from 907 to 1812, then 3rd column goes from1813 to 2718 and so on until 52 columns.

Not sure where in the code it is going wrong yet.

[Voss]

Try moving m=0; into the outer loop, like this:

for n=1:52
    m=0;
    for i=1:length(obs)
        % your existing code here remains the same
    end
end

This will reset the m counter for each column. See if that gives you what you have in mind.

[J A]

Great, this worked! Thanks a lot. This saved a lot of my time having to do it manually.

[Voss]

Awesome! Do you mind marking my answer as Accepted (so that the question won't show up as unanswered anymore - and so that I get credit)? Thanks

[J A]

Sure, I did it. Thanks