Ufdjrw

Why would a jet engine that runs at temps excess of 2000°C burn when it crashes?

infinitive telling the purpose

A three room house but a three headED dog

Why would one plane in this picture not have gear down yet?

Why does the negative sign arise in this thermodynamic relation?

Subset counting for even numbers

Do items de-spawn in Diablo?

How could our ancestors have domesticated a solitary predator?

Are the terms "stab" and "staccato" synonyms?

Do I really need to have a scientific explanation for my premise?

The bar has been raised

Does splitting a potentially monolithic application into several smaller ones help prevent bugs?

Should I take out a loan for a friend to invest on my behalf?

Why the color red for the Republican Party

Can someone explain what is being said here in color publishing in the American Mathematical Monthly?

"One can do his homework in the library"

Time travel short story where dinosaur doesn't taste like chicken

Placing subfig vertically

Is having access to past exams cheating and, if yes, could it be proven just by a good grade?

Space in array system equations

Is Gradient Descent central to every optimizer?

Is there an equal sign with wider gap?

Rejected in 4th interview round citing insufficient years of experience

Are babies of evil humanoid species inherently evil?

Join datatables using more than one column name stored in one variable

1

I am trying to perform an update join of two data tables with the fields (more than one) I need to use to join stored in a variable. Below is an example:

library(data.table)
DT1 <- data.table(col1 = 1:5, col2 = 5:1, lett = letters[1:5])
DT2 <- data.table(col1 = c(1:3, 2:5, 1), col2 = c(5:3, 4:1, 5))

joinFields <- c('col1', 'col2')

I tried doing it this way:

DT1[DT2,
 on=c(paste0(joinFields, '=', joinFields)),
 nomatch=0L]

This way is based on a solution suggested in Join datatables using column names stored in variables.

dt1[dt2_temp, 
 on=c(paste0(varName, ">valueMin"), paste0(varName, "<=valueMax")),
 nomatch=0L]

It does not work. Obviously, my case is a bit different, because in the example I used, there are 2 pastes. Is there a solution that continues to allow me using on = c()?

Edit: I am aware I can do it with merge()`

r data.table

share|improve this question

edited Mar 6 at 16:39

Frank

55.4k659134

asked Mar 6 at 16:27

koteletjekoteletje

1688

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  You mention "update join" but don't use :=. Could you show and explain the desired output? Btw, you might need "==" not "=" there, I guess, eg DT1[DT2, on=sprintf("%s==%s", joinFields, joinFields)]
  
  – Frank
  Mar 6 at 16:40
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  the keys used to join appears to be the same in both tables so why not DT1[DT2, on=joinFields, nomatch=0L] ?
  
  – chinsoon12
  Mar 7 at 0:47
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @Frank, thanks for your suggestion. It is indeed not an update join. I was a bit lazy and copied from the other thread. I should have added let := i.lett
  
  – koteletje
  Mar 7 at 14:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @chinsoon12, thanks that is a very short solution
  
  – koteletje
  Mar 7 at 14:01
  

  
  
  
  

add a comment | 

1

I am trying to perform an update join of two data tables with the fields (more than one) I need to use to join stored in a variable. Below is an example:

library(data.table)
DT1 <- data.table(col1 = 1:5, col2 = 5:1, lett = letters[1:5])
DT2 <- data.table(col1 = c(1:3, 2:5, 1), col2 = c(5:3, 4:1, 5))

joinFields <- c('col1', 'col2')

I tried doing it this way:

DT1[DT2,
 on=c(paste0(joinFields, '=', joinFields)),
 nomatch=0L]

This way is based on a solution suggested in Join datatables using column names stored in variables.

dt1[dt2_temp, 
 on=c(paste0(varName, ">valueMin"), paste0(varName, "<=valueMax")),
 nomatch=0L]

It does not work. Obviously, my case is a bit different, because in the example I used, there are 2 pastes. Is there a solution that continues to allow me using on = c()?

Edit: I am aware I can do it with merge()`

r data.table

share|improve this question

edited Mar 6 at 16:39

Frank

55.4k659134

asked Mar 6 at 16:27

koteletjekoteletje

1688

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  You mention "update join" but don't use :=. Could you show and explain the desired output? Btw, you might need "==" not "=" there, I guess, eg DT1[DT2, on=sprintf("%s==%s", joinFields, joinFields)]
  
  – Frank
  Mar 6 at 16:40
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  the keys used to join appears to be the same in both tables so why not DT1[DT2, on=joinFields, nomatch=0L] ?
  
  – chinsoon12
  Mar 7 at 0:47
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @Frank, thanks for your suggestion. It is indeed not an update join. I was a bit lazy and copied from the other thread. I should have added let := i.lett
  
  – koteletje
  Mar 7 at 14:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @chinsoon12, thanks that is a very short solution
  
  – koteletje
  Mar 7 at 14:01
  

  
  
  
  

add a comment | 

