Column 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:

Column to Text in R

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

Column to Text in Excel

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. Always remembering that a dataframe column is actually just a vector, we can use the paste() function which is used for combining strings.

# print the column vector just to check
col
length(col)
[1] 156863 141205 481808 515823 648410 656289 900801
[1] 7

There we see col is a vector of seven numbers.

# collapse the col vector into a string
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

Be the First to Comment!

avatar

wpDiscuz