Processing data of different classes read from the same column in an excel file

3 views (last 30 days)
Saeid
Saeid on 5 Feb 2019
Answered: Saeid on 5 Feb 2019
Usually when I read large amounts of data from excel files the format of data in every column are the same. For example, a targetted column only contains numerical data. However, sometimes the same column alternately contains data that are not in the numerical format. For example, inbetween normal numerical values the data happens to have been entered (let's say by some operator) as fractions:
1,50
2,50
0,40
1/3
1/5
1/3
1/4
0,10
0,30
4,50
This causes a stop in the assignment of the values to an array because assigned data have to be of the same type. Naturally for a small table I could manually change these fractions (1/3, 1/5 etc.) into their nuzmerical values but for an extremely large dataset this can be very cumbersome.
How can I write get Matlab to detect these "fractions" in the columns and recalculate them into their numerical equivalents?

Answers (2)

qrqr
qrqr on 5 Feb 2019
Edited: qrqr on 5 Feb 2019
For example, if A = {1, 2} is 2/4 as a character string when reading from Excel to MATLAB
If you do str2num (A {1,2}), it will be the numerical value of 0.5
A = '2/4'
%ans '2/4'
a = 2/4
%ans 0.5
str2num(A)
%ans 0.5
Can I do this by matrix for for statement?
If sr2num is set for intermediate values, an error will occur, so it is good to use [isnumeric] to determine the numerical value
A = {'2/4',[2/4];...
'2/6',[2/6];...
'3/9',[3/9]};
%A{1,2} → 0.5
%A{2,2} → 0.333
%A{3,2} → 0.333
%A{1,1} → '2/4'
%A{2,1} → '2/6'
%A{3,1} → '3/9'
[x,y] = size(A);% Examine the number of matrices
%In this example x=2 y=3
for i = 1:y
for k = 1:x
if isnumeric(A{i,k}) == 0
A{i,k} = str2num(A{i,k})
end
end
end
%result
%A{1,2} → 0.5
%A{2,2} → 0.333
%A{3,2} → 0.333
%A{1,1} → 0.5
%A{2,1} → 0.333
%A{3,1} → 0.333

Saeid
Saeid on 5 Feb 2019
青野さん、答えありがとうございます。
Actually, I usually read all the data in the raw format as:
[~,~,rawdata]=xlsread('filename.xls',1)
And then turn the individual columns into numerical data using cell2mat. I guess I need to reformulate my question into a new one, but thanks anyway.

Community Treasure Hunt

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

Start Hunting!