Reformatting a table with (yyyy-mm-dd HH:mm:ss) in one column to a new table with dates in the rows and time in the columns.

I have a table of time series data over several years with the following format:

 '2014-01-01 00:30:00'  54
 '2014-01-01 01:00:00'  53
.
.
.
 '2014-01-03 02:30:00'  36
 '2014-01-03 03:00:00'  34
etc.

With columns: Time(yyyy-mm-dd HH:mm:ss) and Data

I am trying to reformat this so I can analyse the data by the day and create hourly averages. For example calculating the average value at 10am every day in January....

I believe the first step would be to reformat the table to have the rows as the date (yyy-mm-dd) and the columns as the time (HH:mm:ss) and populated with the corresponding data. What would be the best way of doing this? Is it best to keep it in a table or use a different data structure?

Thanks very much in advance

[James Lam]

Edit: For example calculating the average value at 10am every day in January....

Answer 1

[Peter Perkins]

James, you're right about retime, what you're looking for is a "seasonal average" (I forget the right term) where all 10:00's across all days are grouped together. There are several ways to do this, here's two of them:

Create some simple data:

>> X = randn(10,1);
>> day = [1 1 2 2 3 3 4 4 5 5]';
>> hour = [0 12 0 12 0 12 0 12 0 12]';
>> tt = timetable(X,'RowTimes',datetime(2017,1,day,hour,0,0))
tt =
  10×1 timetable
            Time                X    
    ____________________    _________
    01-Jan-2017 00:00:00     0.083462
    01-Jan-2017 12:00:00      0.42727
    02-Jan-2017 00:00:00       1.3205
    02-Jan-2017 12:00:00      0.72672
    03-Jan-2017 00:00:00       1.0172
    03-Jan-2017 12:00:00    0.0081844
    04-Jan-2017 00:00:00        1.711
    04-Jan-2017 12:00:00      0.17656
    05-Jan-2017 00:00:00       1.5919
    05-Jan-2017 12:00:00      0.47648

Add hour of day as a new variable, and compute grouped means by Hour:

>> tt.Hour = tt.Time.Hour;
>> varfun(@mean,tt,'GroupingVariable','Hour','OutputFormat','table')
ans =
2×3 table
  Hour    GroupCount     mean_X 
  ____    __________    ________
    0         5         -0.20561
   12         5          -0.6783

Or, add day as a new variable and unstack the data by hour within day:

>> tt.Day = tt.Time.Day;
>> ttu = unstack(tt,'X','Hour','GroupingVariable','Day');
>> ttu.Time.Format = 'defaultDate'
Warning: Variable names were modified to make them valid MATLAB identifiers. 
ttu =
  5×3 timetable
        Time       Day       x0          x12   
    ___________    ___    ________    _________
    01-Jan-2017     1      0.66052    -0.071304
    02-Jan-2017     2     -0.37509      0.44703
    03-Jan-2017     3        1.085      -1.8861
    04-Jan-2017     4      -1.7021      -2.0648
    05-Jan-2017     5     -0.69644       0.1836

Then you can compute means by hour. I would think the first version is more useful unless you want the unstacked version for something else.

[James Lam]

Thanks very much, Peter.

Answer 2

[Steven Lord]

If you're using release R2016b or later, consider storing your data as a timetable. If you can store it as a timetable, you can use retime on that timetable. See the "Aggregate Timetable Data and Calculate Mean Values" example on the retime documentation page for more information.

[James Lam]

Thanks very much. It doesn't seem to be possible to create an hourly average over a number of days using this method, though. For example if I wanted to know the average value at 10am everyday in January...