How to create a new column based on existing column and conditions

Let’s take a look at a way to create a new column based on existing column and conditions

Suppose there is a column called Product Name in your data. If a product name contains “Phone”, you want to label it as “Phone”, if the product name contains “Microwave”, you want to label it as “Household Appliances”, and label it as “Others” otherwise.

To do so, open column header menu for Product Name Column, select Replace / Fill / Convert Data then select Replace Values Conditionally.

Then Mutate dialog is opened and some expression is already filled in like below.

This default expression uses case_when function and it accepts “condition” and “value” pairs, which are connected with “~”, as arguments like below.

Condition#1 ~ Value when Condition#1 is met,
Conditon2#2 ~ Value when Condition#2 is met,
...

As for this default expression, it means that when `Product Name` == “A” (i.e. Product Name is “A”), it returns “Result when it is A”, When `Product Name` == “B” (i.e. Product Name is “B”), it returns “Result when it is B”, and since the last condition is TRUE, it is always satisfied when above two conditions are not met and returns “Result when it is neither of them”.

So with this example, we want to check if the Product Name contains “Phone” or “Microwave”, and show a value based on the result of these conditions.

To see if the Product Name Column contains “Phone” or not, you can use str_detect function which tells us if the text contains the strings that we pass to the function as an argument like below.

str_detect(`Product Name` , "Phone")

You can use same function to detect “Microwave” in the Product Name as follows.

str_detect(`Product Name` , "Microwave")

If you fit these conditions with the values that you want to see as matching result, you can pass both conditions and result values as pairs like below.

case_when(
   str_detect(`Product Name` , "Phone") ~ "Phone",
   str_detect(`Product Name` , "Microwave") ~ "Household Appliances",
   TRUE ~ "Others"
)

Once you enter the expression, click Run button.

Then you’ll get a new column that has the derived value.