Checkpoint: Time Series & I/O

Reviewed & published by Brayan K

A checkpoint is a hands-on review that ties recent lessons together — here you'll combine resampling, interpolation, exploding, cross-tabs, SQL/Parquet I/O, and ranking into a single working pipeline.

Part of the free Pandas course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Recap the six skills, then prove them with a multi-step build challenge and a short quiz before moving on to the capstone.

What You'll Review in This Checkpoint

• resample() to change time frequency
• interpolate() to fill missing values
• explode() to unpack list columns
• crosstab() to count combinations
• read_sql / to_sql and Parquet/Pickle I/O
• rank() and nlargest for top-N

1 The Six Skills at a Glance

Here is the whole toolkit from this stretch of the course, each as a one-liner you can reach for:

# Resample — change time frequency (needs a DatetimeIndex)
df.resample("ME").sum()            # daily -> monthly totals

# Interpolate — estimate gaps between known points
s.interpolate(method="time")        # weight by elapsed time

# Explode — one row per list element
df.explode("tags")                  # split a list cell into rows

# Crosstab — count combinations of two categories
pd.crosstab(df["region"], df["choice"], margins=True)

# I/O — move data in and out efficiently
df.to_sql("t", conn, if_exists="replace")
pd.read_sql("SELECT * FROM t", conn)
df.to_pickle("cache.pkl")           # always-available binary round-trip

# Rank — order values, with top-N helpers
df.nlargest(3, "sales")             # top 3 rows
s.rank(method="dense", ascending=False)

import pandas as pd

# A quick warm-up touching three of the skills at once
df = pd.DataFrame({
    "date":   pd.to_datetime(["2024-01-05", "2024-01-25", "2024-02-10"]),
    "amount": [100, 200, 300],
}).set_index("date")

# resample to monthly totals, then rank the months
monthly = df["amount"].resample("ME").sum()
print(monthly)

print(monthly.rank(method="dense", ascending=False))

# ✅ Expected output:
# date
# 2024-01-31    300
# 2024-02-29    300
# Freq: ME, Name: amount, dtype: int64
# date
# 2024-01-31    1.0
# 2024-02-29    1.0
# Freq: ME, Name: amount, dtype: float64

2 🛠️ Build Challenge: From Database to Ranked Report

Put the pipeline together end to end. Starting from a sales table stored in SQLite, your job is to:

• Load the table back from SQLite with read_sql.
• Resample the daily rows into a monthly total per month.
• Rank the months so the biggest total is rank 1, and report the best month.

The starter below runs as-is and prints the raw table. Extend it to produce the ranked report, then check yourself against the full solution.

import pandas as pd
import sqlite3

# --- given: a sales table living in SQLite ---
conn = sqlite3.connect(":memory:")
sales = pd.DataFrame({
    "date":   ["2024-01-03", "2024-01-18", "2024-02-05", "2024-02-22",
               "2024-03-01", "2024-03-15", "2024-03-28"],
    "amount": [120, 80, 300, 250, 90, 60, 110],
})
sales.to_sql("sales", conn, index=False, if_exists="replace")

# STEP 1: load it back (this runs as-is)
df = pd.read_sql("SELECT * FROM sales", conn)
print(df)

# 🎯 YOUR TURN — add the rest:
# STEP 2: parse "date" to datetime, set it as the index,
#         and resample "amount" to a monthly total ("ME").
# STEP 3: rank the months (biggest total = rank 1) and
#         print the single best month with nlargest(1).

conn.close()

# ✅ Expected output:
#          date  amount
# 0  2024-01-03     120
# 1  2024-01-18      80
# 2  2024-02-05     300
# 3  2024-02-22     250
# 4  2024-03-01      90
# 5  2024-03-15      60
# 6  2024-03-28     110

import pandas as pd
import sqlite3

conn = sqlite3.connect(":memory:")
sales = pd.DataFrame({
    "date":   ["2024-01-03", "2024-01-18", "2024-02-05", "2024-02-22",
               "2024-03-01", "2024-03-15", "2024-03-28"],
    "amount": [120, 80, 300, 250, 90, 60, 110],
})
sales.to_sql("sales", conn, index=False, if_exists="replace")

# STEP 1: load it back from SQLite
df = pd.read_sql("SELECT * FROM sales", conn)

# STEP 2: parse dates, index them, total each month
df["date"] = pd.to_datetime(df["date"])
monthly = df.set_index("date")["amount"].resample("ME").sum()

# STEP 3: rank months (biggest = 1) and report the winner
report = monthly.to_frame("total")
report["rank"] = monthly.rank(method="dense", ascending=False).astype(int)
print(report)
# Expected output:
#             total  rank
# date
# 2024-01-31    200     3
# 2024-02-29    550     1
# 2024-03-31    260     2

print("Best month:")
print(monthly.nlargest(1))
# Expected output:
# Best month:
# date
# 2024-02-29    550
# Freq: ME, Name: amount, dtype: int64

conn.close()

3 📝 Checkpoint Quiz

Answer each in your head, then expand to check.

df.resample("ME").sum(). It requires a DatetimeIndex (parse the dates and set_index first, or pass on="date"). Remember the alias is "ME", not the old "M".

Use interpolate() when the value changes smoothly between known points (temperatures, prices) so a straight-line estimate makes sense. Use ffill() for step-like data that stays constant until it changes — it copies the last value forward rather than estimating a trend.

First split into a list: df["col"] = df["col"].str.split(","). Then explode it: df.explode("col"). explode only unpacks real list-like values, so the split must come first.

In to_sql it drops the existing table and recreates it, so any rows already there are lost. Use "append" when you want to keep accumulating data, and "fail" (the default) to be warned that the table exists.

With "min" the tied values are both 2 and the next value is 4 (a gap). With "dense" the tied values are both 2 and the next value is 3 (no gap).

df.nlargest(3, "revenue") is faster (and shorter) because it only has to locate the top 3 rather than sorting the entire DataFrame. Both return the same rows.

❓ Frequently Asked Questions

Checkpoint complete — your pipeline skills are solid!

You can move data with read_sql/to_sql and binary formats, reshape time with resample and interpolate, restructure with explode and crosstab, and surface winners with rank and nlargest — all in one flow.

Practice quiz

Continue this course

• Previous: Ranking & Top-N (rank, nlargest)
• Next: Capstone: End-to-End Data Cleaning — Combine every skill into a complete real-world data-cleaning pipeline
• Quick reference: Pandas cheat sheet

Frequently asked questions

Why does this checkpoint combine so many different tools?

Real data work is a pipeline: you load data from somewhere (SQL, Parquet), reshape its frequency (resample), fill gaps (interpolate), restructure rows (explode, crosstab), and finally rank or summarise the result. The checkpoint stitches these skills into one flow so you practise moving between them the way an actual analysis does.

I scored well — am I ready for the capstone?

If you can load a table from SQLite, resample it to a coarser frequency, and rank the results without peeking at the solution, you are ready. The capstone is an end-to-end data-cleaning project that leans on exactly these moves plus everything earlier in the course.

What if I get stuck on the build challenge?

Work it in three separate steps and print after each one: first confirm the data loaded from SQLite, then confirm the monthly resample, then add the ranking. Building incrementally and checking output at every stage is the single most reliable debugging habit in pandas.