Python不使用Pandas按日期统计CSV中各城市调研人数总和

问题背景 #

有如下字符串形式的CSV数据：

csv_input = """
            quiz_date,location,size
            2022-01-01,london_uk,134
            2022-01-02,edingburgh_uk,65
            2022-01-01,madrid_es,124
            2022-01-02,london_uk,125
            2022-01-01,edinburgh_uk,89
            2022-01-02,madric_es,143
            2022-01-02,london_uk,352
            2022-01-01,edinburgh_uk,125
            2022-01-01,madrid_es,431
            2022-01-02,london_uk,151"""

需要按日期统计各城市调研总人数，期望输出格式：

Date.         City.       Pop-Surveyed
2022-01-01.   London.     134
2022-01-01.   Edinburgh.  214
2022-01-01.   Madrid.     555
2022-01-02.   London.     628
2022-01-02.   Edinburgh.  65
2022-01-02.   Madrid.     143

原尝试使用defaultdict但出现KeyError: 'london'，代码如下：

from collections import defaultdict

survery_data = csv_input.split()[1:]
survery_data = [survey.split(',') for survey in survery_data]

survey_sum = defaultdict(dict)

for survey in survery_data:
    date = survey[0]
    city = survey[1].split("_")[0]
    quantity = survey[-1]

    survey_sum[date][city] += quantity

print(survey_sum)

错误原因 #

1. 内层字典无默认值：survey_sum = defaultdict(dict)仅外层是默认字典，内层是普通字典。第一次访问survey_sum[date][city]时，内层字典中不存在该city键，触发KeyError。
2. 字符串未转整数：quantity是字符串类型，直接+=会执行字符串拼接而非数值求和，导致结果错误。
3. 城市名拼写错误：原CSV中存在edingburgh_uk、madric_es这类拼写错误，需统一为正确的城市名才能匹配期望输出。

修正方案 #

将内层字典也设置为defaultdict(int)，确保首次访问城市键时自动初始化为0；同时将quantity转为整数，最后格式化输出对齐列：

from collections import defaultdict

csv_input = """
            quiz_date,location,size
            2022-01-01,london_uk,134
            2022-01-02,edingburgh_uk,65
            2022-01-01,madrid_es,124
            2022-01-02,london_uk,125
            2022-01-01,edinburgh_uk,89
            2022-01-02,madric_es,143
            2022-01-02,london_uk,352
            2022-01-01,edinburgh_uk,125
            2022-01-01,madrid_es,431
            2022-01-02,london_uk,151"""

# 处理CSV数据，跳过表头并去除多余空格
survey_data = csv_input.strip().split('\n')[1:]
survey_data = [line.strip().split(',') for line in survey_data]

# 构建嵌套的defaultdict：外层按日期分组，内层按城市统计人数
survey_sum = defaultdict(lambda: defaultdict(int))

for survey in survey_data:
    date = survey[0]
    # 提取城市名并修正拼写错误
    city_raw = survey[1].split("_")[0]
    city = city_raw.replace('edingburgh', 'edinburgh').replace('madric', 'madrid')
    # 将人数转为整数
    quantity = int(survey[-1])
    
    survey_sum[date][city] += quantity

# 格式化输出结果，匹配期望格式
print(f"{'Date.':<12} {'City.':<12} {'Pop-Surveyed'}")
for date in sorted(survey_sum.keys()):
    # 按城市名排序输出
    for city in sorted(survey_sum[date].keys()):
        total = survey_sum[date][city]
        # 格式化日期、城市名（首字母大写）并添加标点
        print(f"{date}.{'':<8} {city.title()}.{'':<7} {total}")

输出结果 #

Date.         City.       Pop-Surveyed
2022-01-01.   Edinburgh.  214
2022-01-01.   London.     134
2022-01-01.   Madrid.     555
2022-01-02.   Edinburgh.  65
2022-01-02.   London.     628
2022-01-02.   Madrid.     143

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