Lesson 36 · Pandas Full Course
Pandas Time Series Basics: DatetimeIndex, Timestamps and date_range | Pandas Course #36
In this lesson you'll be able to: Put real dates into the index of a DataFrame Slice a time series by year, month or date range Read years, months and…
- CoursePandas Full Course
- Lesson36 of 65
- FormatJupyter notebook · 45 code cells
- Data3 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- taxis.csv preview849.0 KB
- stocks.csv preview148.1 KB
- flights.csv preview2.3 KB
📓 Full notebook
Download .ipynbPandas 36: Introduction to DatetimeIndex and Timestamps#
In this lesson you'll be able to:
- Put real dates into the index of a DataFrame
- Slice a time series by year, month or date range
- Read years, months and weekdays straight from the index
- Do date maths with Timestamp, Timedelta and DateOffset
- Build a date index from scratch with pd.date_range Data: daily stock prices 2007-2016 and monthly airline passengers
Warm-up: dates as columns#
Lesson 35 turned text into datetimes and pulled out their parts.
import pandas as pd
taxis = pd.read_csv("data/pandas/taxis.csv", parse_dates=["pickup"])
print(taxis["pickup"].min(), taxis["pickup"].max())
taxis["pickup"].dt.day_name().value_counts().head(3)
Put dates in the index#
Problem: look rows up by date, not by row number.
stocks = pd.read_csv("data/pandas/stocks.csv")
print(stocks.shape)
stocks.head(3).round(2)
print(stocks["Date"].dtype)
print(stocks.index)
Common mistake: making a text column the index
by_text = stocks.set_index("Date")
by_text.loc["2008"]
stocks["Date"] = pd.to_datetime(stocks["Date"])
stocks = stocks.set_index("Date")
print(type(stocks.index).__name__)
print(len(stocks.loc["2008"]))
stocks = pd.read_csv("data/pandas/stocks.csv", index_col="Date",
parse_dates=True)
print(stocks.index.min(), stocks.index.max())
Quick check: is the index in date order, with no repeats?
print(stocks.index.is_monotonic_increasing)
print(stocks.index.is_unique)
stocks.loc["2008-09-15"].round(2)
shuffled = stocks.sample(frac=1, random_state=42)
print(shuffled.index.is_monotonic_increasing)
print(shuffled.sort_index().index.is_monotonic_increasing)
Slice by year, month and date range#
Problem: pull out a whole year, a month, or a week in one line.
y2008 = stocks.loc["2008"]
print(len(y2008))
y2008["GSPC"].iloc[[0, -1]].round(1)
stocks.loc["2008-09-12":"2008-09-19", "GSPC"].round(1)
recent = stocks.loc["2015-06":]
print(len(recent))
print(recent.index[0].date(), recent.index[-1].date())
Common mistake: square brackets with a date
stocks["2008"]
stocks.loc["2008", "AAPL"].agg(["min", "max"]).round(2)
Quick check: how many trading days were in September 2008?
print(len(stocks.loc["2008-09"]))
Your turn#
How much did Starbucks (SBUX) rise from its first to last close in 2009?
sbux = stocks.loc["2009", "SBUX"]
print(sbux.iloc[0].round(2), sbux.iloc[-1].round(2))
print(round(sbux.iloc[-1] / sbux.iloc[0] - 1, 3))
Read the calendar from the index#
Problem: group or filter by year, month or weekday.
idx = stocks.index
print(idx.year[:3].tolist())
print(idx.month[:3].tolist())
print(idx.day_name()[:3].tolist())
Common mistake: adding .dt to the index
stocks.index.dt.year
stocks.index.year.value_counts().sort_index()
print(stocks.index.day_name().value_counts())
yearly = stocks.groupby(stocks.index.year)["AAPL"].last()
yearly.round(2)
Your turn#
Which month of the year had the highest average S&P 500 close in 2015?
sp15 = stocks.loc["2015", "GSPC"]
by_month = sp15.groupby(sp15.index.month).mean()
print(by_month.idxmax(), by_month.max().round(1))
Timestamps, Timedeltas and offsets#
Problem: work with single dates and lengths of time.
ts = pd.Timestamp("2008-09-15")
print(repr(ts))
print(ts.day_name(), ts.quarter, ts.is_month_end)
print(ts + pd.Timedelta(days=30))
print(ts + pd.DateOffset(months=3))
print(stocks.index[-1] - stocks.index[0])
span = stocks.index[-1] - stocks.index[0]
print(span.days)
print(round(span / pd.Timedelta(days=365.25), 2))
Common mistake: adding a plain number to a date
ts + 1
print(ts + pd.Timedelta(days=1))
print(ts + pd.offsets.BDay(5))
pos = stocks.index.get_loc(ts)
print(pos)
print(stocks.index[pos + 1].date(), stocks.index[pos - 1].date())
best = stocks["AAPL"].idxmax()
print(best.date(), stocks.loc[best, "AAPL"].round(2))
print((best - stocks["AAPL"].idxmin()).days)
Common mistake: looking up a day with no trading
stocks.loc["2008-09-13"]
prev = stocks.index.asof(pd.Timestamp("2008-09-13"))
print(prev.date(), prev.day_name())
print(stocks.loc[prev, "GSPC"].round(1))
Build a date index with pd.date_range#
Problem: the data has year and month, but no date at all.
flights = pd.read_csv("data/pandas/flights.csv")
print(flights.shape)
flights.head(3)
months = pd.date_range("1949-01-01", periods=len(flights),
freq="MS")
print(months[:3].strftime("%Y-%m-%d").tolist())
print(months[-1].date(), months.freqstr)
text = flights["year"].astype(str) + "-" + flights["month"]
check = pd.to_datetime(text, format="%Y-%B")
print((check == months).all())
flights = flights.set_index(months)[["passengers"]]
flights.loc["1955-06":"1955-08"]
fridays = pd.date_range("2008-09-01", "2008-10-31", freq="W-FRI")
on_fri = stocks.index.isin(fridays)
stocks.loc[on_fri, "GSPC"].round(1)
Your turn#
Build the four quarter starts of 1958, then select those months from flights.
q58 = pd.date_range("1958-01-01", periods=4, freq="QS")
flights.loc[q58]
Quick check: markets close at weekends. Which weekdays are missing?
first, last = stocks.index[0], stocks.index[-1]
bdays = pd.date_range(first, last, freq="B")
missing = bdays.difference(stocks.index)
print(len(bdays), len(missing))
gone = missing[missing.year == 2015]
print(gone.strftime("%d %b").tolist())
Mini-project: how long did the 2008 crash last?#
Plan: find the peak and the bottom, measure the fall, find the recovery.
sp = stocks["GSPC"]
peak = sp.loc[:"2008"].idxmax()
trough = sp.idxmin()
print(peak.date(), sp[peak].round(1))
print(trough.date(), sp[trough].round(1))
fall = sp[trough] / sp[peak] - 1
print(f"{fall:.1%}")
print((trough - peak).days)
after = sp.loc[trough:]
back = after[after >= sp[peak]].index[0]
print(back.date(), (back - peak).days)
import matplotlib.pyplot as plt
ax = sp.plot(figsize=(9, 4))
ax.axvline(peak, color="green", linestyle="--")
ax.axvline(trough, color="red", linestyle="--")
ax.axvline(back, color="grey", linestyle="--")
ax.set_title("S&P 500: 2007 peak, 2009 bottom, recovery")
ax.set_xlabel("Date")
ax.set_ylabel("Index level")
plt.show()
change = stocks.loc[trough] / stocks.loc[peak] - 1
change.sort_values().round(3)
years = (back - peak).days / 365.25
print(f"Fell {-fall:.0%} in {(trough - peak).days} days; "
f"took {years:.1f} years to regain the peak")
Recap#
- Convert dates first, then set_index, or use index_col + parse_dates
- .loc['2008'], .loc['2008-09'] and date ranges slice by time
- The index has .year, .month, .day_name() directly, without .dt
- Timestamp + Timedelta or DateOffset does date maths
- pd.date_range builds a regular index; .asof handles gaps
Challenges#
- Find IBM's highest close and the date it happened.
- Compare July passenger numbers across the flights years.
- How many trading days fell on a Friday in 2012?
What's next#
Lesson 37: Resampling, Shifting, and Rolling Windows Turn daily prices into monthly ones, compare with the past, and smooth trends.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



