Counting unique id's based on condition(s) - pandas
I have a dataset that contains a bunch of unique ids and would like to get a value count of how many of these ids contain both "original" and "copy" in the content column. Also, how would I do this across multiple columns?
I know how to do this in excel but fairly new to python, so any help would be super useful!
df:
user_id content_type status
1234 original pending
1234 copy blocked
4321 original blocked
4321 original distributed
5678 original blocked
5678 copy pending
Output:
original + copy = 2
original + pending = 1
original + blocked = 2
etc..
python pandas
asked Nov 12 at 20:35
KirklandShawty
696
add a comment |
I have a dataset that contains a bunch of unique ids and would like to get a value count of how many of these ids contain both "original" and "copy" in the content column. Also, how would I do this across multiple columns?
I know how to do this in excel but fairly new to python, so any help would be super useful!
df:
user_id content_type status
1234 original pending
1234 copy blocked
4321 original blocked
4321 original distributed
5678 original blocked
5678 copy pending
Output:
original + copy = 2
original + pending = 1
original + blocked = 2
etc..
python pandas
asked Nov 12 at 20:35
KirklandShawty
696
should original + pending = 2 and not 1?
– Chris
Nov 12 at 20:59
@chris in referencing the df above, there's only a single row with both original as the content type and pending as status.
– KirklandShawty
Nov 12 at 21:02
add a comment |
I have a dataset that contains a bunch of unique ids and would like to get a value count of how many of these ids contain both "original" and "copy" in the content column. Also, how would I do this across multiple columns?
I know how to do this in excel but fairly new to python, so any help would be super useful!
df:
user_id content_type status
1234 original pending
1234 copy blocked
4321 original blocked
4321 original distributed
5678 original blocked
5678 copy pending
Output:
original + copy = 2
original + pending = 1
original + blocked = 2
etc..
python pandas
asked Nov 12 at 20:35
KirklandShawty
696
I have a dataset that contains a bunch of unique ids and would like to get a value count of how many of these ids contain both "original" and "copy" in the content column. Also, how would I do this across multiple columns?
I know how to do this in excel but fairly new to python, so any help would be super useful!
df:
user_id content_type status
1234 original pending
1234 copy blocked
4321 original blocked
4321 original distributed
5678 original blocked
5678 copy pending
Output:
original + copy = 2
original + pending = 1
original + blocked = 2
etc..
python pandas
python pandas
asked Nov 12 at 20:35
KirklandShawty
696
asked Nov 12 at 20:35
KirklandShawty
696
asked Nov 12 at 20:35
KirklandShawty
696
asked Nov 12 at 20:35
KirklandShawty
696
asked Nov 12 at 20:35
KirklandShawty
696
696
should original + pending = 2 and not 1?
– Chris
Nov 12 at 20:59
@chris in referencing the df above, there's only a single row with both original as the content type and pending as status.
– KirklandShawty
Nov 12 at 21:02
add a comment |
should original + pending = 2 and not 1?
– Chris
Nov 12 at 20:59
@chris in referencing the df above, there's only a single row with both original as the content type and pending as status.
– KirklandShawty
Nov 12 at 21:02
should original + pending = 2 and not 1?
– Chris
Nov 12 at 20:59
should original + pending = 2 and not 1?
– Chris
Nov 12 at 20:59
@chris in referencing the df above, there's only a single row with both original as the content type and pending as status.
– KirklandShawty
Nov 12 at 21:02
@chris in referencing the df above, there's only a single row with both original as the content type and pending as status.
– KirklandShawty
Nov 12 at 21:02
add a comment |
1 Answer
1
active
oldest
votes
Groups having 'copy':
sum(df.groupby('user_id').apply(lambda x: 'copy' in x['content_type'].unique()))
(summation of rows having 'copy'; True=1 and False=0)
Or
df.groupby('user_id').apply(lambda x: x[x['content_type']=='copy']).shape[0]
Count by status:
df[df['content_type'] == 'original'].groupby('status').size()
status
blocked 2
distributed 1
pending 1
Or if you want to count both original and copy,
df.groupby(['content_type','status']).size()
content_type status
copy blocked 1
pending 1
original blocked 2
distributed 1
pending 1
dtype: int64
answered Nov 12 at 21:18
Michael O.
2,7792521
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%2f53269704%2fcounting-unique-ids-based-on-conditions-pandas%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
Groups having 'copy':
sum(df.groupby('user_id').apply(lambda x: 'copy' in x['content_type'].unique()))
(summation of rows having 'copy'; True=1 and False=0)
Or
df.groupby('user_id').apply(lambda x: x[x['content_type']=='copy']).shape[0]
Count by status:
df[df['content_type'] == 'original'].groupby('status').size()
status
blocked 2
distributed 1
pending 1
Or if you want to count both original and copy,
df.groupby(['content_type','status']).size()
content_type status
copy blocked 1
pending 1
original blocked 2
distributed 1
pending 1
dtype: int64
answered Nov 12 at 21:18
Michael O.
2,7792521
add a comment |
Groups having 'copy':
sum(df.groupby('user_id').apply(lambda x: 'copy' in x['content_type'].unique()))
(summation of rows having 'copy'; True=1 and False=0)
Or
df.groupby('user_id').apply(lambda x: x[x['content_type']=='copy']).shape[0]
Count by status:
df[df['content_type'] == 'original'].groupby('status').size()
status
blocked 2
distributed 1
pending 1
Or if you want to count both original and copy,
df.groupby(['content_type','status']).size()
content_type status
copy blocked 1
pending 1
original blocked 2
distributed 1
pending 1
dtype: int64
answered Nov 12 at 21:18
Michael O.
2,7792521
add a comment |
Groups having 'copy':
sum(df.groupby('user_id').apply(lambda x: 'copy' in x['content_type'].unique()))
(summation of rows having 'copy'; True=1 and False=0)
Or
df.groupby('user_id').apply(lambda x: x[x['content_type']=='copy']).shape[0]
Count by status:
df[df['content_type'] == 'original'].groupby('status').size()
status
blocked 2
distributed 1
pending 1
Or if you want to count both original and copy,
df.groupby(['content_type','status']).size()
content_type status
copy blocked 1
pending 1
original blocked 2
distributed 1
pending 1
dtype: int64
answered Nov 12 at 21:18
Michael O.
2,7792521
Groups having 'copy':
sum(df.groupby('user_id').apply(lambda x: 'copy' in x['content_type'].unique()))
(summation of rows having 'copy'; True=1 and False=0)
Or
df.groupby('user_id').apply(lambda x: x[x['content_type']=='copy']).shape[0]
Count by status:
df[df['content_type'] == 'original'].groupby('status').size()
status
blocked 2
distributed 1
pending 1
Or if you want to count both original and copy,
df.groupby(['content_type','status']).size()
content_type status
copy blocked 1
pending 1
original blocked 2
distributed 1
pending 1
dtype: int64
answered Nov 12 at 21:18
Michael O.
2,7792521
edited Nov 12 at 21:36
answered Nov 12 at 21:18
Michael O.
2,7792521
answered Nov 12 at 21:18
Michael O.
2,7792521
answered Nov 12 at 21:18
Michael O.
2,7792521
2,7792521
add a comment |
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%2f53269704%2fcounting-unique-ids-based-on-conditions-pandas%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
should original + pending = 2 and not 1?
– Chris
Nov 12 at 20:59
@chris in referencing the df above, there's only a single row with both original as the content type and pending as status.
– KirklandShawty
Nov 12 at 21:02