Mixing
enumerating grouped pandas dataframe
.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 pandas data frame where each row is a sub-sequence. I want to do the following;
- Group the data by 'SN' then order each group by 'date'.
- Create an additional column called 'steps' which is an enumeration of rows in each group from 0-to-n (0:n) based on this CONDITION: within each ordered group, start enumeration from 0 until 'event' equals 1, repeat this within each group. ALWAYS restart enumeration each time 'SN' changes. Please refer to code and example images for further clarity...Thanks in advance guys!
The code below creates the original data
data = {'date':['1/1/18', '1/2/18', '1/3/18', '1/1/18', '1/2/18', '2/2/18', 2/3/18', '2/4/18', '2/4/18', '2/4/18', '2/5/18'],
'SN': ['111', '111', '111', '222', '222','222', '333', '333', '333', '444', '444'],
'feat1': [1,2,3,4,5,6,7,8,9,10,11],
'event':[0,0,1,0,1,0,0,1,0,0,1]}
orig_data = pd.DataFrame(data)
orig_data:
The output desired is as follows:
pandas data-manipulation
edited Jan 3 at 7:45
Zanshin
7601523
asked Dec 18 '18 at 0:51
EdMEdM
206
|
show 2 more comments
1
I have a pandas data frame where each row is a sub-sequence. I want to do the following;
- Group the data by 'SN' then order each group by 'date'.
- Create an additional column called 'steps' which is an enumeration of rows in each group from 0-to-n (0:n) based on this CONDITION: within each ordered group, start enumeration from 0 until 'event' equals 1, repeat this within each group. ALWAYS restart enumeration each time 'SN' changes. Please refer to code and example images for further clarity...Thanks in advance guys!
The code below creates the original data
data = {'date':['1/1/18', '1/2/18', '1/3/18', '1/1/18', '1/2/18', '2/2/18', 2/3/18', '2/4/18', '2/4/18', '2/4/18', '2/5/18'],
'SN': ['111', '111', '111', '222', '222','222', '333', '333', '333', '444', '444'],
'feat1': [1,2,3,4,5,6,7,8,9,10,11],
'event':[0,0,1,0,1,0,0,1,0,0,1]}
orig_data = pd.DataFrame(data)
orig_data:
The output desired is as follows:
pandas data-manipulation
edited Jan 3 at 7:45
Zanshin
7601523
asked Dec 18 '18 at 0:51
EdMEdM
206
2
So do you have a question?
– jpp
Dec 18 '18 at 0:53
Your desired output seems nothing but addition of two new columnsEventandstepbased on the value ofeventandtimesteprespectively.
– Always Sunny
Dec 18 '18 at 0:58
@jpp yes the question as implied in item #2 is to enumerate rows within each group with the condition that if event == 1 within each group, restart the enumeration.
– EdM
Dec 18 '18 at 1:11
@Curious_MInd well said, would be great if you could demo how I can produce the output following conditions in item #1. After grouping by 'id' then enumerate rows within each group but restarting the enumeration when 'event' ==1 within each group. If 'event' !=1 continue the enumeration only within a group.
– EdM
Dec 18 '18 at 1:15
@Curious_MInd - I edited the desired output, I accidentally repeated the 'event' column.
– EdM
Dec 18 '18 at 1:22
|
show 2 more comments
1
1
1
I have a pandas data frame where each row is a sub-sequence. I want to do the following;
- Group the data by 'SN' then order each group by 'date'.
- Create an additional column called 'steps' which is an enumeration of rows in each group from 0-to-n (0:n) based on this CONDITION: within each ordered group, start enumeration from 0 until 'event' equals 1, repeat this within each group. ALWAYS restart enumeration each time 'SN' changes. Please refer to code and example images for further clarity...Thanks in advance guys!
The code below creates the original data
data = {'date':['1/1/18', '1/2/18', '1/3/18', '1/1/18', '1/2/18', '2/2/18', 2/3/18', '2/4/18', '2/4/18', '2/4/18', '2/5/18'],
'SN': ['111', '111', '111', '222', '222','222', '333', '333', '333', '444', '444'],
'feat1': [1,2,3,4,5,6,7,8,9,10,11],
'event':[0,0,1,0,1,0,0,1,0,0,1]}
orig_data = pd.DataFrame(data)
orig_data:
The output desired is as follows:
pandas data-manipulation
edited Jan 3 at 7:45
Zanshin
7601523
asked Dec 18 '18 at 0:51
EdMEdM
206
I have a pandas data frame where each row is a sub-sequence. I want to do the following;
- Group the data by 'SN' then order each group by 'date'.
- Create an additional column called 'steps' which is an enumeration of rows in each group from 0-to-n (0:n) based on this CONDITION: within each ordered group, start enumeration from 0 until 'event' equals 1, repeat this within each group. ALWAYS restart enumeration each time 'SN' changes. Please refer to code and example images for further clarity...Thanks in advance guys!
The code below creates the original data
data = {'date':['1/1/18', '1/2/18', '1/3/18', '1/1/18', '1/2/18', '2/2/18', 2/3/18', '2/4/18', '2/4/18', '2/4/18', '2/5/18'],
'SN': ['111', '111', '111', '222', '222','222', '333', '333', '333', '444', '444'],
'feat1': [1,2,3,4,5,6,7,8,9,10,11],
'event':[0,0,1,0,1,0,0,1,0,0,1]}
orig_data = pd.DataFrame(data)
orig_data:
The output desired is as follows:
pandas data-manipulation
pandas data-manipulation
edited Jan 3 at 7:45
Zanshin
7601523
asked Dec 18 '18 at 0:51
EdMEdM
206
edited Jan 3 at 7:45
Zanshin
7601523
asked Dec 18 '18 at 0:51
EdMEdM
206
edited Jan 3 at 7:45
Zanshin
7601523
edited Jan 3 at 7:45
Zanshin
7601523
edited Jan 3 at 7:45
Zanshin
7601523
7601523
asked Dec 18 '18 at 0:51
EdMEdM
206
asked Dec 18 '18 at 0:51
EdMEdM
206
asked Dec 18 '18 at 0:51
EdMEdM
206
206
2
So do you have a question?
– jpp
Dec 18 '18 at 0:53
Your desired output seems nothing but addition of two new columnsEventandstepbased on the value ofeventandtimesteprespectively.
– Always Sunny
Dec 18 '18 at 0:58
@jpp yes the question as implied in item #2 is to enumerate rows within each group with the condition that if event == 1 within each group, restart the enumeration.
– EdM
Dec 18 '18 at 1:11
@Curious_MInd well said, would be great if you could demo how I can produce the output following conditions in item #1. After grouping by 'id' then enumerate rows within each group but restarting the enumeration when 'event' ==1 within each group. If 'event' !=1 continue the enumeration only within a group.
– EdM
Dec 18 '18 at 1:15
@Curious_MInd - I edited the desired output, I accidentally repeated the 'event' column.
– EdM
Dec 18 '18 at 1:22
|
show 2 more comments
2
So do you have a question?
– jpp
Dec 18 '18 at 0:53
Your desired output seems nothing but addition of two new columnsEventandstepbased on the value ofeventandtimesteprespectively.
– Always Sunny
Dec 18 '18 at 0:58
@jpp yes the question as implied in item #2 is to enumerate rows within each group with the condition that if event == 1 within each group, restart the enumeration.
– EdM
Dec 18 '18 at 1:11
@Curious_MInd well said, would be great if you could demo how I can produce the output following conditions in item #1. After grouping by 'id' then enumerate rows within each group but restarting the enumeration when 'event' ==1 within each group. If 'event' !=1 continue the enumeration only within a group.
– EdM
Dec 18 '18 at 1:15
@Curious_MInd - I edited the desired output, I accidentally repeated the 'event' column.
– EdM
Dec 18 '18 at 1:22
2
2
So do you have a question?
– jpp
Dec 18 '18 at 0:53
So do you have a question?
– jpp
Dec 18 '18 at 0:53
Your desired output seems nothing but addition of two new columns
Event and step based on the value of event and timestep respectively.– Always Sunny
Dec 18 '18 at 0:58
Your desired output seems nothing but addition of two new columns
Event and step based on the value of event and timestep respectively.– Always Sunny
Dec 18 '18 at 0:58
@jpp yes the question as implied in item #2 is to enumerate rows within each group with the condition that if event == 1 within each group, restart the enumeration.
– EdM
Dec 18 '18 at 1:11
@jpp yes the question as implied in item #2 is to enumerate rows within each group with the condition that if event == 1 within each group, restart the enumeration.
– EdM
Dec 18 '18 at 1:11
@Curious_MInd well said, would be great if you could demo how I can produce the output following conditions in item #1. After grouping by 'id' then enumerate rows within each group but restarting the enumeration when 'event' ==1 within each group. If 'event' !=1 continue the enumeration only within a group.
– EdM
Dec 18 '18 at 1:15
@Curious_MInd well said, would be great if you could demo how I can produce the output following conditions in item #1. After grouping by 'id' then enumerate rows within each group but restarting the enumeration when 'event' ==1 within each group. If 'event' !=1 continue the enumeration only within a group.
– EdM
Dec 18 '18 at 1:15
@Curious_MInd - I edited the desired output, I accidentally repeated the 'event' column.
– EdM
Dec 18 '18 at 1:22
@Curious_MInd - I edited the desired output, I accidentally repeated the 'event' column.
– EdM
Dec 18 '18 at 1:22
|
show 2 more comments
1 Answer
1
active
oldest
votes
2
IIUC, you might try this;
EDITED
orig_data['steps_'] = orig_data.groupby(['SN',(1 == orig_data['event'].shift(1)).cumsum()]).cumcount()
print(orig_data)
SN date event feat1 steps_
0 111 1/1/18 0 1 0
1 111 1/2/18 0 2 1
2 111 1/3/18 1 3 2
3 222 1/1/18 0 4 0
4 222 1/2/18 1 5 1
5 222 2/2/18 0 6 0
6 333 2/3/18 0 7 0
7 333 2/4/18 1 8 1
8 333 2/4/18 0 9 0
9 444 2/4/18 0 10 0
10 444 2/5/18 1 11 1
note; I've omitted the groupby on 'date', because ordering by 'date' gives problems with '333'. '2/4/18' is given twice, so which one comes first?
answered Dec 21 '18 at 9:26
ZanshinZanshin
7601523
thanks! This partially works except it continues the count through different "id". Here's what I need - For every unique id, start the count and continue on 1 condition, that is, if 'event' equals 1 then stop the count and restart at 0 the next row. So every change of 'id' the count starts over...
– EdM
Dec 21 '18 at 17:40
I understand, but then your output example isn't right. Please provide a new output example
– Zanshin
Dec 21 '18 at 19:53
OK - fair enough, will do so shortly, looking forward to your assistance....thanks again Zanshin!
– EdM
Dec 21 '18 at 21:14
sorry for the delayed update. I have updated the data and code snippet. I appreciate your assistance in advance.
– EdM
Jan 2 at 14:33
@EdM, is this what you are looking for?
– Zanshin
Jan 4 at 16:30
|
show 1 more 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%2f53824941%2fenumerating-grouped-pandas-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
2
IIUC, you might try this;
EDITED
orig_data['steps_'] = orig_data.groupby(['SN',(1 == orig_data['event'].shift(1)).cumsum()]).cumcount()
print(orig_data)
SN date event feat1 steps_
0 111 1/1/18 0 1 0
1 111 1/2/18 0 2 1
2 111 1/3/18 1 3 2
3 222 1/1/18 0 4 0
4 222 1/2/18 1 5 1
5 222 2/2/18 0 6 0
6 333 2/3/18 0 7 0
7 333 2/4/18 1 8 1
8 333 2/4/18 0 9 0
9 444 2/4/18 0 10 0
10 444 2/5/18 1 11 1
note; I've omitted the groupby on 'date', because ordering by 'date' gives problems with '333'. '2/4/18' is given twice, so which one comes first?
answered Dec 21 '18 at 9:26
ZanshinZanshin
7601523
thanks! This partially works except it continues the count through different "id". Here's what I need - For every unique id, start the count and continue on 1 condition, that is, if 'event' equals 1 then stop the count and restart at 0 the next row. So every change of 'id' the count starts over...
– EdM
Dec 21 '18 at 17:40
I understand, but then your output example isn't right. Please provide a new output example
– Zanshin
Dec 21 '18 at 19:53
OK - fair enough, will do so shortly, looking forward to your assistance....thanks again Zanshin!
– EdM
Dec 21 '18 at 21:14
sorry for the delayed update. I have updated the data and code snippet. I appreciate your assistance in advance.
– EdM
Jan 2 at 14:33
@EdM, is this what you are looking for?
– Zanshin
Jan 4 at 16:30
|
show 1 more comment
2
IIUC, you might try this;
EDITED
orig_data['steps_'] = orig_data.groupby(['SN',(1 == orig_data['event'].shift(1)).cumsum()]).cumcount()
print(orig_data)
SN date event feat1 steps_
0 111 1/1/18 0 1 0
1 111 1/2/18 0 2 1
2 111 1/3/18 1 3 2
3 222 1/1/18 0 4 0
4 222 1/2/18 1 5 1
5 222 2/2/18 0 6 0
6 333 2/3/18 0 7 0
7 333 2/4/18 1 8 1
8 333 2/4/18 0 9 0
9 444 2/4/18 0 10 0
10 444 2/5/18 1 11 1
note; I've omitted the groupby on 'date', because ordering by 'date' gives problems with '333'. '2/4/18' is given twice, so which one comes first?
answered Dec 21 '18 at 9:26
ZanshinZanshin
7601523
thanks! This partially works except it continues the count through different "id". Here's what I need - For every unique id, start the count and continue on 1 condition, that is, if 'event' equals 1 then stop the count and restart at 0 the next row. So every change of 'id' the count starts over...
– EdM
Dec 21 '18 at 17:40
I understand, but then your output example isn't right. Please provide a new output example
– Zanshin
Dec 21 '18 at 19:53
OK - fair enough, will do so shortly, looking forward to your assistance....thanks again Zanshin!
– EdM
Dec 21 '18 at 21:14
sorry for the delayed update. I have updated the data and code snippet. I appreciate your assistance in advance.
– EdM
Jan 2 at 14:33
@EdM, is this what you are looking for?
– Zanshin
Jan 4 at 16:30
|
show 1 more comment
2
2
2
IIUC, you might try this;
EDITED
orig_data['steps_'] = orig_data.groupby(['SN',(1 == orig_data['event'].shift(1)).cumsum()]).cumcount()
print(orig_data)
SN date event feat1 steps_
0 111 1/1/18 0 1 0
1 111 1/2/18 0 2 1
2 111 1/3/18 1 3 2
3 222 1/1/18 0 4 0
4 222 1/2/18 1 5 1
5 222 2/2/18 0 6 0
6 333 2/3/18 0 7 0
7 333 2/4/18 1 8 1
8 333 2/4/18 0 9 0
9 444 2/4/18 0 10 0
10 444 2/5/18 1 11 1
note; I've omitted the groupby on 'date', because ordering by 'date' gives problems with '333'. '2/4/18' is given twice, so which one comes first?
answered Dec 21 '18 at 9:26
ZanshinZanshin
7601523
IIUC, you might try this;
EDITED
orig_data['steps_'] = orig_data.groupby(['SN',(1 == orig_data['event'].shift(1)).cumsum()]).cumcount()
print(orig_data)
SN date event feat1 steps_
0 111 1/1/18 0 1 0
1 111 1/2/18 0 2 1
2 111 1/3/18 1 3 2
3 222 1/1/18 0 4 0
4 222 1/2/18 1 5 1
5 222 2/2/18 0 6 0
6 333 2/3/18 0 7 0
7 333 2/4/18 1 8 1
8 333 2/4/18 0 9 0
9 444 2/4/18 0 10 0
10 444 2/5/18 1 11 1
note; I've omitted the groupby on 'date', because ordering by 'date' gives problems with '333'. '2/4/18' is given twice, so which one comes first?
answered Dec 21 '18 at 9:26
ZanshinZanshin
7601523
edited Jan 3 at 8:22
answered Dec 21 '18 at 9:26
ZanshinZanshin
7601523
answered Dec 21 '18 at 9:26
ZanshinZanshin
7601523
answered Dec 21 '18 at 9:26
ZanshinZanshin
7601523
7601523
thanks! This partially works except it continues the count through different "id". Here's what I need - For every unique id, start the count and continue on 1 condition, that is, if 'event' equals 1 then stop the count and restart at 0 the next row. So every change of 'id' the count starts over...
– EdM
Dec 21 '18 at 17:40
I understand, but then your output example isn't right. Please provide a new output example
– Zanshin
Dec 21 '18 at 19:53
OK - fair enough, will do so shortly, looking forward to your assistance....thanks again Zanshin!
– EdM
Dec 21 '18 at 21:14
sorry for the delayed update. I have updated the data and code snippet. I appreciate your assistance in advance.
– EdM
Jan 2 at 14:33
@EdM, is this what you are looking for?
– Zanshin
Jan 4 at 16:30
|
show 1 more comment
thanks! This partially works except it continues the count through different "id". Here's what I need - For every unique id, start the count and continue on 1 condition, that is, if 'event' equals 1 then stop the count and restart at 0 the next row. So every change of 'id' the count starts over...
– EdM
Dec 21 '18 at 17:40
I understand, but then your output example isn't right. Please provide a new output example
– Zanshin
Dec 21 '18 at 19:53
OK - fair enough, will do so shortly, looking forward to your assistance....thanks again Zanshin!
– EdM
Dec 21 '18 at 21:14
sorry for the delayed update. I have updated the data and code snippet. I appreciate your assistance in advance.
– EdM
Jan 2 at 14:33
@EdM, is this what you are looking for?
– Zanshin
Jan 4 at 16:30
thanks! This partially works except it continues the count through different "id". Here's what I need - For every unique id, start the count and continue on 1 condition, that is, if 'event' equals 1 then stop the count and restart at 0 the next row. So every change of 'id' the count starts over...
– EdM
Dec 21 '18 at 17:40
thanks! This partially works except it continues the count through different "id". Here's what I need - For every unique id, start the count and continue on 1 condition, that is, if 'event' equals 1 then stop the count and restart at 0 the next row. So every change of 'id' the count starts over...
– EdM
Dec 21 '18 at 17:40
I understand, but then your output example isn't right. Please provide a new output example
– Zanshin
Dec 21 '18 at 19:53
I understand, but then your output example isn't right. Please provide a new output example
– Zanshin
Dec 21 '18 at 19:53
OK - fair enough, will do so shortly, looking forward to your assistance....thanks again Zanshin!
– EdM
Dec 21 '18 at 21:14
OK - fair enough, will do so shortly, looking forward to your assistance....thanks again Zanshin!
– EdM
Dec 21 '18 at 21:14
sorry for the delayed update. I have updated the data and code snippet. I appreciate your assistance in advance.
– EdM
Jan 2 at 14:33
sorry for the delayed update. I have updated the data and code snippet. I appreciate your assistance in advance.
– EdM
Jan 2 at 14:33
@EdM, is this what you are looking for?
– Zanshin
Jan 4 at 16:30
@EdM, is this what you are looking for?
– Zanshin
Jan 4 at 16:30
|
show 1 more 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%2f53824941%2fenumerating-grouped-pandas-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
2
So do you have a question?
– jpp
Dec 18 '18 at 0:53
Your desired output seems nothing but addition of two new columns
Eventandstepbased on the value ofeventandtimesteprespectively.– Always Sunny
Dec 18 '18 at 0:58
@jpp yes the question as implied in item #2 is to enumerate rows within each group with the condition that if event == 1 within each group, restart the enumeration.
– EdM
Dec 18 '18 at 1:11
@Curious_MInd well said, would be great if you could demo how I can produce the output following conditions in item #1. After grouping by 'id' then enumerate rows within each group but restarting the enumeration when 'event' ==1 within each group. If 'event' !=1 continue the enumeration only within a group.
– EdM
Dec 18 '18 at 1:15
@Curious_MInd - I edited the desired output, I accidentally repeated the 'event' column.
– EdM
Dec 18 '18 at 1:22