EDS 217 Cheatsheet

datetime and Time Series

Introduction

This cheatsheet covers essential datetime and time series operations in pandas needed for environmental data analysis.

Setup

Code 
import pandas as pd
import matplotlib.pyplot as plt
import math
import random

# Set random seed for reproducible examples
random.seed(42)

# Create sample time series data
dates = pd.date_range('2020-01-01', '2023-12-31', freq='ME')

temperature = []
co2 = []
level = 400.0
for i in range(len(dates)):
    temperature.append(15 + 10 * math.sin(i * 2 * math.pi / 12) + random.gauss(0, 5))
    level = level + random.gauss(0.1, 0.5)
    co2.append(level)

sample_df = pd.DataFrame({
    'date': dates,
    'temperature': temperature,
    'co2': co2
})

Loading Time Series Data

Reading CSV with Date Parsing

Code 
# Parse dates while reading a CSV (the way you will do it on real data)
# df = pd.read_csv('https://eds-217-essential-python.github.io/data/some_file.csv',
#                  parse_dates=['Date'])

# For demonstration here, round-trip the sample data through text rather than a file
from io import StringIO
df = pd.read_csv(StringIO(sample_df.to_csv(index=False)), parse_dates=['date'])

print(f"Date column dtype: {df['date'].dtype}")
print(f"Data shape: {df.shape}")
Date column dtype: datetime64[ns]
Data shape: (48, 3)

Converting Strings to Datetime

Code 
# Convert year-only data to datetime (common in environmental datasets)
year_data = pd.DataFrame({
    'year': [2020, 2021, 2022, 2023],
    'annual_temp': [14.5, 15.2, 14.8, 15.6]
})

year_data['date'] = pd.to_datetime(year_data['year'], format='%Y')
print(year_data.head())
   year  annual_temp       date
0  2020         14.5 2020-01-01
1  2021         15.2 2021-01-01
2  2022         14.8 2022-01-01
3  2023         15.6 2023-01-01

Setting DateTime Index

Code 
# Set date column as index for time series operations
df_indexed = df.set_index('date')
print(f"Index is datetime: {pd.api.types.is_datetime64_any_dtype(df_indexed.index)}")
print(df_indexed.head())
Index is datetime: True
            temperature         co2
date                               
2020-01-31    14.279548  400.013548
2020-02-29    19.443421  400.464540
2020-03-31    23.022313  399.815863
2020-04-30    26.661592  399.782195
2020-05-31    22.575461  399.940137

Extracting Date Components

Code 
# Extract date components using .dt accessor
df['year'] = df['date'].dt.year
df['month'] = df['date'].dt.month
df['quarter'] = df['date'].dt.quarter

# Create decade grouping (useful for climate analysis)
df['decade'] = (df['date'].dt.year / 10).astype(int) * 10

print(df[['date', 'year', 'month', 'decade']].head())
        date  year  month  decade
0 2020-01-31  2020      1    2020
1 2020-02-29  2020      2    2020
2 2020-03-31  2020      3    2020
3 2020-04-30  2020      4    2020
4 2020-05-31  2020      5    2020

Creating Seasonal Groupings

Code 
# Define seasonal groupings as constants
SEASON_MAP = {
    12: 'Winter', 1: 'Winter', 2: 'Winter',
    3: 'Spring', 4: 'Spring', 5: 'Spring', 
    6: 'Summer', 7: 'Summer', 8: 'Summer',
    9: 'Fall', 10: 'Fall', 11: 'Fall'
}

def get_season(date):
    """Assign season based on month (Northern Hemisphere)."""
    return SEASON_MAP[date.month]

# Apply season function
df['season'] = df['date'].apply(get_season)

print(df[['date', 'season']].head())
        date  season
0 2020-01-31  Winter
1 2020-02-29  Winter
2 2020-03-31  Spring
3 2020-04-30  Spring
4 2020-05-31  Spring

Time Series Resampling

Basic Resampling

Code 
# Resample monthly data to annual averages
annual_avg = df_indexed.resample('YE').mean(numeric_only=True)
print("Annual averages:")
print(annual_avg.head())

# Resample to quarterly data
quarterly_data = df_indexed.resample('QE').mean(numeric_only=True)
print("Quarterly averages:")
print(quarterly_data.head())
Annual averages:
            temperature         co2
date                               
2020-12-31    15.162698  400.459906
2021-12-31    15.788746  400.144374
2022-12-31    15.151169  402.093794
2023-12-31    15.635156  403.700269
Quarterly averages:
            temperature         co2
date                               
2020-03-31    18.915094  400.097984
2020-06-30    23.466180  400.114749
2020-09-30    10.721010  400.757463
2020-12-31     7.548508  400.869430
2021-03-31    22.940636  400.867512
Handling Mixed Data Types

