Datetime Handling

Datetime Handling#

Working with dates and times is essential when analyzing time series data from building sensors and energy meters. pandas provides powerful tools for parsing, manipulating, and indexing datetime data.

import pandas as pd

Converting Strings to Datetime#

When loading CSV files, date and time columns are read as text (dtype object in pandas 2, str in pandas 3). Use pd.to_datetime() to convert them.

url = "https://raw.githubusercontent.com/retomarek/edap/main/edap/sampleData/flatElectricity.csv"

df = pd.read_csv(url, sep=";")
df.head()
time FlatA_Ele FlatB_Ele FlatC_Ele FlatD_Ele
0 2018-11-30 23:45:00 5619.889 7246.254 5125.006 8387.030
1 2018-12-01 00:00:00 5619.904 7246.551 5125.039 8387.085
2 2018-12-01 00:15:00 5619.929 7246.753 5125.077 8387.137
3 2018-12-01 00:30:00 5619.952 7246.933 5125.117 8387.181
4 2018-12-01 00:45:00 5619.973 7247.005 5125.153 8387.222 
df.dtypes

time             str
FlatA_Ele    float64
FlatB_Ele    float64
FlatC_Ele    float64
FlatD_Ele    float64
dtype: object

Note

The time column is still text (dtype object in pandas 2, str in pandas 3). We need to convert it to datetime64.

df["time"] = pd.to_datetime(df["time"])
df.dtypes

time         datetime64[us]
FlatA_Ele           float64
FlatB_Ele           float64
FlatC_Ele           float64
FlatD_Ele           float64
dtype: object

Format Strings#

If the date format is non-standard, you can specify a format string to help pandas parse it correctly.

Code

Meaning

Example

%Y

4-digit year

2023

%m

Month (zero-padded)

01, 12

%d

Day (zero-padded)

01, 31

%H

Hour (24h)

00, 23

%M

Minute

00, 59

%S

Second

00, 59

 
# Example with explicit format
dates = pd.Series(["03.10.2018 00:00", "03.10.2018 01:00", "03.10.2018 02:00"])
pd.to_datetime(dates, format="%d.%m.%Y %H:%M")

0   2018-10-03 00:00:00
1   2018-10-03 01:00:00
2   2018-10-03 02:00:00
dtype: datetime64[us]

Tip

Specifying the format parameter explicitly is faster than letting pandas infer it, especially for large data sets.

Setting a Datetime Index#

Setting the datetime column as the DataFrame index enables powerful time-based slicing and resampling.

df = df.set_index("time")
df.head()
FlatA_Ele FlatB_Ele FlatC_Ele FlatD_Ele
time
2018-11-30 23:45:00 5619.889 7246.254 5125.006 8387.030
2018-12-01 00:00:00 5619.904 7246.551 5125.039 8387.085
2018-12-01 00:15:00 5619.929 7246.753 5125.077 8387.137
2018-12-01 00:30:00 5619.952 7246.933 5125.117 8387.181
2018-12-01 00:45:00 5619.973 7247.005 5125.153 8387.222 
df.index

DatetimeIndex(['2018-11-30 23:45:00', '2018-12-01 00:00:00',
               '2018-12-01 00:15:00', '2018-12-01 00:30:00',
               '2018-12-01 00:45:00', '2018-12-01 01:00:00',
               '2018-12-01 01:15:00', '2018-12-01 01:30:00',
               '2018-12-01 01:45:00', '2018-12-01 02:00:00',
               ...
               '2020-05-31 21:45:00', '2020-05-31 22:00:00',
               '2020-05-31 22:15:00', '2020-05-31 22:30:00',
               '2020-05-31 22:45:00', '2020-05-31 23:00:00',
               '2020-05-31 23:15:00', '2020-05-31 23:30:00',
               '2020-05-31 23:45:00', '2020-06-01 00:00:00'],
              dtype='datetime64[us]', name='time', length=52610, freq=None)

