Need every hour obs data averaged for every 6 hours.

2 views (last 30 days)
Amanda Viken
Amanda Viken on 10 Oct 2021
Commented: Amanda Viken on 11 Oct 2021
I have streamflow observation data for 3 sites from the year 2015 to present. All three either have one hour, 30 minutes, or 15 minutes observations or a mixtures of all three. Assuming it would be a similar process for various time steps, I will show you some of my 1 hour data below for one day.
obs =
204999x6 double
1 1 2015 0 0 3.49
1 1 2015 1 0 3.49
1 1 2015 2 0 3.49
1 1 2015 3 0 3.49
1 1 2015 4 0 3.48
1 1 2015 5 0 3.48
1 1 2015 6 0 3.48
1 1 2015 7 0 3.47
1 1 2015 8 0 3.47
1 1 2015 9 0 3.47
1 1 2015 10 0 3.47
1 1 2015 11 0 3.47
1 1 2015 12 0 3.46
1 1 2015 13 0 3.46
1 1 2015 14 0 3.45
1 1 2015 15 0 3.45
1 1 2015 16 0 3.44
1 1 2015 17 0 3.44
1 1 2015 18 0 3.43
1 1 2015 19 0 3.43
1 1 2015 20 0 3.42
1 1 2015 21 0 3.41
1 1 2015 22 0 3.4
1 1 2015 23 0 3.4
The data is as follows: month, day, year, hour, minute, streamflow. How do I average the observations to get a 6 hour average?
How would I do this once the gauge changes to 30 minute data later on in line 978 for example?

Answers (2)

Walter Roberson
Walter Roberson on 10 Oct 2021
Edited: Walter Roberson on 10 Oct 2021
In the below, I interpreted "every 6 hours" as having to do with absolute time of day (00:00, 06:00, 12:00, 18:00) rather than as being every 6 hours relative to whatever the first time in the data happened to be. For example if the first entry in the data happened to be at Jan 1, 2015 at 00:15 then I decided that you would want that bin to end just before 06:00 not at just before 06:15 .
The code would have been easier if it had been every 6 hours relative to the first time, especially if it could be assumed that the first entry would be the start of a day.
obs = [
1 1 2015 0 0 3.49
1 1 2015 1 0 3.49
1 1 2015 2 0 3.49
1 1 2015 3 0 3.49
1 1 2015 4 0 3.48
1 1 2015 5 0 3.48
1 1 2015 6 0 3.48
1 1 2015 7 0 3.47
1 1 2015 8 0 3.47
1 1 2015 9 0 3.47
1 1 2015 10 0 3.47
1 1 2015 11 0 3.47
1 1 2015 12 0 3.46
1 1 2015 13 0 3.46
1 1 2015 14 0 3.45
1 1 2015 15 0 3.45
1 1 2015 16 0 3.44
1 1 2015 17 0 3.44
1 1 2015 18 0 3.43
1 1 2015 19 0 3.43
1 1 2015 20 0 3.42
1 1 2015 21 0 3.41
1 1 2015 22 0 3.4
1 1 2015 23 0 3.4
];
secs = zeros(size(obs,1),1);
T = datetime( [obs(:,[3 1 2 4 5]), secs] );
streamflow = obs(:,6);
TT = timetable(streamflow, 'RowTimes', T);
firstt = min(T);
[y,m,d] = ymd(firstt);
h = hms(firstt);
h = floor(h/6) * 6; %6 hour boundary
firstt = datetime(y, m, d, h, 0, 0);
lastt = max(T);
newtimes = firstt : hours(6) : lastt;
SixHour = retime(TT, newtimes);
SixHour
SixHour = 4×1 timetable
Time streamflow ____________________ __________ 01-Jan-2015 00:00:00 3.49 01-Jan-2015 06:00:00 3.48 01-Jan-2015 12:00:00 3.46 01-Jan-2015 18:00:00 3.43
  8 Comments
Amanda Viken
Amanda Viken on 11 Oct 2021
This works but how do I do it for the whole dataset?

Sign in to comment.


Amanda Viken
Amanda Viken on 10 Oct 2021
I have a code written like this but keep getting NaN for my 5th column which is where the observation data is.
ct=0;
obs = load(".\Obs.COMO2.txt");
% Average every 1 hour days until 2/10/2015 @ 18:00
for x=1:5:978
ct = ct + 1;
new_obs(ct,5) = mean(obs(x:x+5:6))
new_obs(ct,1) = obs(x,1)
new_obs(ct,2) = obs(x,2)
new_obs(ct,3) = obs(x,3)
new_obs(ct,4) = obs(x+4,4)
end
  4 Comments
Amanda Viken
Amanda Viken on 11 Oct 2021
My process works but I am unsure of what to do once it changes to 30 minutes.

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!