How to Fill NA with Values from Other Columns
Sometimes you might have NAs in one column and want to fill them with values from another column.
Take a look at the data below.
For example, the 30th row has NA for ‘viz_path1’ column, and I want to fill it with the value from the next column ‘viz_path2’.
We can use a function called coalesce to do this.
coalesce(viz_path1, viz_path2)The coalesce function here evaluates if ‘viz_path1’ for each row is NA or not and copy a value from viz_path2 column if it is NA.
Here’s how you can do in Exploratory.
Fill NA with Another Columns
You can create the above calculation with the ‘Create Calculation (Mutate)’ step.
Select ‘Create Calculation (Mutate)’ from the column header menu.
Then, type the function like below.
You will see NAs in ‘viz_path1’ being filled with values from viz_path2.
Except, the row 39 still has NA!
This is because even ‘viz_path2’ column is NA.
Fill NA with One of the Columns
Now, in this case, I want to copy from another column, ‘viz_path3’.
Basically, what I want to do is to fill NA for a given column, in this case, that is ‘viz_path1’ with values from one of the other columns as long as it is non-NA.
And, the coalesce function can handle this very well. All you need to do is to list up all the possible columns that you want to copy the values from. For example, I can type like below.
coalesce(viz_path1, viz_path2, viz_path3)Click the token, at which we created the original calculation previously, to open the ‘Create Calculation (Mutate)’ dialog.
Then, update the calculation.
Now, we can see all the NAs under ‘viz_path1’ being filled with the values from either ‘viz_path2’ or ‘viz_path3’ column.
