How to set multiple columns in a data table to values from different columns in the same data table?

Question

Suppose I have a data frame with 6 columns, and I want to set col 1:3 to the values in col 4:6 (this comes up a lot when merging). With data frames it's easy:

set.seed(1)
df <- data.frame(matrix(sample(1:100,30),ncol=6))
df
#   X1 X2 X3 X4 X5 X6
# 1 27 86 19 43 75 29
# 2 37 97 16 88 17  1
# 3 57 62 61 83 51 28
# 4 89 58 34 32 10 81
# 5 20  6 67 63 21 25

df[,1:3] <- df[,4:6]                    # very, very straightforward...
df
#   X1 X2 X3 X4 X5 X6
# 1 43 75 29 43 75 29
# 2 88 17  1 88 17  1
# 3 83 51 28 83 51 28
# 4 32 10 81 32 10 81
# 5 63 21 25 63 21 25

With data.tables, not so much:

library(data.table)
dt <- data.table(df)
dt[,1:3,with=F] <- dt[,4:6,with=F]
## Error in `[<-.data.table`(`*tmp*`, , 1:3, with = F, value = list(X4 = c(43L,  : unused argument (with = F)

This works, but seems extremely complicated for such a simple transformation:

dt[, names(dt)[1:3]:=dt[,4:6,with=F]]   # very, very complicated...
dt
#    X1 X2 X3 X4 X5 X6
# 1: 43 75 29 43 75 29
# 2: 88 17  1 88 17  1
# 3: 83 51 28 83 51 28
# 4: 32 10 81 32 10 81
# 5: 63 21 25 63 21 25

The question is: is there a simpler way to assign one set of columns in a data table to the values from another set of columns in the same data table?

MichaelChirico · Accepted Answer · 2019-10-28 15:27:38Z

12

You can use the := operator and name column numbers in .SD:

dt[, 1:3 := .SD, .SDcols = 4:6]

> dt
   X1 X2 X3 X4 X5 X6
1: 43 75 29 43 75 29
2: 88 17  1 88 17  1
3: 83 51 28 83 51 28
4: 32 10 81 32 10 81
5: 63 21 25 63 21 25

edited Oct 28, 2019 at 15:27

MichaelChirico

34.9k17 gold badges122 silver badges209 bronze badges

answered Dec 4, 2013 at 18:40

Justin

43.4k9 gold badges96 silver badges111 bronze badges

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

Comments

eddi · Accepted Answer · 2013-12-04 18:43:10Z

4

Perhaps a for loop would look better?

for (i in 1:3) dt[[i]] = dt[[i+3]]

answered Dec 4, 2013 at 18:43

eddi

49.5k6 gold badges109 silver badges157 bronze badges

5 Comments

Arun Over a year ago

This'll make a copy. Use set. for (i in 1:3) set(dt, i=NULL, j=i, value=dt[[i+3]]).

eddi Over a year ago

@Arun sure but I thought the goal of OP was simplicity, not efficiency

Arun Over a year ago

My take was "simple" without losing the advantages of using a data.table. Otherwise, why use "data.table" in the first place..?

eddi Over a year ago

I can come up with many many reasons for that "why" :) Anyway, looks like OP got what they wanted.

jlhoward Over a year ago

@Arun's take is correct. I accept that data tables require named columns unless you include with=F, and that using := is preferred so the table is not copied. I was surprised, though, by the fact that dt[,1:3,with=F] on the LHS is not allowed. This seems very counter intuitive. Basically, I tried everything except using with=F twice. Figures...

Collectives™ on Stack Overflow

How to set multiple columns in a data table to values from different columns in the same data table?

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related