Concatenate row-wise across specific columns of dataframe

Question

I have a data frame with columns that, when concatenated (row-wise) as a string, would allow me to partition the data frame into a desired form.

> str(data)
'data.frame':   680420 obs. of  10 variables:
 $ A              : chr  "2011-01-26" "2011-01-26" "2011-02-09" "2011-02-09" ...
 $ B              : chr  "2011-01-26" "2011-01-27" "2011-02-09" "2011-02-10" ...
 $ C              : chr  "2011-01-26" "2011-01-26" "2011-02-09" "2011-02-09" ...
 $ D              : chr  "AAA" "AAA" "BCB" "CCC" ...
 $ E              : chr  "A00001" "A00002" "B00002" "B00001" ...
 $ F              : int  9 9 37 37 37 37 191 191 191 191 ...
 $ G              : int  NA NA NA NA NA NA NA NA NA NA ...
 $ H              : int  4 4 4 4 4 4 4 4 4 4 ...

For each row, I would like to concatenate the data in columns F, E, D, and C into a string (with the underscore character as separator). Below is my unsuccessful attempt at this:

data$id <- sapply(as.data.frame(cbind(data$F,data$E,data$D,data$C)), paste, sep="_")

And below is the undesired result:

  > str(data)
    'data.frame':   680420 obs. of  10 variables:
     $ A              : chr  "2011-01-26" "2011-01-26" "2011-02-09" "2011-02-09" ...
     $ B              : chr  "2011-01-26" "2011-01-27" "2011-02-09" "2011-02-10" ...
     $ C              : chr  "2011-01-26" "2011-01-26" "2011-02-09" "2011-02-09" ...
     $ D              : chr  "AAA" "AAA" "BCB" "CCC" ...
     $ E              : chr  "A00001" "A00002" "B00002" "B00001" ...
     $ F              : int  9 9 37 37 37 37 191 191 191 191 ...
     $ G              : int  NA NA NA NA NA NA NA NA NA NA ...
     $ H              : int  4 4 4 4 4 4 4 4 4 4 ...
     $ id             : chr [1:680420, 1:4] "9" "9" "37" "37" ...
      ..- attr(*, "dimnames")=List of 2
      .. ..$ : NULL
      .. ..$ : chr  "V1" "V2" "V3" "V4"

Any help would be greatly appreciated.

mightypile · Accepted Answer · 2017-05-14 16:26:01Z

70

Try

 data$id <- paste(data$F, data$E, data$D, data$C, sep="_")

instead. The beauty of vectorized code is that you do not need row-by-row loops, or loop-equivalent *apply functions.

Edit Even better is

 data <- within(data,  id <- paste(F, E, D, C, sep=""))

edited May 14, 2017 at 16:26

mightypile

8,1303 gold badges41 silver badges44 bronze badges

answered Jun 10, 2011 at 15:58

Dirk is no longer here

370k60 gold badges668 silver badges742 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jubbles Over a year ago

Wow. That was quick and correct. It looks like I was over-complicating the process. I am really trying to wean myself off of complete 'loop dependency' and am still learning to appreciate vectorized code.

Richie Cotton Over a year ago

And try with(data, paste(...)) for even more succinctness.

Dirk is no longer here Over a year ago

Correct. Or even within() as the less-loved cousin of with(). Amended my answer accordingly.

smci Over a year ago

So that's how within differs!?! (Why the hell wasn't it named with(..., modify=TRUE) ?)

Parsa Over a year ago

What if you use data$id <- paste(data[, c(1)], data[,c(2)]) oddly then the code doesn't do what expected. Is there something I'm missing here?

|

JelenaČuklina · Accepted Answer · 2016-08-09 15:11:40Z

13

Use unite of tidyr package:

require(tidyr)
data <- data %>% unite(id, F, E, D, C, sep = '_')

First parameter is the desired name, all next up to sep - columns to concatenate.

answered Aug 9, 2016 at 15:11

JelenaČuklina

3,7622 gold badges25 silver badges35 bronze badges

1 Comment

jessi Over a year ago

When I needed to collapse dynamic vectors by groups, this method was the only one that consistently worked as such mycols <- c("F","E","D","C") data <- data %>% unite_("id", mycols, sep = "_")

smci · Accepted Answer · 2019-06-23 17:06:29Z

7

Either stringr::str_c() or paste() will work.

require(stringr)
data <- within(data, str_c(F,E,D,C, sep="_")

or else

data <- within(data, paste(F,E,D,C, sep="_")

(stringr is better performance on large datasets)

edited Jun 23, 2019 at 17:06

answered Apr 4, 2014 at 23:30

smci

34.2k21 gold badges118 silver badges152 bronze badges

1 Comment

smci Over a year ago

@the serial downvoter: stringr::str_c can be used and noone said that. What's your problem?

Collectives™ on Stack Overflow

Concatenate row-wise across specific columns of dataframe

3 Answers 3

7 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related