Defining an aggregation using the .groupby method

Last update on April 10 2020 13:15:07 (UTC/GMT +8 hours)

The most common use of the .groupby method is to perform an aggregation. Before moving ahead, quickly let’s see what an aggregation is. An aggregation takes place when a sequence of many inputs get summarized or combined into a single value output. For example, summing up all the values of a column or finding its maximum are aggregations applied to a sequence of data. An aggregation takes a sequence and reduces it to a single value.

Most aggregations have two other components, the aggregating columns and aggregating functions. The aggregating columns are the columns whose values will be aggregated. The aggregating functions define what aggregations take place. Aggregation functions include sum, min, max, mean, count, variance, std, and so on.

This article is an excerpt from the book Pandas 1.x Cookbook, Second Edition by Matt Harrison and Theodore Petrou. This newly updated and revised edition provides you with unique, idiomatic, and fun recipes for both fundamental and advanced data manipulation tasks with pandas.

In this recipe, we examine the flights dataset and perform the simplest aggregation involving only a single grouping column, a single aggregating column, and a single aggregating function. We will find the average arrival delay for each airline. pandas has different syntaxes to create an aggregation, and this recipe will show them.

How to do it ...

Read in the flights dataset:

>>> import pandas as pd 
>>> import numpy as np 
>>> flights = pd.read_csv(‘data/flights.csv’) 
>>> flights.head() 
0     1     1     4  ...      65.0         0           0 
1     1     1     4  ...      -13.0        0           0 
2     1     1     4  ...      35.0         0           0
3     1     1     4  ...      -7.0         0           0
4     1     1     4  ...      39.0         0

Define the grouping columns (AIRLINE), aggregating columns (ARR_DELAY), and aggregating functions (mean). Place the grouping column in the .groupby method and then call the .agg method with a dictionary pairing the aggregating column with its aggregating function. If you pass in a dictionary, it returns back a DataFrame instance:

>>> (flights 
  ...   .groupby(‘AIRLINE’) 
  ...   .agg({‘ARR_DELAY’:’mean’}) 
  ... ) 
  ARR_DELAY 
  AIRLINE            
  AA          5.542661 
  AS         -0.833333 
  B6          8.692593 
  DL          0.339691 
  EV          7.034580 
  ...         ... 
  OO          7.593463 
  UA          7.765755 
  US          1.681105 
  VX          5.348884 
  WN          6.397353

Alternatively, you may place the aggregating column in the index operator and then pass the aggregating function as a string to .agg. This will return a Series:

>>> (flights 
  ...   .groupby('AIRLINE') 
  ...   ['ARR_DELAY'] 
  ...   .agg('mean') 
  ... ) 
  AIRLINE 
  AA    5.542661 
  AS   -0.833333 
  B6    8.692593 
  DL    0.339691 
  EV    7.034580 
  ...    
  OO    7.593463 
  UA    7.765755 
  US    1.681105 
  VX    5.348884 
  WN    6.397353 
  Name: ARR_DELAY, Length: 14, dtype: float64

The string names used in the previous step are a convenience that pandas offers you to refer to a particular aggregation function. You can pass any aggregating function directly to the .agg method, such as the NumPy mean function. The output is the same as the previous step:

>>> (flights 
  ...   .groupby(‘AIRLINE’) 
  ...   [‘ARR_DELAY’] 
  ...   .agg(np.mean) 
  ... ) 
  AIRLINE 
  AA    5.542661 
  AS   -0.833333 
  B6    8.692593 
  DL    0.339691 
  EV    7.034580 
  ...    
  OO    7.593463 
  UA    7.765755 
  US    1.681105 
  VX    5.348884 
  WN    6.397353 
  Name: ARR_DELAY, Length: 14, dtype: float64

It’s possible to skip the agg method altogether in this case and use the mean method directly. This output is also the same as step 3:

>>> (flights 
  ...   .groupby(‘AIRLINE’) 
  ...   [‘ARR_DELAY’] 
  ...     .mean() 
  ... ) 
  AIRLINE 
  AA    5.542661 
  AS   -0.833333 
  B6    8.692593 
  DL    0.339691 
  EV    7.034580 
  ...    
  OO    7.593463 
  UA    7.765755 
  US    1.681105 
  VX    5.348884 
  WN    6.397353 
  Name: ARR_DELAY, Length: 14, dtype: float64

How it work ...

The syntax for the .groupby method is not as straightforward as other methods. Let's intercept the chain of methods in step 2 by storing the result of the .groupby method as its own variable:

>>> grouped = flights.groupby('AIRLINE') 
>>> type(grouped) 
<class  'pandas.core.groupby.generic.DataFrameGroupBy'>

A completely new intermediate object is first produced with its own distinct attributes and methods. No calculations take place at this stage. Pandas merely validates the grouping columns. This groupby object has an .agg method to perform aggregations. One of the ways to use this method is to pass it a dictionary mapping the aggregating column to the aggregating function, as done in step 2. If you pass in a dictionary, the result will be a DataFrame.

The pandas library often has more than one way to perform the same operation. Step 3 shows another way to perform a groupby. Instead of identifying the aggregating column in the dictionary, place it inside the index operator as if you were selecting it as a column from a DataFrame. The function string name is then passed as a scalar to the .agg method. The result, in this case, is a Series.

You may pass any aggregating function to the .agg method. Pandas allows you to use the string names for simplicity, but you may also explicitly call an aggregating function as done in step 4. NumPy provides many functions that aggregate values.

Step 5 shows one last syntax flavor. When you are only applying a single aggregating function as in this example, you can often call it directly as a method on the groupby object itself without .agg. Not all aggregation functions have a method equivalent, but most do.

For practical, easy to implement recipes for quick solutions to common problems in data using pandas, please refer to the book Pandas 1.x Cookbook, Second Edition by Matt Harrison and Theodore Petrou.

About the authors

Matt Harrison has been using Python since 2000. He runs MetaSnake, which provides corporate training for Python and Data Science. He is the author of Machine Learning Pocket Reference, the bestselling Illustrated Guide to Python 3, and Learning the Pandas Library, among other books.

Theodore Petrou is the founder of Dunder Data, a training company dedicated to helping teach the Python data science ecosystem effectively to individuals and corporations. Read his tutorials and attempt his data science challenges at the Dunder Data website.