Summarizing Datetime Data in Pandas

Summarizing Datetime Data in Pandas

Summarizing datetime data is one of the most powerful and common operations in data science. Whether you need monthly sales trends, hourly usage patterns, day-of-week behavior, or rolling time windows, pandas provides fast, flexible tools to aggregate and analyze time-based data at any granularity.

TL;DR — Key Techniques

• Use .dt accessor to extract components (year, month, hour, weekday)
• groupby() + .dt for aggregation by date parts
• resample() for time-series resampling
• rolling() + Timedelta for time-based windows

1. Extracting Components and Basic Grouping

import pandas as pd

# Create a small demo file so this example runs as-is
# (in your project, sales_data.csv is your real export)
import numpy as np
rng = np.random.default_rng(42)
n = 60
pd.DataFrame({
    "order_id": range(1001, 1001 + n),
    "order_date": pd.date_range("2026-01-01", periods=n, freq="3D"),
    "customer_id": rng.integers(101, 121, n),
    "customer_name": rng.choice([" ana", "ben ", "chen", "dana"], n),
    "product_id": rng.integers(1, 9, n),
    "region": rng.choice(["North", "South", "East", "West"], n),
    "category": rng.choice(["Electronics", "Computers", "Books", "Home"], n),
    "payment_method": rng.choice(["card", "cash", "wallet"], n),
    "segment": rng.choice(["consumer", "business"], n),
    "quantity": rng.integers(1, 6, n),
    "amount": rng.uniform(50, 2500, n).round(2),
    "discount": rng.choice([0.0, 0.05, 0.1, 0.2], n),
}).assign(
    price=lambda d: (d["amount"] / d["quantity"]).round(2),
    profit=lambda d: (d["amount"] * rng.uniform(0.05, 0.3, n)).round(2),
).assign(
    ship_date=lambda d: d["order_date"] + pd.Timedelta(days=2),
    status=lambda d: np.where(d["amount"] > 2000, "priority", "standard"),
).to_csv("sales_data.csv", index=False)

df = pd.read_csv("sales_data.csv", parse_dates=["order_date"])

# Extract components
df["year"] = df["order_date"].dt.year
df["month"] = df["order_date"].dt.month
df["day_name"] = df["order_date"].dt.day_name()
df["hour"] = df["order_date"].dt.hour

# Monthly summary
monthly_sales = df.groupby(["year", "month"])["amount"].agg(["sum", "mean", "count"])
print(monthly_sales)

2. Time-Series Resampling with resample()

# Set datetime as index first
df = df.set_index("order_date")

# Resample to daily, weekly, monthly
daily = df.resample("D")["amount"].sum()
weekly = df.resample("W")["amount"].sum()
monthly = df.resample("ME")["amount"].sum()

print("Monthly sales:\n", monthly)

3. Rolling Time Windows

# 7-day rolling average
df["rolling_7d_avg"] = df["amount"].rolling("7D").mean()

# 30-day rolling sum
df["rolling_30d_sum"] = df["amount"].rolling("30D").sum()

4. Real-World Data Science Patterns

# Hourly pattern by day of week
hourly_pattern = df.groupby([df.index.day_name(), df.index.hour])["amount"].mean()

# Quarter-over-quarter growth
quarterly = df.resample("QE")["amount"].sum()
quarterly_growth = quarterly.pct_change()

5. Best Practices in 2026

• Always parse datetime columns early with parse_dates or pd.to_datetime
• Set the datetime column as index for resample() and rolling operations
• Use .dt accessor for flexible grouping by year, month, weekday, etc.
• Combine resample() and groupby() for powerful multi-level summaries
• Keep timezone-aware datetimes when working with global data

groupby, resample and rolling: Which One to Use

groupby with .dt components groups by labels such as month or weekday, so January 2025 and January 2026 can be combined if you group by month only. resample groups by consecutive time bins and fills empty bins, which suits time series charts. rolling computes moving statistics over a sliding window.

Since pandas 2.2, month-end and quarter-end bins use the aliases "ME" and "QE"; the older "M" and "Q" are deprecated. Time-based rolling windows such as "7D" need a sorted DatetimeIndex.

Choose the tool from the question: seasonality by weekday calls for groupby, a monthly trend for resample, and a smoothed daily line for rolling.

Worked Example: Weekday Pattern Versus Monthly Trend

A small daily series is summarised in three different ways.

import pandas as pd

days = pd.date_range("2026-01-01", periods=60, freq="D")
sales = pd.Series([100 + (d.day_of_week >= 5) * 50 for d in days], index=days)

by_weekday = sales.groupby(sales.index.day_name()).mean()
print(by_weekday[["Friday", "Saturday", "Sunday"]])

print(sales.resample("ME").sum())
print(sales.rolling("7D").mean().iloc[-1])

Weekend days average 150 and weekdays 100, which the weekday grouping shows clearly. resample gives one total per month, and the last seven-day rolling mean mixes both, giving about 114.29 (five weekdays and two weekend days).

Practical Tips

Before aggregating, sort the index with sort_index(). Several time-based operations assume sorted data, and sorting once early avoids confusing errors later in the pipeline.

Common Mistakes

• Grouping by month number and mixing different years.
• Using deprecated aliases such as "M" and "Q".
• Running time-based rolling on an unsorted index.
• Forgetting that resample creates empty bins.

Quick Summary

• groupby suits calendar labels.
• resample suits continuous time bins.
• rolling suits moving statistics.
• Use "ME" and "QE" in current pandas.

Conclusion

Summarizing datetime data in pandas is fast and flexible once you master .dt, resample(), rolling(), and groupby(). In 2026 data science projects, these tools let you turn raw timestamps into actionable insights — monthly trends, hourly patterns, rolling metrics, and time-based aggregations — with just a few lines of clean, high-performance code.

Next steps:

• Take one of your datasets and create monthly, weekly, and hourly summaries using resample() and groupby()

Related Tutorials

• All datetime operations in Pandas
• Additional datetime methods in Pandas