Earth & Environmental Computing | Computational Methods for Earth & Environmental Science | 16-Week Course

Pandas and Time Series

Rain falls, the river answers days later. This week the code can finally see the lag.

Series & DataFrame · Datetime · Groupby · Resample · Merge

Week 06·Course session·Open Course Materials | Adapt for your classroom·Week 6 | Course session

Learning Objectives

By the End of This Lecture, You Will Be Able To

Build, read, and inspect a DataFrame: shape, columns, dtypes, missing values
Select rows and columns by label, by position, and by condition
Parse dates and use the .dt accessor and a datetime index
Explain groupby as split-apply-combine
Resample a datetime-indexed series with the right aggregation
Predict what an inner merge keeps and drops

Contents

Today's Lecture

01Pandas basics

03Aggregating time

02Dates

04Combining records

Recap

Last Week in Sixty Seconds

A grid is a flat column given back its shape with reshape
A mask is a map of where a condition is true, and it counts and selects
np.gradient takes dy before dx, and the spacings differ on our grid
Today: the same thinking, but the axis is time and the labels are dates

Motivation

The Question of the Week

Rain to river

A big storm hits Iowa City

When does the river peak?
How many days of lag, and why?

What it takes

Two records, one table

Two records aligned by date
Monthly and seasonal aggregation
Anomalies: was that month unusual?

Weather: 2,549 days at IOW, 2019-2025. Streamflow: 4,018 days at USGS 05454500, 2015-2025.

Data

Today's Datasets

File	Source	Coverage	Used for
iowa_streamflow_daily.csv	USGS NWIS, site 05454500, Iowa River at Iowa City	Daily, 2015–2025, 4,018 rows	Groupby, resample, rolling, the anomaly
iowa_city_weather_daily.csv	Iowa Environmental Mesonet, station IOW	Daily, 2019–2025, 2,549 rows	Precipitation, and the merge

Pandas Basics

A table where every column has a name and every row has a label.

01 · Pandas basics

From NumPy Arrays to Labeled Tables

Last week · NumPy

ndarray

One dtype for the whole grid
Position is the only address
arr[3, 2]
Missing data needs a sentinel value

This week · pandas

DataFrame

Each column keeps its own dtype
Rows and columns have labels
df.loc[3, "temp_mean_c"]
NaN is understood by every method

import pandas as pd is the convention. Every example today assumes it.

01 · Pandas basics

A Series: Values Plus an Index

Python

import pandas as pd

flow = pd.Series(
    [10600.0, 10700.0, 11600.0, 12600.0],
    index=["May 26", "May 27", "May 28", "May 29"],
    name="discharge_cfs")
flow

print(flow["May 29"])              # by label
print(flow.max(), flow.idxmax())   # value, label

Output

May 26    10600.0
May 27    10700.0
May 28    11600.0
May 29    12600.0
Name: discharge_cfs, dtype: float64

12600.0
12600.0 May 29

A Series is one column: an array of values with a label for each value
These are real flows: the Iowa River at Iowa City, late May 2019

01 · Pandas basics

A DataFrame: Columns That Share One Index

Python

may = pd.DataFrame({
    "date":             ["2019-05-27", "2019-05-28", "2019-05-29"],
    "temp_mean_c":      [21.0, 16.4, 18.3],
    "precipitation_mm": [8.9, 42.7, 20.8],
    "discharge_cfs":    [10700.0, 11600.0, 12600.0],
})
may
may.shape      # (rows, columns)

Output

         date  temp_mean_c  precipitation_mm  discharge_cfs
0  2019-05-27         21.0               8.9        10700.0
1  2019-05-28         16.4              42.7        11600.0
2  2019-05-29         18.3              20.8        12600.0

(3, 4)

Each dict key becomes a column (a Series); the index 0, 1, 2 labels the rows

01 · Pandas basics

Reading the Course Weather File

Python

from earthcourse.io import read_processed_csv    # wraps pd.read_csv

weather = read_processed_csv("iowa_city_weather_daily.csv")
weather.shape
weather[["date", "temp_mean_c", "precipitation_mm"]].head(3)

