When you have a data like the below.
And you want to concatenate the two columns to get something like this.
It seems to be pretty easy.
You can either use ‘Unite’ command to unite the two columns or use Mutate command with str_c function to concatenate the values from the two columns.
Unite Command
Select multiple columns and select ‘Unite’ from the column header menu.
Mutate Command with str_c function
But, it turned out that it was not as simple as I thought.
NA in the data messes all up.
The Unite command takes NA as “NA” text, so the result would be something like this.
And, the str_c function takes NA very seriously so that it will return NA if one of the concated columns happens to have NA.
Both of them don’t produce the output we wanted to have.
What Can We Do?
There are a few options to work around this, but the one I’m finding the most simple consists of the following two steps below.
- Unite two columns with Unite Command
- Remove “NA” related text
1. Unite two columns with Unite Command
Select multiple columns and select ‘Unite’ from the column header menu.
Make sure to remove the space after the comma for the separator.
We get a data like the below.
2. Remove “NA” related text
We’ll take three steps to remove “NA” characters and the comma that is either before “NA” or after “NA” from the data.
1st Step
Select ‘Work with Text’ -> ‘Remove Text (All)’ from the column header menu.
We want to remove “NA,”, note that there is a comma and space after “NA”.
2nd Step
And do the same thing, except this time we’re going to remove “,NA”.
3rd Step
Lastly, we’re going to remove just “NA”.
This will give you something like below.
