Módulo 1 — Diagnóstico operacional (Rappi)¶

Dataset: data/rappi_delivery_case_data.xlsx en la raíz del proyecto — 30 días × 24 h × 14 zonas. La primera celda localiza automáticamente esa ruta (sirve en VS Code con workspace en caso_tecnico o al abrir solo la carpeta notebooks/).

Métrica: $\text{ratio} = \text{ORDERS}/\text{CONNECTED_RT}$.
Saturación: ratio > 1.8 · Sobre-oferta: ratio < 0.5 · Saludable (doc): 0.9–1.2.

In [9]:
%matplotlib inline
from pathlib import Path
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import statsmodels.formula.api as smf
from scipy.stats import spearmanr

# VS Code / Jupyter: el cwd puede ser la raíz del repo o esta carpeta; resolvemos la raíz del proyecto.
def _project_root() -> Path:
    here = Path.cwd().resolve()
    for p in (here, here.parent, here.parent.parent):
        data = p / "data" / "rappi_delivery_case_data.xlsx"
        if data.is_file():
            return p
    raise FileNotFoundError(
        "No se encuentra data/rappi_delivery_case_data.xlsx. "
        "Abre la carpeta caso_tecnico como workspace o ejecuta el notebook desde modulo1_diagnostico/notebooks/."
    )

PROJECT_ROOT = _project_root()
sns.set_theme(style="whitegrid", context="talk")
FIG = PROJECT_ROOT / "modulo1_diagnostico" / "figures"
FIG.mkdir(parents=True, exist_ok=True)

DATA = PROJECT_ROOT / "data" / "rappi_delivery_case_data.xlsx"
raw = pd.read_excel(DATA, sheet_name="RAW_DATA")
zone_info = pd.read_excel(DATA, sheet_name="ZONE_INFO")
raw.head()
Out[9]:
COUNTRY DATE HOUR CITY ZONE CONNECTED_RT ORDERS EARNINGS PRECIPITATION_MM
0 Mexico 2024-03-01 0 Monterrey Centro 6 2 54.4 0.0
1 Mexico 2024-03-01 0 Monterrey Mitras Centro 5 1 52.4 0.0
2 Mexico 2024-03-01 0 Monterrey Apodaca Centro 4 1 59.1 0.0
3 Mexico 2024-03-01 0 Monterrey Escobedo 3 1 53.3 0.0
4 Mexico 2024-03-01 0 Monterrey Carretera Nacional 2 0 55.0 0.0
In [10]:
raw["ratio"] = raw["ORDERS"] / raw["CONNECTED_RT"].replace(0, np.nan)

def clasificar(r):
    if pd.isna(r):
        return np.nan
    if r > 1.8:
        return "saturacion"
    if r < 0.5:
        return "sobre_oferta"
    if 0.9 <= r <= 1.2:
        return "saludable"
    return "intermedio"

raw["clasificacion"] = raw["ratio"].apply(clasificar)
raw[["ZONE", "HOUR", "ratio", "clasificacion", "PRECIPITATION_MM", "EARNINGS"]].describe(include="all")
Out[10]:
ZONE HOUR ratio clasificacion PRECIPITATION_MM EARNINGS
count 10080 10080.00000 10080.000000 10080 10080.000000 10080.000000
unique 14 NaN NaN 4 NaN NaN
top Centro NaN NaN intermedio NaN NaN
freq 720 NaN NaN 4807 NaN NaN
mean NaN 11.50000 0.817924 NaN 0.246667 57.696081
std NaN 6.92253 0.550939 NaN 1.265046 8.292825
min NaN 0.00000 0.000000 NaN 0.000000 49.000000
25% NaN 5.75000 0.500000 NaN 0.000000 52.400000
50% NaN 11.50000 0.800000 NaN 0.000000 55.600000
75% NaN 17.25000 1.096774 NaN 0.000000 58.700000
max NaN 23.00000 5.666667 NaN 10.300000 97.000000

P1 — Horas y zonas con saturación crítica¶

In [11]:
sat = raw[raw["clasificacion"] == "saturacion"]
print(f"Horas en saturación: {len(sat)} / {len(raw)} ({100*len(sat)/len(raw):.2f}%)")

p1h = sat.groupby("HOUR").size().reindex(range(24), fill_value=0)
p1z = sat.groupby("ZONE").size().sort_values(ascending=False)

fig, ax = plt.subplots(figsize=(10, 4))
p1h.plot(kind="bar", ax=ax, color="#c0392b")
ax.set_xlabel("Hora (Monterrey)")
ax.set_ylabel("Conteo horas saturadas")
ax.set_title("P1 — Horas con mayor frecuencia de saturación (conteo de filas)")
fig.tight_layout()
fig.savefig(FIG / "p1_horas_saturacion.png", dpi=150)
plt.show()

