Mathew K Analytics

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…

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.

📓 Full notebook

Download .ipynb

Pandas 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)
2019-02-28 23:29:03 2019-03-31 23:43:45
pickup
Friday       1115
Saturday     1046
Wednesday     966
Name: count, dtype: int64

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)
(2306, 6)
MSFT IBM SBUX AAPL GSPC Date
0 23.95 80.52 16.15 11.09 1416.60 2007-01-03
1 23.91 81.38 16.17 11.33 1418.34 2007-01-04
2 23.77 80.64 16.10 11.25 1409.71 2007-01-05
print(stocks["Date"].dtype)
print(stocks.index)
str
RangeIndex(start=0, stop=2306, step=1)

Common mistake: making a text column the index

by_text = stocks.set_index("Date")
by_text.loc["2008"]
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3641, in Index.get_loc(self, key)
   3640 try:
-> 3641     return self._engine.get_loc(casted_key)
   3642 except KeyError as err:

File pandas/_libs/index.pyx:168, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/index.pyx:197, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/hashtable_class_helper.pxi:7668, in pandas._libs.hashtable.PyObjectHashTable.get_item()

File pandas/_libs/hashtable_class_helper.pxi:7676, in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: '2008'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Cell In[5], line 2
      1 by_text = stocks.set_index("Date")
----> 2 by_text.loc["2008"]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1207, in _LocationIndexer.__getitem__(self, key)
   1205 maybe_callable = com.apply_if_callable(key, self.obj)
   1206 maybe_callable = self._raise_callable_usage(key, maybe_callable)
-> 1207 return self._getitem_axis(maybe_callable, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1449, in _LocIndexer._getitem_axis(self, key, axis)
   1447 # fall thru to straight lookup
   1448 self._validate_key(key, axis)
-> 1449 return self._get_label(key, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1399, in _LocIndexer._get_label(self, label, axis)
   1397 def _get_label(self, label, axis: AxisInt):
   1398     # GH#5567 this will fail if the label is not present in the axis.
-> 1399     return self.obj.xs(label, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:4241, in NDFrame.xs(self, key, axis, level, drop_level)
   4239             new_index = index[loc]
   4240 else:
-> 4241     loc = index.get_loc(key)
   4243     if isinstance(loc, np.ndarray):
   4244         if loc.dtype == np.bool_:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3648, in Index.get_loc(self, key)
   3643     if isinstance(casted_key, slice) or (
   3644         isinstance(casted_key, abc.Iterable)
   3645         and any(isinstance(x, slice) for x in casted_key)
   3646     ):
   3647         raise InvalidIndexError(key) from err
-> 3648     raise KeyError(key) from err
   3649 except TypeError:
   3650     # If we have a listlike key, _check_indexing_error will raise
   3651     #  InvalidIndexError. Otherwise we fall through and re-raise
   3652     #  the TypeError.
   3653     self._check_indexing_error(key)

KeyError: '2008'
stocks["Date"] = pd.to_datetime(stocks["Date"])
stocks = stocks.set_index("Date")
print(type(stocks.index).__name__)
print(len(stocks.loc["2008"]))
DatetimeIndex
253
stocks = pd.read_csv("data/pandas/stocks.csv", index_col="Date",
                     parse_dates=True)
print(stocks.index.min(), stocks.index.max())
2007-01-03 00:00:00 2016-03-01 00:00:00

Quick check: is the index in date order, with no repeats?

print(stocks.index.is_monotonic_increasing)
print(stocks.index.is_unique)
True
True
stocks.loc["2008-09-15"].round(2)
MSFT      22.06
IBM       97.84
SBUX       6.91
AAPL      18.57
GSPC    1192.70
Name: 2008-09-15 00:00:00, dtype: float64
shuffled = stocks.sample(frac=1, random_state=42)
print(shuffled.index.is_monotonic_increasing)
print(shuffled.sort_index().index.is_monotonic_increasing)
False
True

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)
253
Date
2008-01-02    1447.2
2008-12-31     903.2
Name: GSPC, dtype: float64
stocks.loc["2008-09-12":"2008-09-19", "GSPC"].round(1)
Date
2008-09-12    1251.7
2008-09-15    1192.7
2008-09-16    1213.6
2008-09-17    1156.4
2008-09-18    1206.5
2008-09-19    1255.1
Name: GSPC, dtype: float64
recent = stocks.loc["2015-06":]
print(len(recent))
print(recent.index[0].date(), recent.index[-1].date())
190
2015-06-01 2016-03-01

Common mistake: square brackets with a date

stocks["2008"]
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3641, in Index.get_loc(self, key)
   3640 try:
-> 3641     return self._engine.get_loc(casted_key)
   3642 except KeyError as err:

File pandas/_libs/index.pyx:168, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/index.pyx:197, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/hashtable_class_helper.pxi:7668, in pandas._libs.hashtable.PyObjectHashTable.get_item()

File pandas/_libs/hashtable_class_helper.pxi:7676, in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: '2008'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Cell In[14], line 1
----> 1 stocks["2008"]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:4378, in DataFrame.__getitem__(self, key)
   4376 if self.columns.nlevels > 1:
   4377     return self._getitem_multilevel(key)
-> 4378 indexer = self.columns.get_loc(key)
   4379 if is_integer(indexer):
   4380     indexer = [indexer]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3648, in Index.get_loc(self, key)
   3643     if isinstance(casted_key, slice) or (
   3644         isinstance(casted_key, abc.Iterable)
   3645         and any(isinstance(x, slice) for x in casted_key)
   3646     ):
   3647         raise InvalidIndexError(key) from err
-> 3648     raise KeyError(key) from err
   3649 except TypeError:
   3650     # If we have a listlike key, _check_indexing_error will raise
   3651     #  InvalidIndexError. Otherwise we fall through and re-raise
   3652     #  the TypeError.
   3653     self._check_indexing_error(key)

KeyError: '2008'
stocks.loc["2008", "AAPL"].agg(["min", "max"]).round(2)
min    10.65
max    25.79
Name: AAPL, dtype: float64

Quick check: how many trading days were in September 2008?

print(len(stocks.loc["2008-09"]))
21

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))
4.51 10.56
1.343

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())
[2007, 2007, 2007]
[1, 1, 1]
['Wednesday', 'Thursday', 'Friday']

