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
采纳的回答
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 个评论
Andrei Bobrov
2018-6-22
+1. Very good!
krai
2018-6-25
Walter Roberson
2018-6-25
I suggest using the TreatAsEmpty option for the readtable() -- but first try using detectImportOptions()
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);
krai
2018-6-27
更多回答（1 个）
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
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Translated by 
