SQL转Pandas遇分组聚合问题：非选中列分组导致统计失效

需求背景 #

需要将这条统计航线数量的SQL转换成等价的Pandas代码：

select a1.city 'Source city', 
    a2.city 'Destination city', 
    count(*) as 'Number of routes'
from routes r
    join airports a1
        on r.source_id = a1.id
    join airports a2
        on r.dest_id = a2.id
group by r.source,r.dest
order by 3 DESC
limit 5

核心问题是SQL分组字段是r.source和r.dest，但最终要输出对应的城市名，直接对城市名列做count无法得到正确结果。用户自行编写的代码如下：

df = pd.merge(routes, airports, left_on="source_id", right_on="id")
df1 = pd.merge(df, airports,  left_on="dest_id", right_on="id")
df1 = df1.groupby(["source", "dest"])[["city_x", "city_y"]].count().sort_values("city_x", ascending = False)
df1

正确实现方案 #

这里提供两种等价的Pandas写法，完全匹配SQL的逻辑：

方法一：先合并表再分组统计

# 合并航线表与机场表，获取出发城市，指定后缀避免列名冲突
merged = pd.merge(routes, airports, left_on="source_id", right_on="id", suffixes=("", "_src"))
# 再次合并获取到达城市
merged = pd.merge(merged, airports, left_on="dest_id", right_on="id", suffixes=("_src", "_dest"))

# 按source和dest分组，用size()统计总条数（对应SQL的count(*)），城市名用first取（同一分组城市名唯一）
result = merged.groupby(["source", "dest"], as_index=False).agg(
    **{
        "Source city": ("city", "first"),
        "Destination city": ("city_dest", "first"),
        "Number of routes": ("source", "size")
    }
).sort_values("Number of routes", ascending=False).head(5)

方法二：先分组统计再关联城市名

# 先按source和dest分组统计航线数量
route_counts = routes.groupby(["source", "dest"], as_index=False).size().rename(columns={"size": "Number of routes"})

# 关联出发城市（需确保routes表有source_id字段关联airports的id）
route_counts = pd.merge(route_counts, airports, left_on="source_id", right_on="id").rename(columns={"city": "Source city"})
# 关联到达城市
route_counts = pd.merge(route_counts, airports, left_on="dest_id", right_on="id").rename(columns={"city": "Destination city"})

# 排序取前5，保留需要的列
result = route_counts[["Source city", "Destination city", "Number of routes"]].sort_values("Number of routes", ascending=False).head(5)

原代码问题说明 #

1. 统计逻辑不符：用count()统计城市列是统计非空值数量，而SQL里的count(*)是统计分组总行数，用size()更准确。
2. 列名混乱：两次合并airports表后，city_x、city_y这类列名易混淆，建议合并时指定后缀避免歧义。
3. 索引处理问题：原代码分组后会把source和dest设为索引，用as_index=False可以保持它们为普通列，更贴近SQL的输出结构。

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