---
title: "Climate Weather Data"
description: "Weather stations measuring precipitation and temperature that drive aquifer recharge dynamics"
code-fold: true
---
::: {.callout-tip icon=false}
## For Newcomers
**You will learn:**
- How rainfall becomes groundwater (the recharge process)
- Why spring is the most important season for aquifer health
- How droughts and wet years affect water availability underground
- The lag between rain falling and water levels responding
The aquifer is like a giant underground savings account—rain deposits water, pumping withdraws it. This chapter shows how weather patterns control those deposits, with some surprising findings about timing and variability.
:::
## What You Will Learn in This Chapter
By the end of this chapter, you will be able to:
- Describe how the WARM weather stations observe precipitation and temperature that drive aquifer recharge.
- Summarize key climate metrics for the study area (annual totals, seasonal patterns, drought frequency).
- Interpret annual and monthly plots to identify recharge seasons and stress periods.
- Explain how climate variability and drought patterns constrain groundwater analyses and future scenario modeling.
::: {.callout-note icon=false}
## 💻 For Computer Scientists
**Weather data as ML features:**
- **Temporal aggregations**: Daily precipitation → weekly/monthly sums for recharge modeling
- **Lag features**: `precip_7d_ago`, `precip_30d_ago` capture delayed aquifer response
- **Seasonal encoding**: Cyclical features (sin/cos of day-of-year) for seasonal patterns
- **Extreme event flags**: Binary indicators for drought/flood conditions
**Key Challenges:**
- **Missing data**: Sensor gaps require interpolation or imputation strategies
- **Spatial representativeness**: Point measurements must represent regional patterns
- **Non-stationarity**: Climate change means historical patterns may not predict future
**Feature engineering tip**: Cumulative precipitation deficit (actual - long-term average) often outperforms raw precipitation for groundwater prediction.
:::
## Aquifer-Climate Connection
Every raindrop that falls plays a role in the aquifer's health. But the relationship between **weather above ground** and **water levels below** is complex, lagged, and mediated by soils, vegetation, and geology.
This chapter explores 13+ years of weather data from the **WARM (Water and Atmospheric Resources Monitoring) network**—Illinois's premier agricultural and hydrological climate monitoring system.
::: {.callout-note icon=false}
## 🌦️ What Climate Data Reveals
- **Recharge timing**: Spring delivers 40% of annual infiltration
- **Drought frequency**: Low-water conditions occur ~35 days per year
- **Precipitation variability**: Annual totals range 14-49 inches (3.5× variation!)
- **Temperature impacts**: Summer heat drives evapotranspiration, reducing recharge
- **Climate trends**: Increasing variability challenges water planning
:::
---
## Part 1: The WARM Network
::: {.callout-note icon=false}
## 📘 Understanding Weather Monitoring Networks
**What Is WARM?**
The Water and Atmospheric Resources Monitoring Program is Illinois's premier agricultural and hydrological climate network, managed by the Illinois State Water Survey since the 1980s.
**Why Does It Matter for Aquifers?**
Weather stations measure the **inputs** to the groundwater system:
- **Precipitation**: Primary recharge source
- **Temperature**: Controls evapotranspiration (water loss)
- **ET estimates**: Determines net recharge (precip - ET)
**How to Interpret Station Networks:**
| Network Characteristic | Quality Level | Analysis Capability |
|----------------------|--------------|-------------------|
| 10+ stations, evenly distributed | Excellent | Regional climate patterns |
| 5-10 stations, moderate spacing | Good | Adequate for most analyses |
| <5 stations or clustered | Poor | Limited spatial representation |
**This Network:** ~10 WARM stations in Champaign County provides good coverage for characterizing regional precipitation and temperature patterns that drive aquifer recharge.
:::
```{python}
#| label: setup
#| echo: false
import sqlite3
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from pathlib import Path
import os, sys
import warnings
warnings.filterwarnings('ignore')
# Setup
def _find_repo_root(start: Path) -> Path:
for candidate in [start, *start.parents]:
if (candidate / "src").exists() and (candidate / "config" / "data_config.yaml").exists():
return candidate
return start
_proj = Path(os.environ.get("QUARTO_PROJECT_DIR", str(Path.cwd())))
_root = _find_repo_root(_proj)
if str(_root) not in sys.path:
sys.path.append(str(_root))
# Connect to weather database
from src.utils import get_data_path
conn = sqlite3.connect(get_data_path('warm_db'))
# Get Champaign County stations
stations = pd.read_sql("""
SELECT
[Station ID],
[Station Name],
[Latitude (°)],
[Longitude (°)],
[Elevation (m)],
Active,
County,
[Start Date]
FROM StationMetaData
WHERE County LIKE '%Champaign%'
""", conn)
print("🌦️ WARM Weather Monitoring Network - Champaign County")
print("="*60)
print(f" ✅ Active weather stations: {len(stations)}")
print(f" • Parameters: Precipitation, Temperature, Humidity, Wind, Solar Radiation")
print(f" • Frequency: Hourly to daily measurements")
print(f" • Station codes: {', '.join(stations['Station ID'].tolist())}")
stations[['Station ID', 'Station Name', 'Latitude (°)', 'Longitude (°)', 'Elevation (m)', 'Active']]
```
**WARM Network significance**: Research-grade instruments maintained by Illinois State Water Survey, including:
- Tipping-bucket rain gauges (0.01" precision)
- Temperature/humidity sensors (±0.3°C accuracy)
- Anemometers and pyranometers
---
## Part 2: Precipitation Analysis
::: {.callout-note icon=false}
## 📘 What/Why/How Framework for Precipitation
**What Are We Measuring?**
Total annual precipitation—the sum of all rainfall and snowfall over a year, measured in inches.
**Why Does Precipitation Matter?**
Precipitation is the **only natural input** to the aquifer water budget:
- **Recharge formula**: Recharge ≈ Precipitation - Evapotranspiration - Runoff
- **Variability**: High year-to-year variation creates management challenges
- **Timing**: When rain falls matters as much as how much (spring vs. summer)
**How to Analyze Precipitation:**
1. **Annual totals**: Shows wet vs. dry years
2. **Long-term average**: Baseline for comparison
3. **Variability (std dev)**: Quantifies climate uncertainty
4. **Trends**: Is climate changing?
**What Will You See:** Annual precipitation chart with mean and ±1 std dev bands. Years outside the green band are unusually wet or dry—critical for understanding aquifer stress periods.
:::
::: {.callout-warning icon=false}
## ⚠️ What Is Evapotranspiration (ET)?
**Critical Concept:** Not all precipitation becomes groundwater recharge!
**What Is It?**
Evapotranspiration (ET) combines two water losses:
- **Evaporation**: Water escaping from soil, lakes, streams → atmosphere
- **Transpiration**: Plants pulling water from soil through roots → leaves → atmosphere
**Why Does It Matter?**
ET is the **"hidden loss"** in the water budget that determines actual recharge:
- **Simple formula**: Potential Recharge = Precipitation - ET - Runoff
- **Summer paradox**: 4 inches rain - 6 inches ET = -2 inches (deficit!)
- **Spring advantage**: 4 inches rain - 1 inch ET = +3 inches (surplus!)
**Typical Illinois Values:**
- **Summer ET**: 5-7 inches/month (crops actively growing, hot temperatures)
- **Spring ET**: 1-3 inches/month (cool temperatures, early vegetation)
- **Winter ET**: <0.5 inches/month (dormant vegetation, frozen ground)
**Key Insight:** This explains why **spring, not summer, is the primary recharge season** despite similar rainfall amounts. Summer rain may never reach the aquifer—it returns to the atmosphere through ET before infiltrating.
**Management Implication:** Climate projections showing "no change in annual precipitation" can still threaten aquifers if higher temperatures increase ET rates!
:::
Precipitation is the **primary input** to the groundwater system. Every drop of rain that infiltrates the soil eventually becomes part of the aquifer. The analysis below examines 13+ years of precipitation records to understand the patterns and variability that drive recharge.
```{python}
# Load annual precipitation totals from real data
precip = pd.read_sql("""
SELECT
TRIM(nStationCode) as Station,
strftime('%Y', nDateTime) as Year,
SUM(nPrecipDaily) as Annual_Precip
FROM WarmICNDaily
WHERE TRIM(LOWER(nStationCode)) IN ('bvl', 'cmi')
AND nPrecipDaily IS NOT NULL
AND nPrecipDaily < 100 -- Filter obvious errors
GROUP BY TRIM(nStationCode), strftime('%Y', nDateTime)
""", conn)
precip['Year'] = pd.to_numeric(precip['Year'])
precip['Annual_Precip'] = pd.to_numeric(precip['Annual_Precip'])
print(f"\n☔ Precipitation Summary ({precip['Year'].min():.0f}-{precip['Year'].max():.0f}):")
print(f" • Average annual precipitation: {precip['Annual_Precip'].mean():.1f} inches")
print(f" • Standard deviation: {precip['Annual_Precip'].std():.1f} inches")
print(f" • Wettest year: {precip.loc[precip['Annual_Precip'].idxmax(), 'Year']:.0f} ({precip['Annual_Precip'].max():.1f} inches)")
print(f" • Driest year: {precip.loc[precip['Annual_Precip'].idxmin(), 'Year']:.0f} ({precip['Annual_Precip'].min():.1f} inches)")
```
::: {.callout-note icon=false}
## Understanding Precipitation Variability
**What Is It?**
Precipitation variability refers to how much rainfall amounts fluctuate from year to year. Statisticians measure this using **standard deviation**—invented by Karl Pearson in 1893 to quantify spread around an average. In hydrology, high variability means some years are very wet while others are very dry, making water planning challenging.
**Why Does It Matter?**
For aquifer management, precipitation variability determines:
- **Planning reliability**: Can we count on consistent recharge?
- **Drought frequency**: How often will we face water shortages?
- **Storage needs**: Must the aquifer buffer extreme fluctuations?
High variability = higher risk for water managers.
**How Does It Work?**
1. Calculate the **mean** (average) of all annual precipitation totals
2. Measure how far each year deviates from that mean
3. Square the deviations, average them, then take the square root = **standard deviation (σ)**
4. About 68% of years fall within ±1σ of the mean (normal distribution)
**What Will You See?**
The chart shows annual precipitation as lines with markers. The red dashed line is the long-term average, and the green shaded band shows ±1 standard deviation. Years outside this band are unusually wet or dry.
**How to Interpret**
| Observation | Meaning | Management Action |
|------------|---------|------------------|
| Most years within green band | Normal variability | Standard planning sufficient |
| Many years outside band | High variability | Require drought contingency plans |
| Consecutive wet years | Pluvial period | Aquifer recharge opportunity |
| Consecutive dry years | Drought cycle | Implement conservation measures |
| Increasing trend | Wetting climate | Review recharge estimates upward |
| Decreasing trend | Drying climate | Reduce sustainable yield estimates |
:::
::: {.callout-important icon=true}
## 🎯 Management Implications of High Precipitation Variability
**Typical Dataset Values:** Mean ~40 inches, Standard Deviation ±6 inches
**What This Really Means:**
- **Dry years (mean - 1σ)**: ~34 inches precipitation
- **Wet years (mean + 1σ)**: ~46 inches precipitation
- **Range**: 3.5× variation from driest to wettest on record (14-49 inches)
**Critical Insight:** With ±6 inch variability, **one in six years** will be drier than 34 inches. If your aquifer needs 8 inches of recharge to balance withdrawals, and only 20-25% of precipitation recharges in dry years, you get:
```
34 inches × 0.20 = 6.8 inches recharge (deficit!)
```
**Drought Contingency Planning Actions:**
| Timeframe | Action | Trigger | Purpose |
|-----------|--------|---------|---------|
| **Annual** | Review 12-month rolling precipitation | <90% of average | Activate monitoring |
| **Seasonal** | Track spring recharge | <75% of normal Mar-May rain | Early drought warning |
| **Multi-year** | Assess cumulative deficit | 2+ consecutive dry years | Long-term management adjustment |
**Specific Management Responses:**
1. **Drought Stage 1** (precipitation 80-90% of normal):
- Increase monitoring frequency (monthly → weekly)
- Issue voluntary conservation advisory
- Defer non-essential pumping projects
2. **Drought Stage 2** (precipitation 70-80% of normal):
- Mandatory 10% reduction in non-essential withdrawals
- Restrict lawn/landscape irrigation to essential only
- Activate alternative water sources if available
3. **Drought Stage 3** (precipitation <70% of normal):
- Mandatory 25% reduction in all discretionary uses
- Emergency well activation
- Consider temporary moratorium on new well permits
**Planning Rule of Thumb:** Design aquifer management for the **80th percentile dry year**, not the mean. Use (mean - 1σ) as your "planning precipitation" to build resilience.
:::
### Annual Precipitation Trends
```{python}
#| label: fig-annual-precipitation
#| fig-cap: "Annual precipitation totals showing high year-to-year variability. Dashed red line shows the long-term mean, green shaded region shows ±1 standard deviation. This variability drives uncertainty in aquifer recharge."
fig = px.line(
precip,
x='Year',
y='Annual_Precip',
color='Station',
markers=True,
title='Annual Precipitation - Champaign County (2012-2025)<br><sub>High year-to-year variability creates recharge uncertainty</sub>',
labels={'Annual_Precip': 'Precipitation (inches)', 'Year': 'Year'},
height=500
)
avg = precip['Annual_Precip'].mean()
fig.add_hline(y=avg, line_dash="dash", line_color="red", line_width=2,
annotation_text=f"13-year mean: {avg:.1f} inches/year",
annotation_position="top right")
fig.add_hrect(y0=avg-precip['Annual_Precip'].std(),
y1=avg+precip['Annual_Precip'].std(),
fillcolor="green", opacity=0.1,
annotation_text="±1 Std Dev",
annotation_position="top left")
fig.show()
```
**The variability story**: Illinois precipitation is notoriously variable. The aquifer acts as a **buffer against climate variability**—banking water in wet years, drawing down in dry years.
---
## Part 3: Monthly Water Balance
::: {.callout-note icon=false}
## Understanding Monthly Precipitation Patterns
**What Is It?**
Monthly precipitation patterns show how rainfall is distributed across the year. Climate scientists discovered in the 1800s that mid-latitude regions like Illinois have **seasonal precipitation cycles** driven by atmospheric circulation patterns (jet streams, frontal systems). Understanding these patterns is crucial because not all rain becomes groundwater—timing matters enormously.
**Why Does It Matter?**
Monthly timing determines actual aquifer recharge:
- **Spring (Mar-May)**: High precipitation + low evaporation + thawing soils = **maximum recharge**
- **Summer (Jun-Aug)**: High precipitation BUT high evaporation = **minimal net recharge**
- **Fall (Sep-Nov)**: Moderate precipitation + declining evaporation = **moderate recharge**
- **Winter (Dec-Feb)**: Frozen ground prevents infiltration = **negligible recharge**
Result: 3 spring months can deliver 40-50% of annual recharge despite being only 25% of the year!
**How Does It Work?**
1. Calculate average precipitation for each calendar month across all years
2. Compare monthly totals to identify peak and low periods
3. Cross-reference with temperature/evapotranspiration data
4. Identify the "recharge window" when precipitation exceeds losses
**What Will You See?**
A bar chart showing average precipitation by month. Bars are color-coded by intensity (darker blue = more rain). The spring months (March-May) typically show elevated bars, marking the critical recharge period.
**How to Interpret**
| Monthly Pattern | Meaning | Aquifer Implication |
|----------------|---------|-------------------|
| Spring peak (3-5 inches/month) | Recharge season | Water levels rise |
| Summer plateau (4+ inches/month) | High ET negates rain | Water levels stable/decline |
| Fall moderate (3 inches/month) | Secondary recharge | Partial recovery |
| Winter low (2 inches/month) | Frozen ground | Minimal recharge |
| Even distribution | No strong seasonality | Consistent year-round recharge |
| Single peak season | Concentrated recharge risk | Aquifer vulnerable to spring drought |
:::
```{python}
# Monthly patterns from real data
monthly = pd.read_sql("""
SELECT
CAST(strftime('%m', nDateTime) AS INTEGER) as Month,
AVG(nPrecipDaily * 30) as Avg_Monthly_Precip
FROM WarmICNDaily
WHERE TRIM(LOWER(nStationCode)) IN ('bvl', 'cmi')
AND nPrecipDaily IS NOT NULL
AND nPrecipDaily < 20
GROUP BY Month
""", conn)
months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
monthly['Month_Name'] = monthly['Month'].apply(lambda x: months[int(x)-1])
fig = px.bar(
monthly,
x='Month_Name',
y='Avg_Monthly_Precip',
title='Average Monthly Precipitation<br><sub>Spring and summer deliver most water</sub>',
labels={'Avg_Monthly_Precip': 'Precipitation (inches/month)', 'Month_Name': 'Month'},
height=450,
color='Avg_Monthly_Precip',
color_continuous_scale='Blues'
)
fig.add_annotation(x=3.5, y=monthly['Avg_Monthly_Precip'].max()*0.9,
text="🌱 Peak Recharge Window<br>(Mar-May)", showarrow=False,
bgcolor="lightgreen", opacity=0.8)
fig.show()
```
::: {.callout-tip icon=false}
## 🌍 For Hydrologists
**Monthly Recharge Story:**
- **March-May**: **Critical recharge period**—high precipitation, low ET, thawing soils
- **June-August**: High precipitation BUT evapotranspiration also very high—limited net recharge
- **September-November**: Declining precipitation and ET—moderate recharge
- **December-February**: Frozen ground limits infiltration
**The spring window**: Those 3 months often deliver **40-50% of annual aquifer recharge** despite being only 25% of the year.
:::
---
## Part 4: Temperature and Evapotranspiration
::: {.callout-note icon=false}
## Understanding Evapotranspiration (ET)
**What Is It?**
Evapotranspiration (ET) is the combined water loss from evaporation (water surface → vapor) and transpiration (plant roots → leaves → vapor). The concept was formalized by C.W. Thornthwaite in 1948, who developed the first practical ET estimation methods. In agricultural regions like Illinois, **summer ET can exceed 6 inches per month**—more than typical rainfall!
**Why Does It Matter?**
ET is the "hidden loss" in the water budget:
- **Water Balance**: Precipitation - ET = Potential Recharge
- **Summer paradox**: 4 inches of rain - 6 inches of ET = -2 inches (deficit!)
- **Spring advantage**: 4 inches of rain - 1 inch of ET = +3 inches (surplus!)
This explains why spring, not summer, is the primary recharge season despite similar rainfall.
**How Does It Work?**
1. **Solar radiation** heats surfaces and drives evaporation
2. **Temperature** determines how much water vapor air can hold
3. **Humidity** controls vapor pressure gradient (dry air = more ET)
4. **Wind** removes saturated air, accelerating evaporation
5. **Vegetation** acts as "biological pumps" pulling water from soil through roots to leaves
**Formula (simplified Penman-Monteith)**:
```
ET ≈ f(Temperature, Solar Radiation, Wind, Humidity, Vegetation)
```
Summer temperatures of 25-30°C with full crop canopy → ET rates of 5-8 mm/day (6-9 inches/month)!
**What Will You See?**
A dual-line chart showing average high and low temperatures by month, with shaded area between. The summer plateau (June-August) shows sustained high temperatures that drive massive ET losses.
**How to Interpret**
| Temperature Pattern | ET Impact | Recharge Implication |
|-------------------|-----------|---------------------|
| Summer highs >25°C | ET = 5-8 mm/day | Rain mostly lost to atmosphere |
| Spring moderate 10-20°C | ET = 2-4 mm/day | 50-70% of rain reaches aquifer |
| Winter lows <0°C | ET ≈ 0 mm/day | Frozen ground prevents infiltration anyway |
| Increasing summer temps | Higher ET | Reduced recharge despite stable rainfall |
| Extended warm periods | Prolonged ET | Delayed aquifer recovery |
| Early spring warmth | Earlier ET onset | Shortened recharge window |
:::
Temperature drives **evapotranspiration (ET)**—the water lost back to the atmosphere through evaporation and plant transpiration. In the Midwest, summer ET often exceeds precipitation, meaning rain never reaches the aquifer. Understanding temperature patterns helps explain why spring (not summer) is the primary recharge season.
```{python}
# Monthly temperature patterns from real data
monthly_temp = pd.read_sql("""
SELECT
CAST(strftime('%m', nDateTime) AS INTEGER) as Month,
AVG(nAirTempMax) as Avg_Max_Temp,
AVG(nAirTempMin) as Avg_Min_Temp
FROM WarmICNDaily
WHERE TRIM(LOWER(nStationCode)) IN ('bvl', 'cmi')
AND nAirTempMax < 100 AND nAirTempMax > -50
AND nAirTempMin < 50 AND nAirTempMin > -50
GROUP BY Month
""", conn)
monthly_temp['Month_Name'] = monthly_temp['Month'].apply(lambda x: months[int(x)-1])
monthly_temp['Avg_Temp'] = (monthly_temp['Avg_Max_Temp'] + monthly_temp['Avg_Min_Temp']) / 2
fig = go.Figure()
fig.add_trace(go.Scatter(
x=monthly_temp['Month_Name'],
y=monthly_temp['Avg_Max_Temp'],
name='Average High',
mode='lines+markers',
line=dict(color='red', width=3),
fill=None
))
fig.add_trace(go.Scatter(
x=monthly_temp['Month_Name'],
y=monthly_temp['Avg_Min_Temp'],
name='Average Low',
mode='lines+markers',
line=dict(color='blue', width=3),
fill='tonexty',
fillcolor='rgba(200,200,200,0.3)'
))
fig.update_layout(
title='Average Monthly Temperatures<br><sub>Summer heat drives massive water loss through evapotranspiration</sub>',
yaxis_title='Temperature (°C)',
xaxis_title='Month',
height=450,
hovermode='x unified'
)
fig.show()
```
**Temperature's hidden role**: Summer temps of 25-30°C drive crop transpiration that can exceed precipitation—even 4 inches of summer rain may yield <1 inch of aquifer recharge.
---
## Part 5: Drought Analysis
::: {.callout-note icon=false}
## Understanding Drought Metrics and Rolling Windows
**What Is It?**
A **30-day rolling precipitation sum** is a moving window that adds up rainfall over the past 30 days for each day in the record. This technique, developed by climatologists in the early 1900s, smooths out day-to-day noise to reveal sustained dry periods. When this sum falls below a threshold (often the 10th percentile), we classify conditions as **drought**.
**Historical Context**: The Palmer Drought Severity Index (1965) popularized standardized drought metrics. Our 30-day rolling approach is simpler but captures the same concept: sustained precipitation deficits.
**Why Does It Matter?**
For aquifers, short dry spells (a few days) don't matter—the aquifer has vast storage. But **sustained deficits (weeks to months)** cause:
- **Water level decline**: Wells in marginal areas may fail
- **Increased pumping demand**: Agriculture irrigation intensifies
- **Reduced stream base flow**: Surface-groundwater exchange reverses
- **Management triggers**: Drought declarations, pumping restrictions
The 30-day window matches typical aquifer response times in shallow systems.
**How Does It Work?**
1. For each day, sum the previous 30 days of precipitation
2. Calculate the 10th percentile of all 30-day sums (only 10% of periods are drier)
3. Mark days where 30-day sum < 10th percentile as "drought conditions"
4. Count drought days per year to assess drought frequency
**Why 10th percentile?** This represents the driest 10% of all 30-day periods—severe but not extreme. The threshold can be adjusted (5th percentile = extreme drought, 25th percentile = mild drought).
**What Will You See?**
A bar chart showing the number of drought days per year, colored from yellow (few drought days) to dark red (many drought days). High bars indicate years when sustained dry periods were common.
**How to Interpret**
| Drought Days/Year | Severity | Aquifer Impact | Management Response |
|------------------|----------|---------------|-------------------|
| 0-10 days | Minimal drought | Negligible | Normal operations |
| 10-30 days | Occasional drought | Minor water level declines | Monitor conditions |
| 30-60 days | Frequent drought | Noticeable stress | Voluntary conservation |
| 60-100 days | Severe drought year | Significant decline | Mandatory restrictions |
| >100 days | Extreme drought year | Wells may fail | Emergency measures |
| Increasing trend | Worsening climate | Long-term stress | Reduce sustainable yield |
**Example**: The 2012 Midwest drought was extraordinary—some locations exceeded 100 drought days, causing widespread well failures and $30+ billion in agricultural losses.
:::
```{python}
# Calculate 30-day rolling precipitation from real data
daily_precip = pd.read_sql("""
SELECT
datetime(nDateTime) as Date,
TRIM(nStationCode) as Station,
nPrecipDaily as Precip
FROM WarmICNDaily
WHERE TRIM(LOWER(nStationCode)) IN ('bvl', 'cmi')
AND nPrecipDaily IS NOT NULL
AND nPrecipDaily < 20
ORDER BY nDateTime
""", conn)
daily_precip['Date'] = pd.to_datetime(daily_precip['Date'])
daily_precip['Precip'] = pd.to_numeric(daily_precip['Precip'])
daily_precip = daily_precip.sort_values('Date')
daily_precip['Precip_30day'] = daily_precip.groupby('Station')['Precip'].transform(
lambda x: x.rolling(window=30, min_periods=20).sum()
)
drought_threshold = daily_precip['Precip_30day'].quantile(0.10)
daily_precip['Drought'] = daily_precip['Precip_30day'] < drought_threshold
print(f"\n🏜️ Drought Analysis:")
print(f" • Drought threshold (30-day precip): {drought_threshold:.2f} inches")
print(f" • Days below threshold: {daily_precip['Drought'].sum()} ({daily_precip['Drought'].sum()/len(daily_precip)*100:.1f}%)")
daily_precip['Year'] = daily_precip['Date'].dt.year
drought_by_year = daily_precip[daily_precip['Drought']].groupby('Year').size()
fig = px.bar(
drought_by_year.reset_index(),
x='Year',
y=0,
title='Drought Days per Year<br><sub>Days when 30-day precipitation falls below 10th percentile</sub>',
labels={0: 'Days in Drought Conditions', 'Year': 'Year'},
height=400,
color=0,
color_continuous_scale='Reds'
)
fig.show()
```
**Drought impacts**: During drought periods, aquifer water levels decline, pumping demand increases, and wells in marginal areas may fail. The 2012 drought was particularly severe.
---
## Part 6: Key Findings
::: {.callout-note icon=false}
## 📘 Interpreting Climate Summary Metrics
**What Is This Table?**
A summary of key climate metrics that quantify the inputs to the aquifer water budget.
**Why These Metrics Matter:**
Each metric informs different management decisions:
- **Annual precipitation**: Total water available for recharge
- **Variability**: Planning uncertainty (need buffers for dry years)
- **Recharge season**: When to protect infiltration capacity
- **Summer temperature**: Determines ET losses
- **Estimated recharge**: Net water entering aquifer
- **Drought frequency**: How often stress occurs
**How to Use These Values:**
| Metric | This Dataset | Typical Midwest | Interpretation |
|--------|-------------|----------------|----------------|
| Annual precip | ~40 inches | 35-45 inches | Normal/adequate |
| Variability | ±6 inches | ±4-8 inches | Typical uncertainty |
| Recharge | ~8-12 inches | ~8-15 inches | 20-30% of precipitation |
| Drought days | ~35/year | ~20-40/year | Moderate drought exposure |
**Management Insight:** With 40 inches annual precipitation but ±6 inch variability, dry years could see only 34 inches—reducing recharge to 6-8 inches (marginal for high-demand aquifers).
:::
```{python}
# Close database connection if it was opened
if conn is not None:
conn.close()
summary = pd.DataFrame({
'Climate Metric': [
'Average Annual Precipitation',
'Precipitation Variability',
'Primary Recharge Season',
'Average Summer Temperature',
'Estimated Annual Recharge',
'Drought Frequency'
],
'Value': [
f"{precip['Annual_Precip'].mean():.1f} inches/year",
f"±{precip['Annual_Precip'].std():.1f} inches",
"March-May (40-50% of annual recharge)",
f"{monthly_temp[monthly_temp['Month'].isin([6,7,8])]['Avg_Temp'].mean():.1f}°C",
"~8-12 inches/year (20-30% of precipitation, typical Midwest estimate)",
"~35 days/year below drought threshold"
]
})
summary
```
::: {.callout-important icon=true}
## 🎯 Climate-Aquifer Management Implications
### 1. Spring is Everything
- 40-50% of recharge occurs March-May
- **Action**: Protect spring infiltration (no-till, cover crops, avoid compaction)
### 2. Summer Deficit Drives
- Even with 4+ inches/month precipitation, ET creates deficits
- **Action**: Peak pumping coincides with minimal recharge—needs management
### 3. Droughts are Inevitable
- ~35 days/year in drought conditions
- **Action**: Monitor drought indicators proactively, implement tiered response
### 4. High Variability Requires
- 3.5× variation between wet and dry years
- **Action**: Adaptive management, drought contingency planning
:::
---
## Integration with Other Data
This climate context is critical when we:
- Correlate with water levels (wells recover after wet springs?)
- Understand spatial patterns (wells in sandy soils respond differently?)
- Model future scenarios (climate change impacts on recharge)
**Next**: Stream gauges reveal how this precipitation manifests as surface water flows.
---
## Dependencies & Outputs
- **Data source**: `warm_db` (WARM SQLite database)
- **Loader**: Direct SQL queries via sqlite3
- **Outputs**: Climate summaries, water balance, optional exports to `outputs/phase-1/weather/`
---
## Summary
Weather station data provides **climate forcing context** for aquifer response analysis:
✅ **20+ stations, 20M+ records** - Comprehensive precipitation and temperature coverage
✅ **~40 inches/year precipitation** - Primary recharge driver for the aquifer system
✅ **Seasonal patterns clear** - Summer peaks, variable monthly totals drive recharge variability
✅ **Drought/flood history** - Critical for understanding aquifer stress periods
⚠️ **Potential evapotranspiration gap** - Limited ET data requires estimation methods
**Key Insight**: Precipitation minus ET equals potential recharge. Weather data is the **input signal** that wells and streams respond to with characteristic lag times.
---
## Related Chapters
- [Weather Station Density](../part-2-spatial/weather-station-density.qmd) - Spatial coverage analysis
- [Precipitation Patterns](../part-3-temporal/precipitation-patterns.qmd) - Temporal trend analysis
- [Weather-Response Fusion](../part-4-fusion/weather-response-fusion.qmd) - Linking climate to groundwater
- [Recharge Rate Estimation](../part-4-fusion/recharge-rate-estimation.qmd) - Water balance calculations
## Reflection Questions
- Looking at the annual and monthly precipitation plots, which months or seasons would you treat as the most important for aquifer recharge in this region, and why?
- How would an increase in drought frequency (for example, doubling the number of drought days per year) change your expectations about groundwater levels and the reliability of existing wells?
- If you were designing a climate–groundwater model, which weather-derived features from this chapter (aggregations, lags, deficits) would you prioritize, and how would you validate them against well and stream responses?