Tutorial 9: Working with Dates and Time Series Data in Pandas

By /

Introduction

Many real-world datasets involve dates and time: sales by day, website traffic by month, stock prices, weather measurements, customer transactions, employee attendance. Pandas provides powerful tools purpose-built for this kind of data.

Raw Date Data → Convert to Datetime → Extract Date Components → Analyze by Time → Resample → Rolling Statistics → Identify Trends

You’ll learn to: convert text into dates, extract year/month/day/weekday, filter data by dates, sort chronologically, resample data into different time frequencies, calculate rolling averages, analyze trends, and create time-series visualizations.

1. Create a Sample Dataset

import pandas as pd

data = {
    "Date": [
        "2024-01-05", "2024-01-12", "2024-02-03", "2024-02-18",
        "2024-03-10", "2024-03-25", "2024-04-08", "2024-04-22"
    ],
    "Sales": [12000, 15000, 18000, 16000, 22000, 25000, 28000, 30000]
}

df = pd.DataFrame(data)
df

At this stage, the Date column is likely stored as plain text. Check:

df.dtypes

You’ll see Date object — meaning Pandas is treating dates as generic text, not as actual dates.

2. Convert Text to Datetime

df["Date"] = pd.to_datetime(df["Date"])

Check again:

df.dtypes

Now: Date datetime64[ns] — a real, Pandas-native datetime column.

3. Why Datetime Conversion Matters

Before conversion, "2024-01-05" and "2024-02-03" are just plain text — Pandas has no idea they represent points in time and can’t compare, sort, or manipulate them meaningfully. After conversion, Pandas can perform date filtering, chronological sorting, year/month extraction, time-difference calculations, and resampling — none of which work correctly on plain strings.

4. Extract the Year

df["Year"] = df["Date"].dt.year

The .dt accessor is the datetime equivalent of the .str accessor for text — it unlocks date-specific operations on a datetime column.

5. Extract the Month

df["Month"] = df["Date"].dt.month

6. Extract the Month Name

df["Month_Name"] = df["Date"].dt.month_name()

Output: January, January, February, February, March, March, April, April

7. Extract the Day

df["Day"] = df["Date"].dt.day

Example: 2024-01-05 → 5, 2024-02-18 → 18.

8. Extract the Day of the Week

df["Day_Name"] = df["Date"].dt.day_name()

You can also get the weekday as a number:

df["Weekday"] = df["Date"].dt.weekday

Numbering: Monday=0, Tuesday=1, Wednesday=2, Thursday=3, Friday=4, Saturday=5, Sunday=6.

9. Extract Quarter

df["Quarter"] = df["Date"].dt.quarter

Example: January → Q1, April → Q2, July → Q3, October → Q4.

10. Extract Multiple Date Components at Once

A common real-world workflow — enriching a date column with several derived columns in one go:

df["Year"] = df["Date"].dt.year
df["Month"] = df["Date"].dt.month
df["Month_Name"] = df["Date"].dt.month_name()
df["Day_Name"] = df["Date"].dt.day_name()
df["Quarter"] = df["Date"].dt.quarter

11. Sort Data by Date

Always sort time-series data chronologically before analyzing it:

df = df.sort_values("Date")

Descending order:

df = df.sort_values("Date", ascending=False)

12. Set Date as the Index

Time-series analysis usually works best when the date column becomes the DataFrame’s index:

df = df.set_index("Date")

This makes the whole DataFrame explicitly “time-oriented,” which unlocks powerful date-based slicing and resampling.

13. Filter Data by Date

Sales after February 1, 2024:

df[df.index >= "2024-02-01"]

Between two dates:

df[
    (df.index >= "2024-02-01") &
    (df.index <= "2024-03-31")
]

If the date is the index, you can also use the simpler slicing syntax:

df.loc["2024-02-01":"2024-03-31"]

14. Resampling

Resampling means changing the time frequency of your data — e.g., converting daily records into weekly, monthly, or yearly totals.

Daily Data → Weekly Data → Monthly Data → Yearly Data

Create some daily sales data:

daily_sales = pd.DataFrame({
    "Date": pd.date_range(start="2024-01-01", periods=90, freq="D"),
    "Sales": range(100, 190)
})

daily_sales = daily_sales.set_index("Date")

15. Resample Daily Data to Monthly Data

monthly_sales = daily_sales["Sales"].resample("ME").sum()

Common frequency codes:

