Skip to content
W3S101

Python Pandas - GroupBy

Pandas DataFrame - GroupBy Operations

Section titled “Pandas DataFrame - GroupBy Operations”

Grouping data is a fundamental operation in data analysis. The Pandas groupby operation allows you to efficiently perform calculations on subsets of your data. It typically involves three steps:

  • Splitting the data into groups based on some criteria (e.g., values in a column).
  • Applying a function to each group independently (e.g., calculating the mean, sum, or a custom transformation).
  • Combining the results into a new data structure (like a DataFrame or Series).

Common applications within the ‘Apply’ step include:

  • Aggregation: Computing summary statistics for each group (e.g., count, mean, standard deviation).
  • Transformation: Performing group-specific computations that return an object of the same shape as the input group (e.g., standardizing data within each group).
  • Filtration: Discarding entire groups based on some group-level computation or condition (e.g., keeping only groups with a sufficient number of data points).

Let’s create a sample DataFrame to illustrate these operations:

# Import the pandas library
import pandas as pd
import numpy as np


# Sample data (corrected 'kings' to 'Kings' for consistency)
ipl_data = {
    'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
             'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
    'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
    'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
    'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]
}
df = pd.DataFrame(ipl_data)


print(df)

Output:

      Team  Rank  Year  Points
0   Riders     1  2014     876
1   Riders     2  2015     789
2   Devils     2  2014     863
3   Devils     3  2015     673
4    Kings     3  2014     741
5    Kings     4  2015     812
6    Kings     1  2016     756
7    Kings     1  2017     788
8   Riders     2  2016     694
9   Royals     4  2014     701
10  Royals     1  2015     804
11  Riders     2  2017     690

Splitting Data into Groups

Section titled “Splitting Data into Groups”

You can split a Pandas object (like a DataFrame) into groups using the groupby() method. Common ways to specify the grouping criteria include:

  • df.groupby('column_name'): Group by the values in a single column.
  • df.groupby(['col1', 'col2']): Group by the combination of values in multiple columns.
  • df.groupby(level='index_level'): Group by levels if the DataFrame has a MultiIndex.

Applying groupby() itself doesn’t immediately compute anything but returns a DataFrameGroupBy object, which holds information about the groups.

Example: Grouping by a Single Column

Section titled “Example: Grouping by a Single Column”
# Import the pandas library
import pandas as pd
import numpy as np


ipl_data = {
    'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
             'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
    'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
    'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
    'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]
}
df = pd.DataFrame(ipl_data)


# Create a GroupBy object
grouped_by_team = df.groupby('Team')


print(grouped_by_team)

Output (representation may vary slightly based on Pandas version):

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x...>

Viewing Groups

Section titled “Viewing Groups”

To see which rows belong to which group, you can inspect the .groups attribute of the GroupBy object. It returns a dictionary where keys are the group names and values are the index labels belonging to that group.

# Import the pandas library
import pandas as pd
import numpy as np


ipl_data = {
    'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
             'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
    'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
    'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
    'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]
}
df = pd.DataFrame(ipl_data)


grouped_by_team = df.groupby('Team')


print(grouped_by_team.groups)

Output (index type might differ based on Pandas version, e.g., Index instead of Int64Index):

{'Devils': [2, 3], 'Kings': [4, 5, 6, 7], 'Riders': [0, 1, 8, 11], 'Royals': [9, 10]}

Example: Grouping by Multiple Columns

Section titled “Example: Grouping by Multiple Columns”

Grouping by multiple columns creates hierarchical groups.

# Import the pandas library
import pandas as pd
import numpy as np


ipl_data = {
    'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
             'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
    'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
    'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
    'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]
}
df = pd.DataFrame(ipl_data)


# Group by both Team and Year
grouped_by_team_year = df.groupby(['Team', 'Year'])


print(grouped_by_team_year.groups)

Output:

{('Devils', 2014): [2], ('Devils', 2015): [3], ('Kings', 2014): [4], ('Kings', 2015): [5], ('Kings', 2016): [6], ('Kings', 2017): [7], ('Riders', 2014): [0], ('Riders', 2015): [1], ('Riders', 2016): [8], ('Riders', 2017): [11], ('Royals', 2014): [9], ('Royals', 2015): [10]}

