How to do group by 2 column and performe count in pandas

Question

How to performe count like this query in pandas?

Select col1, col2, count(col3) as total from table
GROUP by col1,col2

See [Comparison with SQL](https://pandas.pydata.org/docs/getting_started/comparison/comparison_with_sql.html#comparison-with-sql) from the docs. — Henry Yik, Sep 23 '20 at 06:44
`df = df.groupby(['col1', 'col2'])['col3'].count().reset_index().rename(columns={'col3': 'total'})` — Dmitriy K., Sep 23 '20 at 08:48

score 1 · Answer 1 · answered Sep 23 '20 at 06:48

1

You want the pandas groupby method:

df2 = df.groupby(['col1', 'col2'], as_index = False).count()

This will give you a count of all your other columns. If you want to specify a different aggregation function for each column, you can use .agg:

df2 = df.groupby(['col1', 'col2'], as_index = False).agg({'col3': 'count', 'col4': 'sum'})

answered Sep 23 '20 at 06:48

Jacob

still got different result from my query result – Rizki Kurniawan Sep 23 '20 at 07:01

1 Answers1