如何基于日期区间与分组计算均值并填充至新数据集？

Got it, let's walk through how to solve this problem with pandas. We'll break it down into three key steps: converting dates to a usable format, computing the mean values for each interval in date_interval, and then matching those means to df_new.

Step 1: Convert Date Columns to Datetime Type #

First, we need to convert all string-based date columns to pandas datetime objects—this lets us properly compare dates and check if a date falls within an interval.

import pandas as pd

# Sample data (as provided)
df = pd.DataFrame({
    'Credit Segmen': ['A','B','A','B','C'], 
    'Name': ['red','white','blue',"red","white"],
    "amount":[100,345,467,345,345], 
    'datev':['4/2/2017','7/28/2017','6/1/2018','4/28/2019','8/1/2019']
})
date_interval = pd.DataFrame({
    'Credit Segmen': ['A','A','A'], 
    'Name': ['red','red','red'], 
    "Date1":['3/1/2017','10/1/2017','3/1/2018'], 
    'date2':['9/30/2017','2/28/2018','9/30/2018']
})
df_new = pd.DataFrame({
    'Credit Segmen': ['A','B','A','B','C'], 
    'Name': ['red','white','blue',"red","white"], 
    'datev':['4/2/2017','7/28/2017','6/1/2018','4/28/2019','8/1/2019']
})

# Convert date columns to datetime
df['datev'] = pd.to_datetime(df['datev'])
date_interval[['Date1', 'date2']] = date_interval[['Date1', 'date2']].apply(pd.to_datetime)
df_new['datev'] = pd.to_datetime(df_new['datev'])

Step 2: Compute Mean Amount for Each Interval in date_interval #

Next, we'll calculate the mean amount from df for each row in date_interval. For each interval, we filter rows in df that match the same Credit Segmen and Name, and whose datev falls between Date1 and date2.

# Function to calculate mean for a single interval row
def get_interval_mean(row):
    # Filter df for matching group and date in interval
    matching_rows = df[
        (df['Credit Segmen'] == row['Credit Segmen']) &
        (df['Name'] == row['Name']) &
        (df['datev'] >= row['Date1']) &
        (df['datev'] <= row['date2'])
    ]
    # Return mean (NaN if no matching rows)
    return matching_rows['amount'].mean()

# Add the new mean column to date_interval
date_interval['mean_amount'] = date_interval.apply(get_interval_mean, axis=1)

If you print date_interval now, you'll see:

Credit Segmen  Name      Date1      date2  mean_amount
0             A   red 2017-03-01 2017-09-30        100.0
1             A   red 2017-10-01 2018-02-28          NaN
2             A   red 2018-03-01 2018-09-30          NaN

The first row has a mean of 100 because df has one matching row (red/A with date 2017-04-02), while the other intervals have no matching rows in df.

Step 3: Apply Mean Values to df_new #

Finally, we need to match each row in df_new to the corresponding mean from date_interval (if its datev falls within a matching interval). We'll use a function to check for matching intervals and pull the mean.

# Function to get the matching mean for df_new rows
def match_mean_to_new(row):
    # Find intervals that match the group and contain the datev
    matching_intervals = date_interval[
        (date_interval['Credit Segmen'] == row['Credit Segmen']) &
        (date_interval['Name'] == row['Name']) &
        (date_interval['Date1'] <= row['datev']) &
        (date_interval['date2'] >= row['datev'])
    ]
    # Return the mean if found, else NA
    return matching_intervals['mean_amount'].iloc[0] if not matching_intervals.empty else pd.NA

# Add the matched mean column to df_new
df_new['matched_mean'] = df_new.apply(match_mean_to_new, axis=1)

Printing df_new gives:

Credit Segmen   Name      datev  matched_mean
0             A    red 2017-04-02         100.0
1             B  white 2017-07-28          <NA>
2             A   blue 2018-06-01          <NA>
3             B    red 2019-04-28          <NA>
4             C  white 2019-08-01          <NA>

Only the first row matches an interval in date_interval, so it gets the mean value—all others return NA since there's no matching interval.

Notes for Edge Cases #

• If a df_new row falls into multiple matching intervals (same Credit Segmen and Name), this solution picks the first one. You can adjust this (e.g., take the mean of all matching interval means) if needed.
• For large datasets, using apply might be slow. You can optimize by using pd.IntervalIndex and merging, which is more efficient:

  # Alternative efficient method for large data
  date_interval['date_range'] = pd.IntervalIndex.from_arrays(date_interval['Date1'], date_interval['date2'], closed='both')
  merged = df_new.merge(date_interval, on=['Credit Segmen', 'Name'], how='left')
  merged['matched_mean'] = merged.apply(lambda x: x['mean_amount'] if x['datev'] in x['date_range'] else pd.NA, axis=1)
  df_new = merged.drop_duplicates(subset=df_new.columns)[df_new.columns.tolist() + ['matched_mean']]
  

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