不使用Pandas/Numpy处理CSV计算平均借阅天数遇问题求指导

需求：不使用numpy和pandas，基于共同的book_id字段，计算同时存在于books.csv（含表头）和bookloans.csv（无表头）中的书籍的整体平均借阅天数，但现有代码中total_loans的计算逻辑错误，导致结果返回错误值1。

原代码：

import csv

total_loan_days = 0
total_loans = 0

# Open the books file and read the title and authors into a list
with open('books.csv', 'r', encoding='utf-8-sig') as csv_books_file:
    books_reader = csv.reader(csv_books_file)
    next(books_reader)  # skip the header row
    for row in books_reader:
        books = row[0]


with open('bookloans.csv', 'r', encoding='utf-8-sig') as csv_bookloans_file:
    loans_reader = csv.reader(csv_bookloans_file)
    for rows in loans_reader:
        book_number = row[0]
        return_date = rows[3]
        start_date = rows[2]
        date_diff = int(return_date) - int(start_date)
        
        
        total_loan_days += date_diff
        total_loans = len(book_number)

       
#Calculate the overall average loan days for all books
    if date_diff > 1:
        overall_average_loan_days = total_loan_days / total_loans
    else:
        overall_average_loan_days = 1

    print(f"Overall Average Loan Days: {overall_average_loan_days}")

问题排查与修正说明 #

核心错误点 #

• 有效书籍ID未存储：读取books.csv时，仅保留了最后一条book_id，没有收集所有存在的book_id，无法筛选出两个文件共有的借阅记录。
• 变量引用错误：book_number = row[0]中的row是前一个文件循环的最后一行，并非当前借阅记录的行，应该用rows[0]（假设book_id在bookloans.csv的第一列）。
• 借阅次数计数错误：total_loans = len(book_number)是取单个book_id字符串的长度，而非统计有效借阅次数，正确做法是每匹配一次就将total_loans +=1。
• 平均计算逻辑错误：用最后一条记录的date_diff判断是否计算平均完全不合理，应该判断total_loans是否大于0，避免除以0的情况。

修正后的代码 #

import csv

total_loan_days = 0
total_loans = 0

# 收集books.csv中所有的book_id，存入集合便于快速查找
valid_book_ids = set()
with open('books.csv', 'r', encoding='utf-8-sig') as csv_books_file:
    books_reader = csv.reader(csv_books_file)
    next(books_reader)  # 跳过表头
    for row in books_reader:
        book_id = row[0]
        valid_book_ids.add(book_id)

# 遍历借阅记录，只统计有效book_id的记录
with open('bookloans.csv', 'r', encoding='utf-8-sig') as csv_bookloans_file:
    loans_reader = csv.reader(csv_bookloans_file)
    for row in loans_reader:
        book_number = row[0]
        # 仅处理存在于books.csv中的书籍借阅记录
        if book_number not in valid_book_ids:
            continue
            
        return_date = row[3]
        start_date = row[2]
        # 假设日期是可转换为整数的格式（如YYYYMMDD或天数差），若为真实日期字符串需用datetime处理
        try:
            date_diff = int(return_date) - int(start_date)
            # 过滤负的天数（可能是数据错误）
            if date_diff >= 0:
                total_loan_days += date_diff
                total_loans += 1
        except ValueError:
            # 跳过日期格式错误的记录
            print(f"无效日期记录：{row}")
            continue

# 计算平均借阅天数
if total_loans > 0:
    overall_average_loan_days = total_loan_days / total_loans
else:
    overall_average_loan_days = 0  # 无有效记录时返回0更合理，可按需调整

print(f"Overall Average Loan Days: {overall_average_loan_days:.2f}")

额外优化说明 #

1. 使用set存储有效book_id，查找效率远高于列表，适合数据量较大的场景。
2. 增加异常处理，跳过日期格式错误的记录，避免程序崩溃。
3. 过滤负的天数差，排除可能的数据源错误。
4. 平均结果保留两位小数，输出更直观。

内容的提问来源于stack exchange，提问作者TegaBusola