Session 1 — The Problem + Tooling Overview

Introduction

Every month, someone on your team opens SQL Developer, runs an enrollment query, downloads the results, pulls school directory data from the Urban Institute website, pastes everything into Excel, writes formulas, and manually builds a report. It takes two or more hours and introduces errors every time.

This workshop replaces that process with a single Python command. By the end of the thirteen core sessions, you will have built the tool yourself, from scratch, one piece at a time.

This first session is an orientation. You will see the finished tool run live, understand what each piece of the stack does, and learn how the workshop is organized.

Teaching Approach

Goals and Expectations

This workshop is designed for people with no previous Python or coding experience. You do not need any prior background to participate.

WarningBeginner-friendly does not mean easy!

We are building a real-world automated data pipeline. That is a complex process with many moving parts, including:

  • a database
  • a web API
  • data transformation logic
  • charts
  • an Excel report

All of these pieces will be wired together and run from a single command. You will encounter concepts that take time to absorb, and some things will not click on the first pass. That is expected and completely normal.

Don’t get frustrated if you encounter many concepts that you don’t understand. Be patient, ask questions, and be prepared to think deeply. The payoff is immense.

How This Workshop Teaches

Sessions follow a code-along format: the instructor introduces code, you follow on your own machine. Each session adds one file or one function to the project. By Session 13, you will have built the complete pipeline.

A few things to keep in mind:

  • Sessions build on each other. Missing a session or falling behind makes the next one harder. Attendance and keeping pace matter more in a cumulative build than in a standalone class.
  • We are moving quickly. This workshop introduces many tools and concepts such as conda, Git, Oracle, REST APIs, pandas, matplotlib, openpyxl without going deep on any of them. The goal is exposure and a working mental model, not mastery.
  • Practice exercises at the end of most sessions give you a chance to apply what was covered independently. They are optional enrichment.
  • You will not understand everything the first time. That is normal. The materials are designed to be returned to.
  • If you fall behind, you can catch up. Every session is typed from scratch, so there is no per-session sync step to remember. To catch up, open the module for the session you missed. Each module ends with the complete version of the file it builds. Copy that code into your fork and you can rejoin at the next session.

What We Do Not Expect

We do not expect you to:

  • Be able to code a complete analytics pipeline from scratch when this workshop ends
  • Memorize syntax, commands, or package names

What We Do Hope

  • You pick up at least a few techniques that are immediately useful in your day-to-day work.
  • You develop a working mental model of what an automated pipeline looks like and what each component does.
  • You feel comfortable returning to the workshop materials as a reference when you want to build something new.
  • You get comfortable directing an AI coding agent. The context you build in this workshop gives you enough vocabulary to ask better questions, validate the output, and catch mistakes.

Support and Resources

All session modules, sample code, and documentation are published on the workshop GitHub page and as a Quarto book. They are yours to read, search, and reference after the workshop ends.

Office hours are held directly after every session. The workshop Teams channel is open for questions and troubleshooting between sessions. There are no bad questions. Ask during sessions, in Teams, or at office hours.

The Problem: A Report That Takes All Day

Your GSU outreach office needs to produce the middle school outreach report every month. The report shows which former middle schools your current students attended, how many students came from each, and how those schools break down by size and location. The report is used to decide which schools to prioritize in the next outreach cycle.

Right now, producing it requires manual exports from Oracle, a CSV download from the Urban Institute website, as well as an an email download of a survey. This process takes hours of work in Excel. This workshop replaces that process in two deliberate phases.

Here is the current workflow for producing the middle school outreach report:

Step Tool Time Pain point
Export enrollment data SQL Developer 15–30 min Manual query, copy-paste to Excel
Download school directory Urban Institute website 15–20 min Manual search, CSV export
Load survey data Excel 10 min Separate file, manual import
Merge and clean Excel (VLOOKUP / Power Query) 30–45 min Formula errors, mismatched IDs
Build aggregations Excel (pivot tables) 20–30 min Rebuilding the same pivots every month
Create charts Excel 15–20 min Manual resizing, copy-paste into report
Save and distribute Email 5–10 min Version confusion

Total: 2+ hours every time, before any fixes for errors discovered later.

The problems stack up:

  • A typo in a formula silently produces wrong numbers.
  • Adding a new data source means rebuilding the merge by hand.
  • There is no record of what changed between runs.
  • No one else can reproduce the report from scratch without asking.

The Solution — In Two Steps

This workshop replaces the manual process in two deliberate phases.

Phase 1 (Sessions 3–8): Three CSV files are pre-loaded into the repo: enrollment data, a school directory, and a survey. They represent the files someone would have exported from SQL Developer and downloaded from the Urban Institute website and email. You will build the Python pipeline that loads, merges, transforms, and reports on them. By the end of Session 8, you will be producing the full report from a two-line command.

