Mathew K Analytics

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…

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 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)
(6433, 14)
str
pickup dropoff fare
0 2019-03-23 20:21:09 2019-03-23 20:27:24 7.0
1 2019-03-04 16:11:55 2019-03-04 16:19:00 5.0
2 2019-03-27 17:53:01 2019-03-27 18:00:25 7.5

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)
str
0      August 14, 2020
1    December 23, 2016
2    December 20, 2018
Name: date_added, dtype: str

Quick check: what will .min() return for these text dates?

print(repr(net["date_added"].min()))
print(repr(net["date_added"].max()))
' April 15, 2018'
'September 9, 2020'

Common mistake: subtracting text times

taxis["dropoff"] - taxis["pickup"]
---------------------------------------------------------------------------
ArrowNotImplementedError                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\arrow\array.py:1097, in ArrowExtensionArray._evaluate_op_method(self, other, op, arrow_funcs)
   1096 try:
-> 1097     result = pc_func(self._pa_array, other)
   1098 except pa.ArrowNotImplementedError as err:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pyarrow\compute.py:255, in _make_generic_wrapper.<locals>.wrapper(memory_pool, *args)
    254     return Expression._call(func_name, list(args))
--> 255 return func.call(args, None, memory_pool)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pyarrow\_compute.pyx:405, in pyarrow._compute.Function.call()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pyarrow\error.pxi:155, in pyarrow.lib.pyarrow_internal_check_status()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pyarrow\error.pxi:92, in pyarrow.lib.check_status()

ArrowNotImplementedError: Function 'subtract_checked' has no kernel matching input types (large_string, large_string)

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

TypeError                                 Traceback (most recent call last)
Cell In[5], line 1
----> 1 taxis["dropoff"] - taxis["pickup"]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\common.py:85, in _unpack_zerodim_and_defer.<locals>.new_method(self, other)
     82     other = sanitize_array(other, None)
     83     other = ensure_wrapped_if_datetimelike(other)