# Slice by date range
df.loc["2019-01-01":"2019-01-03"]
FlatA_Ele FlatB_Ele FlatC_Ele FlatD_Ele
time
2019-01-01 00:00:00 5849.920 7576.754 5289.692 8742.399
2019-01-01 00:15:00 5849.925 7576.782 5289.714 8742.508
2019-01-01 00:30:00 5849.931 7576.803 5289.731 8742.626
2019-01-01 00:45:00 5849.939 7576.823 5289.746 8742.741
2019-01-01 01:00:00 5849.953 7576.844 5289.761 8742.844
... ... ... ... ...
2019-01-03 22:45:00 5852.718 7583.857 5295.587 8783.319
2019-01-03 23:00:00 5852.722 7583.877 5295.608 8783.358
2019-01-03 23:15:00 5852.727 7583.897 5295.620 8783.389
2019-01-03 23:30:00 5852.741 7583.917 5295.633 8783.417
2019-01-03 23:45:00 5852.754 7583.939 5295.645 8783.445

288 rows × 4 columns

The .dt Accessor#

The .dt accessor provides convenient properties for extracting components from datetime columns.

Note

The .dt accessor works on columns (Series), not on a DatetimeIndex. If the datetime is the index, access the properties directly (e.g., df.index.year).

# Reset index to use .dt accessor on the column
df_reset = df.reset_index()
df_reset.head()
time FlatA_Ele FlatB_Ele FlatC_Ele FlatD_Ele
0 2018-11-30 23:45:00 5619.889 7246.254 5125.006 8387.030
1 2018-12-01 00:00:00 5619.904 7246.551 5125.039 8387.085
2 2018-12-01 00:15:00 5619.929 7246.753 5125.077 8387.137
3 2018-12-01 00:30:00 5619.952 7246.933 5125.117 8387.181
4 2018-12-01 00:45:00 5619.973 7247.005 5125.153 8387.222 
# Extract year
df_reset["time"].dt.year.head()

0    2018
1    2018
2    2018
3    2018
4    2018
Name: time, dtype: int32

# Extract month
df_reset["time"].dt.month.head()

0    11
1    12
2    12
3    12
4    12
Name: time, dtype: int32

# Extract day
df_reset["time"].dt.day.head()

0    30
1     1
2     1
3     1
4     1
Name: time, dtype: int32

# Extract hour
df_reset["time"].dt.hour.head()

0    23
1     0
2     0
3     0
4     0
Name: time, dtype: int32

# Day of the week (Monday=0, Sunday=6)
df_reset["time"].dt.day_of_week.head()

0    4
1    5
2    5
3    5
4    5
Name: time, dtype: int32

# Custom string formatting
df_reset["time"].dt.strftime("%A, %d %B %Y").head()

0      Friday, 30 November 2018
1    Saturday, 01 December 2018
2    Saturday, 01 December 2018
3    Saturday, 01 December 2018
4    Saturday, 01 December 2018
Name: time, dtype: str

Adding Derived Columns#

The .dt accessor is useful for creating new columns that group data by time period.

df_reset["year"] = df_reset["time"].dt.year
df_reset["month"] = df_reset["time"].dt.month
df_reset["hour"] = df_reset["time"].dt.hour
df_reset["day_of_week"] = df_reset["time"].dt.day_of_week

df_reset.head()
time FlatA_Ele FlatB_Ele FlatC_Ele FlatD_Ele year month hour day_of_week
0 2018-11-30 23:45:00 5619.889 7246.254 5125.006 8387.030 2018 11 23 4
1 2018-12-01 00:00:00 5619.904 7246.551 5125.039 8387.085 2018 12 0 5
2 2018-12-01 00:15:00 5619.929 7246.753 5125.077 8387.137 2018 12 0 5
3 2018-12-01 00:30:00 5619.952 7246.933 5125.117 8387.181 2018 12 0 5
4 2018-12-01 00:45:00 5619.973 7247.005 5125.153 8387.222 2018 12 0 5

Easier with pyedautils: adding a season column#

The code above extracts year, month, hour, and day of week manually. For season detection (spring, summer, fall, winter), the logic is more complex — it depends on solstice and equinox dates that change every year.

The pyedautils package provides a get_season() function that handles this automatically:

from pyedautils.data_prep.season import get_season

# Add a season column — one line instead of complex date logic
df_reset["season"] = df_reset["time"].apply(get_season)

df_reset[["time", "year", "month", "season"]].drop_duplicates(subset=["month", "season"]).head(12)
time year month season
0 2018-11-30 23:45:00 2018 11 Fall
1 2018-12-01 00:00:00 2018 12 Fall
2011 2018-12-21 22:30:00 2018 12 Winter
2977 2019-01-01 00:00:00 2019 1 Winter
5953 2019-02-01 00:00:00 2019 2 Winter
8641 2019-03-01 00:00:00 2019 3 Winter
10553 2019-03-20 22:00:00 2019 3 Spring
11617 2019-04-01 00:00:00 2019 4 Spring
14497 2019-05-01 00:00:00 2019 5 Spring
17473 2019-06-01 00:00:00 2019 6 Spring
19457 2019-06-21 16:00:00 2019 6 Summer
20353 2019-07-01 00:00:00 2019 7 Summer