fig, ax = plt.subplots(figsize=(10, 4.5))
p1z.head(14).plot(kind="barh", ax=ax, color="#922b21")
ax.set_title("P1 — Zonas con más observaciones en saturación")
fig.tight_layout()
fig.savefig(FIG / "p1_zonas_saturacion.png", dpi=150)
plt.show()

print("Top horas:", p1h.nlargest(5).to_string())
print("Top zonas:\n", p1z.head(8))

Horas en saturación: 513 / 10080 (5.09%)
No description has been provided for this image
No description has been provided for this image 
Top horas: HOUR
14    117
13    109
12    103
21     68
19     52
Top zonas:
 ZONE
San Nicolás           45
Santiago              45
MTY_Guadalupe         44
Carretera Nacional    43
Mitras Centro         40
Independencia         38
Santa Catarina        38
Apodaca Centro        37
dtype: int64

P2 — Variable externa (precipitación)¶

Correlación simple y regresión con controles de hora y zona para reducir confusión por picos de demanda.

In [12]:
print(raw[["PRECIPITATION_MM", "ratio", "EARNINGS"]].corr(method="pearson"))

m = smf.ols("ratio ~ PRECIPITATION_MM + C(HOUR) + C(ZONE)", data=raw).fit()
print(m.params["PRECIPITATION_MM"], m.pvalues["PRECIPITATION_MM"])
print(m.summary2().tables[0])

rain = raw["PRECIPITATION_MM"] > 0.5
print("P(saturación | precip>0.5):", raw.loc[rain, "clasificacion"].eq("saturacion").mean())
print("P(saturación | precip<=0.5):", raw.loc[~rain, "clasificacion"].eq("saturacion").mean())

                  PRECIPITATION_MM     ratio  EARNINGS
PRECIPITATION_MM          1.000000  0.318902  0.226029
ratio                     0.318902  1.000000  0.099885
EARNINGS                  0.226029  0.099885  1.000000
0.08060564022168862 4.254775042697023e-240
                     0                 1                    2          3
0               Model:               OLS      Adj. R-squared:      0.716
1  Dependent Variable:             ratio                 AIC:  3923.7092
2                Date:  2026-03-20 15:41                 BIC:  4198.0049
3    No. Observations:             10080      Log-Likelihood:    -1923.9
4            Df Model:                37         F-statistic:      689.1
5        Df Residuals:             10042  Prob (F-statistic):       0.00
6           R-squared:             0.717               Scale:   0.086088
P(saturación | precip>0.5): 0.3081632653061224
P(saturación | precip<=0.5): 0.037747653806047964

P3 — Heterogeneidad por zona (sensibilidad a lluvia)¶

In [13]:
slopes = []
for z in sorted(raw["ZONE"].unique()):
    sub = raw[raw["ZONE"] == z]
    mz = smf.ols("ratio ~ PRECIPITATION_MM + C(HOUR)", data=sub).fit()
    slopes.append((z, float(mz.params["PRECIPITATION_MM"])))

slopes = sorted(slopes, key=lambda x: -abs(x[1]))
coef_df = pd.DataFrame(slopes, columns=["ZONE", "coef_precip_mm"])
print(coef_df.head(8))

fig, ax = plt.subplots(figsize=(9, 5))
coef_df.set_index("ZONE")["coef_precip_mm"].plot(kind="bar", ax=ax, color="#2471a3")
ax.set_title("P3 — Coeficiente marginal ratio~precip (control hora), por zona")
ax.set_ylabel("Δ ratio por +1 mm/hr precip")
fig.tight_layout()
fig.savefig(FIG / "p3_sensibilidad_zona.png", dpi=150)
plt.show()

                  ZONE  coef_precip_mm
0             Santiago        0.191517
1   Carretera Nacional        0.133912
2       Santa Catarina        0.102633
3  MTY_Apodaca_Huinalá        0.084674
4        Independencia        0.074754
5            San Pedro        0.067027
6             Escobedo        0.066836
7       Apodaca Centro        0.065833
No description has been provided for this image

P4 — Calibración de earnings y posible gasto ineficiente¶

In [14]:
daily = raw.groupby("DATE").agg(
    earn_mean=("EARNINGS", "mean"),
    sat_frac=("clasificacion", lambda s: (s == "saturacion").mean()),
    ratio_mean=("ratio", "mean"),
).reset_index()

daily = daily.sort_values("sat_frac", ascending=False)
print("Días con mayor fracción de horas saturadas (top 10):")
print(daily.head(10))

p75 = daily["earn_mean"].quantile(0.75)
stress = daily["sat_frac"] >= daily["sat_frac"].quantile(0.75)
ineff = daily[(daily["earn_mean"] >= p75) & stress]
print("\nDías candidatos a desalineación earn vs estrés (p75 earn y p75 sat_frac):")
print(ineff[["DATE", "earn_mean", "sat_frac"]])

