SyntaxPilot
Courses/Data Analytics with Python/Lesson 22

Data Analytics with Python · Lesson 22 of 90 · 13 min

Cleaning 4: dates and times

datetimeTimestampto_datetime (format, errors, dayfirst).dt accessor (year, month, day, dayofweek, quarter, day_name)Timedeltadate arithmeticPeriodto_periodtime zonetz_localizetz_convertUTC

These are the notes for Data Analytics with Python, episode 22. Almost every dataset has a date: when an order was placed, delivered, or a user signed up. Dates read from a file arrive as text, and text dates sort wrongly and can't be subtracted. Here you turn them into real dates with pd.to_datetime (with dayfirst, format and errors), meet the Timestamp, datetime64 and NaT, read parts of dates with the .dt accessor, measure durations with Timedelta, work with whole months as Periods, and handle time zones with UTC, tz_localize and tz_convert. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5); outputs are unedited.

The data: a delivery-app export

Ramesh's chai stall now sells on a delivery app. Each week the app exports a CSV file with the order id, when it was ordered, when it was delivered, and the amount in rupees. Dates are written the Indian way: day first.

import sys; sys.tracebacklimit = 0
import pandas as pd, io
raw = """order_id,ordered,delivered,amount
101,02/03/2026 09:15,02/03/2026 09:52,180
102,15/03/2026 13:40,15/03/2026 14:05,240
103,9/03/2026 19:05,9/03/2026 19:50,120
104,28/02/2026 21:30,28/02/2026 22:10,300
"""
orders = pd.read_csv(io.StringIO(raw))

sys.tracebacklimit = 0 only makes error messages short (just the last line), so they fit on screen.

Why text dates fail

print(orders.dtypes)
print(orders.sort_values("ordered")[["order_id", "ordered"]])
print(orders["delivered"] - orders["ordered"])
order_id     int64
ordered        str
delivered      str
amount       int64
dtype: object
   order_id           ordered
0       101  02/03/2026 09:15
1       102  15/03/2026 13:40
3       104  28/02/2026 21:30
2       103   9/03/2026 19:05
TypeError: unsupported operand type(s) for -: 'str' and 'str'
  • read_csv does not turn dates into dates by itself: ordered and delivered are str (plain text).
  • Text sorts character by character, like words in a dictionary: 0, 1, 2, then 9 last. So 9 March lands after 28 February.
  • You can't subtract two pieces of text, so "how long did delivery take?" crashes with a TypeError.

Both problems have one cause and one fix: a real date type.

A real date type: datetime, Timestamp, Timedelta

Real-life picture:

Real life pandas Meaning
"02/03" scribbled on a sticky note str just characters: no order in time, no weekday
A wall calendar Timestamp one moment: a date plus a clock time, knows what comes next and the weekday
A stopwatch reading: 37 minutes Timedelta a length of time, the gap between two moments
  • datetime: in programming, a value that holds a date and a clock time together, for example 2 March 2026, 09:15.
  • Timestamp: pandas' name for one datetime value, pd.Timestamp("2026-03-02 09:15").
  • datetime64: the column type (dtype) that stores Timestamps. It shows as datetime64[us]: us means it counts time in microseconds (millionths of a second).
  • Timedelta: a duration, for example 0 days 00:37:00. Moment minus moment gives a Timedelta.

pd.to_datetime: text to dates

print(pd.to_datetime(orders["ordered"]))
ValueError: time data "15/03/2026 13:40" doesn't match format "%m/%d/%Y %H:%M". 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.

pandas looked at the first value, 02/03/2026, and guessed month first (the American way). Then it met 15/03/2026, and there is no month 15. Tell it the dates are day first:

o = pd.to_datetime(orders["ordered"], dayfirst=True)
print(o)
print(o.sort_values().index.tolist())
0   2026-03-02 09:15:00
1   2026-03-15 13:40:00
2   2026-03-09 19:05:00
3   2026-02-28 21:30:00
Name: ordered, dtype: datetime64[us]
[3, 0, 2, 1]

The column is now datetime64[us] and sorting follows time: row 3 (28 February) first, then 2, 9 and 15 March.

The ambiguity trap: dayfirst and format

import pandas as pd
print(pd.to_datetime("02/03/2026"))
print(pd.to_datetime("02/03/2026", dayfirst=True))
print(pd.to_datetime("02/03/2026", format="%d/%m/%Y"))
2026-02-03 00:00:00
2026-03-02 00:00:00
2026-03-02 00:00:00

