Got Questions? Get Answers.
Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
Creating sub-matrices from a matrix

Subject: Creating sub-matrices from a matrix

From: Jerry

Date: 29 Oct, 2012 16:42:08

Message: 1 of 6

Hello,

There is an xls file that includes following columns and rows, let's call it "D":

Name Score Serial # CC Ranked
BMW A 21457601 1.59596 43.97984
vW B 21458248 1.48693 43.98082
Mazda B 21460865 1.00902 43.9845
BMW A 21489832 0.42058 44.01286
Honda A 21490333 0.41688 44.01308
BMW C 22033310 0.64276 44.86476
vW C 22088866 0.44933 44.94009
Honda A 22098330 0.38698 44.9448
vW B 22099678 3.20381 44.94546
Honda C 22261914 0.69764 45.16914

Now I would like to create three sub-datasets that are "A", "B", and "C":

"A":
Name Score Serial # CC Ranked
BMW A 21457601 1.59596 43.97984
BMW A 21489832 0.42058 44.01286
Honda A 21490333 0.41688 44.01308
Honda A 22098330 0.38698 44.9448

"B":
Name Score Serial # CC Ranked
vW B 21458248 1.48693 43.98082
Mazda B 21460865 1.00902 43.9845
vW B 22099678 3.20381 44.94546


"C":
Name Score Serial # CC Ranked
BMW C 22033310 0.64276 44.86476
vW C 22088866 0.44933 44.94009
Honda C 22261914 0.69764 45.16914

How may I use MATLAB to make these sub-datasets?

Thanks,
Jerry

Subject: Creating sub-matrices from a matrix

From: Ben

Date: 29 Oct, 2012 16:57:07

Message: 2 of 6

"Jerry " <jerrycholo@gmail.com> wrote in message <k6mbl0$l38$1@newscl01ah.mathworks.com>...
> Hello,
>
> There is an xls file that includes following columns and rows, let's call it "D":
>
> Name Score Serial # CC Ranked
> BMW A 21457601 1.59596 43.97984
> vW B 21458248 1.48693 43.98082
> Mazda B 21460865 1.00902 43.9845
> BMW A 21489832 0.42058 44.01286
> Honda A 21490333 0.41688 44.01308
> BMW C 22033310 0.64276 44.86476
> vW C 22088866 0.44933 44.94009
> Honda A 22098330 0.38698 44.9448
> vW B 22099678 3.20381 44.94546
> Honda C 22261914 0.69764 45.16914
>
> Now I would like to create three sub-datasets that are "A", "B", and "C":
>
> "A":
> Name Score Serial # CC Ranked
> BMW A 21457601 1.59596 43.97984
> BMW A 21489832 0.42058 44.01286
> Honda A 21490333 0.41688 44.01308
> Honda A 22098330 0.38698 44.9448
>
> "B":
> Name Score Serial # CC Ranked
> vW B 21458248 1.48693 43.98082
> Mazda B 21460865 1.00902 43.9845
> vW B 22099678 3.20381 44.94546
>
>
> "C":
> Name Score Serial # CC Ranked
> BMW C 22033310 0.64276 44.86476
> vW C 22088866 0.44933 44.94009
> Honda C 22261914 0.69764 45.16914
>
> How may I use MATLAB to make these sub-datasets?
>
> Thanks,
> Jerry


Haven't done much reading from excel myself, but I do know MATLAB has an XLSREAD function. Others might be able to give you cleaner/better solutions (or any tricks I don't know), but here's a way to do it:

[mat text] = xlsread('CarProblem.xlsx');
 A = mat(findstr([text{2:end,2}],'A'),:)
 B = mat(findstr([text{2:end,2}],'B'),:)
 C = mat(findstr([text{2:end,2}],'C'),:)

