How to fill NaN's by averaging previous rows?
4 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
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
0 comentarios
Respuesta aceptada
Adam Danz
el 20 de Dic. de 2019
Editada: Adam Danz
el 20 de Dic. de 2019
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');
2 comentarios
Adam Danz
el 20 de Dic. de 2019
I just updated my answer to show that same line of code implemented in a table.
Más respuestas (1)
Image Analyst
el 20 de Dic. de 2019
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
3 comentarios
Image Analyst
el 20 de Dic. de 2019
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.
Ver también
Categorías
Más información sobre Logical en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!