Improving pandas speed for deriving data from other datasets
$begingroup$
I don't want to filter the set I'm starting with or perform mathematical operations on it. Instead, I want to count the number of entries which appear in another set based on the observations here:
Imagine I have a set like this:
year family count
1 A 5
2 B 7
Which continues for some 1000 entries.
And then I also have a separate data set which lists every single element, pretend here it's some kind of firm:
firm_id family year_started year_ended
1234 A 1234 1942
4567 B 1836 2011
...
And that continues for almost half a million entries. How do I most efficiently count the number of entries in the second data set given matches by family and years between year_started and year_ended.
Right now, I'm using an apply function:
def get_count(year, family, a=attributes):
a = a[a['family'].str.startswith(naics_prefix)]
a = a[a['year_started'].apply(lambda d: d.year) <= year]
a = a[a['year_ended'].apply(lambda d: d.year) >= year] # can be 9999-12-31, so must be python date not pandas dt
return len(a)
Invoked by
counts.progress_apply(lambda r: get_count(r['ending_year'], r['family']),
axis=1)
Which understandably takes forever.
pandas
asked 1 hour ago
ifly6ifly6
1062
$endgroup$
add a comment |
$begingroup$
I don't want to filter the set I'm starting with or perform mathematical operations on it. Instead, I want to count the number of entries which appear in another set based on the observations here:
Imagine I have a set like this:
year family count
1 A 5
2 B 7
Which continues for some 1000 entries.
And then I also have a separate data set which lists every single element, pretend here it's some kind of firm:
firm_id family year_started year_ended
1234 A 1234 1942
4567 B 1836 2011
...
And that continues for almost half a million entries. How do I most efficiently count the number of entries in the second data set given matches by family and years between year_started and year_ended.
Right now, I'm using an apply function:
def get_count(year, family, a=attributes):
a = a[a['family'].str.startswith(naics_prefix)]
a = a[a['year_started'].apply(lambda d: d.year) <= year]
a = a[a['year_ended'].apply(lambda d: d.year) >= year] # can be 9999-12-31, so must be python date not pandas dt
return len(a)
Invoked by
counts.progress_apply(lambda r: get_count(r['ending_year'], r['family']),
axis=1)
Which understandably takes forever.
pandas
asked 1 hour ago
ifly6ifly6
1062
$endgroup$
add a comment |
$begingroup$
I don't want to filter the set I'm starting with or perform mathematical operations on it. Instead, I want to count the number of entries which appear in another set based on the observations here:
Imagine I have a set like this:
year family count
1 A 5
2 B 7
Which continues for some 1000 entries.
And then I also have a separate data set which lists every single element, pretend here it's some kind of firm:
firm_id family year_started year_ended
1234 A 1234 1942
4567 B 1836 2011
...
And that continues for almost half a million entries. How do I most efficiently count the number of entries in the second data set given matches by family and years between year_started and year_ended.
Right now, I'm using an apply function:
def get_count(year, family, a=attributes):
a = a[a['family'].str.startswith(naics_prefix)]
a = a[a['year_started'].apply(lambda d: d.year) <= year]
a = a[a['year_ended'].apply(lambda d: d.year) >= year] # can be 9999-12-31, so must be python date not pandas dt
return len(a)
Invoked by
counts.progress_apply(lambda r: get_count(r['ending_year'], r['family']),
axis=1)
Which understandably takes forever.
pandas
asked 1 hour ago
ifly6ifly6
1062
$endgroup$
I don't want to filter the set I'm starting with or perform mathematical operations on it. Instead, I want to count the number of entries which appear in another set based on the observations here:
Imagine I have a set like this:
year family count
1 A 5
2 B 7
Which continues for some 1000 entries.
And then I also have a separate data set which lists every single element, pretend here it's some kind of firm:
firm_id family year_started year_ended
1234 A 1234 1942
4567 B 1836 2011
...
And that continues for almost half a million entries. How do I most efficiently count the number of entries in the second data set given matches by family and years between year_started and year_ended.
Right now, I'm using an apply function:
def get_count(year, family, a=attributes):
a = a[a['family'].str.startswith(naics_prefix)]
a = a[a['year_started'].apply(lambda d: d.year) <= year]
a = a[a['year_ended'].apply(lambda d: d.year) >= year] # can be 9999-12-31, so must be python date not pandas dt
return len(a)
Invoked by
counts.progress_apply(lambda r: get_count(r['ending_year'], r['family']),
axis=1)
Which understandably takes forever.
pandas
pandas
asked 1 hour ago
ifly6ifly6
1062
asked 1 hour ago
ifly6ifly6
1062
asked 1 hour ago
ifly6ifly6
1062
asked 1 hour ago
ifly6ifly6
1062
asked 1 hour ago
ifly6ifly6
1062
1062
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
});
});
}, "mathjax-editing");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "557"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdatascience.stackexchange.com%2fquestions%2f47975%2fimproving-pandas-speed-for-deriving-data-from-other-datasets%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Data Science Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
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%2fdatascience.stackexchange.com%2fquestions%2f47975%2fimproving-pandas-speed-for-deriving-data-from-other-datasets%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