Common mistake: adding .dt to the index

stocks.index.dt.year
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[19], line 1
----> 1 stocks.index.dt.year

AttributeError: 'DatetimeIndex' object has no attribute 'dt'
stocks.index.year.value_counts().sort_index()
Date
2007    251
2008    253
2009    252
2010    252
2011    252
2012    250
2013    252
2014    252
2015    252
2016     40
Name: count, dtype: int64
print(stocks.index.day_name().value_counts())
Date
Wednesday    474
Tuesday      473
Thursday     464
Friday       460
Monday       435
Name: count, dtype: int64
yearly = stocks.groupby(stocks.index.year)["AAPL"].last()
yearly.round(2)
Date
2007     26.21
2008     11.29
2009     27.88
2010     42.67
2011     53.58
2012     71.03
2013     76.76
2014    107.95
2015    104.69
2016    100.53
Name: AAPL, dtype: float64

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))
5 2111.9

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)
Timestamp('2008-09-15 00:00:00')
Monday 3 False
print(ts + pd.Timedelta(days=30))
print(ts + pd.DateOffset(months=3))
print(stocks.index[-1] - stocks.index[0])
2008-10-15 00:00:00
2008-12-15 00:00:00
3345 days 00:00:00
span = stocks.index[-1] - stocks.index[0]
print(span.days)
print(round(span / pd.Timedelta(days=365.25), 2))
3345
9.16

Common mistake: adding a plain number to a date

ts + 1
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[27], line 1
----> 1 ts + 1

File pandas/_libs/tslibs/timestamps.pyx:580, in pandas._libs.tslibs.timestamps._Timestamp.__add__()

TypeError: Addition/subtraction of integers and integer-arrays with Timestamp is no longer supported.  Instead of adding/subtracting `n`, use `n * obj.freq`
print(ts + pd.Timedelta(days=1))
print(ts + pd.offsets.BDay(5))
2008-09-16 00:00:00
2008-09-22 00:00:00
pos = stocks.index.get_loc(ts)
print(pos)
print(stocks.index[pos + 1].date(), stocks.index[pos - 1].date())
428
2008-09-16 2008-09-12
best = stocks["AAPL"].idxmax()
print(best.date(), stocks.loc[best, "AAPL"].round(2))
print((best - stocks["AAPL"].idxmin()).days)
2015-05-22 130.67
2313

Common mistake: looking up a day with no trading

stocks.loc["2008-09-13"]
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File pandas/_libs/index.pyx:660, in pandas._libs.index.DatetimeEngine.get_loc()

File pandas/_libs/hashtable_class_helper.pxi:2761, in pandas._libs.hashtable.Int64HashTable.get_item()