---> 85 return method(self, other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arraylike.py:198, in OpsMixin.__sub__(self, other)
    196 @unpack_zerodim_and_defer("__sub__")
    197 def __sub__(self, other):
--> 198     return self._arith_method(other, operator.sub)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:6751, in Series._arith_method(self, other, op)
   6749 def _arith_method(self, other, op):
   6750     self, other = self._align_for_op(other)
-> 6751     return base.IndexOpsMixin._arith_method(self, other, op)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\base.py:1644, in IndexOpsMixin._arith_method(self, other, op)
   1641     rvalues = np.arange(rvalues.start, rvalues.stop, rvalues.step)
   1643 with np.errstate(all="ignore"):
-> 1644     result = ops.arithmetic_op(lvalues, rvalues, op)
   1646 return self._construct_result(result, name=res_name, other=other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\array_ops.py:279, in arithmetic_op(left, right, op)
    270 right = ensure_wrapped_if_datetimelike(right)
    272 if (
    273     should_extension_dispatch(left, right)
    274     or isinstance(right, (Timedelta, BaseOffset, Timestamp))
   (...)
    277     # Timedelta/Timestamp and other custom scalars are included in the check
    278     # because numexpr will fail on it, see GH#31457
--> 279     res_values = op(left, right)
    280 else:
    281     # TODO we should handle EAs consistently and move this check before the if/else
    282     # (https://github.com/pandas-dev/pandas/issues/41165)
    283     # error: Argument 2 to "_bool_arith_check" has incompatible type
    284     # "Union[ExtensionArray, ndarray[Any, Any]]"; expected "ndarray[Any, Any]"
    285     _bool_arith_check(op, left, right)  # type: ignore[arg-type]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\common.py:85, in _unpack_zerodim_and_defer.<locals>.new_method(self, other)
     82     other = sanitize_array(other, None)
     83     other = ensure_wrapped_if_datetimelike(other)
---> 85 return method(self, other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arraylike.py:198, in OpsMixin.__sub__(self, other)
    196 @unpack_zerodim_and_defer("__sub__")
    197 def __sub__(self, other):
--> 198     return self._arith_method(other, operator.sub)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\arrow\array.py:1158, in ArrowExtensionArray._arith_method(self, other, op)
   1154 if pa.types.is_string(self._pa_array.type) or pa.types.is_large_string(
   1155     self._pa_array.type
   1156 ):
   1157     try:
-> 1158         result = self._evaluate_op_method(other, op, ARROW_ARITHMETIC_FUNCS)
   1159     except (pa.ArrowInvalid, pa.ArrowTypeError):
   1160         result = self._str_arith_method_object_fallback(other, op)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\arrow\array.py:1099, in ArrowExtensionArray._evaluate_op_method(self, other, op, arrow_funcs)
   1097     result = pc_func(self._pa_array, other)
   1098 except pa.ArrowNotImplementedError as err:
-> 1099     raise TypeError(self._op_method_error_message(other_original, op)) from err
   1100 return self._from_pyarrow_array(result)

TypeError: operation 'sub' not supported for dtype 'str' with dtype 'str'
pick = pd.to_datetime(taxis["pickup"])
drop = pd.to_datetime(taxis["dropoff"])
(drop - pick).head(3)
0   0 days 00:06:15
1   0 days 00:07:05
2   0 days 00:07:24
dtype: timedelta64[us]

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)
datetime64[us]
0   2019-03-23 20:21:09
1   2019-03-04 16:11:55
2   2019-03-27 17:53:01
Name: pickup, dtype: datetime64[us]
taxis = pd.read_csv("data/pandas/taxis.csv",
                    parse_dates=["pickup", "dropoff"])
print(taxis[["pickup", "dropoff"]].dtypes)
pickup     datetime64[us]
dropoff    datetime64[us]
dtype: object
ts = pd.to_datetime("2019-03-23 20:21:09")
print(repr(ts))
print(ts.year, ts.month_name(), ts.day_name(), ts.hour)
Timestamp('2019-03-23 20:21:09')
2019 March Saturday 20

Common mistake: hidden spaces in date text

pd.to_datetime(net["date_added"])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[10], line 1
----> 1 pd.to_datetime(net["date_added"])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\tools\datetimes.py:1036, in to_datetime(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, origin, cache)
   1034             result = arg.tz_localize("utc")
   1035 elif isinstance(arg, ABCSeries):
-> 1036     cache_array = _maybe_cache(arg, format, cache, convert_listlike)
   1037     if not cache_array.empty:
   1038         result = arg.map(cache_array)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\tools\datetimes.py:254, in _maybe_cache(arg, format, cache, convert_listlike)
    252 unique_dates = unique(arg)
    253 if len(unique_dates) < len(arg):
--> 254     cache_dates = convert_listlike(unique_dates, format)
    255     # GH#45319
    256     try:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\tools\datetimes.py:435, in _convert_listlike_datetimes(arg, format, name, utc, unit, errors, dayfirst, yearfirst, exact)
    433 # `format` could be inferred, or user didn't ask for mixed-format parsing.
    434 if format is not None and format != "mixed":
--> 435     return _array_strptime_with_fallback(arg, name, utc, format, exact, errors)
    437 result, tz_parsed = objects_to_datetime64(
    438     arg,
    439     dayfirst=dayfirst,
   (...)
    443     allow_object=True,
    444 )
    446 if tz_parsed is not None:
    447     # We can take a shortcut since the datetime64 numpy array
    448     # is in UTC

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\tools\datetimes.py:470, in _array_strptime_with_fallback(arg, name, utc, fmt, exact, errors)
    459 def _array_strptime_with_fallback(
    460     arg,
    461     name,
   (...)
    465     errors: str,
    466 ) -> Index:
    467     """
    468     Call array_strptime, with fallback behavior depending on 'errors'.
    469     """
--> 470     result, tz_out = array_strptime(arg, fmt, exact=exact, errors=errors, utc=utc)
    471     if tz_out is not None:
    472         unit = np.datetime_data(result.dtype)[0]

File pandas/_libs/tslibs/strptime.pyx:564, in pandas._libs.tslibs.strptime.array_strptime()

File pandas/_libs/tslibs/strptime.pyx:512, in pandas._libs.tslibs.strptime.array_strptime()

File pandas/_libs/tslibs/strptime.pyx:618, in pandas._libs.tslibs.strptime._parse_with_format()

ValueError: time data " August 4, 2017" doesn't match format "%B %d, %Y". You might want to try:
    - passing `format` if your strings have a consistent format;
    - passing `format='ISO8601'` if your strings are all ISO8601 but not necessarily in exactly the same format;
    - passing `format='mixed'`, and the format will be inferred for each element individually. You might want to use `dayfirst` alongside this.
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)
88
0   2020-08-14
1   2016-12-23
2   2018-12-20
Name: added, dtype: datetime64[us]

Quick check: what is the real earliest date now?

print(net["added"].min().date(), net["added"].max().date())
print(net["added"].isna().sum())
2008-01-01 2021-01-16
10
net = net.dropna(subset=["added"])
print(len(net))
7777

Format codes and values that won't parse#

Problem: dates come in many layouts, and some won't parse.

  • %Y 2019, %m 03, %d 23, %B March, %b Mar
  • %H:%M:%S 20: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"))
23 March 2019 at 20:27
Sat 23 Mar
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"))
5/4/2021 0:05:22
2021-04-05 00:05:22
2021-05-04 00:05:22
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())
88
[' August 4, 2017', ' December 23, 2018']
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"))
['2007-01-03', '10/1/2012', 'August 14, 2020']
0   2007-01-03
1   2012-10-01
2   2020-08-14
dtype: datetime64[us]

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"))
Sat 23 Mar, 20:27

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)
added year month weekday
0 2020-08-14 2020 8 Friday
1 2016-12-23 2016 12 Friday
2 2018-12-20 2018 12 Thursday
net["year"].value_counts().sort_index().tail(6)
year
2016     443
2017    1225
2018    1685
2019    2153
2020    2009
2021     117
Name: count, dtype: int64
parts = {"year": net["year"], "month": net["month"], "day": 1}
month_start = pd.to_datetime(parts)
month_start.value_counts().head(3)
2019-11-01    272
2020-01-01    228
2019-12-01    222
Name: count, dtype: int64

