Convert nonuniform cell array to numeric array

Question

I am using xlsread in MATLAB to read in sheets from an excel file. My goal is to have each column of the excel sheet read as a numeric array. One of the columns has a mix of numbers and numbers+char. For example, the values could be 200, 300A, 450, 500A, 200A, 100. here is what I have so far:

[num, txt, raw] = xlsread(fileIn, sheets{ii});    % Reading in each sheet from a for loop
myCol = raw(:, 4)                             % I want all rows of column 4
for kk=1:numel(myCol)
       if iscellstr(myCol(kk))
           myCol(kk) = (cellfun(@(x)strrep(x, 'A', ''), myCol(kk), 'UniformOutput', false));
       end
end

myCol = cell2mat(myCol);

This is able to strip off the char from the number but then I am left with:

myCol = 

[200]

'300'

[450]

'500'

'200'

[100]

which errors out on cell2mat with:

cell2mat(myCol)

??? Error using ==> cell2mat at 46

All contents of the input cell array must be of the same data type.

I feel like I am probably mixing up () and {} somewhere. Can someone help me out with this?

What is the variable alt? Should that be myCol?

gnovice
– gnovice

2011-12-22 19:28:01 +00:00
Commented Dec 22, 2011 at 19:28 — gnovice
– gnovice, Commented Dec 22, 2011 at 19:28
Whoops sorry. Yes it should be. I edited it.

Doomchinchilla
– Doomchinchilla

2011-12-22 19:36:48 +00:00
Commented Dec 22, 2011 at 19:36 — Doomchinchilla
– Doomchinchilla, Commented Dec 22, 2011 at 19:36

yuk · Accepted Answer · 2011-12-22 19:46:42Z

7

Let me start from reading the file

[num, txt, raw] = xlsread('test.xlsx');
myCol = raw(:, 4);

idx = cellfun(@ischar,myCol ); %# find strings
data = zeros(size(myCol)); %# preallocate matrix for numeric data
data(~idx) = cell2mat(myCol(~idx)); %# convert numeric data
data(idx) = str2double(regexprep(myCol(idx),'\D','')); %# remove non-digits and convert to numeric

answered Dec 22, 2011 at 19:46

yuk

19.9k14 gold badges70 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gnovice · Accepted Answer · 2011-12-22 19:58:00Z

The variable myCol is initially a cell array containing both numbers and strings, something like this in your example:

myCol = {200; '300A'; 450; '500A'; '200A'; 100};

The steps you have to follow to convert the string entries into numeric values is:

Identify the cell entries in myCol that are strings. You can use a loop to do this, as in your example, or you can use the function CELLFUN to get a logical index like so:
```
index = cellfun(@ischar,myCol);
```
Remove the letters. If you know the letters to remove will always be 'A', as in your example, you can use a simple function like STRREP on all of your indexed cells like so:
```
strrep(myCol(index),'A','')
```
If you can have all sorts of other characters and letters in the string, then a function like REGEXPREP may work better for you. For your example, you could do this:
```
regexprep(myCol(index),'\D','')
```
Convert the strings of numbers to numeric values. You can do this for all of your indexed cells using the function STR2DOUBLE:
```
str2double(regexprep(myCol(index),'\D',''))
```

The final result of the above can then be combined with the original numeric values in myCol. Putting it all together, you get the following:

>> index = cellfun(@ischar,myCol);
>> result(index,1) = str2double(regexprep(myCol(index),'\D',''));
>> result(~index) = [myCol{~index}]

result =

   200
   300
   450
   500
   200
   100

Collectives™ on Stack Overflow

Convert nonuniform cell array to numeric array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related