Logan's Analytics Bootcamp
Dashboard › Phase 4: Python for Report Automation
M4.3

pandas II: Joining, Aggregating, Reshaping, Time Series

⏱ 8 hours📅 Weeks 22–23❓ 4 quiz questions
Phase 4 goal

Write Python that reads messy program data, transforms it with pandas, and generates finished Excel and Word deliverables (the same kind of document automation your coworker builds), reliably and repeatably.

Environment note: Use your organization's approved Python distribution if one exists. Some environments require specific installers, package mirrors, or a pre-approved package list. Packages used: pandas, openpyxl, python-docx, matplotlib (and optionally docxtpl). Work only with the fictional dataset while learning.

🎯 Learning objectives

Tick each one when you can actually do it.

💡 Key concepts

  • merge: pd.merge(milestones, programs, on="ProgramID", how="left", validate="many_to_one", indicator=True). indicator shows unmatched rows (like an anti-join check).
  • groupby + agg:
    summary = (evm.groupby("ProgramID", as_index=False)
              .agg(BCWP=("BCWP","sum"), ACWP=("ACWP","sum"), BCWS=("BCWS","sum")))
summary["CPI"] = summary.BCWP / summary.ACWP
  • Cumulative EVM: evm.sort_values("PeriodEnd").groupby("ProgramID")[["BCWS","BCWP","ACWP"]].cumsum()
  • pivot_table: pd.pivot_table(budget, index="ProgramID", columns="FYMonth", values="ActualObligation", aggfunc="sum", fill_value=0)
  • melt: wide months → long (the Unpivot equivalent).
  • Dates: .dt.year, .dt.month, fiscal year = df.Date.dt.year + (df.Date.dt.month >= 10), pd.offsets.MonthEnd(), .resample("ME") (month-end frequency alias in recent pandas).
  • Latest snapshot per group: df.sort_values("PeriodEnd").groupby("ProgramID").tail(1)

🛠 Hands-on exercise: Python EVM Engine

⬇ Practice data for this module
D1 programsProgram dimension (12 rows)CSVXLSX
D3 evm_monthlyMonthly EVM by control account (~2,000 rows)CSVXLSX
D5 budget_executionBudget execution by appropriation (1,296 rows)CSVXLSX

Data: evm_monthly (ProgramID, ContractNumber, WBS, PeriodEnd, BCWS, BCWP, ACWP, BAC, EAC_Contractor), programs.

  1. Compute cumulative BCWS/BCWP/ACWP per program over time (sum control accounts per program-month first).
  2. Get the latest period snapshot per program. BAC = sum of control-account BAC in the latest period.
  3. Calculate CPI, SPI, CV, SV, EAC (BAC/CPI), VAC, TCPI, and EAC Gap vs. EAC_Contractor.
  4. Merge in ProgramName and Portfolio (with validate).
  5. Cross-check: your numbers must match the Power BI and Excel results for PRG-005. Write an assert that fails if the difference is > $1.

📦 Mini-project: Budget Execution Analyzer

From budget_execution, produce: (a) FYTD planned vs. actual obligations by program and appropriation, (b) obligation rate and gap to plan, (c) a pivot by FY month, (d) the 5 programs furthest behind plan. Save each as a separate DataFrame, ready for export in 4.4.

🧠 Self-check quiz

Answer in your own words, then check. Grade yourself honestly; your score is saved.

Q4.3.1
What does validate="many_to_one" protect against?
Write at least a few words to unlock.
Q4.3.2
How do you get the most recent record per program?
Write at least a few words to unlock.
Q4.3.3
Which pandas function is the equivalent of Power Query's Unpivot?
Write at least a few words to unlock.
Q4.3.4
Why aggregate control accounts to program-month level before taking cumsum?
Write at least a few words to unlock.
← M4.2 pandas I: Loading, Cleaning, SelectingM4.4 Excel Output with openpyxl →