lassence.net

Field notes from the AI frontier

Use date indexes with pandas DataFrames

--- archive: codebook, 2020–2023 · topic: Pandas ---

Pandas provides convenient features to select, slice and aggregate DataFrames that have date or datetime indexes.

Setup

# Import libraries
import pandas as pd

# Read sample data with date index
df = pd.read_csv('data/finance_data.csv', parse_dates=['Date'])
df = df.set_index('Date').loc[lambda x: x['Name'] == 'Nasdaq100', ['Value']]

df
Value
Date
2019-01-026360.87
2019-01-036147.13
2019-01-046422.67
2019-01-076488.25
2019-01-086551.85
……
2020-05-048834.11
2020-05-058930.62
2020-05-068984.86
2020-05-079101.88
2020-05-089220.35

Select dates in DataFrames

With a date index, you can easily select a specific year, month or day.

# Select year
df['2020']
Value
Date
2020-01-028872.22
2020-01-038793.90
2020-01-068848.52
2020-01-078846.45
2020-01-088912.37
……
2020-05-048834.11
2020-05-058930.62
2020-05-068984.86
2020-05-079101.88
2020-05-089220.35
# Select month
df['2020-05']
Value
Date
2020-05-018718.18
2020-05-048834.11
2020-05-058930.62
2020-05-068984.86
2020-05-079101.88
2020-05-089220.35
# Select period interval
df['2020-02-01':'2020-02-10']
Value
Date
2020-02-039126.23
2020-02-049334.06
2020-02-059367.48
2020-02-069445.92
2020-02-079401.10
2020-02-109516.84
# Select period after or before a date
df['2020-04-30':]
Value
Date
2020-04-309000.51
2020-05-018718.18
2020-05-048834.11
2020-05-058930.62
2020-05-068984.86
2020-05-079101.88
2020-05-089220.35

Aggregate rows

It is also trivial to apply aggregation at a time level, like month or week for example, and compute metrics with resample().

# Number of data points per month
df['2020'].resample('M').count()
Value
Date
2020-01-3121
2020-02-2919
2020-03-3122
2020-04-3021
2020-05-316
# Mean value per week
df['2020-04'].resample('W').mean()
Value
Date
2020-04-057550.0200
2020-04-128149.7600
2020-04-198641.4200
2020-04-268644.4500
2020-05-038874.6325