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

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.

[dpb]

See the detailed Answer why the above is abadidea™ but for any real help attach a representative data file.

[Jonnalagadda Suvardhan]

Hi, included the sample file for better understanding. My apologies.

So, the 'text' row is what I need to seperate.

Trial 1:

[~,txtData] = xlsread('All_absorbance_data.xlsx','Sheet1','A1:A600')

Using the above line I was able to seperate the 'text' in a cell array.

However, the numeric data rows were zero. And I want to delete those empty cells and retian my 'text' so that I can call them in a for loop as and when required.

Hope this helps. Thank you.

[dpb]

txtData=txtData(strlength(txtData>0));

[Jonnalagadda Suvardhan]

Operator '>' is not supported for operands of type 'cell'.

Since they are 'cells', I am getting the above mentioned error.

[dpb]

>> [~,t]=xlsread('Sample_matlab.xlsx');
>> t=t(strlength(t)>0)
t =
  2×1 cell array
    {'Text1'}
    {'Text2'}
>> 

works fine here. What release of MATLAB are you on? This is R2020b.

If you have R2016b or later, the simplest workaround would be

t=t(strlength(string(t))>0);

which will leave you w/ a string array.

Failing that,

>> t=t(cellfun(@(s)strlength(s)>0,t))
t =
  2×1 cell array
    {'Text1'}
    {'Text2'}
>> 

[Jonnalagadda Suvardhan]

The first one works now. (t=t(strlength(t)>0))

I dont understand why I got that error message, earlier.

Thanks for the help.

Answer 1

[dpb]

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.