Water Collection System in Mexico City - 2022

Jesus LM
Economist & Data Scientist

Mar 28, 2024

Summary

Rainwater harvesting is a sustainable and effective way to manage water resources in Mexico.
By promoting water conservation and increasing water security, these systems offer a path towards a more water-resilient future.

What is SCALL?

Rainwater harvesting systems are a prominent water collection method in Mexico, especially in areas facing water scarcity. These systems, called “Sistemas de Captación de Agua de Lluvia” (SCALL) offer several benefits:

Reduced strain on aquifers: By collecting rainwater, these systems decrease reliance on groundwater sources, which are often overexploited.
Improved water security: Especially in peri-urban areas, rainwater harvesting provides a more dependable water source, particularly during dry spells.
Cost-effective solution: Rainwater harvesting can significantly reduce water bills for households.
Empowering communities: Programs like “Agua a tu Casa” in Mexico City train women to install and maintain these systems, fostering economic opportunities.

Environment setting

Import libraries

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import duckdb as db
import os
from dotenv import load_dotenv
load_dotenv('.env')
import folium
from folium.plugins import Fullscreen
plt.style.use('ggplot')

Load dataset

# database connection
token = os.getenv('MD_TOKEN')
conn = db.connect(f"md:?motherduck_token={token}")
# create dataframe
df = conn.sql('select * from projects.water_collection').df()
conn.close()

Dataset preview

Dataset extract

df.columns = ['file', 'village', 'scall', 'territory', 'facility_date', 'capacity', 'alcaldia',
              'suburb', 'latitude','longitude']
df.head(3)

	file	village	scall	territory	facility_date	capacity	alcaldia	suburb	latitude	longitude
0	1-MEMEMA-MIA-22	SAN ANTONIO TECOMITL	BARRIO XALTIPAC	NO APLICA	2022-05-02	2500	MILPA ALTA	SAN ANTONIO TECOMITL (PBLO)	19.219028	-98.991283
1	2-RUCOAL-MIA-22	SAN ANTONIO TECOMITL	BARRIO TENANTITLA	NO APLICA	2022-05-07	2500	MILPA ALTA	SAN ANTONIO TECOMITL (PBLO)	19.219880	-99.002227
2	3-GAALRA-MIA-22	SAN ANTONIO TECOMITL	BARRIO XALTIPAC	NO APLICA	2022-07-21	2500	MILPA ALTA	SAN ANTONIO TECOMITL (PBLO)	19.218343	-98.997507

Descriptive Statitistics

Summary of basic stats

df.describe(include='number')

	capacity	latitude	longitude
count	16971.000000	16971.000000	16971.000000
mean	2200.218019	19.244556	-99.057861
std	574.321315	0.169027	0.069845
min	1100.000000	0.000000	-99.245452
25%	2500.000000	19.192449	-99.089082
50%	2500.000000	19.218752	-99.029098
75%	2500.000000	19.274890	-99.003864
max	2500.000000	19.578064	-98.961553

Installed Capacity by Alcaldia

We will group capacity by alcaldia

alcaldias = (
    df.groupby('alcaldia')['capacity']
        .agg('sum')
        .to_frame()
        .reset_index()
        .sort_values('capacity')
)

Installed Capacity by Village

villages = (
    df.groupby('village')['capacity']
        .agg('sum')
        .to_frame()
        .reset_index()
        .sort_values('capacity')
)

Installed Capacity by Month

months = (
    df.groupby(pd.Grouper(key='facility_date', freq="ME"))
    ['capacity'].sum()
                .to_frame()
                .reset_index()
)

Villages with Installations

zones = (
    df.groupby('village')[['capacity','latitude','longitude']]
        .agg({'capacity':'sum','latitude':'mean','longitude':'mean'})
        .reset_index(drop=False)
)

<folium.plugins.fullscreen.Fullscreen at 0x7ccc88141670>

map

Make this Notebook Trusted to load map: File -> Trust Notebook

References

Resources

Contact

Jesus LM
Economist & Data Scientist

Linkedin | Medium | Twitter