get_season() options

Parameter

Default

Description

hemisphere

"north"

"north" or "south" — seasons are reversed in the southern hemisphere

labels

["Spring", "Summer", "Fall", "Winter"]

Custom season names, e.g. ["Frühling", "Sommer", "Herbst", "Winter"]

tracking_type

"astronomical"

"astronomical" (based on solstice/equinox) or "meteorological" (fixed months: Mar-May, Jun-Aug, Sep-Nov, Dec-Feb)

Example with German labels and meteorological seasons:

df["saison"] = df["time"].apply(
    get_season,
    labels=["Frühling", "Sommer", "Herbst", "Winter"],
    tracking_type="meteorological"
)

Time Zones#

By default, datetime objects in pandas are timezone-naive. You can localize them to a specific timezone and convert between timezones.

# Create a small example
dates = pd.date_range("2023-06-15 12:00", periods=4, freq="h")
s = pd.Series([22.1, 22.5, 23.0, 22.8], index=dates)
s

2023-06-15 12:00:00    22.1
2023-06-15 13:00:00    22.5
2023-06-15 14:00:00    23.0
2023-06-15 15:00:00    22.8
Freq: h, dtype: float64

# Localize to a timezone (assign a timezone to naive datetimes)
s_zurich = s.tz_localize("Europe/Zurich")
s_zurich

2023-06-15 12:00:00+02:00    22.1
2023-06-15 13:00:00+02:00    22.5
2023-06-15 14:00:00+02:00    23.0
2023-06-15 15:00:00+02:00    22.8
dtype: float64

# Convert to another timezone
s_utc = s_zurich.tz_convert("UTC")
s_utc

2023-06-15 10:00:00+00:00    22.1
2023-06-15 11:00:00+00:00    22.5
2023-06-15 12:00:00+00:00    23.0
2023-06-15 13:00:00+00:00    22.8
dtype: float64

Daylight saving time#

Local time has one hour per year that occurs twice (end of October) and one that does not exist (end of March). Meter exports in local time often show a repeated or a missing hour there. Tell tz_localize how to handle them, or store and exchange data in UTC to avoid the problem.

# Autumn: 02:30 occurs twice -> "infer" assigns summer time, then winter time, from the order
autumn = pd.Series(pd.to_datetime(["2023-10-29 01:30", "2023-10-29 02:30", "2023-10-29 02:30", "2023-10-29 03:30"]))
print(autumn.dt.tz_localize("Europe/Zurich", ambiguous="infer"))

# Spring: 02:30 does not exist -> NaT (or nonexistent="shift_forward")
spring = pd.Series(pd.to_datetime(["2023-03-26 01:30", "2023-03-26 02:30", "2023-03-26 03:30"]))
print(spring.dt.tz_localize("Europe/Zurich", nonexistent="NaT"))

0   2023-10-29 01:30:00+02:00
1   2023-10-29 02:30:00+02:00
2   2023-10-29 02:30:00+01:00
3   2023-10-29 03:30:00+01:00
dtype: datetime64[us, Europe/Zurich]
0   2023-03-26 01:30:00+01:00
1                         NaT
2   2023-03-26 03:30:00+02:00
dtype: datetime64[us, Europe/Zurich]

Creating Date Ranges#

pd.date_range() creates a sequence of evenly spaced datetime values. This is useful for creating reference time axes or filling gaps.

# Hourly timestamps for one week (7 x 24 hours)
date_range = pd.date_range(start="2023-01-01", periods=7 * 24, freq="h")
print(f"Number of timestamps: {len(date_range)}")
date_range[:10]

Number of timestamps: 168

DatetimeIndex(['2023-01-01 00:00:00', '2023-01-01 01:00:00',
               '2023-01-01 02:00:00', '2023-01-01 03:00:00',
               '2023-01-01 04:00:00', '2023-01-01 05:00:00',
               '2023-01-01 06:00:00', '2023-01-01 07:00:00',
               '2023-01-01 08:00:00', '2023-01-01 09:00:00'],
              dtype='datetime64[us]', freq='h')

