如何在Pandas中按自定义月周期（21日至次月20日）分组求和

To handle your custom monthly period (from the 21st of one month to the 20th of the next), we can adjust your dates to align with standard monthly grouping logic. Here's how to do it step by step:

Step 1: Ensure Your Date Column is Datetime Type #

First, make sure your date column is properly converted to datetime (skip this if it's already datetime):

import pandas as pd
import datetime

df['date'] = pd.to_datetime(df['date'])

Step 2: Create a Custom Grouping Key #

The key insight is to shift each date back by 20 days. This shifts the boundary so that all dates from the 21st of month X to the 20th of month X+1 will fall into the same standard month (month X).

For example:

• 2015-11-21 minus 20 days → 2015-11-01 (month X = November)
• 2015-12-20 minus 20 days → 2015-11-30 (still November)
• 2015-12-21 minus 20 days → 2015-12-01 (month X = December)

We can use this shifted date to create our grouping key.

Step 3: Group and Sum #

Option 1: Group by Year-Month Label #

If you want group labels like 2015-11 (representing Nov 21 – Dec 20), use this one-liner:

monthly_sum = df.groupby((df['date'] - pd.Timedelta(days=20)).dt.to_period('M'))['Value'].sum()

Option 2: Group by Custom Period Start Date #

If you prefer more intuitive labels (like the actual start date of each custom period, e.g., 2015-11-21), adjust the key to return the start date:

# Create a column for the start of each custom period
df['custom_period_start'] = (df['date'] - pd.Timedelta(days=20)).dt.to_period('M').dt.start_time + pd.Timedelta(days=20)

# Group and sum
monthly_sum = df.groupby('custom_period_start')['Value'].sum()

Step 4: Verify the Result #

To confirm this works, you can check a subset of your data. For example, filter dates between 2015-11-21 and 2015-12-20 and compare the sum to the corresponding group in monthly_sum:

test_sum = df[(df['date'] >= datetime.datetime(2015,11,21)) & (df['date'] <= datetime.datetime(2015,12,20))]['Value'].sum()
print(test_sum == monthly_sum.iloc[0])  # Should return True

This approach dynamically handles your custom date range (2015-11-21 to 2017-11-20) without needing to manually define bins.

内容的提问来源于stack exchange，提问作者Mohamed Thasin ah