concatenating the rows to have a column wise data
Show older comments
In the attached data set, the rows provide daily information at a fifteen minute interval for a set of 9 variables for 31 days of January. Is there an easy way to rearrage this data, so I have 9 variables as columns and their sequence of observations for the entire month as rows. Please help!
Accepted Answer
This could almost certainly be simplified, but I think this is at least readable:
% data arranged as groups of 4 row vectors
A = [1:10; 101:110; 201:210; 301:310; 11:20; 111:120; 211:220; 311:320]
% rearrange
B = [reshape(A(1:4:end,:).',[],1) ...
reshape(A(2:4:end,:).',[],1) ...
reshape(A(3:4:end,:).',[],1) ...
reshape(A(4:4:end,:).',[],1)]
The webpage truncates the final output here, but it does continue in a column vector format.
8 Comments
Kushal Bhalla
on 24 May 2021
Kushal Bhalla
on 20 Nov 2023
Afaik, operations like transpose(), reshape() don't work on tables. In the given example, they're just working on numeric arrays.
A = readmatrix('data.csv'); % read into a numeric array
A = A(:,2:end); % changed
cols = 9; % number of output columns
A = reshape(A.',size(A,2),cols,[]);
A = permute(A,[1 3 2]);
A = reshape(A,[],cols)
Kushal Bhalla
on 21 Nov 2023
Oh that's just great. The behavior of readmatrix() differs between versions, so it returns a different size output than I was getting. That's why it's wrong. I'll fix it in a minute.
There is one column denoting date and category. There are four trailing columns containing empty chars. When converted to numeric, all five of these columns will become NaN. In R2019b, all five columns are preserved. In R2023b, apparently only some of them are. The trailing columns get discarded in the newer version, so attempting to discard them manually was truncating the data.
Kushal Bhalla
on 21 Nov 2023
Edited: Kushal Bhalla
on 21 Nov 2023
DGM
on 22 Nov 2023
readmatrix() is picking up the time headers as data, so row 1 needs to be omitted.
A = A(2:end,3:end);
Stuff like readmatrix() and readtable() might be conveniences, but it pays to make sure it's picking up the file like you expect.
Kushal Bhalla
on 22 Nov 2023
More Answers (1)
The MATLAB approach:
T = readtable('data.csv', 'VariableNamingRule','Preserve')
P = digitsPattern+":"+digitsPattern;
U = stack(T, P, 'NewDataVariableName','Data', 'IndexVariableName','QuarterHours')
C = split(U.('Date-Fuel'),'-');
D = strcat(C(:,1),'_',cellstr(U.QuarterHours));
U.Fuel = C(:,2);
U.Date = datetime(D,'InputFormat','d/M/yy_HH:mm', 'Format','yyyy-MM-dd HH:mm')
V = unstack(U,'Data','Fuel', 'GroupingVariables','Date')
Having your data arranged this way will make it much easier to work with. For example:
format compact
summary(V)
Categories
Find more on Data Preprocessing in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
