PHP逐行读取CSV并按SubAccountID和CallType统计Amount总和

需求说明 #

读取CSV文件（跳过表头），按SubAccountID和CallType的组合分组，动态累加每组的Amount总和，且无法提前知晓所有SubAccountID的值。

示例CSV数据 #

AccountNumber,CallStartDateTime,CallType,Duration,PerMinRate,Amount,SubAccountID
5004017,9/24/2023 15:29,WVO,6,0.003,0.0003,18499
5004017,9/24/2023 21:37,WVO,54,0.003,0.0027,18499
5004017,9/24/2023 21:38,INTERSTATE,60,0.00211,0.00211,18499
5004017,9/25/2023 11:30,WVO,6,0.003,0.0003,18499
5004017,9/25/2023 13:34,WVO,6,0.003,0.0003,18499
5004017,9/25/2023 13:43,INTERSTATE,54,0.001,0.0009,19203
5004017,9/25/2023 13:52,WVO,6,0.003,0.0003,18499
5004017,9/25/2023 13:43,WVO,54,0.003,0.0027,19203
5004017,9/25/2023 14:00,WVO,48,0.003,0.0024,19203

核心解决思路 #

不要动态创建变量，而是用字典存储分组统计结果。字典支持动态添加键值对，完美适配未知的SubAccountID和CallType组合，以下是两种实用实现方式：

方式1：元组作为字典键（简洁高效）

将(SubAccountID, CallType)作为唯一键，直接映射对应组的Amount总和：

import csv

# 初始化统计字典
total_amounts = {}

with open('your_call_data.csv', 'r') as csv_file:
    # DictReader自动跳过表头，按列名读取数据
    reader = csv.DictReader(csv_file)
    
    for row in reader:
        sub_acc = row['SubAccountID']
        call_type = row['CallType']
        amount = float(row['Amount'])
        
        # 构造唯一分组键
        group_key = (sub_acc, call_type)
        # 累加：键存在则加当前amount，不存在则初始化为当前amount
        total_amounts[group_key] = total_amounts.get(group_key, 0.0) + amount

# 输出统计结果
for (sub_acc, call_type), total in total_amounts.items():
    print(f"SubAccountID: {sub_acc}, CallType: {call_type}, 总Amount: {total:.4f}")

方式2：嵌套字典（结构更直观）

外层字典键为SubAccountID，内层字典键为CallType，值为对应组的总和：

import csv

total_amounts = {}

with open('your_call_data.csv', 'r') as csv_file:
    reader = csv.DictReader(csv_file)
    
    for row in reader:
        sub_acc = row['SubAccountID']
        call_type = row['CallType']
        amount = float(row['Amount'])
        
        # 初始化当前SubAccountID的内层字典
        if sub_acc not in total_amounts:
            total_amounts[sub_acc] = {}
        # 累加当前CallType的Amount
        total_amounts[sub_acc][call_type] = total_amounts[sub_acc].get(call_type, 0.0) + amount

# 输出统计结果
for sub_acc, call_types in total_amounts.items():
    print(f"=== SubAccountID: {sub_acc} ===")
    for call_type, total in call_types.items():
        print(f"  CallType: {call_type}, 总Amount: {total:.4f}")

输出结果示例 #

两种方式都会得到正确统计结果，例如：

• SubAccountID: 18499, CallType: WVO, 总Amount: 0.0036
• SubAccountID: 18499, CallType: INTERSTATE, 总Amount: 0.0021
• SubAccountID: 19203, CallType: INTERSTATE, 总Amount: 0.0009
• SubAccountID: 19203, CallType: WVO, 总Amount: 0.0051

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