Output

(2549, 13)

         date  temp_mean_c  precipitation_mm
0  2019-01-01    -2.099151               0.0
1  2019-01-02    -4.923418               0.0
2  2019-01-03    -1.703704               0.0

2,549 rows (days) and 13 columns (variables); head(n) and tail(n) show the ends
read_processed_csv finds data/processed from any notebook folder, then calls pd.read_csv

01 · Pandas basics

Check dtypes Before Anything Else

Python

weather.dtypes

# object  = text (Python strings)
# float64 = numbers you can compute on

# date is text for now.
# Part 2 fixes that.

Output

date                       object
station                    object
temp_mean_c               float64
temp_min_c                float64
temp_max_c                float64
dewpoint_mean_c           float64
relative_humidity_mean    float64
wind_speed_mean_mps       float64
precipitation_mm          float64
latitude                  float64
longitude                 float64
elevation_m               float64
source_type                object
dtype: object

A number stored as object cannot be averaged; a date stored as object cannot be compared correctly

01 · Pandas basics

Selecting Columns: One Bracket or Two

Python

temp = weather["temp_mean_c"]          # a name
type(temp)

pair = weather[["date", "precipitation_mm"]]  # a list
type(pair), pair.shape

print(round(weather["temp_mean_c"].mean(), 2))

Output

<class 'pandas.core.series.Series'>

(<class 'pandas.core.frame.DataFrame'>,
 (2549, 2))

11.08

A single name returns a Series; a list of names returns a DataFrame
mean() skipped 3 missing temperatures without being asked

01 · Pandas basics

Selecting Rows: Position, Label, Condition

Python

weather.iloc[:2, :3]    # rows 0-1, columns 0-2

wet = weather["precipitation_mm"] > 50
print(wet.sum())        # True counts as 1

weather.loc[wet, ["date", "precipitation_mm"]]

Output

         date  station  temp_mean_c
0  2019-01-01      IOW    -2.099151
1  2019-01-02      IOW    -4.923418

6

            date  precipitation_mm
525   2020-06-09            88.392
1027  2021-10-24            56.642
1271  2022-06-25            54.356
1918  2024-04-02            59.436
1997  2024-06-20            67.310
2053  2024-08-15            64.262

.iloc uses integer positions; .loc uses labels or a True/False mask
Six days above 50 mm since 2019; each row keeps its original index label

01 · Pandas basics

New Columns Are One Line of Arithmetic

Python

weather["temp_range_c"] = weather["temp_max_c"] - weather["temp_min_c"]
weather["wet_day"] = weather["precipitation_mm"] > 0

weather[["temp_max_c", "temp_min_c", "temp_range_c", "wet_day"]].head(3)
print(weather["wet_day"].sum(), "wet days of", len(weather))

Output

   temp_max_c  temp_min_c  temp_range_c  wet_day
0    1.111111   -5.611111      6.722222    False
1   -1.569444   -6.722222      5.152778    False
2    7.222222   -6.722222     13.944444    False

718 wet days of 2549

The subtraction runs on all 2,549 rows at once, with no loop; a new name adds a column

01 · Pandas basics

Missing Values: Count Them, Then Decide

Python

cols = ["temp_mean_c", "wind_speed_mean_mps",
        "precipitation_mm"]
weather[cols].isna().sum()

gaps = weather["temp_mean_c"].isna()
weather.loc[gaps, ["date", "temp_mean_c"]]

len(weather.dropna(subset=["temp_mean_c"]))

Output

temp_mean_c            3
wind_speed_mean_mps    2
precipitation_mm       0
dtype: int64

            date  temp_mean_c
726   2020-12-27          NaN
2130  2024-10-31          NaN
2141  2024-11-18          NaN

2546

NaN marks a missing value; mean(), sum() and max() skip it by default
dropna() removes rows and fillna() invents values: both are scientific choices

01 · Pandas basics

describe() and idxmax() on the Iowa River

Python

stream = read_processed_csv("iowa_streamflow_daily.csv")

stream["discharge_cfs"].describe().round(1)

