Merging based on multiple ranges
I would like to merge two data-frames on multiple ranges. Below I have produced a representative example. The sqldf solution works, however, I am wondering if there is a better way to do this (e.g., using data.table).
base <- data.frame(lower1 = c(12, 12, 3, 2), upper1 = c(20, 20, 20, 4),
lower2 = c(12, 12, 3, 2), upper2 = c(20, 20, 20, 4)) %>%
data.table()
more_info <- data.frame(color = 'red', value1 = 4, value2 = 4, thing1 = 5, thing2 = 5) %>%
data.table()
setkey(base, lower1, upper1, lower2, upper2)
setkey(more_info, value1, value2, thing1, thing2)
# works
sqldf('select * from base left join more_info
on ( base.lower1 <= more_info.value1 and base.upper1 >= more_info.value1
and base.lower2 <= more_info.thing1 and base.upper2 >= more_info.thing1)')
# doesn't work but is what i would like to do
setkey(base, lower1, upper1, lower2, upper2)
setkey(more_info, value1, value2, thing1, thing2)
foverlaps(more_info, base, by.x = key(more_info), by.y = key(base), type = 'within',
mult = 'all', nomatch = NA)
As a little bit of background, I have a matching algorithm that I need to improve run-times for. The matching algorithm works by filtering down a large number of loans based on certain characteristics to a smaller number of potential matches. Then, I apply whatever additional statistical techniques are necessary to find the best match. The hold-up is repeatedly filtering down the large data set of all matches to a smaller number of potential matches. My goal is to find a faster way to create the data-frame of potential matches and then use a group-by and other vectorized functions to complete the matching process.
r data.table sqldf
asked Nov 15 '18 at 14:31
Johnny AtlisJohnny Atlis
85
add a comment |
I would like to merge two data-frames on multiple ranges. Below I have produced a representative example. The sqldf solution works, however, I am wondering if there is a better way to do this (e.g., using data.table).
base <- data.frame(lower1 = c(12, 12, 3, 2), upper1 = c(20, 20, 20, 4),
lower2 = c(12, 12, 3, 2), upper2 = c(20, 20, 20, 4)) %>%
data.table()
more_info <- data.frame(color = 'red', value1 = 4, value2 = 4, thing1 = 5, thing2 = 5) %>%
data.table()
setkey(base, lower1, upper1, lower2, upper2)
setkey(more_info, value1, value2, thing1, thing2)
# works
sqldf('select * from base left join more_info
on ( base.lower1 <= more_info.value1 and base.upper1 >= more_info.value1
and base.lower2 <= more_info.thing1 and base.upper2 >= more_info.thing1)')
# doesn't work but is what i would like to do
setkey(base, lower1, upper1, lower2, upper2)
setkey(more_info, value1, value2, thing1, thing2)
foverlaps(more_info, base, by.x = key(more_info), by.y = key(base), type = 'within',
mult = 'all', nomatch = NA)
As a little bit of background, I have a matching algorithm that I need to improve run-times for. The matching algorithm works by filtering down a large number of loans based on certain characteristics to a smaller number of potential matches. Then, I apply whatever additional statistical techniques are necessary to find the best match. The hold-up is repeatedly filtering down the large data set of all matches to a smaller number of potential matches. My goal is to find a faster way to create the data-frame of potential matches and then use a group-by and other vectorized functions to complete the matching process.
r data.table sqldf
asked Nov 15 '18 at 14:31
Johnny AtlisJohnny Atlis
85
Thebetweenkeyword in SQL can be used to shorten the conditions.setkeyis part of data.table, not sqldf.create indexis available in SQL to set indexes.
– G. Grothendieck
Nov 16 '18 at 3:34
add a comment |
I would like to merge two data-frames on multiple ranges. Below I have produced a representative example. The sqldf solution works, however, I am wondering if there is a better way to do this (e.g., using data.table).
base <- data.frame(lower1 = c(12, 12, 3, 2), upper1 = c(20, 20, 20, 4),
lower2 = c(12, 12, 3, 2), upper2 = c(20, 20, 20, 4)) %>%
data.table()
more_info <- data.frame(color = 'red', value1 = 4, value2 = 4, thing1 = 5, thing2 = 5) %>%
data.table()
setkey(base, lower1, upper1, lower2, upper2)
setkey(more_info, value1, value2, thing1, thing2)
# works
sqldf('select * from base left join more_info
on ( base.lower1 <= more_info.value1 and base.upper1 >= more_info.value1
and base.lower2 <= more_info.thing1 and base.upper2 >= more_info.thing1)')
# doesn't work but is what i would like to do
setkey(base, lower1, upper1, lower2, upper2)
setkey(more_info, value1, value2, thing1, thing2)
foverlaps(more_info, base, by.x = key(more_info), by.y = key(base), type = 'within',
mult = 'all', nomatch = NA)
As a little bit of background, I have a matching algorithm that I need to improve run-times for. The matching algorithm works by filtering down a large number of loans based on certain characteristics to a smaller number of potential matches. Then, I apply whatever additional statistical techniques are necessary to find the best match. The hold-up is repeatedly filtering down the large data set of all matches to a smaller number of potential matches. My goal is to find a faster way to create the data-frame of potential matches and then use a group-by and other vectorized functions to complete the matching process.
r data.table sqldf
asked Nov 15 '18 at 14:31
Johnny AtlisJohnny Atlis
85
I would like to merge two data-frames on multiple ranges. Below I have produced a representative example. The sqldf solution works, however, I am wondering if there is a better way to do this (e.g., using data.table).
base <- data.frame(lower1 = c(12, 12, 3, 2), upper1 = c(20, 20, 20, 4),
lower2 = c(12, 12, 3, 2), upper2 = c(20, 20, 20, 4)) %>%
data.table()
more_info <- data.frame(color = 'red', value1 = 4, value2 = 4, thing1 = 5, thing2 = 5) %>%
data.table()
setkey(base, lower1, upper1, lower2, upper2)
setkey(more_info, value1, value2, thing1, thing2)
# works
sqldf('select * from base left join more_info
on ( base.lower1 <= more_info.value1 and base.upper1 >= more_info.value1
and base.lower2 <= more_info.thing1 and base.upper2 >= more_info.thing1)')
# doesn't work but is what i would like to do
setkey(base, lower1, upper1, lower2, upper2)
setkey(more_info, value1, value2, thing1, thing2)
foverlaps(more_info, base, by.x = key(more_info), by.y = key(base), type = 'within',
mult = 'all', nomatch = NA)
As a little bit of background, I have a matching algorithm that I need to improve run-times for. The matching algorithm works by filtering down a large number of loans based on certain characteristics to a smaller number of potential matches. Then, I apply whatever additional statistical techniques are necessary to find the best match. The hold-up is repeatedly filtering down the large data set of all matches to a smaller number of potential matches. My goal is to find a faster way to create the data-frame of potential matches and then use a group-by and other vectorized functions to complete the matching process.
r data.table sqldf
r data.table sqldf
asked Nov 15 '18 at 14:31
Johnny AtlisJohnny Atlis
85
asked Nov 15 '18 at 14:31
Johnny AtlisJohnny Atlis
85
edited Nov 15 '18 at 15:11
Johnny Atlis
asked Nov 15 '18 at 14:31
Johnny AtlisJohnny Atlis
85
asked Nov 15 '18 at 14:31
Johnny AtlisJohnny Atlis
85
asked Nov 15 '18 at 14:31
Johnny AtlisJohnny Atlis
85
85
Thebetweenkeyword in SQL can be used to shorten the conditions.setkeyis part of data.table, not sqldf.create indexis available in SQL to set indexes.
– G. Grothendieck
Nov 16 '18 at 3:34
add a comment |
Thebetweenkeyword in SQL can be used to shorten the conditions.setkeyis part of data.table, not sqldf.create indexis available in SQL to set indexes.
– G. Grothendieck
Nov 16 '18 at 3:34
The
between keyword in SQL can be used to shorten the conditions. setkey is part of data.table, not sqldf. create index is available in SQL to set indexes.– G. Grothendieck
Nov 16 '18 at 3:34
The
between keyword in SQL can be used to shorten the conditions. setkey is part of data.table, not sqldf. create index is available in SQL to set indexes.– G. Grothendieck
Nov 16 '18 at 3:34
add a comment |
1 Answer
1
active
oldest
votes
Something like:
more_info[base, .(lower1, upper1, lower2, upper2, color, value1 = x.value1,
value2 = x.value2, thing1 = x.thing1, thing2 = x.thing2),
on = .(value1 >= lower1, value1 <= upper1, thing1 >= lower2, thing1 <= upper2)]
Output:
lower1 upper1 lower2 upper2 color value1 value2 thing1 thing2
1: 12 20 12 20 <NA> NA NA NA NA
2: 12 20 12 20 <NA> NA NA NA NA
3: 3 20 3 20 red 4 4 5 5
4: 2 4 2 4 <NA> NA NA NA NA
answered Nov 15 '18 at 15:16
arg0nautarg0naut
5,7241320
1
not sure why this answer isn't accepted yet.. benchmarking shows this solution is about 10x faster than the sqldf....
– Wimpel
Nov 18 '18 at 12:52
I also don't think there's a much faster solution in R, but probably OP is just new to SO and doesn't know how it functions.
– arg0naut
Nov 18 '18 at 12:58
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321703%2fmerging-based-on-multiple-ranges%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Something like:
more_info[base, .(lower1, upper1, lower2, upper2, color, value1 = x.value1,
value2 = x.value2, thing1 = x.thing1, thing2 = x.thing2),
on = .(value1 >= lower1, value1 <= upper1, thing1 >= lower2, thing1 <= upper2)]
Output:
lower1 upper1 lower2 upper2 color value1 value2 thing1 thing2
1: 12 20 12 20 <NA> NA NA NA NA
2: 12 20 12 20 <NA> NA NA NA NA
3: 3 20 3 20 red 4 4 5 5
4: 2 4 2 4 <NA> NA NA NA NA
answered Nov 15 '18 at 15:16
arg0nautarg0naut
5,7241320
1
not sure why this answer isn't accepted yet.. benchmarking shows this solution is about 10x faster than the sqldf....
– Wimpel
Nov 18 '18 at 12:52
I also don't think there's a much faster solution in R, but probably OP is just new to SO and doesn't know how it functions.
– arg0naut
Nov 18 '18 at 12:58
add a comment |
Something like:
more_info[base, .(lower1, upper1, lower2, upper2, color, value1 = x.value1,
value2 = x.value2, thing1 = x.thing1, thing2 = x.thing2),
on = .(value1 >= lower1, value1 <= upper1, thing1 >= lower2, thing1 <= upper2)]
Output:
lower1 upper1 lower2 upper2 color value1 value2 thing1 thing2
1: 12 20 12 20 <NA> NA NA NA NA
2: 12 20 12 20 <NA> NA NA NA NA
3: 3 20 3 20 red 4 4 5 5
4: 2 4 2 4 <NA> NA NA NA NA
answered Nov 15 '18 at 15:16
arg0nautarg0naut
5,7241320
1
not sure why this answer isn't accepted yet.. benchmarking shows this solution is about 10x faster than the sqldf....
– Wimpel
Nov 18 '18 at 12:52
I also don't think there's a much faster solution in R, but probably OP is just new to SO and doesn't know how it functions.
– arg0naut
Nov 18 '18 at 12:58
add a comment |
Something like:
more_info[base, .(lower1, upper1, lower2, upper2, color, value1 = x.value1,
value2 = x.value2, thing1 = x.thing1, thing2 = x.thing2),
on = .(value1 >= lower1, value1 <= upper1, thing1 >= lower2, thing1 <= upper2)]
Output:
lower1 upper1 lower2 upper2 color value1 value2 thing1 thing2
1: 12 20 12 20 <NA> NA NA NA NA
2: 12 20 12 20 <NA> NA NA NA NA
3: 3 20 3 20 red 4 4 5 5
4: 2 4 2 4 <NA> NA NA NA NA
answered Nov 15 '18 at 15:16
arg0nautarg0naut
5,7241320
Something like:
more_info[base, .(lower1, upper1, lower2, upper2, color, value1 = x.value1,
value2 = x.value2, thing1 = x.thing1, thing2 = x.thing2),
on = .(value1 >= lower1, value1 <= upper1, thing1 >= lower2, thing1 <= upper2)]
Output:
lower1 upper1 lower2 upper2 color value1 value2 thing1 thing2
1: 12 20 12 20 <NA> NA NA NA NA
2: 12 20 12 20 <NA> NA NA NA NA
3: 3 20 3 20 red 4 4 5 5
4: 2 4 2 4 <NA> NA NA NA NA
answered Nov 15 '18 at 15:16
arg0nautarg0naut
5,7241320
edited Nov 15 '18 at 15:22
answered Nov 15 '18 at 15:16
arg0nautarg0naut
5,7241320
answered Nov 15 '18 at 15:16
arg0nautarg0naut
5,7241320
answered Nov 15 '18 at 15:16
arg0nautarg0naut
5,7241320
5,7241320
1
not sure why this answer isn't accepted yet.. benchmarking shows this solution is about 10x faster than the sqldf....
– Wimpel
Nov 18 '18 at 12:52
I also don't think there's a much faster solution in R, but probably OP is just new to SO and doesn't know how it functions.
– arg0naut
Nov 18 '18 at 12:58
add a comment |
1
not sure why this answer isn't accepted yet.. benchmarking shows this solution is about 10x faster than the sqldf....
– Wimpel
Nov 18 '18 at 12:52
I also don't think there's a much faster solution in R, but probably OP is just new to SO and doesn't know how it functions.
– arg0naut
Nov 18 '18 at 12:58
1
1
not sure why this answer isn't accepted yet.. benchmarking shows this solution is about 10x faster than the sqldf....
– Wimpel
Nov 18 '18 at 12:52
not sure why this answer isn't accepted yet.. benchmarking shows this solution is about 10x faster than the sqldf....
– Wimpel
Nov 18 '18 at 12:52
I also don't think there's a much faster solution in R, but probably OP is just new to SO and doesn't know how it functions.
– arg0naut
Nov 18 '18 at 12:58
I also don't think there's a much faster solution in R, but probably OP is just new to SO and doesn't know how it functions.
– arg0naut
Nov 18 '18 at 12:58
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321703%2fmerging-based-on-multiple-ranges%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
The
betweenkeyword in SQL can be used to shorten the conditions.setkeyis part of data.table, not sqldf.create indexis available in SQL to set indexes.– G. Grothendieck
Nov 16 '18 at 3:34