Interactive Session

📆 A Date Is Not a Number

A cartoon panda is visiting Stonehenge. MidJourney 5

This afternoon’s lesson is dates. We will start by addressing two lines of code we have given you to use without ever explaining what they are actually doing!

On Thursday, an exercise gave you .dt.year inside a boxed aside and told you not to worry about it. Then, on Friday (and just now before lunch!), another one gave you aq['datetimeLocal'].str[11:13] and said the same thing.

Both of these code patterns were doing the same job by different means: pulling one piece out of a date so you could group by it.

This afternoon we will learn how both of them work, and write them ourselves.

Dates get a session of their own because a date is one of the few things in a data file that is almost never stored as what it is. It shows up as text, or as an integer, or as five separate columns… Each of those disguises often works well enough that nothing looks wrong… until something breaks!

By the end of this session you will be able to:

Getting Started

  1. Create the file. In the Explorer, hover over the EDS217 heading and click New File…, then type the name in full, extension included: Session_6C_Dates.ipynb

  2. Check the kernel. The Kernel Selector in the notebook’s action bar should read Python 3.11.15 (Conda: eds217). If it reads anything else, click it, choose Change Kernel, and pick the eds217 entry.

  3. Add a title cell. Click + Markdown in the action bar, and give it this content, with today’s date:

# Day 6: Session 6C - A Date Is Not a Number