fig, ax = plt.subplots(figsize=(10, 4))
ax.scatter(daily["earn_mean"], daily["sat_frac"], alpha=0.7, c="#117a65")
ax.set_xlabel("Earnings medio (MXN)")
ax.set_ylabel("Fracción horas saturadas")
ax.set_title("P4 — Earnings diario vs estrés (saturación)")
fig.tight_layout()
fig.savefig(FIG / "p4_earnings_vs_estres.png", dpi=150)
plt.show()

Días con mayor fracción de horas saturadas (top 10):
          DATE  earn_mean  sat_frac  ratio_mean
27  2024-03-28  55.863393  0.166667    1.102925
7   2024-03-08  56.218155  0.139881    0.975630
24  2024-03-25  56.071131  0.130952    0.896001
4   2024-03-05  57.172917  0.095238    0.979752
26  2024-03-27  56.660714  0.065476    0.809092
13  2024-03-14  60.444940  0.050595    0.746482
15  2024-03-16  57.166369  0.050595    0.800138
0   2024-03-01  56.587500  0.047619    0.830797
17  2024-03-18  56.638393  0.044643    0.814346
25  2024-03-26  56.550595  0.044643    0.816600

Días candidatos a desalineación earn vs estrés (p75 earn y p75 sat_frac):
          DATE  earn_mean  sat_frac
13  2024-03-14   60.44494  0.050595
No description has been provided for this image

P5 — Relación earnings–saturación con interacción (lluvia)¶

In [15]:
print("Spearman earnings vs ratio (global):", spearmanr(raw["EARNINGS"], raw["ratio"]))
mask = raw["PRECIPITATION_MM"] > 0
print("Con precip>0:", spearmanr(raw.loc[mask, "EARNINGS"], raw.loc[mask, "ratio"]))

m5 = smf.ols("ratio ~ EARNINGS * PRECIPITATION_MM + C(HOUR) + C(ZONE)", data=raw).fit()
print(m5.params["EARNINGS"], m5.params["EARNINGS:PRECIPITATION_MM"])

Spearman earnings vs ratio (global): SignificanceResult(statistic=np.float64(0.07613533003613025), pvalue=np.float64(1.9468694077848612e-14))
Con precip>0: SignificanceResult(statistic=np.float64(-0.2509179807834915), pvalue=np.float64(2.6791141022556686e-10))
-0.024943005163521048 -0.0018947792121322992

Análisis de sensibilidad (1.2 → 1.8 en ratio)¶

Con el modelo marginal por zona ratio ~ precip + C(HOUR), estimamos cuántos mm/hr adicionales (orden de magnitud) llevarían el ratio desde el techo saludable (1.2) hasta saturación (1.8), i.e. $\Delta\rho=0.6 \approx \beta_z \cdot \Delta precip$.

In [16]:
coefs = []
for z in sorted(raw["ZONE"].unique()):
    sub = raw[raw["ZONE"] == z]
    mz = smf.ols("ratio ~ PRECIPITATION_MM + C(HOUR)", data=sub).fit()
    b = float(mz.params["PRECIPITATION_MM"])
    delta_mm = (0.6 / b) if b > 1e-8 else np.nan
    coefs.append({"ZONE": z, "beta_precip": b, "mm_for_0_6_ratio": delta_mm})

sens_df = pd.DataFrame(coefs).sort_values("mm_for_0_6_ratio")
display(sens_df.head(14))

fig, ax = plt.subplots(figsize=(9, 4.5))
ax.barh(sens_df["ZONE"], sens_df["mm_for_0_6_ratio"], color="#1a5276")
ax.set_xlabel("mm/hr estimados para Δratio 0.6 (1.2→1.8, aprox. lineal local)")
ax.set_title("Sensibilidad operativa a precipitación por zona")
fig.tight_layout()
fig.savefig(FIG / "p_sensitivity_mm_12_to_18.png", dpi=150)
plt.show()
ZONE beta_precip mm_for_0_6_ratio
13 Santiago 0.191517 3.132878
1 Carretera Nacional 0.133912 4.480568
12 Santa Catarina 0.102633 5.846053
7 MTY_Apodaca_Huinalá 0.084674 7.086019
5 Independencia 0.074754 8.026371
11 San Pedro 0.067027 8.951625
4 Escobedo 0.066836 8.977235
0 Apodaca Centro 0.065833 9.113931
2 Centro 0.061034 9.830606
6 La Fe 0.059015 10.166982
10 San Nicolás 0.058999 10.169630
8 MTY_Guadalupe 0.058795 10.204931
3 Cumbres Poniente 0.053112 11.296817
9 Mitras Centro 0.050338 11.919314
No description has been provided for this image