Average values from timeseries

Hiiii. I am very new to matlab. I have a timeseries of data that I would like to average for average values per hour per day per month per year ( 24*365=8760 values in output file). Basically the data is structured as follows:

Date Time Temperature

1/1/2005 1:00 282.19

1/1/2005 2:00 281.5

1/1/2005 3:00 281.04

1/1/2005 4:00 282.58

....

31/12/2014 23:00 294.75

31/12/2014 24:00 294.57

The data I am trying to import as individual vectors/columns or as a timetable. I would like to get a new table/matrix with 8760 new rows each averaging value of each hour of each day of each month by averaging all the 14 years of data. I have tried doing it using index or 'find' approach but it didnt quite work. Any help would be highly appreciated. Thaaanks :D

[Adam Danz]

Read your data in as a timetable if you haven't already done so.

Then use TT2 = retime(TT,'hourly','mean') to compute the hourly mean, monthly mean, yearly, etc.

https://www.mathworks.com/help/matlab/ref/timetable.retime.html#bvdhiwr-newTimeStep

Answer 1

[Adam Danz]

Below is a general scetch of what you need to do. Give it a shot and if you get stuck, share the code and let us know where you're stuck.

1. Use d = day(t,'dayofyear') to get the day-of-year number for each datatime value where t is the datetime column.
2. Use h = hour(t) to get the hour of each datetime value where t is the datetime column.
3. Use [G,ID] = findgroups(d,h) to group the days & hours (d and h are from the steps above)
4. Use Y = splitapply(@mean,data,G) to get the mean of each group of data where 'data' is a colum in your table. G is from the previous step.

[Adam Danz]

Why are those values bizzarre? They look appropriate to me (without having seen your data). I can't see what's going on without having your data. Could you attach a mat file with all needed variables so I can run your code?

You can inspect the grouping by looking at the rows of this table.

T = table(Day(:), Hour(:), G(:), 'VariableNames', {'DayOfYear','Hour','Group'})

[Adam Danz]

Some feedback:

1) Just FYI; you can save several variables to the same mat file using :

save('data.mat', 'Date','Temp','Time')

2) You need to add a 3rd output to findgroup() so you can get the group ID for the day and the hour column (see code below).

Run this code and then inspect the table.

load('Date.mat')
load('Temp.mat')
load('Time.mat')

Day = day(Date,'dayofyear');
Hour = hour(UTtime);
[G,dayID,hourID]=findgroups(Day,Hour);  % Added 3rd output
Y = splitapply(@mean,Temperature,G);

% View the results in a table
T = table(Y, dayID, hourID, 'VariableNames', {'Means','DayOfYear','Hour'});
T(1:50,:)

Here's a way to confirm the results. In the first two lines below select a day-of-the-year and an hour.

The 3rd line manually computes the mean and the 4th line looks up the value in the table. They should (and do) match.

% Select day of year and hour
selectDayOfYear = 150; 
selectHour = 12; 

% manually compute the mean
testValue = mean(Temperature(Day == selectDayOfYear & Hour == selectHour))
% look up the mean in the table
T(T.DayOfYear == selectDayOfYear & T.Hour == selectHour, :)

We can see they match

>> testValue =
       310.97
>> T(T.DayOfYear == selectDayOfYear & T.Hour == selectHour, :) =
  1×3 table
    Means     DayOfYear    Hour
    ______    _________    ____
    310.97       150        12 

If your temperature data contain NaN or missing data you can change the mean function to

  Y = splitapply(@(v)mean(v,'omitnan'),Temperature,G);  

[Kate Heisenberg]

Thanks alot. works perfect for me now:)

[Adam Danz]

Glad I could help!

Answer 2

[Jancoba Dorley]

The best wway to do this is to convert the table to timetable

Example:

x = table2timetable(data); %Note that data is a table containing time xby1 datetime and xby1 double

x_daily=retime(x,'daily','mean'); %this calculates the daily mean from the datetime table

% you can do the saame to get the hourly mean.