Concatinate text (header) and numbers

Question

krai 2018-6-22

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/406891-concatinate-text-header-and-numbers

评论： krai 2018-6-27

I have a excel file with data as shown,

Time  Exp1  Exp2
1  26  965
2  23  966
3  25  963
4  NA  956
5  24  951
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
1   26   965
2   23   966
3   25   963
4   24.5   956
5   24   951
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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Guillaume 2018-6-22

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/406891-concatinate-text-header-and-numbers#answer_325796

编辑：Guillaume 2018-6-22

在 MATLAB Online 中打开

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!

5 个评论
显示 3更早的评论隐藏 3更早的评论

Guillaume 2018-6-25

在 MATLAB Online 中打开

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 2018-6-27

Thanks it worked

请先登录，再进行评论。

Answer 2

KSSV 2018-6-22

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/406891-concatinate-text-header-and-numbers#answer_325769

在 MATLAB Online 中打开

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