How to replace cell in a table with number?

29 views (last 30 days)
Mike Buba
Mike Buba on 30 Jun 2022
Commented: Lars Svensson on 9 Mar 2023
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'.

Sign in to answer this question.

Accepted Answer

Chunru
Chunru on 30 Jun 2022
Ran in:
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
Mike Buba
Mike Buba on 30 Jun 2022
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 on 1 Jul 2022
Ran in:
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 (1)

Lars Svensson
Lars Svensson on 8 Mar 2023
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 Comments
Show 2 older comments
Stephen23
Stephen23 on 9 Mar 2023
"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.

Sign in to comment.

Categories

Find more on Logical in Help Center and File Exchange

Tags

Products


Release

R2022a

Community Treasure Hunt

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

Start Hunting!