lesson 214 pandas Transformations · advanced

Dates and times in a table

Timestamps read from a CSV are text. to_datetime turns them into real moments, so you can take them apart with .dt, subtract them, and group by month.

You'll be able to

Convert text to datetimes with pd.to_datetime(col, format=...), using errors="coerce" for bad values
Read parts (.dt.month, .dt.day_name()) and compute durations by subtracting columns
Group by month with .dt.to_period("M")

The idea

ordered_at arrives as text like "2024-01-16 13:03:50". Text sorts correctly in this format, but you can't ask its weekday or subtract two of them. pd.to_datetime(column, format="%Y-%m-%d %H:%M:%S") converts a whole column (the format codes are the strptime ones from the Dates Numbers & Regex module; giving the format makes parsing exact and fast). The result has a datetime dtype, and .dt gives the parts: .dt.month, .dt.hour, .dt.day_name(). Subtracting two datetime columns gives Timedeltas (durations).

Run each example to see what it prints. Running examples doesn't count toward progress.

example-1.py · Weekdays and delivery times

import pandas as pd

orders = pd.DataFrame({
    "order_id": ["O100282", "O100376", "O101219"],
    "ordered_at": ["2024-01-16 13:03:50", "2024-01-21 22:19:23", "2024-03-13 10:01:28"],
    "delivered_at": ["2024-01-18 07:35:32", "2024-01-24 20:02:08", "2024-03-19 01:26:45"],
})
stamp = "%Y-%m-%d %H:%M:%S"
ordered = pd.to_datetime(orders["ordered_at"], format=stamp)
delivered = pd.to_datetime(orders["delivered_at"], format=stamp)
print(ordered.dt.day_name().tolist(), ordered.dt.month.tolist())
took = delivered - ordered
print(took.iloc[0])
print((took.dt.total_seconds() / 86400).round(1).tolist())

example-2.py · Subtracting timestamps that are still text

import pandas as pd

orders = pd.DataFrame({"ordered_at": ["2024-01-16 13:03:50"], "delivered_at": ["2024-01-18 07:35:32"]})
print(orders["delivered_at"] - orders["ordered_at"])

example-3.py · Revenue per month, with one bad timestamp

import pandas as pd

orders = pd.DataFrame({
    "ordered_at": ["2024-01-16 13:03:50", "2024-01-21 22:19:23", "2024-02-25 22:28:23", "2024-02-30 10:00:00", "2024-03-13 10:01:28"],
    "total": [35.84, 91.47, 55.72, 12.0, 246.6],
})
when = pd.to_datetime(orders["ordered_at"], format="%Y-%m-%d %H:%M:%S", errors="coerce")
print(when.isna().sum(), "bad timestamp")
month = when.dt.to_period("M")
print(orders.groupby(month)["total"].sum().round(2))

When to use it: any time-based question: per month, per weekday, how long, how late. Mistakes to avoid: computing with text timestamps; letting pandas guess an ambiguous format (01/02/2024 is January or February depending on the country: pass format=); comparing a datetime with a date that still has its time part (normalise with .dt.normalize() to compare whole days); and dropping NaT silently without counting how many.

Review

pd.to_datetime(col, format=..., errors="coerce") turns text into datetimes (bad values become NaT).
.dt gives parts and methods; subtracting datetimes gives Timedeltas (.dt.total_seconds(), .days).
dt.to_period("M") groups by month; dt.normalize() compares whole days.
Next: find and fix duplicates and inconsistent values before trusting any of these numbers.

Practise this