Concatinate text (header) and numbers
1 Ansicht (letzte 30 Tage)
Ältere Kommentare anzeigen
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
0 Kommentare
Akzeptierte Antwort
Guillaume
am 22 Jun. 2018
Bearbeitet: Guillaume
am 22 Jun. 2018
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 Kommentare
Guillaume
am 25 Jun. 2018
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);
Weitere Antworten (1)
KSSV
am 22 Jun. 2018
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 Kommentare
KSSV
am 22 Jun. 2018
[num,txt,raw] = xlsread(mufile) ;
YOu can extract headers from txt.
Siehe auch
Kategorien
Mehr zu Tables finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!