Exploring Colombian municipal water data

Data: data/water_municipal.csv (one row = municipality x zone x year, 2010-2023). IRCA here is utility-supplied (“prestadores”) IRCA from INS open data, not INS’s national total.

import pandas as pd
df = pd.read_csv("data/water_municipal.csv", dtype={"divipola": str})
print(f"{df.shape[0]:,} rows x {df.shape[1]} columns")
df.dtypes.to_frame("dtype").T
31,416 rows x 29 columns
divipola municipio dpto_code departamento region entity_type department_capital zone year irca ... ipm mdm idf idf_new sgp_water_pc rainfall_mm infant_mortality saber11_mean saber11_n dropout_rate
dtype str str int64 str str str int64 str int64 float64 ... float64 float64 float64 float64 float64 float64 float64 float64 float64 float64

1 rows × 29 columns

Missing values (%) per column, by zone

miss = df.drop(columns="zone").isna().groupby(df["zone"]).mean().mul(100).round(1).T
miss
zone rural urban
divipola 0.0 0.0
municipio 0.0 0.0
dpto_code 0.0 0.0
departamento 0.0 0.0
region 0.0 0.0
entity_type 0.0 0.0
department_capital 0.0 0.0
year 0.0 0.0
irca 52.9 14.5
irca_missing 0.0 0.0
water_coverage 10.5 9.7
sewer_coverage 10.5 9.7
water_coverage_total 6.6 6.6
sewer_coverage_total 6.7 6.7
population 0.1 0.1
rural_share 0.1 0.1
rurality_category 1.9 1.9
altitude_m 0.0 0.0
ipm 1.9 1.9
mdm 43.9 43.9
idf 23.0 23.0
idf_new 72.0 72.0
sgp_water_pc 1.8 1.8
rainfall_mm 0.1 0.1
infant_mortality 14.9 14.9
saber11_mean 58.9 52.2
saber11_n 50.0 50.0
dropout_rate 8.1 8.1

IRCA bands (Resolución 2115 de 2007, art. 15), by zone

Counts of rows with a utility-supplied IRCA value.

bins = [-0.001, 5, 14, 35, 80, 100]
labels = ["no risk (0-5)", "low (5.1-14)", "medium (14.1-35)", "high (35.1-80)", "inviable (80.1-100)"]
d = df.dropna(subset=["irca"]).copy()
d["band"] = pd.cut(d["irca"], bins=bins, labels=labels)
pd.crosstab(d["band"], d["zone"], margins=True)
zone rural urban All
band
no risk (0-5) 1510 6594 8104
low (5.1-14) 842 2801 3643
medium (14.1-35) 1273 2372 3645
high (35.1-80) 3143 1469 4612
inviable (80.1-100) 630 197 827
All 7398 13433 20831

Median IRCA (utility-supplied) by year and zone

med = df.pivot_table(index="year", columns="zone", values="irca", aggfunc="median").round(1)
med
zone rural urban
year
2010 49.4 10.8
2011 50.3 14.7
2012 49.7 10.9
2013 49.1 7.1
2014 48.0 6.4
2015 47.2 5.3
2016 34.4 4.6
2017 31.0 4.0
2018 39.4 4.8
2019 36.5 2.8
2020 9.9 2.4
2021 11.3 3.3
2022 13.4 3.6
2023 13.4 3.0 
ax = med.plot(marker="o", title="Median IRCA (utility-supplied, INS open data) by year and zone")
ax.set_ylabel("median IRCA (0-100)")