How to count grouped occurrences?
$begingroup$
My dataset has 2 columns:
1) Gender( that has 1 or 2)
2) Rating1(That has 3 ratings (1,2,3))
rating1: 1,2,3,3,2
gender: 2,1,2,2,1
I want output as:
I have tried this:
nutri.groupby(['Rating1','Gender']).nunique()
but I get an output which looks wrong.
python pandas data-cleaning
asked Apr 3 '18 at 7:55
FirstSlackFirstSlack
1016
$endgroup$
add a comment |
$begingroup$
My dataset has 2 columns:
1) Gender( that has 1 or 2)
2) Rating1(That has 3 ratings (1,2,3))
rating1: 1,2,3,3,2
gender: 2,1,2,2,1
I want output as:
I have tried this:
nutri.groupby(['Rating1','Gender']).nunique()
but I get an output which looks wrong.
python pandas data-cleaning
asked Apr 3 '18 at 7:55
FirstSlackFirstSlack
1016
$endgroup$
1
$begingroup$
On what basis is count generated?
$endgroup$
– Ankit Seth
Apr 3 '18 at 10:01
add a comment |
$begingroup$
My dataset has 2 columns:
1) Gender( that has 1 or 2)
2) Rating1(That has 3 ratings (1,2,3))
rating1: 1,2,3,3,2
gender: 2,1,2,2,1
I want output as:
I have tried this:
nutri.groupby(['Rating1','Gender']).nunique()
but I get an output which looks wrong.
python pandas data-cleaning
asked Apr 3 '18 at 7:55
FirstSlackFirstSlack
1016
$endgroup$
My dataset has 2 columns:
1) Gender( that has 1 or 2)
2) Rating1(That has 3 ratings (1,2,3))
rating1: 1,2,3,3,2
gender: 2,1,2,2,1
I want output as:
I have tried this:
nutri.groupby(['Rating1','Gender']).nunique()
but I get an output which looks wrong.
python pandas data-cleaning
python pandas data-cleaning
asked Apr 3 '18 at 7:55
FirstSlackFirstSlack
1016
asked Apr 3 '18 at 7:55
FirstSlackFirstSlack
1016
edited 6 mins ago
FirstSlack
asked Apr 3 '18 at 7:55
FirstSlackFirstSlack
1016
asked Apr 3 '18 at 7:55
FirstSlackFirstSlack
1016
asked Apr 3 '18 at 7:55
FirstSlackFirstSlack
1016
1016
1
$begingroup$
On what basis is count generated?
$endgroup$
– Ankit Seth
Apr 3 '18 at 10:01
add a comment |
1
$begingroup$
On what basis is count generated?
$endgroup$
– Ankit Seth
Apr 3 '18 at 10:01
1
1
$begingroup$
On what basis is count generated?
$endgroup$
– Ankit Seth
Apr 3 '18 at 10:01
$begingroup$
On what basis is count generated?
$endgroup$
– Ankit Seth
Apr 3 '18 at 10:01
add a comment |
1 Answer
1
active
oldest
votes
$begingroup$
You can do that by
>>> import pandas as pd
>>> m = pd.DataFrame({'gender': [1, 2, 2, 1, 1, 2, 1], 'rating': [3, 4, 2, 1, 3, 1, 5]})
>>> m.groupby(['rating','gender']).size().to_frame('count').reset_index()
rating gender count
0 1 1 1
1 1 2 1
2 2 2 1
3 3 1 2
4 4 2 1
5 5 1 1
Hope this is what you want to pull.
Edit:
As mentioned, I did not account for zero values.
You need to do one more step additional to get what you want. Finding the combinations that are missing and then joining it. The one liner solution would be
>>> from itertools import product
>>> m.groupby(['rating', 'gender']).size().to_frame('count').reset_index().merge(
pd.DataFrame(list(set([i for i in product(*[m.gender, m.rating])])), columns=['gender', 'rating']),
on=['rating', 'gender'],
how='right').fillna(value=0)
rating gender count
0 1 1 1.0
1 1 2 1.0
2 2 2 1.0
3 3 1 2.0
4 4 2 1.0
5 5 1 1.0
6 2 1 0.0
7 4 1 0.0
8 3 2 0.0
9 5 2 0.0
Explanation
Get the original grouped counts as mentioned before the first edit but this time you need to join with the combinations that are missing to get the zero counts. Use itertools.product to get all combinations of gender and rating and right join it with original grouped frame on rating and gender to get merged DataFrame which has numpy.na values if no count is present and then use fillna method to fill it with zero. The only loophole is if a rating say 4 is not there in the original data, there is no combination that follows it.
Hope this helps.
answered Apr 3 '18 at 8:40
Kiritee GakKiritee Gak
1,3591521
$endgroup$
$begingroup$
He wants some counts to be zero also.., otherwise awesome solution..
$endgroup$
– Aditya
Apr 3 '18 at 9:06
$begingroup$
I was actually working on a Big Dataset and I don't really need a count for 0 If anything I will use fillna(0.0). I was interested in getting the format. like the example in above question: If the rating is 2.0, how many are men(1) and how many are women (2). In R, there is this 'dplyr ' package which works so smooth, I wanted to learn Python for job interviews.
$endgroup$
– FirstSlack
Apr 3 '18 at 19:03
$begingroup$
Glad if it is of help! Good luck.
$endgroup$
– Kiritee Gak
Apr 4 '18 at 2:49
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "557"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdatascience.stackexchange.com%2fquestions%2f29840%2fhow-to-count-grouped-occurrences%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
$begingroup$
You can do that by
>>> import pandas as pd
>>> m = pd.DataFrame({'gender': [1, 2, 2, 1, 1, 2, 1], 'rating': [3, 4, 2, 1, 3, 1, 5]})
>>> m.groupby(['rating','gender']).size().to_frame('count').reset_index()
rating gender count
0 1 1 1
1 1 2 1
2 2 2 1
3 3 1 2
4 4 2 1
5 5 1 1
Hope this is what you want to pull.
Edit:
As mentioned, I did not account for zero values.
You need to do one more step additional to get what you want. Finding the combinations that are missing and then joining it. The one liner solution would be
>>> from itertools import product
>>> m.groupby(['rating', 'gender']).size().to_frame('count').reset_index().merge(
pd.DataFrame(list(set([i for i in product(*[m.gender, m.rating])])), columns=['gender', 'rating']),
on=['rating', 'gender'],
how='right').fillna(value=0)
rating gender count
0 1 1 1.0
1 1 2 1.0
2 2 2 1.0
3 3 1 2.0
4 4 2 1.0
5 5 1 1.0
6 2 1 0.0
7 4 1 0.0
8 3 2 0.0
9 5 2 0.0
Explanation
Get the original grouped counts as mentioned before the first edit but this time you need to join with the combinations that are missing to get the zero counts. Use itertools.product to get all combinations of gender and rating and right join it with original grouped frame on rating and gender to get merged DataFrame which has numpy.na values if no count is present and then use fillna method to fill it with zero. The only loophole is if a rating say 4 is not there in the original data, there is no combination that follows it.
Hope this helps.
answered Apr 3 '18 at 8:40
Kiritee GakKiritee Gak
1,3591521
$endgroup$
$begingroup$
He wants some counts to be zero also.., otherwise awesome solution..
$endgroup$
– Aditya
Apr 3 '18 at 9:06
$begingroup$
I was actually working on a Big Dataset and I don't really need a count for 0 If anything I will use fillna(0.0). I was interested in getting the format. like the example in above question: If the rating is 2.0, how many are men(1) and how many are women (2). In R, there is this 'dplyr ' package which works so smooth, I wanted to learn Python for job interviews.
$endgroup$
– FirstSlack
Apr 3 '18 at 19:03
$begingroup$
Glad if it is of help! Good luck.
$endgroup$
– Kiritee Gak
Apr 4 '18 at 2:49
add a comment |
$begingroup$
You can do that by
>>> import pandas as pd
>>> m = pd.DataFrame({'gender': [1, 2, 2, 1, 1, 2, 1], 'rating': [3, 4, 2, 1, 3, 1, 5]})
>>> m.groupby(['rating','gender']).size().to_frame('count').reset_index()
rating gender count
0 1 1 1
1 1 2 1
2 2 2 1
3 3 1 2
4 4 2 1
5 5 1 1
Hope this is what you want to pull.
Edit:
As mentioned, I did not account for zero values.
You need to do one more step additional to get what you want. Finding the combinations that are missing and then joining it. The one liner solution would be
>>> from itertools import product
>>> m.groupby(['rating', 'gender']).size().to_frame('count').reset_index().merge(
pd.DataFrame(list(set([i for i in product(*[m.gender, m.rating])])), columns=['gender', 'rating']),
on=['rating', 'gender'],
how='right').fillna(value=0)
rating gender count
0 1 1 1.0
1 1 2 1.0
2 2 2 1.0
3 3 1 2.0
4 4 2 1.0
5 5 1 1.0
6 2 1 0.0
7 4 1 0.0
8 3 2 0.0
9 5 2 0.0
Explanation
Get the original grouped counts as mentioned before the first edit but this time you need to join with the combinations that are missing to get the zero counts. Use itertools.product to get all combinations of gender and rating and right join it with original grouped frame on rating and gender to get merged DataFrame which has numpy.na values if no count is present and then use fillna method to fill it with zero. The only loophole is if a rating say 4 is not there in the original data, there is no combination that follows it.
Hope this helps.
answered Apr 3 '18 at 8:40
Kiritee GakKiritee Gak
1,3591521
$endgroup$
$begingroup$
He wants some counts to be zero also.., otherwise awesome solution..
$endgroup$
– Aditya
Apr 3 '18 at 9:06
$begingroup$
I was actually working on a Big Dataset and I don't really need a count for 0 If anything I will use fillna(0.0). I was interested in getting the format. like the example in above question: If the rating is 2.0, how many are men(1) and how many are women (2). In R, there is this 'dplyr ' package which works so smooth, I wanted to learn Python for job interviews.
$endgroup$
– FirstSlack
Apr 3 '18 at 19:03
$begingroup$
Glad if it is of help! Good luck.
$endgroup$
– Kiritee Gak
Apr 4 '18 at 2:49
add a comment |
$begingroup$
You can do that by
>>> import pandas as pd
>>> m = pd.DataFrame({'gender': [1, 2, 2, 1, 1, 2, 1], 'rating': [3, 4, 2, 1, 3, 1, 5]})
>>> m.groupby(['rating','gender']).size().to_frame('count').reset_index()
rating gender count
0 1 1 1
1 1 2 1
2 2 2 1
3 3 1 2
4 4 2 1
5 5 1 1
Hope this is what you want to pull.
Edit:
As mentioned, I did not account for zero values.
You need to do one more step additional to get what you want. Finding the combinations that are missing and then joining it. The one liner solution would be
>>> from itertools import product
>>> m.groupby(['rating', 'gender']).size().to_frame('count').reset_index().merge(
pd.DataFrame(list(set([i for i in product(*[m.gender, m.rating])])), columns=['gender', 'rating']),
on=['rating', 'gender'],
how='right').fillna(value=0)
rating gender count
0 1 1 1.0
1 1 2 1.0
2 2 2 1.0
3 3 1 2.0
4 4 2 1.0
5 5 1 1.0
6 2 1 0.0
7 4 1 0.0
8 3 2 0.0
9 5 2 0.0
Explanation
Get the original grouped counts as mentioned before the first edit but this time you need to join with the combinations that are missing to get the zero counts. Use itertools.product to get all combinations of gender and rating and right join it with original grouped frame on rating and gender to get merged DataFrame which has numpy.na values if no count is present and then use fillna method to fill it with zero. The only loophole is if a rating say 4 is not there in the original data, there is no combination that follows it.
Hope this helps.
answered Apr 3 '18 at 8:40
Kiritee GakKiritee Gak
1,3591521
$endgroup$
You can do that by
>>> import pandas as pd
>>> m = pd.DataFrame({'gender': [1, 2, 2, 1, 1, 2, 1], 'rating': [3, 4, 2, 1, 3, 1, 5]})
>>> m.groupby(['rating','gender']).size().to_frame('count').reset_index()
rating gender count
0 1 1 1
1 1 2 1
2 2 2 1
3 3 1 2
4 4 2 1
5 5 1 1
Hope this is what you want to pull.
Edit:
As mentioned, I did not account for zero values.
You need to do one more step additional to get what you want. Finding the combinations that are missing and then joining it. The one liner solution would be
>>> from itertools import product
>>> m.groupby(['rating', 'gender']).size().to_frame('count').reset_index().merge(
pd.DataFrame(list(set([i for i in product(*[m.gender, m.rating])])), columns=['gender', 'rating']),
on=['rating', 'gender'],
how='right').fillna(value=0)
rating gender count
0 1 1 1.0
1 1 2 1.0
2 2 2 1.0
3 3 1 2.0
4 4 2 1.0
5 5 1 1.0
6 2 1 0.0
7 4 1 0.0
8 3 2 0.0
9 5 2 0.0
Explanation
Get the original grouped counts as mentioned before the first edit but this time you need to join with the combinations that are missing to get the zero counts. Use itertools.product to get all combinations of gender and rating and right join it with original grouped frame on rating and gender to get merged DataFrame which has numpy.na values if no count is present and then use fillna method to fill it with zero. The only loophole is if a rating say 4 is not there in the original data, there is no combination that follows it.
Hope this helps.
answered Apr 3 '18 at 8:40
Kiritee GakKiritee Gak
1,3591521
edited Apr 3 '18 at 12:26
answered Apr 3 '18 at 8:40
Kiritee GakKiritee Gak
1,3591521
answered Apr 3 '18 at 8:40
Kiritee GakKiritee Gak
1,3591521
answered Apr 3 '18 at 8:40
Kiritee GakKiritee Gak
1,3591521
1,3591521
$begingroup$
He wants some counts to be zero also.., otherwise awesome solution..
$endgroup$
– Aditya
Apr 3 '18 at 9:06
$begingroup$
I was actually working on a Big Dataset and I don't really need a count for 0 If anything I will use fillna(0.0). I was interested in getting the format. like the example in above question: If the rating is 2.0, how many are men(1) and how many are women (2). In R, there is this 'dplyr ' package which works so smooth, I wanted to learn Python for job interviews.
$endgroup$
– FirstSlack
Apr 3 '18 at 19:03
$begingroup$
Glad if it is of help! Good luck.
$endgroup$
– Kiritee Gak
Apr 4 '18 at 2:49
add a comment |
$begingroup$
He wants some counts to be zero also.., otherwise awesome solution..
$endgroup$
– Aditya
Apr 3 '18 at 9:06
$begingroup$
I was actually working on a Big Dataset and I don't really need a count for 0 If anything I will use fillna(0.0). I was interested in getting the format. like the example in above question: If the rating is 2.0, how many are men(1) and how many are women (2). In R, there is this 'dplyr ' package which works so smooth, I wanted to learn Python for job interviews.
$endgroup$
– FirstSlack
Apr 3 '18 at 19:03
$begingroup$
Glad if it is of help! Good luck.
$endgroup$
– Kiritee Gak
Apr 4 '18 at 2:49
$begingroup$
He wants some counts to be zero also.., otherwise awesome solution..
$endgroup$
– Aditya
Apr 3 '18 at 9:06
$begingroup$
He wants some counts to be zero also.., otherwise awesome solution..
$endgroup$
– Aditya
Apr 3 '18 at 9:06
$begingroup$
I was actually working on a Big Dataset and I don't really need a count for 0 If anything I will use fillna(0.0). I was interested in getting the format. like the example in above question: If the rating is 2.0, how many are men(1) and how many are women (2). In R, there is this 'dplyr ' package which works so smooth, I wanted to learn Python for job interviews.
$endgroup$
– FirstSlack
Apr 3 '18 at 19:03
$begingroup$
I was actually working on a Big Dataset and I don't really need a count for 0 If anything I will use fillna(0.0). I was interested in getting the format. like the example in above question: If the rating is 2.0, how many are men(1) and how many are women (2). In R, there is this 'dplyr ' package which works so smooth, I wanted to learn Python for job interviews.
$endgroup$
– FirstSlack
Apr 3 '18 at 19:03
$begingroup$
Glad if it is of help! Good luck.
$endgroup$
– Kiritee Gak
Apr 4 '18 at 2:49
$begingroup$
Glad if it is of help! Good luck.
$endgroup$
– Kiritee Gak
Apr 4 '18 at 2:49
add a comment |
Thanks for contributing an answer to Data Science Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
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%2fdatascience.stackexchange.com%2fquestions%2f29840%2fhow-to-count-grouped-occurrences%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
1
$begingroup$
On what basis is count generated?
$endgroup$
– Ankit Seth
Apr 3 '18 at 10:01