Dates, time series & resampling

Sales, footfall, website traffic and stock prices are all time series. pandas has excellent date support once your dates are real datetime64 values rather than strings.

Parsing dates properly

import pandas as pd

df = pd.read_csv("daily_sales.csv")
df["date"] = pd.to_datetime(df["date"], format="%d-%m-%Y")   # explicit is safest
df = df.set_index("date").sort_index()

Passing an explicit format is faster and avoids silent day/month swaps. Bad values can be turned into NaT (missing time) with errors="coerce".

The .dt accessor

On a datetime column (not the index) use .dt to extract parts:

orders["month"] = orders["order_date"].dt.month
orders["weekday"] = orders["order_date"].dt.day_name()
orders["is_weekend"] = orders["order_date"].dt.dayofweek >= 5
orders["days_to_ship"] = (orders["ship_date"] - orders["order_date"]).dt.days

Subtracting two dates gives a Timedelta; .dt.days turns it into an integer.

Selecting by date

With a sorted DatetimeIndex, partial strings work:

df.loc["2025-10"]                   # all of October 2025
df.loc["2025-10-15":"2025-11-05"]   # an inclusive range around Diwali

Resampling: change the frequency

resample is a time-aware groupby. It works on a DatetimeIndex:

weekly = df["amount"].resample("W").sum()          # weeks ending Sunday
monthly = df["amount"].resample("ME").sum()        # month end
quarterly = df["amount"].resample("QE").agg(["sum", "mean"])

Alias	Meaning
D	Calendar day
W / W-MON	Weekly (ending Sunday / Monday)
ME / MS	Month end / month start
QE	Quarter end
YE	Year end
h	Hourly

Since pandas 2.2, "ME", "QE" and "YE" replace the older "M", "Q" and "Y" aliases, which now give a deprecation warning.

To group by something other than the index, use pd.Grouper:

df.reset_index().groupby(["branch", pd.Grouper(key="date", freq="ME")])["amount"].sum()

Indian financial year

Financial years run April to March. A period with a March year-end handles this neatly:

fy = df.index.to_period("Y-MAR")          # April 2025 -> FY ending March 2026
df.groupby(fy)["amount"].sum()

Rolling windows, shifts and growth

daily = df["amount"].resample("D").sum()
daily_7 = daily.rolling(7).mean()                 # 7-day moving average
wow = daily.pct_change(periods=7) * 100            # vs same day last week (%)
yesterday = daily.shift(1)                         # previous day's value

A 7-day moving average smooths weekday/weekend patterns, which makes real trends easier to see.

Time zones

Server logs are often in UTC. Convert before analysing daily patterns in India:

logs["ts"] = pd.to_datetime(logs["ts"], utc=True).dt.tz_convert("Asia/Kolkata")

Common mistakes

• Leaving dates as strings, so sorting puts "10-01-2025" before "2-01-2025".
• Resampling a DataFrame without a DatetimeIndex (you get a TypeError).
• Using .sum() for gaps in daily data: missing days become 0 after resampling, which may or may not be correct.
• Forgetting UTC conversion, so late-night Indian orders land on the wrong date.