skip to content

Temporal Data

Datetime parsing, time zones, resampling and rolling windows over a DatetimeIndex. Interviewers ask resample versus rolling and what 3.0 changed in resolution and aliases.

on this pageshow

explore

questions

12

In pandas, how do you turn a text date column into real datetimes and then pull out its year and weekday?

level: juniorimportance: must knowfreq 70%

answer

  1. convert first, then read parts
  2. a function at the top level of pandas
  3. an accessor for datetime Series
  4. Monday counts as zero

basics

~10 s

Convert the column with pd.to_datetime and assign it back, then read its parts through the .dt accessor: .dt.year for the year and .dt.dayofweek (Monday is 0) or .dt.day_name() for the weekday.

solid answer

~30 s

I parse once with `df["event_date"] = pd.to_datetime(df["event_date"])`, which gives a `datetime64` column instead of text. From then on the `.dt` accessor exposes vectorised parts: `.dt.year`, `.dt.month`, `.dt.hour`, `.dt.dayofweek` (Monday is 0, Sunday is 6) and `.dt.day_name()` for names. The same accessor rounds with `.dt.floor("h")` and formats with `.dt.strftime("%d %b %Y")`, which returns text again. If I skip the conversion, `.dt` raises `AttributeError: Can only use .dt accessor with datetimelike values`. Subtracting two datetime columns gives a timedelta column, and `.dt.total_seconds()` turns that into numbers.

code

python · 17 lines
python
import pandas as pd

df = pd.DataFrame({
    "event_date": ["2025-03-14", "2025-03-15", "2025-03-16"],
    "clicks": [3, 5, 2],
})

df["event_date"] = pd.to_datetime(df["event_date"])

df["year"] = df["event_date"].dt.year
df["weekday"] = df["event_date"].dt.dayofweek
df["day_name"] = df["event_date"].dt.day_name()
print(df)

started = pd.to_datetime(pd.Series(["2025-03-14 09:00"]))
ended = pd.to_datetime(pd.Series(["2025-03-14 18:30"]))
print((ended - started).dt.total_seconds())

go deeper

for a junior

Recall the two steps: pd.to_datetime to convert and assign back, then .dt.year, .dt.dayofweek or .dt.day_name() to read parts. Know that dayofweek starts at Monday as 0.

for a middle

Explain why .dt raises on text columns, the attribute-versus-method split in the accessor, and how subtracting datetimes yields a timedelta column read through total_seconds().

for a senior

Show the habit of converting once at ingest and checking dtypes, and why vectorised .dt members beat apply with a lambda on large frames.

for a principal

Treat parsing at the pipeline boundary as a team convention, so no later step ever sees dates as text and every report agrees on what a day is.