Iterating Through Groups

Section titled “Iterating Through Groups”

You can iterate over a GroupBy object. Each iteration yields a tuple containing the group name (or tuple of names for multi-column grouping) and the corresponding subset of the original DataFrame.

# Import the pandas library
import pandas as pd
import numpy as np


ipl_data = {
    'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
             'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
    'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
    'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
    'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]
}
df = pd.DataFrame(ipl_data)


grouped_by_year = df.groupby('Year')


print("Iterating through groups based on Year:")
for name, group_df in grouped_by_year:
    print(f"\nYear: {name}")
    print(group_df)

Output:

Iterating through groups based on Year:


Year: 2014
     Team  Rank  Year  Points
0  Riders     1  2014     876
2  Devils     2  2014     863
4   Kings     3  2014     741
9  Royals     4  2014     701


Year: 2015
      Team  Rank  Year  Points
1   Riders     2  2015     789
3   Devils     3  2015     673
5    Kings     4  2015     812
10  Royals     1  2015     804


Year: 2016
     Team  Rank  Year  Points
6   Kings     1  2016     756
8  Riders     2  2016     694


Year: 2017
      Team  Rank  Year  Points
7    Kings     1  2017     788
11  Riders     2  2017     690

Iteration is useful for performing complex operations that don’t fit neatly into the standard aggregation or transformation patterns.

Selecting a Specific Group

Section titled “Selecting a Specific Group”

Use the get_group() method to retrieve the DataFrame corresponding to a single specific group name.

# Import the pandas library
import pandas as pd
import numpy as np


ipl_data = {
    'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
             'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
    'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
    'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
    'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]
}
df = pd.DataFrame(ipl_data)


grouped_by_year = df.groupby('Year')


# Get the data for the year 2014
group_2014 = grouped_by_year.get_group(2014)
print(group_2014)

Output:

     Team  Rank  Year  Points
0  Riders     1  2014     876
2  Devils     2  2014     863
4   Kings     3  2014     741
9  Royals     4  2014     701

Aggregation

Section titled “Aggregation”

Aggregation computes a summary statistic (or statistics) for each group, resulting in a smaller data structure where the index often corresponds to the group names.

The most common way to aggregate is using the aggregate() method (or its alias agg()). You can apply aggregation functions to specific columns or the entire group.

# Import the pandas library
import pandas as pd
import numpy as np


ipl_data = {
    'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
             'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
    'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
    'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
    'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]
}
df = pd.DataFrame(ipl_data)


grouped_by_year = df.groupby('Year')


# Calculate the mean points for each year
# Using numpy function
mean_points_np = grouped_by_year['Points'].agg(np.mean)
print("Mean Points per Year (using np.mean):")
print(mean_points_np)


# Using pandas string alias (more common)
mean_points_str = grouped_by_year['Points'].agg('mean')
print("\nMean Points per Year (using 'mean'):")
print(mean_points_str)

Output:

Mean Points per Year (using np.mean):
Year
2014    795.25
2015    769.50
2016    725.00
2017    739.00
Name: Points, dtype: float64


Mean Points per Year (using 'mean'):
Year
2014    795.25
2015    769.50
2016    725.00
2017    739.00
Name: Points, dtype: float64

The size() method is a convenient way to get the number of rows in each group:

import pandas as pd
import numpy as np


ipl_data = {
    'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
             'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
    'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
    'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
    'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]
}
df = pd.DataFrame(ipl_data)


grouped_by_team = df.groupby('Team')


# Get the size of each group
print(grouped_by_team.size())

Output:

Team
Devils    2
Kings     4
Riders    4
Royals    2
dtype: int64

Note: size() counts NaN values, while count() does not. If you apply agg without specifying columns, it applies the aggregation to all valid (usually numeric) columns.

# Example: Using agg('size') on the GroupBy object (applies to all columns implicitly)
print("\nUsing grouped_by_team.agg('size'):") # Note: This might not be the most intuitive use
# print(grouped_by_team.agg('size')) # This often results in a DataFrame if multiple columns are aggregated
# More commonly, you might calculate size per group like this:
print(grouped_by_team.agg(size=('Team', 'size')))

