Apply aggr only to a column of the sliding

Question

I would like to transform this SQL in pandas:

pd.read_sql_query("""
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
""",conn)

The fact is that it is group by for something different from the first two columns, that are strings, and if I slide and apply the count() to the columns of the select it does not work

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

In the photo there is the heads of the tables and the expected outcome (from SQL)