How do I take a table which has one value for every hour and produce an average for hours X:Y, one for each day?

I have a table that has a first column as dates in 'dd-mmm-yyyy hh-mm-ss' format and the second column is values. I want to produce a second table from this first table but have only one entry per day such that the first column will only be 'dd-mmm-yyyy' and the second column will be the average of the hourly values between hours X and Y. How can I do this?

Answer 1

[Jos (10584)]

Bin the times using HISTC and get the indices. Then accumalate the values using ACCUMARRAY. An example:

% data : [time values]
data = [1.0 10 ; 1.1 12 ; 1.2 10 ; 2.2 15 ; 2.4 25 ; 3.3 33]
edges = 1:3
[~,binind] = histc(data(:,1),[edges inf]) 
AVGvalue = accumarray(binind, data(:,2),[], @mean)

Answer 2

[Peter Perkins]

Using timetables, retime makes this simple. It sounds like you want an average for each day only considereing the values between, say, 8am and 8pm. Create a time vector at 8am and 8pm spanning your data, and retime to that, using 'mean'. Then throw away the corresponding to 8pm to 8am.

If you don't have access to R2016b or later, you can still do this with tables. I'll assume your date variable is a datetime. Create a subset of your data using something like (8<=hour(t.Date) & hour(t.Date)< 20) to select rows of interest. Then creat a new variable in the table using the dateshift function to truncate your timestamps whole days. Then use varfun, grouping on your day variable, and compute the mean of your data variables.