How to fill NaN's by averaging previous rows?

I have a table named T2.mat. This table has 12 columns. The last column sometimes has NaN values. I want to fill NaN in this column by the average of columns 9 and 10 in the same rows.

I attached my file.

Thanks in advance

Answer 1

[Adam Danz]

For matrix m

% Create a 10x12 matrix with NaNs in col 12
m = randi(100,10,12);
m(randi(10,1,7),12) = NaN; 

% Replace nans in col 12 with mean of col 9,10
m(isnan(m(:,12)),12) = mean(m(isnan(m(:,12)),[9,10]),2)

For table T

% Create a 10x12 table with NaNs in col 12
m = randi(100,10,12);
m(randi(10,1,7),12) = NaN; 
T = array2table(m); 

T.m12(isnan(T.m12)) = mean([T.m9(isnan(T.m12)), T.m10(isnan(T.m12))],2);

For your data, that will look like,

T2.tm_m(isnan(T2.tm_m)) = mean([T2.tmax_m(isnan(T2.tm_m)), T2.tmin_m(isnan(T2.tm_m))],2);

If T2.tmin_m or T2.tmax_m contain a NaN, the mean will be a NaN. If 1 of the values are NaN and other is not, you could return that single value using

T2.tm_m(isnan(T2.tm_m)) = mean(. . .,2,'omitnan');

[BN]

Dear Adam Danz, thank you for your answer. After I tried to use this I got an error.

clear all
clc
filename='Qaen.xlsx'
T = readtable(filename);
sort = sortrows(T, 8);
selected_table = sort (:, 8:9); 
dt1 = datetime([1982 01 01]);
dt2 = datetime([2018 12 31]);
allDates = (dt1 : calmonths(1) : dt2).';
allDates.Format = 'MM/dd/yyyy';
tempTable = table(allDates(~ismember(allDates,selected_table.data)), NaN(sum(~ismember(allDates,selected_table.data)),size(selected_table,2)-1),'VariableNames',selected_table.Properties.VariableNames);
T2 = outerjoin(sort,tempTable,'MergeKeys', 1);
m = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'})
m(isnan(m(:,12)),12) = mean(m(isnan(m(:,12)),[9,10]),2)

the error is:

Undefined function 'isnan' for input arguments of type 'table'. 

Do you know how to fix this?

Thanks

[Adam Danz]

I just updated my answer to show that same line of code implemented in a table.

Answer 2

[Image Analyst]

This will do it:

s = load('T2.mat')
T2 = s.T2;
% Determine where the last column has nans
lastCol = T2{:, end};
nanMap = isnan(lastCol)
% Get mean of columns 9 and 10.
meanCol9and10 = (T2{:, 9} + T2{:, 10}) / 2;
% Replace last column by average
lastCol(nanMap) = meanCol9and10(nanMap);
% Stick back into table.
T2{:, end} = lastCol

[Adam Danz]

Note that it's usually not a good idea to use numeric indexing with tables. One of the benefits of using tables is to not rely on the order of the columns. Using the column names (VariableNames) with dot notation is a safer and more readable approach.

[Image Analyst]

I agree -- using field names is better and safer, though fieldnames could also vary, just as column order could, so you need to be careful either way if you're going to be changing the form of a table.

[BN]

Oh yes you are right. it's safer. I appreciate your guidance and help.