# Exercise: Report Card Merge

Write six functions that put a middle school's records together, stacking two terms of quiz scores, melting them long, joining the student list with a validated left join, averaging each homeroom, finding the students in no club, and pivoting the scores back to a table people can read, and let pytest check the table each one returns.

Journeyman · Python · Database · Updated November 19, 2026

By Gregor Dzierzon

Oakridge Middle School's principal wants this year's report cards, and the records are in five separate files: the student list, a file of quiz scores for each term, the after-school clubs, and who is in each club. You'll write the six functions that put them together, each one step of the report, and tests call each function and check the table it hands back. A notebook comes with the exercise for looking at the files while you work.

## Get the exercise code

Open this lesson's GitHub repository, [github.com/ask-codey/exercise-data-report-card-merge](https://github.com/ask-codey/exercise-data-report-card-merge), also linked from the **Exercise** box beside the lesson. Clone it into your `python-course` folder. In VS Code's terminal:

```bash
cd ~/python-course
git clone https://github.com/ask-codey/exercise-data-report-card-merge.git
```

Git makes a folder, `exercise-data-report-card-merge`, with the exercise code inside. *Get the Exercise Code with Git* walks through cloning step by step, if you'd like a reminder. Open that folder in VS Code (**File > Open Folder**), open its terminal with **Terminal > New Terminal**, and make the exercise's virtual environment. On Windows (PowerShell):

```powershell
python -m venv .venv
.venv\Scripts\Activate.ps1
pip install -r requirements.txt
```

On a Mac (zsh):

```bash
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
```

The prompt starts with `(.venv)` once the virtual environment is on. The exercise's README says what each command does, and what to do if PowerShell won't run the second one. Then make the data, once:

```bash
python seed.py
```

```text
Wrote students.csv: 240 rows.
Wrote fall_scores.csv: 240 rows.
Wrote spring_scores.csv: 240 rows.
Wrote clubs.csv: 8 rows.
Wrote memberships.csv: 237 rows.
```

The folder holds:

- `seed.py`: makes the school's five files, our own made-up data. Its fixed random seed means everyone gets the same rows.
- `report_cards.py`: the six functions, for you to finish.
- `explore.ipynb`: a notebook for looking at the files and trying your code. Choose the `.venv` kernel with **Select Kernel** (top right) before you run it. The tests don't read it.
- `report.py`: builds the report-card tables with your six functions and prints what each step gave. Ready to use.
- `tests/`: the tests, which call each function and check what comes back, and `conftest.py`, which gives every test its own fresh copy of the five files. You don't need to change them.
- `requirements.txt` and `pytest.ini`: the packages the exercise needs, at exact versions, and where pytest finds the tests and your code.

## What to do

Every function in `report_cards.py` is already declared with its name and parameters, and for now it stops with a `NotImplementedError` that says *Not built yet.* The docstring under each one says what it should do, with examples:

```python
def scores_long(wide):
    """Return the wide table melted into a long one: one row for each student's score on each quiz.

    wide has student_id, term, and a column per quiz (quiz_1 to quiz_4): what both_terms returns.
    The result has exactly four columns, in this order: student_id, term, quiz and score.
    quiz holds the old column's name ('quiz_1') and score its number.

    Student 1001's fall row, 74, 59, 84 and 72 -> four rows:
        1001, fall, quiz_1, 74
        1001, fall, quiz_2, 59
        1001, fall, quiz_3, 84
        1001, fall, quiz_4, 72
    On the files seed.py makes: 480 rows times 4 quizzes = 1920 rows.
    """
    raise NotImplementedError("Not built yet.")
```

In each function, replace the `raise NotImplementedError("Not built yet.")` line with your own code:

1. **`both_terms(fall, spring)`** stacks the two term files into one table, fall's rows first, with a `term` column that says `fall` or `spring`, and its rows numbered from 0. On the seeded files, 240 and 240 rows make 480.
2. **`scores_long(wide)`** melts that table into the columns `student_id`, `term`, `quiz` and `score`. Student 1001's fall row, 74, 59, 84 and 72, becomes four rows: `1001, fall, quiz_1, 74` to `1001, fall, quiz_4, 72`. On the seeded files, 1,920 rows.
3. **`with_students(scores, students)`** adds each score's `first_name`, `grade` and `homeroom` with a left join on `student_id`, so every score row stays and a score whose student isn't on the list gets `NaN`. If the student list has a `student_id` twice, it raises pandas' `MergeError`.
4. **`average_by_homeroom(df)`** returns two columns, `homeroom` and `average_score`: every score in each homeroom averaged and rounded to 1 decimal place, one row per homeroom, sorted by homeroom. Homeroom 6A with scores 80 and 90, and 7B with 70, 75 and 77, give 6A 85.0, then 7B 74.0.
5. **`students_without_a_club(students, memberships)`** returns the students with no row in `memberships`, with the student list's four columns, in its order, numbered from 0. With students 1001, 1002 and 1003, where 1001 is in two clubs and 1003 in one, the answer is 1002 alone.
6. **`quiz_table(long)`** turns the long table back into one row per student per term, with the columns `student_id`, `term` and `quiz_1` to `quiz_4`, sorted by student and then term, numbered from 0. If the same student, term and quiz appear on two rows, it raises `ValueError` instead of averaging them.