The same text becomes 3 February or 2 March, and there is no error. If every date in a file has a day of 12 or less, pandas guesses month first, never complains, and every date is silently wrong. The safest fix is format=: you spell out exactly how the text is written, and pandas never guesses.

Format codes (strftime codes)

These codes come from an old C function called strftime ("string format time"), so they're called strftime codes. The same codes are used to read text (format=) and to write dates as text (.dt.strftime).

Code Meaning Example
%d day of month, 01-31 02
%m month number, 01-12 03
%Y 4-digit year 2026
%y 2-digit year 26
%H hour, 24-hour clock, 00-23 13
%I + %p hour on a 12-hour clock + AM/PM 01 PM
%M minute 40
%S second 05
%b / %B short / full month name Mar / March
%a / %A short / full weekday name Mon / Monday

So "15/03/2026 13:40" is "%d/%m/%Y %H:%M". Careful: %m is month, %M is minute. Mixing them up is a classic bug.

print(pd.Timestamp("2026-03-02 09:15").strftime("%A %d %B %Y, %I:%M %p"))
Monday 02 March 2026, 09:15 AM

Converting both columns with an exact format:

fmt = "%d/%m/%Y %H:%M"
orders["ordered"] = pd.to_datetime(orders["ordered"], format=fmt)
orders["delivered"] = pd.to_datetime(orders["delivered"], format=fmt)
print(orders.dtypes)
print(orders["ordered"][0], type(orders["ordered"][0]).__name__)
order_id              int64
ordered      datetime64[us]
delivered    datetime64[us]
amount                int64
dtype: object
2026-03-02 09:15:00 Timestamp

pandas always shows dates as year-month-day. That is the ISO 8601 style, an international standard for writing dates (2026-03-02), and it's the one format nobody can misread. It also sorts correctly even as text.

Bad dates: errors="coerce" and NaT

Real files contain dates that aren't dates:

import sys; sys.tracebacklimit = 0
import pandas as pd
typed = pd.Series(["2026-03-02", "yesterday", "2026-02-31", ""])
print(pd.to_datetime(typed, format="%Y-%m-%d"))
ValueError: time data "yesterday" doesn't match format "%Y-%m-%d". 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.

One bad value stops the whole column. errors="coerce" ("coerce" means force) turns anything that can't be a date into a missing date instead of an error:

typed = pd.Series(["2026-03-02", "yesterday", "2026-02-31", ""])
d = pd.to_datetime(typed, format="%Y-%m-%d", errors="coerce")
print(d)
print(d.isna().sum(), "dates need a human")
0   2026-03-02
1          NaT
2          NaT
3          NaT
dtype: datetime64[us]
3 dates need a human

