Python Pandas - Aggregations

Aggregating data is a key step in data analysis, especially when dealing with large datasets. In Pandas, you can perform aggregations using the DataFrame.agg() method, This method is flexible, enabling various operations that summarize and analyze your data. Aggregation operations in Pandas can be applied to either the index axis (default) or the column axis.

In this tutorial we will discuss about how to use the DataFrame.agg() method to perform various aggregation techniques, including how to apply multiple aggregation functions, customize aggregations for specific columns, and work with both rows and columns.

Understanding the DataFrame.agg() Method

The DataFrame.agg() method (an alias for aggregate) is a powerful tool that allows you to apply one or more aggregation functions to a DataFrame, either across rows or columns, providing a summary of the data.

Syntax

Following is the syntax −

DataFrame.agg(func=None, axis=0, *args, **kwargs)

Where,

• func: This parameter specifies the aggregation function(s) to be applied. It accepts a single function or function name (e.g., np.sum, 'mean'), a list of functions or function names, or a dictionary mapping axis labels to functions.

• axis: Specifies the axis along which to apply the aggregation. 0 or 'index' applies the function(s) to each column (default), while 1 or 'columns' applies the function(s) to each row.

• *args: Positional arguments to pass to the aggregation function(s).

• **kwargs: Keyword arguments to pass to the aggregation function(s).

The result of agg() method depends on the input, it returns a scalar or Series if a single function is used, or a DataFrame if multiple functions are applied.

Applying Aggregations on DataFrame Rows

You can aggregate multiple functions over the rows (index axis) using the agg function. This method applies the specified aggregation functions to each column in the DataFrame.

Example

Let us create a DataFrame and apply aggregation functions sum and min on it. In this example, the sum and min functions are applied to each column, providing a summary of the data.

import pandas as pd
import numpy as np

df = pd.DataFrame([[1, 2, 3, 1],
                   [4, 5, 6, np.nan],
                   [7, 8, 9, 2],
                   [np.nan, 2, np.nan, 3]],
   index = pd.date_range('1/1/2024', periods=4),
   columns = ['A', 'B', 'C', 'D'])

print("Input DataFrame:\n",df)
result = df.agg(['sum', 'min'])
print("\nResults:\n",result)

Its output is as follows −

Input DataFrame:
               A  B    C    D
2024-01-01  1.0  2  3.0  1.0
2024-01-02  4.0  5  6.0  NaN
2024-01-03  7.0  8  9.0  2.0
2024-01-04  NaN  2  NaN  3.0

Results:
         A   B     C    D
sum  12.0  17  18.0  6.0
min   1.0   2   3.0  1.0

Applying Different Functions Per Column

You can also apply different aggregation functions to different columns by passing a dictionary to the agg function. Each key in the dictionary corresponds to a column, and the value is a list of aggregation functions to apply.

import pandas as pd
import numpy as np

df = pd.DataFrame([[1, 2, 3, 1],
                   [4, 5, 6, np.nan],
                   [7, 8, 9, 2],
                   [np.nan, 2, np.nan, 3]],
   index = pd.date_range('1/1/2024', periods=4),
   columns = ['A', 'B', 'C', 'D'])

print("Input DataFrame:\n",df)
result = df.agg({'A': ['sum', 'min'], 'B': ['min', 'max']})
print("\nResults:\n",result)

On executing the above code, it produces following output:

Input DataFrame:
               A  B    C    D
2024-01-01  1.0  2  3.0  1.0
2024-01-02  4.0  5  6.0  NaN
2024-01-03  7.0  8  9.0  2.0
2024-01-04  NaN  2  NaN  3.0

Results:
         A    B
sum  12.0  NaN
min   1.0  2.0
max   NaN  8.0

Apply Aggregation on a Single Column

You can apply aggregation functions to individual columns, such as calculating a rolling sum.

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.randn(10, 4),
   index = pd.date_range('1/1/2000', periods=10),
   columns = ['A', 'B', 'C', 'D'])
print(df)
r = df.rolling(window=3,min_periods=1)
print(r['A'].aggregate(np.sum))

Its output is as follows −

                 A           B           C           D
2000-01-01   1.088512   -0.650942   -2.547450   -0.566858
2000-01-02   1.879182   -1.038796   -3.215581   -0.299575
2000-01-03   1.303660   -2.003821   -3.155154   -2.479355
2000-01-04   1.884801   -0.141119   -0.862400   -0.483331
2000-01-05   1.194699    0.010551    0.297378   -1.216695
2000-01-06   1.925393    1.968551   -0.968183    1.284044
2000-01-07   0.565208    0.032738   -2.125934    0.482797
2000-01-08   0.564129   -0.759118   -2.454374   -0.325454
2000-01-09   2.048458   -1.820537   -0.535232   -1.212381
2000-01-10   2.065750    0.383357    1.541496   -3.201469
2000-01-01   1.088512
2000-01-02   1.879182
2000-01-03   1.303660
2000-01-04   1.884801
2000-01-05   1.194699
2000-01-06   1.925393
2000-01-07   0.565208
2000-01-08   0.564129
2000-01-09   2.048458
2000-01-10   2.065750
Freq: D, Name: A, dtype: float64

Customizing the Result

Pandas allows you to aggregate different functions across the columns and rename the resulting DataFrame's index. This can be done by passing tuples to the agg() function.

Example

The following example applies the aggregation with custom index labels.

import pandas as pd
import numpy as np

df = pd.DataFrame([[1, 2, 3, 1],
                   [4, 5, 6, np.nan],
                   [7, 8, 9, 2],
                   [np.nan, 2, np.nan, 3]],
   index = pd.date_range('1/1/2024', periods=4),
   columns = ['A', 'B', 'C', 'D'])

print("Input DataFrame:\n",df)
result = df.agg(x=('A', 'max'), y=('B', 'min'), z=('C', 'mean'))
print("\nResults:\n",result)

Its output is as follows −

Input DataFrame:
               A  B    C    D
2024-01-01  1.0  2  3.0  1.0
2024-01-02  4.0  5  6.0  NaN
2024-01-03  7.0  8  9.0  2.0
2024-01-04  NaN  2  NaN  3.0

Results:
      A    B    C
x  7.0  NaN  NaN
y  NaN  2.0  NaN
z  NaN  NaN  6.0

Applying Aggregating Over Columns

In addition to aggregating over rows, you can aggregate over the columns by setting the axis parameter to columns (axis=1). This is useful when you want to apply an aggregation function across the rows.

Example

This example applies the mean() function across the columns for each row.

import pandas as pd
import numpy as np

df = pd.DataFrame([[1, 2, 3, 1],
                   [4, 5, 6, np.nan],
                   [7, 8, 9, 2],
                   [np.nan, 2, np.nan, 3]],
   index = pd.date_range('1/1/2024', periods=4),
   columns = ['A', 'B', 'C', 'D'])

print("Input DataFrame:\n",df)
result = df.agg("mean", axis="columns")
print("\nResults:\n",result)

Its output is as follows −

Input DataFrame:
               A  B    C    D
2024-01-01  1.0  2  3.0  1.0
2024-01-02  4.0  5  6.0  NaN
2024-01-03  7.0  8  9.0  2.0
2024-01-04  NaN  2  NaN  3.0

Results:
 2024-01-01    1.75
2024-01-02    5.00
2024-01-03    6.50
2024-01-04    2.50
Freq: D, dtype: float64