(In excel) Reading rows with text, in between continuous rows of numbers, by 'readtable' function?

1 view (last 30 days)
I have an excel sheet. I read the data using 'readtable' function. All the rows which are either empty or containing text are read as 'NaN'. I want to read these text (which occupy a complete row of the excel sheet, in between the numebers) in the excel sheet. After reading I want to assign these text to the corresponding data. They are like headers to the following numerical values.
Thanks in advance.
  6 Comments
Jonnalagadda Suvardhan
Jonnalagadda Suvardhan on 18 May 2022
The first one works now. (t=t(strlength(t)>0))
I dont understand why I got that error message, earlier.
Thanks for the help.

Sign in to comment.

Accepted Answer

dpb
dpb on 16 May 2022
Edited: dpb on 16 May 2022
readtable() can't do that -- each column must be the same data type -- or a cell which can contain either, but then would have to import as cellstr or string; the numeric values won't/can't be numeric excepting in a cell.
You'll have to do the initial import with readcell or with readtable as all string/char and fix up after the fact.
But, the underlying problem will still be there; the table isn't designed for such data.
Depending upon what is needed to be done, one might be able to create a container table where each row is the subtable -- that probably would end up being a pain to work with but could be done.
As always, a sample file attached would let somebody at least see the problem and a more complete description of what is needed/next in using the result.
Just happen to still have a small table in memory from a previous Answer, so here's illustration of the problem you'll run into -- and, there really was a missing value there to begin with, didn't even have to fudge it! :)
>> tCoords.Latitude(tCoords.Latitude==0)=nan
tCoords =
8×4 table
Latitude Longitude Lat Lon
________________ _________ ________________ _______________
125.772230521827 93 {'125.77223052'} {'93.00000000'}
110.870680472275 77 {'110.87068047'} {'77.00000000'}
125.284599897971 83 {'125.28459990'} {'83.00000000'}
118.8839686379 75 {'118.88396864'} {'75.00000000'}
NaN 80 {'123.58952619'} {'80.00000000'}
121.582916053722 70 {'121.58291605'} {'70.00000000'}
131.078778687756 88 {'131.07877869'} {'88.00000000'}
115.688767857832 82 {'115.68876786'} {'82.00000000'}
>> tCoords.Latitude(isnan(tCoords.Latitude))={'Heading'}
Conversion to double from cell is not possible.
>>
As you see, it won't let you put the nonumeric data into the double array that is the content of the table 'Latitude' variable. This illustrates the other point as well, actually -- the last two columns are the same data but as cellstr formatted to have a fixed number of decimal digits. This was illustrative to that particular Q? that was putting the data into a UITABLE in a gui but needed more precision than is given by the builtin numeric formatting rules so had to convert to/from numeric. Something like that is all you'll be able to do on import; you then could do
>> tCoords.Lat(isnan(tCoords.Latitude))={'Heading'}
tCoords =
8×4 table
Latitude Longitude Lat Lon
________________ _________ ________________ _______________
125.772230521827 93 {'125.77223052'} {'93.00000000'}
110.870680472275 77 {'110.87068047'} {'77.00000000'}
125.284599897971 83 {'125.28459990'} {'83.00000000'}
118.8839686379 75 {'118.88396864'} {'75.00000000'}
NaN 80 {'Heading' } {'80.00000000'}
121.582916053722 70 {'121.58291605'} {'70.00000000'}
131.078778687756 88 {'131.07877869'} {'88.00000000'}
115.688767857832 82 {'115.68876786'} {'82.00000000'}
>>
The closest you can get is
>> tCoords.Lat=num2cell(tCoords.Latitude,11);
>> tCoords.Lat(isnan(tCoords.Latitude))={'Heading'}
tCoords =
8×4 table
Latitude Longitude Lat Lon
________________ _________ ____________________ _______________
125.772230521827 93 {[125.772230521827]} {'93.00000000'}
110.870680472275 77 {[110.870680472275]} {'77.00000000'}
125.284599897971 83 {[125.284599897971]} {'83.00000000'}
118.8839686379 75 {[ 118.8839686379]} {'75.00000000'}
NaN 80 {'Heading' } {'80.00000000'}
121.582916053722 70 {[121.582916053722]} {'70.00000000'}
131.078778687756 88 {[131.078778687756]} {'88.00000000'}
115.688767857832 82 {[115.688767857832]} {'82.00000000'}
>>
Which looks like what you've described but it's fraught with problems in use--
>> tCoords.Lat
ans =
8×1 cell array
{[125.772230521827]}
{[110.870680472275]}
{[125.284599897971]}
{[ 118.8839686379]}
{'Heading' }
{[121.582916053722]}
{[131.078778687756]}
{[115.688767857832]}
>>
shows you get what you've created -- a cell array of disparate data types -- now when you try to dereference these to do something with them, you've got a problem, Houston!
>> mean(tCoords.Lat)
Error using sum
Invalid data type. First argument must be numeric or logical.
Error in mean (line 127)
y = sum(x, dim, flag) ./ mysize(x,dim);
>>
Yeah, it's a cell array, just happens to have some numbers in some cells...let's try to dereference with "the curlies" {}
>> mean(tCoords.Lat{:})
Error using mean
Too many input arguments.
>>
Yeah, that returns a list, not an array...
>> mean([tCoords.Lat{:}])
ans =
109.5
>>
Oohhh! We got a number, but it can't be the mean of those; it's smaller than the smallest of any one number!! The answer (well, at least one answer) is...
nanmean(tCoords.Latitude)
ans =
121.308848875612
>>
So, what did we average instead????
([tCoords.Lat{:}])
ans =
'}n}vHeadingyƒs'
>>
That doesn't look at all like what we were after...MATLAB got totally confused by that...let's try a different subterfuge...
>> mean(vertcat(tCoords.Lat{:}))
Error using vertcat
Dimensions of arrays being concatenated are not consistent.
>>
That's because the string data is N characters long trying to smoosh together with the 1-vectors of the numbers.
All in all, it's just a bad idea -- you'll need to normalize these data to either handle as separate pieces or, if they are same data with some other ID, that ID needs to be added as a variable/category (or set thereof) for each observation in the overall dataset.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!