Exploring Dates, Times, and Time-Series Data
Dates and times (datetimes), such as the time of a particular sale or the date of a public health statistic, are frequently encountered during preprocessing for data analysis and machine learning. Longitudinal data, or time-series data, is data that is collected repeatedly for the same variables over points in time. Dates and times data are temporal data that capture when an event occurs, while time-series data is widely used to understand patterns, trends, and changes over time. Because machine-learning algorithms generally require numerical and consistently formatted inputs, dates and times often need to be transformed into useful numerical features before they can be used effectively in a model.
In this episode, we will take a practical approach to handling numerical and temporal aspects of Dates, Times, and Time-Series Data. We will build a toolbox of strategies for converting date and time values into ready-to-use features, including working with time zones, selecting observations by date and time, extracting useful components such as year, month, and day, calculating differences between dates, creating lagged features, and using rolling time windows. Specifically, we will focus on time-series capabilities of the pandas library, which provides a centralized set of tools for working with temporal data and integrates naturally with Python’s general-purpose datetime functionality.
Objectives
Convert and manipulate date and time data using pandas datetime functionality.
Select and filter observations based on dates, times, and time periods.
Create useful time-based features, including elapsed time, lagged features, and rolling-window statistics.
Identify and appropriately handle missing values and missing timestamps in time-series data.
Instructor note
40 minutes teaching
20 minutes exercising/discussion
1. UCI Individual Household Electric Power Consumption Dataset
The UCI Individual Household Electric Power Consumption dataset contains 2,075,259 measurements of electricity consumption from a single household in Sceaux, France, recorded at one-minute intervals from December 2006 to November 2010. It includes date and time information together with numerical measurements such as active power, reactive power, voltage, current intensity, and electricity consumption from three household sub-meterings. This dataset is particularly useful for learning how to handle numerical and time-series data, and it also contains missing values that can be used for data-cleaning exercises.
import pandas as pd
url = "https://raw.githubusercontent.com/jbrownlee/Datasets/master/household_power_consumption.zip"
df = pd.read_csv(url, sep=";", na_values="?")
# quick preview and inspect a dataset's structure, column names, and data types
df.head() # df.tail() # df.sample(5)
| Date | Time | Global_active_power | Global_reactive_power | Voltage | Global_intensity | Sub_metering_1 | Sub_metering_2 | Sub_metering_3 | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 16/12/2006 | 17:24:00 | 4.216 | 0.418 | 234.84 | 18.4 | 0.0 | 1.0 | 17.0 |
| 1 | 16/12/2006 | 17:25:00 | 5.360 | 0.436 | 233.63 | 23.0 | 0.0 | 1.0 | 16.0 |
| 2 | 16/12/2006 | 17:26:00 | 5.374 | 0.498 | 233.29 | 23.0 | 0.0 | 2.0 | 17.0 |
| 3 | 16/12/2006 | 17:27:00 | 5.388 | 0.502 | 233.74 | 23.0 | 0.0 | 1.0 | 17.0 |
| 4 | 16/12/2006 | 17:28:00 | 3.666 | 0.528 | 235.68 | 15.8 | 0.0 | 1.0 | 17.0 |
# get number of rows and columns in a DataFrame
df.shape
(2075259, 9)
# print a concise summary of a DataFrame
df.info()
<class 'pandas.DataFrame'>
RangeIndex: 2075259 entries, 0 to 2075258
Data columns (total 9 columns):
# Column Dtype
--- ------ -----
0 Date str
1 Time str
2 Global_active_power float64
3 Global_reactive_power float64
4 Voltage float64
5 Global_intensity float64
6 Sub_metering_1 float64
7 Sub_metering_2 float64
8 Sub_metering_3 float64
dtypes: float64(7), str(2)
memory usage: 142.5 MB
# basic numerical analysis
print(df.describe()) # df.Global_active_power
Global_active_power Global_reactive_power Voltage \
count 2.049280e+06 2.049280e+06 2.049280e+06
mean 1.091615e+00 1.237145e-01 2.408399e+02
std 1.057294e+00 1.127220e-01 3.239987e+00
min 7.600000e-02 0.000000e+00 2.232000e+02
25% 3.080000e-01 4.800000e-02 2.389900e+02
50% 6.020000e-01 1.000000e-01 2.410100e+02
75% 1.528000e+00 1.940000e-01 2.428900e+02
max 1.112200e+01 1.390000e+00 2.541500e+02
Global_intensity Sub_metering_1 Sub_metering_2 Sub_metering_3
count 2.049280e+06 2.049280e+06 2.049280e+06 2.049280e+06
mean 4.627759e+00 1.121923e+00 1.298520e+00 6.458447e+00
std 4.444396e+00 6.153031e+00 5.822026e+00 8.437154e+00
min 2.000000e-01 0.000000e+00 0.000000e+00 0.000000e+00
25% 1.400000e+00 0.000000e+00 0.000000e+00 0.000000e+00
50% 2.600000e+00 0.000000e+00 0.000000e+00 1.000000e+00
75% 6.400000e+00 0.000000e+00 1.000000e+00 1.700000e+01
max 4.840000e+01 8.800000e+01 8.000000e+01 3.100000e+01
# average, minimum and maximum power consumption
print("Average of Global_active_power:", df["Global_active_power"].mean())
print("Minimum of Global_active_power:", df["Global_active_power"].min())
print("Maximum of Global_active_power:", df["Global_active_power"].max())
Average of Global_active_power: 1.0916150365006245
Minimum of Global_active_power: 0.076
Maximum of Global_active_power: 11.122
2. From Date Strings to Datetime
Once we have imported the dataset, one of the first preprocessing tasks is to make sure that information representing dates and times is stored in a format that Python and pandas can understand.
In this dataset, the Date (the first column) and Time (the second column) columns initially contain values as strings, even though they represent meaningful points in time. The output info tells us that pandas currently sees Date and Time as text rather than datetime information.
print(df[["Date", "Time"]].head(), '\n')
print(df[["Date", "Time"]].dtypes)
Date Time
0 16/12/2006 17:24:00
1 16/12/2006 17:25:00
2 16/12/2006 17:26:00
3 16/12/2006 17:27:00
4 16/12/2006 17:28:00
Date str
Time str
dtype: object
2.1 Convert strings to datetime type
Here, we will use pd.to_datetime() to convert these strings into pandas datetime values.
df["Date"] = pd.to_datetime(df["Date"], format="%d/%m/%Y")
print(df.dtypes) # now it is a datetime type
df.head()
Date datetime64[us]
Time str
Global_active_power float64
Global_reactive_power float64
Voltage float64
Global_intensity float64
Sub_metering_1 float64
Sub_metering_2 float64
Sub_metering_3 float64
dtype: object
| Date | Time | Global_active_power | Global_reactive_power | Voltage | Global_intensity | Sub_metering_1 | Sub_metering_2 | Sub_metering_3 | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2006-12-16 | 17:24:00 | 4.216 | 0.418 | 234.84 | 18.4 | 0.0 | 1.0 | 17.0 |
| 1 | 2006-12-16 | 17:25:00 | 5.360 | 0.436 | 233.63 | 23.0 | 0.0 | 1.0 | 16.0 |
| 2 | 2006-12-16 | 17:26:00 | 5.374 | 0.498 | 233.29 | 23.0 | 0.0 | 2.0 | 17.0 |
| 3 | 2006-12-16 | 17:27:00 | 5.388 | 0.502 | 233.74 | 23.0 | 0.0 | 1.0 | 17.0 |
| 4 | 2006-12-16 | 17:28:00 | 3.666 | 0.528 | 235.68 | 15.8 | 0.0 | 1.0 | 17.0 |
Instead of ordinary strings, pandas now recognizes these values as dates, from which we can perform operations such as extracting the year, month, and day.
df["Year"] = df["Date"].dt.year
df["Month"] = df["Date"].dt.month
df["Day"] = df["Date"].dt.day
df.head()
| Date | Time | Global_active_power | Global_reactive_power | Voltage | Global_intensity | Sub_metering_1 | Sub_metering_2 | Sub_metering_3 | Year | Month | Day | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2006-12-16 | 17:24:00 | 4.216 | 0.418 | 234.84 | 18.4 | 0.0 | 1.0 | 17.0 | 2006 | 12 | 16 |
| 1 | 2006-12-16 | 17:25:00 | 5.360 | 0.436 | 233.63 | 23.0 | 0.0 | 1.0 | 16.0 | 2006 | 12 | 16 |
| 2 | 2006-12-16 | 17:26:00 | 5.374 | 0.498 | 233.29 | 23.0 | 0.0 | 2.0 | 17.0 | 2006 | 12 | 16 |
| 3 | 2006-12-16 | 17:27:00 | 5.388 | 0.502 | 233.74 | 23.0 | 0.0 | 1.0 | 17.0 | 2006 | 12 | 16 |
| 4 | 2006-12-16 | 17:28:00 | 3.666 | 0.528 | 235.68 | 15.8 | 0.0 | 1.0 | 17.0 | 2006 | 12 | 16 |
2.2 Explicit formats to combine Date and Time
For time-series analysis, having separate Date and Time columns is often less convenient than having one timestamp, which contains information such as the year, month, day, hour, minute, and second.
df["Datetime"] = df["Date"] + pd.to_timedelta(df["Time"])
print(df[["Date", "Time", "Datetime"]].head())
Date Time Datetime
0 2006-12-16 17:24:00 2006-12-16 17:24:00
1 2006-12-16 17:25:00 2006-12-16 17:25:00
2 2006-12-16 17:26:00 2006-12-16 17:26:00
3 2006-12-16 17:27:00 2006-12-16 17:27:00
4 2006-12-16 17:28:00 2006-12-16 17:28:00
A timestamp (the Datetime column) is convenient for us to select periods, to calculate time differences, to create lagged features, and work with rolling windows as we will explore in following sections.
2.3 Handling invalid dates
Real-world datasets are rarely perfect. We may encounter values that do not represent valid dates. For example, in the toy dateset below, the value 31/02/2007 is invalid because February does not have 31 days.
dates = pd.Series([
"16/12/2006",
"17/12/2006",
"31/02/2007",
"18/12/2006"
])
If we attempt pd.to_datetime(dates, format="%d/%m/%Y"), pandas can raise a parsing error like ValueError: day is out of range for month, at position 2 because it cannot interpret the invalid value as a real date.
For safely handle values that cannot be converted, we can use errors="coerce", which can tell pandas to convert invalid dates into missing values.
dates = pd.Series([
"16/12/2006",
"17/12/2006",
"31/02/2007",
"18/12/2006"
])
# pd.to_datetime(dates, format="%d/%m/%Y") # leads to "ValueError: day is out of range for month"
pd.to_datetime(dates, format="%d/%m/%Y", errors="coerce")
0 2006-12-16
1 2006-12-17
2 NaT
3 2006-12-18
dtype: datetime64[us]
The invalid date becomes NaT, which means Not a Time and is the datetime equivalent of a missing value such as NaN.
Let’s apply the same idea to the UCI Individual Household Electric Power Consumption dataset, and check how many dates could not be converted. If the result is greater than zero, we know that some values could not be interpreted as valid dates.
df["Date"] = pd.to_datetime(df["Date"], format="%d/%m/%Y", errors="coerce")
print(df["Date"].isna().sum())
0
The output 0 means there is no invalid dates in the dataset.
3. Handling Time Zones
After converting the Date and Time columns into a proper Datetime column, the next challenge is understanding time zones. A timestamp such as 2006-12-16 17:24:00 tells us the local clock time, but by itself it does not tell us where that time occurred. This distinction becomes particularly important when combining time-series data collected from different locations.
Using the UCI Individual Household Electric Power Consumption dataset, we will see how to assign a timezone to our timestamps and then convert them to other time zones without changing the actual moment represented by the observation.
We contine with the combined timestamp Datetime we created in the previous section.
df[["Date", "Time", "Datetime"]].head()
| Date | Time | Datetime | |
|---|---|---|---|
| 0 | 2006-12-16 | 17:24:00 | 2006-12-16 17:24:00 |
| 1 | 2006-12-16 | 17:25:00 | 2006-12-16 17:25:00 |
| 2 | 2006-12-16 | 17:26:00 | 2006-12-16 17:26:00 |
| 3 | 2006-12-16 | 17:27:00 | 2006-12-16 17:27:00 |
| 4 | 2006-12-16 | 17:28:00 | 2006-12-16 17:28:00 |
At this point, our timestamp is naive datetime, which contains a date and time but has no timezone information attached to it. We can verify this using df["Datetime"].dt.tz and the output should be None.
print(df["Datetime"].dt.tz)
None
This indicates that pandas knows that “the observation occurred at 17:24”. But it does not know “the observation occurred at 17:24 in which timezone?”. This distinction may not matter when working exclusively with one local dataset, but it becomes important when we combine datasets from different geographic locations. For example, when data analysts in Sweden and Greece share observational data for analysis, or even schedule meetings, they should clearly specify which time zone is being used to avoid misunderstandings, scheduling conflicts, or errors in interpreting timestamps.
The timestamp 2006-12-16 17:24:00 could represent 17:24 in London, 17:24 in Stockholm, or 17:24 in New York. These are not the same moment in time.
A timezone-aware datetime contains both timestamp and information about its timezone, such as 2006-12-16 17:24:00+01:00, in which the +01:00 tells us that the timestamp is one hour ahead of UTC.
Note
UTC (Coordinated Universal Time) is the primary time standard used worldwide. It provides a common reference point for expressing and comparing times across different time zones, without being affected by daylight saving time.
3.1 Localization: Assigning a local time zone
When assigning a timezone to a naive datetime, it does not change the clock time.
from datetime import datetime
# get computer's local timezone
local_now = datetime.now().astimezone()
local_tz = local_now.tzinfo
print(local_now)
print(local_tz)
# if Datetime contains UTC timestamps
df["Datetime_Stockholm"] = (
df["Datetime"]
.dt.tz_localize("UTC")
.dt.tz_convert(local_tz)
)
df[["Date", "Time", "Datetime", "Datetime_Stockholm"]].head()
2026-09-15 12:42:03.708407+02:00
CEST
| Date | Time | Datetime | Datetime_Stockholm | |
|---|---|---|---|---|
| 0 | 2006-12-16 | 17:24:00 | 2006-12-16 17:24:00 | 2006-12-16 19:24:00+02:00 |
| 1 | 2006-12-16 | 17:25:00 | 2006-12-16 17:25:00 | 2006-12-16 19:25:00+02:00 |
| 2 | 2006-12-16 | 17:26:00 | 2006-12-16 17:26:00 | 2006-12-16 19:26:00+02:00 |
| 3 | 2006-12-16 | 17:27:00 | 2006-12-16 17:27:00 | 2006-12-16 19:27:00+02:00 |
| 4 | 2006-12-16 | 17:28:00 | 2006-12-16 17:28:00 | 2006-12-16 19:28:00+02:00 |
3.2 Conversion: Changing time zones
What if we want to convert the timestamps to another time zone? The goal is to represent the same point in time using a different local time, without changing the underlying observation or event. We can use tz_convert() to convert a timezone-aware timestamp from one time zone to another while preserving the same point in time.
# convert same moments to California time
df["Datetime_California"] = df["Datetime_Stockholm"].dt.tz_convert("America/Los_Angeles")
df[["Date", "Time", "Datetime", "Datetime_Stockholm", "Datetime_California"]].head()
| Date | Time | Datetime | Datetime_Stockholm | Datetime_California | |
|---|---|---|---|---|---|
| 0 | 2006-12-16 | 17:24:00 | 2006-12-16 17:24:00 | 2006-12-16 19:24:00+02:00 | 2006-12-16 09:24:00-08:00 |
| 1 | 2006-12-16 | 17:25:00 | 2006-12-16 17:25:00 | 2006-12-16 19:25:00+02:00 | 2006-12-16 09:25:00-08:00 |
| 2 | 2006-12-16 | 17:26:00 | 2006-12-16 17:26:00 | 2006-12-16 19:26:00+02:00 | 2006-12-16 09:26:00-08:00 |
| 3 | 2006-12-16 | 17:27:00 | 2006-12-16 17:27:00 | 2006-12-16 19:27:00+02:00 | 2006-12-16 09:27:00-08:00 |
| 4 | 2006-12-16 | 17:28:00 | 2006-12-16 17:28:00 | 2006-12-16 19:28:00+02:00 | 2006-12-16 09:28:00-08:00 |
After conversion, the clock time changed from 19:24 to 09:24, but the actual moment represented by the timestamp did not change.
This gives us a clean, timezone-aware timestamp that is ready for the next stages of time-series preprocessing, such as selecting dates and times, extracting temporal features, calculating time differences, and creating lagged features.
Localization vs. Conversion
Localization uses
tz_localize().When datetime is naive, the clock remains and the output of following code fragment is 17:24.
naive = pd.Timestamp("2006-12-16 17:24:00") aware = naive.tz_localize("Europe/Paris")
Conversion uses
tz_convert().When datetime is already timezone-aware, perform conversion to change the clock time in another timezone.
converted = aware.tz_convert("UTC")
A common mistake is trying to convert a naive datetime directly
df["Datetime"].dt.tz_convert("UTC").This will fail because pandas does not yet know what timezone the naive timestamp represents.
4. Selecting Time-Series Data
Once the UCI Individual Household Electric Power Consumption dataset has been converted into proper datetime values and, when needed, assigned a timezone, the next step is to select specific dates, times, and time periods efficiently.
This dataset contains measurements recorded approximately every minute, producing a large number of observations over several years. But in a time-series dataset, we are rarely interested in every observation at once. We may want to examine electricity consumption on a particular day, during a specific month, or across a selected range of dates.
In this section, we will use pandas date-based indexing and filtering to navigate this dataset. Before selecting dates, it is useful to make sure the data is sorted chronologically via sort_values("Datetime"). This is an important step when working with time-series data.
df = df.sort_values("Datetime")
df[["Date", "Time", "Datetime"]].head()
| Date | Time | Datetime | |
|---|---|---|---|
| 0 | 2006-12-16 | 17:24:00 | 2006-12-16 17:24:00 |
| 1 | 2006-12-16 | 17:25:00 | 2006-12-16 17:25:00 |
| 2 | 2006-12-16 | 17:26:00 | 2006-12-16 17:26:00 |
| 3 | 2006-12-16 | 17:27:00 | 2006-12-16 17:27:00 |
| 4 | 2006-12-16 | 17:28:00 | 2006-12-16 17:28:00 |
4.1 Select a specific date
When we want to select a specific date, one straightforward approach is to filter dataframe using a condition. Suppose we want to examine electricity consumption on December 17, 2006. We can do so by running the code snippet below.
df_17_dec = df[df["Datetime"].dt.date == pd.Timestamp("2006-12-17").date()]
df_17_dec.head()
| Date | Time | Global_active_power | Global_reactive_power | Voltage | Global_intensity | Sub_metering_1 | Sub_metering_2 | Sub_metering_3 | Year | Month | Day | Datetime | Datetime_Stockholm | Datetime_California | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 396 | 2006-12-17 | 00:00:00 | 1.044 | 0.152 | 242.73 | 4.4 | 0.0 | 2.0 | 0.0 | 2006 | 12 | 17 | 2006-12-17 00:00:00 | 2006-12-17 02:00:00+02:00 | 2006-12-16 16:00:00-08:00 |
| 397 | 2006-12-17 | 00:01:00 | 1.520 | 0.220 | 242.20 | 7.4 | 0.0 | 1.0 | 0.0 | 2006 | 12 | 17 | 2006-12-17 00:01:00 | 2006-12-17 02:01:00+02:00 | 2006-12-16 16:01:00-08:00 |
| 398 | 2006-12-17 | 00:02:00 | 3.038 | 0.194 | 240.14 | 12.6 | 0.0 | 2.0 | 0.0 | 2006 | 12 | 17 | 2006-12-17 00:02:00 | 2006-12-17 02:02:00+02:00 | 2006-12-16 16:02:00-08:00 |
| 399 | 2006-12-17 | 00:03:00 | 2.974 | 0.194 | 239.97 | 12.4 | 0.0 | 1.0 | 0.0 | 2006 | 12 | 17 | 2006-12-17 00:03:00 | 2006-12-17 02:03:00+02:00 | 2006-12-16 16:03:00-08:00 |
| 400 | 2006-12-17 | 00:04:00 | 2.846 | 0.198 | 240.39 | 11.8 | 0.0 | 2.0 | 0.0 | 2006 | 12 | 17 | 2006-12-17 00:04:00 | 2006-12-17 02:04:00+02:00 | 2006-12-16 16:04:00-08:00 |
We can check how many observations we retrieved via len(df_17_dec). As this dataset contains one measurement per minute, a complete day should contain close to 24 × 60 = 1,440 observations.
len(df_17_dec)
1440
Here we are filtering rows based on the value of a datetime column. It works, but pandas provides an even more convenient approach when we use DatetimeIndex, which tells pandas that the index of the dataframe consists of timestamps. Here we set our Datetime column as the index df_index = df.set_index("Datetime").
df_index = df.set_index("Datetime")
df_index.head()
| Date | Time | Global_active_power | Global_reactive_power | Voltage | Global_intensity | Sub_metering_1 | Sub_metering_2 | Sub_metering_3 | Year | Month | Day | Datetime_Stockholm | Datetime_California | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Datetime | ||||||||||||||
| 2006-12-16 17:24:00 | 2006-12-16 | 17:24:00 | 4.216 | 0.418 | 234.84 | 18.4 | 0.0 | 1.0 | 17.0 | 2006 | 12 | 16 | 2006-12-16 19:24:00+02:00 | 2006-12-16 09:24:00-08:00 |
| 2006-12-16 17:25:00 | 2006-12-16 | 17:25:00 | 5.360 | 0.436 | 233.63 | 23.0 | 0.0 | 1.0 | 16.0 | 2006 | 12 | 16 | 2006-12-16 19:25:00+02:00 | 2006-12-16 09:25:00-08:00 |
| 2006-12-16 17:26:00 | 2006-12-16 | 17:26:00 | 5.374 | 0.498 | 233.29 | 23.0 | 0.0 | 2.0 | 17.0 | 2006 | 12 | 16 | 2006-12-16 19:26:00+02:00 | 2006-12-16 09:26:00-08:00 |
| 2006-12-16 17:27:00 | 2006-12-16 | 17:27:00 | 5.388 | 0.502 | 233.74 | 23.0 | 0.0 | 1.0 | 17.0 | 2006 | 12 | 16 | 2006-12-16 19:27:00+02:00 | 2006-12-16 09:27:00-08:00 |
| 2006-12-16 17:28:00 | 2006-12-16 | 17:28:00 | 3.666 | 0.528 | 235.68 | 15.8 | 0.0 | 1.0 | 17.0 | 2006 | 12 | 16 | 2006-12-16 19:28:00+02:00 | 2006-12-16 09:28:00-08:00 |
Once Datetime is the index, selecting an entire day becomes much simpler. For example, df_index.loc["2006-12-17"] tells pandas to give “all observations belonging to December 17 2006”. This is considerably more convenient than manually constructing a filtering condition.
df_index.loc["2006-12-17"]
| Date | Time | Global_active_power | Global_reactive_power | Voltage | Global_intensity | Sub_metering_1 | Sub_metering_2 | Sub_metering_3 | Year | Month | Day | Datetime_Stockholm | Datetime_California | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Datetime | ||||||||||||||
| 2006-12-17 00:00:00 | 2006-12-17 | 00:00:00 | 1.044 | 0.152 | 242.73 | 4.4 | 0.0 | 2.0 | 0.0 | 2006 | 12 | 17 | 2006-12-17 02:00:00+02:00 | 2006-12-16 16:00:00-08:00 |
| 2006-12-17 00:01:00 | 2006-12-17 | 00:01:00 | 1.520 | 0.220 | 242.20 | 7.4 | 0.0 | 1.0 | 0.0 | 2006 | 12 | 17 | 2006-12-17 02:01:00+02:00 | 2006-12-16 16:01:00-08:00 |
| 2006-12-17 00:02:00 | 2006-12-17 | 00:02:00 | 3.038 | 0.194 | 240.14 | 12.6 | 0.0 | 2.0 | 0.0 | 2006 | 12 | 17 | 2006-12-17 02:02:00+02:00 | 2006-12-16 16:02:00-08:00 |
| 2006-12-17 00:03:00 | 2006-12-17 | 00:03:00 | 2.974 | 0.194 | 239.97 | 12.4 | 0.0 | 1.0 | 0.0 | 2006 | 12 | 17 | 2006-12-17 02:03:00+02:00 | 2006-12-16 16:03:00-08:00 |
| 2006-12-17 00:04:00 | 2006-12-17 | 00:04:00 | 2.846 | 0.198 | 240.39 | 11.8 | 0.0 | 2.0 | 0.0 | 2006 | 12 | 17 | 2006-12-17 02:04:00+02:00 | 2006-12-16 16:04:00-08:00 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2006-12-17 23:55:00 | 2006-12-17 | 23:55:00 | 0.276 | 0.122 | 245.63 | 1.2 | 0.0 | 1.0 | 0.0 | 2006 | 12 | 17 | 2006-12-18 01:55:00+02:00 | 2006-12-17 15:55:00-08:00 |
| 2006-12-17 23:56:00 | 2006-12-17 | 23:56:00 | 0.274 | 0.118 | 244.57 | 1.2 | 0.0 | 1.0 | 0.0 | 2006 | 12 | 17 | 2006-12-18 01:56:00+02:00 | 2006-12-17 15:56:00-08:00 |
| 2006-12-17 23:57:00 | 2006-12-17 | 23:57:00 | 0.274 | 0.116 | 244.19 | 1.2 | 0.0 | 1.0 | 0.0 | 2006 | 12 | 17 | 2006-12-18 01:57:00+02:00 | 2006-12-17 15:57:00-08:00 |
| 2006-12-17 23:58:00 | 2006-12-17 | 23:58:00 | 0.274 | 0.120 | 244.82 | 1.2 | 0.0 | 1.0 | 0.0 | 2006 | 12 | 17 | 2006-12-18 01:58:00+02:00 | 2006-12-17 15:58:00-08:00 |
| 2006-12-17 23:59:00 | 2006-12-17 | 23:59:00 | 0.276 | 0.120 | 244.89 | 1.2 | 0.0 | 1.0 | 0.0 | 2006 | 12 | 17 | 2006-12-18 01:59:00+02:00 | 2006-12-17 15:59:00-08:00 |
1440 rows × 14 columns
The DatetimeIndex also allows us to select an entire week/month/year using a partial date string.
# one entire month of data
print(len(df_index.loc["2007-01"]), '\t', 31*1440)
df_index.loc["2007-01"].head()
# one entire year of data
# print(len(df_index.loc["2007"]), '\t', 365*1440)
# df_index.loc["2007"].head()
44640 44640
| Date | Time | Global_active_power | Global_reactive_power | Voltage | Global_intensity | Sub_metering_1 | Sub_metering_2 | Sub_metering_3 | Year | Month | Day | Datetime_Stockholm | Datetime_California | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Datetime | ||||||||||||||
| 2007-01-01 00:00:00 | 2007-01-01 | 00:00:00 | 2.580 | 0.136 | 241.97 | 10.6 | 0.0 | 0.0 | 0.0 | 2007 | 1 | 1 | 2007-01-01 02:00:00+02:00 | 2006-12-31 16:00:00-08:00 |
| 2007-01-01 00:01:00 | 2007-01-01 | 00:01:00 | 2.552 | 0.100 | 241.75 | 10.4 | 0.0 | 0.0 | 0.0 | 2007 | 1 | 1 | 2007-01-01 02:01:00+02:00 | 2006-12-31 16:01:00-08:00 |
| 2007-01-01 00:02:00 | 2007-01-01 | 00:02:00 | 2.550 | 0.100 | 241.64 | 10.4 | 0.0 | 0.0 | 0.0 | 2007 | 1 | 1 | 2007-01-01 02:02:00+02:00 | 2006-12-31 16:02:00-08:00 |
| 2007-01-01 00:03:00 | 2007-01-01 | 00:03:00 | 2.550 | 0.100 | 241.71 | 10.4 | 0.0 | 0.0 | 0.0 | 2007 | 1 | 1 | 2007-01-01 02:03:00+02:00 | 2006-12-31 16:03:00-08:00 |
| 2007-01-01 00:04:00 | 2007-01-01 | 00:04:00 | 2.554 | 0.100 | 241.98 | 10.4 | 0.0 | 0.0 | 0.0 | 2007 | 1 | 1 | 2007-01-01 02:04:00+02:00 | 2006-12-31 16:04:00-08:00 |
4.2 Select a specific period
Furthermore, we can also select a period rather than a single date, month, or a single year. This is particularly useful for exploring a short section of a much larger time series.
df_period = df_index.loc["2006-12-17":"2006-12-18"]
print(len(df_period))
2880
Sometimes we need more precise control than selecting complete days. For example, suppose we want electricity consumption between December 16 2006, 17:30 and 18:00. This gives us observations within that specific time interval. We could then calculate the average power consumption during this period.
df_half_hour = df_index.loc["2006-12-16 17:30:00":"2006-12-16 18:00:00"]
print(len(df_half_hour))
df_index.loc[
"2006-12-16 17:30:00":"2006-12-16 18:00:00",
"Global_active_power"
].mean()
31
np.float64(4.106129032258064)
This demonstrates why timestamps are much more powerful than ordinary strings: pandas can understand the chronological relationship between them.
We can also use normal Boolean filtering. For example, suppose we want observations from December 2006 where global active power was greater than 7 kW. This combines two concepts: Time-based filtering and Value-based filtering. This type of filtering becomes very useful when preparing data for machine-learning analysis.
high_power = df_index[
(df_index.index >= "2006-12-01") &
(df_index.index < "2007-01-01") &
(df_index["Global_active_power"] > 7)
]
print(len(high_power))
high_power.head()
80
| Date | Time | Global_active_power | Global_reactive_power | Voltage | Global_intensity | Sub_metering_1 | Sub_metering_2 | Sub_metering_3 | Year | Month | Day | Datetime_Stockholm | Datetime_California | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Datetime | ||||||||||||||
| 2006-12-16 17:45:00 | 2006-12-16 | 17:45:00 | 7.706 | 0.000 | 230.98 | 33.2 | 0.0 | 0.0 | 17.0 | 2006 | 12 | 16 | 2006-12-16 19:45:00+02:00 | 2006-12-16 09:45:00-08:00 |
| 2006-12-16 17:46:00 | 2006-12-16 | 17:46:00 | 7.026 | 0.000 | 232.21 | 30.6 | 0.0 | 0.0 | 16.0 | 2006 | 12 | 16 | 2006-12-16 19:46:00+02:00 | 2006-12-16 09:46:00-08:00 |
| 2006-12-17 09:03:00 | 2006-12-17 | 09:03:00 | 7.064 | 0.124 | 235.57 | 30.0 | 0.0 | 37.0 | 0.0 | 2006 | 12 | 17 | 2006-12-17 11:03:00+02:00 | 2006-12-17 01:03:00-08:00 |
| 2006-12-19 08:47:00 | 2006-12-19 | 08:47:00 | 7.828 | 0.182 | 232.20 | 33.6 | 36.0 | 72.0 | 17.0 | 2006 | 12 | 19 | 2006-12-19 10:47:00+02:00 | 2006-12-19 00:47:00-08:00 |
| 2006-12-19 08:48:00 | 2006-12-19 | 08:48:00 | 7.840 | 0.188 | 232.55 | 33.6 | 36.0 | 71.0 | 16.0 | 2006 | 12 | 19 | 2006-12-19 10:48:00+02:00 | 2006-12-19 00:48:00-08:00 |
For the UCI Individual Household Electric Power Consumption dataset, selecting data for a specific date or period is more important than treating millions of observations as a single, undifferentiated table. We can ask much more meaningful questions such as “What happened on this day?”, “What happened during December?”, or “How much electricity was consumed during the evening?” This provides the foundation for the next step: breaking datetime information into multiple features such as year, month, day, hour, and weekday.
5. Calculating Differences and Elapsed Time in Time-Series Data
Once we have converted timestamps into proper datetime values and extracted useful calendar features, we can ask another important question: how much time has passed between observations or events? In time-series data, the difference between two timestamps can provide valuable information about the frequency of observations, the duration of an event, or the time since a previous measurement.
Let’s continue with the the UCI Individual Household Electric Power Consumption dataset from the previous sections. We first check that we already have a Datetime column and that the data is sorted chronologically.
df = df.sort_values("Datetime")
df[["Datetime", "Global_active_power"]].head()
| Datetime | Global_active_power | |
|---|---|---|
| 0 | 2006-12-16 17:24:00 | 4.216 |
| 1 | 2006-12-16 17:25:00 | 5.360 |
| 2 | 2006-12-16 17:26:00 | 5.374 |
| 3 | 2006-12-16 17:27:00 | 5.388 |
| 4 | 2006-12-16 17:28:00 | 3.666 |
One of the useful properties of pandas datetime objects is that we can simply subtract them.
For example, let’s select two timestamps, and the output is a pandas Timedelta, which represents a duration or difference between two points in time.
time1 = pd.Timestamp("2006-12-16 17:24:00")
time2 = pd.Timestamp("2006-12-16 17:30:00")
difference = time2 - time1
print(difference)
0 days 00:06:00
We can also extracting the number of days, and also the duration in other units, such as the numbers of seconds.
start = pd.Timestamp("2006-12-16")
end = pd.Timestamp("2006-12-20")
elapsed = end - start
print(elapsed)
print(elapsed.days)
print(elapsed.total_seconds())
4 days 00:00:00
4
345600.0
Why can time differences help data analysis and machine learning?
Time differences can be useful features when the spacing between observations is meaningful.
For example, imagine two electricity measurements: “Measurement A → 4.2 kW” and “Measurement B → 5.1 kW”.
The model may benefit from knowing not only the values but also how long passed between A and B?
In regularly sampled data such as this electricity dataset, the answer is usually about one minute.
But in event-based datasets – such as customer purchases, website visits, or medical events – the elapsed time can vary considerably.
6. Creating Lagged Features: Using the Past to Predict the Future
In time-series data analysis and machine learning tasks, the most useful information for predicting what happens next is often found in what happened previously. A lagged feature stores an earlier observation alongside the current observation, allowing a model to learn relationships between past and future values. For the UCI Individual Household Electric Power Consumption dataset, for example, the electricity consumption one minute ago may contain useful information for predicting consumption at the current minute or at a future time.
In this section, we will explore what a lag is, and then create electricity-consumption lagged features using pandas, see how lagged features support forecasting. We continue with the UCI Individual Household Electric Power Consumption dataset, and always start to chronologically sort observations, and thus these observations have a temporal order.
df = df.sort_values("Datetime")
df[["Datetime", "Global_active_power"]].head()
| Datetime | Global_active_power | |
|---|---|---|
| 0 | 2006-12-16 17:24:00 | 4.216 |
| 1 | 2006-12-16 17:25:00 | 5.360 |
| 2 | 2006-12-16 17:26:00 | 5.374 |
| 3 | 2006-12-16 17:27:00 | 5.388 |
| 4 | 2006-12-16 17:28:00 | 3.666 |
6.1 Creating a one-step lag
Suppose the electricity consumption is
Time Power
17:24 4.216
17:25 5.360
17:26 5.374
17:27 5.388
, and a one-step lag looks like this:
Current Time Current Power Previous Power
17:24 4.216 NaN
17:25 5.360 4.216
17:26 5.374 5.360
17:27 5.388 5.374
So when we are at 17:26, the lagged feature tells us what the power consumption was at 17:25.
We can use .shift() in pandas. In below code snippet, the .shift(1) means to move the values down by one row so that the previous observation becomes a feature for the current observation. We can see that the first row contains NaN because there is no previous observation available.
df["Power_Lag_1"] = (df["Global_active_power"].shift(1))
df[["Datetime", "Global_active_power", "Power_Lag_1"]].head()
| Datetime | Global_active_power | Power_Lag_1 | |
|---|---|---|---|
| 0 | 2006-12-16 17:24:00 | 4.216 | NaN |
| 1 | 2006-12-16 17:25:00 | 5.360 | 4.216 |
| 2 | 2006-12-16 17:26:00 | 5.374 | 5.360 |
| 3 | 2006-12-16 17:27:00 | 5.388 | 5.374 |
| 4 | 2006-12-16 17:28:00 | 3.666 | 5.388 |
If one previous observation is not enough, we can create several lagged features.
df["Power_Lag_2"] = (df["Global_active_power"].shift(2))
df["Power_Lag_3"] = (df["Global_active_power"].shift(3))
df[["Datetime", "Global_active_power",
"Power_Lag_1", "Power_Lag_2", "Power_Lag_3"
]].head()
| Datetime | Global_active_power | Power_Lag_1 | Power_Lag_2 | Power_Lag_3 | |
|---|---|---|---|---|---|
| 0 | 2006-12-16 17:24:00 | 4.216 | NaN | NaN | NaN |
| 1 | 2006-12-16 17:25:00 | 5.360 | 4.216 | NaN | NaN |
| 2 | 2006-12-16 17:26:00 | 5.374 | 5.360 | 4.216 | NaN |
| 3 | 2006-12-16 17:27:00 | 5.388 | 5.374 | 5.360 | 4.216 |
| 4 | 2006-12-16 17:28:00 | 3.666 | 5.388 | 5.374 | 5.360 |
Why are lagged features useful?
Electricity consumption often has temporal dependence. In other words, what happens now may be related to what happened recently. For example, if household electricity consumption is currently high, the consumption one minute ago may also have been high.
A model can potentially learn relationships such as:
Power at t-1 → Power at t Power at t-2 → Power at t Power at t-3 → Power at t
where:
t = current time t-1 = one observation ago t-2 = two observations ago t-3 = three observations ago
This is one of the fundamental ideas behind many time-series forecasting approaches.
6.2 Connect lagged features for forecasting
Now let’s connect lagged features for forecasting. Suppose our goal is to predict electricity consumption at the next minute, we can define the future value as our target. One simple approach is to shift target backward df["Target_Next_Minute"] = (df["Global_active_power"].shift(-1)).
df["Target"] = (df["Global_active_power"].shift(-1))
df[["Datetime", "Global_active_power",
"Target"
]].head()
| Datetime | Global_active_power | Target | |
|---|---|---|---|
| 0 | 2006-12-16 17:24:00 | 4.216 | 5.360 |
| 1 | 2006-12-16 17:25:00 | 5.360 | 5.374 |
| 2 | 2006-12-16 17:26:00 | 5.374 | 5.388 |
| 3 | 2006-12-16 17:27:00 | 5.388 | 3.666 |
| 4 | 2006-12-16 17:28:00 | 3.666 | 3.520 |
This means that we can use information available at time t to predict electricity consumption at time t+1. We could even use information at t-1, t-2, and t-3 as predictors. The basic forecasting structure becomes:
Past
↓
t-3 t-2 t-1 t t+1
| | | | |
└───────┴───────┴─────────┘ ↓
Features Prediction
6.3 Building a simple forecasting dataset
Let’s building a simple forecasting dataset by combining these lagged features and the target together.
model_df = df[["Datetime", "Global_active_power"]].copy()
model_df["Lag_3"] = (model_df["Global_active_power"].shift(3))
model_df["Lag_2"] = (model_df["Global_active_power"].shift(2))
model_df["Lag_1"] = (model_df["Global_active_power"].shift(1))
model_df["Target"] = (model_df["Global_active_power"].shift(-1))
model_df = model_df[
["Datetime", "Lag_3", "Lag_2", "Lag_1",
"Global_active_power", "Target",]
]
model_df.head()
| Datetime | Lag_3 | Lag_2 | Lag_1 | Global_active_power | Target | |
|---|---|---|---|---|---|---|
| 0 | 2006-12-16 17:24:00 | NaN | NaN | NaN | 4.216 | 5.360 |
| 1 | 2006-12-16 17:25:00 | NaN | NaN | 4.216 | 5.360 | 5.374 |
| 2 | 2006-12-16 17:26:00 | NaN | 4.216 | 5.360 | 5.374 | 5.388 |
| 3 | 2006-12-16 17:27:00 | 4.216 | 5.360 | 5.374 | 5.388 | 3.666 |
| 4 | 2006-12-16 17:28:00 | 5.360 | 5.374 | 5.388 | 3.666 | 3.520 |
# for a simple demonstration, we remove rows with missing lag values
model_df = model_df.dropna()
model_df.head()
| Datetime | Lag_3 | Lag_2 | Lag_1 | Global_active_power | Target | |
|---|---|---|---|---|---|---|
| 3 | 2006-12-16 17:27:00 | 4.216 | 5.360 | 5.374 | 5.388 | 3.666 |
| 4 | 2006-12-16 17:28:00 | 5.360 | 5.374 | 5.388 | 3.666 | 3.520 |
| 5 | 2006-12-16 17:29:00 | 5.374 | 5.388 | 3.666 | 3.520 | 3.702 |
| 6 | 2006-12-16 17:30:00 | 5.388 | 3.666 | 3.520 | 3.702 | 3.700 |
| 7 | 2006-12-16 17:31:00 | 3.666 | 3.520 | 3.702 | 3.700 | 3.668 |
Data leakage issues in time-series machine learning
Data leakage is one of the most important concepts in time-series machine learning. It occurs when information that would not actually be available at the time of prediction is inadvertently used to train or evaluate a model. Leakage can lead to overly optimistic performance estimates because the model has access to information that would not be available in a real-world forecasting scenario.
For example, suppose we want to predict household electricity consumption at 17:30. At that moment, we may have access to the observations recorded at 17:27, 17:28, and 17:29, which can be used as lagged features to predict the power consumption at 17:30. However, we cannot use the actual power consumption at 17:31 as a feature, because this observation belongs to the future and would not yet be available. Including such information would introduce data leakage and make the model’s performance appear better than it would be in practice.
There is another form of leakage that is especially important in time-series problems: temporal leakage caused by an inappropriate train-test split. Suppose we randomly split the observations into train and test sets: train, test = train_test_split(model_df, test_size=0.2,random_state=42). This approach can be problematic for time-series forecasting because observations from the future may end up in the training set, while earlier observations are placed in the test set. Consequently, the model may learn from information that would not have been available when making predictions for the test period. This can result in overly optimistic evaluation metrics.
For time-series forecasting, we normally preserve chronological order when splitting the data. A representative code example is shown below:
split_date = "2010-01-01"
train = model_df[model_df["Datetime"] < split_date]
test = model_df[model_df["Datetime"] >= split_date]
In this example, the model is trained on observations before January 1, 2010, and evaluated on observations from that date onward. This better reflects the real-world forecasting scenario, where future observations are not available when making predictions.
7. Using Rolling Time Windows
Lagged features allow us to look at individual previous observations, but sometimes a single past value is too noisy to provide a reliable picture of recent behavior. Rolling time windows solve this problem by summarizing a group of recent observations, for example, calculating the average electricity consumption over the previous 5, 30, or 60 minutes.
In this section, we will calculate rolling mean, sum, minimum, and maximum values, check these statistics with smooth short-term fluctuations, and reveal local trends in household electricity usage in the UCI Individual Household Electric Power Consumption dataset.
df = df.sort_values("Datetime")
df[["Datetime", "Global_active_power"]].head()
| Datetime | Global_active_power | |
|---|---|---|
| 0 | 2006-12-16 17:24:00 | 4.216 |
| 1 | 2006-12-16 17:25:00 | 5.360 |
| 2 | 2006-12-16 17:26:00 | 5.374 |
| 3 | 2006-12-16 17:27:00 | 5.388 |
| 4 | 2006-12-16 17:28:00 | 3.666 |
7.1 Creating a rolling window
A rolling window takes a group of consecutive observations and calculates a statistic over that group, that is, it summarizes recent history rather than looking at only one previous observation.
Suppose our electricity consumption is:
Time Power
17:24 4.216
17:25 5.360
17:26 5.374
17:27 5.388
17:28 5.400
A 3-observation rolling mean might look like:
Time Power Rolling Mean
17:24 4.216 NaN
17:25 5.360 NaN
17:26 5.374 4.983
17:27 5.388 5.374
17:28 5.400 5.387
At 17:26, the rolling window contains 17:24, 17:25, 17:26, and pandas calculates the average of those observations, (4.216+5.360+5.374)/3 = 4.983.
For the rolling window, the most common rolling statistic is the rolling mean. Let’s calculate a 5-observation rolling average.
df["Rolling_Mean_5"] = (
df["Global_active_power"]
.rolling(window=5)
.mean()
)
df[[
"Datetime",
"Global_active_power",
"Rolling_Mean_5"
]].head(7)
| Datetime | Global_active_power | Rolling_Mean_5 | |
|---|---|---|---|
| 0 | 2006-12-16 17:24:00 | 4.216 | NaN |
| 1 | 2006-12-16 17:25:00 | 5.360 | NaN |
| 2 | 2006-12-16 17:26:00 | 5.374 | NaN |
| 3 | 2006-12-16 17:27:00 | 5.388 | NaN |
| 4 | 2006-12-16 17:28:00 | 3.666 | 4.8008 |
| 5 | 2006-12-16 17:29:00 | 3.520 | 4.6616 |
| 6 | 2006-12-16 17:30:00 | 3.702 | 4.3300 |
The first four rows will contain NaN because five observations are required to calculate the first complete 5-observation window.
Because this dataset is approximately minute-level data, a 30-observation window represents approximately 30 minutes.
df["Rolling_Mean_30"] = (
df["Global_active_power"]
.rolling(window=30).mean()
)
df[[
"Datetime",
"Global_active_power",
"Rolling_Mean_30"
]].head(40)
| Datetime | Global_active_power | Rolling_Mean_30 | |
|---|---|---|---|
| 0 | 2006-12-16 17:24:00 | 4.216 | NaN |
| 1 | 2006-12-16 17:25:00 | 5.360 | NaN |
| 2 | 2006-12-16 17:26:00 | 5.374 | NaN |
| 3 | 2006-12-16 17:27:00 | 5.388 | NaN |
| 4 | 2006-12-16 17:28:00 | 3.666 | NaN |
| 5 | 2006-12-16 17:29:00 | 3.520 | NaN |
| 6 | 2006-12-16 17:30:00 | 3.702 | NaN |
| 7 | 2006-12-16 17:31:00 | 3.700 | NaN |
| 8 | 2006-12-16 17:32:00 | 3.668 | NaN |
| 9 | 2006-12-16 17:33:00 | 3.662 | NaN |
| 10 | 2006-12-16 17:34:00 | 4.448 | NaN |
| 11 | 2006-12-16 17:35:00 | 5.412 | NaN |
| 12 | 2006-12-16 17:36:00 | 5.224 | NaN |
| 13 | 2006-12-16 17:37:00 | 5.268 | NaN |
| 14 | 2006-12-16 17:38:00 | 4.054 | NaN |
| 15 | 2006-12-16 17:39:00 | 3.384 | NaN |
| 16 | 2006-12-16 17:40:00 | 3.270 | NaN |
| 17 | 2006-12-16 17:41:00 | 3.430 | NaN |
| 18 | 2006-12-16 17:42:00 | 3.266 | NaN |
| 19 | 2006-12-16 17:43:00 | 3.728 | NaN |
| 20 | 2006-12-16 17:44:00 | 5.894 | NaN |
| 21 | 2006-12-16 17:45:00 | 7.706 | NaN |
| 22 | 2006-12-16 17:46:00 | 7.026 | NaN |
| 23 | 2006-12-16 17:47:00 | 5.174 | NaN |
| 24 | 2006-12-16 17:48:00 | 4.474 | NaN |
| 25 | 2006-12-16 17:49:00 | 3.248 | NaN |
| 26 | 2006-12-16 17:50:00 | 3.236 | NaN |
| 27 | 2006-12-16 17:51:00 | 3.228 | NaN |
| 28 | 2006-12-16 17:52:00 | 3.258 | NaN |
| 29 | 2006-12-16 17:53:00 | 3.178 | 4.338733 |
| 30 | 2006-12-16 17:54:00 | 2.720 | 4.288867 |
| 31 | 2006-12-16 17:55:00 | 3.758 | 4.235467 |
| 32 | 2006-12-16 17:56:00 | 4.342 | 4.201067 |
| 33 | 2006-12-16 17:57:00 | 4.512 | 4.171867 |
| 34 | 2006-12-16 17:58:00 | 4.058 | 4.184933 |
| 35 | 2006-12-16 17:59:00 | 2.472 | 4.150000 |
| 36 | 2006-12-16 18:00:00 | 2.790 | 4.119600 |
| 37 | 2006-12-16 18:01:00 | 2.624 | 4.083733 |
| 38 | 2006-12-16 18:02:00 | 2.772 | 4.053867 |
| 39 | 2006-12-16 18:03:00 | 3.740 | 4.056467 |
Compared with the original measurement, we can see that instant measurement may fluctuate considerably from minute to minute, while the rolling mean will generally be smoother.
We can extend the rolling window to approximately one hour. Different window lengths provide different levels of temporal context. In general, a longer rolling window produces smoother output by reducing influence of short-term fluctuations, while a shorter window is more responsive to rapid changes in dataset.
The window size matter!
The size of the rolling window determines how much recent history we summarize.
A rolling window with 5 observations captures very short-term behavior.
A rolling window with 60 observations captures approximately one hour of history in this dataset.
Conceptually:
Small window -> More responsive -> More sensitive to noise
Large window -> More smoothing -> Less sensitive to short-term changes
This is an important modeling decision.
7.2 Rolling sum, minimum & maximum
A rolling window does not have to calculate an average. We can calculate the rolling sum.
df["Rolling_Sum_60"] = (
df["Global_active_power"]
.rolling(window=60).sum()
)
df[[
"Datetime",
"Global_active_power",
"Rolling_Sum_60"
]].head(70)
| Datetime | Global_active_power | Rolling_Sum_60 | |
|---|---|---|---|
| 0 | 2006-12-16 17:24:00 | 4.216 | NaN |
| 1 | 2006-12-16 17:25:00 | 5.360 | NaN |
| 2 | 2006-12-16 17:26:00 | 5.374 | NaN |
| 3 | 2006-12-16 17:27:00 | 5.388 | NaN |
| 4 | 2006-12-16 17:28:00 | 3.666 | NaN |
| ... | ... | ... | ... |
| 65 | 2006-12-16 18:29:00 | 2.920 | 242.844 |
| 66 | 2006-12-16 18:30:00 | 2.930 | 242.072 |
| 67 | 2006-12-16 18:31:00 | 2.912 | 241.284 |
| 68 | 2006-12-16 18:32:00 | 2.608 | 240.224 |
| 69 | 2006-12-16 18:33:00 | 2.714 | 239.276 |
70 rows × 3 columns
This calculates the sum of the previous 60 observations, including the current observation. If each observation represents approximately one minute, this summarizes approximately one hour of measurements.
We can also calculate the minimum/maximum value in a rolling window. This answers “What was the lowest/highest measured power consumption within the recent 60 observations”? Rolling minimum/maximum can be useful when we want to understand the lower/upper boundary of recent activity.
df["Rolling_Min_60"] = (
df["Global_active_power"]
.rolling(window=60)
.min() # .max()
)
df[[
"Datetime",
"Global_active_power",
"Rolling_Min_60"
]].head(65)
| Datetime | Global_active_power | Rolling_Min_60 | |
|---|---|---|---|
| 0 | 2006-12-16 17:24:00 | 4.216 | NaN |
| 1 | 2006-12-16 17:25:00 | 5.360 | NaN |
| 2 | 2006-12-16 17:26:00 | 5.374 | NaN |
| 3 | 2006-12-16 17:27:00 | 5.388 | NaN |
| 4 | 2006-12-16 17:28:00 | 3.666 | NaN |
| ... | ... | ... | ... |
| 60 | 2006-12-16 18:24:00 | 3.452 | 2.308 |
| 61 | 2006-12-16 18:25:00 | 4.870 | 2.308 |
| 62 | 2006-12-16 18:26:00 | 4.868 | 2.308 |
| 63 | 2006-12-16 18:27:00 | 4.866 | 2.308 |
| 64 | 2006-12-16 18:28:00 | 3.176 | 2.308 |
65 rows × 3 columns
Lagged featrues vs. rolling windows
Lagged features and rolling windows both use information from the past, but in different ways.
Lagged features keep specific previous values, such as previou step or the value from three steps ago, so the model can learn how individual past observations relate to current or future value.
A rolling window combines several recent observations and calculates a summary, such as the mean, minimum, or maximum, to capture the recent trend or overall behavior.
In simple terms, lagged features remember specific past values, while rolling windows summarize recent history.
8. Handling Missing Data in Time Series
Talking about missing data in time-series analysis, there are two different kinds of missingness we need to distinguish: a missing value and a missing timestamp. In the the UCI Individual Household Electric Power Consumption dataset, for example, a household’s electricity measurement may be unavailable for a particular minute, or an entire period of timestamps may be absent from the dataset. These situations require different solutions.
In this section, we will first identify missing values and missing timestamps, and then impute missing values using methods such as forward fill, backward fill, and interpolation. A key consideration when applying these methods is to avoid creating unrealistic patterns or introducing future information into a forecasting model.
import pandas as pd
url = "https://raw.githubusercontent.com/jbrownlee/Datasets/master/household_power_consumption.zip"
df = pd.read_csv(url, sep=";", na_values="?")
df["Date"] = pd.to_datetime(df["Date"], format="%d/%m/%Y")
df["Datetime"] = df["Date"] + pd.to_timedelta(df["Time"])
df[["Date", "Time", "Datetime"]].head()
| Date | Time | Datetime | |
|---|---|---|---|
| 0 | 2006-12-16 | 17:24:00 | 2006-12-16 17:24:00 |
| 1 | 2006-12-16 | 17:25:00 | 2006-12-16 17:25:00 |
| 2 | 2006-12-16 | 17:26:00 | 2006-12-16 17:26:00 |
| 3 | 2006-12-16 | 17:27:00 | 2006-12-16 17:27:00 |
| 4 | 2006-12-16 | 17:28:00 | 2006-12-16 17:28:00 |
8.1 Missing values vs. missing timestamps
What is a missing value and what is a missing timestamp? Suppose we have a toy dataset:
Time Power
10:30 2.4
10:31 NaN
10:33 2.8
Here, the timestamp 10:31 exists, but the measurement is missing. This is a missing value.
However, the timestamp 10:32 is completely absent. This is a missing timestamp.
# check how many missing values exist in electricity dataset
print("Dataset shape:", df.shape)
# check missing values across all columns
df.isna().sum()
Dataset shape: (2075259, 10)
Date 0
Time 0
Global_active_power 25979
Global_reactive_power 25979
Voltage 25979
Global_intensity 25979
Sub_metering_1 25979
Sub_metering_2 25979
Sub_metering_3 25979
Datetime 0
dtype: int64
# look at missing observations
missing_power = df[df["Global_active_power"].isna()]
missing_power[["Global_active_power"]].head()
| Global_active_power | |
|---|---|
| 6839 | NaN |
| 6840 | NaN |
| 19724 | NaN |
| 19725 | NaN |
| 41832 | NaN |
From these results, we can figure it out that there are missing values in dataset, but there is no missing timestamps. This dataset contains measurements at a one-minute sampling rate, and all calendar timestamps are present.
8.2 Filling missing values
For time-series data, one of the simplest ways to fill missing values is forward fill. Forward fill means using the most recent available observation (previous value) to fill the missing value.
Suppose the following observations are recorded:
10:30 → 2.4
10:31 → NaN
10:32 → 2.6
Forward fill produces:
10:30 → 2.4
10:31 → 2.4
10:32 → 2.6
df_ffill = df.copy()
# forward fill missing values
df_ffill["Global_active_power"] = (
df_ffill["Global_active_power"]
.ffill()
)
# check whether missing values remain
df_ffill["Global_active_power"].isna().sum()
np.int64(0)
Backward fill works in the opposite direction. It uses the next available observation to fill a missing value. For the same toy dataset shown above, backward fill gives:
10:30 → 2.4
10:31 → 2.6
10:32 → 2.6
df_bfill = df.copy()
# backward fill missing values
df_bfill["Global_active_power"] = (
df_bfill["Global_active_power"]
.bfill()
)
# check whether missing values remain
df_bfill["Global_active_power"].isna().sum()
np.int64(0)
Forward fill or Backward fill?
Forward fill uses the previous value, whereas backward fill uses the next value.
If only one observation is missing in dataset, either forward fill or backward fill is applicable, but neither approach is automatically better.
The choice depends on the meaning of the data and the objectives of the downstream analysis.
Instead of copying one value forward or backward, we can estimate a missing value based on surrounding observations. This is called interpolation. For the same set of observations, we can also estimate the missing value by interpolation. Linear interpolation estimates “10:01 → 2.5” because 2.5 lies halfway between 2.4 and 2.6.
10:30 → 2.4
10:31 → 2.5
10:32 → 2.6
df_interp = df.copy()
# linear interpolation
df_interp["Global_active_power"] = (
df_interp["Global_active_power"]
.interpolate(method="linear")
)
# check whether missing values remain
df_interp[["Global_active_power"]].isna().sum()
Global_active_power 0
dtype: int64
Interpolation is useful when we believe the missing values should lie somewhere between known observations. Besides “linear”, pandas supports several interpolation methods as listed below.
Method |
Description |
|---|---|
“time” |
Uses actual time intervals between observations; useful for time-series data |
“nearest” |
Uses the value from the nearest observation |
“zero” |
Uses the previous value |
“slinear” |
First-order spline interpolation |
“quadratic” |
Quadratic spline interpolation |
“cubic” |
Cubic spline interpolation |
“polynomial” |
Polynomial interpolation; requires order |
“spline” |
Spline interpolation; requires order |
Because we are handling time-series data, it is natural to choose time-aware interpolation. It uses the time information represented by the datetime index when estimating missing values. This is particularly useful when observations are not perfectly equally spaced.
Note
Noted that time-weighted interpolation only works on Series or DataFrames with a DatetimeIndex.
df_interp_time = df.copy()
df_interp_time["Datetime"] = pd.to_datetime(df_interp_time["Datetime"])
df_interp_time = (df_interp_time.set_index("Datetime").sort_index())
df_interp_time.head()
| Date | Time | Global_active_power | Global_reactive_power | Voltage | Global_intensity | Sub_metering_1 | Sub_metering_2 | Sub_metering_3 | |
|---|---|---|---|---|---|---|---|---|---|
| Datetime | |||||||||
| 2006-12-16 17:24:00 | 2006-12-16 | 17:24:00 | 4.216 | 0.418 | 234.84 | 18.4 | 0.0 | 1.0 | 17.0 |
| 2006-12-16 17:25:00 | 2006-12-16 | 17:25:00 | 5.360 | 0.436 | 233.63 | 23.0 | 0.0 | 1.0 | 16.0 |
| 2006-12-16 17:26:00 | 2006-12-16 | 17:26:00 | 5.374 | 0.498 | 233.29 | 23.0 | 0.0 | 2.0 | 17.0 |
| 2006-12-16 17:27:00 | 2006-12-16 | 17:27:00 | 5.388 | 0.502 | 233.74 | 23.0 | 0.0 | 1.0 | 17.0 |
| 2006-12-16 17:28:00 | 2006-12-16 | 17:28:00 | 3.666 | 0.528 | 235.68 | 15.8 | 0.0 | 1.0 | 17.0 |
# time-weighted interpolation
df_interp_time["Global_active_power"] = (
df_interp_time["Global_active_power"]
.interpolate(method="time")
)
# check whether missing values remain
df_interp_time[["Global_active_power"]].isna().sum()
Global_active_power 0
dtype: int64
How about a dataset with a long missing period?
For a dataset with a long missing period, avoid ordinary linear or time interpolation. They may create unrealistic values, especially when the signal changes over time. There are several recommended approach depending on the length of missing period.
Short gaps: Use linear or time interpolation.
Long gaps: Use a model-based method, such as: seasonal averages, interpolation using similar days or hours, time-series forecasting, machine-learning imputation.
Very long gaps: Consider leaving the values missing or removing that period if reliable reconstruction is not possible.
Callout
When working with time-series data, the first question should not simply be “How do I fill the missing values?” Instead, ask “What exactly is missing?”
Missing Data
│
┌─────────┴─────────┐
↓ ↓
Missing value Missing timestamp
│ │
Value is NaN Row is absent
│ │
┌─────┼─────┐ │
↓ ↓ ↓ ↓
ffill bfill interpolate reindex
The central lesson is that there is no universally correct imputation method for time-series data. Forward fill assumes the most recent value remains reasonable, backward fill uses the next available observation, and interpolation estimates values between observations. Most importantly, when preparing data for forecasting, the imputation process must respect the temporal direction of the problem so that future information does not leak into past.
Exercise
Select Bike Sharing Demand dataset or Jena Climate Weather dataset that contains date, time, or time-series information.
import pandas as pd url = "https://raw.githubusercontent.com/TeamLab/machine_learning_from_scratch_with_python/master/code/ch8/data/train.csv" bike = pd.read_csv(url, parse_dates=["datetime"]) bike.head() url = "https://huggingface.co/datasets/sayanroy058/Jena-Climate/resolve/main/jena_climate_2009_2016.csv" jena = pd.read_csv(url) jena["Date Time"] = pd.to_datetime( jena["Date Time"], format="%d.%m.%Y %H:%M:%S" ) jena.head()
Inspect dataset by examining its dimensions, columns, data types, and sample observations.
Calculate descriptive statistics to understand the distribution and characteristics of the numerical variables.
Inspect and convert date and time columns into appropriate pandas datetime representations.
Check for missing values and missing timestamps, and identify gaps or irregularities in the time sequence.
Extract date and time features, such as year, month, day, hour, quarter, day of year, and day of the week.
Create time-based features, including elapsed-time variables, lagged features, and rolling-window statistics where appropriate.
Handle missing observations using suitable techniques such as forward fill, backward fill, or interpolation, while considering the temporal meaning of the data.
Verify the cleaned dataset by checking data types, missing values, chronological ordering, feature values, and the final dataset structure before using it for machine learning.
Keypoints
Convert date and time strings into datetime objects and work with time zones using
tz_localize()andtz_convert().Select and filter time-series observations using dates, time ranges, and DatetimeIndex.
Extract useful temporal information and calculate time differences for analysis and feature engineering.
Create lagged and rolling-window features to capture past observations, trends, and local patterns while avoiding data leakage.
Detect and handle missing values and missing timestamps using techniques such as reindexing, forward fill, backward fill, and interpolation.