R - Fastest way to make an edgelist from data.frame in long format
1
I'm looking for a fast and scalable solution to coerce a massive data.frame from a long format to an edgelist in R.
Consider the following data.frame:
df1 <- data.frame(ID=c("A1", "A1", "A1", "B1", "B1", "B1"),
score=c(3,4,5,3,6,5))
> df1
ID score
1 A1 3
2 A1 4
3 A1 5
4 B1 3
5 B1 6
6 B1 5
The outcome should look like this. Note that the elements in score become nodes that are linked with ties if they are held by the same ID.
> el
X Y
1 3 4
2 3 5
3 4 5
4 3 6
5 6 5
The original df1 has roughly 30 million observations from which an edgelist needs to be calculated frequently.
r dataframe combinations
edited Nov 18 '18 at 5:11
Henrik
41.4k994109
asked Nov 18 '18 at 4:29
wake_wakewake_wake
4131826
add a comment |
1
I'm looking for a fast and scalable solution to coerce a massive data.frame from a long format to an edgelist in R.
Consider the following data.frame:
df1 <- data.frame(ID=c("A1", "A1", "A1", "B1", "B1", "B1"),
score=c(3,4,5,3,6,5))
> df1
ID score
1 A1 3
2 A1 4
3 A1 5
4 B1 3
5 B1 6
6 B1 5
The outcome should look like this. Note that the elements in score become nodes that are linked with ties if they are held by the same ID.
> el
X Y
1 3 4
2 3 5
3 4 5
4 3 6
5 6 5
The original df1 has roughly 30 million observations from which an edgelist needs to be calculated frequently.
r dataframe combinations
edited Nov 18 '18 at 5:11
Henrik
41.4k994109
asked Nov 18 '18 at 4:29
wake_wakewake_wake
4131826
Does order matter?
– r2evans
Nov 18 '18 at 4:31
@r2evans No - at this point not.
– wake_wake
Nov 18 '18 at 4:32
2
Is this 'just' combinations of two by group? Is so, see a possible duplicate: Faster version of combn
– Henrik
Nov 18 '18 at 5:02
add a comment |
1
1
1
I'm looking for a fast and scalable solution to coerce a massive data.frame from a long format to an edgelist in R.
Consider the following data.frame:
df1 <- data.frame(ID=c("A1", "A1", "A1", "B1", "B1", "B1"),
score=c(3,4,5,3,6,5))
> df1
ID score
1 A1 3
2 A1 4
3 A1 5
4 B1 3
5 B1 6
6 B1 5
The outcome should look like this. Note that the elements in score become nodes that are linked with ties if they are held by the same ID.
> el
X Y
1 3 4
2 3 5
3 4 5
4 3 6
5 6 5
The original df1 has roughly 30 million observations from which an edgelist needs to be calculated frequently.
r dataframe combinations
edited Nov 18 '18 at 5:11
Henrik
41.4k994109
asked Nov 18 '18 at 4:29
wake_wakewake_wake
4131826
I'm looking for a fast and scalable solution to coerce a massive data.frame from a long format to an edgelist in R.
Consider the following data.frame:
df1 <- data.frame(ID=c("A1", "A1", "A1", "B1", "B1", "B1"),
score=c(3,4,5,3,6,5))
> df1
ID score
1 A1 3
2 A1 4
3 A1 5
4 B1 3
5 B1 6
6 B1 5
The outcome should look like this. Note that the elements in score become nodes that are linked with ties if they are held by the same ID.
> el
X Y
1 3 4
2 3 5
3 4 5
4 3 6
5 6 5
The original df1 has roughly 30 million observations from which an edgelist needs to be calculated frequently.
r dataframe combinations
r dataframe combinations
edited Nov 18 '18 at 5:11
Henrik
41.4k994109
asked Nov 18 '18 at 4:29
wake_wakewake_wake
4131826
edited Nov 18 '18 at 5:11
Henrik
41.4k994109
asked Nov 18 '18 at 4:29
wake_wakewake_wake
4131826
edited Nov 18 '18 at 5:11
Henrik
41.4k994109
edited Nov 18 '18 at 5:11
Henrik
41.4k994109
edited Nov 18 '18 at 5:11
Henrik
41.4k994109
41.4k994109
asked Nov 18 '18 at 4:29
wake_wakewake_wake
4131826
asked Nov 18 '18 at 4:29
wake_wakewake_wake
4131826
asked Nov 18 '18 at 4:29
wake_wakewake_wake
4131826
4131826
Does order matter?
– r2evans
Nov 18 '18 at 4:31
@r2evans No - at this point not.
– wake_wake
Nov 18 '18 at 4:32
2
Is this 'just' combinations of two by group? Is so, see a possible duplicate: Faster version of combn
– Henrik
Nov 18 '18 at 5:02
add a comment |
Does order matter?
– r2evans
Nov 18 '18 at 4:31
@r2evans No - at this point not.
– wake_wake
Nov 18 '18 at 4:32
2
Is this 'just' combinations of two by group? Is so, see a possible duplicate: Faster version of combn
– Henrik
Nov 18 '18 at 5:02
Does order matter?
– r2evans
Nov 18 '18 at 4:31
Does order matter?
– r2evans
Nov 18 '18 at 4:31
@r2evans No - at this point not.
– wake_wake
Nov 18 '18 at 4:32
@r2evans No - at this point not.
– wake_wake
Nov 18 '18 at 4:32
2
2
Is this 'just' combinations of two by group? Is so, see a possible duplicate: Faster version of combn
– Henrik
Nov 18 '18 at 5:02
Is this 'just' combinations of two by group? Is so, see a possible duplicate: Faster version of combn
– Henrik
Nov 18 '18 at 5:02
add a comment |
1 Answer
1
active
oldest
votes
2
A popular (and efficient) tool for "large-ish" data is data.table:
library('data.table')
DT <- as.data.table(df1)
unique(DT[,as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,])
# V1 V2
# 1: 3 4
# 2: 3 5
# 3: 4 5
# 4: 3 6
# 5: 6 5
answered Nov 18 '18 at 4:48
r2evansr2evans
26.4k33058
This code works great for the example data. However, in the original data I get the following error: "negative length vectors are not allowed". Does this, perhaps, correspond to the fact that someIDhave only one score?
– wake_wake
Nov 18 '18 at 5:37
Likely, yes. Since those cannot contribute to edges, should they be filtered out before this process?
– r2evans
Nov 18 '18 at 6:00
Yes, I think so. Indeed, because they don't produce edges.
– wake_wake
Nov 18 '18 at 6:06
1
unique(DT[,if (.N>1) as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,])?
– r2evans
Nov 18 '18 at 6:49
This works great, @r2evans. I'm keeping this question unanswered for a bit so others can suggest an approach too. Will close it soon. Thank you!
– wake_wake
Nov 19 '18 at 4:34
|
show 2 more comments
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53357898%2fr-fastest-way-to-make-an-edgelist-from-data-frame-in-long-format%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
2
A popular (and efficient) tool for "large-ish" data is data.table:
library('data.table')
DT <- as.data.table(df1)
unique(DT[,as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,])
# V1 V2
# 1: 3 4
# 2: 3 5
# 3: 4 5
# 4: 3 6
# 5: 6 5
answered Nov 18 '18 at 4:48
r2evansr2evans
26.4k33058
This code works great for the example data. However, in the original data I get the following error: "negative length vectors are not allowed". Does this, perhaps, correspond to the fact that someIDhave only one score?
– wake_wake
Nov 18 '18 at 5:37
Likely, yes. Since those cannot contribute to edges, should they be filtered out before this process?
– r2evans
Nov 18 '18 at 6:00
Yes, I think so. Indeed, because they don't produce edges.
– wake_wake
Nov 18 '18 at 6:06
1
unique(DT[,if (.N>1) as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,])?
– r2evans
Nov 18 '18 at 6:49
This works great, @r2evans. I'm keeping this question unanswered for a bit so others can suggest an approach too. Will close it soon. Thank you!
– wake_wake
Nov 19 '18 at 4:34
|
show 2 more comments
2
A popular (and efficient) tool for "large-ish" data is data.table:
library('data.table')
DT <- as.data.table(df1)
unique(DT[,as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,])
# V1 V2
# 1: 3 4
# 2: 3 5
# 3: 4 5
# 4: 3 6
# 5: 6 5
answered Nov 18 '18 at 4:48
r2evansr2evans
26.4k33058
This code works great for the example data. However, in the original data I get the following error: "negative length vectors are not allowed". Does this, perhaps, correspond to the fact that someIDhave only one score?
– wake_wake
Nov 18 '18 at 5:37
Likely, yes. Since those cannot contribute to edges, should they be filtered out before this process?
– r2evans
Nov 18 '18 at 6:00
Yes, I think so. Indeed, because they don't produce edges.
– wake_wake
Nov 18 '18 at 6:06
1
unique(DT[,if (.N>1) as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,])?
– r2evans
Nov 18 '18 at 6:49
This works great, @r2evans. I'm keeping this question unanswered for a bit so others can suggest an approach too. Will close it soon. Thank you!
– wake_wake
Nov 19 '18 at 4:34
|
show 2 more comments
2
2
2
A popular (and efficient) tool for "large-ish" data is data.table:
library('data.table')
DT <- as.data.table(df1)
unique(DT[,as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,])
# V1 V2
# 1: 3 4
# 2: 3 5
# 3: 4 5
# 4: 3 6
# 5: 6 5
answered Nov 18 '18 at 4:48
r2evansr2evans
26.4k33058
A popular (and efficient) tool for "large-ish" data is data.table:
library('data.table')
DT <- as.data.table(df1)
unique(DT[,as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,])
# V1 V2
# 1: 3 4
# 2: 3 5
# 3: 4 5
# 4: 3 6
# 5: 6 5
answered Nov 18 '18 at 4:48
r2evansr2evans
26.4k33058
answered Nov 18 '18 at 4:48
r2evansr2evans
26.4k33058
answered Nov 18 '18 at 4:48
r2evansr2evans
26.4k33058
answered Nov 18 '18 at 4:48
r2evansr2evans
26.4k33058
26.4k33058
This code works great for the example data. However, in the original data I get the following error: "negative length vectors are not allowed". Does this, perhaps, correspond to the fact that someIDhave only one score?
– wake_wake
Nov 18 '18 at 5:37
Likely, yes. Since those cannot contribute to edges, should they be filtered out before this process?
– r2evans
Nov 18 '18 at 6:00
Yes, I think so. Indeed, because they don't produce edges.
– wake_wake
Nov 18 '18 at 6:06
1
unique(DT[,if (.N>1) as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,])?
– r2evans
Nov 18 '18 at 6:49
This works great, @r2evans. I'm keeping this question unanswered for a bit so others can suggest an approach too. Will close it soon. Thank you!
– wake_wake
Nov 19 '18 at 4:34
|
show 2 more comments
This code works great for the example data. However, in the original data I get the following error: "negative length vectors are not allowed". Does this, perhaps, correspond to the fact that someIDhave only one score?
– wake_wake
Nov 18 '18 at 5:37
Likely, yes. Since those cannot contribute to edges, should they be filtered out before this process?
– r2evans
Nov 18 '18 at 6:00
Yes, I think so. Indeed, because they don't produce edges.
– wake_wake
Nov 18 '18 at 6:06
1
unique(DT[,if (.N>1) as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,])?
– r2evans
Nov 18 '18 at 6:49
This works great, @r2evans. I'm keeping this question unanswered for a bit so others can suggest an approach too. Will close it soon. Thank you!
– wake_wake
Nov 19 '18 at 4:34
This code works great for the example data. However, in the original data I get the following error: "negative length vectors are not allowed". Does this, perhaps, correspond to the fact that some
ID have only one score?– wake_wake
Nov 18 '18 at 5:37
This code works great for the example data. However, in the original data I get the following error: "negative length vectors are not allowed". Does this, perhaps, correspond to the fact that some
ID have only one score?– wake_wake
Nov 18 '18 at 5:37
Likely, yes. Since those cannot contribute to edges, should they be filtered out before this process?
– r2evans
Nov 18 '18 at 6:00
Likely, yes. Since those cannot contribute to edges, should they be filtered out before this process?
– r2evans
Nov 18 '18 at 6:00
Yes, I think so. Indeed, because they don't produce edges.
– wake_wake
Nov 18 '18 at 6:06
Yes, I think so. Indeed, because they don't produce edges.
– wake_wake
Nov 18 '18 at 6:06
1
1
unique(DT[,if (.N>1) as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,]) ?– r2evans
Nov 18 '18 at 6:49
unique(DT[,if (.N>1) as.data.frame(t(combn(score,2))), by = "ID"][,ID := NULL,]) ?– r2evans
Nov 18 '18 at 6:49
This works great, @r2evans. I'm keeping this question unanswered for a bit so others can suggest an approach too. Will close it soon. Thank you!
– wake_wake
Nov 19 '18 at 4:34
This works great, @r2evans. I'm keeping this question unanswered for a bit so others can suggest an approach too. Will close it soon. Thank you!
– wake_wake
Nov 19 '18 at 4:34
|
show 2 more comments
draft saved
draft discarded
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.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53357898%2fr-fastest-way-to-make-an-edgelist-from-data-frame-in-long-format%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
Does order matter?
– r2evans
Nov 18 '18 at 4:31
@r2evans No - at this point not.
– wake_wake
Nov 18 '18 at 4:32
2
Is this 'just' combinations of two by group? Is so, see a possible duplicate: Faster version of combn
– Henrik
Nov 18 '18 at 5:02