How to write a code that takes the mean of values of same dates of other years and replaces the missing hourly value (NaN) of a specific day of a year by the mean value?

I have a Matlab timetable named TT which contains GHI data from 01 Jan 2011 to 31 Dec 2014 as showing below. How to write a Matlab code which whenever see a missing or NaN hourly value, it finds the mean using the same values of other years and the missing value should be replaced by this mean value. For example here in the timetable showing below, for '01-May-2013 07:00:00' the "NaN" should be replaced by "326" as the mean of the corresponding date values from the other 3 year is 349+317+312 / 3 = 326. Any help to resolve this issue is highly appreciated. With thanks

'01-May-2011 05:00:00' 5

'01-May-2011 06:00:00' 91

'01-May-2011 07:00:00' 349

'01-May-2011 08:00:00' 631

.

.

.

'01-May-2012 05:00:00' 5

'01-May-2012 06:00:00' 78

'01-May-2012 07:00:00' 317

'01-May-2012 08:00:00' 629

.

.

.

'01-May-2013 05:00:00' 5

'01-May-2013 06:00:00' 81

'01-May-2013 07:00:00' NaN

'01-May-2013 08:00:00' 562

.

.

.

'01-May-2014 05:00:00' 1

'01-May-2014 06:00:00' 70

'01-May-2014 07:00:00' 312

'01-May-2014 08:00:00' 589

[Luca Ferro]

can you share the time table?

[Muhammad Umair Khan]

Sure, Please see attached timetable.

Answer 1

[Luca Ferro]

This works;

function tt=nanfiller(tt)

nanPos= find(ismissing(tt)==1); %finds position of NaN elements

nanTime=tt.Time(nanPos); %gets time parameter for said elements

for jj=1:height(nanPos)

fillPos= find(month(tt.Time)==month(nanTime(jj))& day(tt.Time)==day(nanTime(jj))& hour(tt.Time)==hour(nanTime(jj))==1); %finds the position of the non-NaN time table elements of previous/folowing years (including Nan)

fillVal=tt.solargd1(fillPos);%gets the value of said elements

meanVal=fillVal(~isnan(fillVal)); %strips the NaN element

tt.solargd1(nanPos(jj))=round(mean(meanVal)); %set the NaN value to the mean of the others

end

end

if you have any doubt just ask.

Note1: i decided to round the mean, it's up to you to keep it that way or change it.

Note2: in the remote case there are no values to to calculate the mean, it will fill with NaN. I tested it and it does not happen in this specific time table, but it may happen in others.

[Muhammad Umair Khan]

It is working beautifully. Really appreciate it. Huge thanks!!