请求合并同一Agent Location下的多组Agent Code数据

原始数据表格：

合并相同Agent Location后的结果：

实现方法示例 #

1. Excel透视表操作

• 选中有效数据区域（排除底部的Grand Total行）
• 插入透视表，将Agent Location拖入行区域，Agent Code拖入值区域并设置为「计数」，Zone拖入行区域辅助展示
• 调整透视表布局即可得到合并统计结果

2. Python Pandas代码实现

import pandas as pd

# 构造原始数据
data = {
    "Agent Code": [7795, 8080, 8069, 8065, 8073, 8092, 8083, 8218, 8064, 8077, 8062, 8070, 8213],
    "Zone": ["East", "South", "South", "North", "West", "East", "South", "South", "West", "East", "South", "South", "South"],
    "Agent Location": ["Kolkata SBM", "Chennai", "Bangalore", "Delhi", "Mumbai", "Noida", "Hyderabad", "Bangalore", "Bihar", "Noida", "Hyderabad", "Bangalore", "Bangalore"]
}
df = pd.DataFrame(data)

# 分组统计
merged_df = df.groupby(["Agent Location", "Zone"], as_index=False)["Agent Code"].count()
merged_df.rename(columns={"Agent Code": "Agent Code 累计数量"}, inplace=True)

print(merged_df)

3. SQL查询实现

假设数据存储在agents表中，执行以下语句：

SELECT 
    `Agent Location`,
    Zone,
    COUNT(`Agent Code`) AS `Agent Code 累计数量`
FROM agents
WHERE `Agent Code` != 'Grand Total'
GROUP BY `Agent Location`, Zone
ORDER BY `Agent Location`;

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