## Why text dates must be converted first A date read from a file or typed into a constructor usually arrives as **text**: the column holds strings such as `"2025-03-14"`. Strings sort alphabetically, cannot be subtracted, and have no notion of a year or a weekday. pandas stores real points in time in a **`datetime64`** column, where each value is a `Timestamp` (pandas' scalar for one instant). The conversion function is **`pd.to_datetime`**: ```python df["event_date"] = pd.to_datetime(df["event_date"]) ``` Three habits matter here: - **Assign the result back.** `pd.to_datetime` returns a new Series; it never changes the column in place. - **Convert once, near the start** of the pipeline, so every later step works on real datetimes. - **Check the dtype** with `df.dtypes` after converting: a `datetime64[...]` entry means it worked, while text means the conversion was skipped or its result was never assigned back. For a single value, `pd.Timestamp("2025-03-14")` builds the scalar directly, and `pd.to_datetime("2025-03-14")` returns the same kind of object. ## The .dt accessor Once the column is a datetime column, the **`.dt` accessor** gives vectorised access to its parts, one result per row, without a Python loop: | attribute or method | what it returns | |---|---| | `.dt.year`, `.dt.month`, `.dt.day` | integer calendar parts | | `.dt.hour`, `.dt.minute`, `.dt.second` | integer clock parts | | `.dt.dayofweek` (alias `.dt.weekday`) | 0 for Monday through 6 for Sunday | | `.dt.day_name()`, `.dt.month_name()` | names such as `"Friday"` | | `.dt.quarter`, `.dt.dayofyear` | calendar positions | | `.dt.is_month_end` and friends | boolean flags | | `.dt.floor("h")`, `.dt.round("h")` | datetimes snapped to a unit | | `.dt.strftime("%d %b %Y")` | formatted text | Note the difference between **attributes** (`year`, `dayofweek`, no parentheses) and **methods** (`day_name()`, `floor("h")`, `strftime(...)`, with parentheses). Mixing them up is a common slip in live coding. If the column is still text, any `.dt` call fails with `AttributeError: Can only use .dt accessor with datetimelike values`. That error almost always means the conversion step was skipped, or its result was not assigned back. ## Durations: Timedelta Subtracting one datetime column from another gives a **timedelta** column, where each value is a `Timedelta` (pandas' scalar for a duration). The `.dt` accessor works on it too, with duration-specific members: 1. `.dt.total_seconds()` returns the whole duration as a float number of seconds. 2. `.dt.days` returns only the whole-days component, not the full length. 3. `pd.Timedelta(hours=2)` or `pd.Timedelta("90min")` builds a scalar you can add to a datetime column. ## Generating dates: date_range To build a regular sequence of datetimes, for example a calendar to join against, use **`pd.date_range`** with a start and either an end or a number of `periods`, plus a `freq` such as `"D"` for daily: ```python pd.date_range("2025-03-01", periods=7, freq="D") ``` It returns a `DatetimeIndex`, which can become a column or the row labels of a frame. ## What interviewers listen for - The candidate converts with `pd.to_datetime` instead of slicing strings (`s.str[:4]` for the year is fragile and leaves the column as text). - They know `.dt` exists only on datetime-like columns, and they can explain the error when it is missing. - They remember that `dayofweek` counts from Monday as 0. - They do not reach for `apply` with a lambda per row when a `.dt` member already does the job; the accessor is vectorised and much faster. ## Common slips in a live round - Converting inside a loop, one value at a time, instead of passing the whole column to `pd.to_datetime`. - Forgetting that `.dt.strftime(...)` returns text, then trying to sort or subtract the result as if it were still a date. - Expecting `.dt.month_name()` or `.dt.day_name()` to give numbers; they give names, while `.dt.month` and `.dt.dayofweek` give integers. - Reading `.dt.days` on a duration as its full length, when it is only the whole-days part. Reading dates straight from a file with the `parse_dates` argument of `read_csv` belongs with CSV import, and parsing several text layouts with `format` and `errors` is the next step up; both build on the same idea of converting once and then using `.dt`.

  • What is the difference between .dt.days and .dt.total_seconds() on a timedelta column in pandas?
    `.dt.days` is only the whole-days component of each duration, so 1 day 6 hours reports 1. `.dt.total_seconds()` returns the entire duration as a float number of seconds, 108000.0 for the same value. For durations shorter or longer than a day, use `total_seconds()` and divide when you need hours or days as numbers.
  • Why is .dt.year better than df['event_date'].str[:4] for getting the year in pandas?
    Slicing text depends on every row sharing one layout, returns strings rather than integers, and leaves the column unusable for date arithmetic. Converting with `pd.to_datetime` validates the values once and `.dt.year` then returns integers that sort, filter and group correctly whatever layout the source used.

Converting a text date is like turning a written address into a map pin: once the pin exists, the map can tell you the district, the distance and the route, but a line of text on paper can only be read, not measured.

  • Uses .dt on a text column and expects it to work
  • Believes pd.to_datetime changes the column in place without assignment
  • Thinks dayofweek counts Sunday as 0
  • Calls day_name without parentheses as if it were an attribute
  • Extracts the year by slicing the date string
  • Reads .dt.days as the full duration in days
open as a page

In pandas, how do you roll minute-level web traffic up to hourly and daily totals with resample, and what does the result look like?

level: juniorimportance: must knowfreq 70%

basics

~10 s

Call resample with a rule on a frame that has a DatetimeIndex, then aggregate: traffic.resample('h').sum() and traffic.resample('D').sum(). The result has one row per hour or day, stamped with each bucket's start time.

open as a page

With pd.to_datetime in pandas 3.0, how do format, errors='coerce' and unit='s' handle an event log of mixed date strings and epoch seconds?

