# Chapter 11: Manipulating, Grouping, Reshaping, and Joining Data
# Fundamentals of Python for Financial Econometrics - Manipulating, Grouping, Reshaping, and Joining Data
# Dataset: Ceteris Lab teaching sample

# Manipulating, Grouping, Reshaping, and Joining Data
#
# **Opening question:** How can information be reorganized without accidentally changing the number or meaning of observations?

# %% Cell 2
import pandas as pd

df = pd.DataFrame({
    "province": ["ON", "ON", "QC", "QC"],
    "year": [2025, 2026, 2025, 2026],
    "income": [60, 63, 55, 58],
})
summary = df.groupby("province", as_index=False).agg(mean_income=("income", "mean"))
print(summary)

# **Interpretation check:** Interpretation. The result has one row per province, so its unit differs from the original province-year table.

# %% Cell 4
meta = pd.DataFrame({"province": ["ON", "QC"], "region": ["Central", "Central"]})
merged = df.merge(meta, on="province", how="left", validate="many_to_one", indicator=True)
print(merged.shape)
print(merged["_merge"].value_counts().to_dict())

# **Interpretation check:** Interpretation. Cardinality validation and the merge indicator confirm that metadata attached without multiplying rows.

# Verified source output
#
# ```text
# province mean_income 0 ON 61.5 1 QC 56.5
# ```
#
# ```text
# (4, 5) {'both': 4, 'left_only': 0, 'right_only': 0}
# ```
#
# ```text
# province  mean_income
# 0       ON         61.5
# 1       QC         56.5
# ```
#
# ```text
# (4, 5)
# {'both': 4, 'left_only': 0, 'right_only': 0}
# ```