# Daily timestamps for one year
daily_range = pd.date_range(start="2023-01-01", periods=365, freq="D")
daily_range[:10]

DatetimeIndex(['2023-01-01', '2023-01-02', '2023-01-03', '2023-01-04',
               '2023-01-05', '2023-01-06', '2023-01-07', '2023-01-08',
               '2023-01-09', '2023-01-10'],
              dtype='datetime64[us]', freq='D')

# 15-minute intervals
quarter_hourly = pd.date_range(start="2023-01-01", periods=96, freq="15min")
quarter_hourly[:10]

DatetimeIndex(['2023-01-01 00:00:00', '2023-01-01 00:15:00',
               '2023-01-01 00:30:00', '2023-01-01 00:45:00',
               '2023-01-01 01:00:00', '2023-01-01 01:15:00',
               '2023-01-01 01:30:00', '2023-01-01 01:45:00',
               '2023-01-01 02:00:00', '2023-01-01 02:15:00'],
              dtype='datetime64[us]', freq='15min')

Tip

Common frequency strings: "h" (hourly), "D" (daily), "W" (weekly), "MS" (month start), "15min" (15 minutes), "min" (1 minute).

Practical Example#

Let’s put it all together with the temperature and humidity data set.

url = "https://raw.githubusercontent.com/retomarek/edap/main/edap/sampleData/flatTempHum.csv"

df = pd.read_csv(url, sep=";")

# Convert time column
df["time"] = pd.to_datetime(df["time"])

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

df.head()
FlatA_Hum FlatA_Temp FlatB_Hum FlatB_Temp FlatC_Hum FlatC_Temp FlatD_Hum FlatD_Temp
time
2018-10-03 00:00:00 53.0 24.43 38.8 22.40 44.0 24.5 49.0 24.43
2018-10-03 01:00:00 53.0 24.40 38.8 22.40 44.0 24.5 49.0 24.40
2018-10-03 02:00:00 53.0 24.40 39.3 22.40 44.7 24.5 48.3 24.38
2018-10-03 03:00:00 53.0 24.40 40.3 22.40 45.0 24.5 48.0 24.33
2018-10-03 04:00:00 53.3 24.40 41.0 22.37 45.2 24.5 47.7 24.30 
# Check the time range of the data
print(f"Start: {df.index.min()}")
print(f"End:   {df.index.max()}")
print(f"Duration: {df.index.max() - df.index.min()}")

Start: 2018-10-03 00:00:00
End:   2020-09-17 20:00:00
Duration: 715 days 20:00:00

# Select data for a specific month
jan_2019 = df.loc["2019-01"]
jan_2019.head()
FlatA_Hum FlatA_Temp FlatB_Hum FlatB_Temp FlatC_Hum FlatC_Temp FlatD_Hum FlatD_Temp
time
2019-01-01 00:00:00 50.0 22.85 45.0 22.85 44.3 22.42 48.0 23.80
2019-01-01 01:00:00 50.0 22.80 43.7 22.85 45.8 22.48 47.2 23.73
2019-01-01 02:00:00 50.0 22.80 42.2 22.80 47.0 22.50 47.0 23.70
2019-01-01 03:00:00 50.0 22.80 41.2 22.73 47.8 22.50 47.0 23.70
2019-01-01 04:00:00 50.0 22.70 40.2 22.65 48.0 22.50 NaN NaN 
# Resample to daily mean temperature
df_daily = df[["FlatA_Temp"]].resample("D").mean()
df_daily.head()
FlatA_Temp
time
2018-10-03 24.200435
2018-10-04 24.232083
2018-10-05 24.210435
2018-10-06 24.180435
2018-10-07 24.296667

When to use .mean() vs .sum() vs .last()?

The right aggregation method depends on what the data represents:

Data type

Example

Method

Why

Measurement

Temperature, humidity, CO2

.mean()

Average over the period

Cumulative counter

Electricity meter, gas meter

.resample("D").last().diff()

Consumption = difference between the readings at the end of each period

Event count

Number of door openings

.sum()

Total events per period

State

On/off, occupancy

.last() or .mode()

Last known state or most frequent

For counters you can also take .diff() first and then .resample("D").sum(min_count=96) (for 15-minute data): min_count drops days with missing readings instead of reporting them as days with low consumption.