Pandas - how to get count of negative and positive values in a row
How can I count the number (count) and sum of negative and positive values in a row without many loops in pandas? I want to get the maximum sum of consecutive negatives and also the maximum sum of consecutive positives . Example dataframe:
datetime Value
2018-11-12 15:10:00 2.00
2018-11-12 15:20:00 -10.50
2018-11-12 15:30:00 10.50
2018-11-12 15:40:00 7.50
2018-11-12 15:50:00 8.00
2018-11-12 16:10:00 -20.00
2018-11-12 16:20:00 -10.00
I would like the output to be:
Max # of negatives in a row: 2, Total value = -30.00
Max # of positives in a row: 3, Total value = 26.00
A simple pd.groupby() doesn't solve the issue since it doesn't group the order. Maybe some sort of sql query? pd.query()?
python pandas
edited Nov 24 '18 at 21:37
2ps
7,4722930
asked Nov 24 '18 at 19:37
Matt ElgazarMatt Elgazar
609
|
show 4 more comments
How can I count the number (count) and sum of negative and positive values in a row without many loops in pandas? I want to get the maximum sum of consecutive negatives and also the maximum sum of consecutive positives . Example dataframe:
datetime Value
2018-11-12 15:10:00 2.00
2018-11-12 15:20:00 -10.50
2018-11-12 15:30:00 10.50
2018-11-12 15:40:00 7.50
2018-11-12 15:50:00 8.00
2018-11-12 16:10:00 -20.00
2018-11-12 16:20:00 -10.00
I would like the output to be:
Max # of negatives in a row: 2, Total value = -30.00
Max # of positives in a row: 3, Total value = 26.00
A simple pd.groupby() doesn't solve the issue since it doesn't group the order. Maybe some sort of sql query? pd.query()?
python pandas
edited Nov 24 '18 at 21:37
2ps
7,4722930
asked Nov 24 '18 at 19:37
Matt ElgazarMatt Elgazar
609
Can you please explain your expected output? Is it in accordance to the shared input?
– Mayank Porwal
Nov 24 '18 at 19:44
row? or column? your sample output and sample data do not seem to be matching. i Presume you want the Value column to show positives and negatives, which would make #3 negatives for a value of -40, and #4 positives for a value of 27.
– Paritosh Singh
Nov 24 '18 at 19:53
The output does match.. I want the number of negatives in a row, and to sum their total values. Since it's time series I want to see how many negatives happen in a row, and what that total sum comes out to be. I also want that for positives but if I figure out one the other should be easy.
– Matt Elgazar
Nov 24 '18 at 19:56
Im afraid i dont follow. could you elaborate? for example, point me to values which add up to -30 that are being used together in your sample.
– Paritosh Singh
Nov 24 '18 at 19:58
@ParitoshSingh looks like the 26 is 10.50 + 7.50 + 8.00 and the -30 is the last two rows of -20.00 and -10.00 - not sure what happened to the first two rows or what happens otherwise regarding negative/positives though... Matt - can you elaborate with expected output and maybe rephrase your criteria - it's a little ambiguous?
– Jon Clements♦
Nov 24 '18 at 20:13
|
show 4 more comments
How can I count the number (count) and sum of negative and positive values in a row without many loops in pandas? I want to get the maximum sum of consecutive negatives and also the maximum sum of consecutive positives . Example dataframe:
datetime Value
2018-11-12 15:10:00 2.00
2018-11-12 15:20:00 -10.50
2018-11-12 15:30:00 10.50
2018-11-12 15:40:00 7.50
2018-11-12 15:50:00 8.00
2018-11-12 16:10:00 -20.00
2018-11-12 16:20:00 -10.00
I would like the output to be:
Max # of negatives in a row: 2, Total value = -30.00
Max # of positives in a row: 3, Total value = 26.00
A simple pd.groupby() doesn't solve the issue since it doesn't group the order. Maybe some sort of sql query? pd.query()?
python pandas
edited Nov 24 '18 at 21:37
2ps
7,4722930
asked Nov 24 '18 at 19:37
Matt ElgazarMatt Elgazar
609
How can I count the number (count) and sum of negative and positive values in a row without many loops in pandas? I want to get the maximum sum of consecutive negatives and also the maximum sum of consecutive positives . Example dataframe:
datetime Value
2018-11-12 15:10:00 2.00
2018-11-12 15:20:00 -10.50
2018-11-12 15:30:00 10.50
2018-11-12 15:40:00 7.50
2018-11-12 15:50:00 8.00
2018-11-12 16:10:00 -20.00
2018-11-12 16:20:00 -10.00
I would like the output to be:
Max # of negatives in a row: 2, Total value = -30.00
Max # of positives in a row: 3, Total value = 26.00
A simple pd.groupby() doesn't solve the issue since it doesn't group the order. Maybe some sort of sql query? pd.query()?
python pandas
python pandas
edited Nov 24 '18 at 21:37
2ps
7,4722930
asked Nov 24 '18 at 19:37
Matt ElgazarMatt Elgazar
609
edited Nov 24 '18 at 21:37
2ps
7,4722930
asked Nov 24 '18 at 19:37
Matt ElgazarMatt Elgazar
609
edited Nov 24 '18 at 21:37
2ps
7,4722930
edited Nov 24 '18 at 21:37
2ps
7,4722930
edited Nov 24 '18 at 21:37
2ps
7,4722930
7,4722930
asked Nov 24 '18 at 19:37
Matt ElgazarMatt Elgazar
609
asked Nov 24 '18 at 19:37
Matt ElgazarMatt Elgazar
609
asked Nov 24 '18 at 19:37
Matt ElgazarMatt Elgazar
609
609
Can you please explain your expected output? Is it in accordance to the shared input?
– Mayank Porwal
Nov 24 '18 at 19:44
row? or column? your sample output and sample data do not seem to be matching. i Presume you want the Value column to show positives and negatives, which would make #3 negatives for a value of -40, and #4 positives for a value of 27.
– Paritosh Singh
Nov 24 '18 at 19:53
The output does match.. I want the number of negatives in a row, and to sum their total values. Since it's time series I want to see how many negatives happen in a row, and what that total sum comes out to be. I also want that for positives but if I figure out one the other should be easy.
– Matt Elgazar
Nov 24 '18 at 19:56
Im afraid i dont follow. could you elaborate? for example, point me to values which add up to -30 that are being used together in your sample.
– Paritosh Singh
Nov 24 '18 at 19:58
@ParitoshSingh looks like the 26 is 10.50 + 7.50 + 8.00 and the -30 is the last two rows of -20.00 and -10.00 - not sure what happened to the first two rows or what happens otherwise regarding negative/positives though... Matt - can you elaborate with expected output and maybe rephrase your criteria - it's a little ambiguous?
– Jon Clements♦
Nov 24 '18 at 20:13
|
show 4 more comments
Can you please explain your expected output? Is it in accordance to the shared input?
– Mayank Porwal
Nov 24 '18 at 19:44
row? or column? your sample output and sample data do not seem to be matching. i Presume you want the Value column to show positives and negatives, which would make #3 negatives for a value of -40, and #4 positives for a value of 27.
– Paritosh Singh
Nov 24 '18 at 19:53
The output does match.. I want the number of negatives in a row, and to sum their total values. Since it's time series I want to see how many negatives happen in a row, and what that total sum comes out to be. I also want that for positives but if I figure out one the other should be easy.
– Matt Elgazar
Nov 24 '18 at 19:56
Im afraid i dont follow. could you elaborate? for example, point me to values which add up to -30 that are being used together in your sample.
– Paritosh Singh
Nov 24 '18 at 19:58
@ParitoshSingh looks like the 26 is 10.50 + 7.50 + 8.00 and the -30 is the last two rows of -20.00 and -10.00 - not sure what happened to the first two rows or what happens otherwise regarding negative/positives though... Matt - can you elaborate with expected output and maybe rephrase your criteria - it's a little ambiguous?
– Jon Clements♦
Nov 24 '18 at 20:13
Can you please explain your expected output? Is it in accordance to the shared input?
– Mayank Porwal
Nov 24 '18 at 19:44
Can you please explain your expected output? Is it in accordance to the shared input?
– Mayank Porwal
Nov 24 '18 at 19:44
row? or column? your sample output and sample data do not seem to be matching. i Presume you want the Value column to show positives and negatives, which would make #3 negatives for a value of -40, and #4 positives for a value of 27.
– Paritosh Singh
Nov 24 '18 at 19:53
row? or column? your sample output and sample data do not seem to be matching. i Presume you want the Value column to show positives and negatives, which would make #3 negatives for a value of -40, and #4 positives for a value of 27.
– Paritosh Singh
Nov 24 '18 at 19:53
The output does match.. I want the number of negatives in a row, and to sum their total values. Since it's time series I want to see how many negatives happen in a row, and what that total sum comes out to be. I also want that for positives but if I figure out one the other should be easy.
– Matt Elgazar
Nov 24 '18 at 19:56
The output does match.. I want the number of negatives in a row, and to sum their total values. Since it's time series I want to see how many negatives happen in a row, and what that total sum comes out to be. I also want that for positives but if I figure out one the other should be easy.
– Matt Elgazar
Nov 24 '18 at 19:56
Im afraid i dont follow. could you elaborate? for example, point me to values which add up to -30 that are being used together in your sample.
– Paritosh Singh
Nov 24 '18 at 19:58
Im afraid i dont follow. could you elaborate? for example, point me to values which add up to -30 that are being used together in your sample.
– Paritosh Singh
Nov 24 '18 at 19:58
@ParitoshSingh looks like the 26 is 10.50 + 7.50 + 8.00 and the -30 is the last two rows of -20.00 and -10.00 - not sure what happened to the first two rows or what happens otherwise regarding negative/positives though... Matt - can you elaborate with expected output and maybe rephrase your criteria - it's a little ambiguous?
– Jon Clements♦
Nov 24 '18 at 20:13
@ParitoshSingh looks like the 26 is 10.50 + 7.50 + 8.00 and the -30 is the last two rows of -20.00 and -10.00 - not sure what happened to the first two rows or what happens otherwise regarding negative/positives though... Matt - can you elaborate with expected output and maybe rephrase your criteria - it's a little ambiguous?
– Jon Clements♦
Nov 24 '18 at 20:13
|
show 4 more comments
1 Answer
1
active
oldest
votes
My approach would be to firstly give every "same sign in a row"-group a different number in order to proceed with groupby.
Comparing Value with its shifted array with regards to sign gives the separators of those groups. Cumulative summation then leads to unique numbers, identifiers if you want:
df['grpnum'] = (np.sign(df.Value) != np.sign(df.Value.shift())).cumsum()
Then you can groupby this identifier and calculate the groups length, sum and their sign:
ranking = df.groupby('grpnum').agg([sum, len, lambda x: sum(x)>0]).Value
which leads to
sum len <lambda>
grpnum
1 2.0 1.0 True
2 -10.5 1.0 False
3 26.0 3.0 True
4 -30.0 2.0 False
print(ranking.groupby('<lambda>')['sum', 'len'].apply(lambda g: g[g['len']==g['len'].max()]))
sum len
<lambda> grpnum
True 3 26.0 3.0
False 4 -30.0 2.0
answered Nov 24 '18 at 22:12
SpghttCdSpghttCd
4,2472313
This is great! However this is pretty slow. Any thoughts on how to speed the code up?
– Matt Elgazar
Jan 15 at 19:47
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%2f53461722%2fpandas-how-to-get-count-of-negative-and-positive-values-in-a-row%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
My approach would be to firstly give every "same sign in a row"-group a different number in order to proceed with groupby.
Comparing Value with its shifted array with regards to sign gives the separators of those groups. Cumulative summation then leads to unique numbers, identifiers if you want:
df['grpnum'] = (np.sign(df.Value) != np.sign(df.Value.shift())).cumsum()
Then you can groupby this identifier and calculate the groups length, sum and their sign:
ranking = df.groupby('grpnum').agg([sum, len, lambda x: sum(x)>0]).Value
which leads to
sum len <lambda>
grpnum
1 2.0 1.0 True
2 -10.5 1.0 False
3 26.0 3.0 True
4 -30.0 2.0 False
print(ranking.groupby('<lambda>')['sum', 'len'].apply(lambda g: g[g['len']==g['len'].max()]))
sum len
<lambda> grpnum
True 3 26.0 3.0
False 4 -30.0 2.0
answered Nov 24 '18 at 22:12
SpghttCdSpghttCd
4,2472313
This is great! However this is pretty slow. Any thoughts on how to speed the code up?
– Matt Elgazar
Jan 15 at 19:47
add a comment |
My approach would be to firstly give every "same sign in a row"-group a different number in order to proceed with groupby.
Comparing Value with its shifted array with regards to sign gives the separators of those groups. Cumulative summation then leads to unique numbers, identifiers if you want:
df['grpnum'] = (np.sign(df.Value) != np.sign(df.Value.shift())).cumsum()
Then you can groupby this identifier and calculate the groups length, sum and their sign:
ranking = df.groupby('grpnum').agg([sum, len, lambda x: sum(x)>0]).Value
which leads to
sum len <lambda>
grpnum
1 2.0 1.0 True
2 -10.5 1.0 False
3 26.0 3.0 True
4 -30.0 2.0 False
print(ranking.groupby('<lambda>')['sum', 'len'].apply(lambda g: g[g['len']==g['len'].max()]))
sum len
<lambda> grpnum
True 3 26.0 3.0
False 4 -30.0 2.0
answered Nov 24 '18 at 22:12
SpghttCdSpghttCd
4,2472313
This is great! However this is pretty slow. Any thoughts on how to speed the code up?
– Matt Elgazar
Jan 15 at 19:47
add a comment |
My approach would be to firstly give every "same sign in a row"-group a different number in order to proceed with groupby.
Comparing Value with its shifted array with regards to sign gives the separators of those groups. Cumulative summation then leads to unique numbers, identifiers if you want:
df['grpnum'] = (np.sign(df.Value) != np.sign(df.Value.shift())).cumsum()
Then you can groupby this identifier and calculate the groups length, sum and their sign:
ranking = df.groupby('grpnum').agg([sum, len, lambda x: sum(x)>0]).Value
which leads to
sum len <lambda>
grpnum
1 2.0 1.0 True
2 -10.5 1.0 False
3 26.0 3.0 True
4 -30.0 2.0 False
print(ranking.groupby('<lambda>')['sum', 'len'].apply(lambda g: g[g['len']==g['len'].max()]))
sum len
<lambda> grpnum
True 3 26.0 3.0
False 4 -30.0 2.0
answered Nov 24 '18 at 22:12
SpghttCdSpghttCd
4,2472313
My approach would be to firstly give every "same sign in a row"-group a different number in order to proceed with groupby.
Comparing Value with its shifted array with regards to sign gives the separators of those groups. Cumulative summation then leads to unique numbers, identifiers if you want:
df['grpnum'] = (np.sign(df.Value) != np.sign(df.Value.shift())).cumsum()
Then you can groupby this identifier and calculate the groups length, sum and their sign:
ranking = df.groupby('grpnum').agg([sum, len, lambda x: sum(x)>0]).Value
which leads to
sum len <lambda>
grpnum
1 2.0 1.0 True
2 -10.5 1.0 False
3 26.0 3.0 True
4 -30.0 2.0 False
print(ranking.groupby('<lambda>')['sum', 'len'].apply(lambda g: g[g['len']==g['len'].max()]))
sum len
<lambda> grpnum
True 3 26.0 3.0
False 4 -30.0 2.0
answered Nov 24 '18 at 22:12
SpghttCdSpghttCd
4,2472313
edited Nov 25 '18 at 23:58
answered Nov 24 '18 at 22:12
SpghttCdSpghttCd
4,2472313
answered Nov 24 '18 at 22:12
SpghttCdSpghttCd
4,2472313
answered Nov 24 '18 at 22:12
SpghttCdSpghttCd
4,2472313
4,2472313
This is great! However this is pretty slow. Any thoughts on how to speed the code up?
– Matt Elgazar
Jan 15 at 19:47
add a comment |
This is great! However this is pretty slow. Any thoughts on how to speed the code up?
– Matt Elgazar
Jan 15 at 19:47
This is great! However this is pretty slow. Any thoughts on how to speed the code up?
– Matt Elgazar
Jan 15 at 19:47
This is great! However this is pretty slow. Any thoughts on how to speed the code up?
– Matt Elgazar
Jan 15 at 19:47
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.
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%2f53461722%2fpandas-how-to-get-count-of-negative-and-positive-values-in-a-row%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 please explain your expected output? Is it in accordance to the shared input?
– Mayank Porwal
Nov 24 '18 at 19:44
row? or column? your sample output and sample data do not seem to be matching. i Presume you want the Value column to show positives and negatives, which would make #3 negatives for a value of -40, and #4 positives for a value of 27.
– Paritosh Singh
Nov 24 '18 at 19:53
The output does match.. I want the number of negatives in a row, and to sum their total values. Since it's time series I want to see how many negatives happen in a row, and what that total sum comes out to be. I also want that for positives but if I figure out one the other should be easy.
– Matt Elgazar
Nov 24 '18 at 19:56
Im afraid i dont follow. could you elaborate? for example, point me to values which add up to -30 that are being used together in your sample.
– Paritosh Singh
Nov 24 '18 at 19:58
@ParitoshSingh looks like the 26 is 10.50 + 7.50 + 8.00 and the -30 is the last two rows of -20.00 and -10.00 - not sure what happened to the first two rows or what happens otherwise regarding negative/positives though... Matt - can you elaborate with expected output and maybe rephrase your criteria - it's a little ambiguous?
– Jon Clements♦
Nov 24 '18 at 20:13