What is Canada's Largest Transitless Municipality?

Happy 2s Day.

I was chatting with my brother the other day, and he opined that I may live in Canada’s largest municipality that doesn’t have a municipal bus transit system. Of course I was interested to find out which is the biggest Canadian city without buses, so here we go.

I was very pleased to find that pandas will read an html page and output a list of dataframes that it was able to parse from tables. Pair this with wikipedia keeping a list of Canadian municipalities with public transport and we have a good start:

import pandas as pd
import requests

transit_url = "https://en.wikipedia.org/wiki/Public_transport_in_Canada"
r = requests.get(transit_url)
df_list = pd.read_html(r.text)
transit_municipalities = df_list[0]

Next, use the same technique to find a list of municipalities in each province.

provinces = ['British Columbia', 'Alberta', 'Saskatchewan', 'Manitoba', 'Ontario', 'Quebec',
            'New Brunswick', 'Newfoundland and Labrador', 'Nova Scotia', 'Prince Edward Island']

p_dfs = {}
def p_url(p):
    return f"https://en.wikipedia.org/wiki/List_of_municipalities_in_{p.replace(' ', '_')}"

for province in provinces:
    print(f"getting municipalities for {province}")
    r = requests.get(p_url(province))
    df_list = pd.read_html(r.text)

    if province == 'Ontario' or province == 'Manitoba': # different html for these two.
        p_dfs[province] = df_list[1]
    else:
        p_dfs[province] = df_list[0]
    print(f"{province} has {len(p_dfs[province])} municipalities")

Clean that data up a bit, and work around the annoyingness of MultiIndex:

canada_dfs = []

def find_series_by_column(df, look_for):
    for x in df.columns:
        if isinstance(x, tuple):
            if look_for in x[1]:
                pop_column = x[0]
                return df[x[0]][x[1]]
                break


for province, df in p_dfs.items():
    prov_series = pd.Series([province for x in range(len(df.index))])
    pop_series = find_series_by_column(df, 'Population')
    name_series = find_series_by_column(df, 'Name')
    canada_dfs.append(pd.DataFrame({"Province": prov_series, "Name": name_series, "Population": pop_series}))
canada_df = pd.concat(canada_dfs)

Gives data like this:

Do an anti-join to find the muncipalities without transit:

with_transit_df = canada_df.merge(trans_df, on=['Name', 'Province'], indicator=True, how='left')
without_transit_df = with_transit_df.loc[with_transit_df._merge == 'left_only', :].drop(columns='_merge')

A little data cleanup:

import re
without_transit_df['Population2'] = without_transit_df['Population'].fillna("0").map(lambda x:
        str(re.sub("\[\d+\]", "", str(x)))
     )
without_transit_df['Population2'] = without_transit_df['Population2'].map(
        lambda x: str(x).replace(".0", "").replace("nan", "0").replace(",", "")
    ).replace('', 0).astype('int32')

And sort:

This really isn’t a satisfactory answer for me to be honest. Most of the entries on this list were either missed in the join (e.g. Metro Vancouver not matching Vancouver, similar issue for York Region), or the municipalities listed are part of a larger municipality that is served by public transit (e.g. Markham). Fixing those issues would be possible, but a lot of work. It was time for the paretto principle. So I looked through the list. This isn’t definitive but I think that Kawartha Lakes, Ontario, (population ~75000), is the largest municipality in Canada without a municipal bus service. They do have a municipal service with these little half-bus, half-van things though, so maybe that counts.