<jupyter_start><jupyter_text>Rural/Urban scoring for councilsThere are measures of Rural/Urban in England/Wales and Scotland which are slightly incomparable. The problem is for the single tier league band all these authorities exist in the table, and so we need some kind of urban/rural measure that can cut across this.We have previously combined these for the LSOA level into three bands. These are hard to exactly scale up to local authority level. ONS did this for for E/W but the Scottish notes give up and just say you should express it as the proportion of different bands in different local authorities. This notebook is an experiment in producing that from our previous LSOA data, and then seeing if this data easily clusters in a way that can be nicely labelled.<jupyter_code># bring in the uk-wide composite ruc info#
ruc = pd.read_csv(Path("data", "source", "composite_ruc.csv"))
ruc = ruc[["lsoa", "ukruc-3", "pop"]].set_index("lsoa")
# get this into the form of a sheet where the percentage of each lower-tier la is urban, rural, and highly rural is a row 

df = pd.read_csv(Path("data", "source", "lsoa_la_2021.csv")).set_index("lsoa")
df = ruc.join(df, how="outer")
df["ukruc-3"] = df["ukruc-3"].map({0:"Urban", 1: "Rural", 2: "Highly rural"})
pt = df.pivot_table("pop", index="local-authority-code", columns=["ukruc-3"], aggfunc="sum").fillna(0)
pt = pt.common.row_percentages()
# now look to see if obvious clusters

c = pt.space.cluster()
c.find_k()<jupyter_output><empty_output><jupyter_text>Not *great* clustering behaviour here (to be expected, more of a spectrum). Instead lets work backwards, we want a fairly basic breakdown, so lets pick 4.<jupyter_code>c.set_k(4).plot()<jupyter_output><empty_output><jupyter_text>These seem ok grouping (although obviously at the margins the limit is not hard).<jupyter_code>c = c.set_k(4).add_labels({1:"Urban", 3:"Sparse rural", 4: "Sub urban", 2: "Rural"})
c.stats()<jupyter_output><empty_output><jupyter_text>Little bit of a problem here in that the Urban and Sub-urban categories are quite large. How much does that matter if we cross it with our leagues?<jupyter_code>pt["cluster"] = c.label_array(include_short=False)

ldf = la.get_la_with_leagues().set_index("local-authority-code")
ldf = ldf.join(pt, how="outer")

ldf.reset_index().pivot_table("local-authority-code", index="league-group", columns="cluster", aggfunc="count").fillna(0)<jupyter_output><empty_output><jupyter_text>Assuming we ignore it for Northern Ireland, The Urban single tier is quite large. But also probably quite similar, so seems ok? No need to artifically split big numbers when they're naturalish.Final question is how does this match up against the official measures?  Comparing to E/W RUCThe Scotland one isn't avaliable as a one off value.<jupyter_code>ewruc = pd.read_csv(Path("data", "source", "RUC_LAD_2011_EN_LU.csv"))

ewruc = ewruc.la.create_code_column(from_type="gss", source_col="LAD18CD", set_index=True)

combo = ewruc[["Broad RUC11", "RUC11"]].join(pt[["cluster"]])

pd.crosstab(index=combo["Broad RUC11"], columns=combo["cluster"]).common.row_percentages().style.format(percentage_1dp)<jupyter_output><empty_output><jupyter_text>This seems pretty good. Going to adapt the category names slightly.<jupyter_code>c = c.set_k(4).add_labels({1:"Urban", 3:"Sparse and rural", 4: "Urban with rural areas", 2: "Rural"})
c.stats()
pt["ruc_cluster"] = c.label_array(include_short=False)
final = pt.reset_index()[["local-authority-code",  "ruc_cluster"]]
final.to_csv(Path("data", "outputs", "ruc_cluster.csv"), index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12482662968099861, 'fraction_numerical': 0.010540915395284327, 'mean_word_length': 6.344195519348268, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '45856', 'license': 'MIT', 'n_tokens_mistral': 1229, 'n_tokens_neox': 1174, 'n_words': 380, 'path': 'notebooks/ruc.ipynb', 'repo_name': 'mysociety/climate_league_filter_datasets', 'size': '3605'}