GroupBy on steroids, unleash GroupBy's full potential with the use of .agg()

Posted on pon 15 kwietnia 2019 in Data Science

We all use .groupby and it saves tons of our time and effort. But do you know how one can:

  • use .groupby() and apply more than one aggregation function on the same column?
  • apply different aggregation functions over different columns in the same line of code?
  • use a custom function as an aggregator?

That's when .agg() comes into play! Let's see how you can use it in your code.

Our input dataset - AirBnB prices in Barcelona

We will use the dataset provided by Inside Airbnb, describing all apartments that were listed on AirBnB in Barcelona in 2018. Dataset contains 4 columns:

df = pd.read_csv('listings.csv', dtype={'name': str})


|   |                      name                       | neighbourhood_group | price_per_night | annual_availability |
| 0 | Flat with Sunny Terrace                         | Sant Martí          |             130 |                  23 |
| 1 | Huge flat for 8 people close to Sagrada Familia | Eixample            |              60 |                  61 |
| 2 | Nice and sunny duble room                       | Sant Martí          |              33 |                 300 |
| 3 | NICE ROOM AVAILABLE IN THE HEART OF GRACIA      | Gràcia              |              31 |                  36 |
| 4 | Room for 2, Sagrada Famili                      | Horta-Guinardó      |              42 |                 190 |

In name column there are names of all listings from AirBnB in Barcelona. neighbourhood_group describes where a particular listings is located. price_per_night tells how much does a rental cost and annual_availability specifies how many days per year a particular listing was available.

.agg() can help us quickly synthesize this data especially if we need to write more complex aggregations on DataFrame.

Use of .agg()

.agg() is an alias for aggregate. Usually it is used in combination with .groupby() when we need more complex grouping to be applied. It allows you to specify an exact aggregation function (it can be more than one) that should be used to calculate results on a particular axis.

Enough talk, let's see an example:

df_mean_prices = ( 
              # Pass a dictionary with the structure: {column_name: [operation]}
              .agg({'price_per_night': ['mean']})



 --------------------- ----------------- 
  Ciutat Vella                70.306712  
  Eixample                   142.417469  
  Gràcia                      89.263447  
  Horta-Guinardó              76.709565  
  Les Corts                  120.997409  
  Nou Barris                  35.365957  
  Sant Andreu                 60.475610  
  Sant Martí                  94.208556  
  Sants-Montjuïc              77.347344  
  Sarrià-Sant Gervasi        107.404908  

Notice that thanks to the dictionary that we passed to .agg() we can not only control which columns are interesting to us, but also what is the aggregation function that we want to apply. As a result we got a summary with mean prices per night in Barcelona for each separate district.

Right now, we would get the same result by using df.groupby(by=['neighbourhood_group']).price_per_night.mean(). But .agg() gives use more flexibility.

.agg() - different operations on columns

Let's build on the previous example and see why we should care about .agg()

df_prices = (
              # Use different aggregation function for each column
              .agg({'price_per_night': ['mean', 'min', 'max'],
                    'annual_availability': ['mean', 'count']})



                        price_per_night                               annual_availability
 --------------------- ----------------- --------------------- ------ ------------ ------ 
                        mean              min                   max    mean        count  
  Ciutat Vella          70.306712         8                     2000   183.585497   4082  
  Eixample              142.417469        8                     6000   203.462267   6228  
  Gràcia                89.263447         7                     5000   189.768949   1636  
  Horta-Guinardó        76.709565         9                     1800   184.514783    575  
  Les Corts             120.997409        9                     7777   182.072539    386  
  Nou Barris            35.365957         11                    175    191.540426    235  
  Sant Andreu           60.475610         11                    2000   192.426829    328  
  Sant Martí            94.208556         8                     3000   193.895965   2057  
  Sants-Montjuïc        77.347344         8                     8000   185.933025   2165  
  Sarrià-Sant Gervasi   107.404908        9                     2000   213.748466    652  

This time dictionary that we passed to .agg() contains list of functions that we selected to apply on a particular column groups. Very powerful when you want to quickly synthesize dataset using several aggregation functions.

But this is not the end...

.agg() - use of custom aggregation function

I saved the best part for the end, so I hope that your ready for it. Aggregation function doesn't have to be pre-defined. You can create your own one as long as it returns a single value! In our dataset, we have names column that contains all listing names. Let's say that we would like to check how many times word "sunny" and "friendly" was mentioned in the listing's name.

We start from defining out aggregation functions:

def count_sunny_in_description(titles):
    count = 0
    for title in titles:
        if 'sunny' in str(title):
            count += 1
    return count

def count_friendly_in_description(titles):
    count = 0
    for title in titles:
        if 'friendly' in str(title):
            count += 1
    return count

Pretty straightforward, we return count which is an integer stating how many times a particular word appeared in the name.

Now, having defined our aggregation functions, we can use these in the same manner as in the previous example:

df_sunny_rainy = (
              # Use custom aggregation function for each column
              .agg({'name': [count_sunny_in_description, count_friendly_in_description]})



 ---------------------------- ------------------------------------------------------------- 
                              count_sunny_in_description      count_friendly_in_description
  Ciutat Vella                 30                              7  
  Eixample                     46                              8  
  Gràcia                       19                              1  
  Horta-Guinardó               3                               0  
  Les Corts                    8                               1  
  Nou Barris                   0                               0  
  Sant Andreu                  2                               3  
  Sant Martí                   21                              7  
  Sants-Montjuïc               26                              3  
  Sarrià-Sant Gervasi          10                              0  

And voilà, now you can analyze all types of data using .groupby() and .agg() as long as you write an appropriate aggregation function!


In this post, we examined the use of .agg(), especially in combination with .groupby(). No more merging of several .groupby() results!

From now on, you should always use .agg() when you need more than one or custom aggregation operations over the DataFrame's axis.

Happy coding!