pandas I: Loading, Cleaning, Selecting
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 optionallydocxtpl). Work only with the fictional dataset while learning.
🎯 Learning objectives
Tick each one when you can actually do it.
💡 Key concepts
- Loading:
pd.read_csv(),pd.read_excel(path, sheet_name=None, skiprows=3)(dict of all sheets),usecols,dtype. - Inspecting:
.head(),.info(),.describe(),.shape,.isna().sum(),.value_counts(). - Selecting:
df["col"],df[["a","b"]],df.loc[row_filter, cols], boolean masks:df[(df.Status=="Open") & (df.Score>=15)],.query("SlipDays > 30"). - New columns: vectorized math
df["CPI"] = df.BCWP / df.ACWP,np.where,.map(dict),.apply(sparingly). - Cleaning:
pd.to_datetime(df.ForecastDate, errors="coerce")(TBD → NaT)df.col.str.strip().str.upper()- money parsing via
.str.replace+ a function for M/K suffixes .fillna(),.dropna(subset=[...]),.drop_duplicates().rename(columns={...})with a header-variant map
- pandas 3.x notes: Copy-on-Write is now the default (avoids "SettingWithCopy" confusion), and a dedicated string dtype is the default for text columns. Assign results back explicitly:
df = df.assign(...).
🛠 Hands-on exercise: Clean the Status Drops in pandas
Data: status_drops/ (D8 messy files).
- Use
Path.glob("PRG-*_Status_*.xlsx")to loop over files,read_excel(skiprows=…)each one, and addSourceFile. - Normalize headers with a mapping dict (
{"Fcst Dt": "ForecastDate", "Est. Completion": "ForecastDate", ...}). - Concatenate with
pd.concat. - Clean RAG (
.str.strip().str.upper().map({"G":"Green","GREEN":"Green","GRN":"Green",...})). - Parse dates (coerce) and money (custom function). Add
DateQualityFlag. - Save to
status_clean.csvand compare row counts with your Power Query output from Module 1.4. They should match.
📦 Mini-project: Data Quality Report
A script that loads all D1–D7 datasets and prints/saves a data quality summary: row counts, % missing per column, duplicate keys, orphaned foreign keys (e.g., milestones whose ProgramID isn't in programs), dates outside the program baseline window, and negative dollar values. Output: dq_report.csv.
🧠 Self-check quiz
Answer in your own words, then check. Grade yourself honestly; your score is saved.
errors="coerce" do in pd.to_datetime?df[(df.Status=="Open") & (df.Likelihood*df.Consequence >= 15)].apply or loops?pd.read_excel(path, sheet_name=None) returns a dict of {sheet name: DataFrame}.