Exercises: Working with Data: CSV, JSON, and APIs

These exercises progress from concept checks through applied data processing to challenging multi-source integration projects. Most exercises require writing runnable Python code.

Difficulty Guide: - ⭐ Foundational (5-10 min each) - ⭐⭐ Intermediate (10-20 min each) - ⭐⭐⭐ Challenging (20-40 min each) - ⭐⭐⭐⭐ Advanced/Research (40+ min each)

Part A: CSV Fundamentals ⭐

A.1. Write a script that creates a CSV file called students.csv with the following data, then reads it back and prints each row:

Use csv.writer to create the file and csv.reader to read it.

A.2. Rewrite Exercise A.1 using csv.DictWriter to create the file and csv.DictReader to read it. Print each student's name and GPA in a formatted string like "Alice Chen: 3.87 GPA".

A.3. Explain why the following code is dangerous and will eventually produce incorrect results. What should be used instead?

with open("data.csv") as f:
    for line in f:
        fields = line.strip().split(",")
        print(fields)

A.4. Write a script that reads students.csv (from A.1) and creates a new file cs_students.csv containing only Computer Science majors. Use DictReader and DictWriter.

A.5. Create a CSV file where one of the names contains a comma: "Smith, John". Write the file using csv.writer, then read it back and verify the name is correctly preserved as a single field. What quoting mechanism makes this work?

Part B: JSON Fundamentals ⭐

B.1. Given the following Python dictionary, convert it to a JSON string, then convert it back. Verify that the round-tripped data matches the original:

course = {
    "code": "CS101",
    "title": "Intro to Computer Science",
    "credits": 3,
    "prerequisites": ["MATH100"],
    "active": True,
    "waitlist": None
}

B.2. Write a function save_config(config: dict, path: str) -> None that saves a configuration dictionary to a JSON file with 2-space indentation and sorted keys. Write a companion function load_config(path: str) -> dict that loads the file back. Test both functions.

B.3. Given the following nested JSON (as a Python dict), write code to extract and print: - The instructor's name - The number of students enrolled - The third student's email - All student names as a list

course_data = {
    "course": "CS101",
    "instructor": {"name": "Dr. Smith", "office": "Room 314"},
    "students": [
        {"name": "Alice", "email": "[email protected]", "grade": "A"},
        {"name": "Bob", "email": "[email protected]", "grade": "B+"},
        {"name": "Carol", "email": "[email protected]", "grade": "A-"},
        {"name": "David", "email": "[email protected]", "grade": "B"}
    ]
}

B.4. Explain why the following code raises a TypeError. Fix it so the data can be serialized to JSON:

import json
data = {
    "tags": {"python", "coding", "cs1"},
    "coordinates": (42.3, -71.1),
    "id": 42
}
json.dumps(data)

Part C: API Basics ⭐⭐

C.1. Write a script that makes a GET request to https://jsonplaceholder.typicode.com/posts/1 and prints: - The HTTP status code - Whether the request was successful (using .ok) - The post's title - The post's body (first 100 characters)

C.2. Write a script that fetches all 10 users from https://jsonplaceholder.typicode.com/users and prints a formatted table showing each user's name, email, and city. Use response.json() to parse the response.

C.3. Write a function get_user_posts(user_id: int) -> list[dict] that fetches all posts by a specific user from https://jsonplaceholder.typicode.com/posts?userId={user_id}. The function should: - Return a list of post dicts if successful - Return an empty list if the request fails - Print the number of posts found

Test it with user IDs 1, 5, and 999. User 999 doesn't exist, but the API doesn't treat that as an error: it returns status 200 with an empty list. Explain why your function should still report "0 posts" rather than an error.

C.4. Modify your function from C.3 to accept any user ID and handle the following errors gracefully: - Network connection failure - Timeout (set a 5-second timeout) - Non-200 status code - Invalid JSON response

Print a descriptive message for each error type.

C.5. Write a script that fetches all users and all posts from JSONPlaceholder, then creates a report showing each user's name followed by the titles of their posts, like:

Leanne Graham (10 posts):
  1. sunt aut facere repellat...
  2. qui est esse...
  ...

Make exactly two requests (one for users, one for posts) and do the matching in Python. Why is that better than requesting each user's posts separately?

C.6. Using the fetch_all_pages() pattern from Section 21.7.4, fetch all 200 todos from https://jsonplaceholder.typicode.com/todos in pages of 25. Print how many pages it took and how many todos are marked completed. Then change the page size to 30 and explain why the last page is smaller.

Part D: Data Processing Patterns ⭐⭐

D.1. Write a function csv_to_json(csv_path: str, json_path: str) -> int that reads a CSV file and writes its contents as a JSON file (a list of dicts). Return the number of records converted. Test it with the students.csv file from Part A.

