Replacing Null values with text

Hi there,

I've about 20 columns that contain Nulls here and there and I want to replace them with 'No Value'. They are inputs to a predictive model and so I don't want to use 0s.

The Imputation tool only seems to enable replacing Nulls with a value. Is there an equivalent tool for searching and replacing values with text that works across multiple variables and all rows?

Thanks in advance,

Paul

Best Practices

Preparation

Accepted answers

BenMoss

Hi Paul,

Yes this is possible using the multi-field formula tool which allows you to apply a formula,in your case something like

if isnull([_CurrentField_]) then "No Value" else [_CurrentiField_] endif

, to multiple columns.

Given that your current field type is likely to be a double then you would have to also use the 'change output type' select box also.

However you should be aware that it is unlikely that the predictive tools would then work on these fields as (depending on which tool you use), they are likely to require a field of numeric type.

Personally I would just leave them as NULLs.

Ben

All comments

BenMoss

Hi Paul,

pcatterson

You can add "No Value" to the field to replace the Nulls, but to do so you would convert the field from a number to a string. This would make the predictive model handle this data very differently and potentially disrupt how you plan to use the data. It would see the fields as categorical, no longer as continuous.

Imputation.yxmd

Markle

This was very helpful. Thank you!

BarryDeCicco

You would lose the ability to treat the field as a numeric variable; it could be treated as a categorical variable. Depending on the variable, you could bin the other values into categories. If you can set the missing flag as the reference level in your model, then the the coefficients of the other categories would be the 'effect' between each category level and 'missing'. This would be a good first look at the data.

Henrytabares

Thanks Guys! This made my day! Appreciated!!!!

sharfor

Thanks, Ben. The formula logic was a great help to me when applied to another context.

PaulinaBrewka

Great Idea BenMoss

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703