pandas adding column values in a loop without using iloc
up vote
0
down vote
favorite
I want to add (ideally get the mean of the sum) of several column values starting with my index i,
investmentlength=list(range(1,13,1))
returns=list()
for i in range(0,len(stocks2)):
if stocks2['Startpoint'][i]==1:
nextmonth=nextmonth+stocks2['RET'][i+1]+stocks2['RET'][i+2]+stocks2['RET'][i+3]+....
counter+=1
Is there a way to give the beginning Index and the end index and prob step size and then sum it all in one command instead of copy and paste to death? I wanted to go trough all the different investment lengths and put the avg returns in the empty list.
SHRCD EXCHCD SICCD PRC VOL RET SHROUT
DATE PERMNO
1970-08-31 10559.0 10.0 1.0 5311.0 35.000 1692.0 0.030657 12048.0
12626.0 10.0 1.0 5411.0 46.250 926.0 0.088235 6624.0
12749.0 11.0 1.0 5331.0 45.500 5632.0 0.126173 34685.0
13100.0 11.0 1.0 5311.0 22.000 1759.0 0.171242 15107.0
13653.0 10.0 1.0 5311.0 13.125 141.0 0.220930 1337.0
13936.0 11.0 1.0 2331.0 11.500 270.0 -0.053061 3942.0
14322.0 11.0 1.0 5311.0 64.750 6934.0 0.024409 154187.0
16969.0 10.0 1.0 5311.0 42.875 1069.0 0.186851 13828.0
17072.0 10.0 1.0 5311.0 14.750 777.0 0.026087 5415.0
17304.0 10.0 1.0 5311.0 24.875 1939.0 0.058511 8150.0
MV XRET IB ... PE2
DATE PERMNO ...
1970-08-31 10559.0 421680.000 0.025357 NaN ... 13.852692
12626.0 306360.000 0.082935 NaN ... 13.145312
12749.0 1578167.500 0.120873 NaN ... 25.970466
13100.0 332354.000 0.165942 NaN ... 9.990711
13653.0 17548.125 0.215630 NaN ... 6.273570
13936.0 45333.000 -0.058361 NaN ... 6.473123
14322.0 9983608.250 0.019109 NaN ... 22.204047
16969.0 592875.500 0.181551 NaN ... 11.948061
17072.0 79871.250 0.020787 NaN ... 8.845526
17304.0 202731.250 0.053211 NaN ... 8.641655
lagPE1 lagPE2 lagMV lagSEQ QUINTILE1
DATE PERMNO
1970-08-31 10559.0 13.852692 13.852692 412644.000 264.686 4
12626.0 13.145312 13.145312 281520.000 164.151 4
12749.0 25.970466 25.970466 1404742.500 367.519 5
13100.0 9.990711 9.990711 288921.375 414.820 3
13653.0 6.273570 6.273570 14372.750 24.958 1
13936.0 6.473123 6.473123 48289.500 76.986 1
14322.0 22.204047 22.204047 9790874.500 3439.802 5
16969.0 11.948061 11.948061 499536.500 NaN 4
17072.0 8.845526 8.845526 77840.625 NaN 3
17304.0 8.641655 8.641655 191525.000 307.721 3
QUINTILE2 avgvol avg Startpoint
DATE PERMNO
1970-08-31 10559.0 4 9229.057592 1697.2 0
12626.0 4 3654.367470 894.4 0
12749.0 5 188206.566860 5828.6 0
13100.0 3 94127.319048 3477.2 0
13653.0 1 816.393162 268.8 0
13936.0 1 71547.050633 553.2 0
14322.0 5 195702.521519 6308.8 0
16969.0 4 3670.297872 2002.0 0
17072.0 3 3774.083333 3867.8 0
17304.0 3 12622.112903 1679.4 0
python pandas dataframe
edited Nov 12 at 1:47
dmitriys
15119
asked Nov 11 at 23:07
hmmmbob
4381021
add a comment |
up vote
0
down vote
favorite
I want to add (ideally get the mean of the sum) of several column values starting with my index i,
investmentlength=list(range(1,13,1))
returns=list()
for i in range(0,len(stocks2)):
if stocks2['Startpoint'][i]==1:
nextmonth=nextmonth+stocks2['RET'][i+1]+stocks2['RET'][i+2]+stocks2['RET'][i+3]+....
counter+=1
Is there a way to give the beginning Index and the end index and prob step size and then sum it all in one command instead of copy and paste to death? I wanted to go trough all the different investment lengths and put the avg returns in the empty list.
SHRCD EXCHCD SICCD PRC VOL RET SHROUT
DATE PERMNO
1970-08-31 10559.0 10.0 1.0 5311.0 35.000 1692.0 0.030657 12048.0
12626.0 10.0 1.0 5411.0 46.250 926.0 0.088235 6624.0
12749.0 11.0 1.0 5331.0 45.500 5632.0 0.126173 34685.0
13100.0 11.0 1.0 5311.0 22.000 1759.0 0.171242 15107.0
13653.0 10.0 1.0 5311.0 13.125 141.0 0.220930 1337.0
13936.0 11.0 1.0 2331.0 11.500 270.0 -0.053061 3942.0
14322.0 11.0 1.0 5311.0 64.750 6934.0 0.024409 154187.0
16969.0 10.0 1.0 5311.0 42.875 1069.0 0.186851 13828.0
17072.0 10.0 1.0 5311.0 14.750 777.0 0.026087 5415.0
17304.0 10.0 1.0 5311.0 24.875 1939.0 0.058511 8150.0
MV XRET IB ... PE2
DATE PERMNO ...
1970-08-31 10559.0 421680.000 0.025357 NaN ... 13.852692
12626.0 306360.000 0.082935 NaN ... 13.145312
12749.0 1578167.500 0.120873 NaN ... 25.970466
13100.0 332354.000 0.165942 NaN ... 9.990711
13653.0 17548.125 0.215630 NaN ... 6.273570
13936.0 45333.000 -0.058361 NaN ... 6.473123
14322.0 9983608.250 0.019109 NaN ... 22.204047
16969.0 592875.500 0.181551 NaN ... 11.948061
17072.0 79871.250 0.020787 NaN ... 8.845526
17304.0 202731.250 0.053211 NaN ... 8.641655
lagPE1 lagPE2 lagMV lagSEQ QUINTILE1
DATE PERMNO
1970-08-31 10559.0 13.852692 13.852692 412644.000 264.686 4
12626.0 13.145312 13.145312 281520.000 164.151 4
12749.0 25.970466 25.970466 1404742.500 367.519 5
13100.0 9.990711 9.990711 288921.375 414.820 3
13653.0 6.273570 6.273570 14372.750 24.958 1
13936.0 6.473123 6.473123 48289.500 76.986 1
14322.0 22.204047 22.204047 9790874.500 3439.802 5
16969.0 11.948061 11.948061 499536.500 NaN 4
17072.0 8.845526 8.845526 77840.625 NaN 3
17304.0 8.641655 8.641655 191525.000 307.721 3
QUINTILE2 avgvol avg Startpoint
DATE PERMNO
1970-08-31 10559.0 4 9229.057592 1697.2 0
12626.0 4 3654.367470 894.4 0
12749.0 5 188206.566860 5828.6 0
13100.0 3 94127.319048 3477.2 0
13653.0 1 816.393162 268.8 0
13936.0 1 71547.050633 553.2 0
14322.0 5 195702.521519 6308.8 0
16969.0 4 3670.297872 2002.0 0
17072.0 3 3774.083333 3867.8 0
17304.0 3 12622.112903 1679.4 0
python pandas dataframe
edited Nov 12 at 1:47
dmitriys
15119
asked Nov 11 at 23:07
hmmmbob
4381021
Can you post some sample input data, and the corresponding output data you'd like? Preferably in a reproducible format. It's usually a bad idea to use explicit loops like this, there is almost certainly a faster/better approach, but it'll be hard for the community to help without explicit input/output data.
– smj
Nov 11 at 23:16
I tried and i hope that clears things up
– hmmmbob
Nov 11 at 23:21
@hmmmbob I'm not quite sure what you'r trying to do with that loop so an example of your expected output would be useful for people to help... However, how close doesdf.RET.mean(level=0)get you?
– Jon Clements♦
Nov 11 at 23:36
I am trying to add up several column values, starting with the index [i+1].. all they way to [i+1+a], then calculate the mean of that sum, and then append that value to my empty list called returns.
– hmmmbob
Nov 12 at 0:11
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I want to add (ideally get the mean of the sum) of several column values starting with my index i,
investmentlength=list(range(1,13,1))
returns=list()
for i in range(0,len(stocks2)):
if stocks2['Startpoint'][i]==1:
nextmonth=nextmonth+stocks2['RET'][i+1]+stocks2['RET'][i+2]+stocks2['RET'][i+3]+....
counter+=1
Is there a way to give the beginning Index and the end index and prob step size and then sum it all in one command instead of copy and paste to death? I wanted to go trough all the different investment lengths and put the avg returns in the empty list.
SHRCD EXCHCD SICCD PRC VOL RET SHROUT
DATE PERMNO
1970-08-31 10559.0 10.0 1.0 5311.0 35.000 1692.0 0.030657 12048.0
12626.0 10.0 1.0 5411.0 46.250 926.0 0.088235 6624.0
12749.0 11.0 1.0 5331.0 45.500 5632.0 0.126173 34685.0
13100.0 11.0 1.0 5311.0 22.000 1759.0 0.171242 15107.0
13653.0 10.0 1.0 5311.0 13.125 141.0 0.220930 1337.0
13936.0 11.0 1.0 2331.0 11.500 270.0 -0.053061 3942.0
14322.0 11.0 1.0 5311.0 64.750 6934.0 0.024409 154187.0
16969.0 10.0 1.0 5311.0 42.875 1069.0 0.186851 13828.0
17072.0 10.0 1.0 5311.0 14.750 777.0 0.026087 5415.0
17304.0 10.0 1.0 5311.0 24.875 1939.0 0.058511 8150.0
MV XRET IB ... PE2
DATE PERMNO ...
1970-08-31 10559.0 421680.000 0.025357 NaN ... 13.852692
12626.0 306360.000 0.082935 NaN ... 13.145312
12749.0 1578167.500 0.120873 NaN ... 25.970466
13100.0 332354.000 0.165942 NaN ... 9.990711
13653.0 17548.125 0.215630 NaN ... 6.273570
13936.0 45333.000 -0.058361 NaN ... 6.473123
14322.0 9983608.250 0.019109 NaN ... 22.204047
16969.0 592875.500 0.181551 NaN ... 11.948061
17072.0 79871.250 0.020787 NaN ... 8.845526
17304.0 202731.250 0.053211 NaN ... 8.641655
lagPE1 lagPE2 lagMV lagSEQ QUINTILE1
DATE PERMNO
1970-08-31 10559.0 13.852692 13.852692 412644.000 264.686 4
12626.0 13.145312 13.145312 281520.000 164.151 4
12749.0 25.970466 25.970466 1404742.500 367.519 5
13100.0 9.990711 9.990711 288921.375 414.820 3
13653.0 6.273570 6.273570 14372.750 24.958 1
13936.0 6.473123 6.473123 48289.500 76.986 1
14322.0 22.204047 22.204047 9790874.500 3439.802 5
16969.0 11.948061 11.948061 499536.500 NaN 4
17072.0 8.845526 8.845526 77840.625 NaN 3
17304.0 8.641655 8.641655 191525.000 307.721 3
QUINTILE2 avgvol avg Startpoint
DATE PERMNO
1970-08-31 10559.0 4 9229.057592 1697.2 0
12626.0 4 3654.367470 894.4 0
12749.0 5 188206.566860 5828.6 0
13100.0 3 94127.319048 3477.2 0
13653.0 1 816.393162 268.8 0
13936.0 1 71547.050633 553.2 0
14322.0 5 195702.521519 6308.8 0
16969.0 4 3670.297872 2002.0 0
17072.0 3 3774.083333 3867.8 0
17304.0 3 12622.112903 1679.4 0
python pandas dataframe
edited Nov 12 at 1:47
dmitriys
15119
asked Nov 11 at 23:07
hmmmbob
4381021
I want to add (ideally get the mean of the sum) of several column values starting with my index i,
investmentlength=list(range(1,13,1))
returns=list()
for i in range(0,len(stocks2)):
if stocks2['Startpoint'][i]==1:
nextmonth=nextmonth+stocks2['RET'][i+1]+stocks2['RET'][i+2]+stocks2['RET'][i+3]+....
counter+=1
Is there a way to give the beginning Index and the end index and prob step size and then sum it all in one command instead of copy and paste to death? I wanted to go trough all the different investment lengths and put the avg returns in the empty list.
SHRCD EXCHCD SICCD PRC VOL RET SHROUT
DATE PERMNO
1970-08-31 10559.0 10.0 1.0 5311.0 35.000 1692.0 0.030657 12048.0
12626.0 10.0 1.0 5411.0 46.250 926.0 0.088235 6624.0
12749.0 11.0 1.0 5331.0 45.500 5632.0 0.126173 34685.0
13100.0 11.0 1.0 5311.0 22.000 1759.0 0.171242 15107.0
13653.0 10.0 1.0 5311.0 13.125 141.0 0.220930 1337.0
13936.0 11.0 1.0 2331.0 11.500 270.0 -0.053061 3942.0
14322.0 11.0 1.0 5311.0 64.750 6934.0 0.024409 154187.0
16969.0 10.0 1.0 5311.0 42.875 1069.0 0.186851 13828.0
17072.0 10.0 1.0 5311.0 14.750 777.0 0.026087 5415.0
17304.0 10.0 1.0 5311.0 24.875 1939.0 0.058511 8150.0
MV XRET IB ... PE2
DATE PERMNO ...
1970-08-31 10559.0 421680.000 0.025357 NaN ... 13.852692
12626.0 306360.000 0.082935 NaN ... 13.145312
12749.0 1578167.500 0.120873 NaN ... 25.970466
13100.0 332354.000 0.165942 NaN ... 9.990711
13653.0 17548.125 0.215630 NaN ... 6.273570
13936.0 45333.000 -0.058361 NaN ... 6.473123
14322.0 9983608.250 0.019109 NaN ... 22.204047
16969.0 592875.500 0.181551 NaN ... 11.948061
17072.0 79871.250 0.020787 NaN ... 8.845526
17304.0 202731.250 0.053211 NaN ... 8.641655
lagPE1 lagPE2 lagMV lagSEQ QUINTILE1
DATE PERMNO
1970-08-31 10559.0 13.852692 13.852692 412644.000 264.686 4
12626.0 13.145312 13.145312 281520.000 164.151 4
12749.0 25.970466 25.970466 1404742.500 367.519 5
13100.0 9.990711 9.990711 288921.375 414.820 3
13653.0 6.273570 6.273570 14372.750 24.958 1
13936.0 6.473123 6.473123 48289.500 76.986 1
14322.0 22.204047 22.204047 9790874.500 3439.802 5
16969.0 11.948061 11.948061 499536.500 NaN 4
17072.0 8.845526 8.845526 77840.625 NaN 3
17304.0 8.641655 8.641655 191525.000 307.721 3
QUINTILE2 avgvol avg Startpoint
DATE PERMNO
1970-08-31 10559.0 4 9229.057592 1697.2 0
12626.0 4 3654.367470 894.4 0
12749.0 5 188206.566860 5828.6 0
13100.0 3 94127.319048 3477.2 0
13653.0 1 816.393162 268.8 0
13936.0 1 71547.050633 553.2 0
14322.0 5 195702.521519 6308.8 0
16969.0 4 3670.297872 2002.0 0
17072.0 3 3774.083333 3867.8 0
17304.0 3 12622.112903 1679.4 0
python pandas dataframe
python pandas dataframe
edited Nov 12 at 1:47
dmitriys
15119
asked Nov 11 at 23:07
hmmmbob
4381021
edited Nov 12 at 1:47
dmitriys
15119
asked Nov 11 at 23:07
hmmmbob
4381021
edited Nov 12 at 1:47
dmitriys
15119
edited Nov 12 at 1:47
dmitriys
15119
edited Nov 12 at 1:47
dmitriys
15119
15119
asked Nov 11 at 23:07
hmmmbob
4381021
asked Nov 11 at 23:07
hmmmbob
4381021
asked Nov 11 at 23:07
hmmmbob
4381021
4381021
Can you post some sample input data, and the corresponding output data you'd like? Preferably in a reproducible format. It's usually a bad idea to use explicit loops like this, there is almost certainly a faster/better approach, but it'll be hard for the community to help without explicit input/output data.
– smj
Nov 11 at 23:16
I tried and i hope that clears things up
– hmmmbob
Nov 11 at 23:21
@hmmmbob I'm not quite sure what you'r trying to do with that loop so an example of your expected output would be useful for people to help... However, how close doesdf.RET.mean(level=0)get you?
– Jon Clements♦
Nov 11 at 23:36
I am trying to add up several column values, starting with the index [i+1].. all they way to [i+1+a], then calculate the mean of that sum, and then append that value to my empty list called returns.
– hmmmbob
Nov 12 at 0:11
add a comment |
Can you post some sample input data, and the corresponding output data you'd like? Preferably in a reproducible format. It's usually a bad idea to use explicit loops like this, there is almost certainly a faster/better approach, but it'll be hard for the community to help without explicit input/output data.
– smj
Nov 11 at 23:16
I tried and i hope that clears things up
– hmmmbob
Nov 11 at 23:21
@hmmmbob I'm not quite sure what you'r trying to do with that loop so an example of your expected output would be useful for people to help... However, how close doesdf.RET.mean(level=0)get you?
– Jon Clements♦
Nov 11 at 23:36
I am trying to add up several column values, starting with the index [i+1].. all they way to [i+1+a], then calculate the mean of that sum, and then append that value to my empty list called returns.
– hmmmbob
Nov 12 at 0:11
Can you post some sample input data, and the corresponding output data you'd like? Preferably in a reproducible format. It's usually a bad idea to use explicit loops like this, there is almost certainly a faster/better approach, but it'll be hard for the community to help without explicit input/output data.
– smj
Nov 11 at 23:16
Can you post some sample input data, and the corresponding output data you'd like? Preferably in a reproducible format. It's usually a bad idea to use explicit loops like this, there is almost certainly a faster/better approach, but it'll be hard for the community to help without explicit input/output data.
– smj
Nov 11 at 23:16
I tried and i hope that clears things up
– hmmmbob
Nov 11 at 23:21
I tried and i hope that clears things up
– hmmmbob
Nov 11 at 23:21
@hmmmbob I'm not quite sure what you'r trying to do with that loop so an example of your expected output would be useful for people to help... However, how close does
df.RET.mean(level=0) get you?– Jon Clements♦
Nov 11 at 23:36
@hmmmbob I'm not quite sure what you'r trying to do with that loop so an example of your expected output would be useful for people to help... However, how close does
df.RET.mean(level=0) get you?– Jon Clements♦
Nov 11 at 23:36
I am trying to add up several column values, starting with the index [i+1].. all they way to [i+1+a], then calculate the mean of that sum, and then append that value to my empty list called returns.
– hmmmbob
Nov 12 at 0:11
I am trying to add up several column values, starting with the index [i+1].. all they way to [i+1+a], then calculate the mean of that sum, and then append that value to my empty list called returns.
– hmmmbob
Nov 12 at 0:11
add a comment |
active
oldest
votes
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%2f53254143%2fpandas-adding-column-values-in-a-loop-without-using-iloc%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53254143%2fpandas-adding-column-values-in-a-loop-without-using-iloc%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
Can you post some sample input data, and the corresponding output data you'd like? Preferably in a reproducible format. It's usually a bad idea to use explicit loops like this, there is almost certainly a faster/better approach, but it'll be hard for the community to help without explicit input/output data.
– smj
Nov 11 at 23:16
I tried and i hope that clears things up
– hmmmbob
Nov 11 at 23:21
@hmmmbob I'm not quite sure what you'r trying to do with that loop so an example of your expected output would be useful for people to help... However, how close does
df.RET.mean(level=0)get you?– Jon Clements♦
Nov 11 at 23:36
I am trying to add up several column values, starting with the index [i+1].. all they way to [i+1+a], then calculate the mean of that sum, and then append that value to my empty list called returns.
– hmmmbob
Nov 12 at 0:11