Parsing them, the .dt accessor, and why a date column read as text breaks everything downstream.
Overview
They arrive as text
Date and time handling is one of the areas where pandas adds the most value. A DatetimeIndex lets you select by month name, aggregate by week, compute rolling windows over calendar periods, and shift values by business days. None of that works until the column is actually a datetime type.
The problem is that dates almost always arrive as text. A CSV file has no date type. It has strings. The reader does not guess which strings are dates unless you tell it to. A column that looks like dates and sorts like dates is still a column of strings, and every date-aware operation will fail or give wrong results.
Detecting this is straightforward. If df.dtypes shows object for a column you expected to hold dates, it is text. Fixing it is one call to pd.to_datetime. After that, the column gains the dt accessor, which exposes year, month, weekday, hour and dozens of other properties.
The format argument matters when dates are not in ISO format. pd.to_datetime with no format uses a parser that guesses. That parser is slow on large columns and can guess wrong when day and month are ambiguous. Passing the format string explicitly is both faster and safer.
Timezone handling adds another layer. A naive datetime has no timezone. An aware datetime does. Mixing the two in a comparison or a merge raises or gives wrong results. The usual pattern is to parse as naive, then localize to the correct zone, then convert to UTC for storage and analysis.
This module covers parsing, the dt accessor, timezone handling, and the common mistakes that come from leaving dates as text. It starts with detection because the hardest part of the problem is noticing you have it.
read_csv doesn't parse dates unless you tell it to. A date column comes in as object. All you do with it's then string action wearing a date's clothes.
Sorting is lexical. For ISO format. YYYY-MM-DD. That happens to give the right answer. This Is why the problem often goes unnoticed until the data arrives in another format. "15/01/2024" sorts before "01/03/2024" as text, and there's no error.
compare has the same problem, as does anything asking for a month, a weekday or a gap in days.
The fix is pd.to_datetime, or parse_dates=["col"] at read time. This Is faster because it happens during the read.
Checking df.dtypes after loading catches this. This Is why it's in the inspection routine.
Worth knowing
A date column read as text sorts and compares lexically — which happens to work for ISO dates and fails for every other format.
pd.to_datetime guesses ambiguous formats; pass format= to remove the guess and make it much faster, or dayfirst=True.
errors="coerce" turns unparseable values into NaT instead of stopping the whole parse.
.dt is to timestamps what .str is to text — year, month, day_name(), quarter, strftime.
Subtracting timestamps gives a Timedelta; DateOffset understands calendar months in a way a fixed duration cannot.
A DatetimeIndex lets you slice by period — s["2024-02"] selects a whole month.
Dates and Times
Parsing them, the .dt accessor, and why a text date breaks all downstream.
Dates arrive as text
And text sorts and compares the wrong way, silently.
example_01.pypandas
import pandas as pd
s = pd.Series(["2024-03-01", "2024-01-15", "2024-11-02"])
print("dtype as read:", s.dtype)
print("sorted as text:", list(s.sort_values()))
print(" looks fine - ISO dates happen to sort correctly as text")
print()
bad = pd.Series(["01/03/2024", "15/01/2024", "02/11/2024"])
print("but any other format does not:")
print(" ", list(bad.sort_values()))
print()
d = pd.to_datetime(s)
print("after to_datetime:", d.dtype)
print(" ", list(d.sort_values().dt.strftime("%Y-%m-%d")))
Output
Parsing, and being explicit about format
Ambiguous formats are guessed, and the guess is not always yours.
example_02.pypandas
import pandas as pd
print("day-first vs month-first is genuinely ambiguous:")
print(" default :", pd.to_datetime("05/04/2024").strftime("%d %b %Y"))
print(" dayfirst :", pd.to_datetime("05/04/2024", dayfirst=True).strftime("%d %b %Y"))
print()
print("an explicit format removes the guess and is much faster:")
d = pd.to_datetime(["05/04/2024", "06/04/2024"], format="%d/%m/%Y")
print(" ", list(d.strftime("%Y-%m-%d")))
print()
print("bad values stop the parse unless you say otherwise:")
mixed = pd.Series(["2024-01-01", "not a date", "2024-02-01"])
try:
pd.to_datetime(mixed)
except Exception as e:
print(" ->", type(e).__name__)
print(" errors='coerce':", list(pd.to_datetime(mixed, errors="coerce")))
Output
The .dt accessor
The same idea as .str, for the parts of a timestamp.
example_03.pypandas
import pandas as pd
s = pd.to_datetime(pd.Series([
"2024-01-15 09:30:00", "2024-06-02 18:45:00", "2024-11-30 23:05:00"]))
print("year :", list(s.dt.year))
print("month :", list(s.dt.month))
print("day :", list(s.dt.day))
print("hour :", list(s.dt.hour))
print("weekday:", list(s.dt.day_name()))
print("quarter:", list(s.dt.quarter))
print()
print("date drops the time part:", list(s.dt.date))
print("normalize keeps it a timestamp at midnight:")
print(" ", list(s.dt.normalize().dt.strftime("%Y-%m-%d %H:%M")))
print()
print("format for output with strftime:")
print(" ", list(s.dt.strftime("%d %b %Y")))
Output
just parallel to .str.
s.dt.year, .month, .day, .hour, .minute pull out parts as ints.
s.dt.day_name() and s.dt.month_name() give text.
s.dt.quarter, .dayofweek, .dayofyear, .is_month_end cover the derived questions that would else need math.
s.dt.date drops the time and returns Python date objects. Note that this gives an object column. This Is often not what you want. s.dt.norm() keeps it a proper datetime column set to midnight. Is the better choice for grouping by day.
s.dt.strftime(fmt) formats for output, returning text. That is the last step before display, not a thing to do in the middle of a pipeline.
Arithmetic gives durations
Subtracting timestamps produces a Timedelta, which has its own accessor.
example_04.pypandas
import pandas as pd
start = pd.to_datetime(pd.Series(["2024-01-01", "2024-03-15"]))
end = pd.to_datetime(pd.Series(["2024-01-11", "2024-04-01"]))
gap = end - start
print("difference:", list(gap))
print("dtype :", gap.dtype)
print()
print("as numbers:")
print(" days :", list(gap.dt.days))
print(" hours :", list((gap.dt.total_seconds() / 3600).astype(int)))
print()
print("add a duration:")
print(" ", list((start + pd.Timedelta(days=30)).dt.strftime("%Y-%m-%d")))
print()
print("offsets understand calendars in a way timedeltas do not:")
print(" +1 month:", list((start + pd.DateOffset(months=1)).dt.strftime("%Y-%m-%d")))
Output
A DatetimeIndex unlocks time selection
Put the dates in the index and you can slice by period.
example_05.pypandas
import pandas as pd
import numpy as np
idx = pd.date_range("2024-01-01", periods=90, freq="D")
s = pd.Series(np.arange(90), index=idx)
print("select a whole month by string:")
print(" len(s['2024-02']) =", len(s["2024-02"]))
print()
print("a range, inclusive of both ends:")
print(" len(s['2024-01-10':'2024-01-20']) =", len(s["2024-01-10":"2024-01-20"]))
print()
print("date_range builds the axis:")
print(" ", list(pd.date_range("2024-01-01", periods=3, freq="ME").strftime("%Y-%m-%d")))
print(" ", list(pd.date_range("2024-01-01", periods=3, freq="W").strftime("%Y-%m-%d")))
Output
Time zones, briefly
Naive and aware timestamps do not mix, and the error is worth meeting once.
example_06.pypandas
import pandas as pd
naive = pd.to_datetime(pd.Series(["2024-01-01 12:00"]))
aware = naive.dt.tz_localize("UTC")
print("naive:", list(naive.astype(str)))
print("aware:", list(aware.astype(str)))
print()
print("converting to another zone:")
print(" ", list(aware.dt.tz_convert("Asia/Kolkata").astype(str)))
print()
try:
naive - aware
except Exception as e:
print("naive - aware ->", type(e).__name__)
print(" ", str(e)[:62])
print()
print("Pick one and stay there. Storing UTC and converting only for")
print("display is the convention that causes the fewest surprises.")
Output
Parsing
pd.to_datetime is good at guessing, and guessing is the problem.
05/04/2024 is 5 April in most of the world and 4 May in the United States. pandas has to pick one. It won't always pick yours.
dayfirst=True states the rule. Better still, format="%d/%m/%Y" states the whole shape. That drops the ambiguity and is much faster. Pandas can skip inference fully. On a large column the gap is large enough to notice.
For messy input, errors="coerce" turns unparseable values into NaT (the datetime equal of NaN) rather than raising on the first bad row. Then df[df["date"].isna()] shows you just what failed. This Is far more handy than a traceback naming one value.
NaT behaves like NaN: it fails each compare and is caught by isna().
Arithmetic
Subtracting two datetime columns gives a timedelta64 column.
gap.dt.days gives whole days. gap.dt.total_seconds() gives the full duration as a float. This Is what you want for anything sub-day or for converting to any units.
Adding a fixed duration uses pd.Timedelta(days=30).
Adding a calendar period uses pd.DateOffset(months=1). The gap matters. A month isn't a fixed number of days. "one month after 31 January" is a calendar question, not an math one. DateOffset handles month ends and leap years. Timedelta can't. It doesn't know what month it's in.
DatetimeIndex
Putting the dates in the index opens up the time-aware pick that makes pandas pleasant for time series.
s["2024-02"] selects the whole of February. s["2024"] selects the year. This is partial string indexing. It works because pandas understands the index is a timeline.
s["2024-01-10":"2024-01-20"] slices a range, inclusive of both endpoints as label slicing always is.
pd.date_range(start, periods=n, freq=...) builds such an index. The rate aliases are worth knowing: D daily, W weekly, ME month end, MS month start, h hourly. Note that pandas 2.2 renamed a few of these. M became ME and H became h. So older code raises a deprecation warning or an error based on version.
A DatetimeIndex is also what resample needs. This Is the subject of a later module.
Time zones
A timestamp is either naive (no zone) or aware (a zone attached). The two can't be checked or subtracted. The error when you try is clear.
s.dt.tz_localize("UTC") attaches a zone to naive timestamps, asserting what they always meant. s.dt.tz_convert("Asia/Kolkata") converts an aware timestamp to another zone.
Getting these backwards is the usual mistake: tz_localize on data that is already aware raises. tz_convert on naive data raises too.
The rule that causes the fewest problems is to store all in UTC and convert only for display. Mixed-zone data in one column isn't representable as a proper datetime dtype at all. It falls back to object. Which is a strong hint that norming early is the right move.
Parsing performance
Date parsing is one of the slowest parts of loading a large file, and it's almost fully avoidable.
pd.to_datetime without a format tries to infer one, per value in the worst case. With format= it uses a single known pattern and runs far faster. Often by an order of magnitude on a large column.
format="ISO8601" handles the common ISO variants without full inference.
cache=True (the default for large inputs) helps enormously when the same date string repeats, as it does in any dataset with many rows per day.
Parsing at read time with parse_dates= is faster than parsing later. Pandas can do it while it already has the strings in hand.
Periods and offsets
Timestamp is a point in time. Period is a span. A whole month, a quarter, a year.
s.dt.to_period("M") converts timestamps to monthly periods. Grouping by that is often more natural than grouping by year and month apart, and it sorts right.
PeriodIndex supports math in period units: adding 1 to a monthly period gives the next month, without the day-of-month ambiguity that plagues timestamp math.
Offsets sit between the two. pd.offsets.MonthEnd(1), BusinessDay(3), Week(weekday=0) show calendar movements. df["date"] + pd.offsets.MonthEnd(0) snaps each date to the end of its month. This Is a common alignment step before joining monthly data.
Business-day offsets understand weekends, and can take a holiday calendar. This Is the sort of thing that is tedious to write by hand and easy to get subtly wrong.
Components, and the leap-year trap
.dt.year, .month, .day are obvious. Two aren't.
.dt.isocalendar() returns ISO year, week and day. This Is what you want for week-based reporting. The ISO year is not always the calendar year. The first days of January can belong to the previous ISO year. So grouping by .dt.year and ISO week together makes wrong groups at the boundary.
.dt.dayofweek is 0 for Monday. .dt.day_name() gives the name and respects locale settings.
finding an age or a duration in years by cutting days by 365 is wrong by a day each four years and builds up. For exact calendar differences, subtract periods or use dateutil.relativedelta.
Time zones, in more depth
The rule that avoids nearly all trouble: store UTC, convert for display.
tz_localize attaches a zone to naive timestamps, asserting what they always meant. It raises on data that is already aware.
tz_convert moves an aware timestamp to another zone. It raises on naive data.
Getting these the wrong way round is the usual error. The messages are clear about which you needed.
Two edge cases are worth knowing because they're real and they raise:
Ambiguous times — when clocks go back, an hour occurs twice. ambiguous="infer", True, False, or NaT decides.
Nonexistent times — when clocks go forward, an hour doesn't exist. nonexistent="shift_forward" or NaT decides.
Both only appear on real local-time data crossing a DST boundary. This Is just when you least want a surprise.
A column mixing time zones can't be a proper datetime dtype and falls back to object, losing .dt fully. Norming on the way in blocks that.
A checklist for date columns
Parse them at read time, with an clear format.
Check dtype later — datetime64[ns] means it worked, object means it didn't.
Check the range with min() and max(). Dates in 1970 often mean a zero timestamp. Dates in 2099 often mean a sentinel.
Decide on a time zone and apply it once.
Set the index if you'll resample or select by period.
Sort, if anything downstream will diff, shift or rolling.
Common date mistakes
Not parsing at all. The column stays object, sorts lexically, and .dt raises. Check dtypes.
Letting the format be inferred on ambiguous data. 05/04 is two other dates based on rule.
Cutting days by 365 to get years. Wrong by a day each four years, and it builds up.
**Grouping by .dt.year and ISO week together.** The ISO year differs from the calendar year at the start of January. The boundary weeks land in the wrong group.
Mixing naive and aware timestamps. Raises. This Is the good case. The bad case is a column that falls back to object and loses .dt.
**Using .dt.date** and getting an object column. .dt.norm() keeps it a datetime.
given rows are sorted.diff and rolling give numbers even so.
Working with durations
A timedelta64 column comes from subtracting two datetime columns.
gap.dt.days truncates toward zero and discards the remainder. gap.dt.total_seconds() keeps all and is the safer basis for converting to any units.
gap.dt.parts breaks a duration into days, hours, minutes and so on as split columns.
Durations aggregate: mean, sum and show all work, and print in a readable form.
For business durations — elapsed working days rather than calendar days — np.busday_count is the tool. It takes a holiday list.
Generating date ranges
pd.date_range(start, end) or pd.date_range(start, periods=n, freq=...) builds an axis.
Three uses worth knowing:
Reindexing onto a full calendar. So Missing days become clear NaN rows rather than being absent.
Building test data with a known shape.
Checking for gaps — checking the actual index against a full range shows just which periods are missing:
full = pd.date_range(s.index.min(), s.index.max(), freq="D")
missing = full.difference(s.index)
That is a better answer than counting rows, because it names the gaps.
pd.bdate_range does the same for business days.
A summary
Parse at read time with an clear format.
Check the dtype is datetime64[ns] later, and check the min and max for sentinel dates.
.dt for parts, strftime only for display.
Timedelta for fixed durations, DateOffset for calendar ones.
Store UTC. Convert for display.
Set a DatetimeIndex when you need period pick or resample.
Sort before anything that checks a row with its neighbour.
And note pandas 2.2 renamed the rate aliases. M to ME, H to h. Which is the usual reason a copied example no longer runs.
Questions people ask about Dates and Times
How do I convert a text column to dates? Use pd.to_datetime(df['col']). Pass format='%Y-%m-%d' for speed and certainty, and errors='coerce' to turn bad values into NaT.
How do I get the year, month or weekday from a date column? Use the .dt accessor, for example df['d'].dt.year, .dt.month or .dt.day_name().
Why are my day and month swapped? Ambiguous strings such as 03/04/2024 are read month-first unless you pass dayfirst=True or an explicit format.
What is the difference between Timestamp and Timedelta? A Timestamp is a point in time and a Timedelta is a duration. Subtracting two Timestamps gives a Timedelta.
How do I handle time zones? Use tz_localize to attach a zone to naive times, and tz_convert to change between zones. Store timestamps in UTC where you can.
Why is to_datetime slow on my column? Without a format, pandas has to infer the pattern, which takes longer on large columns. Giving format= makes parsing much faster.
A closing note on Dates and Times
Dates arrive as text, and all that follows depends on noticing that.
An unparsed date column sorts lexically, can't do math. Has no .dt. For ISO-formatted dates the lexical sort happens to be correct. This Is just why the problem often lives until the data arrives in a other format.
Parsing with an clear format= drops both the ambiguity between day-first and month-first and most of the parsing cost. This On a large column is large.
After that, the two things worth being deliberate about are time zones and calendar math. Store UTC and convert for display. Mixed-zone columns can't be a proper datetime dtype at all. And use DateOffset rather than Timedelta when you mean a calendar month. A month isn't a fixed number of days and only one of the two knows that.
Finally, pandas 2.2 renamed the rate aliases. When a copied example stops working, that is often why.
Check yourself
0 of 4
Answer without scrolling back up.
Why does a date column read as text often seem to sort correctly?
The bug hides until the data arrives in another format - '15/01/2024' sorts before '01/03/2024' as text, with no error.
Why pass `format=` to `pd.to_datetime`?
05/04/2024 is 5 April in most of the world and 4 May in the US, and pandas has to pick one. An explicit format also skips inference.
What is the difference between `pd.Timedelta(months=1)` and `pd.DateOffset(months=1)`?
'One month after 31 January' is a calendar question. Timedelta cannot answer it because it does not know what month it is in.
What does `s['2024-02']` do on a Series with a DatetimeIndex?
Partial string indexing works because pandas understands the index is a timeline. s['2024'] selects the whole year.
Cheat sheet
Dates and Times
Parse date strings into pandas datetime, use the dt accessor for components and resample time series. Fix the text dates that break analysis.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.