Omit nans from data set when calculating weights

Hello!

I currently have data from 5 rain gages and am trying to calulated weighted averages for each to better represent how much rain fell in certain areas. Some of the rows have NaNs in them so when I calulate the weighted average across the five gages the product = NaN. I would like to ignore the NaNs in the calculation but cannot figure out how to do this. I have tried the 'omitnan' but maybe I am not putting it in the right place? Thank you in advance!!

%Doing all precipitation
Precip1 = table2timetable(P1);
Precip2 = table2timetable(P2);
Precip3 = table2timetable(P3);
Precip4 = table2timetable(P4);
Precip5 = table2timetable(P5);
AllPrecip=synchronize(Precip1, Precip2,Precip3,Precip4,Precip5);

Weight_HQ=[0.003365661397
0.1528054559
0.3846818121
0.2268942917
0.2322527789];

Weight_N1B=[0
0
0.01912681913
0.9064449064
0.07442827443]

Weight_N2B=[0
0.8899179677
0.1100820323
0
0]

Weight_N4D=[0
0.03316776158
0.8987341772
0.06809806121
0]

Weight_N20B=[0
0
0.001316655695
0.6356155365
0.3630678078]

AllPrecip.HQ_Precip=Weight_HQ(1)*AllPrecip.ppt_Precip1+Weight_HQ(2)*AllPrecip.ppt_Precip2+Weight_HQ(3)*AllPrecip.ppt_Precip3+Weight_HQ(4)*AllPrecip.ppt_Precip4+Weight_HQ(5)*AllPrecip.ppt_Precip5;
AllPrecip.N1B_Precip=Weight_N1B(1)*AllPrecip.ppt_Precip1+Weight_N1B(2)*AllPrecip.ppt_Precip2+Weight_N1B(3)*AllPrecip.ppt_Precip3+Weight_N1B(4)*AllPrecip.ppt_Precip4+Weight_N1B(5)*AllPrecip.ppt_Precip5;
AllPrecip.N2B_Precip=Weight_N2B(1)*AllPrecip.ppt_Precip1+Weight_N2B(2)*AllPrecip.ppt_Precip2+Weight_N2B(3)*AllPrecip.ppt_Precip3+Weight_N2B(4)*AllPrecip.ppt_Precip4+Weight_N2B(5)*AllPrecip.ppt_Precip5;
AllPrecip.N4D_Precip=Weight_N4D(1)*AllPrecip.ppt_Precip1+Weight_N4D(2)*AllPrecip.ppt_Precip2+Weight_N4D(3)*AllPrecip.ppt_Precip3+Weight_N4D(4)*AllPrecip.ppt_Precip4+Weight_N4D(5)*AllPrecip.ppt_Precip5;
AllPrecip.N20B_Precip=Weight_N20B(1)*AllPrecip.ppt_Precip1+Weight_N20B(2)*AllPrecip.ppt_Precip2+Weight_N20B(3)*AllPrecip.ppt_Precip3+Weight_N20B(4)*AllPrecip.ppt_Precip4+Weight_N20B(5)*AllPrecip.ppt_Precip5;

Answer 1

[Voss]

You can use fillmissing to replace any NaN with 0, so that it doesn't contribute to the weighted sum.

Give the resulting timetable a different name if you want to preserve your original timetable.

T = fillmissing(AllPrecip,'constant',0);
AllPrecip.HQ_Precip = Weight_HQ(1)*T.ppt_Precip1+Weight_HQ(2)*T.ppt_Precip2+Weight_HQ(3)*T.ppt_Precip3+Weight_HQ(4)*T.ppt_Precip4+Weight_HQ(5)*T.ppt_Precip5;
% etc. for the other calculated columns

[Voss]

Note that replacing NaNs with zeros is fine for a weighted sum, which is what you are calculating:

x = [1 NaN 2 NaN 3];
w = [1 2 3 4 5];

x = fillmissing(x,'constant',0)

x = 1x5

1 0 2 0 3

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

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

sum(x.*w) % 1*1 + 2*3 + 3*5 = 22

ans = 22

[Callie]

Thank you so much!

[Voss]

You're welcome!

Answer 2

[Star Strider]

Since NaN values are considered to be ‘missing’, to remove them from a specific vector (or matrix), you can use the rmmissing function.

[Callie]

Thank you for your answer! If I do this, it deletes the whole row. I am trying to keep the row but just ignore the NaN in the calulation. Thoughts on this?

[Star Strider]

My pleasure!

Without your data, it is not easy to suggest a specific alternative. Probably the best option is to iterate through each row and use @(x)~isnan(x).

Perhaps something like this —

AllPrecipEdited = structfun(@(x)x(~isnan(x)), AllPrecip);

The problem with setting the NaN values to a speciffic value is that zeros (or whatever finite value you set them to) will be counted in calculating the mean or other statistics. If you simply eliminate the NaN values, they will not be considered in those calculations.

.

[Callie]

Great! I have a large data set but am going to try this method as well. Thank you!

[Star Strider]

My pleasure!

Note —

x = randi(9, 1, 5);
x([2 4]) = NaN

x = 1x5

2 NaN 9 NaN 4

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

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

w = 1:numel(x)

w = 1x5

1 2 3 4 5

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

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

x = fillmissing(x, 'Constant',0)

x = 1x5

2 0 9 0 4

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

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

mean_xw = mean(x.*w)                            % Counts Zeros As Vector Elements

mean_xw = 9.8000

x([2 4]) = NaN

x = 1x5

2 NaN 9 NaN 4

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

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

correct_mean_xw =  mean(x.*w,'omitnan')         % Omits 'NaN' Values

correct_mean_xw = 16.3333

EDIT — (11 Apr 2024 at 22:08)

Added illustration.

.