(I'm using Excel 2010 and Matlab R2012a)

Subject: Creating sub-matrices from a matrix

From: Jerry

Date: 30 Oct, 2012 13:29:09

Message: 3 of 6

Hello,

I ran following command and I got this error

"Index exceeds matrix dimensions."

Thanks,
Jerry

> Haven't done much reading from excel myself, but I do know MATLAB has an XLSREAD function. Others might be able to give you cleaner/better solutions (or any tricks I don't know), but here's a way to do it:
>
> [mat text] = xlsread('CarProblem.xlsx');
> A = mat(findstr([text{2:end,2}],'A'),:)
> B = mat(findstr([text{2:end,2}],'B'),:)
> C = mat(findstr([text{2:end,2}],'C'),:)
>
> (I'm using Excel 2010 and Matlab R2012a)

Subject: Creating sub-matrices from a matrix

From: Ben

Date: 30 Oct, 2012 18:56:08

Message: 4 of 6

> I ran following command and I got this error
>
> "Index exceeds matrix dimensions."


....Ok. Then we probably have differently formatted excel files. I just copied what you posted earlier into an excel sheet -- perhaps what you posted is different from the contents of the excel sheet?

At least this line should work for you, right?
>> [mat text] = xlsread('CarProblem.xlsx')

If that works, then post the output of mat and text.

Subject: Creating sub-matrices from a matrix

From: Jerry

Date: 31 Oct, 2012 20:40:08

Message: 5 of 6

Yes this [mat text] = xlsread('CarProblem.xlsx') command is working and here is

text:

'Name' ' Score' ' Serial #' ' CC' ' Ranked'
'BMW' ' A' '' '' ''
'vW' ' B' '' '' ''
'Mazda' ' B' '' '' ''
'BMW' ' A ' '' '' ''
'Honda ' 'A' '' '' ''
'BMW' ' C ' '' '' ''
'vW' ' C ' '' '' ''
'Honda ' 'A ' '' '' ''
'vW' ' B ' '' '' ''
'Honda' ' C ' '' '' ''

and mat:

21457601 1.59596000000000 43.9798400000000
21458248 1.48693000000000 43.9808200000000
21460865 1.00902000000000 43.9845000000000
21489832 0.420580000000000 44.0128600000000
21490333 0.416880000000000 44.0130800000000
22033310 0.642760000000000 44.8647600000000
22088866 0.449330000000000 44.9400900000000
22098330 0.386980000000000 44.9448000000000
22099678 3.20381000000000 44.9454600000000
22261914 0.697640000000000 45.1691400000000

but again I got this error ("Index exceeds matrix dimensions.") when I ran the next command:

Jerry

Subject: Creating sub-matrices from a matrix

From: Ben

Date: 1 Nov, 2012 18:10:08

Message: 6 of 6

"Jerry " <jerrycholo@gmail.com> wrote in message <k6s2b8$a24$1@newscl01ah.mathworks.com>...
> Yes this [mat text] = xlsread('CarProblem.xlsx') command is working and here is
>
> text:
>
> 'Name' ' Score' ' Serial #' ' CC' ' Ranked'
> 'BMW' ' A' '' '' ''
> 'vW' ' B' '' '' ''
> 'Mazda' ' B' '' '' ''
> 'BMW' ' A ' '' '' ''
> 'Honda ' 'A' '' '' ''
> 'BMW' ' C ' '' '' ''
> 'vW' ' C ' '' '' ''
> 'Honda ' 'A ' '' '' ''
> 'vW' ' B ' '' '' ''
> 'Honda' ' C ' '' '' ''
>
> and mat:
>
> 21457601 1.59596000000000 43.9798400000000
> 21458248 1.48693000000000 43.9808200000000
> 21460865 1.00902000000000 43.9845000000000
> 21489832 0.420580000000000 44.0128600000000
> 21490333 0.416880000000000 44.0130800000000
> 22033310 0.642760000000000 44.8647600000000
> 22088866 0.449330000000000 44.9400900000000
> 22098330 0.386980000000000 44.9448000000000
> 22099678 3.20381000000000 44.9454600000000
> 22261914 0.697640000000000 45.1691400000000
>
> but again I got this error ("Index exceeds matrix dimensions.") when I ran the next command:
>
> Jerry


Ahh, ok. When I pasted the data into excel, I removed the leading white spaces from the 'Score' column. With the spaces in there, the indexing is off. Anyway, the code below should work regardless of white spaces (though a two character score of 'A-' will break it). This code will also write to (creating if necessary) sheets in your excel file titled 'A', 'B', and 'C'. The excel document can't be open (or it can't write) and it will probably warn you if it has to create the sheets.

Change the file location in the below code to the appropriate path before you run.

file_location = 'C:\Users\Ben\Desktop\CarProblem.xlsx';
[mat txt] = xlsread(file_location);
txt(:,2) = strtrim(txt(:,2));
numA = num2cell(mat(findstr([txt{2:end,2}],'A'),:));
numB = num2cell(mat(findstr([txt{2:end,2}],'B'),:));
numC = num2cell(mat(findstr([txt{2:end,2}],'C'),:));
txtA = txt(findstr([txt{2:end,2}],'A')+1,:);
txtB = txt(findstr([txt{2:end,2}],'B')+1,:);
txtC = txt(findstr([txt{2:end,2}],'C')+1,:);
txtA(:,3:end) = numA(:,1:end);
txtB(:,3:end) = numB(:,1:end);
txtC(:,3:end) = numC(:,1:end);
A = [{txt{1,:}};txtA];
B = [{txt{1,:}};txtB];
C = [{txt{1,:}};txtC];
xlswrite(file_location,A,'A');
xlswrite(file_location,B,'B');
xlswrite(file_location,C,'C');


Hopefully the approach I'm taking makes sense (with some inspection, of course).

numA: The output from xlsread separates out the text from the data. It puts the text in a cell array, the data in a matrix. You want to sort by letter (the second row of the cell array stored in 'txt'), so I'm concatenating everything into a string (which means [txt{2:end,2}] should return a string of 'ABBAACCABC'). I need to index from 2:end because the first row is the header 'Score'. After I get that string, I try to find the letter you want to get a vector to use for indices (so the section findstr([txt{2:end,2}],'A') should return a vector [1 4 5 8]). Then I use those indices to grab the appropriate rows from the data. Lastly, I convert that to a cell since we need to write a cell back into excel.

txtA: Using the same indexing idea, get the car names (have to add one to the indexing this time around because of the header) and then combine it with the data from numA.

A: Add the headers back onto data.

xlswrite: write it to the same file, creating sheets if necessary.

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us