Phase 1 — provided CSV files

enrollment CSV               survey CSV                schools CSV
data/enrollment.csv    data/survey_middle_schools.csv  data/schools.csv
      │                              │                          │
  read_csv()                    read_csv()                   read_csv()
      │                              │                          │
  enrollment_df                 survey_df                   school_df
  (one row per                  student_id                  (NY + NJ
  student × course)             middle_school_name          middle schools)
                                ncessch
      │                              │                          │
      └──────────── transform.py ────┴──────────────────────────┘
                          │
             1. deduplicate enrollment → one row per student
             2. merge students → survey on student_id
             3. merge result → CCD on ncessch
             4. assign school_size bucket (enrollment column)
             5. summarize: top 10 schools, ZIP counts, city counts, size dist.
                          │
                      report.py
               ┌──────────┴──────────┐
           charts (.png)        Excel workbook
           - top 10 schools     - Student Data
           - school size dist.  - Top 10 Schools
                                - By ZIP
                                - By School Size
                                - Charts

Phase 2 (Sessions 9–12): You will build the code that automates where those CSV files come from: a database connection to Oracle and an API call to the Urban Institute. When main.py wires everything together in Session 13, the pipeline fetches fresh data and produces the full report end-to-end with no manual steps.

Phase 2 — automated pipeline

Oracle EC2                    survey CSV                Urban Institute API
(STUDENT schema)         data/survey_middle_schools.csv  (CCD directory)
      │                              │                          │
   db.py                        read_csv()                   api.py
      │                              │                          │
  enrollment_df                 survey_df                   school_df
  (one row per                  student_id                  (NY + NJ,
  student × course)             middle_school_name          fips='36,34')
                                ncessch
      │                              │                          │
      └──────────── transform.py ────┴──────────────────────────┘
                          │
             1. deduplicate enrollment → one row per student
             2. merge students → survey on student_id
             3. merge result → CCD on ncessch
             4. assign school_size bucket (enrollment column)
             5. summarize: top 10 schools, ZIP counts, city counts, size dist.
                          │
                      report.py
               ┌──────────┴──────────┐
           charts (.png)        Excel workbook
           - top 10 schools     - Student Data
           - school size dist.  - Top 10 Schools
                                - By ZIP
                                - By School Size
                                - Charts

What We’ll Build

By Session 13 you will run one command:

python main.py --year 2019 --output reports/

And in under a minute, the reports/ folder will contain:

Output Description
merged.csv One row per student, with their former middle school’s name, location, and enrollment profile
top_middle_schools.png Horizontal bar chart — top 10 schools by student count
school_size_distribution.png Bar chart — students grouped by school size (Small / Medium / Large)
student_report.xlsx Five-sheet Excel workbook: Student Data, Top 10 Schools, By ZIP, By School Size, Charts

The same command runs every month. The output is identical in structure every time. If the data changes, the report reflects it automatically.

Live Demo

Your instructor will run python main.py --year 2019 --output reports/ live. As you watch, notice:

  1. The terminal output — each step prints what it is doing and how many records it found.
  2. The output folder — four files appear in reports/ within about a minute.
  3. The Excel workbook — open student_report.xlsx and click through the five sheets. Everything the manual process produced, in one file.
  4. The source code — the instructor will briefly scroll through the five Python files that make this work. You are going to write all of this yourself.

You do not need to follow along on your own computer today. This session is observation only.

Workshop Roadmap

Session Topic What you’ll build
0 (before session 1) Before you begin Miniconda, Git, VS Code installed; GitHub account; repo forked
1 The problem + tooling overview (this session — observation)
2 Clone, build, and run Clone your fork, create the conda environment, first commit and push
— Phase 1: Working with Provided Data —
3 Python foundations Core Python primer: variables, loops, functions
4 Pandas and working with data Load and explore all three provided CSVs
5 Merging the three sources transform.py v1 — three-way merge from provided CSVs
6 Aggregations and summaries transform.py v2 — groupby, pd.cut
7 Creating visualizations report.py v1 — two charts
8 Generating the Excel report report.py v2 — five-sheet workbook; run Phase 1 end-to-end
— Phase 2: Automating Data Collection —
9 Connecting to the database db.py v1 — raw Oracle connection
10 Working with database results db.py v2 — conn.execute_query(), save to CSV
11 Calling a web API api.py v1 — fetch school directory
12 Working with API results api.py v2 — select columns, save to CSV
13 The automated pipeline main.py — wire it all together
14 (optional) Unit testing tests/ — pytest basics

Sessions 9 and 10 require a connection to the GSU network. On campus, GSU WiFi is sufficient. Off campus, connect to the GSU VPN first.

Additional Resources