9  Working with Temporal Data

Dates and times are among the most common — and most frustrating — data types you will encounter. Sixty seconds in a minute, sixty minutes in an hour, twenty-four hours in a day, seven days in a week, and then months of varying lengths: 28, 29, 30, or 31 days depending on the month and whether it is a leap year. Add time zones, daylight saving time, and inconsistent string formatting across countries and systems, and you have a recipe for subtle but consequential errors.

This chapter introduces Python’s datetime library and pandas’s Timestamp objects. By the end, you will be able to parse dates from strings, compute time differences, extract temporal components, create date ranges, set datetime indices, slice data by date, plot time series, and compute rolling averages. These skills are the foundation for the pattern-finding techniques in Chapter 10.

9.1 Setup

import numpy as np
np.set_printoptions(suppress=True)

import pandas as pd
idx = pd.IndexSlice
pd.options.display.max_columns = 100

%matplotlib inline
import matplotlib.pyplot as plt

import seaborn as sb

9.2 Python’s datetime Library

A “classic” joke among Python programmers is the very justifiable confusion between the datetime object within the datetime module. Time and dates are confusing to work with, so let’s take a moment to embrace the awkwardness.

import datetime

9.2.1 Creating datetime objects

The .now() method gets the current time.

now = datetime.datetime.now()
now

There is also a .utcnow() that gets Coordinated Universal Time — basically the time at 0° longitude in London — rather than your system’s local time.

utc = datetime.datetime.utcnow()
utc

You create a specific datetime by passing year, month, day, and optionally hour, minute, and second as integers.

birthday = datetime.datetime(1984, 2, 13, 7, 30, 15)
birthday
WarningStrings are not datetimes

Passing a string like '2020-09-25' to datetime.datetime() will raise a TypeError. Python’s datetime constructor expects integers, not strings. To convert strings to datetimes, use strptime (described below).

datetime.datetime('2020-09-25')

9.2.2 Attributes and methods

Every datetime object has attributes for its individual components: .year, .month, .day, .hour, .minute, .second.

now.second

Methods for converting to simpler objects:

now.date()
now.time()

Other powerful methods include .weekday() (Monday=0 through Sunday=6), .isoweekday() (Monday=1 through Sunday=7), and .isoformat() for standardized string output.

print(now)
now.isoformat()

9.2.3 strftime and strptime

Dates and times are often stored as strings like “2020-07-04” or “07/04/2020” to represent July 4, 2020 in the U.S. — or April 7, 2020 in Europe. (See? Dates and times can be really frustrating! Check out date format by country.)

If you want to convert a datetime to a string, use strftime (f for format). If you want to convert a string to a datetime, use strptime (p for parse). Both use a shared grammar of format codes documented in strftime and strptime behavior.

Code Meaning Example
%Y 4-digit year 2024
%m 2-digit month 03
%d 2-digit day 15
%H 24-hour hour 14
%M Minute 30
%S Second 45
%A Full weekday name Monday
%B Full month name March
%b Abbreviated month Mar
%p AM/PM PM

Format a datetime as a custom string:

datetime.datetime(2024, 4, 1, 12, 0, 0).strftime("%A! %b %d, %Y and we're at %S seconds")

Parse a string into a datetime:

str_time = datetime.datetime.strptime(
    'Friday, February 29, 9960 11:27 am',
    '%A, %B %d, %Y %H:%M %p'
)
str_time

9.2.4 Unix timestamps

Sometimes computer scientists represent time as the number of seconds elapsed since midnight on January 1, 1970. That moment was second 0, and every second since is incremented. This is popularly known as “Unix time” or the “Unix epoch.”

now.timestamp()

You can convert Unix timestamps back to datetime objects with fromtimestamp.

datetime.datetime.fromtimestamp(10000000000)
TipThe Year 2038 Problem

Many of you are too young to remember the panic around the Y2K problem. But there is a much bigger problem known as the Year 2038 problem: 32-bit systems that store time as Unix timestamps will overflow on January 19, 2038 at 3:14:07 UTC when the timestamp exceeds \(2^{31} - 1 = 2{,}147{,}483{,}647\). Mark that date in your calendars.

9.2.5 timedelta

Subtracting one datetime from another produces a timedelta object representing the elapsed time.

birthday_td = now - birthday
birthday_td
birthday_td.seconds

You can add a timedelta to a datetime to compute a future or past date.

birthday + datetime.timedelta(seconds=1)

You can convert timedelta objects into floats by dividing by a “unit” timedelta. This returns your age in days:

birthday_td / datetime.timedelta(hours=24)

9.3 Dates and Times in Pandas

Pandas has its own type called Timestamp for representing dates and times, distinct from datetime. There are good reasons for pandas to have its own representation, but juggling two distinct and sometimes incompatible date representations within Python can definitely be frustrating.

9.3.1 Timestamp