Output:

Using grouped_by_team.agg('size'):
        size
Team
Devils     2
Kings      4
Riders     4
Royals     2

Applying Multiple Aggregation Functions

Section titled “Applying Multiple Aggregation Functions”

You can pass a list of functions (or string aliases) to agg() to compute multiple summary statistics at once. The result is a DataFrame with hierarchical columns.

# Import the pandas library
import pandas as pd
import numpy as np


ipl_data = {
    'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
             'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
    'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
    'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
    'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]
}
df = pd.DataFrame(ipl_data)


grouped_by_team = df.groupby('Team')


# Calculate sum, mean, and standard deviation of Points for each team
agg_stats = grouped_by_team['Points'].agg(['sum', 'mean', 'std'])
print(agg_stats)

Output:

          sum    mean         std
Team
Devils   1536  768.00   134.350288
Kings    3107  776.75    31.899321
Riders   3049  762.25    88.567771
Royals   1505  752.50    72.831998

You can also use a dictionary to apply different aggregations to different columns: df.groupby('Team').agg({'Points': ['mean', 'sum'], 'Rank': 'mean'})

Transformation

Section titled “Transformation”

Transformation applies a function to each group and returns an object (like a Series or DataFrame) that has the same index as the original object. This is useful for group-specific calculations that need to align with the original data, such as centering data within groups or filling missing values group-wise.

The transform() method is used for this. The function passed to transform must return a scalar value or a Series/DataFrame of the same shape as the input group chunk.

# Import the pandas library
import pandas as pd
import numpy as np


ipl_data = {
    'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
             'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
    'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
    'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
    'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]
}
df = pd.DataFrame(ipl_data)


grouped_by_team = df.groupby('Team')


# Example: Calculate the z-score for 'Points' within each team group
# z-score = (value - group_mean) / group_std
# Define the z-score function
zscore = lambda x: (x - x.mean()) / x.std()


# Apply the transformation to the 'Points' column
# Note: transform applies the function to each numeric column if not specified
# We will select specific columns after grouping for clarity
transformed_scores = grouped_by_team[['Points', 'Rank']].transform(zscore)
print(transformed_scores)


# Note: If a group has only one member, its std deviation is NaN, resulting in NaN z-scores.
# If a group has zero std deviation (all values are the same), z-score will be NaN or Inf.

Output (values might differ due to random data generation if it were used; this uses fixed data):

      Points      Rank
0   1.284327 -1.336306
1   0.302029  0.267261
2   0.707107 -0.707107
3  -0.707107  0.707107
4  -1.120330  0.481125
5   1.105721  1.443376
6  -0.650947 -1.082532
7   0.350004 -1.082532
8  -0.770596  0.267261
9  -0.707107  0.707107
10  0.707107 -0.707107
11 -0.815759  0.267261

Filtration

Section titled “Filtration”

Filtration allows you to discard entire groups based on a condition evaluated at the group level. The filter() method takes a function that accepts a group (as a DataFrame/Series) and returns True if the group should be kept, and False otherwise.

Common use case: Removing groups that don’t meet a minimum size threshold.

import pandas as pd
import numpy as np


ipl_data = {
    'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
             'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
    'Rank': [1, 2, 2, 3, 3, 4, 1, 1, 2, 4, 1, 2],
    'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
    'Points': [876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]
}
df = pd.DataFrame(ipl_data)


# Filter out teams that appeared less than 3 times in the dataset
filtered_df = df.groupby('Team').filter(lambda group: len(group) >= 3)


print(filtered_df)

Output (Only ‘Kings’ and ‘Riders’ groups remain as they have 4 entries each):

      Team  Rank  Year  Points
0   Riders     1  2014     876
1   Riders     2  2015     789
4    Kings     3  2014     741
5    Kings     4  2015     812
6    Kings     1  2016     756
7    Kings     1  2017     788
8   Riders     2  2016     694
11  Riders     2  2017     690

In this example, the lambda function lambda group: len(group) >= 3 checks the size of each team’s group. Only groups satisfying this condition (size >= 3) are included in the final filtered_df.