DA-01-015-P · Primer — Trends Over Time with Line Charts

The idea in one sentence

A time-series line chart puts real dates on the x-axis and your metric on the y-axis and connects the dots in time order, turning a lifeless column of dates and numbers into a shape your eye reads instantly — a trend that climbs or falls, a season that repeats, and a jump where something changed.

Why it exists

Almost every metric a business cares about is measured over time — daily signups, weekly revenue, hourly server errors — and the single most common question anyone asks about such a metric is "is it going up or down, and when did that change?" You cannot answer that by scanning a column of numbers, because the eye can't hold fifty dates in memory or spot that every seventh value dips. A line chart exists to do that holding for you: by placing points at their true position in time and drawing a line between them, it converts the pattern into slope (trend), rhythm (seasonality), and steps or spikes (jumps and anomalies) that you recognize in a single glance. But the chart is only honest if the machine truly understands your dates as time — not as text, not as row order — which is why the real skill here is two-part: first datetime handling (teaching pandas that "2024-01-15" is a moment on a number line, not a string), then choosing the right view of that time — every raw point, a resampled summary per month, or a rolling average that smooths the jitter so the trend underneath shows through. Get the dates right and pick the right zoom, and the story tells itself.

How it works

The workflow is always the same three moves: parse, index, view. First you parse the date column with pd.to_datetime(...) so pandas stores real timestamps instead of strings — now it knows January comes before March and can sort, slice, and space the axis correctly. Then you make that column the index with .set_index("date"), which turns an ordinary table into a time series that pandas can resample and roll. Finally you pick a view: plot every raw point to see detail, .resample("MS").sum() to roll fine-grained rows up into month totals (the trend), or .rolling(window).mean() to average out the noise so the underlying direction is visible. Then .plot() draws it, connecting points in time order along a proper date axis. Here we take twelve weekly signup counts that arrived as text and do all three:

import matplotlib
matplotlib.use("Agg")            # headless backend; omit in a notebook
import pandas as pd
import matplotlib.pyplot as plt

# 12 weeks of signups; the date column arrives as TEXT, as it would from a CSV
raw = pd.DataFrame({
    "date": ["2024-01-01","2024-01-08","2024-01-15","2024-01-22","2024-01-29",
             "2024-02-05","2024-02-12","2024-02-19","2024-02-26","2024-03-04",
             "2024-03-11","2024-03-18"],
    "signups": [40, 55, 48, 62, 71, 68, 90, 85, 110, 102, 130, 125],
})

raw["date"] = pd.to_datetime(raw["date"])     # STEP 1: text -> real timestamps
s = raw.set_index("date")["signups"]          # STEP 2: datetime index -> a time series
monthly = s.resample("MS").sum()              # STEP 3: roll weekly rows up to month totals
print(monthly.tolist())                       # -> [276, 353, 357]  (each month higher: an upward trend)

fig, ax = plt.subplots()
s.plot(ax=ax, marker="o")                      # the line climbs left-to-right...
ax.set_ylabel("signups"); ax.set_title("Signups over time")
# ...the chart shows a clear upward trend from ~40 in January to ~125 by mid-March

The number (276 -> 353 -> 357) and the rising line tell the same growth story, because the date axis is now real time.

The one gotcha

A line chart connects points in row order, not date order — so if your rows are not sorted by date, the line zig-zags backwards through time and paints a completely fake trend. This bites constantly, because dates come out of joins, exports, and grouped queries in whatever order the database felt like, and .plot() will dutifully draw a line from March to January to February if that's the order the rows sit in. The chart looks like a real trend, but it's an artifact of shuffled rows, not of time:

import pandas as pd

# dates arrive OUT OF ORDER (common after a join or a messy export)
df = pd.DataFrame({"date": ["2024-03-01", "2024-01-01", "2024-02-01"],
                   "value": [30, 10, 20]})
df["date"] = pd.to_datetime(df["date"])
print(df["value"].tolist())                    # -> [30, 10, 20]  a fake down-then-up jag

df = df.sort_values("date")                     # THE FIX: sort by date BEFORE plotting
print(df["value"].tolist())                     # -> [10, 20, 30]  the honest rising line

The cure is a one-liner you should make a reflex: df.sort_values("date") (or .sort_index() once the date is the index) before you ever plot a time series. Two cousins ride along with this same discipline. First, if your dates are still strings, pandas sorts them alphabetically — which happens to work for YYYY-MM-DD but breaks the instant a format like "3/1/2024" shows up, so always to_datetime first. Second, resampling and rolling both assume the series is sorted and regularly spaced; feed them scrambled or gap-riddled dates and the "monthly total" or "7-day average" silently covers the wrong window. Parse to real datetimes, sort by time, then view — in that order, every time.

See it — turning a raw date column into an honest trend line

flowchart TD
    A["Date column as text<br/>+ a metric column"] --> B["to_datetime:<br/>text -> real timestamps"]
    B --> C["sort by date<br/>(then set as index)"]
    C --> D{"Which view<br/>do I need?"}
    D -->|"See every detail"| E["Plot raw points:<br/>spikes and gaps show"]
    D -->|"See the trend"| F["resample('MS').sum():<br/>month totals"]
    D -->|"See through noise"| G["rolling(k).mean():<br/>smoothed trend line"]
    E --> H["Line chart in<br/>true time order"]
    F --> H
    G --> H

The one-line takeaway

Parse the dates to real timestamps, sort by time, then pick your zoom — raw points, resampled totals, or a rolling average — because a line chart only tells the truth when its x-axis is genuine, ordered time.

Where you'll meet it

You'll meet this on day one of almost any real dataset, because the first plot people want is "show me the metric over time" — signups, sales, sensor readings, ticket counts. You'll reach for resampling the moment raw data is too fine-grained to read (thousands of per-minute rows collapsed into a clean daily or monthly line) or too coarse and needs regularizing, and for rolling averages whenever day-to-day jitter or a strong weekly cycle hides the trend you actually care about — a 7-day rolling mean is the analyst's default for turning a spiky daily line into a smooth, readable one. You'll meet the sort-order gotcha the first time a chart shows an impossible sawtooth and you realize the rows came back unsorted from a GROUP BY. And you'll meet it in interviews through questions like "your revenue line looks like it's crashing and recovering every week — what's going on?" (weekly seasonality, or unsorted dates), and "how would you tell a real trend from noise?" (resample or roll, then look). Keep asking the three questions that make you good at this: what am I plotting against time, what does the shape reveal — trend, season, or jump — and what would fool me: unsorted rows, string dates, or a window that hides the very pattern I'm hunting?

Named concepts: datetime handling, time-series line charts, resampling and rolling views