How do I reshape a raw data matrix?

2 次查看（过去 30 天）
manxino8
manxino8 2016-4-21
编辑： manxino8 2016-4-29
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
  2 个评论
Walter Roberson
Walter Roberson 2016-4-21
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
manxino8 2016-4-21
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

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Guillaume
Guillaume 2016-4-21
编辑：Guillaume 2016-4-21
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)))
  2 个评论
manxino8
manxino8 2016-4-21
编辑：manxino8 2016-4-21
Thanks! It worked. Fantastic!! I tried both of them and the second one is exactly what I need!
manxino8
manxino8 2016-4-29
编辑：manxino8 2016-4-29
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

请先登录，再进行评论。

更多回答（1 个）

Andrei Bobrov
Andrei Bobrov 2016-4-21
编辑：Andrei Bobrov 2016-4-21
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),[]))];
  3 个评论
显示 1更早的评论
Andrei Bobrov
Andrei Bobrov 2016-4-21
Please attach mat file with your matrix and look at the Guillaume answer.
manxino8
manxino8 2016-4-21
Thank you for your help Andrei!

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Characters and Strings 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by