paste two data.table columns

Question

dt <- data.table(L=1:5,A=letters[7:11],B=letters[12:16])
   L A B
1: 1 g l
2: 2 h m
3: 3 i n
4: 4 j o
5: 5 k p

Now I want to paste columns "A" and "B" to get a new one, let's call it "new":

dt2
   L A B new
1: 1 g l  gl
2: 2 h m  hm
3: 3 i n  in
4: 4 j o  jo
5: 5 k p  kp

If you use a data frame you can just use paste: `dt <- data.frame(L=1:5,A=letters[7:11],B=letters[12:16])` `dt$new <- paste0(dt$A,dt$B)` — dayne, Aug 21 '13 at 14:03
ok I just wanted to ask you to post it. I can't accept my own answer without waiting for 2 days... — beginneR, Aug 21 '13 at 14:36

score 24 · Answer 1 · edited Dec 14 '17 at 13:36

24

I had a similar issue but had many columns, and didn't want to type them each manually.

New version

(based on comment from @mnel)

dt[, new:=do.call(paste0,.SD), .SDcols=-1]

This is roughly twice as fast as the old version, and seems to sidestep the quirks. Note the use of .SDcols to identify the columns to use in paste0. The -1 uses all columns but the first, since the OP wanted to paste columns A and B but not L.

If you would like to use a different separator:

dt[ , new := do.call(paste, c(.SD, sep = ":"))]

Old version

You can use .SD and by to handle multiple columns:

dt[,new:=paste0(.SD,collapse=""),by=seq_along(L)]

I added seq_along in case L was not unique. (You can check this using dt<-data.table(L=c(1:4,4),A=letters[7:11],B=letters[12:16])).

Also, in my actual instance for some reason I had to use t(.SD) in the paste0 part. There may be other similar quirks.

edited Dec 14 '17 at 13:36

dayne

7,504
6
38
56

answered May 23 '14 at 00:23

dnlbrky

9,396
2
51
64

For this you should be using `do.call(paste0,.SD)`, eg `dt[ , new := do.call(paste0,.SD)]` – mnel May 23 '14 at 00:42
Good suggestion, and I'll add that you may need to use `.SDcols` too. I've updated the answer. – dnlbrky May 23 '14 at 04:47
1

I am curious if you know of a way to use the new version with a different separator? – dayne Dec 13 '17 at 21:48
how would I remove NAs in such a construct? `paste` doesn't accept `na.rm = TRUE`. `dt[ , new := do.call(paste, c(na.omit(.SD), sep = ':'))]` neither works. – andschar Feb 26 '19 at 17:09
found an answer here: https://stackoverflow.com/questions/13673894/suppress-nas-in-paste – andschar Feb 26 '19 at 17:17
How is it specifying columns A & B? And what is `.SD` ? – Paulito.Bandito Mar 16 '22 at 02:14

score 17 · Accepted Answer · edited May 23 '17 at 12:17

17

Arun's comment answered this question:

dt[,new:=paste0(A,B)]

edited May 23 '17 at 12:17

Community

1
1

answered Aug 21 '13 at 14:25

beginneR

3,207
5
30
52

3

This is the precise method I used for a very similar task, however I decided to use the option `collapse = "."` as I am using the resulting column, `new`, as an ID of sorts, and the addition of the `collapse =` option changes the behaviour unexpectedly -- on a decently sized `data.table` it effectively froze R -- is this actually expected behaviour or a bug? ie: `dt[, new := paste0(A, B, collapse = "."]` which yields the following on the sample table used in this example: `l g l l.h m.i n.j o.k p` – daRknight Mar 27 '17 at 17:51
What to do if I want to name the column "new" as "AB" ie I want to append the column names as well – Abhishek Singh Jul 04 '17 at 07:38
Just use names(dt)[names(dt)=="new"]<-"AB" – Max M Nov 09 '17 at 10:31

score -1 · Answer 3 · answered Aug 21 '13 at 14:06

-1

This should do it:

 dt <- data.table(dt, new = paste(dt$A, dt$B, sep = ""))

answered Aug 21 '13 at 14:06

Mayou

8,498
16
59
98

now I'm confused, this result was the first that came to my mind, too. Thought I checked this option. Anyway, thanks! – beginneR Aug 21 '13 at 14:11
1

this is more data.table-like: dt[,new:=paste(dt[,A],dt[,B],sep="")] – beginneR Aug 21 '13 at 14:17
True. That works. I am more familiar with dataframes to be honest. – Mayou Aug 21 '13 at 14:19

score -3 · Answer 4 · answered Sep 18 '16 at 22:17

-3

If you want to paste strictly using column indexes (when you may not know the row names)... I want to get a new column by pasting two columns 6 and 4

dt$new <- apply( dt[,c(6,4)], 1, function(row){ paste(row[1],row[2],sep="/") })

answered Sep 18 '16 at 22:17

Sqripter

101
2
7

does this imply we cannot pass in column indices to paste? – desval Apr 24 '19 at 21:56

paste two data.table columns

4 Answers4

New version

Old version

Linked

Related