Replace_NA

Function replaces missing values in selected columns using defined value.

Properties

Keywords

replace missing cells, fill missing cells by defined value, replace missing, replace_NA

See Also

Select_NA, Drop_NA, Fill_NA

Video-tutorial

How to Replace Empty (Missing) Excel Cells by Custom Value

When working with data often happens to us, part of the cells in the table is empty for various reasons. However, when analyzing this data, we need it to be filled in, either with a default value or a value indicating the missing values (e.g. NA, NULL or MISSING). For this purpose you can use the Replace_NA function in the Reshape.XL add-in. An example of its practical application is in the following figure.

For the examples below, we will use the following sample dataset. As can be seen from the figure, the table contains several empty cells.

To fill these cells with the default value, use the Replace_NA function from the ribbon toolbar Variables (under the Missing button) tab.

The function parameters are defined in the side panel. There are two properties - Value and Variables for Replacing. Use the Value parameter to define the value you want to use to fill the empty cells. Within the Variables for Replacing parameter, you define the variables (columns) in which you want to fill empty cells.

The following figure shows an example where we used the “{NULL}” string for filling empty cells in two string variables - CAT_A and Str. You must always be careful about the format of the variable you want to fill in. For example, you cannot add a text string to a numeric column, because an error occurs when inserting a value into an empty cell.

The following example shows the dual use of the Replace_NA function. When we first applied the function, we filled missing values in string variables (“{NULL}”). For the second use we filled numerical variables (ID - integer and Num - double) using -9999 as the default value.

Using Replace_NA is very easy and fast. When working with missing values, you can also use the Drop_NA, Fill_NA and Select_NA functions. If you need a more advanced type of functions for missing values processing, you can also use features from the Mutate group (Mutate, Mutate_If, If_Else, NA_If or Case_When).