File pandas/_libs/hashtable_class_helper.pxi:2785, in pandas._libs.hashtable.Int64HashTable.get_item()

KeyError: 1221264000000000

During handling of the above exception, another exception occurred:

KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3641, in Index.get_loc(self, key)
   3640 try:
-> 3641     return self._engine.get_loc(casted_key)
   3642 except KeyError as err:

File pandas/_libs/index.pyx:628, in pandas._libs.index.DatetimeEngine.get_loc()

File pandas/_libs/index.pyx:662, in pandas._libs.index.DatetimeEngine.get_loc()

KeyError: Timestamp('2008-09-13 00:00:00')

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\datetimes.py:996, in DatetimeIndex.get_loc(self, key)
    995 try:
--> 996     return Index.get_loc(self, key)
    997 except KeyError as err:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3648, in Index.get_loc(self, key)
   3647         raise InvalidIndexError(key) from err
-> 3648     raise KeyError(key) from err
   3649 except TypeError:
   3650     # If we have a listlike key, _check_indexing_error will raise
   3651     #  InvalidIndexError. Otherwise we fall through and re-raise
   3652     #  the TypeError.

KeyError: Timestamp('2008-09-13 00:00:00')

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Cell In[31], line 1
----> 1 stocks.loc["2008-09-13"]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1207, in _LocationIndexer.__getitem__(self, key)
   1205 maybe_callable = com.apply_if_callable(key, self.obj)
   1206 maybe_callable = self._raise_callable_usage(key, maybe_callable)
-> 1207 return self._getitem_axis(maybe_callable, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1449, in _LocIndexer._getitem_axis(self, key, axis)
   1447 # fall thru to straight lookup
   1448 self._validate_key(key, axis)
-> 1449 return self._get_label(key, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1399, in _LocIndexer._get_label(self, label, axis)
   1397 def _get_label(self, label, axis: AxisInt):
   1398     # GH#5567 this will fail if the label is not present in the axis.
-> 1399     return self.obj.xs(label, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:4241, in NDFrame.xs(self, key, axis, level, drop_level)
   4239             new_index = index[loc]
   4240 else:
-> 4241     loc = index.get_loc(key)
   4243     if isinstance(loc, np.ndarray):
   4244         if loc.dtype == np.bool_:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\datetimes.py:998, in DatetimeIndex.get_loc(self, key)
    996     return Index.get_loc(self, key)
    997 except KeyError as err:
--> 998     raise KeyError(orig_key) from err

KeyError: '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))
2008-09-12 Friday
1251.7

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)
(144, 3)
year month passengers
0 1949 January 112
1 1949 February 118
2 1949 March 132
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)
['1949-01-01', '1949-02-01', '1949-03-01']
1960-12-01 MS
text = flights["year"].astype(str) + "-" + flights["month"]
check = pd.to_datetime(text, format="%Y-%B")
print((check == months).all())
True
flights = flights.set_index(months)[["passengers"]]
flights.loc["1955-06":"1955-08"]
passengers
1955-06-01 315
1955-07-01 364
1955-08-01 347
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)
Date
2008-09-05    1242.3
2008-09-12    1251.7
2008-09-19    1255.1
2008-09-26    1213.3
2008-10-03    1099.2
2008-10-10     899.2
2008-10-17     940.5
2008-10-24     876.8
2008-10-31     968.8
Name: GSPC, dtype: float64

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]
passengers
1958-01-01 340
1958-04-01 348
1958-07-01 491
1958-10-01 359

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))
2390 84
gone = missing[missing.year == 2015]
print(gone.strftime("%d %b").tolist())
['01 Jan', '19 Jan', '16 Feb', '03 Apr', '25 May', '03 Jul', '07 Sep', '26 Nov', '25 Dec']

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))
2007-10-09 1565.2
2009-03-09 676.5
fall = sp[trough] / sp[peak] - 1
print(f"{fall:.1%}")
print((trough - peak).days)
-56.8%
517
after = sp.loc[trough:]
back = after[after >= sp[peak]].index[0]
print(back.date(), (back - peak).days)
2013-03-28 1997
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()
No description has been provided for this image
change = stocks.loc[trough] / stocks.loc[peak] - 1
change.sort_values().round(3)
SBUX   -0.691
GSPC   -0.568
AAPL   -0.505
MSFT   -0.482
IBM    -0.276
dtype: float64
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")
Fell 57% in 517 days; took 5.5 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#

  1. Find IBM's highest close and the date it happened.
  2. Compare July passenger numbers across the flights years.
  3. 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.