pandas dataframe sum date range of another DataFrame
1
I have two dataframes. I want to sum an "amount" column in the 2nd, for each record in the first datafame.
So for each
df1.Date = sum(df2.amount WHERE df1.Date <= df2.Date AND df1.yearAgo >= df2.Date)
df1 = pd.DataFrame('Date':['2018-10-31','2018-10-30','2018-10-29','2018-10-28'],'yearAgo':['2017-10-31','2017-10-30','2017-10-29','2017-10-28'])
df2 = pd.DataFrame('Date':['2018-10-30','2018-7-30','2018-4-30','2018-1-30','2017-10-30'],'amount':[1.0,1.0,1.0,1.0,0.75])
desired results:
df1.Date yearToDateTotalAmount
2018-10-31 3.0
2018-10-30 4.75
2018-10-29 3.75
2018-10-28 3.75
python pandas dataframe
edited Nov 12 '18 at 21:45
ehacinom
1,45721532
asked Nov 12 '18 at 19:44
Ethan
82
add a comment |
1
I have two dataframes. I want to sum an "amount" column in the 2nd, for each record in the first datafame.
So for each
df1.Date = sum(df2.amount WHERE df1.Date <= df2.Date AND df1.yearAgo >= df2.Date)
df1 = pd.DataFrame('Date':['2018-10-31','2018-10-30','2018-10-29','2018-10-28'],'yearAgo':['2017-10-31','2017-10-30','2017-10-29','2017-10-28'])
df2 = pd.DataFrame('Date':['2018-10-30','2018-7-30','2018-4-30','2018-1-30','2017-10-30'],'amount':[1.0,1.0,1.0,1.0,0.75])
desired results:
df1.Date yearToDateTotalAmount
2018-10-31 3.0
2018-10-30 4.75
2018-10-29 3.75
2018-10-28 3.75
python pandas dataframe
edited Nov 12 '18 at 21:45
ehacinom
1,45721532
asked Nov 12 '18 at 19:44
Ethan
82
1
Are you sure shouldn't be4in your first row in desired output?
– RafaelC
Nov 12 '18 at 20:08
add a comment |
1
1
1
I have two dataframes. I want to sum an "amount" column in the 2nd, for each record in the first datafame.
So for each
df1.Date = sum(df2.amount WHERE df1.Date <= df2.Date AND df1.yearAgo >= df2.Date)
df1 = pd.DataFrame('Date':['2018-10-31','2018-10-30','2018-10-29','2018-10-28'],'yearAgo':['2017-10-31','2017-10-30','2017-10-29','2017-10-28'])
df2 = pd.DataFrame('Date':['2018-10-30','2018-7-30','2018-4-30','2018-1-30','2017-10-30'],'amount':[1.0,1.0,1.0,1.0,0.75])
desired results:
df1.Date yearToDateTotalAmount
2018-10-31 3.0
2018-10-30 4.75
2018-10-29 3.75
2018-10-28 3.75
python pandas dataframe
edited Nov 12 '18 at 21:45
ehacinom
1,45721532
asked Nov 12 '18 at 19:44
Ethan
82
I have two dataframes. I want to sum an "amount" column in the 2nd, for each record in the first datafame.
So for each
df1.Date = sum(df2.amount WHERE df1.Date <= df2.Date AND df1.yearAgo >= df2.Date)
df1 = pd.DataFrame('Date':['2018-10-31','2018-10-30','2018-10-29','2018-10-28'],'yearAgo':['2017-10-31','2017-10-30','2017-10-29','2017-10-28'])
df2 = pd.DataFrame('Date':['2018-10-30','2018-7-30','2018-4-30','2018-1-30','2017-10-30'],'amount':[1.0,1.0,1.0,1.0,0.75])
desired results:
df1.Date yearToDateTotalAmount
2018-10-31 3.0
2018-10-30 4.75
2018-10-29 3.75
2018-10-28 3.75
python pandas dataframe
python pandas dataframe
edited Nov 12 '18 at 21:45
ehacinom
1,45721532
asked Nov 12 '18 at 19:44
Ethan
82
edited Nov 12 '18 at 21:45
ehacinom
1,45721532
asked Nov 12 '18 at 19:44
Ethan
82
edited Nov 12 '18 at 21:45
ehacinom
1,45721532
edited Nov 12 '18 at 21:45
ehacinom
1,45721532
edited Nov 12 '18 at 21:45
ehacinom
1,45721532
1,45721532
asked Nov 12 '18 at 19:44
Ethan
82
asked Nov 12 '18 at 19:44
Ethan
82
asked Nov 12 '18 at 19:44
Ethan
82
82
1
Are you sure shouldn't be4in your first row in desired output?
– RafaelC
Nov 12 '18 at 20:08
add a comment |
1
Are you sure shouldn't be4in your first row in desired output?
– RafaelC
Nov 12 '18 at 20:08
1
1
Are you sure shouldn't be
4 in your first row in desired output?– RafaelC
Nov 12 '18 at 20:08
Are you sure shouldn't be
4 in your first row in desired output?– RafaelC
Nov 12 '18 at 20:08
add a comment |
1 Answer
1
active
oldest
votes
0
IIUC, your expected output should have 4 in first row.
You can achieve this very efficiently using numpy's feature of outer comparison, since less_equal and greater_equal are ufuncs.
Notice that
>>> np.greater_equal.outer(df1.Date, df2.Date)
array([[ True, True, True, True, True],
[ True, True, True, True, True],
[False, True, True, True, True],
[False, True, True, True, True]])
So you can get your mask by
mask = np.greater_equal.outer(df1.Date, df2.Date) &
np.less_equal.outer(df1.yearAgo, df2.Date)
And use outer multiplication + summing along axis=1
>>> np.sum(np.multiply(mask, df2.amount.values), axis=1)
Out[49]:
array([4. , 4.75, 3.75, 3.75])
In the end, just assign back
>>> df1['yearToDateTotalAmount'] = np.sum(np.multiply(mask, df2.amount.values), axis=1)
Date yearAgo yearToDateTotalAmount
0 2018-10-31 2017-10-31 4.00
1 2018-10-30 2017-10-30 4.75
2 2018-10-29 2017-10-29 3.75
3 2018-10-28 2017-10-28 3.75
answered Nov 12 '18 at 20:13
RafaelC
26k82649
Thanks for quick reply! Yes, the first expected value should be 4, I got a bit sloppy trying to cobble together a simple example. One question though, maybe I’m blind, but I don’t see where the “v.values” is coming from.
– Ethan
Nov 12 '18 at 21:58
@Ethan sorry,v.valuesisdf2.amount.values. ;)
– RafaelC
Nov 12 '18 at 22:03
1
No worries at all, I was just getting my head out and coming to the same answer! Thanks a million, this has been driving me a bit crazy. P.S. my wife thanks you, too!
– Ethan
Nov 12 '18 at 22:12
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53269061%2fpandas-dataframe-sum-date-range-of-another-dataframe%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
0
IIUC, your expected output should have 4 in first row.
You can achieve this very efficiently using numpy's feature of outer comparison, since less_equal and greater_equal are ufuncs.
Notice that
>>> np.greater_equal.outer(df1.Date, df2.Date)
array([[ True, True, True, True, True],
[ True, True, True, True, True],
[False, True, True, True, True],
[False, True, True, True, True]])
So you can get your mask by
mask = np.greater_equal.outer(df1.Date, df2.Date) &
np.less_equal.outer(df1.yearAgo, df2.Date)
And use outer multiplication + summing along axis=1
>>> np.sum(np.multiply(mask, df2.amount.values), axis=1)
Out[49]:
array([4. , 4.75, 3.75, 3.75])
In the end, just assign back
>>> df1['yearToDateTotalAmount'] = np.sum(np.multiply(mask, df2.amount.values), axis=1)
Date yearAgo yearToDateTotalAmount
0 2018-10-31 2017-10-31 4.00
1 2018-10-30 2017-10-30 4.75
2 2018-10-29 2017-10-29 3.75
3 2018-10-28 2017-10-28 3.75
answered Nov 12 '18 at 20:13
RafaelC
26k82649
Thanks for quick reply! Yes, the first expected value should be 4, I got a bit sloppy trying to cobble together a simple example. One question though, maybe I’m blind, but I don’t see where the “v.values” is coming from.
– Ethan
Nov 12 '18 at 21:58
@Ethan sorry,v.valuesisdf2.amount.values. ;)
– RafaelC
Nov 12 '18 at 22:03
1
No worries at all, I was just getting my head out and coming to the same answer! Thanks a million, this has been driving me a bit crazy. P.S. my wife thanks you, too!
– Ethan
Nov 12 '18 at 22:12
add a comment |
0
IIUC, your expected output should have 4 in first row.
You can achieve this very efficiently using numpy's feature of outer comparison, since less_equal and greater_equal are ufuncs.
Notice that
>>> np.greater_equal.outer(df1.Date, df2.Date)
array([[ True, True, True, True, True],
[ True, True, True, True, True],
[False, True, True, True, True],
[False, True, True, True, True]])
So you can get your mask by
mask = np.greater_equal.outer(df1.Date, df2.Date) &
np.less_equal.outer(df1.yearAgo, df2.Date)
And use outer multiplication + summing along axis=1
>>> np.sum(np.multiply(mask, df2.amount.values), axis=1)
Out[49]:
array([4. , 4.75, 3.75, 3.75])
In the end, just assign back
>>> df1['yearToDateTotalAmount'] = np.sum(np.multiply(mask, df2.amount.values), axis=1)
Date yearAgo yearToDateTotalAmount
0 2018-10-31 2017-10-31 4.00
1 2018-10-30 2017-10-30 4.75
2 2018-10-29 2017-10-29 3.75
3 2018-10-28 2017-10-28 3.75
answered Nov 12 '18 at 20:13
RafaelC
26k82649
Thanks for quick reply! Yes, the first expected value should be 4, I got a bit sloppy trying to cobble together a simple example. One question though, maybe I’m blind, but I don’t see where the “v.values” is coming from.
– Ethan
Nov 12 '18 at 21:58
@Ethan sorry,v.valuesisdf2.amount.values. ;)
– RafaelC
Nov 12 '18 at 22:03
1
No worries at all, I was just getting my head out and coming to the same answer! Thanks a million, this has been driving me a bit crazy. P.S. my wife thanks you, too!
– Ethan
Nov 12 '18 at 22:12
add a comment |
0
0
0
IIUC, your expected output should have 4 in first row.
You can achieve this very efficiently using numpy's feature of outer comparison, since less_equal and greater_equal are ufuncs.
Notice that
>>> np.greater_equal.outer(df1.Date, df2.Date)
array([[ True, True, True, True, True],
[ True, True, True, True, True],
[False, True, True, True, True],
[False, True, True, True, True]])
So you can get your mask by
mask = np.greater_equal.outer(df1.Date, df2.Date) &
np.less_equal.outer(df1.yearAgo, df2.Date)
And use outer multiplication + summing along axis=1
>>> np.sum(np.multiply(mask, df2.amount.values), axis=1)
Out[49]:
array([4. , 4.75, 3.75, 3.75])
In the end, just assign back
>>> df1['yearToDateTotalAmount'] = np.sum(np.multiply(mask, df2.amount.values), axis=1)
Date yearAgo yearToDateTotalAmount
0 2018-10-31 2017-10-31 4.00
1 2018-10-30 2017-10-30 4.75
2 2018-10-29 2017-10-29 3.75
3 2018-10-28 2017-10-28 3.75
answered Nov 12 '18 at 20:13
RafaelC
26k82649
IIUC, your expected output should have 4 in first row.
You can achieve this very efficiently using numpy's feature of outer comparison, since less_equal and greater_equal are ufuncs.
Notice that
>>> np.greater_equal.outer(df1.Date, df2.Date)
array([[ True, True, True, True, True],
[ True, True, True, True, True],
[False, True, True, True, True],
[False, True, True, True, True]])
So you can get your mask by
mask = np.greater_equal.outer(df1.Date, df2.Date) &
np.less_equal.outer(df1.yearAgo, df2.Date)
And use outer multiplication + summing along axis=1
>>> np.sum(np.multiply(mask, df2.amount.values), axis=1)
Out[49]:
array([4. , 4.75, 3.75, 3.75])
In the end, just assign back
>>> df1['yearToDateTotalAmount'] = np.sum(np.multiply(mask, df2.amount.values), axis=1)
Date yearAgo yearToDateTotalAmount
0 2018-10-31 2017-10-31 4.00
1 2018-10-30 2017-10-30 4.75
2 2018-10-29 2017-10-29 3.75
3 2018-10-28 2017-10-28 3.75
answered Nov 12 '18 at 20:13
RafaelC
26k82649
edited Nov 12 '18 at 22:03
answered Nov 12 '18 at 20:13
RafaelC
26k82649
answered Nov 12 '18 at 20:13
RafaelC
26k82649
answered Nov 12 '18 at 20:13
RafaelC
26k82649
26k82649
Thanks for quick reply! Yes, the first expected value should be 4, I got a bit sloppy trying to cobble together a simple example. One question though, maybe I’m blind, but I don’t see where the “v.values” is coming from.
– Ethan
Nov 12 '18 at 21:58
@Ethan sorry,v.valuesisdf2.amount.values. ;)
– RafaelC
Nov 12 '18 at 22:03
1
No worries at all, I was just getting my head out and coming to the same answer! Thanks a million, this has been driving me a bit crazy. P.S. my wife thanks you, too!
– Ethan
Nov 12 '18 at 22:12
add a comment |
Thanks for quick reply! Yes, the first expected value should be 4, I got a bit sloppy trying to cobble together a simple example. One question though, maybe I’m blind, but I don’t see where the “v.values” is coming from.
– Ethan
Nov 12 '18 at 21:58
@Ethan sorry,v.valuesisdf2.amount.values. ;)
– RafaelC
Nov 12 '18 at 22:03
1
No worries at all, I was just getting my head out and coming to the same answer! Thanks a million, this has been driving me a bit crazy. P.S. my wife thanks you, too!
– Ethan
Nov 12 '18 at 22:12
Thanks for quick reply! Yes, the first expected value should be 4, I got a bit sloppy trying to cobble together a simple example. One question though, maybe I’m blind, but I don’t see where the “v.values” is coming from.
– Ethan
Nov 12 '18 at 21:58
Thanks for quick reply! Yes, the first expected value should be 4, I got a bit sloppy trying to cobble together a simple example. One question though, maybe I’m blind, but I don’t see where the “v.values” is coming from.
– Ethan
Nov 12 '18 at 21:58
@Ethan sorry,
v.values is df2.amount.values. ;)– RafaelC
Nov 12 '18 at 22:03
@Ethan sorry,
v.values is df2.amount.values. ;)– RafaelC
Nov 12 '18 at 22:03
1
1
No worries at all, I was just getting my head out and coming to the same answer! Thanks a million, this has been driving me a bit crazy. P.S. my wife thanks you, too!
– Ethan
Nov 12 '18 at 22:12
No worries at all, I was just getting my head out and coming to the same answer! Thanks a million, this has been driving me a bit crazy. P.S. my wife thanks you, too!
– Ethan
Nov 12 '18 at 22:12
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53269061%2fpandas-dataframe-sum-date-range-of-another-dataframe%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
1
Are you sure shouldn't be
4in your first row in desired output?– RafaelC
Nov 12 '18 at 20:08