Python Pandas - Aggregations
Pandas - Window Aggregations
Section titled “Pandas - Window Aggregations”Pandas provides powerful window operations like rolling(), expanding(), and ewm() (exponentially weighted moving) to perform calculations over a sliding or expanding window of data. Once you create a window object (e.g., df.rolling(window=3)), you can apply various aggregation methods to it.
Applying Aggregations on Window Objects
Section titled “Applying Aggregations on Window Objects”Let’s create a DataFrame with a time series index and demonstrate aggregations on a rolling window.
import pandas as pdimport numpy as np
rng = np.random.default_rng(42)df = pd.DataFrame(rng.standard_normal((10, 4)), index=pd.date_range('2023-01-01', periods=10, freq='D'), columns=['A', 'B', 'C', 'D'])
print('Original DataFrame:')print(df)
# Create a rolling window object# window=3 means calculations consider the current row and the previous 2 rows.# min_periods=1 means it will calculate even if the window has only 1 or 2 points (at the start).rolling_window = df.rolling(window=3, min_periods=1)
print('\nRolling Window Object:')print(rolling_window)Example output:
Original DataFrame: A B C D2023-01-01 0.496714 -0.138264 0.647689 1.5230302023-01-02 -0.234153 -0.234137 1.579213 0.7674352023-01-03 -0.469474 0.542560 -0.463418 -0.4657302023-01-04 0.241962 -1.913280 -1.724918 -0.5622882023-01-05 -1.012831 0.314247 -0.908024 -1.4123042023-01-06 1.465649 -0.225776 0.067528 -1.4247482023-01-07 -0.544383 0.110923 -1.150994 0.3756982023-01-08 -0.600639 -0.291694 -0.601707 1.8522782023-01-09 -0.013497 -1.057711 0.822545 -1.2208442023-01-10 0.208864 -1.150856 -1.347759 0.577046
Rolling Window Object:Rolling [window=3,min_periods=1,center=False,axis=0,method=single]Now, we can apply aggregation functions like sum(), mean(), max(), min(), std(), etc., to this rolling_window object. The .agg() method provides flexibility for applying one or more functions.
Apply Aggregation to the Entire DataFrame
Section titled “Apply Aggregation to the Entire DataFrame”You can apply an aggregation function (like sum or mean) to all columns simultaneously.
# Using the df and rolling_window from the previous example
# Calculate the rolling sumrolling_sum = rolling_window.sum()print('\nRolling Sum (window=3):')print(rolling_sum)
# Equivalent using .agg() with a string aliasrolling_sum_agg = rolling_window.agg('sum')# print('\nRolling Sum using .agg("sum"):')# print(rolling_sum_agg)
# Calculate the rolling meanrolling_mean = rolling_window.mean()print('\nRolling Mean (window=3):')print(rolling_mean)
# Equivalent using .agg() with a NumPy functionrolling_mean_agg = rolling_window.agg(np.mean)# print('\nRolling Mean using .agg(np.mean):')# print(rolling_mean_agg)Example output:
Rolling Sum (window=3): A B C D2023-01-01 0.496714 -0.138264 0.647689 1.5230302023-01-02 0.262561 -0.372401 2.226902 2.2904652023-01-03 -0.206913 0.170159 1.763483 1.8247352023-01-04 -0.461665 -1.604857 -0.610123 -0.2505832023-01-05 -1.240343 -1.056473 -3.096360 -2.4403222023-01-06 0.694780 -1.824809 -2.565414 -3.3993402023-01-07 -0.091565 0.199394 -1.991490 -2.4613542023-01-08 0.320627 -0.406547 -1.685173 -0.1967722023-01-09 -1.158519 -1.238482 -0.929156 1.0071322023-01-10 -0.405272 -2.500261 -1.126921 1.208480
Rolling Mean (window=3): A B C D2023-01-01 0.496714 -0.138264 0.647689 1.5230302023-01-02 0.131280 -0.186201 1.113451 1.1452322023-01-03 -0.068971 0.056720 0.587828 0.6082452023-01-04 -0.153888 -0.534952 -0.203374 -0.0835282023-01-05 -0.413448 -0.352158 -1.032120 -0.8134412023-01-06 0.231593 -0.608270 -0.855138 -1.1331132023-01-07 -0.030522 0.066465 -0.663830 -0.8204512023-01-08 0.106876 -0.135516 -0.561724 -0.0655912023-01-09 -0.386173 -0.412827 -0.309719 0.3357112023-01-10 -0.135091 -0.833420 -0.375640 0.402827Apply Aggregation on a Single Column
Section titled “Apply Aggregation on a Single Column”You can select a specific column before applying the aggregation.
# Using the df and rolling_window from the first example
# Calculate rolling sum for column 'A' onlyrolling_sum_A = rolling_window['A'].sum()print('\nRolling Sum for Column A:')print(rolling_sum_A)
# Alternative: Select column first, then apply rollingrolling_sum_A_alt = df['A'].rolling(window=3, min_periods=1).sum()# print('\nRolling Sum for Column A (Alternative):')# print(rolling_sum_A_alt)Example output:
Rolling Sum for Column A:2023-01-01 0.4967142023-01-02 0.2625612023-01-03 -0.2069132023-01-04 -0.4616652023-01-05 -1.2403432023-01-06 0.6947802023-01-07 -0.0915652023-01-08 0.3206272023-01-09 -1.1585192023-01-10 -0.405272Freq: D, Name: A, dtype: float64Apply Aggregation on Multiple Columns
Section titled “Apply Aggregation on Multiple Columns”Select multiple columns using list indexing.
# Using the df and rolling_window from the first example
# Calculate rolling sum for columns 'A' and 'B'rolling_sum_AB = rolling_window[['A', 'B']].sum()print("\nRolling Sum for Columns 'A' and 'B':")print(rolling_sum_AB)Example output:
Rolling Sum for Columns 'A' and 'B': A B2023-01-01 0.496714 -0.1382642023-01-02 0.262561 -0.3724012023-01-03 -0.206913 0.1701592023-01-04 -0.461665 -1.6048572023-01-05 -1.240343 -1.0564732023-01-06 0.694780 -1.8248092023-01-07 -0.091565 0.1993942023-01-08 0.320627 -0.4065472023-01-09 -1.158519 -1.2384822023-01-10 -0.405272 -2.500261Apply Multiple Functions on a Single Column
Section titled “Apply Multiple Functions on a Single Column”Use the .agg() method with a list of function names (strings) or callable functions.
# Using the df and rolling_window from the first example
# Calculate rolling sum and mean for column 'A'rolling_agg_A = rolling_window['A'].agg([np.sum, np.mean])print("\nRolling Sum and Mean for Column 'A' (using NumPy functions):")print(rolling_agg_A)
# Using string aliasesrolling_agg_A_str = rolling_window['A'].agg(['sum', 'mean'])print("\nRolling Sum and Mean for Column 'A' (using strings):")print(rolling_agg_A_str)Example output:
Rolling Sum and Mean for Column 'A' (using NumPy functions): sum mean2023-01-01 0.496714 0.4967142023-01-02 0.262561 0.1312802023-01-03 -0.206913 -0.0689712023-01-04 -0.461665 -0.1538882023-01-05 -1.240343 -0.4134482023-01-06 0.694780 0.2315932023-01-07 -0.091565 -0.0305222023-01-08 0.320627 0.1068762023-01-09 -1.158519 -0.3861732023-01-10 -0.405272 -0.135091
Rolling Sum and Mean for Column 'A' (using strings): sum mean2023-01-01 0.496714 0.4967142023-01-02 0.262561 0.1312802023-01-03 -0.206913 -0.0689712023-01-04 -0.461665 -0.1538882023-01-05 -1.240343 -0.4134482023-01-06 0.694780 0.2315932023-01-07 -0.091565 -0.0305222023-01-08 0.320627 0.1068762023-01-09 -1.158519 -0.3861732023-01-10 -0.405272 -0.135091Apply Multiple Functions on Multiple Columns
Section titled “Apply Multiple Functions on Multiple Columns”Combine column selection with .agg() and a list of functions. The result will have a MultiIndex for columns.
# Using the df and rolling_window from the first example
# Calculate rolling sum and mean for columns 'A' and 'B'rolling_agg_AB = rolling_window[['A', 'B']].agg(['sum', 'mean'])print("\nRolling Sum and Mean for Columns 'A' and 'B':")print(rolling_agg_AB)Example output:
Rolling Sum and Mean for Columns 'A' and 'B': A B sum mean sum mean2023-01-01 0.496714 0.496714 -0.138264 -0.1382642023-01-02 0.262561 0.131280 -0.372401 -0.1862012023-01-03 -0.206913 -0.068971 0.170159 0.0567202023-01-04 -0.461665 -0.153888 -1.604857 -0.5349522023-01-05 -1.240343 -0.413448 -1.056473 -0.3521582023-01-06 0.694780 0.231593 -1.824809 -0.6082702023-01-07 -0.091565 -0.030522 0.199394 0.0664652023-01-08 0.320627 0.106876 -0.406547 -0.1355162023-01-09 -1.158519 -0.386173 -1.238482 -0.4128272023-01-10 -0.405272 -0.135091 -2.500261 -0.833420Apply Different Functions to Different Columns
Section titled “Apply Different Functions to Different Columns”Use .agg() with a dictionary where keys are column names and values are the function(s) to apply to that column.
import pandas as pdimport numpy as np
rng = np.random.default_rng(43)df_small = pd.DataFrame(rng.standard_normal((5, 4)), index=pd.date_range('2023-01-01', periods=5, freq='D'), columns=['A', 'B', 'C', 'D'])
print('Small DataFrame:')print(df_small)
rolling_small = df_small.rolling(window=3, min_periods=1)
# Calculate rolling sum for 'A' and rolling mean for 'B'rolling_diff_agg = rolling_small.agg({ 'A': 'sum', # Apply sum to column A 'B': np.mean # Apply mean to column B})
print("\nRolling Sum for 'A' and Rolling Mean for 'B':")print(rolling_diff_agg)
# Apply multiple functions to specific columnsrolling_multi_diff_agg = rolling_small.agg({ 'A': ['sum', 'std'], 'C': 'mean'})print("\nRolling Sum/Std for 'A' and Rolling Mean for 'C':")print(rolling_multi_diff_agg)Example output:
Small DataFrame: A B C D2023-01-01 -0.535952 -0.875490 -0.269001 -0.0871762023-01-02 0.125819 0.747178 -1.255743 -0.9779892023-01-03 -1.034384 -0.977989 -0.375991 0.1670762023-01-04 0.417561 -0.166471 0.674515 -1.1798242023-01-05 -0.563337 -0.508814 -0.494112 -0.211489
Rolling Sum for 'A' and Rolling Mean for 'B': A B2023-01-01 -0.535952 -0.8754902023-01-02 -0.410133 -0.0641562023-01-03 -1.444517 -0.3687672023-01-04 -0.491004 -0.1324272023-01-05 -1.180160 -0.551091
Rolling Sum/Std for 'A' and Rolling Mean for 'C': A C sum std mean2023-01-01 -0.535952 NaN -0.2690012023-01-02 -0.410133 0.467941 -0.7623722023-01-03 -1.444517 0.608989 -0.6335782023-01-04 -0.491004 0.738906 -0.3190732023-01-05 -1.180160 0.756068 0.068137