How to replace cell in a table with number?

조회 수: 32 (최근 30일)
Mike Buba
Mike Buba 2022년 6월 30일
댓글: Lars Svensson 2023년 3월 9일
Hi,
I have a table and some of the cell in a table are not numbers. How to replace them with numbers so I can plot them.
So far I have managed to replace '---OF---' and 'Error' with 'NaN', but not sure how to replace e.g. '-0.6629' with -0.6629 and all NaN and 'NaN' with 0.
CSV file is in the attachment and code is below.
T = readtable('Auto_20220630100635.csv');
H = height(T);
for i = 1: width(T)
if iscellstr(T.(i))
T.(i)(strcmp(T.(i),'---O F---')) = {'NaN'};
end
end
for i = 1: width(T)
if iscellstr(T.(i))
T.(i)(strcmp(T.(i),'Error')) = {'NaN'};
end
end
Thank you in advance.
P.S.
For replacing NaN with 0 I get error message when using isnan function.
>> T(isnan(T))=0;
Check for incorrect argument data type or missing argument in call to function 'isnan'.
>> for i= 1: width(T)
T.(i)(isnan(T.(i))) = 0;
end
Check for incorrect argument data type or missing argument in call to function 'isnan'.

채택된 답변

Chunru
Chunru 2022년 6월 30일
T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1050910/Auto_20220630100635.csv');
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
head(T)
ans = 8×14 table
StoreNo Time Urms_1 Urms_2 Urms_3 PF_1 PF_2 PF_3 FreqU_1 FreqU_2 FreqU_3 Uthd_1 Uthd_2 Uthd_3 _______ ________ ______ ______ ______ _____________ _____________ _____________ __________ __________ __________ ______ ______ ______ 1 10:06:36 125 125 127 {'---O F---'} {'---O F---'} {'---O F---'} {'Error' } {'Error' } {'Error' } NaN NaN NaN 2 10:06:37 125 125 127 {'---O F---'} {'---O F---'} {'---O F---'} {'Error' } {'Error' } {'Error' } NaN NaN NaN 3 10:06:38 125 125 127 {'---O F---'} {'---O F---'} {'---O F---'} {'Error' } {'Error' } {'Error' } NaN NaN NaN 4 10:06:40 125 125 127 {'-0.6629' } {'-0.9785' } {'-0.9353' } {'861.08'} {'500.06'} {'690.31'} 63.275 89.266 65.475 5 10:06:41 124 125 127 {'-0.6472' } {'-0.9499' } {'-0.9676' } {'792.82'} {'528.58'} {'672.35'} 65.499 93.551 72.373 6 10:06:42 125 125 127 {'-0.6346' } {'-0.9049' } {'-0.9782' } {'787.57'} {'537.42'} {'680.75'} 61.815 95.827 62.228 7 10:06:43 125 125 127 {'-0.6199' } {'-0.8868' } {'-0.9808' } {'855.82'} {'516.86'} {'665.06'} 66.628 96.359 64.222 8 10:06:44 125 125 127 {'-0.6113' } {'-0.8492' } {'-0.9826' } {'829.57'} {'519.75'} {'630.33'} 63.682 94.031 60.312
T.PF_1 = cellfun(@(x) str2double(strrep(x, '---O F---', 'nan')), T.PF_1);
T.PF_2 = cellfun(@(x) str2double(strrep(x, '---O F---', 'nan')), T.PF_2);
T.PF_3 = cellfun(@(x) str2double(strrep(x, '---O F---', 'nan')), T.PF_3);
T.FreqU_1 = cellfun(@(x) str2double(strrep(x, 'Error', 'nan')), T.FreqU_1);
% and so on
T
T = 15×14 table
StoreNo Time Urms_1 Urms_2 Urms_3 PF_1 PF_2 PF_3 FreqU_1 FreqU_2 FreqU_3 Uthd_1 Uthd_2 Uthd_3 _______ ________ ______ ______ ______ _______ _______ _______ _______ __________ __________ ______ ______ ______ 1 10:06:36 125 125 127 NaN NaN NaN NaN {'Error' } {'Error' } NaN NaN NaN 2 10:06:37 125 125 127 NaN NaN NaN NaN {'Error' } {'Error' } NaN NaN NaN 3 10:06:38 125 125 127 NaN NaN NaN NaN {'Error' } {'Error' } NaN NaN NaN 4 10:06:40 125 125 127 -0.6629 -0.9785 -0.9353 861.08 {'500.06'} {'690.31'} 63.275 89.266 65.475 5 10:06:41 124 125 127 -0.6472 -0.9499 -0.9676 792.82 {'528.58'} {'672.35'} 65.499 93.551 72.373 6 10:06:42 125 125 127 -0.6346 -0.9049 -0.9782 787.57 {'537.42'} {'680.75'} 61.815 95.827 62.228 7 10:06:43 125 125 127 -0.6199 -0.8868 -0.9808 855.82 {'516.86'} {'665.06'} 66.628 96.359 64.222 8 10:06:44 125 125 127 -0.6113 -0.8492 -0.9826 829.57 {'519.75'} {'630.33'} 63.682 94.031 60.312 9 10:06:45 125 125 127 -0.6349 -0.8459 -0.9778 855.83 {'496.27'} {'680.75'} 68.427 96.569 63.953 10 10:06:46 124 125 127 -0.6653 -0.8344 -0.9539 643.63 {'521.94'} {'664.5' } 71.365 90.878 68.531 11 10:06:48 125 125 127 NaN NaN NaN NaN {'Error' } {'Error' } NaN NaN NaN 12 10:06:49 125 125 127 NaN NaN NaN NaN {'Error' } {'Error' } NaN NaN NaN 13 10:06:50 125 125 127 NaN NaN NaN NaN {'Error' } {'Error' } NaN NaN NaN 14 10:06:51 125 125 127 NaN NaN NaN NaN {'Error' } {'Error' } NaN NaN NaN 15 10:06:52 125 125 127 NaN NaN NaN NaN {'Error' } {'Error' } NaN NaN NaN
  댓글 수: 2
