group by two columns count in pandas

Question

I have a Pandas DataFrame like this :

df = pd.DataFrame({
    'Date': ['2017-1-1', '2017-1-1', '2017-1-2', '2017-1-2', '2017-1-3'],
    'Groups': ['one', 'one', 'one', 'two', 'two']})

    Date      Groups  
0  2017-1-1    one       
1  2017-1-1    one       
2  2017-1-2    one       
3  2017-1-2    two       
4  2017-1-3    two

How can I generate a new DataFrame like this?

     Date    Groups_counts     
0  2017-1-1    1        
1  2017-1-2    2        
2  2017-1-3    1

Thanks a lot!

Do you mean grouping by how many unique values there are in the second column? I'm guessing you are, but you should explain that in your question. Also, what did you try? What output did you get? See if this helps: https://stackoverflow.com/questions/15411158/pandas-countdistinct-equivalent — Ofer Sadan, May 24 '18 at 08:38

score 2 · Answer 1 · answered May 24 '18 at 08:38

2

To get count of unique records use:

df.groupby('Date')['Groups'].nunique()

answered May 24 '18 at 08:38

zipa

27,316
6
40
58

Thanks. What i should add if i want to sort values based on Groups_counts's values? – ah bon May 24 '18 at 09:37
@ahbon just `sort_values()` using `df.groupby('Date')['Groups'].nunique().sort_values(ascending=False)`. – zipa May 24 '18 at 09:46
Wonderful answer :) – Shalini Baranwal Apr 02 '19 at 08:38

group by two columns count in pandas

1 Answers1