Common mistake: forgetting .dt

net["added"].year
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_25408\2131903352.py in ?()
----> 1 net["added"].year

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py in ?(self, name)
   6190             and name not in self._accessors
   6191             and self._info_axis._can_hold_identifiers_and_holds_name(name)
   6192         ):
   6193             return self[name]
-> 6194         return object.__getattribute__(self, name)

AttributeError: 'Series' object has no attribute 'year'
print(net["added"].iloc[0].year)
print(net["added"].dt.year.iloc[0])
2020
2020
print(net["added"].dt.quarter.value_counts().sort_index())
print(net["added"].dt.is_month_start.mean().round(3))
added
1    1898
2    1686
3    1837
4    2356
Name: count, dtype: int64
0.266

Quick check: which day of the month is most popular for new titles?

net["added"].dt.day.value_counts().head(3)
added
1     2069
15     644
31     274
Name: count, dtype: int64

Your turn#

In which year did Netflix add the most titles in December?

dec = net[net["month"] == 12]
dec["year"].value_counts().head(3)
year
2019    222
2018    190
2020    175
Name: count, dtype: int64

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))
pickup
18    417
19    406
17    388
Name: count, dtype: int64
pickup
3    67
4    57
5    51
Name: count, dtype: int64
taxis["trip"] = taxis["dropoff"] - taxis["pickup"]
mins = taxis["trip"].dt.total_seconds() / 60
mins.describe().round(1)
count    6433.0
mean       14.3
std        11.6
min         0.0
25%         6.5
50%        10.9
75%        18.5
max       107.7
Name: trip, dtype: float64
long = taxis["trip"] > pd.Timedelta(hours=1)
zero = taxis["trip"] == pd.Timedelta(0)
print(long.sum(), zero.sum())
50 6
taxis["day"] = taxis["pickup"].dt.date
taxis["day"].value_counts().head(3)
day
2019-03-14    260
2019-03-06    257
2019-03-13    244
Name: count, dtype: int64
slot = taxis["pickup"].dt.floor("h")
slot.value_counts().head(3)
pickup
2019-03-06 08:00:00    25
2019-03-31 14:00:00    24
2019-03-20 18:00:00    23
Name: count, dtype: int64

Your turn#

Which weekday has the most taxi trips, and which the fewest?

trip_days = taxis["pickup"].dt.day_name()
trip_days.value_counts()
pickup
Friday       1115
Saturday     1046
Wednesday     966
Thursday      905
Sunday        868
Tuesday       825
Monday        708
Name: count, dtype: int64

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))
1381
12.89
in_2019 = net[net["added"].dt.year == 2019]
print(len(in_2019))
in_2019["type"].value_counts()
2153
type
Movie      1497
TV Show     656
Name: count, dtype: int64
net["lag"] = net["year"] - net["release_year"]
print(net["lag"].median())
print((net["lag"] == 0).mean().round(3))
1.0
0.363
last = net["added"].max()
age = (last - net["added"]).dt.days
print(last.date(), age.median())
2021-01-16 680.0

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)
12

title added release_year
583 Arrested Development 2018-05-29 2019
1025 BoJack Horseman 2019-10-25 2020
2311 Fuller House 2019-12-06 2020

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)
weekday
Monday        814
Tuesday      1070
Wednesday    1020
Thursday     1147
Friday       2287
Saturday      731
Sunday        708
Name: count, dtype: int64
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()
No description has been provided for this image
months = net["added"].dt.month_name().value_counts()
print(months.head(3))
print(months.tail(3))
added
December    833
October     785
January     757
Name: count, dtype: int64
added
May         543
June        542
February    472
Name: count, dtype: int64
mix = pd.crosstab(net["weekday"], net["type"], normalize="columns")
mix.reindex(order).round(2)
type Movie TV Show
weekday
Monday 0.11 0.09
Tuesday 0.15 0.12
Wednesday 0.14 0.12
Thursday 0.16 0.12
Friday 0.26 0.37
Saturday 0.09 0.10
Sunday 0.10 0.07
net["friday"] = net["weekday"] == "Friday"
fri_year = net.groupby("year")["friday"].mean()
fri_year.loc[2016:].round(2)
year
2016    0.35
2017    0.32
2018    0.24
2019    0.34
2020    0.23
2021    0.68
Name: friday, dtype: float64
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%}")
Friday: 29% of titles, 1st of month: 27%

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#

  1. Find the average taxi trip length, in minutes, for each hour.
  2. Count Netflix TV shows added per quarter of 2019.
  3. 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.