Dates & the DatetimeIndex

By the end of this lesson you will be able to turn date text into real pandas dates, use them as the index of a table, and select any day, month or range with one short line. These skills sit under every later lesson, so they are worth getting exactly right.

The Dates & Time Series lesson in the Data Science course introduced dates in pandas. Here we focus on the parts that matter most for forecasting, and on the mistakes that quietly ruin a forecast.

Text is not a date

When you read a CSV file, a column such as 03-04-2025 arrives as plain text. Text sorts like words, not like days, so "10-01-2025" comes before "9-01-2025". You cannot subtract text or ask which weekday it is. You must parse it, which means converting the text into a real date value.

The trap for Indian data is the order of day and month. Indian bills, school records and railway tickets usually write day first: 03-04-2025 means 3 April. Many software defaults assume the American order, month first, which reads it as 4 March.

Step 1. Run the code and compare the two results.

import pandas as pd

raw = pd.Series(["03-04-2025", "15-04-2025", "01-05-2025"])   # day-month-year, as on Indian bills

wrong = pd.to_datetime(raw.iloc[:1])                 # pandas guesses month first
right = pd.to_datetime(raw, format="%d-%m-%Y")       # say exactly what the text means

print("Guessed:", wrong.iloc[0].date())
print(right)

Guessed: 2025-03-04
0   2025-04-03
1   2025-04-15
2   2025-05-01
dtype: datetime64[us]

You should see that the guess turned 3 April into 4 March. No error appeared, which makes this mistake dangerous: a whole month of sales could shift silently. With format="%d-%m-%Y" you tell pandas the exact layout: %d is the day, %m the month and %Y the four-digit year. The result is correct.

The type datetime64[us] means "dates stored to the microsecond". In pandas 3.0, dates parsed from text use this unit; older versions showed datetime64[ns] (nanoseconds). Both behave the same in everyday work.

The DatetimeIndex

A DatetimeIndex is a pandas index made of dates. Once your table has one, pandas understands time: you can select by month, resample, shift and plot with real date labels. Think of it like the page numbers in a diary. Without them you only have a pile of pages.

Step 2. Build a small table of daily boxes of mangoes sold in Lucknow, convert its date column, and set it as the index.

import numpy as np
import pandas as pd

rng = np.random.default_rng(3)
days = pd.date_range("2025-03-01", "2025-04-30", freq="D")
shop = pd.DataFrame({"date": days.strftime("%Y-%m-%d"),
                     "boxes": rng.integers(20, 60, len(days))})
print(shop.dtypes.to_string())

shop["date"] = pd.to_datetime(shop["date"])
shop = shop.set_index("date")
print(type(shop.index).__name__)

print("April rows:", len(shop.loc["2025-04"]))
print(shop.loc["2025-03-30":"2025-04-02"])

date       str
boxes    int64
DatetimeIndex
April rows: 30
            boxes
date             
2025-03-30     49
2025-03-31     50
2025-04-01     58
2025-04-02     51

After Step 2 you should see that the date column started as str (text). After to_datetime and set_index, the index is a DatetimeIndex. Two kinds of selection now work:

• Partial string: shop.loc["2025-04"] returns every day in April. pandas reads "2025-04" as "the whole month".
• Date slice: shop.loc["2025-03-30":"2025-04-02"] returns the range, and both ends are included. This differs from normal Python slices, where the end is left out.

Asking questions of dates

Each date knows its weekday, month and quarter. These parts become very useful later, when we build calendar features for a model.

Step 3. Print the parts of a week that includes Diwali 2025 (20 October 2025 in most calendars).

import pandas as pd

days = pd.date_range("2025-10-17", periods=7, freq="D")
info = pd.DataFrame({
    "day_name": days.day_name(),
    "dayofweek": days.dayofweek,
    "month": days.month,
    "quarter": days.quarter,
    "weekend": days.dayofweek >= 5,
}, index=days)
print(info)

             day_name  dayofweek  month  quarter  weekend
2025-10-17     Friday          4     10        4    False
2025-10-18   Saturday          5     10        4     True
2025-10-19     Sunday          6     10        4     True
2025-10-20     Monday          0     10        4    False
2025-10-21    Tuesday          1     10        4    False
2025-10-22  Wednesday          2     10        4    False
2025-10-23   Thursday          3     10        4    False

dayofweek counts from Monday = 0 to Sunday = 6, so dayofweek >= 5 marks Saturday and Sunday. When the dates are a column instead of the index, use the .dt accessor: df["date"].dt.day_name().

Date arithmetic

Subtracting two dates gives a Timedelta, a length of time. Adding a Timedelta to a date moves it forward.

Step 4. Priya books a train ticket on 14 August for travel on 20 October. How long is the gap?

import pandas as pd

booking = pd.Timestamp("2025-08-14 09:30")
travel = pd.Timestamp("2025-10-20 06:15")

gap = travel - booking
print("Gap:", gap)
print("Whole days:", gap.days)
print("One week later:", booking + pd.Timedelta(days=7))
print("One month later:", pd.Timestamp("2025-01-31") + pd.DateOffset(months=1))

Gap: 66 days 20:45:00
Whole days: 66
One week later: 2025-08-21 09:30:00
One month later: 2025-02-28 00:00:00

A Timedelta is a fixed length, such as exactly 7 days. A month is not a fixed length, so for months use pd.DateOffset(months=1), which follows the calendar. Adding one month to 31 January gives 28 February, the last real day, not an impossible "31 February".

Common mistakes

• Letting pandas guess day-first dates. Pass format= or the months and days swap silently.
• Leaving dates as text. Sorting, slicing and resampling all misbehave on strings.
• Forgetting that .loc date slices include both ends. "2025-03-30":"2025-04-02" gives four days, not three.
• Using Timedelta(days=30) for "one month". Use DateOffset(months=1) to follow the calendar.

Key points

• Parse date text with pd.to_datetime(..., format="..."); Indian data is usually day first.
• Set the parsed column as the index to get a DatetimeIndex.
• df.loc["2025-04"] selects a whole month; date slices include both ends.
• .day_name(), .dayofweek, .month and .quarter give calendar parts.
• Timedelta is a fixed length; DateOffset follows the calendar.

import pandas as pd

dates = pd.to_datetime(pd.Series(["05/06/2025", "28/06/2025", "02/07/2025"]), format="%d/%m/%Y")
print(dates.dt.day_name().tolist())
print("Days between first and last:", (dates.iloc[-1] - dates.iloc[0]).days)

['Thursday', 'Saturday', 'Wednesday']
Days between first and last: 27