Hourly average of the data which is not continuous?

I have an excel workbook of data includes 3 parameters date, time & data. I want to take an hourly average of the data but I am unable to figure it out. Can somebody help me out in this issue? I am attaching the excel sheet here with this question. Any help would be appreciated. Thanks.

Answer 1

[Benjamin Kraus]

If you have MATLAB R2016b or newer, you can use the retime method on time tables.

t = readtable('data file.csv'); % Read the table
t = standardizeMissing(t,-999); % Replace -999 with NaN
t.Time = t.(1)+t.(2); % Create a new 'Time' column with full date+time.
t.Time.Format = 'MM/dd/uuuu HH:mm:ss'; % Change format of 'Time' column to show date+time.
tt = table2timetable(t, 'RowTimes', 'Time'); % Convert to a timetable.
tt2 = retime(tt, 'hourly', 'mean') % Take hourly average.

Some notes of caution:

• This should go without saying: Double-check the math performed on at least a few example hours to make sure you are getting the results you expect.
• I noticed your CSV file has no times greater than 12:00:00, suggesting that your CVS file is not differentiating between AM and PM. Without that information, MATLAB is assuming you have no readings from the afternoon.
• I assumed that -999 were invalid readings.
• You may want to remove the -999 readings (instead of replacing them with NaN). You can do that using rmmissing.