Working with Date and Time Series Data in Pandas – A Complete Guide

Introduction

In this article we walk through the essential steps for handling date‑time and time‑series data with pandas. Using a historical Ethereum (ETH) price dataset sampled hourly, we cover reading the data, converting string dates to proper datetime objects, filtering, resampling, and basic plotting.

Loading the Data

• The CSV file (eth_1h.csv) contains columns: date, symbol, open, close, high, low, volume.
• df.shape shows roughly 23,000 rows (≈24 k hourly records).

Converting Strings to datetime

1. Initial check – accessing a single value and calling .day_name() raises AttributeError because the column is a string.
2. Using pd.to_datetime – python df['date'] = pd.to_datetime(df['date']) This fails when pandas cannot infer the format.
3. Providing a format string – The source format is '%Y-%m-%d %I %p' (e.g., 2020-03-13 8 PM). python fmt = '%Y-%m-%d %I %p' df['date'] = pd.to_datetime(df['date'], format=fmt) After conversion, .day_name() correctly returns Friday for March 13 2020.

Parsing Dates While Reading CSV

Instead of a post‑load conversion, dates can be parsed during import:

parse_dates = ['date']
date_parser = lambda x: pd.to_datetime(x, format='%Y-%m-%d %I %p')
df = pd.read_csv('eth_1h.csv', parse_dates=parse_dates, date_parser=date_parser)

The dataframe arrives with a proper datetime index.

Exploring the Date Range

• Earliest date: df['date'].min() → 2017-07-01
• Latest date: df['date'].max() → 2020-03-13
• Time span: (max - min).days → 986 days

Filtering by Date

You can filter with strings or datetime objects:

# All rows from 2020
df_2020 = df[df['date'] >= '2020']
# All rows from 2019
mask = (df['date'] >= '2019-01-01') & (df['date'] < '2020-01-01')
df_2019 = df.loc[mask]

Using actual datetime objects works as well after wrapping them with pd.to_datetime.

Setting the Date as Index and Slicing

df.set_index('date', inplace=True)
# Slice by year
df_2019 = df['2019']
# Slice a custom range
df_jan_feb_2020 = df['2020-01':'2020-02']

Index‑based slicing is concise and leverages pandas’ time‑aware indexing.

Resampling – From Hourly to Daily/Weekly

• Daily high: python daily_high = df['high'].resample('D').max()
• Weekly summary with multiple aggregations: python weekly = df.resample('W').agg({ 'close': 'mean', 'high' : 'max', 'low' : 'min', 'volume': 'sum' }) The resample method accepts frequency strings ('D', 'W', 'M', etc.) and can apply different aggregation functions per column via .agg().

Simple Plotting

After enabling inline plotting in a Jupyter notebook (%matplotlib inline) and installing matplotlib, a quick line plot is:

daily_high.plot()

This produces a clear visual of daily maximum prices.

Putting It All Together

The workflow demonstrated: 1. Load CSV → parse dates (either on import or after). 2. Convert to datetime if needed. 3. Explore min/max and compute time deltas. 4. Filter by specific periods. 5. Set the date column as the index for easy slicing. 6. Resample to desired frequency and aggregate. 7. Plot the resulting series.

These steps give you a solid foundation for any time‑series analysis in pandas.

What’s Next?

The next tutorial will cover reading data from Excel, web APIs, SQL databases, and other sources, expanding the data‑ingestion toolbox.

If you have questions, leave a comment below; I’ll be happy to help.

By mastering date‑time conversion, indexing, filtering, resampling, and basic plotting, you can turn raw timestamped data into actionable insights with just a few pandas commands.