Lab 12 — CSV Files: Pandas and By Hand

Skills: None

Submission

This lab is done via VSCode on your computer, and submitted to Github via the Source Control tab. Go to Pawtograder to find the repository, which you can clone locally to do the assignment. Commits automatically make submissions, and feedback can be viewed on Pawtograder.

---

Introduction

A CSV (comma-separated values) file is a plain-text table. The first line is usually a header naming the columns; every line after that is one row, with values separated by commas. Here is a tiny example:

book_title,borrower_name,days,loan_type
The Hobbit,Alice Nguyen,7,in-library
1984,Ben Carter,16,home-loan

In this lab you will load a CSV of library loans, transform it, and write a new CSV — twice. First with Pandas, which does most of the work for you. Then by hand, using only open, string methods, and lists, so you can see what Pandas is doing under the hood.

The dataset is library_loans.csv. If it is not already in your starter repository, download it from:

https://raw.githubusercontent.com/neu-pdi/cs2000-public-resources/refs/heads/main/static/support/library_loans.csv

and save it in the same folder as your Python file. The columns are:

Column	Meaning
book_title	title of the book
borrower_name	who borrowed it
days	how many days it was out
loan_type	"in-library" or "home-loan"

Work in a file named lab12.py (or whatever the starter uses). You will write two versions of each later problem: one using Pandas, and one using only basic Python.

---

Part 1 — With Pandas

Pandas represents a CSV as a DataFrame: a table with named columns. You have already used DataFrames to load and filter tables; this part focuses on reading from and writing to files.

Loading

import pandas as pd

loans = pd.read_csv('library_loans.csv')
print(loans)
print(loans.columns)   # the header, as a list-like object

read_csv opens the file, splits on commas, uses the first row as column names, and tries to guess types (so days will already be an integer, not a string).

Filtering

A boolean mask selects rows. This keeps only home loans — the same filter we will redo by hand in Part 2:

home_loans = loans[loans['loan_type'] == 'home-loan']

Adding a column

You can assign a new column in one shot. For example, a fee of $0.25 for every day over 14:

loans['overdue_fee'] = loans['days'].apply(lambda d: max(d - 14, 0) * 0.25)

apply runs the function on each value in the days column. max(d - 14, 0) is 0 when the book is not overdue.

Writing

home_loans.to_csv('home_loans.csv', index=False)

index=False stops Pandas from writing the row numbers (0, 1, 2, …) as an extra first column. You almost always want index=False.

Worked example

Putting those pieces together — read, filter, write:

import pandas as pd

loans = pd.read_csv('library_loans.csv')
home_loans = loans[loans['loan_type'] == 'home-loan']
home_loans.to_csv('home_loans.csv', index=False)

Run this once and open home_loans.csv to confirm it looks like a CSV with the same header and only home-loan rows.

---

Part 2 — By hand (no Pandas)

Everything Pandas just did can be done with ordinary Python. The steps are:

1. Open the file and read its lines.
2. Split each line on commas to get a list of cells.
3. Treat the first row as the header and the rest as data.
4. Convert strings to numbers where needed.
5. Filter / transform the rows.
6. Write the result back out.

Reading

with open('library_loans.csv', 'r') as file:
    lines = file.readlines()

with open(...) as file: opens the file and closes it automatically when the block ends (even if an error happens). The 'r' means read mode.

readlines() returns a list of strings, one per line, each still including the newline character \n at the end.

Splitting into cells

data = []
for line in lines:
    cells = line.strip().split(',')
    data.append(cells)

• strip() removes the trailing \n (and any extra spaces).
• split(',') turns "The Hobbit,Alice Nguyen,7,in-library" into ["The Hobbit", "Alice Nguyen", "7", "in-library"].

Every value is still a string, including "7".

Header vs. data

headers = data[0]
loans = data[1:]

headers is ["book_title", "borrower_name", "days", "loan_type"]. To find a column by name:

days_index = headers.index('days')
loan_type_index = headers.index('loan_type')

index returns the position of that name in the header list, so you can look up the matching cell in each row.

Converting and filtering

home_loans = []
for row in loans:
    row[days_index] = int(row[days_index])   # "16" -> 16
    if row[loan_type_index] == 'home-loan':
        home_loans.append(row)

Writing

output = [headers] + home_loans
with open('home_loans.csv', 'w') as out_file:
    for row in output:
        out_file.write(','.join(map(str, row)) + '\n')

• 'w' means write mode (this overwrites the file if it already exists).
• map(str, row) turns every cell back into a string (16 -> "16"), because join only works on strings.
• ','.join(...) puts commas back between cells.
• + '\n' ends the line.

Worked example

The same task as Part 1, by hand:

with open('library_loans.csv', 'r') as file:
    lines = file.readlines()

data = []
for line in lines:
    cells = line.strip().split(',')
    data.append(cells)

headers = data[0]
loans = data[1:]

days_index = headers.index('days')
loan_type_index = headers.index('loan_type')

home_loans = []
for row in loans:
    row[days_index] = int(row[days_index])
    if row[loan_type_index] == 'home-loan':
        home_loans.append(row)

output = [headers] + home_loans
with open('home_loans_by_hand.csv', 'w') as out_file:
    for row in output:
        out_file.write(','.join(map(str, row)) + '\n')

Open home_loans.csv (Pandas) and home_loans_by_hand.csv (by hand). They should contain the same rows.

---

Part 3 — Exercises

For each problem below, write both a Pandas version and a by-hand version. Name them so it is obvious which is which, e.g. overdue_fees_pandas() and overdue_fees_by_hand().

Use library_loans.csv as the input unless the problem says otherwise.

Problem 1 — Overdue fees

Add a new column overdue_fee. Each day over 14 costs $0.25; a loan of 14 days or fewer has fee 0. Write the full table (header plus every row, with the new column) to overdue_fees.csv.

Problem 2 — Missing cells

CSV files in the wild are often messy: a row might have too few commas, so it has fewer cells than the header.

Create a small file messy_loans.csv by copying library_loans.csv and deleting one cell from one data row (leave the commas so that row has fewer fields than the header, or remove a comma so two fields fuse). Then:

1. Try to process it. What happens, in each version? (Pandas and by-hand fail in different ways — write down what you observe.)
2. Change your by-hand code to skip any row whose length does not match the header, print a warning that includes the line number, and write only the complete rows to clean_loans.csv.

Problem 3 — Count by loan type

Count how many loans there are of each unique loan_type (you should see in-library and home-loan). Print each type and its count.

Problem 4 — Most-borrowed title

Find the book_title whose rows have the highest total days (if a title appeared more than once you would add its days; in this file each title appears once, but write the code as if duplicates were possible). Print the title and the total.

Problem 5 — Sort by days

Sort the loans by days, largest first. Write the sorted table (with the header) to sorted_loans.csv.

If days is still a string, "9" will sort after "28" — convert first.