Lesson 2.7 - GroupBy & Aggregation

# --- GroupBy + agg: summary stats per (site, month) ---

monthly_by_site = (
    energy_df
    # add a "month" column: dt.to_period("M") collapses each date to its
    # year-month (e.g. 2021-03), so every day in the same month shares a value
    .assign(month=energy_df["date"].dt.to_period("M"))
    # split the data into one group per unique (site, month) pair...
    .groupby(["site", "month"])["energy_kwh"]
    # ...and compute three summary statistics of energy_kwh within each group
    .agg(["sum", "mean", "max"])
    # groupby puts "site" and "month" into the index; reset_index() moves them
    # back to ordinary columns so the result is a plain DataFrame
    .reset_index()
)
print(monthly_by_site.head())

# --- Multiple named aggregations at once, one row per sector ---

# The "name=(column, function)" form aggregates different columns with
# different functions in a single call and names each output:
#   total_energy_kwh = sum of energy_kwh
#   avg_ambient_c    = mean of ambient_c
#   n_readings       = count of non-null energy_kwh values (rows that had data)
sector_summary = energy_df.groupby("sector").agg(
    total_energy_kwh=("energy_kwh", "sum"),
    avg_ambient_c=("ambient_c", "mean"),
    n_readings=("energy_kwh", "count"),
)
print("\nSector summary:")
print(sector_summary)

# --- Pivot table: reshape into a site x year grid of average ambient temp ---

# First derive a plain "year" column from the date.
energy_df["year"] = energy_df["date"].dt.year

# pivot_table spreads one variable across rows and another across columns:
#   index="site"    -> one row per site
#   columns="year"  -> one column per year
#   values="ambient_c", aggfunc="mean" -> each cell is the mean ambient_c
#                                         for that site in that year
# .round(1) just trims the displayed precision.
pivot = energy_df.pivot_table(values="ambient_c", index="site", columns="year", aggfunc="mean").round(1)
print("\nAvg ambient temp by site and year:")
pivot

# --- Build a synthetic sector electricity-demand dataset for Mini-Project 2 ---

np.random.seed(7)   # fixed seed -> everyone generates the same numbers

sectors = ["Chemical", "Semiconductor", "Data Center", "Manufacturing", "Logistics"]
years = [2021, 2022, 2023]
months = range(1, 13)

# Starting-point average demand (kW) per sector, before growth / seasonality / noise
sector_base = {"Chemical": 2600, "Semiconductor": 4200, "Data Center": 5200,
               "Manufacturing": 3100, "Logistics": 1400}

demand_rows = []
for sector in sectors:
    base = sector_base[sector]
    for year in years:
        # a steady year-on-year growth factor applied to the base demand
        growth = 1 + 0.04 * (year - 2021)
        for month in months:
            # bump demand in the hot months (Apr-Jun), trim it around year-end
            seasonal = 1.15 if month in (4, 5, 6) else (0.92 if month in (12, 1) else 1.0)
            # base * growth * seasonal, then +/- ~3% random noise
            demand_kw = base * growth * seasonal * (1 + np.random.normal(0, 0.03))
            demand_rows.append((sector, year, month, round(demand_kw, 1)))

# one row per (sector, year, month) = 5 x 3 x 12 = 180 rows
sector_demand = pd.DataFrame(demand_rows, columns=["sector", "year", "month", "avg_demand_kw"])
print(sector_demand.shape)
sector_demand.head()

# Try the practice exercises here