按ticker分组筛选距组内最新时间戳整年间隔的DataFrame行

Hey there! Let's tackle this problem cleanly and Pythonically. The core challenge here is that each ticker group has its own latest timestamp, and we need to retain rows that fall exactly 0, 1, 2, ... full years before that date—even when the dates in each group aren't evenly spaced.

Step 1: Prepare Your Data (Ensure Dates Are Datetime) #

First, make sure your date column is converted to a proper datetime type—this is non-negotiable for accurate date calculations. Let's start with sample data matching your example:

import pandas as pd

# Sample input data
data = pd.DataFrame({
    'ticker': ['AA', 'AA', 'AA', 'AA', 'CC', 'CC', 'CC', 'CC', 'CC'],
    'date': ['2019-03-31', '2018-03-31', '2018-06-30', '2017-03-30',
             '2018-12-31', '2017-12-31', '2016-12-31', '2015-12-31', '2014-06-30'],
    'value': [100, 90, 95, 80, 200, 180, 160, 140, 120]
})

# Convert date column to datetime
data['date'] = pd.to_datetime(data['date'])

Step 2: Calculate Group-Specific Latest Dates & Filter Anniversaries #

Instead of using groupby.filter (which works on entire groups, not individual rows), we'll use groupby.transform to broadcast the latest date of each group to every row in that group. Then we can check if each row's date is an exact annual anniversary of the group's latest date.

Basic Solution (Works for Non-Leap Year Cases)

This checks if the month and day match exactly, and the year difference is a non-negative integer:

# Add the latest date for each ticker group as a new column
data['latest_date'] = data.groupby('ticker')['date'].transform('max')

# Calculate year difference between latest date and row date
data['year_diff'] = data['latest_date'].dt.year - data['date'].dt.year

# Check if month and day match exactly (to ensure "exactly 1 year" intervals)
data['is_anniversary'] = (data['latest_date'].dt.month == data['date'].dt.month) & \
                         (data['latest_date'].dt.day == data['date'].dt.day)

# Filter rows that are exact anniversaries (year_diff >= 0 ensures we don't pick future dates)
filtered_data = data[(data['is_anniversary']) & (data['year_diff'] >= 0)]

# Clean up helper columns if needed
filtered_data = filtered_data.drop(['latest_date', 'year_diff', 'is_anniversary'], axis=1)

print(filtered_data)

Robust Solution (Handles Leap Years)

If you need to account for leap days (e.g., 2020-02-29), use pd.DateOffset to verify that adding the year difference to the row's date gives the group's latest date. This avoids edge cases with February 29:

data['latest_date'] = data.groupby('ticker')['date'].transform('max')

# Use DateOffset to check if row date + N years equals latest date
data['is_anniversary'] = data.apply(
    lambda row: (row['date'] + pd.DateOffset(years=row['latest_date'].year - row['date'].year)) == row['latest_date'],
    axis=1
)

filtered_data = data[data['is_anniversary']].drop('latest_date', axis=1)

Output #

Running either solution will give you exactly what you need:

ticker       date  value
0     AA 2019-03-31    100
1     AA 2018-03-31     90
4     CC 2018-12-31    200
5     CC 2017-12-31    180
6     CC 2016-12-31    160
7     CC 2015-12-31    140

Why This Is Pythonic #

• groupby.transform is efficient and avoids the overhead of groupby.apply for row-wise operations.
• Boolean indexing keeps the code readable and aligns with pandas' idiomatic style.
• The leap-year-aware version uses pandas' built-in date utilities to handle edge cases correctly.

内容的提问来源于stack exchange，提问作者Convoxity