How to group Time based on hours and assign Time groups to their Dates ?

I have a table of 3 columns [Date, Time, Observation]

The Goal here is to have the hourly mean value of “Observation”.

So I split “Observations" by groups specific by the “Time” and calculate the mean of the observations.

R=readtable('KWI.xlsx');
[group,times] = findgroups(R.Time);
avobvservation = splitapply(@mean, R.Observation, group);

The results were wrong compared to manual calculation.

Q1: How can I specify the grouping of Time to be based on hours?

Then I want to write it in a table as [Date, Time group, mean Observations]

My problem here is with the “Date”.

Q2 : How can I assign the Time groups to their Dates?

If you think the approach here is wrong, I'm very open to any suggestions as long as I reach the goal.

Attached sample of data.

Thank you

Answer 1

[Marco Morganti]

A1: You should first convert the time values to get explicit information about the hour of the day. E.g. if the current representation is a number showing time as a fraction of the day (a value between 0 and 1), you could convert it to integer hour values using:

[group,times] = findgroups(floor(R.Time*24));

A2: To be able to differentiate between the same hour value on different days you can add a second input to the findgroups() function:

[group,times] = findgroups(floor(R.Time*24),R.Date);

Answer 2

[Peter Perkins]

Not sure exactly what you are asking for, because you don't say what's in your table, and readtable's behavior for a spreadsheet like that has changed in recent releases.

It sounds like you need to create an Hour variable in that table, and use both that and the date as the grouping variables in the call to findgroups. Converting Date to a datetime and Time to a duration (if they aren't already) should make that simple.

Hope this helps.