Excluding most recent day from rolling sum
I have a pandas dataframe, excerpt shown below, of rainfall data. 'Pcp' is a one day total, which I have then used to calculate rolling cumulative totals of rainfall for the other periods of time leading up to the day of interest (3 days up to 28 days), using:
df['Pcp_3day'] = df['Pcp'].rolling(3).sum()
What I would like to achieve is a rolling total of n days prior to but not including the date of interest. In other words, at the moment, the rolling totals are being formed with rainfall totals from days 0, -1, -2, whereas I would like to exclude day 0 (the day of interest) and have a rolling total of days -1, -2, -3, i.e the three days leading up to it.
I am unsure as to whether this analogy is very clear, but if there is any advice out there, it would be hugely appreciated.
Thanks
Pcp Pcp_3day Pcp_7day Pcp_10day Pcp_14day Pcp_21day Pcp_28day
date
2017-12-04 8.382 19.304 21.082 40.132 40.132 42.418 71.374
2017-12-05 12.192 20.574 33.020 42.164 52.324 52.578 81.534
2017-12-06 1.016 21.590 33.020 34.290 53.340 53.594 82.550
2017-12-07 12.700 25.908 45.466 46.990 66.040 66.040 95
2017-12-08 5.080 18.796 50.292 51.816 71.120 71.120 88.900
python sum rolling
asked Nov 12 at 11:43
SHV_la
596
add a comment |
I have a pandas dataframe, excerpt shown below, of rainfall data. 'Pcp' is a one day total, which I have then used to calculate rolling cumulative totals of rainfall for the other periods of time leading up to the day of interest (3 days up to 28 days), using:
df['Pcp_3day'] = df['Pcp'].rolling(3).sum()
What I would like to achieve is a rolling total of n days prior to but not including the date of interest. In other words, at the moment, the rolling totals are being formed with rainfall totals from days 0, -1, -2, whereas I would like to exclude day 0 (the day of interest) and have a rolling total of days -1, -2, -3, i.e the three days leading up to it.
I am unsure as to whether this analogy is very clear, but if there is any advice out there, it would be hugely appreciated.
Thanks
Pcp Pcp_3day Pcp_7day Pcp_10day Pcp_14day Pcp_21day Pcp_28day
date
2017-12-04 8.382 19.304 21.082 40.132 40.132 42.418 71.374
2017-12-05 12.192 20.574 33.020 42.164 52.324 52.578 81.534
2017-12-06 1.016 21.590 33.020 34.290 53.340 53.594 82.550
2017-12-07 12.700 25.908 45.466 46.990 66.040 66.040 95
2017-12-08 5.080 18.796 50.292 51.816 71.120 71.120 88.900
python sum rolling
asked Nov 12 at 11:43
SHV_la
596
It would help others when you provide the data structure explicitly. In this case, that it is a pandas DataFrame.
– eaydin
Nov 12 at 11:55
@eaydin noted and edited, thanks.
– SHV_la
Nov 12 at 11:59
add a comment |
I have a pandas dataframe, excerpt shown below, of rainfall data. 'Pcp' is a one day total, which I have then used to calculate rolling cumulative totals of rainfall for the other periods of time leading up to the day of interest (3 days up to 28 days), using:
df['Pcp_3day'] = df['Pcp'].rolling(3).sum()
What I would like to achieve is a rolling total of n days prior to but not including the date of interest. In other words, at the moment, the rolling totals are being formed with rainfall totals from days 0, -1, -2, whereas I would like to exclude day 0 (the day of interest) and have a rolling total of days -1, -2, -3, i.e the three days leading up to it.
I am unsure as to whether this analogy is very clear, but if there is any advice out there, it would be hugely appreciated.
Thanks
Pcp Pcp_3day Pcp_7day Pcp_10day Pcp_14day Pcp_21day Pcp_28day
date
2017-12-04 8.382 19.304 21.082 40.132 40.132 42.418 71.374
2017-12-05 12.192 20.574 33.020 42.164 52.324 52.578 81.534
2017-12-06 1.016 21.590 33.020 34.290 53.340 53.594 82.550
2017-12-07 12.700 25.908 45.466 46.990 66.040 66.040 95
2017-12-08 5.080 18.796 50.292 51.816 71.120 71.120 88.900
python sum rolling
asked Nov 12 at 11:43
SHV_la
596
I have a pandas dataframe, excerpt shown below, of rainfall data. 'Pcp' is a one day total, which I have then used to calculate rolling cumulative totals of rainfall for the other periods of time leading up to the day of interest (3 days up to 28 days), using:
df['Pcp_3day'] = df['Pcp'].rolling(3).sum()
What I would like to achieve is a rolling total of n days prior to but not including the date of interest. In other words, at the moment, the rolling totals are being formed with rainfall totals from days 0, -1, -2, whereas I would like to exclude day 0 (the day of interest) and have a rolling total of days -1, -2, -3, i.e the three days leading up to it.
I am unsure as to whether this analogy is very clear, but if there is any advice out there, it would be hugely appreciated.
Thanks
Pcp Pcp_3day Pcp_7day Pcp_10day Pcp_14day Pcp_21day Pcp_28day
date
2017-12-04 8.382 19.304 21.082 40.132 40.132 42.418 71.374
2017-12-05 12.192 20.574 33.020 42.164 52.324 52.578 81.534
2017-12-06 1.016 21.590 33.020 34.290 53.340 53.594 82.550
2017-12-07 12.700 25.908 45.466 46.990 66.040 66.040 95
2017-12-08 5.080 18.796 50.292 51.816 71.120 71.120 88.900
python sum rolling
python sum rolling
asked Nov 12 at 11:43
SHV_la
596
asked Nov 12 at 11:43
SHV_la
596
edited Nov 12 at 11:58
asked Nov 12 at 11:43
SHV_la
596
asked Nov 12 at 11:43
SHV_la
596
asked Nov 12 at 11:43
SHV_la
596
596
It would help others when you provide the data structure explicitly. In this case, that it is a pandas DataFrame.
– eaydin
Nov 12 at 11:55
@eaydin noted and edited, thanks.
– SHV_la
Nov 12 at 11:59
add a comment |
It would help others when you provide the data structure explicitly. In this case, that it is a pandas DataFrame.
– eaydin
Nov 12 at 11:55
@eaydin noted and edited, thanks.
– SHV_la
Nov 12 at 11:59
It would help others when you provide the data structure explicitly. In this case, that it is a pandas DataFrame.
– eaydin
Nov 12 at 11:55
It would help others when you provide the data structure explicitly. In this case, that it is a pandas DataFrame.
– eaydin
Nov 12 at 11:55
@eaydin noted and edited, thanks.
– SHV_la
Nov 12 at 11:59
@eaydin noted and edited, thanks.
– SHV_la
Nov 12 at 11:59
add a comment |
1 Answer
1
active
oldest
votes
Try this:
df['Pcp_3day'] = df['Pcp'].shift().rolling(3).sum()
answered Nov 12 at 11:45
John Zwinck
150k16175286
And this is why I love stackoverflow, thank you @John
– SHV_la
Nov 12 at 12:01
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%2f53261447%2fexcluding-most-recent-day-from-rolling-sum%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
Try this:
df['Pcp_3day'] = df['Pcp'].shift().rolling(3).sum()
answered Nov 12 at 11:45
John Zwinck
150k16175286
And this is why I love stackoverflow, thank you @John
– SHV_la
Nov 12 at 12:01
add a comment |
Try this:
df['Pcp_3day'] = df['Pcp'].shift().rolling(3).sum()
answered Nov 12 at 11:45
John Zwinck
150k16175286
And this is why I love stackoverflow, thank you @John
– SHV_la
Nov 12 at 12:01
add a comment |
Try this:
df['Pcp_3day'] = df['Pcp'].shift().rolling(3).sum()
answered Nov 12 at 11:45
John Zwinck
150k16175286
Try this:
df['Pcp_3day'] = df['Pcp'].shift().rolling(3).sum()
answered Nov 12 at 11:45
John Zwinck
150k16175286
answered Nov 12 at 11:45
John Zwinck
150k16175286
answered Nov 12 at 11:45
John Zwinck
150k16175286
answered Nov 12 at 11:45
John Zwinck
150k16175286
150k16175286
And this is why I love stackoverflow, thank you @John
– SHV_la
Nov 12 at 12:01
add a comment |
And this is why I love stackoverflow, thank you @John
– SHV_la
Nov 12 at 12:01
And this is why I love stackoverflow, thank you @John
– SHV_la
Nov 12 at 12:01
And this is why I love stackoverflow, thank you @John
– SHV_la
Nov 12 at 12:01
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.
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.
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%2f53261447%2fexcluding-most-recent-day-from-rolling-sum%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
It would help others when you provide the data structure explicitly. In this case, that it is a pandas DataFrame.
– eaydin
Nov 12 at 11:55
@eaydin noted and edited, thanks.
– SHV_la
Nov 12 at 11:59