Pandas Datetime Exercise: Time-Series Queries and Date Ranges

Test your time-series wrangling skills by generating and querying a 365-day equipment maintenance log dataset. This challenge covers creating synthetic date ranges, slicing time intervals, filtering by year/month/weekday, calculating elapsed days, and performing temporal groupby() aggregations.

Video Guide: Generating the Sample Maintenance Log 🔝

Generate the 365-Day Maintenance Log DataFrame 🔝

Run the script below to create a 365-day equipment maintenance log ending on today's date:

import datetime
import pandas as pd
import random

random.seed(42)  # Reproducible seed
dt_range = 365  # 365 days of historical logs
dt_list = pd.date_range(end=datetime.date.today(), periods=dt_range).tolist()

l_activity = ["Checking", "Cleaning", "Inspection", "Repair", "Painting"]
l_dept = ["Dept1", "Dept2", "Dept3", "Dept4"]
l_eqpt = ["AC", "Fan", "Pump", "Generator", "Server", "Battery", "UPS"]

df = pd.DataFrame({
    "Log_ID": [1000 + i for i in range(dt_range)],
    "dt": pd.to_datetime(dt_list),
    "dept": [random.choice(l_dept) for _ in range(dt_range)],
    "activity": [random.choice(l_activity) for _ in range(dt_range)],
    "eqpt": [random.choice(l_eqpt) for _ in range(dt_range)]
})

print(df.head(5))

Download Sample log_report.xlsx 🔝

Download log_report.xlsx ↓

Time-Series Query Questions (Q1 to Q8) 🔝

1. How many records are there for the current calendar year?
2. How many maintenance tasks were conducted in March?
3. Retrieve the full record details for Log_ID == 1012.
4. Find the latest recorded date when equipment "Pump" was serviced.
5. Filter all records between April 1st and June 30th.
6. How many days have elapsed since the last "Pump" inspection relative to today?
7. Display all log entries completed in the last 30 days.
8. List all maintenance tasks performed on Wednesdays (dt.weekday == 2).

Video Guide: Solving the Date Queries 🔝

Step-by-Step Solutions & Code Blocks 🔝

c_year = pd.to_datetime("now").year
count_year = len(df[df["dt"].dt.year == c_year])
print(f"Current Year Records: {count_year}")

march_records = df[df["dt"].dt.month == 3]
print(f"March Records: {len(march_records)}")

log_1012 = df.loc[df["Log_ID"] == 1012]
print(log_1012)

pump_logs = df[df["eqpt"] == "Pump"]
latest_pump = pump_logs["dt"].max()
days_elapsed = (pd.to_datetime("now") - latest_pump).days

print(f"Latest Pump Service: {latest_pump.strftime('%Y-%m-%d')}")
print(f"Days Elapsed: {days_elapsed} days")

cutoff_30d = pd.to_datetime("now") - pd.Timedelta(days=30)
recent_30d = df[df["dt"] >= cutoff_30d]
print(f"Past 30 Days Logs: {len(recent_30d)}")

wednesdays = df[df["dt"].dt.weekday == 2]  # 0=Monday, 2=Wednesday
print(f"Wednesday Task Count: {len(wednesdays)}")

Temporal Grouping & Aggregations 🔝

Aggregate counts across year, month, or combination intervals:

# Group by Year
print(df.groupby(df["dt"].dt.year)["Log_ID"].count())

# Group by Month
print(df.groupby(df["dt"].dt.month)["Log_ID"].count())

# Group by Year and Month combined
print(df.groupby([df["dt"].dt.year, df["dt"].dt.month])["Log_ID"].count())

Datetime Exercise Series Navigation 🔝

Continue practicing across the complete 4-part datetime exercise sequence:

• Exercise 3-1 (Current Page): Time-series maintenance logs, date queries, and intervals.
• Exercise 3-2: Datetime basics, birthday arithmetic, and format conversions.
• Exercise 3-3: Webmaster central external backlink crawl date analysis.
• Exercise 3-4: Student accommodation check-in durations and rent calculation.

Practice in Google Colab 🔝

Open in Google Colab View on GitHub
Run all maintenance log queries, date range filters, and temporal group aggregations interactively in Google Colab. All synthetic generators are embedded in the notebook.

Further Reading & Related Tutorials 🔝

• Pandas Date and Time Hub — Master pillar for all Pandas datetime operations.
• pd.date_range() — Generating sequential timestamps and frequency series.
• pd.to_datetime() — Parsing dates from text, CSV, and logs.
• Pandas groupby() — Split, apply, and combine operations for time analysis.
• Pandas set_index() — Creating DatetimeIndexes for time-series slicing.
• Pandas Section Home — Master index of all Pandas tutorials, pillars, and guides.

Reference: Pandas Time Series / Date functionality documentation.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: