pandas GroupBy: Your Guide to Grouping Data in Python (original) (raw)

Watch Now This tutorial has a related video course created by the Real Python team. Watch it together with the written tutorial to deepen your understanding: pandas GroupBy: Grouping Real World Data in Python

The pandas .groupby() method allows you to efficiently analyze and transform datasets when working with data in Python. With df.groupby(), you can split a DataFrame into groups based on column values, apply functions to each group, and combine the results into a new DataFrame. This technique is essential for tasks like aggregation, filtering, and transformation on grouped data.

By the end of this tutorial, you’ll understand that:

This tutorial assumes that you have some experience with pandas itself, including how to read CSV files into memory as pandas objects with read_csv(). If you need a refresher, then check out Reading CSVs With pandas and pandas: How to Read and Write Files.

You can download the source code for all the examples in this tutorial by clicking on the link below:

Prerequisites

Before you proceed, make sure that you have the latest version of pandas available within a new virtual environment:

In this tutorial, you’ll focus on three datasets:

  1. The U.S. Congress dataset contains public information on historical members of Congress and illustrates several fundamental capabilities of .groupby().
  2. The air quality dataset contains periodic gas sensor readings. This will allow you to work with floats and time series data.
  3. The news aggregator dataset holds metadata on several hundred thousand news articles. You’ll be working with strings and doing text munging with .groupby().

You can download the source code for all the examples in this tutorial by clicking on the link below:

Once you’ve downloaded the .zip file, unzip the file to a folder called groupby-data/ in your current directory. Before you read on, ensure that your directory tree looks like this:

./ │ └── groupby-data/ │ ├── legislators-historical.csv ├── airqual.csv └── news.csv

With pandas installed, your virtual environment activated, and the datasets downloaded, you’re ready to jump in!

Example 1: U.S. Congress Dataset

You’ll jump right into things by dissecting a dataset of historical members of Congress. You can read the CSV file into a pandas DataFrame with read_csv():

Python pandas_legislators.py

The dataset contains members’ first and last names, birthday, gender, type ("rep" for House of Representatives or "sen" for Senate), U.S. state, and political party. You can use df.tail() to view the last few rows of the dataset:

The DataFrame uses categorical dtypes for space efficiency:

You can see that most columns of the dataset have the type category, which reduces the memory load on your machine.

The Hello, World! of pandas GroupBy

Now that you’re familiar with the dataset, you’ll start with a Hello, World! for the pandas GroupBy operation. What is the count of Congressional members, on a state-by-state basis, over the entire history of the dataset? In SQL, you could find this answer with a SELECT statement:

Here’s the near-equivalent in pandas:

You call .groupby() and pass the name of the column that you want to group on, which is "state". Then, you use ["last_name"] to specify the columns on which you want to perform the actual aggregation.

You can pass a lot more than just a single column name to .groupby() as the first argument. You can also specify any of the following:

Here’s an example of grouping jointly on two columns, which finds the count of Congressional members broken out by state and then by gender:

The analogous SQL query would look like this:

As you’ll see next, .groupby() and the comparable SQL statements are close cousins, but they’re often not functionally identical.

pandas GroupBy vs SQL

This is a good time to introduce one prominent difference between the pandas GroupBy operation and the SQL query above. The result set of the SQL query contains three columns:

  1. state
  2. gender
  3. count

In the pandas version, the grouped-on columns are pushed into the MultiIndex of the resulting Series by default:

To more closely emulate the SQL result and push the grouped-on columns back into columns in the result, you can use as_index=False:

This produces a DataFrame with three columns and a RangeIndex, rather than a Series with a MultiIndex. In short, using as_index=False will make your result more closely mimic the default SQL output for a similar operation.

Also note that the SQL queries above explicitly use ORDER BY, whereas .groupby() does not. That’s because .groupby() does this by default through its parameter sort, which is True unless you tell it otherwise:

Next, you’ll dive into the object that .groupby() actually produces.

How pandas GroupBy Works

Before you get any further into the details, take a step back to look at .groupby() itself:

What is DataFrameGroupBy? Its .__str__() value that the print function shows doesn’t give you much information about what it actually is or how it works. The reason that a DataFrameGroupBy object can be difficult to wrap your head around is that it’s lazy in nature. It doesn’t really do any operations to produce a useful result until you tell it to.

One term that’s frequently used alongside .groupby() is split-apply-combine. This refers to a chain of three steps:

  1. Split a table into groups.
  2. Apply some operations to each of those smaller tables.
  3. Combine the results.

It can be difficult to inspect df.groupby("state") because it does virtually none of these things until you do something with the resulting object. A pandas GroupBy object delays virtually every part of the split-apply-combine process until you invoke a method on it.

So, how can you mentally separate the split, apply, and combine stages if you can’t see any of them happening in isolation? One useful way to inspect a pandas GroupBy object and see the splitting in action is to iterate over it:

If you’re working on a challenging aggregation problem, then iterating over the pandas GroupBy object can be a great way to visualize the split part of split-apply-combine.

There are a few other methods and properties that let you look into the individual groups and their splits. The .groups attribute will give you a dictionary of {group name: group label} pairs. For example, by_state.groups is a dict with states as keys. Here’s the value for the "PA" key:

Each value is a sequence of the index locations for the rows belonging to that particular group. In the output above, 4, 19, and 21 are the first indices in df at which the state equals "PA".

You can also use .get_group() as a way to drill down to the sub-table from a single group:

This is virtually equivalent to using .loc[]. You could get the same output with something like df.loc[df["state"] == "PA"].

It’s also worth mentioning that .groupby() does do some, but not all, of the splitting work by building a Grouping class instance for each key that you pass. However, many of the methods of the BaseGrouper class that holds these groupings are called lazily rather than at .__init__(), and many also use a cached property design.

Next, what about the apply part? You can think of this step of the process as applying the same operation (or callable) to every sub-table that the splitting stage produces.

From the pandas GroupBy object by_state, you can grab the initial U.S. state and DataFrame with next(). When you iterate over a pandas GroupBy object, you’ll get pairs that you can unpack into two variables:

Now, think back to your original, full operation:

The apply stage, when applied to your single, subsetted DataFrame, would look like this:

You can see that the result, 16, matches the value for AK in the combined result.

The last step, combine, takes the results of all of the applied operations on all of the sub-tables and combines them back together in an intuitive way.

Read on to explore more examples of the split-apply-combine process.

Example 2: Air Quality Dataset

The air quality dataset contains hourly readings from a gas sensor device in Italy. Missing values are denoted with -200 in the CSV file. You can use read_csv() to combine two columns into a timestamp while using a subset of the other columns:

This produces a DataFrame with a DatetimeIndex and four float columns:

Here, co is that hour’s average carbon monoxide reading, while temp_c, rel_hum, and abs_hum are the average Celsius temperature, relative humidity, and absolute humidity over that hour, respectively. The observations run from March 2004 through April 2005:

So far, you’ve grouped on columns by specifying their names as str, such as df.groupby("state"). But .groupby() is a whole lot more flexible than this! You’ll see how next.

Grouping on Derived Arrays

Earlier you saw that the first parameter to .groupby() can accept several different arguments:

You can take advantage of the last option in order to group by the day of the week. Use the index’s .day_name() to produce a pandas Index of strings. Here are the first ten observations:

You can then take this object and use it as the .groupby() key. In pandas, day_names is array-like. It’s a one-dimensional sequence of labels.

Now, pass that object to .groupby() to find the average carbon monoxide (co) reading by day of the week:

The split-apply-combine process behaves largely the same as before, except that the splitting this time is done on an artificially created column. This column doesn’t exist in the DataFrame itself, but rather is derived from it.

What if you wanted to group not just by day of the week, but by hour of the day? That result should have 7 * 24 = 168 observations. To accomplish that, you can pass a list of array-like objects. In this case, you’ll pass pandas Int64Index objects:

Here’s one more similar case that uses .cut() to bin the temperature values into discrete intervals:

In this case, bins is actually a Series:

Whether it’s a Series, NumPy array, or list doesn’t matter. What’s important is that bins still serves as a sequence of labels, comprising cool, warm, and hot. If you really wanted to, then you could also use a Categorical array or even a plain old list:

As you can see, .groupby() is smart and can handle a lot of different input types. Any of these would produce the same result because all of them function as a sequence of labels on which to perform the grouping and splitting.

Resampling

You’ve grouped df by the day of the week with df.groupby(day_names)["co"].mean(). Now consider something different. What if you wanted to group by an observation’s year and quarter? Here’s one way to accomplish that:

This whole operation can, alternatively, be expressed through resampling. One of the uses of resampling is as a time-based groupby. All that you need to do is pass a frequency string, such as "Q" for "quarterly", and pandas will do the rest:

Often, when you use .resample() you can express time-based grouping operations in a much more succinct manner. The result may be a tiny bit different than the more verbose .groupby() equivalent, but you’ll often find that .resample() gives you exactly what you’re looking for.

Example 3: News Aggregator Dataset

Now you’ll work with the third and final dataset, which holds metadata on several hundred thousand news articles and groups them into topic clusters:

To read the data into memory with the proper dtype, you need a helper function to parse the timestamp column. This is because it’s expressed as the number of milliseconds since the Unix epoch, rather than fractional seconds. If you want to learn more about working with time in Python, check out Using Python datetime to Work With Dates and Times.

Similar to what you did before, you can use the categorical dtype to efficiently encode columns that have a relatively small number of unique values relative to the column length.

Each row of the dataset contains the title, URL, publishing outlet’s name, and domain, as well as the publication timestamp. cluster is a random ID for the topic cluster to which an article belongs. category is the news category and contains the following options:

Here’s the first row:

Now that you’ve gotten a glimpse of the data, you can begin to ask more complex questions about it.

Using Lambda Functions in .groupby()

This dataset invites a lot more potentially involved questions. Here’s a random but meaningful one: which outlets talk most about the Federal Reserve? Assume for simplicity that this entails searching for case-sensitive mentions of "Fed". Bear in mind that this may generate some false positives with terms like "Federal government".

To count mentions by outlet, you can call .groupby() on the outlet, and then quite literally .apply() a function on each group using a Python lambda function:

Let’s break this down since there are several method calls made in succession. Like before, you can pull out the first group and its corresponding pandas object by taking the first tuple from the pandas GroupBy iterator:

In this case, ser is a pandas Series rather than a DataFrame. That’s because you followed up the .groupby() call with ["title"]. This effectively selects that single column from each sub-table.

Next comes .str.contains("Fed"). This returns a Boolean Series that’s True when an article title registers a match on the search. Sure enough, the first row starts with "Fed official says weak data caused by weather,..." and lights up as True:

The next step is to .sum() this Series. Since bool is technically just a specialized type of int, you can sum a Series of True and False just as you would sum a sequence of 1 and 0:

The result is the number of mentions of "Fed" by the Los Angeles Times in the dataset. The same routine gets applied for Reuters, NASDAQ, Businessweek, and the rest of the lot.

Improving the Performance of .groupby()

Now backtrack again to .groupby().apply() to see why this pattern can be suboptimal. To get some background information, check out How to Speed Up Your pandas Projects. What may happen with .apply() is that it’ll effectively perform a Python loop over each group. While the .groupby().apply() pattern can provide some flexibility, it can also inhibit pandas from otherwise using its Cython-based optimizations.

All that is to say that whenever you find yourself thinking about using .apply(), ask yourself if there’s a way to express the operation in a vectorized way. In that case, you can take advantage of the fact that .groupby() accepts not just one or more column names, but also many array-like structures:

Also note that .groupby() is a valid instance method for a Series, not just a DataFrame, so you can essentially invert the splitting logic. With that in mind, you can first construct a Series of Booleans that indicate whether or not the title contains "Fed":

Now, .groupby() is also a method of Series, so you can group one Series on another:

The two Series don’t need to be columns of the same DataFrame object. They just need to be of the same shape:

