Capstone - Complete Analytics System

# Python: Load and analyze transfer window data
import pandas as pd
import numpy as np
from datetime import datetime, timedelta

# Simulated transfer data
np.random.seed(42)
n_transfers = 500

# Generate transfer dates within summer window
start_date = datetime(2023, 6, 1)
date_range = pd.date_range(start_date, periods=93, freq="D")

transfers = pd.DataFrame({
    "transfer_id": range(1, n_transfers + 1),
    "player_name": [f"Player {i}" for i in range(1, n_transfers + 1)],
    "from_club": np.random.choice([f"Club {c}" for c in "ABCDEFGHIJKLMNOPQRST"],
                                   n_transfers),
    "to_club": np.random.choice([f"Club {c}" for c in "ABCDEFGHIJKLMNOPQRST"],
                                 n_transfers),
    "transfer_date": np.random.choice(date_range, n_transfers),
    "fee_millions": np.random.lognormal(mean=2.5, sigma=1.2, size=n_transfers),
    "player_age": np.random.randint(18, 36, n_transfers),
    "contract_years_remaining": np.random.randint(1, 6, n_transfers)
})

# Add timing features
window_start = datetime(2023, 6, 14)
window_end = datetime(2023, 9, 1)

transfers["days_into_window"] = (
    pd.to_datetime(transfers["transfer_date"]) - window_start
).dt.days
transfers["days_until_deadline"] = (
    window_end - pd.to_datetime(transfers["transfer_date"])
).dt.days

# Categorize window phase
def categorize_phase(row):
    if row["days_into_window"] <= 14:
        return "Early"
    elif row["days_until_deadline"] <= 7:
        return "Deadline"
    return "Middle"

transfers["window_phase"] = transfers.apply(categorize_phase, axis=1)

# Analyze spending by phase
phase_analysis = transfers.groupby("window_phase").agg({
    "transfer_id": "count",
    "fee_millions": ["sum", "mean", "median"]
}).reset_index()

phase_analysis.columns = ["window_phase", "n_transfers", "total_spend",
                          "avg_fee", "median_fee"]
phase_analysis["pct_transfers"] = (
    phase_analysis["n_transfers"] / phase_analysis["n_transfers"].sum() * 100
)
phase_analysis["pct_spend"] = (
    phase_analysis["total_spend"] / phase_analysis["total_spend"].sum() * 100
)

print(phase_analysis)

# Python: Model price dynamics across window phases
import pandas as pd
import numpy as np
import statsmodels.formula.api as smf

# Prepare model data
transfers["log_fee"] = np.log(transfers["fee_millions"] + 1)
transfers["is_deadline"] = (transfers["window_phase"] == "Deadline").astype(int)
transfers["is_early"] = (transfers["window_phase"] == "Early").astype(int)
transfers["age_squared"] = transfers["player_age"] ** 2

# Build price model
price_model = smf.ols(
    "log_fee ~ player_age + age_squared + contract_years_remaining + "
    "is_deadline + is_early + days_until_deadline",
    data=transfers
).fit()

print(price_model.summary().tables[1])

# Calculate price premium/discount by timing
timing_effects = transfers.groupby("window_phase").agg({
    "log_fee": "mean",
    "fee_millions": "mean",
    "transfer_id": "count"
}).reset_index()

timing_effects.columns = ["window_phase", "avg_log_fee", "avg_fee", "n"]

# Price index relative to middle of window
middle_avg = timing_effects.loc[
    timing_effects["window_phase"] == "Middle", "avg_fee"
].values[0]
timing_effects["price_index"] = timing_effects["avg_fee"] / middle_avg * 100

print("\nTiming Effects:")
print(timing_effects)

# Daily average fees
daily_fees = transfers.groupby("transfer_date").agg({
    "fee_millions": ["mean", "count"]
}).reset_index()
daily_fees.columns = ["date", "daily_avg", "daily_n"]
daily_fees["rolling_avg"] = daily_fees["daily_avg"].rolling(7, center=True).mean()

# Python: Analyze contract situation effects on pricing
import pandas as pd
import numpy as np
import statsmodels.formula.api as smf

# Contract situation categories
def categorize_contract(years):
    if years == 1:
        return "Final Year"
    elif years == 2:
        return "2 Years"
    elif years >= 3:
        return "3+ Years"
    return "Unknown"

