How to manage NaN values and calculate mean under conditions

7 views (last 30 days)
Hi guys! I need your help on that.
In the attached file I have daily values of Temperature. What I want to calculate for every daily value is the Factor
Factor(i) = (Ti+Ti-1+Ti-2)/3
The problem starts when trying to "tidy up" NaN values:
If one of Ti,Ti-1,Ti-2 is NaN, then
Factor(i) = (Ti+Ti-1)/2 [assuming that Ti-2=NaN].
If two of Ti,Ti-1,Ti-2 are NaN, then
Factor(i) = Ti [assuming that Ti-2=NaN=Ti-1].
If all of Ti,Ti-1,Ti-2 are NaN, then
Factor(i) = 'NaN'
In the (most hopeful) case that none of the three are NaN, then
Factor(i) = (Ti+Ti-1+Ti-2)/3
Here is what I have done so far, but it doesnt work as expected
Daily_T = Imported_data.Tmean;
Daily_T = array2table(Daily_T);
[col] = height(Daily_T);
Factors = zeros(4018,2);
for i = 1:col
if (isnan(Daily_T{i,1}))
Factors(i,1) = (1/2)* (Daily_T{i-1,1}+Daily_T{i-2,1});
elseif (isnan(Daily_T{i-1,1}))
Factors(i,1) = (1/2)* (Daily_T{i,1}+Daily_T{i-2,1});
elseif (isnan(Daily_T{i-2,1}))
Factors(i,1) = (1/2)* (Daily_T{i,1}+Daily_T{i-1,1});
elseif (isnan(Daily_T{i,1})) && (isnan(Daily_T{i-1,1}))
Factors(i,1) = Daily_T{i-2,1}
elseif (isnan(Daily_T{i,1})) && (isnan(Daily_T{i-2,1}))
Factors(i,1) = Daily_T{i-1,1}
elseif (isnan(Daily_T{i-1,1})) && (isnan(Daily_T{i-2,1}))
Factors(i,1) = Daily_T{i,1}
elseif (isnan(Daily_T{i,1})) && (isnan(Daily_T{i-1,1})) && (isnan(Daily_T{i-2,1}))
Factors(i,1) = 'NaN';
else
Factors(i,1) = (1/3)* (Daily_T{i,1}+Daily_T{i-1,1}+Daily_T{i-2,1});
end
end
However, Factors are not built as it should... Can anyone point out where is the flaw of my code please?
PS. I'm on Matlab 2019a
  2 Comments
Daphne PARLIARI
Daphne PARLIARI on 21 Jul 2021
How can I use nanmean in (let's say)
Factors(i,1) = (1/2)* (Daily_T{i-1,1}+Daily_T{i-2,1}) ?
When I tried earlier, I got error.

Sign in to comment.

Accepted Answer

Simon Chan
Simon Chan on 21 Jul 2021
Edited: Simon Chan on 21 Jul 2021
Try the following:
summation = sum for each group of data (Each group has 3 data)
notnandata = count the number of data which are not NaN. So if the entire group of data contains only NaN, it will output zero. And if this value is zero, set it to NaN.
Noticed that Factor(1) calculate the mean value for rawdata.Tmean(1:3). If you don't like this pattern, you may need to adjust the indexing yourself.
rawdata=readtable('Daily data.xlsx');
idx = 1:size(rawdata.Tmean,1)-2;
summation = arrayfun(@(x) sum(rawdata.Tmean(x:x+2),'omitnan'),idx,'UniformOutput',false);
notnandata = cell2mat(arrayfun(@(x) sum(~isnan(rawdata.Tmean(x:x+2))),idx,'UniformOutput',false));
notnandata(notnandata==0)=NaN;
Factor = cell2mat(summation)./notnandata
  3 Comments
Daphne PARLIARI
Daphne PARLIARI on 22 Jul 2021
The modification for Factor30 works! Thanks!
But what is the difference between your approach and
idx = 33:size(Dailydata1.Tmean,1);
summation = arrayfun(@(x) sum(Dailydata1.Tmean(x-32:x-3),'omitnan'),idx,'UniformOutput',false);
notnandata = cell2mat(arrayfun(@(x) sum(~isnan(Dailydata1.Tmean(x-32:x-3))),idx,'UniformOutput',false));
notnandata(notnandata==0)=NaN;
Factor30 = cell2mat(summation)./notnandata;
that I tried myself? Besides the length, which in my case is 3986*1 and in yours 3989*1 .
In addition, I would use movmean as dpb suggested but I don't know how to make it work for Factor30.

Sign in to comment.

More Answers (2)

dpb
dpb on 21 Jul 2021
Edited: dpb on 21 Jul 2021
M=movmean(Daily,[0 2],'omitnan');
or, for the specific file
>> tDaily=readtable('Daily data.xlsx');
>> tDaily.MTmean=movmean(tDaily.Tmean,[0,2],'omitnan');
>> head(tDaily)
ans =
8×3 table
Daily_Date Tmean MTmean
___________ _____ ______
01-Jan-2006 9.33 11.68
02-Jan-2006 11.90 13.04
03-Jan-2006 13.80 12.76
04-Jan-2006 13.43 11.53
05-Jan-2006 11.05 9.92
06-Jan-2006 10.12 8.39
07-Jan-2006 8.59 7.52
08-Jan-2006 6.45 6.24
>>
  4 Comments
dpb
dpb on 22 Jul 2021
  1. "movmean(A,[kb kf]) computes the mean with a window of length kb+kf+1 that includes the element in the current position, kb elements backward, and kf elements forward."
  2. From 1. above, ergo [0 32] would be from 0 to 32 past point i, not before.(*)
Again, "read the documentation" combined with experimenting with a sample dataset short enough to be able to watch the results; simply using 1:10 so can easily verify what the results should be/how are calculated would be an ideal debugging tool. You don't need all 32 to test what the various combinations are how how to manipulate the series to get what you're shooting for.
(*) Of course, you could fliplr() the series, then do the averaging and fliplr() back, but why not just put the point offset in in the correct order to begin with? The offset would be needed to use movmean with both elements negative; TMW didn't think of that possibility and won't accept anything <0 as the second argument. There's no real reason it couldn't; just that they didn't think of it -- all that would be required is that the start index be less than the ending one.

Sign in to comment.


Peter Perkins
Peter Perkins on 27 Jul 2021
Another possibility that uses and preserves a timetable:
>> tt = readtimetable("Daily data.xlsx");
>> head(tt)
ans =
8×1 timetable
Daily_Date Tmean
___________ ______
01-Jan-2006 9.3282
02-Jan-2006 11.901
03-Jan-2006 13.802
04-Jan-2006 13.427
05-Jan-2006 11.052
06-Jan-2006 10.124
07-Jan-2006 8.5933
08-Jan-2006 6.4544
>> ttSm = smoothdata(tt,'movmean',days([0,2]),'omitnan');
>> head(ttSm)
ans =
8×1 timetable
Daily_Date Tmean
___________ ______
01-Jan-2006 11.677
02-Jan-2006 13.043
03-Jan-2006 12.76
04-Jan-2006 11.534
05-Jan-2006 9.9229
06-Jan-2006 8.3905
07-Jan-2006 7.5239
08-Jan-2006 6.2444

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!