Replacing -999 in a table to NaN regardless of the type of the column
Show older comments
Hi,
I have table Final with -999 and -9999 representing missing values (Data attached). I would like to replace these -999 with NaN regardless of the type of the column (double or string).I tried
Final= convertvars(Final, @isnumeric, @no999);
function x = no999(x)
x(x==-999) = nan;
end
But still it shows -999 values. Any hint about how to do it?
3 Comments
Mathieu NOE
on 29 Jan 2021
hi
I tried this but I am 100% sure yet if it works ...
str = load('matlab.mat')
MyTable = table2cell(str.Final);
StringIndex = cellfun(@(x) isnumeric(x) && (x == -999 || x == -9999), MyTable); % true for elements of MyTable that are numerical scalars = -999 or -9999
MyTable(StringIndex) = {'NaN'}; % so nan instaed
Wesso
on 29 Jan 2021
dpb
on 29 Jan 2021
I'd strongly suggest to first clean up the categorical variables to normalize the categories, eliminating the instances of misspellings and spacing as well as inconsistency in encoding...for example, the "I don't know" category for variable org04b looks the same but
>> sort(categories(Final.org04b))
ans =
46×1 cell array
{'-999' }
{'-9999' }
{'150 million USD or above' }
{'150 million usd or above' }
{'Betweeen 1 million and 4.9 million USD' }
{'Between 1 million USD and 4.9 million USD'}
{'Between 1 million and 4 million usd' }
{'Between 1 million and 4.9 million USD' }
{'Between 1 million and 4.9 million uSD' }
{'Between 1 million and 4.9 million usd' }
{'Between 10 million and 24 million usd' }
{'Between 10 million and 24.9 million USD' }
{'Between 10 million and 24.9 million usd' }
{'Between 10 million and 249.9 million USD' }
{'Between 100 000 USD and 249 999 USD' }
{'Between 100 000 usd and 249 999 USD' }
{'Between 100 USD and 249 999 USD' }
{'Between 100 million and 149 million usd' }
{'Between 100 million and 149.9 million USD'}
{'Between 100,000 usd and 249,000 usd' }
{'Between 25 million and 49 million usd' }
{'Between 25 million and 49.9 million USD' }
{'Between 25 million and 49.9 million usd' }
{'Between 250 000 USD and 499 999 USD' }
{'Between 250 000 usd and 499 999 USD' }
{'Between 250,000 usd and 499,000 usd' }
{'Between 250,000 usd and 499,999 usd' }
{'Between 5 million and 9 million usd' }
{'Between 5 million and 9.9 million USD' }
{'Between 5 million and 9.9 million usd' }
{'Between 50 000 USD and 99 999 USD' }
{'Between 50 000 usd and 99 999 USD' }
{'Between 50 million and 99 million usd' }
{'Between 50 million and 99.9 million USD' }
{'Between 50 million and 99.9 million usd' }
{'Between 50,000 usd and 99,000 usd' }
{'Between 50,000 usd and 99,999 usd' }
{'Between 500 000 USD and 999 999 USD' }
{'Between 500 000 usd and 999 999 USD' }
{'Between 500,000 usd and 999,000 usd' }
{'Between 500,000 usd and 999,999 usd' }
{'I don't know' }
{'I don’t know' }
{'Under 50 000 USD' }
{'Under 50,000 usd' }
{'org04a' }
>> tmp=ans(42:43)
tmp =
2×1 cell array
{'I don't know'}
{'I don’t know'}
>> strcmp(tmp(1),tmp(2))
ans =
logical
0
>> [double(tmp{1});double(tmp{2})]
ans =
73 32 100 111 110 39 116 32 107 110 111 119
73 32 100 111 110 8217 116 32 107 110 111 119
>>
I'd create them first from the numerical data and have the matching to the missing value indicators taken care of there. Then the ismissing function will work.
As this is, you'll play the devil in trying to retrieve all elements of a given level because the categories aren't unique for what are supposed to be the same thing.
Accepted Answer
More Answers (0)
Categories
Find more on Data Type Identification in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!