COUNT( ) in pandas

Posted by Kevin

Selecting the number of rows from a SQL table or column, translated to Python's pandas.

Selecting the count of a single column's non-null rows

In SQL:

SELECT 
  COUNT(column_1) 
FROM 
  table;

In pandas:

table['column_1'].count()

Selecting the count of all rows in a table

In SQL:

SELECT 
  COUNT(*) 
FROM 
  table;

In pandas:

table.shape[0]

Note the use of .shape[0] instead of .count() to return a scalar.

Selecting the count of non-null rows for multiple columns

In SQL:

SELECT 
  COUNT(column_1), 
  COUNT(column_2) 
FROM 
  table;

In pandas:

table
    .agg({'column_1': ['count'], 'column_2': ['count']})

Selecting the count of non-null rows for multiple columns with an alias

In SQL:

SELECT 
  column_1, 
  COUNT(column_2) AS sum_alias_1, 
  COUNT(column_3) AS sum_alias_2 
FROM 
  table 
GROUP BY 
  column_1;

In pandas:

table 
    .groupby('column_1') 
    .agg(sum_alias_1=('column_2', 'count'), sum_alias_2=('column_3', 'count'))

References