How to extract last non-zero element above current row in same column?

Hi everyone,

I'm dealing with a column of quarterly data (shares outstanding in an equities dataset) and need to fill in NaN or 0 elements with the last non-zero entry in a row above in the same column (provided the security is the same - I've got a loop that checks this condition first).

Loops quickly become inefficient for the missing data filling task if i need to check each prior row sequentially. The number of empty cells above is typically two, but it can be quite irregular...could be six, could be more. I don't think 'find' works for this particular task either, but I'm quite new to coding so could be wrong.

I'm sure there must be a very simple solution. Any suggestions?

Best,

Gerry

Answer 1

[Abdolkarim Mohammadi]

I think the best way is find as you mentioned.

for i1 = 1:numel(X)
    if any ([isnan(X(i1)); X(i1)==0])
        idx = find (X(1:i1-1),1,'last');
        X(i1) = X(idx);
    end
end

[Genaro Hiscock]

Hi Mar,

I was employing a similar logic with a loop and 'find', but couldn't work out how to index into the vector properly (i.e. your X(1:i1-1) part) to efficiently keep searching back up through the column row by row for a non-zero element. I was getting the following error at first due to the first value in the vector being 'NaN'.

"idx = 0×1 empty double column vector

Unable to perform assignment because the left and right sides have a different number of elements."

Once I put in a value for the first element it worked perfectly. Thanks for your time; much appreciated.

[Genaro Hiscock]

Apologies, that should have read 'Kar', not 'Mar'!

Answer 2

[Ameer Hamza]

Try fillmissing(): https://www.mathworks.com/help/releases/R2020a/matlab/ref/fillmissing.html

fillmissing(A, 'previous') % A is your matrix

[Genaro Hiscock]

Hi Ameer,

This is a fantastic solution just for filling missing data values; much faster than Mar's as it avoids the looping. For the particular issue I had asked about it will write in zeros though if there are zero elements in the row above and the row to be filled is NaN, rather than looking further up the column for non-zero elements.

However, it solves other strictly missing data/NaN issue problems (no zeros in the vectors) I tested loop-based solutions for on smaller data sets yesterday in an absolute fraction of the time. Therefore, it has tunred out to be an enormous help to me as well.

Thanks to you as well.