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": "alice@univ.edu", "grade": "A"},
        {"name": "Bob", "email": "bob@univ.edu", "grade": "B+"},
        {"name": "Carol", "email": "carol@univ.edu", "grade": "A-"},
        {"name": "David", "email": "david@univ.edu", "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 (which has no posts).

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...
  ...

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.

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 2. For each city, make a request to https://jsonplaceholder.typicode.com/users and find any user who lives in that 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

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 rate limiting.

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 per day 3. Identifies the most productive day of the week (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

Selected solutions are available in Appendix G. For exercises involving API calls, the JSONPlaceholder API (https://jsonplaceholder.typicode.com) is free, requires no authentication, and returns predictable data, making it ideal for testing and grading.