MATLAB: How to reshape a raw data matrix

datasetrawreshapetime

Hi,

I have the following problem and I am struggling to find out a solution.

I have a MATLAB table that comes from a database and looks like this:

           | Id | update_time | station_id | status | slots | bikes
                     00:00          1                   25
                     00:00          2                   15
                       .            .                    .
                       .            .                    .
                     00:00         428                  19
                     00:01          1                   27
                     00:01          2                   18
                       .            .                    .
                       .            .                    .
                     00:01         428                  14

So, the data is collected each minute for 428 stations (station_id) until 23:59 (one day of data).

I would like to reshape this matrix into one that has the Update_time in column "zero" and then it has 428 more columns being Column 1 = station_id 1, column 2 = station_id 2, …, Column 428 = station_id 428. Then, the matrix must be filled in with the "SOLTS". It should look like:

                       | 1 | 2 |  . . . . . | 428 |
               00:00    25   15               19
               00:01    27   18               14
                 .
                 .
               23:59    12   15                0

How should I do that?

Thanks,

Oriol

Best Answer

This sounds like a job for unstack:

newtable = unstack(t(:, {'update_time', 'station_id', 'slots'}), 'slots', 'station_id')

You may want to specify new names for the columns:

   newtable = unstack(t(:, {'update_time', 'station_id', 'slots'}), 'slots', 'station_id', 'NewDataVariableNames', sprintfc('station_id%d', unique(t.station_id)))

Related Solutions

MATLAB: Shifting a timeseries without lagmatrix

Something like this maybe,

%create some data
C = {'06-Jan-1911 00:00:00'  425
'07-Jan-1911 00:00:00'  345
'08-Jan-1911 00:00:00'  425
'09-Jan-1911 00:00:00'  317
'10-Jan-1911 00:00:00'  428
'11-Jan-1911 00:00:00'  566
'12-Jan-1911 00:00:00'  515
'13-Jan-1911 00:00:00'  473
'14-Jan-1911 00:00:00'  369
'15-Jan-1911 00:00:00'  330
'16-Jan-1911 00:00:00'  305
'17-Jan-1911 00:00:00'  243
'18-Jan-1911 00:00:00'  256
'19-Jan-1911 00:00:00'  266
'20-Jan-1911 00:00:00'  240};
%convert it timetable
TT = timetable(datetime(C(:,1)),[C{:,2}].')

now it looks like,

TT =
    15×1 timetable
         Time       Var1
      __________    ____
      1911-01-06    425 
      1911-01-07    345 
      1911-01-08    425 
      1911-01-09    317 
      1911-01-10    428 
      1911-01-11    566 
      1911-01-12    515 
      1911-01-13    473 
      1911-01-14    369 
      1911-01-15    330 
      1911-01-16    305 
      1911-01-17    243 
      1911-01-18    256 
      1911-01-19    266 
      1911-01-20    240

now add 7 days to the time column,

 TT.Time = TT.Time+days(7);

now the modified timetable looks like,

TT =
    15×1 timetable
         Time       Var1
      __________    ____
      1911-01-13    425 
      1911-01-14    345 
      1911-01-15    425 
      1911-01-16    317 
      1911-01-17    428 
      1911-01-18    566 
      1911-01-19    515 
      1911-01-20    473 
      1911-01-21    369 
      1911-01-22    330 
      1911-01-23    305 
      1911-01-24    243 
      1911-01-25    256 
      1911-01-26    266 
      1911-01-27    240

is this what you want?

MATLAB: Arrayfun with a timetable

timetable1 is not a timetable, and probably not even legal MATLAB code. I'm going to assume you mean that you have an actual timetable with datetime row times and one numeric variable. And a list of datetimes at which you want to select rows of your timetable.

Just subscript the timetable with the datetimes. That's it.

>> tt = timetable([1;2;3;4;5],'RowTimes',datetime(2020,11,18:22))
tt =
  5×1 timetable
       Time        Var1
    ___________    ____
    18-Nov-2020     1  
    19-Nov-2020     2  
    20-Nov-2020     3  
    21-Nov-2020     4  
    22-Nov-2020     5  
>> tt(datetime(2020,11,[18 20 22]),:)
ans =
  3×1 timetable
       Time        Var1
    ___________    ____
    18-Nov-2020     1  
    20-Nov-2020     3  
    22-Nov-2020     5

You should also take a look at timerange, which is a way to create a subscript that will select rows in a range rather tha exact matches.

Best Answer

Related Solutions

MATLAB: Shifting a timeseries without lagmatrix

MATLAB: Arrayfun with a timetable

Related Question