Lesson 35 · Pandas Full Course
Convert Strings to Dates in Pandas with to_datetime and .dt | Pandas Course #35
In this lesson you'll be able to: Explain why dates stored as text give wrong answers Convert text to datetime with pd.todatetime and format codes Clean…
- CoursePandas Full Course
- Lesson35 of 65
- FormatJupyter notebook · 42 code cells
- Data5 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
- netflix_titles.csv preview2.9 MB
- salary_survey.csv preview4.9 MB
- superstore.csv preview1.7 MB
- stocks.csv preview148.1 KB
📓 Full notebook
Download .ipynbPandas 35: Converting Strings to Datetime and Extracting Components#
In this lesson you'll be able to:
- Explain why dates stored as text give wrong answers
- Convert text to datetime with pd.to_datetime and format codes
- Clean messy date strings and handle values that won't parse
- Pull out years, months, weekdays and hours with .dt
- Measure durations and filter by date Data: the Netflix catalogue and a month of New York taxi trips
Warm-up: dates are labels too#
Lesson 34 showed that unparsed dates silently match nothing.
import pandas as pd
taxis = pd.read_csv("data/pandas/taxis.csv")
print(taxis.shape)
print(taxis["pickup"].dtype)
taxis[["pickup", "dropoff", "fare"]].head(3)
Why text dates are not enough#
Problem: text sorts by letters, and can't do date maths.
net = pd.read_csv("data/pandas/netflix_titles.csv")
print(net["date_added"].dtype)
net["date_added"].head(3)
Quick check: what will .min() return for these text dates?
print(repr(net["date_added"].min()))
print(repr(net["date_added"].max()))
Common mistake: subtracting text times
taxis["dropoff"] - taxis["pickup"]
pick = pd.to_datetime(taxis["pickup"])
drop = pd.to_datetime(taxis["dropoff"])
(drop - pick).head(3)
Convert strings with pd.to_datetime#
Problem: turn a whole column of text into real dates, once.
taxis["pickup"] = pd.to_datetime(taxis["pickup"])
print(taxis["pickup"].dtype)
taxis["pickup"].head(3)
taxis = pd.read_csv("data/pandas/taxis.csv",
parse_dates=["pickup", "dropoff"])
print(taxis[["pickup", "dropoff"]].dtypes)
ts = pd.to_datetime("2019-03-23 20:21:09")
print(repr(ts))
print(ts.year, ts.month_name(), ts.day_name(), ts.hour)
Common mistake: hidden spaces in date text
pd.to_datetime(net["date_added"])
spaced = net["date_added"].str.startswith(" ")
print(spaced.sum())
clean = net["date_added"].str.strip()
net["added"] = pd.to_datetime(clean, format="%B %d, %Y")
net["added"].head(3)
Quick check: what is the real earliest date now?
print(net["added"].min().date(), net["added"].max().date())
print(net["added"].isna().sum())
net = net.dropna(subset=["added"])
print(len(net))
Format codes and values that won't parse#
Problem: dates come in many layouts, and some won't parse.
%Y2019,%m03,%d23,%BMarch,%bMar%H:%M:%S20:21:09 for hours, minutes and seconds
stamp = taxis["dropoff"].iloc[0]
print(stamp.strftime("%d %B %Y at %H:%M"))
print(stamp.strftime("%a %d %b"))
survey = pd.read_csv("data/pandas/salary_survey.csv")
stamps = survey["timestamp"]
text = stamps[stamps.str.startswith("5/4/")].iloc[0]
print(text)
print(pd.to_datetime(text, dayfirst=True))
print(pd.to_datetime(text, format="%m/%d/%Y %H:%M:%S"))
raw = net["date_added"]
forced = pd.to_datetime(raw, format="%B %d, %Y", errors="coerce")
print(forced.isna().sum())
print(raw[forced.isna()].head(2).to_list())
shop = pd.read_csv("data/pandas/superstore.csv")["Order Date"]
market = pd.read_csv("data/pandas/stocks.csv")["Date"]
pieces = [market.head(1), shop.iloc[1:2], clean.head(1)]
mixed = pd.concat(pieces, ignore_index=True)
print(mixed.to_list())
print(pd.to_datetime(mixed, format="mixed"))
Your turn#
Parse "2019-03-23 20:21:09" text with an explicit format, then print it as "Sat 23 Mar, 20:21".
raw_drop = pd.read_csv("data/pandas/taxis.csv")["dropoff"]
fmt = "%Y-%m-%d %H:%M:%S"
parsed = pd.to_datetime(raw_drop, format=fmt)
print(parsed.iloc[0].strftime("%a %d %b, %H:%M"))
Extract components with .dt#
Problem: answer questions about years, months and weekdays.
net["year"] = net["added"].dt.year
net["month"] = net["added"].dt.month
net["weekday"] = net["added"].dt.day_name()
net[["added", "year", "month", "weekday"]].head(3)
net["year"].value_counts().sort_index().tail(6)
parts = {"year": net["year"], "month": net["month"], "day": 1}
month_start = pd.to_datetime(parts)
month_start.value_counts().head(3)
Common mistake: forgetting .dt
net["added"].year
print(net["added"].iloc[0].year)
print(net["added"].dt.year.iloc[0])
print(net["added"].dt.quarter.value_counts().sort_index())
print(net["added"].dt.is_month_start.mean().round(3))
Quick check: which day of the month is most popular for new titles?
net["added"].dt.day.value_counts().head(3)
Your turn#
In which year did Netflix add the most titles in December?
dec = net[net["month"] == 12]
dec["year"].value_counts().head(3)
Times of day and durations#
Problem: when do people ride, and for how long?
hour = taxis["pickup"].dt.hour
print(hour.value_counts().head(3))
print(hour.value_counts().tail(3))
taxis["trip"] = taxis["dropoff"] - taxis["pickup"]
mins = taxis["trip"].dt.total_seconds() / 60
mins.describe().round(1)
long = taxis["trip"] > pd.Timedelta(hours=1)
zero = taxis["trip"] == pd.Timedelta(0)
print(long.sum(), zero.sum())
taxis["day"] = taxis["pickup"].dt.date
taxis["day"].value_counts().head(3)
slot = taxis["pickup"].dt.floor("h")
slot.value_counts().head(3)
Your turn#
Which weekday has the most taxi trips, and which the fewest?
trip_days = taxis["pickup"].dt.day_name()
trip_days.value_counts()
Filter and compare with dates#
Problem: keep a date range, or measure a gap in years.
late = taxis["pickup"] >= "2019-03-25"
print(late.sum())
print(taxis.loc[late, "fare"].mean().round(2))
in_2019 = net[net["added"].dt.year == 2019]
print(len(in_2019))
in_2019["type"].value_counts()
net["lag"] = net["year"] - net["release_year"]
print(net["lag"].median())
print((net["lag"] == 0).mean().round(3))
last = net["added"].max()
age = (last - net["added"]).dt.days
print(last.date(), age.median())
Quick check: can a title be added before it was released?
early = net[net["lag"] < 0]
print(len(early))
early[["title", "added", "release_year"]].head(3)
Mini-project: when does Netflix add titles?#
Plan: weekday counts, in calendar order, then month, then a finding.
order = ["Monday", "Tuesday", "Wednesday", "Thursday",
"Friday", "Saturday", "Sunday"]
by_day = net["weekday"].value_counts().reindex(order)
print(by_day)
import matplotlib.pyplot as plt
ax = by_day.plot(kind="bar", figsize=(8, 4), rot=0)
ax.set_title("Netflix titles added, by weekday")
ax.set_xlabel("Day of the week")
ax.set_ylabel("Titles added")
plt.show()
months = net["added"].dt.month_name().value_counts()
print(months.head(3))
print(months.tail(3))
mix = pd.crosstab(net["weekday"], net["type"], normalize="columns")
mix.reindex(order).round(2)
net["friday"] = net["weekday"] == "Friday"
fri_year = net.groupby("year")["friday"].mean()
fri_year.loc[2016:].round(2)
fri = (net["weekday"] == "Friday").mean()
first = net["added"].dt.is_month_start.mean()
print(f"Friday: {fri:.0%} of titles, 1st of month: {first:.0%}")
Recap#
- Text dates sort by letters and can't do date maths
- pd.to_datetime or parse_dates converts them; format= makes it exact
- Strip spaces first; errors='coerce' turns rubbish into NaT
- .dt gives year, month, day_name, hour, date and more
- Subtracting dates gives durations; compare dates to filter
Challenges#
- Find the average taxi trip length, in minutes, for each hour.
- Count Netflix TV shows added per quarter of 2019.
- Print every Netflix date as text like 'Fri 09 Sep 2016'.
What's next#
Lesson 36: Introduction to DatetimeIndex and Timestamps Put dates in the index, then slice a whole time series by date.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