CodeMeaning
DDay
WWeek
MEMonth-end
QEQuarter-end
YEYear-end

Weekly example:

weekly_sales = daily_sales["Sales"].resample("W").sum()

16. Different Aggregations During Resampling

monthly_average = daily_sales["Sales"].resample("ME").mean()
monthly_max = daily_sales["Sales"].resample("ME").max()
monthly_min = daily_sales["Sales"].resample("ME").min()

17. Multiple Aggregations at Once

monthly_summary = daily_sales["Sales"].resample("ME").agg([
    "sum", "mean", "min", "max"
])

18. Rolling Average

A rolling average (also called a moving average) smooths out short-term fluctuations to reveal the underlying trend.

daily_sales["Rolling_Average"] = (
    daily_sales["Sales"]
    .rolling(window=7)
    .mean()
)

This calculates a 7-day moving average — for each day, it’s the average of that day and the previous 6.

19. Why Use Rolling Averages?

Raw daily data often bounces around a lot (e.g., 100 → 150 → 120 → 180 → 140 → 220), making the underlying trend hard to see. A rolling average smooths that noise out into a clearer upward or downward path — this is exactly the technique behind, for example, the “7-day average” lines commonly shown on COVID case charts or stock price charts.

20. Visualize a Rolling Average

import matplotlib.pyplot as plt

daily_sales["Sales"].plot(label="Daily Sales")
daily_sales["Rolling_Average"].plot(label="7-Day Rolling Average")

plt.title("Daily Sales and Rolling Average")
plt.xlabel("Date")
plt.ylabel("Sales")
plt.legend()
plt.show()

The raw line will show daily jitter, while the rolling-average line reveals the smoother underlying trend.

21. Analyze Monthly Sales Trends

monthly_sales = daily_sales["Sales"].resample("ME").sum()

monthly_sales.plot(marker="o")
plt.title("Monthly Sales Trend")
plt.xlabel("Month")
plt.ylabel("Total Sales")
plt.show()

22. Calculate Month-over-Month Growth

If January sales = 100,000 and February sales = 120,000, growth = 20%. In Pandas:

monthly_growth = monthly_sales.pct_change() * 100

pct_change() calculates the percentage change between each period and the one before it — extremely common in business and financial reporting.

23. Calculate Year-over-Year Growth

yearly_sales = daily_sales["Sales"].resample("YE").sum()
yearly_growth = yearly_sales.pct_change() * 100

This answers: how much did sales grow compared to the previous year?

24. Group Sales by Month (an Alternative to Resampling)

df["Month"] = df.index.month
df.groupby("Month")["Sales"].sum()

However, for genuine time-series work, resample() is usually the better choice, since it preserves the actual time structure (rather than collapsing, say, January 2024 and January 2025 into the same “Month” bucket).

25. Complete Time-Series Example

import pandas as pd
import matplotlib.pyplot as plt

# Create daily data
data = {
    "Date": pd.date_range(start="2024-01-01", periods=90, freq="D"),
    "Sales": range(100, 190)
}
df = pd.DataFrame(data)

# Convert to datetime
df["Date"] = pd.to_datetime(df["Date"])

# Set Date as index
df = df.set_index("Date")

# Calculate 7-day rolling average
df["Rolling_Average"] = df["Sales"].rolling(7).mean()

# Monthly sales
monthly_sales = df["Sales"].resample("ME").sum()

# Plot daily sales
df["Sales"].plot(label="Daily Sales")

# Plot rolling average
df["Rolling_Average"].plot(label="7-Day Rolling Average")

plt.title("Sales Trend Analysis")
plt.xlabel("Date")
plt.ylabel("Sales")
plt.legend()
plt.show()

Important Pandas Date-Time Methods

MethodPurpose
pd.to_datetime()Convert values to dates
.dt.year / .dt.month / .dt.dayExtract date components
.dt.day_name() / .dt.month_name()Extract weekday/month names
.dt.quarterExtract quarter
.resample()Change time frequency
.rolling()Calculate moving statistics
.pct_change()Calculate percentage change

Practice Exercise

Create a sales dataset with Date, Product, and Sales columns, then: convert Date to datetime, extract year and month, set Date as the index, calculate monthly sales, calculate a 7-day rolling average, calculate month-over-month growth, and create a line chart of the sales trend.

Key Takeaway: Time-series analysis transforms dates and time into meaningful trends, allowing us to understand how data changes over days, weeks, months, and years.

Scroll to Top