I am trying to perform an update join of two data tables with the fields (more than one) I need to use to join stored in a variable. Below is an example:

library(data.table)
DT1 <- data.table(col1 = 1:5, col2 = 5:1, lett = letters[1:5])
DT2 <- data.table(col1 = c(1:3, 2:5, 1), col2 = c(5:3, 4:1, 5))

joinFields <- c('col1', 'col2')

I tried doing it this way:

DT1[DT2,
 on=c(paste0(joinFields, '=', joinFields)),
 nomatch=0L]

This way is based on a solution suggested in Join datatables using column names stored in variables.

dt1[dt2_temp, 
 on=c(paste0(varName, ">valueMin"), paste0(varName, "<=valueMax")),
 nomatch=0L]

It does not work. Obviously, my case is a bit different, because in the example I used, there are 2 pastes. Is there a solution that continues to allow me using on = c()?

Edit: I am aware I can do it with merge()`

r data.table

share|improve this question

edited Mar 6 at 16:39

Frank

55.4k659134

asked Mar 6 at 16:27

koteletjekoteletje

1688

library(data.table)
DT1 <- data.table(col1 = 1:5, col2 = 5:1, lett = letters[1:5])
DT2 <- data.table(col1 = c(1:3, 2:5, 1), col2 = c(5:3, 4:1, 5))

joinFields <- c('col1', 'col2')

DT1[DT2,
 on=c(paste0(joinFields, '=', joinFields)),
 nomatch=0L]

dt1[dt2_temp, 
 on=c(paste0(varName, ">valueMin"), paste0(varName, "<=valueMax")),
 nomatch=0L]

share|improve this question

edited Mar 6 at 16:39

Frank

55.4k659134

asked Mar 6 at 16:27

koteletjekoteletje

1688

share|improve this question

edited Mar 6 at 16:39

Frank

55.4k659134

asked Mar 6 at 16:27

koteletjekoteletje

1688

edited Mar 6 at 16:39

Frank

55.4k659134

edited Mar 6 at 16:39

Frank

55.4k659134

asked Mar 6 at 16:27

koteletjekoteletje

1688

asked Mar 6 at 16:27

koteletjekoteletje

1688

1 Answer
1

active

oldest

votes

4

I think you just need to put two ==, as following:

DT1[DT2,
 on=c(paste0(joinFields, '==', joinFields)),
 nomatch=0L]
# col1 col2 lett
# 1: 1 5 a
# 2: 2 4 b
# 3: 3 3 c
# 4: 2 4 b
# 5: 3 3 c
# 6: 4 2 d
# 7: 5 1 e
# 8: 1 5 a

Even you do not need to use c() :

 DT1[DT2,
 on=paste0(joinFields, '==', joinFields),
 nomatch=0L]
 # col1 col2 lett
 # 1: 1 5 a
 # 2: 2 4 b
 # 3: 3 3 c
 # 4: 2 4 b
 # 5: 3 3 c
 # 6: 4 2 d
 # 7: 5 1 e
 # 8: 1 5 a

share|improve this answer

edited Mar 6 at 17:01

JonnyCrunch

1,658522

answered Mar 6 at 16:42

Carles Sans FuentesCarles Sans Fuentes

612110

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much @Carles Sans Fuentes. I must say I have some things to look into, because I don't understand why it works with the == operator. I generally join like this DT1[DT2, on = .(col1 = col1, col2 = col2), nomatch=0L]
  
  – koteletje
  Mar 7 at 6:52
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @koteletje It's just the documented behavior with strings in on= (from ?data.table)
  
  – Frank
  Mar 7 at 15:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Happy to help @koteletje. We all need to be checking staff. Regarding your question of why it is two equals: besides being stated like that on the data.table documentation, the standard way to check if an object/something is exactly equal than another object/something is using logical operators, and it is stablished as two equals . Check this link for further understanding of it: statmethods.net/management/operators.html
  
  – Carles Sans Fuentes
  Mar 7 at 16:32
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55027851%2fjoin-datatables-using-more-than-one-column-name-stored-in-one-variable%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

4

I think you just need to put two ==, as following:

DT1[DT2,
 on=c(paste0(joinFields, '==', joinFields)),
 nomatch=0L]
# col1 col2 lett
# 1: 1 5 a
# 2: 2 4 b
# 3: 3 3 c
# 4: 2 4 b
# 5: 3 3 c
# 6: 4 2 d
# 7: 5 1 e
# 8: 1 5 a

Even you do not need to use c() :

 DT1[DT2,
 on=paste0(joinFields, '==', joinFields),
 nomatch=0L]
 # col1 col2 lett
 # 1: 1 5 a
 # 2: 2 4 b
 # 3: 3 3 c
 # 4: 2 4 b
 # 5: 3 3 c
 # 6: 4 2 d
 # 7: 5 1 e
 # 8: 1 5 a

share|improve this answer

edited Mar 6 at 17:01

JonnyCrunch

1,658522

answered Mar 6 at 16:42

Carles Sans FuentesCarles Sans Fuentes

612110

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much @Carles Sans Fuentes. I must say I have some things to look into, because I don't understand why it works with the == operator. I generally join like this DT1[DT2, on = .(col1 = col1, col2 = col2), nomatch=0L]
  
  – koteletje
  Mar 7 at 6:52
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @koteletje It's just the documented behavior with strings in on= (from ?data.table)
  
  – Frank
  Mar 7 at 15:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Happy to help @koteletje. We all need to be checking staff. Regarding your question of why it is two equals: besides being stated like that on the data.table documentation, the standard way to check if an object/something is exactly equal than another object/something is using logical operators, and it is stablished as two equals . Check this link for further understanding of it: statmethods.net/management/operators.html
  
  – Carles Sans Fuentes
  Mar 7 at 16:32
  

  
  
  
  

add a comment | 

4

I think you just need to put two ==, as following:

DT1[DT2,
 on=c(paste0(joinFields, '==', joinFields)),
 nomatch=0L]
# col1 col2 lett
# 1: 1 5 a
# 2: 2 4 b
# 3: 3 3 c
# 4: 2 4 b
# 5: 3 3 c
# 6: 4 2 d
# 7: 5 1 e
# 8: 1 5 a

Even you do not need to use c() :

 DT1[DT2,
 on=paste0(joinFields, '==', joinFields),
 nomatch=0L]
 # col1 col2 lett
 # 1: 1 5 a
 # 2: 2 4 b
 # 3: 3 3 c
 # 4: 2 4 b
 # 5: 3 3 c
 # 6: 4 2 d
 # 7: 5 1 e
 # 8: 1 5 a

share|improve this answer

edited Mar 6 at 17:01

JonnyCrunch

1,658522

answered Mar 6 at 16:42

Carles Sans FuentesCarles Sans Fuentes

612110

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much @Carles Sans Fuentes. I must say I have some things to look into, because I don't understand why it works with the == operator. I generally join like this DT1[DT2, on = .(col1 = col1, col2 = col2), nomatch=0L]
  
  – koteletje
  Mar 7 at 6:52
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @koteletje It's just the documented behavior with strings in on= (from ?data.table)
  
  – Frank
  Mar 7 at 15:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Happy to help @koteletje. We all need to be checking staff. Regarding your question of why it is two equals: besides being stated like that on the data.table documentation, the standard way to check if an object/something is exactly equal than another object/something is using logical operators, and it is stablished as two equals . Check this link for further understanding of it: statmethods.net/management/operators.html
  
  – Carles Sans Fuentes
  Mar 7 at 16:32
  

  
  
  
  

add a comment | 

I think you just need to put two ==, as following:

DT1[DT2,
 on=c(paste0(joinFields, '==', joinFields)),
 nomatch=0L]
# col1 col2 lett
# 1: 1 5 a
# 2: 2 4 b
# 3: 3 3 c
# 4: 2 4 b
# 5: 3 3 c
# 6: 4 2 d
# 7: 5 1 e
# 8: 1 5 a

Even you do not need to use c() :

 DT1[DT2,
 on=paste0(joinFields, '==', joinFields),
 nomatch=0L]
 # col1 col2 lett
 # 1: 1 5 a
 # 2: 2 4 b
 # 3: 3 3 c
 # 4: 2 4 b
 # 5: 3 3 c
 # 6: 4 2 d
 # 7: 5 1 e
 # 8: 1 5 a

share|improve this answer

edited Mar 6 at 17:01

JonnyCrunch

1,658522

answered Mar 6 at 16:42

Carles Sans FuentesCarles Sans Fuentes

612110

DT1[DT2,
 on=c(paste0(joinFields, '==', joinFields)),
 nomatch=0L]
# col1 col2 lett
# 1: 1 5 a
# 2: 2 4 b
# 3: 3 3 c
# 4: 2 4 b
# 5: 3 3 c
# 6: 4 2 d
# 7: 5 1 e
# 8: 1 5 a

 DT1[DT2,
 on=paste0(joinFields, '==', joinFields),
 nomatch=0L]
 # col1 col2 lett
 # 1: 1 5 a
 # 2: 2 4 b
 # 3: 3 3 c
 # 4: 2 4 b
 # 5: 3 3 c
 # 6: 4 2 d
 # 7: 5 1 e
 # 8: 1 5 a

share|improve this answer

edited Mar 6 at 17:01

JonnyCrunch

1,658522

answered Mar 6 at 16:42

Carles Sans FuentesCarles Sans Fuentes

612110

edited Mar 6 at 17:01

JonnyCrunch

1,658522

edited Mar 6 at 17:01

JonnyCrunch

1,658522

answered Mar 6 at 16:42

Carles Sans FuentesCarles Sans Fuentes

612110

answered Mar 6 at 16:42

Carles Sans FuentesCarles Sans Fuentes

612110