Adrian Ribao Adrian Ribao - 6 months ago 139
Python Question

How to GroupBy a Dataframe in Pandas and keep Columns

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!

Answer

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