Concatinate text (header) and numbers

I have a excel file with data as shown,

Time  Exp1  Exp2

0.1  26  965

0.2  23  966

0.3  25  963

0.4  NA  956

0.5  24  951

0.6  26  944

Now I want to write the processed data (replacing the NA with the average of previous and next cell) with the header to an excel file as shown below

Time  Exp1  Exp2

0.1   26   965

0.2   23   966

0.3   25   963

0.4   24.5   956

0.5   24   951

0.6   26   944

I have used the code,

[num, text] = xlsread('test.xlsx');

numfill = fillmissing(num,'linear'); % fill missing cells, replaces NA with the average of previous and next cell

text1=text(1,:);                     %  since the text has "NA" strings as well

T = [numfill ; text1]

when I read the excel initially using the below mentioned code,

[num, text] = xlsread('test.xlsx');

"NA" in the excel is also considered as a string as shown below,

text = 5×3 cell array

    'Time'    'Exp1'    'Exp2'
    ''        ''        ''    
    ''        ''        ''    
    ''        ''        ''    
    ''        'NA'      ''

That is the reason I have used this

text1=text(1,:);        % to consider only the first row of the array.

But when I run the code I get the error,

Error using vertcat Dimensions of matrices being concatenated are not consistent.

Error in Untitled3 (line 5) T = [numfill ; text1]

Please let me know what am I doing wrong?

Thanks

Answer 1

[Guillaume]

A much simpler approach is to use the modern readtable instead of the ancient xlsread. readtable will recognise the header and use it to name the column, so it won't be part of the data. And it will give you all your data into just one table with columns of the appropriate type instead of splitting it into numeric and text data.

You can then use fillmissing directly on the table. So:

t = readtable('test.xlsx');
t = fillmissing(t, 'linear');

All done!

[Andrei Bobrov]

+1. Very good!

[krai]

Hi Guillaume

I am getting an error using fillmissing, when I use readtable

Error using fillmissing/checkArrayType (line 502)
  Table contains variables of types for which the 'linear' fill method is not supported.

    Error in fillmissing/fillTableVar (line 155)
    [intConstVj,extMethodVj] = checkArrayType(Avj,intMethod,intConstVj,extMethodVj,x,true);

    Error in fillmissing/fillTable (line 133)
            B.(vj) =
            fillTableVar(indVj,A.(vj),intMethod,intConst,extMethod,x,useJthFillConstant,useJthExtrapConstant);

    Error in fillmissing (line 116)
            B = fillTable(A,intM,intC,extM,x,dataVars);

    Error in Untitled3 (line 2)
    t = fillmissing(t,'linear');% fill missing cells, replaces NA with the average of previous and
    next cell

[Walter Roberson]

I suggest using the TreatAsEmpty option for the readtable() -- but first try using detectImportOptions()

[Guillaume]

As per Walter comment, you can either customise readtable so that it reads the data as you want straight away.

Otherwise, after the fact, yes you can't use fillmissing with text columns. You can restrict it to numeric columns very simply:

t = fillmissing(t, 'linear', 'DataVariables', @isnumeric);

[krai]

Thanks it worked

Answer 2

[KSSV]

A = [0.1  26  965
0.2  23  966
0.3  25  963
0.4  NaN  956
0.5  24  951
0.6  26  944] ;

for i = 2%1:size(A,2)
    idx = find(isnan(A(:,i))) ;
    for j = 1:length(idx)
        A(idx(j),i) = mean(A(1:idx(j)-1,i)) ;
    end      
end

[krai]

Hi KSSV,

I am using the code,

numfill = fillmissing(num,'linear');

to replace the NA with the Average value, which is working fine. The problem is when I try to concatenate the text and num arrays before exporting the final result to the excel file.

[KSSV]

A = [0.1  26  965
0.2  23  966
0.3  25  963
0.4  NaN  956
0.5  24  951
0.6  26  944] ;

B = fillmissing(A,'linear'); 
str = {'Time'  'Exp1'  'Exp2'} ;
T = [str ; num2cell(B)]

[krai]

Thanks KSSV,

How do I do it, if I want to use the same code for different excel sheets with different data size and headers?

[KSSV]

[num,txt,raw] = xlsread(mufile) ;

YOu can extract headers from txt.