Pandas：如何基于固定起始日期与变动结束日期分组统计唯一值？

Great question! Calculating rolling cumulative unique counts efficiently in Pandas is a common need, and you’re right to avoid loops—there are vectorized approaches that work well even for large datasets. Let’s break this down step by step.

Step 1: Preprocess Your Data #

First, we need to convert the date column to a datetime type (critical for grouping by year/month) and extract grouping keys for year and year-month:

import pandas as pd

# Your sample data
df = pd.DataFrame({
    'date': ['01-01-2012', '02-01-2012', '05-01-2012', '05-01-2012',
             '01-02-2012', '02-02-2012', '02-02-2012', '05-02-2012'],
    'value': ['a', 'b', 'c', 'c', 'a', 'a', 'b', 'd']
})

# Convert date string to datetime object
df['date'] = pd.to_datetime(df['date'], format='%d-%m-%Y')
# Extract year and year-month groups for later aggregation
df['year'] = df['date'].dt.year
df['year_month'] = df['date'].dt.to_period('M')
# Ensure data is sorted by date (critical for cumulative calculations)
df = df.sort_values('date').reset_index(drop=True)

Step 2: Flag First Occurrences of Each Value #

The key insight here is to mark when a value first appears in its month or year. We can use cumcount() to track the order of occurrences within each group:

# Mark if this is the first time the value appears in the current month
df['first_in_month'] = df.groupby(['year_month', 'value']).cumcount() == 0
# Mark if this is the first time the value appears in the current year
df['first_in_year'] = df.groupby(['year', 'value']).cumcount() == 0

This creates boolean columns where True (1) means the value is new to the group, and False (0) means it’s a repeat.

Step 3: Calculate Cumulative Unique Counts #

Now we just need to compute the rolling sum of these flags within each year/month group. This gives us the total unique values up to each date:

# Compute month-to-date unique count (cumulative sum within the month)
df['Month to date unique'] = df.groupby('year_month')['first_in_month'].cumsum()
# Compute year-to-date unique count (cumulative sum within the year)
df['Year to date unique'] = df.groupby('year')['first_in_year'].cumsum()

Step 4: Format the Final Output #

Clean up the result to match your desired structure:

# Extract only the columns we need
result = df[['date', 'Month to date unique', 'Year to date unique']]
# Convert date back to original string format (optional)
result['date'] = result['date'].dt.strftime('%d-%m-%Y')

print(result)

Output: #

date  Month to date unique  Year to date unique
0  01-01-2012                     1                    1
1  02-01-2012                     2                    2
2  05-01-2012                     3                    3
3  01-02-2012                     1                    3
4  02-02-2012                     2                    3
5  05-02-2012                     3                    4

Why This Works for Large Datasets #

• All operations are vectorized (no loops!), so they scale efficiently with big data.
• We avoid storing large sets/lists of unique values by using boolean flags and cumulative sums—this keeps memory usage low.
• If you have duplicate dates (multiple rows per day), you can collapse them to one row per date with result.groupby('date').last().reset_index() without losing accuracy.

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