transfers["contract_situation"] = transfers["contract_years_remaining"].apply(
    categorize_contract
)

# Analyze by contract situation
contract_analysis = transfers.groupby("contract_situation").agg({
    "transfer_id": "count",
    "fee_millions": ["mean", "median"]
}).reset_index()

contract_analysis.columns = ["contract_situation", "n_transfers",
                              "avg_fee", "median_fee"]

# Calculate contract discount
base_fee = contract_analysis.loc[
    contract_analysis["contract_situation"] == "3+ Years", "avg_fee"
].values[0]

contract_analysis["contract_discount"] = (
    (1 - contract_analysis["avg_fee"] / base_fee) * 100
)

print(contract_analysis)

# Model: Fee vs Contract Years
contract_model = smf.ols(
    "np.log(fee_millions + 1) ~ contract_years_remaining + player_age",
    data=transfers
).fit()

# Each additional contract year effect
contract_coef = contract_model.params["contract_years_remaining"]
contract_effect = (np.exp(contract_coef) - 1) * 100
print(f"\nEach additional contract year adds ~{contract_effect:.1f}% to transfer fee")

# Python: Model optimal timing for buying/selling
import pandas as pd
import numpy as np

# Create timing strategy framework
days = np.arange(1, 81)

timing_strategy = pd.DataFrame({
    "day_of_window": days,
    "seller_leverage": 1 - (days / 80) ** 0.5,
    "buyer_leverage": (days / 80) ** 0.5,
    "market_activity": 0.5 + 0.4 * np.cos(np.pi * days / 80) + 0.3 * (days > 70),
    "price_premium": 1.15 - 0.2 * (days / 80) + 0.1 * (days > 70)
})

# Find optimal timing
optimal_buying = timing_strategy[
    (timing_strategy["buyer_leverage"] > 0.6) &
    (timing_strategy["price_premium"] < 1.05)
]

optimal_selling = timing_strategy[
    timing_strategy["seller_leverage"] > 0.7
]

print(f"Optimal buying window: Days {optimal_buying['day_of_window'].min()}-"
      f"{optimal_buying['day_of_window'].max()}")
print(f"Optimal selling window: Days {optimal_selling['day_of_window'].min()}-"
      f"{optimal_selling['day_of_window'].max()}")

# Strategy recommendations
club_strategies = pd.DataFrame({
    "situation": [
        "Need specific player",
        "Flexible targets",
        "Selling unwanted player",
        "Selling star player",
        "Emergency cover needed"
    ],
    "recommended_timing": [
        "Early (premium for certainty)",
        "Mid-window (best value)",
        "Deadline (maximize competition)",
        "Early (maximize bidding war)",
        "Any (urgency dominates)"
    ],
    "expected_premium": ["+15-20%", "0%", "-10-15%", "+10-15%", "+20-30%"]
})

print("\nClub Situation Strategies:")
print(club_strategies.to_string(index=False))

# Python: Comprehensive transfer price prediction model
import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import mean_squared_error, mean_absolute_error

# Feature engineering
transfers["age_bucket"] = pd.cut(
    transfers["player_age"],
    bins=[0, 22, 26, 30, 35, 100],
    labels=["U23", "Prime_Early", "Prime_Peak", "Declining", "Veteran"]
)
transfers["is_prime_age"] = (
    (transfers["player_age"] >= 24) & (transfers["player_age"] <= 29)
).astype(int)
transfers["contract_urgency"] = 1 / transfers["contract_years_remaining"]
transfers["is_final_year"] = (transfers["contract_years_remaining"] == 1).astype(int)
transfers["window_progress"] = transfers["days_into_window"] / 80
transfers["is_deadline_week"] = (transfers["days_until_deadline"] <= 7).astype(int)
transfers["log_fee"] = np.log(transfers["fee_millions"] + 1)

# Prepare features
feature_cols = ["player_age", "contract_years_remaining", "window_progress",
                "is_deadline_week", "is_prime_age"]

X = transfers[feature_cols]
y = transfers["log_fee"]

# Train/test split
X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42
)

# Random Forest model
rf_model = RandomForestRegressor(n_estimators=500, random_state=42, n_jobs=-1)
rf_model.fit(X_train, y_train)

# Feature importance
importance_df = pd.DataFrame({
    "feature": feature_cols,
    "importance": rf_model.feature_importances_
}).sort_values("importance", ascending=False)