peak = stream["discharge_cfs"].idxmax()
stream.loc[peak, ["date", "discharge_cfs"]]

Output

count     4018.0
mean      2712.5
std       2682.5
min         89.3
25%        700.0
50%       1850.0
75%       3837.5
max      14800.0
Name: discharge_cfs, dtype: float64

date             2018-10-06
discharge_cfs       14800.0
Name: 1374, dtype: object

Mean 2,712 cfs vs median 1,850 cfs: a few floods pull the mean up
idxmax gives the row label of the maximum; .loc turns that label into the row

Pause and think

What does weather["precipitation_mm"] > 50 return: a number, a DataFrame, or something else? And what does weather[ that ] return?

Answer

A Series of 2,549 True/False values, one per row, sharing the weather index

Placed inside [ ], it keeps only the 6 rows where the value is True

Dates

The index stops being a row number and becomes a date.

02 · Dates

The Silent Failure of String Dates

Python

weather["date"].dtype                            # the file we loaded in Part 1
len(weather[weather["date"] >= "2019-6-1"])      # text compared to text

parsed = read_processed_csv("iowa_city_weather_daily.csv", parse_dates=["date"])
len(parsed[parsed["date"] >= "2019-6-1"])        # dates compared to a date

Output

dtype('O')
2184
2398

214 rows lost: every day from June through December 2019, with no error and no warning
"2019-06-15" < "2019-6-1" as text, because the character "0" sorts before "6"

02 · Dates

Dates Done Right, from the First Line

Python

weather = read_processed_csv("iowa_city_weather_daily.csv",
                             parse_dates=["date"])
stream  = read_processed_csv("iowa_streamflow_daily.csv",
                             parse_dates=["date"])
weather["date"].dtype

weather["date"].dt.month       # 1 ... 12
weather["date"].dt.dayofyear   # 1 ... 366

weather["date"].dt.year.value_counts().sort_index()

Output

dtype('<M8[ns]')

date
2019    365
2020    366
2021    365
2022    365
2023    365
2024    359
2025    364
Name: count, dtype: int64

parse_dates turns strings into timestamps at read time; .dt exposes calendar parts
2024 has 359 rows: November 8-14 are absent from the file, not NaN

02 · Dates

A Date Index Lets You Slice by Calendar

Python

w = weather.set_index("date")

print(w.loc["2019-05", "precipitation_mm"].sum().round(1))

cols = ["temp_mean_c", "precipitation_mm"]
w.loc["2019-05-26":"2019-05-29", cols].round(1)

flow = stream.set_index("date")["discharge_cfs"]
print(flow.loc["2019-05-29"])

Output

255.3

            temp_mean_c  precipitation_mm
date
2019-05-26         21.7               6.1
2019-05-27         21.0               8.9
2019-05-28         16.4              42.7
2019-05-29         18.3              20.8

12600.0

.loc accepts a year, a month, or a date range written as text
42.7 mm on May 28, and the river reaches 12,600 cfs on May 29

Aggregating Time

Typical versus actual: groupby, resample, rolling, and the anomaly between them.

03 · Aggregating time

Groupby: Split, Apply, Combine

Python

clim = (stream
        .groupby(stream["date"].dt.month)
        ["discharge_cfs"]
        .mean()
        .round(1))
clim

# split   = rows into 12 month-groups
# apply   = mean within each group
# combine = a 12-row table

Output

date
1     1817.0
2     1756.5
3     3365.7
4     3415.6
5     3896.4
6     4412.1
7     3649.0
8     1922.1
9     1711.7
10    2538.2
11    1969.9
12    2032.3
Name: discharge_cfs, dtype: float64

Every June from 2015 to 2025 contributes to the June number: "what is a typical June?"

03 · Aggregating time

Resample: Calendar-Aware Aggregation

Python

monthly = (weather.set_index("date")
                  .resample("MS")
                  .agg({"temp_mean_c": "mean",
                        "precipitation_mm": "sum"})
                  .round(1))

monthly.loc["2019-01":"2019-08"]

Output

            temp_mean_c  precipitation_mm
