MATLAB: How to arrange time series data into yearly groups

Question

Hi,

I have daily temperature time series from 01/03/1961 to 28/07/2018. I want to arrange this data as per water year convention (01 June to 31 May) in following form:

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 
                           Temperature                                            Temperature                              Temperature  
Date                   (1960-1961)              Date                         (1961-1962)        . . . . . . . . . . . (2017-2018)
01/06/1960                                      01/06/1961
.                                                        .
.                                                       .
.                                                       .
.                                                       .
.                                                       .
31/05/1961                                     31/05/1962
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Finally, I need this data table in following form

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =                                                                       
                  = = = = = = = = = = = = Temperature = = = = = = = = = = = = 
Day          1960-1961                 1961-1962  .   .   .   .   .   .   .  2017-2018
1
2
3
.
.
.
.
.
.
365
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Please tell me how to do this.

Thanks in advance.

Answer 1

A simpler and faster way of obtaining the same as Joe's answer:

dataInitial = readtimetable('Data.xlsx');
dataFinal = table(day(dataInitial.DateTime, 'dayofyear'), year(dataInitial.DateTime), TemperatureDifference, ...
                  'VariableNames', {'Day', 'Year', 'TemperatureDifference'});
uyears = unique(dataFinal.Year);
dataFinal = unstack(dataFinal, 'TemperatureDifference', 'Year', 'NewVariableNames', compose('%d-%d', uyears, uyears+1))

Since tables and matrices can't have a different number of rows per column, empty entries are automatically filled by NaN.

edit: and for versions < R2019b which don't support arbitrary variable names for table variables:

edit: and for versions < R2019a which don't have readtimetable:

dataInitial = table2timetable(readtable('Data.xlsx'));
dataFinal = table(day(dataInitial.DateTime, 'dayofyear'), year(dataInitial.DateTime), TemperatureDifference, ...
                  'VariableNames', {'Day', 'Year', 'TemperatureDifference'});
uyears = unique(dataFinal.Year);
dataFinal = unstack(dataFinal, 'TemperatureDifference', 'Year', 'NewVariableNames', compose('Y%d_%d', uyears, uyears+1))

Again, this produces the same result as Joe's code (only faster and with a lot less work!)