How-to | Fill empty cells of a column with the value of the corresponding row from another column#

Handling missing data is one data preparation challenge that analysts routinely face. Let’s review one way to fill in missing data.

In this example, we will fill the empty cells of a column with values of the corresponding rows from another column.

Coalesce function#

In a Prepare recipe, use the Formula processor with the coalesce() function as shown below:

../../_images/kb-coalesce-1.png

Here we fill the empty values of `col1` with the corresponding values of `col2` in a new column.#

You can also specify multiple columns, or even directly specify the missing values.

../../_images/dimitri_0-1588874053641.png

Here we fill the empty values of `col1` with the values of `col2`, or `0` when `col2` is also empty.#

The Formula language gives you the flexibility to achieve more customized tasks. For example, you can combine functions in the same expression.

../../_images/kb-coalesce-3.png

Here we fill the empty values of `col1` with the corresponding floored values of `col2` in a new column.#

See also

See this article and video to learn more about using Formulas in Dataiku.