date
2019-01-01         -6.7              28.4
2019-02-01         -6.1              67.6
2019-03-01          1.4              51.1
2019-04-01         10.6             112.5
2019-05-01         15.2             255.3
2019-06-01         21.9              81.8
2019-07-01         26.5              34.0
2019-08-01         22.9             122.2

Needs a datetime index: set_index first; "MS" labels each bin by its month start
May 2019 stands alone: 255 mm of rain, the wettest month in this window

03 · Aggregating time

Groupby Month vs Resample Month

groupby(month)

12 rows, the climatology

A typical year, month by month
Every May averaged together
s.groupby(s.index.month).mean()

resample("MS")

One row per actual month

The historical sequence
May 2019 stands alone
s.resample("MS").mean()

Conflating these two is the most common error of the week.

03 · Aggregating time

Aggregation Is a Scientific Choice

Monthly temperature: a mean
Monthly precipitation: a sum
Monthly discharge: a mean of a rate

A monthly mean of daily rainfall answers a different question.

03 · Aggregating time

Rolling Windows Smooth Without Calendar Edges

Python

flow = stream.set_index("date")["discharge_cfs"]
flow = flow.sort_index()

week = flow.rolling(7, min_periods=7).mean()

tbl = pd.DataFrame({"daily": flow,
                    "rolling_7d": week})
tbl.loc["2019-05-26":"2019-06-01"].round(1)

Output

              daily  rolling_7d
date
2019-05-26  10600.0      6627.1
2019-05-27  10700.0      7827.1
2019-05-28  11600.0      9204.3
2019-05-29  12600.0     10425.7
2019-05-30  12300.0     11125.7
2019-05-31  11100.0     11428.6
2019-06-01  10700.0     11371.4

Each value averages the current day and the 6 before it, so the smoothed peak arrives 2 days later
min_periods=7 returns NaN until a full window exists

03 · Aggregating time

Rolling and Groupby Inside earthcourse.hydro

Python

from earthcourse.hydro import annual_peak_flow, seven_day_low_flow

peaks = annual_peak_flow(stream)     # groupby year, then idxmax
low7  = seven_day_low_flow(stream)   # reindex every day, rolling(7, min_periods=7)
peaks.merge(low7, on="year").round(1).tail(4)

Output

    year   peak_date  peak_discharge_cfs  low7_discharge_cfs
7   2022  2022-05-06              6770.0               172.1
8   2023  2023-03-04              6480.0                89.3
9   2024  2024-05-28             10600.0               140.4
10  2025  2025-08-10             10700.0               293.3

seven_day_low_flow reindexes to a full calendar so a missing day breaks the window
2023 holds the lowest 7-day flow in 2015-2025: 89.3 cfs, December 2-9

03 · Aggregating time

Anomaly = Actual Month Minus Typical Month

Python

s = stream.set_index("date")["discharge_cfs"]
m = s.resample("MS").mean()            # actual
clim = m.groupby(m.index.month).mean() # typical

anom = m - m.index.month.map(clim)
anom.loc["2024"].round(1)

Output

date
2024-01-01    -1645.4
2024-02-01    -1262.7
2024-03-01    -3030.9
2024-04-01    -2228.1
2024-05-01     1980.7
2024-06-01     1295.2
2024-07-01     1105.8
2024-08-01     -126.1
2024-09-01    -1348.7
2024-10-01    -2361.9
2024-11-01    -1097.5
2024-12-01    -1070.8
Freq: MS, Name: discharge_cfs, dtype: float64

Resample gives the actual month, groupby the typical month, subtraction the anomaly

Pause and think

Which tool answers: was May 2019 an unusual month for the Iowa River?

Answer

Both, together: resample gives May 2019 (6,321 cfs), groupby gives a typical May (3,896 cfs)

The difference, +2,425 cfs, is the anomaly

Combining Records

Two files, two date ranges, one table. Count rows every time.

04 · Combining records

Merging Two Records

Python

merged = weather.merge(stream[["date", "discharge_cfs"]], on="date", how="inner")

print(len(weather), len(stream), len(merged))
merged["date"].min(), merged["date"].max()

Output

