MATLAB Answers

How can I import Excel data in a cell array?

조회 수: 116(최근 30일)
Sherwin 18 Oct 2016
편집: Sherwin 19 Oct 2016
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);
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
Sherwin 18 Oct 2016
Thank you so much. Yes, the code exists before I import the data but I can change it easily. The size doesn't change at all (and of course through the 17 big blocks) but actually there are two 1x6 vectors, for each 17 big blocks and 4 rows. Maybe It's better to be coded like this:
tt = cell (4,1);
for i = 1:4
tt {i,1} = cell (16,1);
for j = 1:16
tt {i,1}{j,1} = cell (2,1);
for k = 1:2
tt {i,1}{j,1}{k,1} = zeros (1,6);
and the data fits into this format exactly.The length of cells don't change at all.

댓글을 달려면 로그인하십시오.

채택된 답변

Marlies 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,
  댓글 수: 1
Sherwin 18 Oct 2016
I am so sorry, the last block (block 17) wasn't supposed to enter the code, I must've forgotten to delete them. Thank you so much for your comprehensive answer, it helped a lot.

댓글을 달려면 로그인하십시오.

추가 답변(1개)

Marlies 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);
  댓글 수: 1
Sherwin 19 Oct 2016
That's amazing how you can easily handle the data, I learned a lot. Thank you so much, it means the world to me.

댓글을 달려면 로그인하십시오.

Community Treasure Hunt

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

Start Hunting!

Translated by