How can I import Excel data in a cell array?
36 views (last 30 days)
Show older comments
Hi, I have a cell array as I coded here:
tt = cell (4,1);
for i = 1:4
tt {i,1} = cell (16,1);
for j = 1:16
tt {i,1}{j,1} = zeros (2,6);
end
end
and the data format in the Excel file is as the attached table. Would you please help me to import it into MATLAB properly?
4 Comments
Guillaume
on 18 Oct 2016
Edited: Guillaume
on 18 Oct 2016
Yes, Ganesh answered the easy bit, importing the data in matlab is trivial. The hard bit is reformatting it in the cell array format you want.
To answer that, more explanation is needed.
- Does the cell array exist before the import and is it the correct size, so that we can infer the structure of the excel file from the size of the cell arrays?
- Otherwise, is the structure of the excel file fixed (i.e. always 17 big blocks of 4x6 smaller block of 1x2 numbers?
- Or the size of big, medium and small black is all variable and to be determined from the excel file?
Accepted Answer
Marlies
on 18 Oct 2016
I would approach this in a different direction. Instead of making the framework first, and then filling that up, I would 'package' the data into cells from the lowest level to the highest level. This is something that MAT2CELL can do for you.
The only thing I struggled with is that your 'framework' has room for sixteen 'blocks', while your Excel-file has seventeen blocks. So my solution is:
- a 4x1 cell
- where each cell contains a 17-by-1 cell
- where every cell conrains a 2-by-1 cell
%%Import the data from excel
data = xlsread('TTT.xlsx');
%%Remove the 'headers' from the file
% Since I could not fit 17 blocks in sixteen cells, this might be the place
% where you use indexing to get rid of the 'irrelevant' block.
data = data(3:end, 2:end);
%%First, place ever set of six corrsponding vectors inside one cell
divRow_1 = ones(size(data,1),1); % Keep the 8 rows as 8 rows
divCol_1 = repmat(6,1,size(data,2)/6); % Place the columns per six into one cell (17 times)
perSix = mat2cell(data,divRow_1,divCol_1);
%%Second, pair every set of two vectors
divRow_2 = repmat(2,size(data,1)/2,1); % Place the rows per two into one cell (4 times)
divCol_2 = ones(1,size(perSix,2)); % Keep the 17 columns as 17 columns
perTwo = mat2cell(perSix,divRow_2,divCol_2)'; % Transpose the output
%%Last, pair every set of seventeen vectors
divRow_3 = size(perTwo,1); % Place rows per 17 into one cell (four times)
divCol_3 = ones(size(perTwo,2),1); % Keep the four columns as four columns
perFour = mat2cell(perTwo,divRow_3, divCol_3)'; % Transpose the output
I hope this helps. Kind regards,
Marlies
More Answers (1)
Marlies
on 19 Oct 2016
There is a second alternative, which is not answering the question you asked, but that might be giving a better starting point to work with your data.
If the data in the various blocks is numerical, I assume you want to be able to perform numerical analysis on it. For instance, compute the average value of the six data points of the ninth dataset for the third observation, but only for the top-observation ("AY7:BD7"). Or what if you would like to do that for every of the 8*16 sets of six-measurements.
Getting data out of a cell-array is not trivial, and getting data out of a cell inside a cell inside a cell is definitely not trivial. An alternative could be to place all data in a table-datatype (available since R2013b), and have the various row- and column numberings as a separate grouping variable. This allows the use of grouping commands like findgroups or splitapply.
You can use the code below on your data, but I also made up a numerical dataset, so you can see better what is going on. See attachment 'TTTdata.xlsx'.
I invented some names for the various grouping 'variables' to keep the code as readable as possible. I hope they resonate with the problem you have.
%%Import into table, and work with groupings
%%Import the data
data = xlsread('TTTdata.xlsx');
data = data(3:end, 2:end-6);
% Transpose the data (so it can be processed in an convenient way), and
% then put it in one long list
dataT = data';
dataL = dataT(:);
%%define some dimensions
%
% In total there are 16 Locations (defined as the columns).
% Each location contains 6 sensors, and each sensor measures two properties.
% There are in total 4 observations ('measurements'), defined over the rows.
numSensor = 6;
numProp = 2;
numLoc = size(dataT,1) / numSensor; % This should give 16
numObs = size(dataT,2) / numProp; % This should give 4
numPerObs = numLoc * numSensor * numProp; % Amount of datapoints per observation
numPerProp = size(dataL,1) / numSensor; % Unique Location-Observation combinations?
%%Create the grouping for the Sensors
grpSensor = (1:numSensor)';
grpSensor = repmat(grpSensor,numPerProp,1);
% If the sensors have indicative names, CATEGORICAL can be usefull here
% sensorNames = {'top','bottom','left','right','front','back'};
% grpSensor = categorical(grpSensor,1:6,sensorNames);
%%Create the grouping for properties (prop1 or prop2)
grpProp = [ones(numSensor*numLoc,1);2*ones(numSensor*numLoc,1)];
grpProp = repmat(grpProp,numObs,1);
% If the properties have indicative names, CATEGORICAL can be usefull here
% propNames = {'temperature','flow'};
% grpProp = categorical(grpProp,1:2,propNames);
%%Create a grouping for the Locations
grpLoc = repelem(1:numLoc,numSensor)'; % repelem has been around since R2015a
grpLoc = repmat(grpLoc,numProp*numObs,1);
%%Create a grouping for the Observations
grpObs = repelem(1:numObs,numPerObs)'; % repelem has been around since R2015a
%%Now combine all data:
Data = table(dataL, grpSensor, grpProp, grpLoc, grpObs);
See Also
Categories
Find more on Spreadsheets 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!