Mike Buba
Mike Buba 2022년 6월 30일
Thank you very much.
Is there a way to do it for a whole table, not just column by column? I have 100+ signals in the table and this was only a small work example
Chunru
Chunru 2022년 7월 1일
You can loop through table columns:
T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1050910/Auto_20220630100635.csv');
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
T
T = 15×14 table
StoreNo Time Urms_1 Urms_2 Urms_3 PF_1 PF_2 PF_3 FreqU_1 FreqU_2 FreqU_3 Uthd_1 Uthd_2 Uthd_3 _______ ________ ______ ______ ______ _____________ _____________ _____________ __________ __________ __________ ______ ______ ______ 1 10:06:36 125 125 127 {'---O F---'} {'---O F---'} {'---O F---'} {'Error' } {'Error' } {'Error' } NaN NaN NaN 2 10:06:37 125 125 127 {'---O F---'} {'---O F---'} {'---O F---'} {'Error' } {'Error' } {'Error' } NaN NaN NaN 3 10:06:38 125 125 127 {'---O F---'} {'---O F---'} {'---O F---'} {'Error' } {'Error' } {'Error' } NaN NaN NaN 4 10:06:40 125 125 127 {'-0.6629' } {'-0.9785' } {'-0.9353' } {'861.08'} {'500.06'} {'690.31'} 63.275 89.266 65.475 5 10:06:41 124 125 127 {'-0.6472' } {'-0.9499' } {'-0.9676' } {'792.82'} {'528.58'} {'672.35'} 65.499 93.551 72.373 6 10:06:42 125 125 127 {'-0.6346' } {'-0.9049' } {'-0.9782' } {'787.57'} {'537.42'} {'680.75'} 61.815 95.827 62.228 7 10:06:43 125 125 127 {'-0.6199' } {'-0.8868' } {'-0.9808' } {'855.82'} {'516.86'} {'665.06'} 66.628 96.359 64.222 8 10:06:44 125 125 127 {'-0.6113' } {'-0.8492' } {'-0.9826' } {'829.57'} {'519.75'} {'630.33'} 63.682 94.031 60.312 9 10:06:45 125 125 127 {'-0.6349' } {'-0.8459' } {'-0.9778' } {'855.83'} {'496.27'} {'680.75'} 68.427 96.569 63.953 10 10:06:46 124 125 127 {'-0.6653' } {'-0.8344' } {'-0.9539' } {'643.63'} {'521.94'} {'664.5' } 71.365 90.878 68.531 11 10:06:48 125 125 127 {'---O F---'} {'---O F---'} {'---O F---'} {'Error' } {'Error' } {'Error' } NaN NaN NaN 12 10:06:49 125 125 127 {'---O F---'} {'---O F---'} {'---O F---'} {'Error' } {'Error' } {'Error' } NaN NaN NaN 13 10:06:50 125 125 127 {'---O F---'} {'---O F---'} {'---O F---'} {'Error' } {'Error' } {'Error' } NaN NaN NaN 14 10:06:51 125 125 127 {'---O F---'} {'---O F---'} {'---O F---'} {'Error' } {'Error' } {'Error' } NaN NaN NaN 15 10:06:52 125 125 127 {'---O F---'} {'---O F---'} {'---O F---'} {'Error' } {'Error' } {'Error' } NaN NaN NaN
for i=3:size(T,2) % from culum 3 onwards
if iscell(T{:, i})
T{:, i} = strrep(T{:, i}, '---O F---', 'nan');
T{:, i} = strrep(T{:, i}, 'Error', 'nan');
T.(T.Properties.VariableNames{i}) = cellfun(@(x) str2double(x), T{:, i});
end
end
% and so on
T
T = 15×14 table
StoreNo Time Urms_1 Urms_2 Urms_3 PF_1 PF_2 PF_3 FreqU_1 FreqU_2 FreqU_3 Uthd_1 Uthd_2 Uthd_3 _______ ________ ______ ______ ______ _______ _______ _______ _______ _______ _______ ______ ______ ______ 1 10:06:36 125 125 127 NaN NaN NaN NaN NaN NaN NaN NaN NaN 2 10:06:37 125 125 127 NaN NaN NaN NaN NaN NaN NaN NaN NaN 3 10:06:38 125 125 127 NaN NaN NaN NaN NaN NaN NaN NaN NaN 4 10:06:40 125 125 127 -0.6629 -0.9785 -0.9353 861.08 500.06 690.31 63.275 89.266 65.475 5 10:06:41 124 125 127 -0.6472 -0.9499 -0.9676 792.82 528.58 672.35 65.499 93.551 72.373 6 10:06:42 125 125 127 -0.6346 -0.9049 -0.9782 787.57 537.42 680.75 61.815 95.827 62.228 7 10:06:43 125 125 127 -0.6199 -0.8868 -0.9808 855.82 516.86 665.06 66.628 96.359 64.222 8 10:06:44 125 125 127 -0.6113 -0.8492 -0.9826 829.57 519.75 630.33 63.682 94.031 60.312 9 10:06:45 125 125 127 -0.6349 -0.8459 -0.9778 855.83 496.27 680.75 68.427 96.569 63.953 10 10:06:46 124 125 127 -0.6653 -0.8344 -0.9539 643.63 521.94 664.5 71.365 90.878 68.531 11 10:06:48 125 125 127 NaN NaN NaN NaN NaN NaN NaN NaN NaN 12 10:06:49 125 125 127 NaN NaN NaN NaN NaN NaN NaN NaN NaN 13 10:06:50 125 125 127 NaN NaN NaN NaN NaN NaN NaN NaN NaN 14 10:06:51 125 125 127 NaN NaN NaN NaN NaN NaN NaN NaN NaN 15 10:06:52 125 125 127 NaN NaN NaN NaN NaN NaN NaN NaN NaN

댓글을 달려면 로그인하십시오.

추가 답변 (1개)

Lars Svensson
Lars Svensson 2023년 3월 8일
You may want to use
T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1050910/Auto_20220630100635.csv');
T1 = convertvars(T,@iscell,'string');
T2 = convertvars(T1,@isstring,'double');
T2
  댓글 수: 4
Stephen23
Stephen23 2023년 3월 9일
"Thanks for the improvement!"
I did not say improvement! My goal was just to show another option for future readers, and to give something to think about. Each approach will be suitable for different situations and data: it is quite possible that your approach is faster (string operations are highly optimised), and for someone whose text data are string type, then your approach would probably be the best. So not an "improvement", just different.
Lars Svensson
Lars Svensson 2023년 3월 9일
OK. Thanks.

댓글을 달려면 로그인하십시오.

카테고리

Help CenterFile Exchange에서 Characters and Strings에 대해 자세히 알아보기

태그

제품


릴리스

R2022a

Community Treasure Hunt

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

Start Hunting!

Translated by