Skip to content

Columns to Text in R

This is not an Excel feature, although it should be! Often times we have a list of things in a column that needs to be combined into, say, a comma-delimited string. Column to Text in R…

Column to Text in Excel

That column looks like this:

Typically I write a quick formula that cumulatively concacatenates the rows, something like this:

That last cell is what I want. Usually it’s to plug it into a SQL query or something.

Column to Text in R

This is super easy in R. Let’s say you have a vector like this:

col = c(156863,141205,481808,515823,648410,656289,900801)

This vector could have been sourced from an existing data frame. We can use the paste() function which is used for combining strings.

string = paste(col, collapse = ", ")
string
length(string)
[1] "156863, 141205, 481808, 515823, 648410, 656289, 900801"
[1] 1

And there you see that after we apply the paste() function with collapse, we get a vector with one single value, and that value is a comma-separated string of customer IDs.

Now that string can be plugged into a SQL query or elsewhere.

Deduping that list

If we do need this for a SQL query (like a WHERE clause), we should probably remove duplicates. R really starts to shine here compared to Excel.

Just put unique(col) into the paste() function instead:

# collapse the col vector into a string
string = paste(unique(col), collapse = ", ")

As with all nested functions, read from inside-out. First we get a unique vector of col values, and then we plug that into paste().

Leave a Reply

Your email address will not be published. Required fields are marked *