level: middleimportance: must knowfreq 60%

basics

~10 s

pd.to_datetime applies one format to the whole column, guessed from the first non-missing value unless format= is given; errors='coerce' turns non-matching rows into NaT, and unit='s' reads integers as seconds since 1970.

open as a page

In pandas, what is the difference between Series.dt.tz_localize and Series.dt.tz_convert, and when do you call each one?

level: middleimportance: must knowfreq 55%

basics

~10 s

tz_localize attaches a zone to naive datetimes and keeps their clock reading; tz_convert takes already-aware datetimes and shows the same instants in another zone. Localize once at ingest, convert whenever you need local display.

open as a page

In pandas, how do `rolling(7)` and `rolling('7D')` differ for a 7-day moving average of daily traffic that has missing days?

level: middleimportance: must knowfreq 65%

basics

~10 s

rolling(7) averages the last seven rows, however many days they span; rolling('7D') averages every row in the seven days ending at each timestamp. With missing days, only the offset window really means a week.

open as a page

In pandas, how do `asfreq` and `resample` differ when you change a series' frequency, and how do you fill the gaps after upsampling?

level: middleimportance: should knowfreq 42%

basics

~20 s

asfreq reindexes to the new frequency and keeps only values stamped exactly on the new timestamps; resample groups rows into buckets and aggregates them. After upsampling, new rows are NaN until you ffill, bfill or interpolate them.

open as a page

In pandas 3.0, tz_localize on a naive event log fails around a daylight-saving change in Europe/Berlin. What went wrong, and how do the ambiguous and nonexistent arguments fix it?

level: seniorimportance: should knowfreq 35%

basics

~20 s

At the autumn change local 02:00-02:59 happens twice, and in spring it never happens, so tz_localize raises ValueError by default. ambiguous ('infer', a boolean array or 'NaT') and nonexistent ('shift_forward', 'shift_backward', a Timedelta or 'NaT') choose the answer.

open as a page

After moving to pandas 3.0, epoch seconds computed as df['ts'].astype('int64') // 10**9 come out 1000 times too small. Why, and what is the robust fix?

level: seniorimportance: should knowfreq 40%

basics

~10 s

pandas 3.0 parses strings to microsecond resolution, datetime64[us], instead of always nanoseconds, so astype('int64') yields microseconds. Divide by a Timedelta, or call .dt.as_unit('s') before casting, so the code no longer assumes a unit.

open as a page

In pandas, why does `resample('W')` stamp weekly traffic totals with Sundays while `resample('h')` stamps each hour by its start, and how do `closed` and `label` change that?

level: seniorimportance: should knowfreq 38%

basics

~20 s

closed and label default to 'left' except for end-anchored rules ME, QE, YE, BME, BQE, BYE and W, which default to 'right'. So 'W' (W-SUN) buckets Monday to Sunday and stamps the Sunday; pass closed and label to change it.

open as a page

In pandas, how do `ewm(span=...)`, `ewm(halflife=...)` and `adjust` shape an exponentially weighted trend of daily web traffic?

level: seniorimportance: should knowfreq 30%

basics

~20 s

span and halflife are two ways to set the decay alpha (2/(span+1), or the weight halving every halflife rows); pass exactly one. adjust=True, the default, normalises weights over the available history; adjust=False uses the recursive form starting from the first value.

open as a page

A pandas 2.x pipeline calls `resample('M')` and `resample('H')`; what happens when it runs on pandas 3.0, and how do you fix it?

level: seniorimportance: should knowfreq 45%

basics

~20 s

In pandas 3.0 both raise ValueError: Invalid frequency. The aliases were removed: use 'ME' for month end (also QE, YE, BME, BQE, BYE, SME, CBME) and lowercase 'h' for hours (also min, ms, us, ns for T, L, U, N).

open as a page

As the lead of a pandas analytics team, would you standardise stored timestamps as tz-aware UTC or naive local time, and what does enforcing that cost?

level: principalimportance: should knowfreq 30%

basics

~20 s

Usually tz-aware UTC in storage with the IANA zone name in its own column, converting to local time only for display or local-calendar logic. It costs a disciplined ingest step for every naive source and per-zone conversion work later.

open as a page