125

given a dataframe that logs uses of some books like this:

Name   Type   ID
Book1  ebook  1
Book2  paper  2
Book3  paper  3
Book1  ebook  1
Book2  paper  2

I need to get the count of all the books, keeping the other columns and get this:

Name   Type   ID    Count
Book1  ebook  1     2
Book2  paper  2     2
Book3  paper  3     1

How can this be done?

Thanks!

Meghdeep Ray
  • 5,262
  • 4
  • 34
  • 58
Adrian Ribao
  • 1,409
  • 3
  • 11
  • 9

5 Answers5

130

You want the following:

In [20]:
df.groupby(['Name','Type','ID']).count().reset_index()

Out[20]:
    Name   Type  ID  Count
0  Book1  ebook   1      2
1  Book2  paper   2      2
2  Book3  paper   3      1

In your case the 'Name', 'Type' and 'ID' cols match in values so we can groupby on these, call count and then reset_index.

An alternative approach would be to add the 'Count' column using transform and then call drop_duplicates:

In [25]:
df['Count'] = df.groupby(['Name'])['ID'].transform('count')
df.drop_duplicates()

Out[25]:
    Name   Type  ID  Count
0  Book1  ebook   1      2
1  Book2  paper   2      2
2  Book3  paper   3      1
EdChum
  • 376,765
  • 198
  • 813
  • 562
  • 3
    This seems to work, but If we had many more columns (as I have in other dataframes), wouldn't this hurt performance? Also, it is not very intuitive. – Adrian Ribao Jul 22 '15 at 18:00
  • 2
    The problem here is that grouping will reduce the amount of information so it won't necessarily yield your desired df in one go, I've updated my answer to show how it could be done in 2 steps which is better to understand – EdChum Jul 22 '15 at 18:15
121

I think as_index=False should do the trick.

df.groupby(['Name','Type','ID'], as_index=False).count()
jtlz2
  • 7,700
  • 9
  • 64
  • 114
jpobst
  • 3,491
  • 2
  • 25
  • 24
32

If you have many columns in a df it makes sense to use df.groupby(['foo']).agg(...), see here. The .agg() function allows you to choose what to do with the columns you don't want to apply operations on. If you just want to keep them, use .agg({'col1': 'first', 'col2': 'first', ...}. Instead of 'first', you can also apply 'sum', 'mean' and others.

NeStack
  • 1,739
  • 1
  • 20
  • 40
  • I use this because it gives custom names to new calculated columns. – Steve Scott Aug 23 '22 at 16:56
  • @SteveScott I actually didn't know about the option to give custom names to new columns. Can you provide an example? I will be certainly using it, I frequently come back to this answer to look up the exact syntax – NeStack Aug 24 '22 at 17:33
  • 4
    @NeStack `.agg(col1_sum=('col1', 'sum'), col2_avg=('col2', 'mean'))` – Umer Aug 31 '22 at 13:39
1

SIMPLEST WAY

df.groupby(['col1', 'col1'], as_index=False).count(). Use as_index=False to retain column names. The default is True.

Also you can use df.groupby(['col_1', 'col_2']).count().reset_index()

NeStack
  • 1,739
  • 1
  • 20
  • 40
Somyadeep Shrivastava
  • 391
  • 2
  • 6
0

You can use value_counts() as well:

df.value_counts().reset_index(name= 'Count')

Output:

    Name   Type  ID  Count
0  Book1  ebook   1      2
1  Book2  paper   2      2
2  Book3  paper   3      1
rhug123
  • 7,893
  • 1
  • 9
  • 24