How to replace cell in a table with number?

4 views (last 30 days)
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'.

Accepted Answer

Chunru
Chunru on 30 Jun 2022
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 Comments
Chunru
Chunru on 1 Jul 2022
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

Sign in to comment.

More Answers (0)

Categories

Find more on Cell Arrays in Help Center and File Exchange

Products


Release

R2022a

Community Treasure Hunt

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

Start Hunting!

Translated by