Each function returns a new table and leaves the ones it was given unchanged; the tests check that too.

## Check your work

Run the tests from the exercise folder, with the virtual environment on:

```bash
python -m pytest
```

At first all 19 tests fail. That's expected: each function you finish turns its tests green. A failing test's message says what it called, what it expected and what came back, for example *len(both_terms(fall, spring)) should give 480, but it gave 240.*

To see your functions at work on the whole school, run `python report.py`.

## Hints

- **Look before you write.** Run the notebook's cells: the shapes and the first rows of each file show which columns you have to work with.
- **Label, then stack.** Add the `term` column to each table with `assign` before `pd.concat`, as *Stacking Tables with concat* shows; afterward, nothing says which row came from which file.
- **Two columns say whose row it is.** In `scores_long`, both `student_id` and `term` go in `id_vars`, as *Wide to Long with melt* does for the whole year.
- **Let pandas raise the errors.** `validate="many_to_one"` in `merge` raises the `MergeError` (*When a Join Goes Wrong*), and `pivot`, unlike `pivot_table`, raises the `ValueError` (*Long to Wide with pivot*).
- **Group, then name the column.** `average_by_homeroom` is a `groupby`, as *Grouping with groupby* shows; `as_index=False` and named aggregation from *Several Summaries at Once: agg* give the columns their names.
- **The indicator finds who's missing.** A left join from the student list with `indicator=True` marks the students with no club `left_only`, as *Inner, Left, Right and Outer Joins* shows. Keep only the student list's columns afterward.

## When you're done

Every test is green: pytest's last line says `19 passed`. And `python report.py` prints:

```text
1) rows in both terms: 480
2) rows in the long table: 1920
3) rows after adding the students: 1920
4) scores with no student: 0
5) highest homeroom average: 6B 80.1
6) students without a club: 58
7) rows in the quiz table: 480
8) student 1001's spring quizzes: [71, 77, 67, 66]
```

Now look at line 6, then open `memberships.csv`: one membership points at a club that doesn't exist. Does that student count as being in a club, and how would you find every membership like it?

## Submit your exercise

The exercise is how you finish this lesson. When your work is done, put it on GitHub in a repo of your own, then submit that repo's address here. *Hand In Your Work with Git* walks through each step.

1. On GitHub, click **+**, then **New repository**. Name it (for example `exercise-data-report-card-merge`), choose **Public**, and click **Create repository**. Leave out the README: your folder already has one.
2. In VS Code's terminal, in your exercise folder, send your work to your new repo. Point your copy at your own repo, then commit and push:

   ```bash
   git remote set-url origin https://github.com/your-name/exercise-data-report-card-merge.git
   git add .
   git commit -m "My solution"
   git push -u origin main
   ```

3. Paste your repo's address, such as `https://github.com/your-name/exercise-data-report-card-merge`, into **Submit your exercise** on this page. The site checks that the repo is public and holds your commits, and then the lesson is complete.

Changed something after you submitted? Push it, and submit again: your latest submission is the one that counts.

## So what

Real answers come from several tables at once. Stacking, joining and reshaping, each step a function you can test with its row count, is how five separate files become one report you can trust.

## Key terms

- [concat()](https://www.ask-codey.com/glossary/concat)
- [merge()](https://www.ask-codey.com/glossary/merge-python)
- [melt()](https://www.ask-codey.com/glossary/melt)
- [pivot()](https://www.ask-codey.com/glossary/pivot-python)
- [pytest](https://www.ask-codey.com/glossary/pytest)

## Related

- [Stacking Tables with concat](https://www.ask-codey.com/lessons/pandas-concat)
- [Joining Tables with merge](https://www.ask-codey.com/lessons/pandas-merge)
- [Inner, Left, Right and Outer Joins](https://www.ask-codey.com/lessons/pandas-join-types)
- [When a Join Goes Wrong](https://www.ask-codey.com/lessons/pandas-join-pitfalls)
- [Wide to Long with melt](https://www.ask-codey.com/lessons/pandas-melt)
- [Long to Wide with pivot](https://www.ask-codey.com/lessons/pandas-pivot)

Source: https://www.ask-codey.com/lessons/exercise-data-report-card-merge