NaT means Not a Time: the date version of NaN (episode 19). "yesterday", 31 February (which doesn't exist) and the blank became NaT. isna() finds NaT just like NaN. Rule: coerce, then count, and never drop them without looking.

The .dt accessor: parts of a date

Just as .str gives string methods for a text column (episode 21), .dt gives date methods for a datetime column. (An accessor is a doorway on a Series to a group of methods.) From here on the two columns are converted with:

fmt = "%d/%m/%Y %H:%M"
for c in ["ordered", "delivered"]:
    orders[c] = pd.to_datetime(orders[c], format=fmt)
o = orders["ordered"]
parts = pd.DataFrame({"year": o.dt.year, "month": o.dt.month,
    "day": o.dt.day, "dow": o.dt.dayofweek,
    "weekday": o.dt.day_name(), "q": o.dt.quarter, "hour": o.dt.hour})
print(parts)
print((o.dt.dayofweek >= 5).sum(), "weekend orders")
   year  month  day  dow   weekday  q  hour
0  2026      3    2    0    Monday  1     9
1  2026      3   15    6    Sunday  1    13
2  2026      3    9    0    Monday  1    19
3  2026      2   28    5  Saturday  1    21
2 weekend orders
Attribute Gives Example
.dt.year, .dt.month, .dt.day the parts as numbers 2026, 3, 2
.dt.dayofweek weekday number, Monday = 0 … Sunday = 6 0
.dt.day_name() weekday name (a method: note the ()) Monday
.dt.quarter quarter of the year: Jan-Mar = 1, Apr-Jun = 2… 1
.dt.hour, .dt.minute clock parts 9
.dt.date just the date, no time 2026-03-02
.dt.strftime(fmt) the date written as text in your format "02 Mar"

dayofweek >= 5 means Saturday or Sunday: one line answers "do weekends sell more?"

Timedelta: durations and date arithmetic

Ramesh promises delivery in 30 minutes:

took = orders["delivered"] - orders["ordered"]
print(took)
mins = took.dt.total_seconds() / 60
print(mins.tolist())
print(orders.loc[mins > 30, "order_id"].tolist(), "were late")
0   0 days 00:37:00
1   0 days 00:25:00
2   0 days 00:45:00
3   0 days 00:40:00
dtype: timedelta64[us]
[37.0, 25.0, 45.0, 40.0]
[101, 103, 104] were late

Doing maths with dates and durations is called date arithmetic: moment − moment = Timedelta; moment + Timedelta = moment. .dt.total_seconds() turns a Timedelta into a number of seconds.

Adding a plain number fails, because the number has no unit:

print(orders["ordered"] + 7)
TypeError: Addition/subtraction of integers and integer-arrays with DatetimeArray is no longer supported.  Instead of adding/subtracting `n`, use `n * obj.freq`

Say the unit with pd.Timedelta:

expiry = orders["ordered"] + pd.Timedelta(days=7)
print(expiry.dt.strftime("%d %b").tolist())
age = pd.Timestamp("2026-03-31") - orders["ordered"]
print(age.dt.days.tolist(), "days old on 31 March")
['09 Mar', '22 Mar', '16 Mar', '07 Mar']
[28, 15, 21, 30] days old on 31 March

.dt.days counts whole days only (28 February 21:30 to 31 March 00:00 is 30 days and 2.5 hours, so 30). pandas knows February 2026 has 28 days. pd.Timedelta also takes hours=, minutes=, weeks=.

Period: a whole month

"How much did you sell in March?" March isn't a moment, it's a stretch of time. A Period represents a whole span, like a month, a quarter or a year. .dt.to_period("M") turns each moment into the month it belongs to ("Q" gives quarters, "Y" years, "D" days).

month = orders["ordered"].dt.to_period("M")
print(month)
print(orders.loc[month == "2026-03", "amount"].sum(), "rupees in March")
print(month[0].start_time, "to", month[0].end_time)
0    2026-03
1    2026-03
2    2026-03
3    2026-02
Name: ordered, dtype: period[M]
540 rupees in March
2026-03-01 00:00:00 to 2026-03-31 23:59:59.999999

A Period knows its edges (start_time, end_time) and its neighbours:

print(pd.Period('2026-03', 'M') + 1)
2026-04

Timestamp = a point in time. Period = a stretch of time. In episode 24 you'll group sales by these months in one line.

Time zones and UTC

The delivery app's server logs times in UTC (Coordinated Universal Time): the world's reference clock. Every place is a fixed offset ahead of or behind it; India Standard Time (IST) is UTC + 5:30. A time zone is the set of clock rules for a region, named like Asia/Kolkata (India) or Europe/London. These names come from the IANA time zone database, which Python uses.

A night owl in Pune orders at 02:15 on Monday; the server logs 20:45 on Sunday:

import pandas as pd
logs = pd.DataFrame({"order_id": [201, 202, 203],
    "at": pd.to_datetime(["2026-03-01 20:45",
        "2026-03-02 04:10", "2026-03-02 18:35"])})
print(logs["at"].dt.day_name().tolist())
['Sunday', 'Monday', 'Monday']

These Timestamps carry no zone. A time without a zone is naive; a time that knows its zone is aware. You can't convert a naive time:

print(logs["at"].dt.tz_convert("Asia/Kolkata"))
TypeError: Cannot convert tz-naive timestamps, use tz_localize to localize

Two steps: tz_localize attaches the zone the clock was written in (the clock reading stays the same), then tz_convert moves the same moment to another clock.

utc = logs["at"].dt.tz_localize("UTC")
ist = utc.dt.tz_convert("Asia/Kolkata")
print(pd.DataFrame({"utc": utc, "india": ist}))
print(ist.dt.day_name().tolist())
                        utc                     india
0 2026-03-01 20:45:00+00:00 2026-03-02 02:15:00+05:30
1 2026-03-02 04:10:00+00:00 2026-03-02 09:40:00+05:30
2 2026-03-02 18:35:00+00:00 2026-03-03 00:05:00+05:30
['Monday', 'Monday', 'Tuesday']

+05:30 at the end shows the offset from UTC. Same moments, the right local days. Good habit: store times in UTC, convert to local time only for reports. Comparing a naive and an aware time also fails (TypeError: Cannot compare tz-naive and tz-aware datetime-like objects): make both aware first.

Mini project: clean_orders()

def clean_orders(df, fmt="%d/%m/%Y %H:%M"):
    df = df.copy()
    for c in ["ordered", "delivered"]:
        df[c] = pd.to_datetime(df[c], format=fmt, errors="coerce")
    df["mins"] = (df["delivered"] - df["ordered"]).dt.total_seconds() / 60
    df["weekday"] = df["ordered"].dt.day_name()
    df["month"] = df["ordered"].dt.to_period("M")
    df["late"] = df["mins"] > 30
    return df.sort_values("ordered")[["order_id", "weekday", "month", "mins", "late"]]
print(clean_orders(orders))
   order_id   weekday    month  mins   late
3       104  Saturday  2026-02  40.0   True
0       101    Monday  2026-03  37.0   True
2       103    Monday  2026-03  45.0   True
1       102    Sunday  2026-03  25.0  False

Parse once, with an exact format and coerce; then everything else uses .dt.

Common mistakes

Code / habit Real error / symptom Why Fix
orders["delivered"] - orders["ordered"] on text TypeError: unsupported operand type(s) for -: 'str' and 'str' dates are still text pd.to_datetime(...) first
Sorting a text date column 9 March after 28 February text sorts character by character convert, then sort
pd.to_datetime(col) on day-first dates ValueError: time data "15/03/2026 13:40" doesn't match format "%m/%d/%Y %H:%M" it guessed month first dayfirst=True or format=
pd.to_datetime("02/03/2026") no error, becomes 3 February ambiguous date, guessed month first format="%d/%m/%Y"
%M for month wrong dates, no error %M is minutes %m for month
One junk value in the column ValueError: time data "yesterday" doesn't match format "%Y-%m-%d" can't parse it errors="coerce", then count NaT
orders["ordered"] + 7 TypeError: Addition/subtraction of integers and integer-arrays with DatetimeArray is no longer supported... 7 has no unit + pd.Timedelta(days=7)
naive.dt.tz_convert("Asia/Kolkata") TypeError: Cannot convert tz-naive timestamps, use tz_localize to localize no zone attached .dt.tz_localize("UTC").dt.tz_convert(...)
Comparing naive with aware TypeError: Cannot compare tz-naive and tz-aware datetime-like objects one side has no zone localize both

Practice

All practice questions use this table:

import pandas as pd, io
raw = """visit_id,signed_up,last_login,city_time
1,05-01-2026,2026-03-01 08:30,2026-03-01 19:00
2,17-01-2026,2026-03-05 23:50,2026-03-04 22:15
3,31-01-2026,not logged in,2026-03-06 03:40
"""
v = pd.read_csv(io.StringIO(raw))

1. Convert signed_up (day-month-year with dashes) with an exact format.

Solution
s = pd.to_datetime(v["signed_up"], format="%d-%m-%Y")
print(s.tolist())
[Timestamp('2026-01-05 00:00:00'), Timestamp('2026-01-17 00:00:00'), Timestamp('2026-01-31 00:00:00')]

2. Convert last_login without crashing, and count the bad values.

Solution
l = pd.to_datetime(v["last_login"], errors="coerce")
print(l.tolist())
print(l.isna().sum())
[Timestamp('2026-03-01 08:30:00'), Timestamp('2026-03-05 23:50:00'), NaT]
1

3. How many whole days passed between sign-up and last login?

Solution
print((l - s).dt.days.tolist())
[55.0, 47.0, nan]

The missing login gives NaN days, so the column becomes float.

4. On which weekday, and in which month (as a Period), did each user sign up?

Solution
print(s.dt.day_name().tolist(), s.dt.to_period("M").tolist())
['Monday', 'Saturday', 'Saturday'] [Period('2026-01', 'M'), Period('2026-01', 'M'), Period('2026-01', 'M')]

5. city_time is in UTC. Show it in Indian time as text like 02 Mar 00:30.

Solution
t = pd.to_datetime(v["city_time"]).dt.tz_localize("UTC").dt.tz_convert("Asia/Kolkata")
print(t.dt.strftime("%d %b %H:%M").tolist())
['02 Mar 00:30', '05 Mar 03:45', '06 Mar 09:10']

The first two moved to the next day in India.

6. A free trial lasts 30 days from sign-up. Print each trial's end date as YYYY-MM-DD.

Solution
print((s + pd.Timedelta(days=30)).dt.strftime("%Y-%m-%d").tolist())
['2026-02-04', '2026-02-16', '2026-03-02']

Quick quiz

t = pd.Timestamp("2026-03-31 22:00")
t = t.tz_localize("UTC")
t = t.tz_convert("Asia/Kolkata")
print(t.month)

A. 3 B. 4 C. 22 D. Error

Answer

B. 4. 22:00 UTC + 5:30 = 03:30 on 1 April in India. A March sale in a UTC log is an April sale in India.

Interview questions

  1. Why convert date columns with pd.to_datetime? Text dates sort character by character and can't be subtracted or grouped by month; datetime64 columns sort in time, support arithmetic and have the .dt accessor.
  2. What's the danger of letting pandas guess date formats? Ambiguous dates like 02/03/2026 parse silently to the wrong date (month-first). Use an explicit format= (or dayfirst=True).
  3. What does errors="coerce" do? Unparseable values become NaT instead of raising an error; count and review them afterwards.
  4. Timestamp vs Timedelta vs Period? A Timestamp is a point in time; a Timedelta is a duration; a Period is a span such as a month.
  5. How do you get the weekday of each date? .dt.dayofweek (Monday = 0) or .dt.day_name().
  6. How do you add 7 days to a date column? col + pd.Timedelta(days=7); adding a plain integer raises a TypeError.
  7. Naive vs aware datetimes? Naive has no time zone; aware knows its zone and offset. Only aware times can be converted between zones.
  8. tz_localize vs tz_convert? tz_localize attaches a zone without changing the clock reading; tz_convert changes the clock reading to another zone for the same moment.
  9. Why store timestamps in UTC? One unambiguous reference clock, no daylight-saving jumps, easy to convert to any local zone for reporting.
  10. How would you total sales per month? df["ordered"].dt.to_period("M") as a key, then filter or group by it (episode 24).

Glossary

Term Meaning Example
datetime A value holding a date and a clock time together 2 March 2026, 09:15
Timestamp pandas' type for one datetime value pd.Timestamp("2026-03-02 09:15")
datetime64[us] The column type that stores Timestamps, counted in microseconds orders["ordered"].dtype
Microsecond A millionth of a second the us in datetime64[us]
pd.to_datetime() Turns text (or numbers) into Timestamps pd.to_datetime(col, format=fmt)
dayfirst=True Read ambiguous dates as day first 02/03 → 2 March
format= The exact pattern of the date text; no guessing "%d/%m/%Y %H:%M"
strftime codes %d %m %Y %H %M %S %b %A…: the letters of a date format %m month, %M minute
strftime Write a date as text in a format .dt.strftime("%d %b") → "02 Mar"
ISO 8601 International standard way to write dates: year-month-day 2026-03-02
errors="coerce" Turn unparseable values into missing values instead of an error "yesterday" → NaT
NaT Not a Time: a missing datetime 31 February
Accessor A doorway on a Series to a group of methods .str, .dt
.dt accessor Date methods for a datetime column col.dt.year
.dt.dayofweek Weekday as a number, Monday = 0 … Sunday = 6 2 March 2026 → 0
.dt.day_name() Weekday as a name "Monday"
Quarter A three-month block of the year Jan-Mar = 1
Timedelta A duration: the gap between two moments 0 days 00:37:00
Date arithmetic Maths with dates and durations delivered - ordered
pd.Timedelta(days=7) A duration with a unit date + pd.Timedelta(days=7)
.dt.total_seconds() A Timedelta as a number of seconds 37 min → 2220.0
.dt.days Whole days in a Timedelta 30 days 2.5 h → 30
Period A span of time such as a month or quarter Period('2026-03', 'M')
to_period("M") Moment → the month it belongs to 2 March → 2026-03
UTC Coordinated Universal Time: the world's reference clock +00:00
IST India Standard Time, UTC + 5:30 +05:30
Time zone The clock rules for a region Asia/Kolkata
Naive datetime A time with no zone attached 2026-03-01 20:45
Aware datetime A time that knows its zone 2026-03-01 20:45+00:00
tz_localize Attach a zone; the clock reading stays the same .dt.tz_localize("UTC")
tz_convert Show the same moment on another zone's clock .dt.tz_convert("Asia/Kolkata")

Summary

  • Dates read from files are text until pd.to_datetime turns them into Timestamps (datetime64 columns).
  • Ambiguous dates fail silently: write an exact format= with strftime codes, or dayfirst=True. %m is month, %M minute.
  • errors="coerce" turns bad dates into NaT: count them and review.
  • .dt reads year, month, day, dayofweek (Monday = 0), day_name(), quarter and hour.
  • Moment − moment = Timedelta; add durations with pd.Timedelta(days=7).
  • A Period (to_period("M")) is a whole month.
  • Store times in UTC; tz_localize attaches a zone, tz_convert moves it (India is Asia/Kolkata, UTC + 5:30).
  • Next episode: cleaning 5, outliers, validation and sanity checks.