[Session Webpage](https://eds-217-essential-python.github.io/course-materials/interactive-sessions/6c_dates.html)

Date: 09/08/2026

  1. Save with Ctrl + S (Cmd + S on macOS), and keep saving as you go.

  2. Read in a file you first met (datetime.forever_ago) on the very first afternoon of this course:

Code 
import pandas as pd

url = 'https://eds-217-essential-python.github.io/data/toolik_weather.csv'
toolik = pd.read_csv(url)

toolik[['Year', 'Month', 'Date', 'Daily_AirTemp_Mean_C']].head()
Year Month Date Daily_AirTemp_Mean_C
0 1988 6 19880601 8.4
1 1988 6 19880602 6.0
2 1988 6 19880603 5.8
3 1988 6 19880604 1.8
4 1988 6 19880605 6.8

Daily weather from the Toolik Field Station on the North Slope of Alaska. We’ve seen this!

The disguise

Look at the Date column, and then at the dtype pandas gives it:

Code 
toolik['Date'].dtype
dtype('int64')

int64. The first row’s date is not the first of June 1988; it is the integer nineteen million, eight hundred and eighty thousand, six hundred and one.

An integer works better here than you might expect. Sorting by it gives the right order, because a date written year first, then month, then day, counts upward as the calendar does. Filtering to a year works, because 19880000 < Date < 19890000 catches exactly 1988. You could get quite a long way like this!

Then we subtract two of them:

Code 
19880701 - 19880630
71

Oops. The thirtieth of June and the first of July are one day apart, and the integer says seventy-one, because integer arithmetic has no concept of a month ending.

Everything else that makes a date useful fails the same way. Which day of the week was it? How many days between sampling visits? Is this row in the growing season? None of those questions can be answered by arithmetic on 19880601, and none of them will raise an error when you try.

Curse you, integer dates!!

The parsing pattern

pd.to_datetime() takes a column of dates in disguise and returns a column of real dates. You describe the disguise with format=:

Code 
toolik['date'] = pd.to_datetime(toolik['Date'], format='%Y%m%d')

toolik[['Date', 'date']].head()
Date date
0 19880601 1988-06-01
1 19880602 1988-06-02
2 19880603 1988-06-03
3 19880604 1988-06-04
4 19880605 1988-06-05 
pd.to_datetime(column, format='%Y%m%d')
#              ↑               ↑
#              what to parse   how it is laid out

The format string is a picture of the text you have. %Y stands where the four-digit year sits, %m where the two-digit month sits, %d where the two-digit day sits, and any punctuation in between is typed literally.

Your data looks like Your format string is
19880601 '%Y%m%d'
1988-06-01 '%Y-%m-%d'
06/01/1988 '%m/%d/%Y'
01/06/1988 '%d/%m/%Y'

Look carefully at the last two rows. 01/06/1988 means the first of June in most of the world and the sixth of January in the United States, and the format string you type decides which of the two readings you get. A great many date bugs start right there, and they are hard to find later, because both readings produce a perfectly ordinary looking date column.

format= is not optional, it is a safety net

pd.to_datetime() will usually guess correctly if you leave format= out. Supply it anyway.

A format string is an assertion about your data. If one row of your file is malformed, or the first few hundred rows are ISO dates and the rest are American ones, a supplied format raises an error and a guessed format will naively produce a column of wrong dates.

Type the format string every time, even when you are fairly sure pandas would have guessed it correctly.

An error you can generate today is much better than a wrong number you find next week.

Check what you got:

Code 
print(toolik['date'].dtype)
print(toolik['date'].min())
print(toolik['date'].max())
datetime64[ns]
1988-06-01 00:00:00
2018-12-31 00:00:00

datetime64[ns], running from 1 June 1988 to 31 December 2018. Thirty years of Arctic weather, stored now as dates rather than as digits.

✏️ Test your knowledge

The date_data.csv file at https://eds-217-essential-python.github.io/data/date_data.csv has a Date column stored as text in the form 2023-11-02. Read it in, write the parsing pattern for it with the correct format string, and print the dtype of the result to prove it worked.

The .dt accessors

Every value in a parsed date column has a year, a month and a day inside it, and .dt is how you get at them:

Code 
toolik['year'] = toolik['date'].dt.year
toolik['month'] = toolik['date'].dt.month
toolik['day'] = toolik['date'].dt.day

toolik[['date', 'year', 'month', 'day']].head()
date year month day
0 1988-06-01 1988 6 1
1 1988-06-02 1988 6 2
2 1988-06-03 1988 6 3
3 1988-06-04 1988 6 4
4 1988-06-05 1988 6 5

.dt works the same way .str does. Both are doorways: .str gives you the string operations for a column of text, and .dt gives you the date operations for a column of dates. Neither one works on the wrong kind of column, which turns out to be useful. If .dt.year raises an AttributeError, the error is telling you that your column has not been parsed yet.

The pair of doorways is what Friday’s supplied line was about. aq['datetimeLocal'].str[11:13] went through the .str door and cut characters 11 and 12 out of every timestamp, which works only because every timestamp in that file was written to the same width, and it hands back the text '06' rather than the number 6. Parse the column first and the same job goes through the .dt door as aq['datetimeLocal'].dt.hour, which returns a number, and which keeps working when one row is written differently from the rest.

Note

🐍 Other things .dt will give you, for when you need them. Of the eight accessors below, you are only responsible for the first three, .dt.year, .dt.month and .dt.day:

.dt.year · .dt.month · .dt.day · .dt.hour · .dt.dayofyear · .dt.dayofweek · .dt.quarter · .dt.date

Proving it worked

The Toolik file gives us something to check against, because it already has Year and Month columns, written by whoever prepared the data. So we can compare our parse against theirs.

Code 
print((toolik['year'] == toolik['Year']).all())
print((toolik['month'] == toolik['Month']).all())
True
True

Two Trues. Every one of the 11,171 dates you parsed agrees with the year and month the station recorded independently.

The check took two lines, and it is worth doing every time you have something to check against, because a wrong format string produces a column that looks completely normal. '%d%m%Y' on this file would have failed spectacularly, because it would have tried to read 88 as a month. On a file of American dates it would not have failed at all: every date whose day is twelve or less parses cleanly with the month and the day swapped, so 03/07/2019 becomes the seventh of March instead of the third of July, and nothing in the printed column would have told you that the swap happened.

✏️ Test your knowledge

Add a dayofyear column to toolik using the .dt accessor for it. Then use the filter pattern to show the rows where dayofyear is 366, and say in one sentence what those rows have in common.

Dates as grouping keys

A date component is an ordinary column of integers, so it is an ordinary grouping key, and Friday’s split-apply-combine pattern works on it unchanged:

Code 
toolik.groupby('month')['Daily_AirTemp_Mean_C'].mean().round(2)
month
1    -22.89
2    -20.70
3    -20.69
4    -11.76
5     -0.80
6      8.59
7     11.22
8      7.23
9     -0.11
10   -10.54
11   -18.34
12   -21.44
Name: Daily_AirTemp_Mean_C, dtype: float64

Ask for the count alongside the mean, the way we have been doing since Friday:

Code 
toolik.groupby('year')['Daily_AirTemp_Mean_C'].agg(['count', 'mean']).head(3).round(2)
count mean
year
1988 214 -4.88
1989 365 -7.94
1990 365 -8.54

1988 has 214 rows because the station opened on the first of June. Its annual mean is warmer than every year that follows it, not because 1988 was warm but because 1988 has no January in it. Friday’s rule about always reading .count() alongside a grouped mean is helpful here, because it puts the 214 in front of you before you read the mean beside it!

✏️ Test your knowledge

Group toolik by month and take the mean of Daily_Precip_Total_mm instead of temperature. Which three months get the most precipitation? Then check .isnull().sum() on that column and say whether it changes how much you trust the answer.

The question that needs a month column

Toolik has thirty years of data, and the obvious question to ask is whether the weather is getting warmer.

Split the record into its first eleven years and its last ten years, using Wednesday’s filter pattern (we are leaving 1999 to 2008 out of both halves, so the two ends of the record are well separated):

Code 
early = toolik[toolik['year'] <= 1998]
late = toolik[toolik['year'] >= 2009]

print(early.shape)
print(late.shape)
(3866, 25)
(3652, 25)

Then ask each half the same question, and put the two answers side by side:

Code 
comparison = pd.DataFrame({
    'early': early.groupby('month')['Daily_AirTemp_Mean_C'].mean(),
    'late': late.groupby('month')['Daily_AirTemp_Mean_C'].mean(),
})

comparison['change'] = comparison['late'] - comparison['early']

comparison.round(2)
early late change
month
1 -24.72 -21.26 3.46
2 -21.86 -19.38 2.48
3 -18.62 -20.64 -2.02
4 -10.40 -11.86 -1.45
5 0.37 -0.54 -0.91
6 8.43 8.19 -0.24
7 12.05 11.28 -0.77
8 7.48 7.18 -0.30
9 -0.85 -0.01 0.84
10 -12.26 -7.99 4.26
11 -19.84 -17.47 2.37
12 -22.32 -21.12 1.20

Read the change column top to bottom, all twelve rows of it.

October is 4.3 °C warmer and January is 3.5 °C warmer. February, November and December are up by one to two and a half degrees, and September by nearly a degree. July, the month people usually think of when they think about warming, is 0.8 °C cooler. May, June and August are cooler too, by between 0.2 and 0.9 °C.

March and April are the two rows that spoil a tidy story: both are in the cold half of the year and both are cooler, March by 2.0 °C and April by 1.5 °C.

If you had computed a single annual mean for each half of the record you would have got 0.5 °C and concluded, correctly but uselessly, that Toolik has warmed a little. The month column turns half a degree into something you can describe: the warming at Toolik (like most of the northern latitudes) has occurred in the early winter and in autumn, the summer is flat or slightly cooler, and the spring has gone the other way. The autumn and early winter warming is a well-documented Arctic pattern with physical causes you can reason about, snow arriving later and sea ice forming later, both of them changing how much heat the land loses in the dark half of the year. Our comparison table raises the question of why March and April have cooled, and it cannot answer it.

The bottom line is that none of these patterns are visible without a month column, and there is no month column until you parse the date! The parsing code pattern at the top of this page turns 19880601 into a value you can group by.

Key points

  • A date stored as text or as an integer is not a date. It will sort correctly and then fail at arithmetic, and it will not raise an error when it fails.
  • The parsing pattern is pd.to_datetime(column, format='...').
  • A format string is a picture of your data: %Y for a four-digit year, %m for a two-digit month, %d for a two-digit day, punctuation typed literally.
  • Always supply format=. It converts a silent wrong answer into a loud error.
  • .dt is to dates what .str is to text. .dt.year, .dt.month, .dt.day.
  • If .dt raises an AttributeError, your column has not been parsed yet.
  • Check your parse against anything you can: another column, a known date range, a row count.
  • Date components are ordinary columns, so they are ordinary grouping keys, which is usually the point of extracting them.

Resources