How to Use Pandas GroupBy, Counts and Value Counts

Table of Contents

• Pandas groupby
  • Basic Pandas groupby usage
  • Using a custom function in Pandas groupby
  • Operating on Pandas groups
  • Iteration and selecting groups
  • Pandas get_group method
• Understanding your data’s shape with Pandas count and value_counts
  • Pandas value_counts method
• Conclusion

If you’re a data scientist, you likely spend a lot of time cleaning and manipulating data for use in your applications. One of the core libraries for preparing data is the Pandas library for Python.

In a previous post, we explored the background of Pandas and the basic usage of a Pandas DataFrame, the core data structure in Pandas. Check out that post if you want to get up to speed with the basics of Pandas.

In this post, we’ll explore a few of the core methods on Pandas DataFrames. These methods help you segment and review your DataFrames during your analysis.

We’ll cover

• Using Pandas groupby to segment your DataFrame into groups.
• Exploring your Pandas DataFrame with counts and value_counts.

Let’s get started.

Pandas groupby

Pandas is typically used for exploring and organizing large volumes of tabular data, like a super-powered Excel spreadsheet. Often, you’ll want to organize a pandas DataFrame into subgroups for further analysis.

For example, perhaps you have stock ticker data in a DataFrame, as we explored in the last post. Your Pandas DataFrame might look as follows:

Perhaps we want to analyze this stock information on a symbol-by-symbol basis rather than combining Amazon (“AMZN”) data with Google (“GOOG”) data or that of Apple (“AAPL”).

This is where the Pandas groupby method is useful. You can use groupby to chunk up your data into subsets for further analysis.

Basic Pandas groupby usage

Let’s do some basic usage of groupby to see how it’s helpful.

In your Python interpreter, enter the following commands:

In the steps above, we’re importing the Pandas and NumPy libraries, then setting up a basic DataFrame by downloading CSV data from a URL. We print our DataFrame to the console to see what we have.

Now, let’s group our DataFrame using the stock symbol. The easiest and most common way to use groupby is by passing one or more column names. For our example, we’ll use “symbol” as the column name for grouping:

Interpreting the output from the printed groups can be a little hard to understand. In the output above, it’s showing that we have three groups: AAPL, AMZN, and GOOG. For each group, it includes an index to the rows in the original DataFrame that belong to each group.

The input to groupby is quite flexible. You can choose to group by multiple columns. For example, if we had a year column available, we could group by both stock symbol and year to perform year-over-year analysis on our stock data.

Using a custom function in Pandas groupby

In the previous example, we passed a column name to the groupby method. You can also pass your own function to the groupby method. This function will receive an index number for each row in the DataFrame and should return a value that will be used for grouping. This can provide significant flexibility for grouping rows using complex logic.

As an example, imagine we want to group our rows depending on whether the stock price increased on that particular day. We would use the following:

First, we would define a function called increased, which receives an index. It returns True if the close value for that row in the DataFrame is higher than the open value; otherwise, it returns False.

When we pass that function into the groupby() method, our DataFrame is grouped into two groups based on whether the stock’s closing price was higher than the opening price on the given day.

Operating on Pandas groups

After you’ve created your groups using the groupby function, you can perform some handy data manipulation on the resulting groups.

In our example above, we created groups of our stock tickers by symbol. Let’s now find the mean trading volume for each symbol.

To complete this task, you specify the column on which you want to operate—volume—then use Pandas’ agg method to apply NumPy’s mean function. The result is the mean volume for each of the three symbols. From this, we can see that AAPL’s trading volume is an order of magnitude larger than AMZN and GOOG’s trading volume.

Iteration and selecting groups

Iteration is a core programming pattern, and few languages have nicer syntax for iteration than Python. Python’s built-in list comprehensions and generators make iteration a breeze.

Pandas groupby is no different, as it provides excellent support for iteration. You can loop over the groupby result object using a for loop:

Each iteration on the groupby object will return two values. The first value is the identifier of the group, which is the value for the column(s) on which they were grouped. The second value is the group itself, which is a Pandas DataFrame object.

Pandas get_group method

If you want more flexibility to manipulate a single group, you can use the get_group method to retrieve a single group.

In the example above, we use the Pandas get_group method to retrieve all AAPL rows. To retrieve a particular group, you pass the identifier of the group into the get_group method. This method returns a Pandas DataFrame, which we can manipulate as needed.

Understanding your data’s shape with Pandas count and value_counts

If you’re working with a large DataFrame, you’ll need to use various heuristics for understanding the shape of your data. In this section, we’ll look at Pandas count and value_counts, two methods for evaluating your DataFrame.

The count method will show you the number of values for each column in your DataFrame. Using our DataFrame from above, we get the following output:

The output isn’t particularly helpful for us, as each of our 15 rows has a value for every column. However, this can be very useful where your data set is missing a large number of values. Using the count method can help to identify columns that are incomplete. From there, you can decide whether to exclude the columns from your processing or to provide default values where necessary.

Pandas value_counts method

For our case, value_counts method is more useful. This method will return the number of unique values for a particular column. If you have continuous variables, like our columns, you can provide an optional “bins” argument to separate the values into half-open bins.

Let’s use the Pandas value_counts method to view the shape of our volume column.

In the output above, Pandas has created four separate bins for our volume column and shows us the number of rows that land in each bin.

Both counts() and value_counts() are great utilities for quickly understanding the shape of your data.

Conclusion

In this post, we learned about groupby, count, and value_counts – three of the main methods in Pandas.

Pandas is a powerful tool for manipulating data once you know the core operations and how to use it. New to Pandas or Python? Download Kite to supercharge your workflow. Kite provides line-of-code completions while you’re typing for faster development, as well as examples of how others are using the same methods.

Head over to our YouTube channel for more Python tips, tutorials and project breakdowns like this one: