data.table: merge() one-key table with two-keys table

Question

I refer also to this questions that is now almost 3 years old. I have a similar problem.

I have these three tables:

require(data.table)

profile <- data.table(id = seq(11,20))
setkey(profile, "id")

post <- data.table(id = seq(1,10))
setkey(post, "id")

comment <- data.table(post_id = seq(1,10),
                      profile_id = seq(11,20))
setkeyv(comment, c("post_id","profile_id"))

Now I want to merge profile and post with comment into two different tables. How do I specify that the key to match in comment is profile_id and in post is post_id? Should I respecify how tables are built?

Answer 1

Unfortunately, merge by.x= by.y= as in merge for data.frame is not implemented for data.tables yet. It will be adressed in the next release 1.9.6. See here: https://github.com/Rdatatable/data.table/issues/637.

What you can do is convert your data.tables to data.frames, do the merge there using by.x and by.y (see ?merge.data.frame for that) and then convert back to data.tables.

Alternatively, name the keys/columns in a way they match on the name basis. Then, data.table's merge should work.

setnames(profile, "id", "profile_id")
setnames(post,    "id", "post_id")

merged_dt1 <- merge(profile, comment)
merged_dt2 <- merge(post, comment)

Answer 2

I don't think I understand your question. What do you expect exactly?

Naming of the keys shouldn't matter--in x[y] the first keys of x and y are matched (and then the second keys, etc.).

What about the following is different from what you expect? I've edited your example tables to make it clear exactly what's happening:

profile <- data.table(id = seq(11,20), v_prof = sample(100:109), key = "id")

post <- data.table(id = seq(1,10), v_pos t= sample(200:209), key = "id")

comment <- data.table(post_id = seq(1, 10),
                      profile_id = seq(11, 20))

setkey(comment, post_id)[post]
#(the reverse would work too: post[setkey(comment, post_id)] )
    post_id profile_id v_post
 1:       1         11    207
 2:       2         12    208
 3:       3         13    201
 4:       4         14    205
 5:       5         15    206
 6:       6         16    200
 7:       7         17    202
 8:       8         18    203
 9:       9         19    209
10:      10         20    204

setkey(comment, profile_id)[profile]
    post_id profile_id v_prof
 1:       1         11    107
 2:       2         12    101
 3:       3         13    109
 4:       4         14    102
 5:       5         15    103
 6:       6         16    104
 7:       7         17    100
 8:       8         18    108
 9:       9         19    105
10:      10         20    106