2549 4018 2549

(Timestamp('2019-01-01 00:00:00'), Timestamp('2025-12-30 00:00:00'))

Inner join keeps only dates present in BOTH tables
1,469 flow rows dropped: 1,461 from 2015-2018, plus 8 days with no weather row
len() before and after, every merge, every time

Pause and think

Weather starts in 2019, streamflow in 2015. After an inner merge on date, what date range survives, and where did 2015-2018 go?

Answer

2019-2025 survives: the intersection

2015-2018 flow rows are dropped silently, including the 2018 record flood; only len() shows it

04 · Combining records

Date Arithmetic for Event Windows

Python

flow = stream.set_index("date")["discharge_cfs"]
event = pd.Timestamp("2024-06-20")
window = flow.loc[event : event + pd.Timedelta(days=5)]

print(window.iloc[0])              # flow on the rain day
print(window.max())                # peak within five days
print(window.to_numpy().argmax())  # days until that peak

Output

2660.0
3600.0
4

67.3 mm fell on 2024-06-20; the river peaked 4 days later
Timedelta makes calendar windows explicit; this pattern builds the lab's event table

04 · Combining records

How Fast Does the River Answer the Rain?

Python

events = merged.nlargest(5, "precipitation_mm")
rows = []
for day, rain in zip(events["date"], events["precipitation_mm"]):
    w = flow.loc[day : day + pd.Timedelta(days=5)]
    rows.append({"date": day.date(), "rain_mm": round(rain, 1), "flow_day0": w.iloc[0],
                 "peak_cfs": w.max(), "days_to_peak": (w.idxmax() - day).days})
pd.DataFrame(rows)

Output

         date  rain_mm  flow_day0  peak_cfs  days_to_peak
0  2020-06-09     88.4     7700.0    7700.0             0
1  2024-06-20     67.3     2660.0    3600.0             4
2  2024-08-15     64.3     3070.0    3070.0             0
3  2024-04-02     59.4     1820.0    2210.0             1
4  2021-10-24     56.6      537.0    1240.0             5

Response ranges from 0 to 5 days; a day-0 peak means the river was already high
A day-5 peak sits on the window edge: widen the window and recheck

04 · This week's lab

Lab 6: Time Series for Streamflow and Weather

Lab session

Build the summaries

Monthly summaries with the right aggregations
Precipitation-discharge comparison figure
30-day rolling mean

Studio session

Ask the questions

2024 anomaly plot
Event-response table: days to peak
Monthly correlation versus daily

04 · Lab preview

Daily vs Monthly Correlation, Rain to River

Python

daily_r = merged["precipitation_mm"].corr(merged["discharge_cfs"])

monthly = (merged.set_index("date").resample("MS")
                 .agg({"precipitation_mm": "sum", "discharge_cfs": "mean"}))
monthly_r = monthly["precipitation_mm"].corr(monthly["discharge_cfs"])
print(f"daily r = {daily_r:.3f}   monthly r = {monthly_r:.3f}")

Output

daily r = 0.107   monthly r = 0.428

Same data, four times the correlation: flow answers rain days later, so same-day pairs miss the link
The aggregation choice changes the answer; state it in every figure caption

Pitfalls

Where This Week Goes Wrong

Single brackets when you meant a DataFrame, double when you meant a Series
Dates left as text: filters run and return the wrong rows
Resample without a datetime index
Precipitation averaged when it should be summed
Climatology table confused with the full-length column
Merge losses discovered three cells too late

Summary

Key Takeaways

A DataFrame is labeled columns sharing one index; check shape, dtypes, isna() first
[ ] selects columns, .loc selects by label or mask, .iloc by position
parse_dates first; .dt and a date index unlock the calendar
groupby = typical, resample = actual; the difference is the anomaly
Sum precipitation, average temperature
Count rows before and after every merge

Next week: scientific visualization. Group labs begin: make the pattern visible and defensible.

Discussion · Next week: scientific visualization

Estimate before you compute: how many days should the Iowa River take to peak after a big rain, and what controls that number?

For Wednesday

Bring your estimate to lab and test it against the event-response table