How to create daily average of different consecutive years?

Hi, I have a question. I am having a dataset made of 6 consecutive years (Hourly Data). I was able to calculate weekly/daily/annual averages of each year but when it's plotted, it is hard to read. I want to create daily averages that is the average of the day in all 6 years. For instance:

Year Value

Day 1 (2000) 1

Day 1 (2001) 0

Day 1 (2002) 1

Day 1 (2003) 1

Day 1 (2004) 0

Day 1 (2005) 1

The average of day 1 for all 6 years is 0.66

[Data is exactly ordered the same in my dataset file.]

I have turned my imported excel file into time table.

Answer 1

[Adam Danz]

Assuming your timestamps are in datetime format, use day() with 'dayofyear' flag to convert the timestamps to day-of-year.

Then use findgroups() to group the data by day-of-year. Then use splitapply() to calculate the means.

% Fake timestamp and data, both must be column vectors! 
d = datetime('01/01/1950','InputFormat','dd/MM/yyyy') + days(0:3000)';
data = rand(size(d)); 

doy = day(d,'dayofyear'); 
[dayGroups, dayKey] = findgroups(doy); 
dayMeans = splitapply(@(x)mean(x,'omitnan'),data,dayGroups); 
T = table(dayKey,dayMeans,'VariableNames',{'DayOfYear','DayMeans'}); 

[Adam Danz]

" apparently the code is providing random data. How can I replace the random data with my data?"

In your question, you have 2 columns of data, "Year","value". Replace my fake random data with those data.

" all missing data is replaced with 0 instead of NaN "

Probably not a good idea. First, what if the sensor actually returns a value of 0? Then you never know which 0s are real data and which are fillers. Second, when you perform statistics on that data, the 0s will affect the results greatly. Use NaNs instead.

" how can I have only 1 week and put all the data as averages on this 1 week? ...so the graph would be like (monday-sunday with the averages of all the data of the days (Day1 to day7)?"

You'll use almost exactly the same solution I shared except instead of day() you'll use weekday().

Regarding the code you shared,

doy = day(MissingData_0_Daily_Mean,'dayofyear');
%         ------------------------  is this a vector of dates or data? It 
%                                   should be your DATES

Lastly, please avoid attaching screenshots of code. I can't do anything with that. Sharing code is helpful but I need to be able to copy-paste it. Also, when you share code, format it using the [>] button in the "code" section of the editor where you type your comment. It will appear as the 3 lines of code above.

If you are still having the error, please provide the date and data inputs (or at least 20 or so rows of the data).

[Guillaume]

I agree with everything Adam said,

If you are on R2018a or later, the above can even be achieved in just one line with groupsummary:

result = groupsummary(yourtableortimetable, 'RowTimes', 'dayofyear', 'mean')

[Adam Danz]

Thanks, Guillaume. I keep forgetting about that function.