Conditionally Calculate Max and Min Values of a Column
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
1
I have a dataframe as below:
row_no stock_name last_price Var1
1 SAIL 501.00 0
2 SAIL 501.60 23
3 SAIL 500.00 0
4 SAIL 499.10 0
5 SAIL 499.40 0
6 SAIL 499.40 0
7 SAIL 502.00 0
8 SAIL 497.95 0
9 SAIL 495.55 20
10 SAIL 496.75 0
11 SAIL 496.75 0
12 SAIL 513.00 0
13 SAIL 497.00 0
14 SAIL 497.20 0
15 SAIL 497.00 0
16 SAIL 494.00 0
17 SAIL 497.00 0
18 SAIL 497.00 0
19 SAIL 497.00 0
20 SAIL 496.60 -9
21 SAIL 497.25 0
Need to Calculate Max and Min last_price when Var1 is not Zero.
When current Var1 is not equal to Zero, Max & Min last_price to be calculated between previous non zero value of Var1.
Example:
For Row No 9: Max last_price to be calculated as Max(last_price from row 9 to row 2) = 502 and Min last_price as Min(last_price from row 9 to row 2) = 495.55
I want the final dataframe as below:
row_no stock_name last_price Var1 Max_LTP Min_LTP
1 SAIL 501 0 0 0 0
2 SAIL 501.6 23 501.6 501
3 SAIL 500 0 0 0 0
4 SAIL 499.1 0 0 0
5 SAIL 499.4 0 0 0
6 SAIL 499.4 0 0 0
7 SAIL 502 0 0 0 0
8 SAIL 497.95 0 0 0
9 SAIL 495.55 20 502 495.55
10 SAIL 496.75 0 0 0
11 SAIL 496.75 0 0 0
12 SAIL 513 0 0 0 0
13 SAIL 497 0 0 0 0
14 SAIL 497.2 0 0 0
15 SAIL 497 0 0 0 0
16 SAIL 494 0 0 0 0
17 SAIL 497 0 0 0 0
18 SAIL 497 0 0 0 0
19 SAIL 497 0 0 0 0
20 SAIL 496.6 -9 513 494
21 SAIL 497.25 0 0 0 0
python python-3.x pandas dataframe list-comprehension
asked Nov 23 '18 at 16:11
PravatPravat
9310
add a comment |
1
I have a dataframe as below:
row_no stock_name last_price Var1
1 SAIL 501.00 0
2 SAIL 501.60 23
3 SAIL 500.00 0
4 SAIL 499.10 0
5 SAIL 499.40 0
6 SAIL 499.40 0
7 SAIL 502.00 0
8 SAIL 497.95 0
9 SAIL 495.55 20
10 SAIL 496.75 0
11 SAIL 496.75 0
12 SAIL 513.00 0
13 SAIL 497.00 0
14 SAIL 497.20 0
15 SAIL 497.00 0
16 SAIL 494.00 0
17 SAIL 497.00 0
18 SAIL 497.00 0
19 SAIL 497.00 0
20 SAIL 496.60 -9
21 SAIL 497.25 0
Need to Calculate Max and Min last_price when Var1 is not Zero.
When current Var1 is not equal to Zero, Max & Min last_price to be calculated between previous non zero value of Var1.
Example:
For Row No 9: Max last_price to be calculated as Max(last_price from row 9 to row 2) = 502 and Min last_price as Min(last_price from row 9 to row 2) = 495.55
I want the final dataframe as below:
row_no stock_name last_price Var1 Max_LTP Min_LTP
1 SAIL 501 0 0 0 0
2 SAIL 501.6 23 501.6 501
3 SAIL 500 0 0 0 0
4 SAIL 499.1 0 0 0
5 SAIL 499.4 0 0 0
6 SAIL 499.4 0 0 0
7 SAIL 502 0 0 0 0
8 SAIL 497.95 0 0 0
9 SAIL 495.55 20 502 495.55
10 SAIL 496.75 0 0 0
11 SAIL 496.75 0 0 0
12 SAIL 513 0 0 0 0
13 SAIL 497 0 0 0 0
14 SAIL 497.2 0 0 0
15 SAIL 497 0 0 0 0
16 SAIL 494 0 0 0 0
17 SAIL 497 0 0 0 0
18 SAIL 497 0 0 0 0
19 SAIL 497 0 0 0 0
20 SAIL 496.6 -9 513 494
21 SAIL 497.25 0 0 0 0
python python-3.x pandas dataframe list-comprehension
asked Nov 23 '18 at 16:11
PravatPravat
9310
subset a df where Var1 !=0 and then do what you need to do
– Ken Dekalb
Nov 23 '18 at 16:13
add a comment |
1
1
1
1
I have a dataframe as below:
row_no stock_name last_price Var1
1 SAIL 501.00 0
2 SAIL 501.60 23
3 SAIL 500.00 0
4 SAIL 499.10 0
5 SAIL 499.40 0
6 SAIL 499.40 0
7 SAIL 502.00 0
8 SAIL 497.95 0
9 SAIL 495.55 20
10 SAIL 496.75 0
11 SAIL 496.75 0
12 SAIL 513.00 0
13 SAIL 497.00 0
14 SAIL 497.20 0
15 SAIL 497.00 0
16 SAIL 494.00 0
17 SAIL 497.00 0
18 SAIL 497.00 0
19 SAIL 497.00 0
20 SAIL 496.60 -9
21 SAIL 497.25 0
Need to Calculate Max and Min last_price when Var1 is not Zero.
When current Var1 is not equal to Zero, Max & Min last_price to be calculated between previous non zero value of Var1.
Example:
For Row No 9: Max last_price to be calculated as Max(last_price from row 9 to row 2) = 502 and Min last_price as Min(last_price from row 9 to row 2) = 495.55
I want the final dataframe as below:
row_no stock_name last_price Var1 Max_LTP Min_LTP
1 SAIL 501 0 0 0 0
2 SAIL 501.6 23 501.6 501
3 SAIL 500 0 0 0 0
4 SAIL 499.1 0 0 0
5 SAIL 499.4 0 0 0
6 SAIL 499.4 0 0 0
7 SAIL 502 0 0 0 0
8 SAIL 497.95 0 0 0
9 SAIL 495.55 20 502 495.55
10 SAIL 496.75 0 0 0
11 SAIL 496.75 0 0 0
12 SAIL 513 0 0 0 0
13 SAIL 497 0 0 0 0
14 SAIL 497.2 0 0 0
15 SAIL 497 0 0 0 0
16 SAIL 494 0 0 0 0
17 SAIL 497 0 0 0 0
18 SAIL 497 0 0 0 0
19 SAIL 497 0 0 0 0
20 SAIL 496.6 -9 513 494
21 SAIL 497.25 0 0 0 0
python python-3.x pandas dataframe list-comprehension
asked Nov 23 '18 at 16:11
PravatPravat
9310
I have a dataframe as below:
row_no stock_name last_price Var1
1 SAIL 501.00 0
2 SAIL 501.60 23
3 SAIL 500.00 0
4 SAIL 499.10 0
5 SAIL 499.40 0
6 SAIL 499.40 0
7 SAIL 502.00 0
8 SAIL 497.95 0
9 SAIL 495.55 20
10 SAIL 496.75 0
11 SAIL 496.75 0
12 SAIL 513.00 0
13 SAIL 497.00 0
14 SAIL 497.20 0
15 SAIL 497.00 0
16 SAIL 494.00 0
17 SAIL 497.00 0
18 SAIL 497.00 0
19 SAIL 497.00 0
20 SAIL 496.60 -9
21 SAIL 497.25 0
Need to Calculate Max and Min last_price when Var1 is not Zero.
When current Var1 is not equal to Zero, Max & Min last_price to be calculated between previous non zero value of Var1.
Example:
For Row No 9: Max last_price to be calculated as Max(last_price from row 9 to row 2) = 502 and Min last_price as Min(last_price from row 9 to row 2) = 495.55
I want the final dataframe as below:
row_no stock_name last_price Var1 Max_LTP Min_LTP
1 SAIL 501 0 0 0 0
2 SAIL 501.6 23 501.6 501
3 SAIL 500 0 0 0 0
4 SAIL 499.1 0 0 0
5 SAIL 499.4 0 0 0
6 SAIL 499.4 0 0 0
7 SAIL 502 0 0 0 0
8 SAIL 497.95 0 0 0
9 SAIL 495.55 20 502 495.55
10 SAIL 496.75 0 0 0
11 SAIL 496.75 0 0 0
12 SAIL 513 0 0 0 0
13 SAIL 497 0 0 0 0
14 SAIL 497.2 0 0 0
15 SAIL 497 0 0 0 0
16 SAIL 494 0 0 0 0
17 SAIL 497 0 0 0 0
18 SAIL 497 0 0 0 0
19 SAIL 497 0 0 0 0
20 SAIL 496.6 -9 513 494
21 SAIL 497.25 0 0 0 0
python python-3.x pandas dataframe list-comprehension
python python-3.x pandas dataframe list-comprehension
asked Nov 23 '18 at 16:11
PravatPravat
9310
asked Nov 23 '18 at 16:11
PravatPravat
9310
edited Nov 23 '18 at 16:31
Pravat
asked Nov 23 '18 at 16:11
PravatPravat
9310
asked Nov 23 '18 at 16:11
PravatPravat
9310
asked Nov 23 '18 at 16:11
PravatPravat
9310
9310
subset a df where Var1 !=0 and then do what you need to do
– Ken Dekalb
Nov 23 '18 at 16:13
add a comment |
subset a df where Var1 !=0 and then do what you need to do
– Ken Dekalb
Nov 23 '18 at 16:13
subset a df where Var1 !=0 and then do what you need to do
– Ken Dekalb
Nov 23 '18 at 16:13
subset a df where Var1 !=0 and then do what you need to do
– Ken Dekalb
Nov 23 '18 at 16:13
add a comment |
1 Answer
1
active
oldest
votes
3
Using Var1 with shift and cumsum create the group key , then groupby , and assign the min and max value back to the original dataframe
mask=df.Var1.ne(0).shift().fillna(0).cumsum()
s=df.groupby(mask).last_price.agg(['min','max'])
s=s[df['Var1'].ne(0).groupby(mask).agg('any')]
s.index=df.index[df.Var1.ne(0)]
df[['Min_LTP','Max_LTP']]=s
df
Out[274]:
row_no stock_name last_price Var1 Max_LTP Min_LTP
0 1 SAIL 501.00 0 NaN NaN
1 2 SAIL 501.60 23 501.6 501.00
2 3 SAIL 500.00 0 NaN NaN
3 4 SAIL 499.10 0 NaN NaN
4 5 SAIL 499.40 0 NaN NaN
5 6 SAIL 499.40 0 NaN NaN
6 7 SAIL 502.00 0 NaN NaN
7 8 SAIL 497.95 0 NaN NaN
8 9 SAIL 495.55 20 502.0 495.55
9 10 SAIL 496.75 0 NaN NaN
10 11 SAIL 496.75 0 NaN NaN
11 12 SAIL 513.00 0 NaN NaN
12 13 SAIL 497.00 0 NaN NaN
13 14 SAIL 497.20 0 NaN NaN
14 15 SAIL 497.00 0 NaN NaN
15 16 SAIL 494.00 0 NaN NaN
16 17 SAIL 497.00 0 NaN NaN
17 18 SAIL 497.00 0 NaN NaN
18 19 SAIL 497.00 0 NaN NaN
19 20 SAIL 496.60 -9 513.0 494.00
20 21 SAIL 497.25 0 NaN NaN
answered Nov 23 '18 at 16:23
Wen-BenWen-Ben
124k83771
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%2f53449898%2fconditionally-calculate-max-and-min-values-of-a-column%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
3
Using Var1 with shift and cumsum create the group key , then groupby , and assign the min and max value back to the original dataframe
mask=df.Var1.ne(0).shift().fillna(0).cumsum()
s=df.groupby(mask).last_price.agg(['min','max'])
s=s[df['Var1'].ne(0).groupby(mask).agg('any')]
s.index=df.index[df.Var1.ne(0)]
df[['Min_LTP','Max_LTP']]=s
df
Out[274]:
row_no stock_name last_price Var1 Max_LTP Min_LTP
0 1 SAIL 501.00 0 NaN NaN
1 2 SAIL 501.60 23 501.6 501.00
2 3 SAIL 500.00 0 NaN NaN
3 4 SAIL 499.10 0 NaN NaN
4 5 SAIL 499.40 0 NaN NaN
5 6 SAIL 499.40 0 NaN NaN
6 7 SAIL 502.00 0 NaN NaN
7 8 SAIL 497.95 0 NaN NaN
8 9 SAIL 495.55 20 502.0 495.55
9 10 SAIL 496.75 0 NaN NaN
10 11 SAIL 496.75 0 NaN NaN
11 12 SAIL 513.00 0 NaN NaN
12 13 SAIL 497.00 0 NaN NaN
13 14 SAIL 497.20 0 NaN NaN
14 15 SAIL 497.00 0 NaN NaN
15 16 SAIL 494.00 0 NaN NaN
16 17 SAIL 497.00 0 NaN NaN
17 18 SAIL 497.00 0 NaN NaN
18 19 SAIL 497.00 0 NaN NaN
19 20 SAIL 496.60 -9 513.0 494.00
20 21 SAIL 497.25 0 NaN NaN
answered Nov 23 '18 at 16:23
Wen-BenWen-Ben
124k83771
add a comment |
3
Using Var1 with shift and cumsum create the group key , then groupby , and assign the min and max value back to the original dataframe
mask=df.Var1.ne(0).shift().fillna(0).cumsum()
s=df.groupby(mask).last_price.agg(['min','max'])
s=s[df['Var1'].ne(0).groupby(mask).agg('any')]
s.index=df.index[df.Var1.ne(0)]
df[['Min_LTP','Max_LTP']]=s
df
Out[274]:
row_no stock_name last_price Var1 Max_LTP Min_LTP
0 1 SAIL 501.00 0 NaN NaN
1 2 SAIL 501.60 23 501.6 501.00
2 3 SAIL 500.00 0 NaN NaN
3 4 SAIL 499.10 0 NaN NaN
4 5 SAIL 499.40 0 NaN NaN
5 6 SAIL 499.40 0 NaN NaN
6 7 SAIL 502.00 0 NaN NaN
7 8 SAIL 497.95 0 NaN NaN
8 9 SAIL 495.55 20 502.0 495.55
9 10 SAIL 496.75 0 NaN NaN
10 11 SAIL 496.75 0 NaN NaN
11 12 SAIL 513.00 0 NaN NaN
12 13 SAIL 497.00 0 NaN NaN
13 14 SAIL 497.20 0 NaN NaN
14 15 SAIL 497.00 0 NaN NaN
15 16 SAIL 494.00 0 NaN NaN
16 17 SAIL 497.00 0 NaN NaN
17 18 SAIL 497.00 0 NaN NaN
18 19 SAIL 497.00 0 NaN NaN
19 20 SAIL 496.60 -9 513.0 494.00
20 21 SAIL 497.25 0 NaN NaN
answered Nov 23 '18 at 16:23
Wen-BenWen-Ben
124k83771
add a comment |
3
3
3
Using Var1 with shift and cumsum create the group key , then groupby , and assign the min and max value back to the original dataframe
mask=df.Var1.ne(0).shift().fillna(0).cumsum()
s=df.groupby(mask).last_price.agg(['min','max'])
s=s[df['Var1'].ne(0).groupby(mask).agg('any')]
s.index=df.index[df.Var1.ne(0)]
df[['Min_LTP','Max_LTP']]=s
df
Out[274]:
row_no stock_name last_price Var1 Max_LTP Min_LTP
0 1 SAIL 501.00 0 NaN NaN
1 2 SAIL 501.60 23 501.6 501.00
2 3 SAIL 500.00 0 NaN NaN
3 4 SAIL 499.10 0 NaN NaN
4 5 SAIL 499.40 0 NaN NaN
5 6 SAIL 499.40 0 NaN NaN
6 7 SAIL 502.00 0 NaN NaN
7 8 SAIL 497.95 0 NaN NaN
8 9 SAIL 495.55 20 502.0 495.55
9 10 SAIL 496.75 0 NaN NaN
10 11 SAIL 496.75 0 NaN NaN
11 12 SAIL 513.00 0 NaN NaN
12 13 SAIL 497.00 0 NaN NaN
13 14 SAIL 497.20 0 NaN NaN
14 15 SAIL 497.00 0 NaN NaN
15 16 SAIL 494.00 0 NaN NaN
16 17 SAIL 497.00 0 NaN NaN
17 18 SAIL 497.00 0 NaN NaN
18 19 SAIL 497.00 0 NaN NaN
19 20 SAIL 496.60 -9 513.0 494.00
20 21 SAIL 497.25 0 NaN NaN
answered Nov 23 '18 at 16:23
Wen-BenWen-Ben
124k83771
Using Var1 with shift and cumsum create the group key , then groupby , and assign the min and max value back to the original dataframe
mask=df.Var1.ne(0).shift().fillna(0).cumsum()
s=df.groupby(mask).last_price.agg(['min','max'])
s=s[df['Var1'].ne(0).groupby(mask).agg('any')]
s.index=df.index[df.Var1.ne(0)]
df[['Min_LTP','Max_LTP']]=s
df
Out[274]:
row_no stock_name last_price Var1 Max_LTP Min_LTP
0 1 SAIL 501.00 0 NaN NaN
1 2 SAIL 501.60 23 501.6 501.00
2 3 SAIL 500.00 0 NaN NaN
3 4 SAIL 499.10 0 NaN NaN
4 5 SAIL 499.40 0 NaN NaN
5 6 SAIL 499.40 0 NaN NaN
6 7 SAIL 502.00 0 NaN NaN
7 8 SAIL 497.95 0 NaN NaN
8 9 SAIL 495.55 20 502.0 495.55
9 10 SAIL 496.75 0 NaN NaN
10 11 SAIL 496.75 0 NaN NaN
11 12 SAIL 513.00 0 NaN NaN
12 13 SAIL 497.00 0 NaN NaN
13 14 SAIL 497.20 0 NaN NaN
14 15 SAIL 497.00 0 NaN NaN
15 16 SAIL 494.00 0 NaN NaN
16 17 SAIL 497.00 0 NaN NaN
17 18 SAIL 497.00 0 NaN NaN
18 19 SAIL 497.00 0 NaN NaN
19 20 SAIL 496.60 -9 513.0 494.00
20 21 SAIL 497.25 0 NaN NaN
answered Nov 23 '18 at 16:23
Wen-BenWen-Ben
124k83771
answered Nov 23 '18 at 16:23
Wen-BenWen-Ben
124k83771
answered Nov 23 '18 at 16:23
Wen-BenWen-Ben
124k83771
answered Nov 23 '18 at 16:23
Wen-BenWen-Ben
124k83771
124k83771
add a comment |
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.
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%2f53449898%2fconditionally-calculate-max-and-min-values-of-a-column%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
subset a df where Var1 !=0 and then do what you need to do
– Ken Dekalb
Nov 23 '18 at 16:13