Programmer's Picnic · Pandas 0 to Infinity · Level 7

Dates, Time Series, Charts and Reporting

Dates turn ordinary tables into timelines. Learn to parse them correctly, filter periods, aggregate by week or month, calculate moving statistics, plot trends and export useful reports.

Intermediate · Datetime analysis · Time-series reporting

1. Dates are data types, not decoration

A value such as 2026-10-23 may look like a date, but if Pandas stores it as ordinary text you cannot reliably group by month, subtract dates or use time-aware indexing.

Create dated sales

import pandas as pd

sales = pd.DataFrame({
    "Date": ["2026-10-01", "2026-10-02", "2026-10-03"],
    "Amount": [1200, 1500, 900]
})

sales["Date"] = pd.to_datetime(sales["Date"])
print(sales.dtypes)

2. to_datetime(): convert safely

Strict parsing

df["Date"] = pd.to_datetime(
    df["Date"],
    format="%Y-%m-%d",
    errors="raise"
)

errors="raise" is useful when invalid dates should stop the pipeline. errors="coerce" converts invalid values to missing datetimes (NaT) for later auditing.

Audit invalid dates

parsed = pd.to_datetime(df["Date"], errors="coerce")
bad = df.loc[parsed.isna(), "Date"]
print(bad)

3. Parse dates while reading CSV

CSV + datetime in one step

df = pd.read_csv(
    "daily_sales.csv",
    parse_dates=["Date"]
)

Download the Level 7 daily_sales.csv sample

4. The .dt accessor

Extract date parts

df["Year"] = df["Date"].dt.year
df["Month"] = df["Date"].dt.month
df["Day"] = df["Date"].dt.day
df["DayName"] = df["Date"].dt.day_name()
df["Quarter"] = df["Date"].dt.quarter

The .dt accessor exposes datetime components without converting the original timestamp back to strings.

5. Filter by date ranges

Boolean date filter

start = pd.Timestamp("2026-10-05")
end = pd.Timestamp("2026-10-12")

period = df[df["Date"].between(start, end)]
print(period)

Compare datetimes with datetimes—not formatted strings—when building robust analytical code.

6. DatetimeIndex

Use Date as an index

ts = df.set_index("Date").sort_index()
print(ts.loc["2026-10-05":"2026-10-12"])

A sorted DatetimeIndex makes time slicing and resampling natural.

7. date_range(): generate a timeline

Create daily dates

days = pd.date_range(
    start="2026-10-01",
    end="2026-10-07",
    freq="D"
)
print(days)

Frequency aliases describe periods such as days, hours and month-end dates. Use explicit frequencies that match the business meaning of your data.

8. Reindex to expose missing dates

Complete the calendar

ts = df.set_index("Date").sort_index()

calendar = pd.date_range(
    ts.index.min(),
    ts.index.max(),
    freq="D"
)

complete = ts.reindex(calendar)
print(complete)

A missing date and a recorded zero are different facts. Decide deliberately whether a missing date should remain missing or be filled.

9. resample(): daily data to weekly or monthly summaries

Monthly totals

ts = df.set_index("Date").sort_index()

monthly = ts["Amount"].resample("ME").sum()
print(monthly)

Weekly summary

weekly = ts["Amount"].resample("W").agg(["sum", "mean", "count"])
print(weekly)

10. groupby with date periods

Month-level grouping

df["Month"] = df["Date"].dt.to_period("M")

summary = df.groupby("Month")["Amount"].sum()
print(summary)

Period-based grouping is useful when you want labels such as 2026-10 rather than timestamp endpoints.

11. rolling(): moving windows

3-day moving average

ts = df.set_index("Date").sort_index()
ts["MA_3"] = ts["Amount"].rolling(window=3).mean()
print(ts)

Moving averages smooth short-term fluctuations. They do not predict the future by themselves.

12. Time-based rolling windows

Seven-calendar-day sum

ts["Last7Days"] = ts["Amount"].rolling("7D").sum()

A row-count window and a calendar-time window answer different questions when dates are irregular.

13. shift(): compare with previous periods

Previous day's amount

ts["Previous"] = ts["Amount"].shift(1)
ts["Change"] = ts["Amount"] - ts["Previous"]
print(ts)

14. pct_change(): percentage change

Daily percentage movement

ts["PctChange"] = ts["Amount"].pct_change() * 100
print(ts[["Amount", "PctChange"]])

Percentage change is undefined when the previous value is missing, and needs special care when the previous value is zero.

15. Time zones: know when they matter

Local business dates may be enough for a simple daily report. Event timestamps from servers, APIs or multiple regions require explicit timezone handling.

Timezone-aware example

times = pd.to_datetime([
    "2026-10-23 09:00",
    "2026-10-23 10:00"
])

times = times.tz_localize("Asia/Kolkata")
print(times)

Do not localize timestamps blindly: first determine whether source times are naive local times or already represent a timezone such as UTC.

16. Plot directly from Pandas

