groupby

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'city':   ['Sydney','Sydney','Melbourne','Melbourne','Brisbane','Sydney'],
    'gender': ['F','M','F','M','M','F'],
    'salary': [120, 95, 140, 130, 65, 110],
    'age':    [32, 28, 41, 36, 22, 29],
})

# 1) Aggregate one column
df.groupby('city')['salary'].mean()
# city
# Brisbane     65.0
# Melbourne   135.0
# Sydney      108.33

# 2) Multiple aggregations
df.groupby('city').agg(
    salary_mean=('salary', 'mean'),
    salary_sum =('salary', 'sum'),
    headcount  =('salary', 'count'),
    youngest   =('age',    'min'),
)

# 3) Apply per column, different aggregations
df.groupby('city').agg({
    'salary': ['mean', 'std', 'sum'],
    'age':    ['min', 'max'],
})

# 4) Multi-key group
df.groupby(['city', 'gender']).salary.mean().unstack()

# 5) Reset to a flat DataFrame
result = df.groupby('city').salary.sum().reset_index()

# 6) Filter groups — keep cities with ≥ 2 employees
df.groupby('city').filter(lambda g: len(g) >= 2)

# 7) Transform — return same-length result, broadcasted to rows
df['salary_z'] = df.groupby('city').salary.transform(lambda s: (s - s.mean()) / s.std())

# 8) Rolling / cumulative within groups
df['salary_cumsum'] = df.groupby('city').salary.cumsum()

# 9) Custom function — apply (slower than agg)
df.groupby('city').apply(lambda g: (g.salary > g.salary.median()).mean())

# 10) named_agg shorthand (1.0+)
df.groupby('city').agg(
    avg_salary=pd.NamedAgg(column='salary', aggfunc='mean'),
)

# 11) GROUP BY + JOIN equivalent — merge results back
city_avg = df.groupby('city').salary.mean().rename('city_avg')
df = df.merge(city_avg, on='city')
# Or in one shot with transform:
df['city_avg'] = df.groupby('city').salary.transform('mean')

# 12) Time-series groupby — resample, the most-used variant
ts = pd.read_csv('events.csv', parse_dates=['ts']).set_index('ts')
ts.resample('D').size()             # daily counts
ts.resample('1H').mean()            # hourly means
ts.groupby(pd.Grouper(freq='W')).agg({'amount':'sum'})

# 13) Performance tips
#   • Use vectorised .agg with built-in funcs ('mean','sum','min') — fast C path
#   • .apply is the slow road — only for custom logic
#   • For very large data → DuckDB or Polars often outpace pandas groupby

import pandas as pd
df.groupby('country')['age'].mean()
df.groupby(['country', 'gender']).size()
df.groupby('country').agg(avg=('age','mean'), n=('age','count'))