When your DataFrame contains both numeric and non-numeric columns, use numeric_only=True with aggregation functions to prevent errors.

Common Frequency Codes

Code Description Example
D Daily 2023-01-01, 2023-01-02, โ€ฆ
ME Month End 2023-01-31, 2023-02-28, โ€ฆ
QE Quarter End 2023-03-31, 2023-06-30, โ€ฆ
YE Year End 2023-12-31, 2024-12-31, โ€ฆ

Time Series Calculations

Year-over-Year Changes

Code 
# Calculate year-over-year changes
annual_data = df_indexed.resample('YE').mean(numeric_only=True)
annual_data['temp_change'] = annual_data['temperature'].diff()
annual_data['co2_change'] = annual_data['co2'].diff()

print("Year-over-year changes:")
print(annual_data[['temperature', 'temp_change', 'co2', 'co2_change']])
Year-over-year changes:
            temperature  temp_change         co2  co2_change
date                                                        
2020-12-31    15.162698          NaN  400.459906         NaN
2021-12-31    15.788746     0.626048  400.144374   -0.315532
2022-12-31    15.151169    -0.637578  402.093794    1.949420
2023-12-31    15.635156     0.483987  403.700269    1.606475

Rolling Averages

Code 
# Calculate 12-month rolling average
df_indexed['temp_rolling'] = df_indexed['temperature'].rolling(window=12).mean()

print("Original vs rolling average:")
print(df_indexed[['temperature', 'temp_rolling']].head(15))
Original vs rolling average:
            temperature  temp_rolling
date                                 
2020-01-31    14.279548           NaN
2020-02-29    19.443421           NaN
2020-03-31    23.022313           NaN
2020-04-30    26.661592           NaN
2020-05-31    22.575461           NaN
2020-06-30    21.161489           NaN
2020-07-31    18.283183           NaN
2020-08-31     6.308392           NaN
2020-09-30     7.571457           NaN
2020-10-31     5.208284           NaN
2020-11-30     8.998627           NaN
2020-12-31     8.438613     15.162698
2021-01-31    19.367022     15.586654
2021-02-28    21.882999     15.789953
2021-03-31    27.571888     16.169084

Time Series Filtering

Selecting Date Ranges

Code 
# Select data from specific year
data_2022 = df_indexed[df_indexed.index.year == 2022]
print(f"Data from 2022: {len(data_2022)} records")

# Select data from date range
recent_data = df_indexed.loc['2023-01-01':'2023-06-30']
print(f"First half 2023: {len(recent_data)} records")

# Filter using datetime conditions
modern_data = df[df['date'] >= pd.to_datetime('2022-01-01')]
print(f"Modern data (2022+): {len(modern_data)} records")
Data from 2022: 12 records
First half 2023: 6 records
Modern data (2022+): 24 records

Filtering by Date Components

Code 
# Filter by month (e.g., all January data)
january_data = df[df['date'].dt.month == 1]

# Filter by season
winter_data = df[df['season'] == 'Winter']

# Filter by decade
recent_decade = df[df['decade'] == 2020]

print(f"January records: {len(january_data)}")
print(f"Winter records: {len(winter_data)}")
print(f"2020s decade: {len(recent_decade)}")
January records: 4
Winter records: 12
2020s decade: 48

Merging Time Series Data

Code 
# Create second dataset with different frequency
daily_dates = pd.date_range('2022-01-01', '2022-12-31', freq='D')
random.seed(123)

precipitation = []
for i in range(len(daily_dates)):
    precipitation.append(random.expovariate(1 / 2))

daily_data = pd.DataFrame({'precipitation': precipitation}, index=daily_dates)

# Resample to monthly to match original data frequency
monthly_precip = daily_data.resample('ME').sum()

# Merge with existing data
merged_data = pd.merge(df_indexed, monthly_precip, 
                      left_index=True, right_index=True, how='inner')

print("Merged time series data:")
print(merged_data.head())
Merged time series data:
            temperature         co2  temp_rolling  precipitation
2022-01-31    11.865073  400.222340     15.163584      43.182268
2022-02-28    17.650159  400.572003     14.810847      50.977277
2022-03-31    22.409676  401.839880     14.380663      56.209820
2022-04-30    20.903537  401.390443     13.802520      59.810817
2022-05-31    27.502622  402.201368     15.214361      60.352277

Key Reminders

  1. Always parse dates when loading data using parse_dates parameter
  2. Set datetime as index for time series operations with set_index()
  3. Use .dt accessor to extract date components from datetime columns
  4. Use numeric_only=True when resampling mixed data types
  5. Use consistent frequency codes for resampling operations