D.2. Write the reverse function: json_to_csv(json_path: str, csv_path: str) -> int that reads a JSON file containing a list of dicts and writes a CSV file. Use the keys from the first dict as column headers. Then test it with a list where a later dict has an extra key: what does DictWriter do, and which of its parameters (see the TaskFlow checkpoint in Section 21.10) changes that behavior? Is silently dropping the extra column the right choice here?

D.3. You have the following CSV data representing daily temperatures:

date,city,temp_f
2025-01-15,Portland,45
2025-01-15,Seattle,42
2025-01-15,San Francisco,58
2025-01-16,Portland,48
2025-01-16,Seattle,44
2025-01-16,San Francisco,61
2025-01-17,Portland,43
2025-01-17,Seattle,40
2025-01-17,San Francisco,55

Write a script that: 1. Reads the CSV 2. Calculates the average temperature for each city 3. Finds the city with the highest average temperature 4. Writes the averages to a new CSV file

D.4. Write a function merge_csv_files(file_paths: list[str], output_path: str) -> int that takes multiple CSV files with the same columns and merges them into a single output file. The output should have one header row followed by all data rows from all files. Return the total number of data rows written.

Part E: Integration and Pipelines ⭐⭐⭐

E.1. Build a complete data pipeline that: 1. Fetches all users from https://jsonplaceholder.typicode.com/users 2. Fetches all posts from https://jsonplaceholder.typicode.com/posts 3. Creates a CSV report with columns: user_name, user_email, user_city, post_count, avg_title_length 4. Saves the report as both CSV and JSON

E.2. Write a script that fetches posts from JSONPlaceholder, filters them to find all posts whose body contains the word "quia" (case-insensitive), and exports the results to a JSON file with pretty printing. Include the total count and the filtered posts.

E.3. Create a simple "data enrichment" script: 1. Start with a CSV file of city names and populations. JSONPlaceholder's users live in made-up cities (Gwenborough, Wisokyburgh, McKenziehaven, ...), so include a few of those plus a few real cities, so that you see both matches and misses. 2. Fetch https://jsonplaceholder.typicode.com/users once, build a dictionary that maps each city to its users, and use it to find the users who live in each CSV city (this is a simulated enrichment — real apps would use a geocoding API) 3. Write an enriched CSV that includes the original data plus any matching user info, with blank columns for cities that had no match

E.4. Write a command-line tool that accepts a JSONPlaceholder resource type (posts, comments, users, todos) as a command-line argument and: 1. Fetches all records of that type 2. Displays a summary (count, first 3 records preview) 3. Asks the user if they want to export as CSV, JSON, or both 4. Performs the export

Part F: Challenge Problems ⭐⭐⭐⭐

F.1. Multi-API Aggregator: Write a program that: 1. Fetches users from https://jsonplaceholder.typicode.com/users 2. For each user, fetches their posts from https://jsonplaceholder.typicode.com/posts?userId=N 3. For each post, fetches its comments from https://jsonplaceholder.typicode.com/comments?postId=N 4. Produces a JSON report showing: user name, number of posts, total comments across all posts, and the post with the most comments

Add a 0.1-second delay between API calls to practice polite, rate-limit-friendly code. Before you run it, predict how many requests your program will make (1 for users, 10 for posts, 100 for comments) and roughly how long the delays alone will add.

F.2. Data Format Converter: Build a general-purpose converter that can convert between CSV and JSON in either direction. The tool should: - Accept source and destination file paths as arguments - Auto-detect the source format from the file extension - Handle nested JSON by flattening it (e.g., {"address": {"city": "Portland"}} becomes a column address.city) - Provide a --pretty flag for JSON output - Print conversion statistics (records processed, fields per record, file sizes)

F.3. TaskFlow Analytics: Using the TaskFlow v2.0 export feature, write a separate analytics script that: 1. Reads exported TaskFlow JSON data 2. Generates statistics: tasks by priority, tasks by category, completion rate, average tasks created per day 3. Identifies the busiest day of the week (the day on which the most tasks were created; v2.0 records a created_at timestamp but not a completion time, so explain what you'd add to TaskFlow to find the day with the most tasks completed) 4. Exports a summary report as both CSV and JSON 5. Fetches a motivational quote from the API and includes it in the report

F.4. CSV Data Quality Checker: Write a tool that analyzes a CSV file for data quality issues: - Missing values (empty cells) - Inconsistent data types in a column (e.g., some rows have numbers, others have text) - Duplicate rows - Outliers (values more than 3 standard deviations from the mean, for numeric columns) - Report findings as a JSON document

Solutions Notes

For exercises involving API calls, the JSONPlaceholder API (https://jsonplaceholder.typicode.com) is free, requires no authentication, and returns the same data every time, making it ideal for testing and grading. Use that to check your own work: it always has 10 users, 100 posts (10 per user), 500 comments (5 per post), and 200 todos. Write requests are only simulated, so a POST gets a realistic response but nothing is actually saved.