Skip to main content

Working with Dates and Times in pandas

Handling date and time data is crucial for many data science tasks, especially when dealing with time series data. Pandas provides robust tools to work with dates and times, making it easy to perform operations like date parsing, indexing by date, and resampling time series data. In this article, we'll explore how to effectively manage date and time data using pandas.

1. Parsing Dates in pandas​

When working with datasets that contain date information, it’s essential to correctly parse and convert these strings into pandas datetime objects.

1.1 Converting Strings to Datetime​

Pandas provides the pd.to_datetime() function, which can automatically parse various date formats.

import pandas as pd

# Sample data with date strings
data = {
    'Date': ['2024-01-01', '2024-02-01', '2024-03-01'],
    'Value': [100, 200, 300]
}
df = pd.DataFrame(data)

# Converting the 'Date' column to datetime
df['Date'] = pd.to_datetime(df['Date'])
print("DataFrame with parsed dates:\n", df)

1.2 Custom Date Formats​

If your date strings follow a custom format, you can specify the format using the format parameter.

# Sample data with a custom date format
data = {
    'Date': ['01-01-2024', '01-02-2024', '01-03-2024'],
    'Value': [100, 200, 300]
}
df = pd.DataFrame(data)

# Converting with a custom date format
df['Date'] = pd.to_datetime(df['Date'], format='%d-%m-%Y')
print("DataFrame with custom formatted dates:\n", df)

2. Indexing and Slicing Time Series Data​

Time series data often benefits from being indexed by date, which allows for powerful slicing and resampling operations.

2.1 Setting a Datetime Index​

You can set a column containing datetime objects as the index of your DataFrame.

# Setting the 'Date' column as the index
df.set_index('Date', inplace=True)
print("DataFrame with DatetimeIndex:\n", df)

2.2 Slicing Data by Date​

Once a DataFrame is indexed by date, you can easily slice it by specific dates or date ranges.

# Slicing the DataFrame by a specific date
slice_date = df.loc['2024-02-01']
print("Data for 2024-02-01:\n", slice_date)

# Slicing the DataFrame by a date range
slice_range = df.loc['2024-01-01':'2024-02-01']
print("Data from 2024-01-01 to 2024-02-01:\n", slice_range)

3. Resampling Time Series Data​

Resampling is the process of changing the frequency of your time series data, such as aggregating daily data into monthly data.

3.1 Downsampling​

Downsampling reduces the frequency of the data by aggregating it over a specified time period. The resample() method is commonly used for this.

# Sample time series data
date_range = pd.date_range(start='2024-01-01', periods=90, freq='D')
data = pd.Series(range(90), index=date_range)

# Downsampling to monthly frequency using sum
monthly_data = data.resample('ME').sum()
print("Monthly aggregated data:\n", monthly_data)

3.2 Upsampling​

Upsampling increases the frequency of the data, often requiring methods to fill in missing values.

# Upsampling to hourly frequency
hourly_data = data.resample('h').ffill()  # Forward fill to fill missing values
print("Hourly data with forward fill:\n", hourly_data.head(10))

3.3 Rolling and Expanding Windows​

Rolling and expanding operations allow you to apply a function (e.g., sum, mean) over a moving window of your data.

# Rolling window with a window size of 7 days
rolling_mean = data.rolling(window=7).mean()
print("7-day rolling mean:\n", rolling_mean.head(10))

4. Extracting Date Components​

You can extract specific components from a datetime object, such as the year, month, day, hour, and minute, which can be useful for feature engineering in machine learning models.

4.1 Extracting Components​

Pandas makes it easy to extract components from datetime objects using the .dt accessor.

# Extracting year, month, and day
df['Year'] = df.index.year
df['Month'] = df.index.month
df['Day'] = df.index.day
print("DataFrame with extracted date components:\n", df)

4.2 Creating Time Deltas​

You can create time deltas to represent differences between dates or to add/subtract time periods from dates.

# Adding 7 days to each date
df['Next Week'] = df.index + pd.Timedelta(days=7)
print("DataFrame with dates for the next week:\n", df)

5. Working with Time Zones​

Handling time zones correctly is essential for global datasets. Pandas supports time zone conversions and manipulations.

5.1 Localizing Time Series​

You can localize naive datetime objects to a specific time zone using the tz_localize() method.

# Localizing to UTC
df.index = df.index.tz_localize('UTC')
print("DataFrame with UTC time zone:\n", df)

5.2 Converting Time Zones​

Once localized, you can convert datetime objects to different time zones using tz_convert().

# Converting to US/Eastern time zone
df.index = df.index.tz_convert('US/Eastern')
print("DataFrame with US/Eastern time zone:\n", df)

Version Notes and Verified Behavior​

Give to_datetime() an explicit format when the source format is known, and choose deliberately how invalid values should be handled. tz_localize() assigns a time zone to naive timestamps; tz_convert() changes already-aware timestamps to another zone. Daylight-saving transitions can create ambiguous or nonexistent local times, so production pipelines should specify the corresponding policies.

timestamps = pd.to_datetime(['2024-01-01 12:00'], format='%Y-%m-%d %H:%M')
utc = timestamps.tz_localize('UTC')
berlin = utc.tz_convert('Europe/Berlin')

assert str(utc.tz) == 'UTC'
assert berlin.hour.tolist() == [13]
assert berlin.tz_convert('UTC').equals(utc)

Official references: time series and date functionality, to_datetime, and DatetimeIndex.tz_localize.

6. Conclusion​

Effectively handling date and time data is crucial for time series analysis and many other data science tasks. Pandas provides a comprehensive set of tools to parse, manipulate, and analyze date and time data with ease. By mastering these techniques, you’ll be well-equipped to handle time-based data and perform complex time series analyses. In the next articles, we’ll continue to explore more advanced functionalities of pandas and their applications in data science.