The biggest and most powerful difference between datetime.datetime and pd.Timestamp is that Timestamp will automatically recognize and convert strings to Timestamps without needing the gnarly strftime/strptime syntax.

Note that pandas’s Timestamp is very, very, very different from the Unix time .timestamp() method in datetime.

ts_now = pd.Timestamp('2024-04-17 7:25')
ts_now

Pass a datetime object to pandas’s Timestamp:

ts_birthday = pd.Timestamp(birthday)
ts_birthday

Confirm that these are really different types:

type(birthday)
type(ts_birthday)

9.3.2 Additional attributes and methods

Pandas Timestamp objects have all the attributes of datetime plus several extras: .quarter (what quarter of the year), .is_leap_year (boolean), .day_name(), .to_pydatetime() (convert back to datetime).

ts_birthday.to_pydatetime()
pd.Timestamp('2024-01-13').is_leap_year
ts_birthday.is_leap_year

9.3.3 pd.to_datetime

Just as Timestamp auto-magically converts individual strings, the to_datetime() function can take an array of misshapen and inconsistently typed date representations and convert them all into a uniform array of Timestamp objects.

misshapen_data = [
    'July 4, 2020',
    '9/11/01',
    datetime.datetime(1984, 2, 13, 7),
    datetime.date(2006, 6, 6)
]

pd.to_datetime(misshapen_data, format='mixed')

9.3.4 Parsing dates during CSV loading

When reading a CSV file, you have two options for converting date columns. The first is to pass column names to the parse_dates parameter in read_csv.

sp500_df = pd.read_csv('sp500.csv', parse_dates=['Date'])
sp500_df.head()
sp500_df.loc[0, 'Date']

The second option is to convert after loading with pd.to_datetime.

sp500_df = pd.read_csv('sp500.csv')
sp500_df.loc[0, 'Date']  # Still a string
sp500_df['Date'] = pd.to_datetime(sp500_df['Date'])
sp500_df.loc[0, 'Date']  # Now a Timestamp

9.4 date_range

The date_range function creates an array of evenly spaced dates. Key parameters: start, end, freq (defaulting to daily), and periods.

pd.date_range(start='March 1, 2022', end='March 31, 2024', freq='D')
pd.date_range(start='2022-03-01', end='2022-03-31', freq='H')

Create 12 hourly timestamps starting now:

pd.date_range(start='2024-03-07 00:00', periods=12, freq='H')

Create only business days (look up frequency aliases):

pd.date_range(start='2020-01-01', end='2024-03-07', freq='B')

Create a date_range of quarter starts from January 1, 2000 to today. How many quarters is that?

Solution:

quarters = pd.date_range(start='2000-01-01', end='2024-10-17', freq='QS')
print(f"{len(quarters)} quarters")
quarters

9.5 Indexing and Slicing with Datetime Indices

We often use the Timestamp column as an index, which enables powerful date-based slicing.

sp500_df.set_index('Date', inplace=True)
sp500_df.head()

Access a specific date — you can use several different string formats:

sp500_df.loc['2020 Mar 23']
sp500_df.loc['March 23, 2020']
sp500_df.loc['2020-03-23']
sp500_df.loc[pd.Timestamp('2020-03-23')]

Slice a date range using strings:

sp500_df.loc['2020-03-01':'2020-03-31', 'Adj Close']

Slice an entire year with just the year string:

sp500_df.loc['2020', 'Adj Close']

You can also slice with datetime and Timestamp objects:

sp500_df.loc[datetime.datetime(2020, 3, 1):datetime.datetime(2020, 3, 31), 'Adj Close']

Load the Boulder weather data with dates parsed. Set the “DATE” column as the index. Slice to just 2020 data.

Solution:

weather_df = pd.read_csv('boulder_weather.csv', sep='|', parse_dates=['DATE'])
weather_df.set_index('DATE', inplace=True)

weather_df.loc['2020']

9.6 Plotting Temporal Data

With a datetime index, pandas handles x-axis formatting automatically.

f, ax = plt.subplots(figsize=(12, 6))

sp500_df.loc['2020':, 'Adj Close'].plot(ax=ax)

ax.set_title('S&P500 Adjusted Closing Values, 2020–present')
ax.set_ylabel('Adjusted Close')
ax.set_xlabel('Date')

If the x-axis labels overlap, use f.autofmt_xdate() to rotate them.

f, ax = plt.subplots(figsize=(12, 6))

sp500_df.loc['2022':, 'Adj Close'].plot(ax=ax)

ax.set_title('S&P500 Adjusted Closing Values, 2022–present')
ax.set_ylabel('Adjusted Close')

f.autofmt_xdate(rotation=0, ha='center')

Plot the daily temperature (“TEMP”) from the Boulder weather data for the year 2019. Label your axes.

Solution:

f, ax = plt.subplots(figsize=(12, 4))

weather_df.loc['2019', 'TEMP'].plot(ax=ax)

ax.set_ylabel('Temperature (°F)')
ax.set_title('Daily Temperatures in Boulder, CO (2019)')

9.7 Rolling Averages

A rolling average smooths out noisy data by averaging observations over a sliding window. You chain .rolling() with an aggregation method like .mean(), .median(), or .max().

sp500_2020 = pd.DataFrame()

sp500_2020['Raw'] = sp500_df.loc[:, 'Adj Close']
sp500_2020['7-day'] = sp500_2020['Raw'].rolling(7).mean()
sp500_2020['90-day'] = sp500_2020['Raw'].rolling(90).mean()
sp500_2020['365-day'] = sp500_2020['Raw'].rolling(365).mean()

sp500_2020.head(10)
f, ax = plt.subplots(figsize=(12, 6))

sp500_2020['Raw'].plot(ax=ax, lw=1, c='tab:blue', label='Raw')
sp500_2020['7-day'].plot(ax=ax, lw=2, c='tab:orange', label='7-day')
sp500_2020['90-day'].plot(ax=ax, lw=2, c='tab:green', label='90-day')
sp500_2020['365-day'].plot(ax=ax, lw=3, c='tab:red', label='365-day')

ax.legend(loc='upper left')
ax.set_ylabel('Adjusted Close ($)')
ax.set_title('S&P 500 with Rolling Averages')
TipChoosing window size

A short window (7 days) preserves detail but remains noisy. A long window (365 days) reveals long-term trends but lags behind recent changes. Choose a window based on the time scale of the pattern you want to reveal.

Plot a 30-day rolling average of Boulder temperatures since 2018. How does it compare to the raw daily data?

Solution:

f, ax = plt.subplots(figsize=(12, 4))

weather_df.loc['2018':, 'TEMP'].plot(ax=ax, alpha=0.3, label='Daily')
weather_df.loc['2018':, 'TEMP'].rolling(30).mean().plot(ax=ax, lw=2, label='30-day avg')

ax.legend()
ax.set_ylabel('Temperature (°F)')
ax.set_title('Boulder Daily Temperatures with 30-Day Rolling Average')

9.8 Reindexing

Temporal data often has gaps — the stock market is closed on weekends and holidays, but social media data is continuous. Comparing both requires a common index. The .reindex() method updates a DataFrame’s index to match a new one, filling gaps with NaN.

sp500_df.index.min(), sp500_df.index.max()
sp500_daterange = pd.date_range(
    start=sp500_df.index.min(),
    end=sp500_df.index.max(),
    freq='D'
)

print(f"Trading days: {sp500_df.index.size}, Calendar days: {sp500_daterange.size}")
sp500_reindexed_df = sp500_df.reindex(sp500_daterange)
print(sp500_reindexed_df.shape)
sp500_reindexed_df.head(10)

The NaN values on Saturdays and Sundays are visible. You could fill them with .fillna(method='ffill') (forward-fill the previous trading day’s value) depending on your analytical needs.

9.9 Grouping Temporal Data

You can use pd.Grouper inside .groupby() to aggregate time series to a lower frequency. Here we compute the total monthly trading volume.

sp500_df.groupby(
    pd.Grouper(freq='MS')
).agg({'Volume': 'sum'}).plot()

Group the Boulder weather data by month using pd.Grouper(freq='MS') and compute the average wind speed (“WDSP”). Plot the result.

Solution:

monthly_wind = weather_df.groupby(pd.Grouper(freq='MS')).agg({'WDSP': 'mean'})

f, ax = plt.subplots(figsize=(12, 4))
monthly_wind.plot(ax=ax, legend=False)
ax.set_ylabel('Wind Speed (mph)')
ax.set_title('Monthly Average Wind Speed in Boulder')

Given the string "13-Feb-1984 07:30:15", use datetime.datetime.strptime to parse it. Then convert it to a pandas Timestamp.

Solution:

dt = datetime.datetime.strptime("13-Feb-1984 07:30:15", "%d-%b-%Y %H:%M:%S")
ts = pd.Timestamp(dt)
print(ts)

Create a datetime object for your birthday. Compute a timedelta representing your current age. Express your age in days, hours, and approximate years.

Solution:

my_birthday = datetime.datetime(2002, 6, 15)
age = datetime.datetime.now() - my_birthday

print(f"Days: {age.days:,}")
print(f"Hours: {age.days * 24 + age.seconds // 3600:,}")
print(f"Years: {age / datetime.timedelta(days=365.25):.1f}")

9.10 Looking Ahead

You now have the tools to parse, manipulate, and index temporal data in Python. In Chapter 10, you will put these skills to work finding common temporal patterns — hourly rhythms, weekly cycles, annual seasonality — and learning techniques for decomposing time series into their component signals.