print("Feature Importance:")
print(importance_df.to_string(index=False))

# Predictions
y_pred = rf_model.predict(X_test)
predicted_fee = np.exp(y_pred) - 1
actual_fee = np.exp(y_test) - 1

# Model performance
rmse = np.sqrt(mean_squared_error(actual_fee, predicted_fee))
mae = mean_absolute_error(actual_fee, predicted_fee)
print(f"\nModel Performance:\n  RMSE: €{rmse:.2f}M\n  MAE: €{mae:.2f}M")

# Python: Identify market inefficiencies
import pandas as pd
import numpy as np

# Create test results dataframe
test_results = pd.DataFrame({
    "actual_fee": actual_fee.values,
    "predicted_fee": predicted_fee,
    "player_age": X_test["player_age"].values,
    "contract_years": X_test["contract_years_remaining"].values
})

# Calculate residuals
test_results["residual"] = test_results["actual_fee"] - test_results["predicted_fee"]
test_results["pct_over_predicted"] = (
    (test_results["actual_fee"] / test_results["predicted_fee"] - 1) * 100
)

# Categorize value
def categorize_value(pct):
    if pct > 25:
        return "Overpaid"
    elif pct < -25:
        return "Bargain"
    return "Fair Value"

test_results["value_category"] = test_results["pct_over_predicted"].apply(
    categorize_value
)

# Summary
value_summary = test_results.groupby("value_category").agg({
    "actual_fee": ["count", "mean"],
    "predicted_fee": "mean",
    "pct_over_predicted": "mean"
}).reset_index()

value_summary.columns = ["value_category", "n_transfers", "avg_actual_fee",
                          "avg_predicted_fee", "avg_overpayment_pct"]
print(value_summary)

# Top bargains and overpays
bargains = test_results[test_results["value_category"] == "Bargain"].nsmallest(
    5, "pct_over_predicted"
)
overpays = test_results[test_results["value_category"] == "Overpaid"].nlargest(
    5, "pct_over_predicted"
)

print("\nTop Bargains:")
print(bargains[["actual_fee", "predicted_fee", "pct_over_predicted"]].round(2))
print("\nTop Overpays:")
print(overpays[["actual_fee", "predicted_fee", "pct_over_predicted"]].round(2))

# Python: Analyze deadline day dynamics
import pandas as pd
import numpy as np

# Filter deadline transfers
deadline_transfers = transfers[transfers["days_until_deadline"] <= 3].copy()
deadline_transfers["hours_until_deadline"] = (
    deadline_transfers["days_until_deadline"] * 24
)

# Hour buckets
def bucket_hours(hours):
    if hours <= 6:
        return "Final 6hrs"
    elif hours <= 12:
        return "6-12hrs"
    elif hours <= 24:
        return "12-24hrs"
    elif hours <= 48:
        return "24-48hrs"
    return "48-72hrs"

deadline_transfers["hour_bucket"] = deadline_transfers[
    "hours_until_deadline"
].apply(bucket_hours)

# Analysis by bucket
deadline_analysis = deadline_transfers.groupby("hour_bucket").agg({
    "transfer_id": "count",
    "fee_millions": ["mean", "median", "std"]
}).reset_index()

deadline_analysis.columns = ["hour_bucket", "n_deals", "avg_fee",
                              "median_fee", "fee_std"]
deadline_analysis["fee_volatility"] = (
    deadline_analysis["fee_std"] / deadline_analysis["avg_fee"]
)
deadline_analysis["pct_of_deadline_deals"] = (
    deadline_analysis["n_deals"] / deadline_analysis["n_deals"].sum() * 100
)

print(deadline_analysis[["hour_bucket", "n_deals", "avg_fee", "fee_volatility"]])

# Deal completion simulation
hours = np.arange(72, -1, -6)
completion_sim = pd.DataFrame({
    "hours_remaining": hours,
    "deal_started_prob": 0.3 + 0.5 * (1 - hours/72),
    "completion_prob": np.where(
        hours > 24, 0.85,
        np.where(hours > 12, 0.70,
                 np.where(hours > 6, 0.55, 0.40))
    )
})

print("\nDeal Completion Rates by Time Remaining:")
print(completion_sim[completion_sim["hours_remaining"].isin([72, 48, 24, 12, 6, 0])])