Finally, you can cast the result back to an unsigned integer with np.uintc if you’re determined to get the most compact result possible. Here’s a head-to-head comparison of the two versions that’ll produce the same result:

Python pandas_news_performance.py

You use the timeit module to estimate the running time of both versions. If you want to learn more about testing the performance of your code, then Python Timer Functions: Three Ways to Monitor Your Code is worth a read.

Now, run the script to see how both versions perform:

When run three times, the test_apply() function takes 2.54 seconds, while test_vectorization() takes just 0.33 seconds. This is an impressive difference in CPU time for a few hundred thousand rows. Consider how dramatic the difference becomes when your dataset grows to a few million rows!

pandas GroupBy: Putting It All Together

If you call dir() on a pandas GroupBy object, then you’ll see enough methods there to make your head spin! It can be hard to keep track of all of the functionality of a pandas GroupBy object. One way to clear the fog is to compartmentalize the different methods into what they do and how they behave.

Broadly, methods of a pandas GroupBy object fall into a handful of categories:

  1. Aggregation methods (also called reduction methods) combine many data points into an aggregated statistic about those data points. An example is to take the sum, mean, or median of ten numbers, where the result is just a single number.
  2. Filter methods come back to you with a subset of the original DataFrame. This most commonly means using .filter() to drop entire groups based on some comparative statistic about that group and its sub-table. It also makes sense to include under this definition a number of methods that exclude particular rows from each group.
  3. Transformation methods return a DataFrame with the same shape and indices as the original, but with different values. With both aggregation and filter methods, the resulting DataFrame will commonly be smaller in size than the input DataFrame. This is not true of a transformation, which transforms individual values themselves but retains the shape of the original DataFrame.
  4. Meta methods are less concerned with the original object on which you called .groupby(), and more focused on giving you high-level information such as the number of groups and the indices of those groups.
  5. Plotting methods mimic the API of plotting for a pandas Series or DataFrame, but typically break the output into multiple subplots.

The official documentation has its own explanation of these categories. They are, to some degree, open to interpretation, and this tutorial might diverge in slight ways in classifying which method falls where.

There are a few methods of pandas GroupBy objects that don’t fall nicely into the categories above. These methods usually produce an intermediate object that’s not a DataFrame or Series. For instance, df.groupby().rolling() produces a RollingGroupby object, which you can then call aggregation, filter, or transformation methods on.

If you want to dive in deeper, then the API documentations for DataFrame.groupby(), DataFrame.resample(), and pandas.Grouper are resources for exploring methods and objects.

There’s also yet another separate table in the pandas docs with its own classification scheme. Pick whichever works for you and seems most intuitive!

Conclusion

In this tutorial, you’ve covered a ton of ground on .groupby(), including its design, its API, and how to chain methods together to get data into a structure that suits your purpose.

You’ve learned:

There’s much more to .groupby() than you can cover in one tutorial. But hopefully this tutorial was a good starting point for further exploration!

You can download the source code for all the examples in this tutorial by clicking on the link below:

Frequently Asked Questions

Now that you have some experience with pandas .groupby() in Python, you can use the questions and answers below to check your understanding and recap what you’ve learned.

These FAQs are related to the most important concepts you’ve covered in this tutorial. Click the Show/Hide toggle beside each question to reveal the answer.

You use .groupby() to split a DataFrame into groups based on some criteria, perform operations on each group, and then combine the results back together.

To group by multiple columns in pandas, you pass a list of column names to .groupby(). Then you can perform operations on combinations of those columns.

You handle missing values by using methods like .fillna() or .dropna() before or after applying .groupby() to ensure accurate results.

To optimize performance, use vectorized operations instead of .apply() and consider using categorical data types to reduce memory usage.

Aggregation reduces data to summary statistics, transformation applies functions while retaining the original DataFrame’s shape, and filtering selects subsets of data based on specific criteria.

Watch Now This tutorial has a related video course created by the Real Python team. Watch it together with the written tutorial to deepen your understanding: pandas GroupBy: Grouping Real World Data in Python