Pandas plotting uses Matplotlib underneath for common charts.

Line chart

import matplotlib.pyplot as plt

ts["Amount"].plot(
    title="Daily Sales",
    xlabel="Date",
    ylabel="Amount"
)

plt.tight_layout()
plt.show()

17. Bar charts for categories

City totals

city = df.groupby("City")["Amount"].sum().sort_values()

city.plot(
    kind="bar",
    title="Sales by City",
    ylabel="Amount"
)

plt.tight_layout()
plt.show()

18. Plot several series carefully

Actual vs moving average

ts[["Amount", "MA_3"]].plot(
    title="Daily Sales and 3-Day Moving Average"
)

plt.tight_layout()
plt.show()

A chart should answer a question. Avoid adding lines simply because they can be plotted.

19. Save charts for reports

Save PNG

ax = ts["Amount"].plot(title="Daily Sales")
fig = ax.get_figure()
fig.tight_layout()
fig.savefig("daily_sales.png", dpi=160)

20. Reporting with DataFrames

A useful report often contains a headline summary, a grouped table, an exception list and one or two charts.

Build report tables

headline = pd.DataFrame({
    "Metric": ["Total", "Average", "Best Day"],
    "Value": [
        df["Amount"].sum(),
        df["Amount"].mean(),
        df.loc[df["Amount"].idxmax(), "Date"]
    ]
})

city_report = (
    df.groupby("City")["Amount"]
      .agg(["sum", "mean", "count"])
      .sort_values("sum", ascending=False)
)

21. Export reports to CSV and HTML

Reusable outputs

city_report.to_csv("city_report.csv")

html = city_report.to_html(
    border=0,
    classes="report-table"
)

with open("city_report.html", "w", encoding="utf-8") as f:
    f.write(html)

HTML export is useful for simple browser-readable reports. For richer documents, combine Pandas with a dedicated reporting or templating workflow.

22. End-to-end time-series report

Load → validate → analyse → chart → export

from pathlib import Path
import pandas as pd
import matplotlib.pyplot as plt

HERE = Path(__file__).resolve().parent
df = pd.read_csv(HERE / "daily_sales.csv", parse_dates=["Date"])

required = {"Date", "City", "Amount"}
missing = required.difference(df.columns)
if missing:
    raise ValueError(f"Missing columns: {sorted(missing)}")

df["Amount"] = pd.to_numeric(df["Amount"], errors="raise")
df = df.sort_values("Date")

daily = df.groupby("Date", as_index=True)["Amount"].sum().to_frame()
daily["MA_3"] = daily["Amount"].rolling(3).mean()
daily["PctChange"] = daily["Amount"].pct_change() * 100

monthly = daily["Amount"].resample("ME").sum()
city = df.groupby("City")["Amount"].sum().sort_values(ascending=False)

daily.to_csv(HERE / "daily_report.csv")
city.to_csv(HERE / "city_report.csv", header=["Amount"])

ax = daily[["Amount", "MA_3"]].plot(title="Daily Sales")
ax.get_figure().tight_layout()
ax.get_figure().savefig(HERE / "daily_sales.png", dpi=160)

print(daily)
print(monthly)
print(city)

Open the runnable Level 7 report program

23. Common mistakes

Mistake	Why it matters	Better approach
Date column left as object/string	Time operations become unreliable	Parse explicitly
Invalid dates silently ignored	Bad source data disappears from attention	Audit `NaT` values
Unsorted DatetimeIndex	Time slicing/rolling logic can confuse readers	Sort before analysis
Missing day treated as zero	Absence of record becomes a false business fact	Decide fill policy explicitly
Row rolling used as calendar rolling	Irregular dates distort the window	Use time-based windows where needed
Charts without labels	Report becomes ambiguous	Add title, axes and units
Timezone guessed	Events can shift to wrong date/hour	Document source timezone

24. Practice exercises

Parse a column containing ISO dates and list invalid rows.
Extract year, month and weekday.
Filter one exact week.
Set a DatetimeIndex and slice a date range.
Find missing calendar dates.
Calculate weekly and monthly totals.
Create a 7-day rolling mean.
Compare each day with the previous day using shift().
Calculate percentage change.
Plot a daily line chart and save it as PNG.
Create a city summary and export it to CSV and HTML.
Explain when timezone-aware timestamps are necessary.

25. Mini project: Monthly Operations Report

Use a CSV of dated orders or sales. Validate dates and amounts, detect missing days, produce daily/weekly/monthly summaries, calculate a moving average, find best/worst days, compare cities, save at least one chart, and export both CSV and HTML summaries.

26. What you should now understand

You should be comfortable with to_datetime(), DatetimeIndex, .dt, date filtering, date_range(), resample(), periods, rolling windows, shift(), pct_change(), basic timezones, plotting and reusable report outputs.

Official references: Time series / date functionality · Chart visualization.

← Level 6: Merge & Reshape Course index Advanced: Pandas + SQL →