HOW TO FILTER AN HOUR DATA FROM ARRAY OF DAILY,MONTHLY AND YEAR DATA

I HAVE DATA WITH FOLLOWING COLUMN: DATE, TIME, S/N, VALUES AS SHOWN IN THE ATTACHED FILE. KINDLY HELP ME WITH CODE TO SEPARATE THE DATA INTO DIFFERENT HOURS SUCH AS 7.30, 8.30, ..., 15.30 AND DIFFERENT MONTHS, JANUARY, FEBRUARY, ... THANKS.

OJO O.S. [e-mail]

[Scott MacKenzie]

What do you mean by "separate"? Do you want the data sorted by hour, or sorted by month? Or something else?

[Ojo Olusola]

Yes, I want the data sorted by hour and also sorted by month.

That is,

I want the data of each hour sorted separately such as 7.30, 8.30, 9.30, ..., 15.30 across 01-Jan-2000 to 31-Dec-2020. Also, for each time such as 7.30, l want to sort the data of each of the month separately. Thanks.

Answer 1

[Scott MacKenzie]

T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/658625/SAMPLE%20DATA.xlsx');

% combine DATE and TIME columns and replace as single DateTime column
DateTime = T.DATE + T.TIME;
DateTime.Format = 'yyyy-MM-dd HH:mm';
T = [table(DateTime) T(:,3:end)];

% for sorting, create separate columns for year, month, and hour
T.Year = year(T.DateTime);
T.Month = month(T.DateTime);
T.Hour = hour(T.DateTime);
yearColumn = find(string(T.Properties.VariableNames) == "Year");
monthColumn = find(string(T.Properties.VariableNames) == "Month");
hourColumn = find(string(T.Properties.VariableNames) == "Hour");

% sort by month
T1 = sortrows(T, monthColumn);

% sort by hour
T2 = sortrows(T, hourColumn);

% sort by hour, separately within each year
T3 = sortrows(T, [yearColumn hourColumn]);

[Ojo Olusola]

Dear Scott Mackenzie, Thank you for this response. The code solved the problem to some extends. I still want the data of each of the hours file by file separately across the years. Thanks.

[Scott MacKenzie]

OK, sure. Just add...

% sort table by hour, separately within each year
T.YEAR = year(T.DATE);
yearColumn = find(string(T.Properties.VariableNames) == "YEAR");
sortrows(T, [yearColumn hourColumn])

I've just added this to the answer code and cleaned up a few other things as well.

[Ojo Olusola]

Thank you,the code worked.How can l convert table format to double array?

[Scott MacKenzie]

Converting to a double array is simple except for the 1st column, since the data in the 1st column are DateTime.

If you just want the numeric data, which start in the 2nd column, then

T{:,2:end}

If you want to include the DateTime information, it must be converted to a serial date number. This will do the trick:

[datenum(T.DateTime) T{:,2:end}]