Concatinate text (header) and numbers

1 次查看（过去 30 天）
krai
krai 2018-6-22
评论： krai 2018-6-27
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

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

采纳的回答

Guillaume
Guillaume 2018-6-22
编辑：Guillaume 2018-6-22
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更早的评论
Guillaume
Guillaume 2018-6-25
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);

请先登录，再进行评论。

更多回答（1 个）

KSSV
KSSV 2018-6-22
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
  4 个评论
显示 2更早的评论
krai
krai 2018-6-22
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
KSSV 2018-6-22
[num,txt,raw] = xlsread(mufile) ;
YOu can extract headers from txt.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Data Type Identification 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by