How to remove all NaN and Inf values when calculate the mean?

I just computed a 167*50 double matrix (Log_ret) mean, with a lot of NaN and +-Inf. I've seen a solution from another question, which is really good. But when I got the extractedData, all the data turn into one column, which means that I got a 6798*1 double variable. So, how can I keep the reult still in their previous column without moving in to one column. (I mean numbers in column 2 after removing all NaN and Inf still in the column2 and column 33 after removing all NaN and Inf still in the column 33 etc.)

Thanks in advance!

mask = (Log_ret ~= 0) & isfinite(Log_ret)
extractedData = Log_ret(mask)

[the cyclist]

To take a smaller example, suppose Log_ret is

Log_ret = [ 2  3 Inf;
           NaN 5  7]

What do want the output extractedData to be? Be specific. There is no such thing as an "empty" slot in a numeric array.

[Amy]

I want each column mean to repalce the NaN and Inf value. So, that's why I want to put each column result separately. And here is what I want with your example.

Log_ret=[2 3 7;
         2 5 7]
%not
Log_ret=[2;
         3;
         5;
         7]

[Star Strider]

Perhaps this —

Log_ret = [ 2  3 Inf;
           NaN 5  7]

Log_ret = 2×3

2 3 Inf NaN 5 7

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

Log_ret(~isfinite(Log_ret)) = NaN

Log_ret = 2×3

2 3 NaN NaN 5 7

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

Log_ret = fillmissing(Log_ret, 'nearest')

Log_ret = 2×3

2 3 7 2 5 7

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

Mean_Log_Ret = mean(Log_ret)

Mean_Log_Ret = 1×3

2 4 7

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

.

[Amy]

Thanks for your answer. But if there is a long column of data, it would affect the mean right?

Answer 1

[Matt J]

If you're saying you want to take the column-wise mean, ignoring zeros and non-finite values, then you could do,

exclude =  (Log_ret == 0) | ~isfinite(Log_ret)  ;

extractedData=Log_ret;

extractedData(exclude)=nan;

columnMeans = mean(extractedData,1,'omitnan'); %the result

In other words, you shouldn't approach it by trying to remove NaNs and Infs. You should approach it by converting all values you want to exclude to NaNs and using the omitnan flag, where appropriate.

[Les Beckham]

I'm wondering why you are excluding the zero elements. Doing that will change the mean values. I don't see anything in OP's question about excluding zeros, just +/- Inf and NaN.

[Matt J]

In the OP's code, we see zeros are excluded,

(Log_ret ~= 0) 

[Amy]

Thanks, that's a really good point. I forget to say that my purpose in calculating the column mean is to use the mean of each column to repalce the NaN and Inf values in ecah column. Then the later calculation won't be affact.

[Image Analyst]

You might also consider using interpolation with interp1. Just identify the non inf and nan locations and then interpolate the whole thing to replace them with the interpolated values from either side of the stretch of nans and infs.

[Matt J]

Thanks, that's a really good point. I forget to say that my purpose in calculating the column mean is to use the mean of each column to repalce the NaN and Inf values in ecah column. Then the later calculation won't be affact.

@Amy But is your original question answered? If so, please Accept-click the answer.

Answer 2

[Image Analyst]

@Amy You might try interpolating the values, for example:

% Create data with good values and inf and nan values.
v = [10, nan, 30, 40, nan, nan, inf, nan, nan, 90];
% Replace nans and infs with interpolated values.
badIndexes = isnan(v) | isinf(v) % Find bad value locations

badIndexes = 1x10 logical array
   0   1   0   0   1   1   1   1   1   0

y = v(~badIndexes) % Extract only the good values.

y = 1×4

10 30 40 90

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

xFull = 1 : length(v) % Interpolate over the whole vector.

xFull = 1×10

1 2 3 4 5 6 7 8 9 10

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

vInterpolated = interp1(find(~badIndexes), y, xFull); % Do the interpolation

vInterpolated =

Columns 1 through 4

10 20 30 40

Columns 5 through 8

48.3333333333333 56.6666666666667 65 73.3333333333333

Columns 9 through 10

81.6666666666667 90