How do I reshape a raw data matrix?

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

[Walter Roberson]

Is this for the purpose of displaying to the command window, or is it for the purpose of writing to a file, or is it for the purpose of displaying in a GUI, or is it for the purpose of creating a spreadsheet, or is it necessary to have a data structure that is accessible as a table?

[manxino8]

Hi Walter,

I need to reshape the data so I can perform some analysis on it. So I need it to be a table to work with it afterwards

Answer 1

[Guillaume]

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)))

[manxino8]

Thanks! It worked. Fantastic!! I tried both of them and the second one is exactly what I need!

[manxino8]

Hi Guillaume,

After unstacking the table I have the data as I wanted. The stations are in the columns and the time in the rows, filled by the slots. However, I need now to aggregate this data as following:

Current Table:

 update_time | station1 | station2| ...
   00:00          14         12
   00:02          13         12
     .
     .
   23:59          2           17

Now, I would like to have only 24 rows, being each row one daily hour and the slots the mean value, keeping the columns as the stations (i.e the mean of all the slots for each station between 00:00 and 00:59, and so on).

how should I do that?

best,

Oriol

Answer 2

[Andrei Bobrov]

f = fopen('20160421.txt');
c = textscan(f,'%s %f %f');
fclose(f);

[c0,~,c1] = unique(c{1});
out = [{nan},num2cell(1:428);c0,num2cell(accumarray([c1,c{2}],c{3}))];

if you had data 'slots' for each minites from array 00:00 - 00:59, then

[c0,~,c1] = unique(c{1});
out = [{nan},num2cell(1:428);c0,num2cell(reshape(c{3},numel(c0),[]))];

[manxino8]

Hi Andrei,

I have the matrix already in my Workspace (which I obtain running a Mysql query through a function I created). That matrix has also values in the other columns, I mean, the one I wrote above was to show the columns I was interested in so I didn't put values in the other ones but they are still there

[Andrei Bobrov]

Please attach mat file with your matrix and look at the Guillaume answer.

[manxino8]

Thank you for your help Andrei!