如何不使用循环结合复杂条件实现Group by分组统计？

我有如下DataFrame：

import pandas as pd
from dateutil.relativedelta import relativedelta

df = pd.DataFrame({
    'Company': {0: 'A', 1: 'A', 2: 'A', 3: 'A', 4: 'A', 5: 'A', 6: 'A', 7: 'A', 8: 'A'},
    'Date': {0: pd.Timestamp('2022-10-10 00:00:00'), 1: pd.Timestamp('2022-10-10 00:00:00'), 2: pd.Timestamp('2022-10-10 00:00:00'), 3: pd.Timestamp('2022-10-10 00:00:00'), 4: pd.Timestamp('2023-10-10 00:00:00'), 5: pd.Timestamp('2023-10-10 00:00:00'), 6: pd.Timestamp('2023-10-10 00:00:00'), 7: pd.Timestamp('2023-10-10 00:00:00'), 8: pd.Timestamp('2023-10-10 00:00:00')}, 
    'Year': {0: 2021, 1: 2022, 2: 2023, 3: 2024, 4: 2022, 5: 2023, 6: 2024, 7: 2025, 8: 2026},
    'Sales_1Q': {0: 5.0, 1: float('nan'), 2: float('nan'), 3: 1.0, 4: float('nan'), 5: float('nan'), 6: float('nan'), 7: float('nan'), 8: float('nan')},
    'Sales_2Q': {0: float('nan'), 1: 10.0, 2: float('nan'), 3: float('nan'), 4: 10.0, 5: float('nan'), 6: float('nan'), 7: 10.0, 8: 20.0}, 
    'Sales_3Q': {0: 5.0, 1: float('nan'), 2: float('nan'), 3: 1.0, 4: float('nan'), 5: float('nan'), 6: float('nan'), 7: float('nan'), 8: float('nan')}, 
    'Sales_4Q': {0: float('nan'), 1: 10.0, 2: float('nan'), 3: float('nan'), 4: 10.0, 5: float('nan'), 6: float('nan'), 7: 10.0, 8: 20.0},
    'Actual_date_1Q': {0: pd.Timestamp('2021-01-01 00:00:00'), 1: pd.NaT, 2: pd.NaT, 3: pd.Timestamp('2024-10-12 00:00:00'), 4: pd.NaT, 5: pd.NaT, 6: pd.NaT, 7: pd.NaT, 8: pd.NaT},
    'Actual_date_2Q': {0: pd.NaT, 1: pd.Timestamp('2023-01-11 00:00:00'), 2: pd.NaT, 3: pd.NaT, 4: pd.Timestamp('2023-01-11 00:00:00'), 5: pd.NaT, 6: pd.NaT, 7: pd.Timestamp('2025-10-10 00:00:00'), 8: pd.Timestamp('2026-10-10 00:00:00')},
    'Actual_date_3Q': {0: pd.Timestamp('2021-01-19 00:00:00'), 1: pd.NaT, 2: pd.NaT, 3: pd.Timestamp('2025-01-01 00:00:00'), 4: pd.NaT, 5: pd.NaT, 6: pd.NaT, 7: pd.NaT, 8: pd.NaT},
    'Actual_date_4Q': {0: pd.NaT, 1: pd.Timestamp('2023-07-11 00:00:00'), 2: pd.NaT, 3: pd.NaT, 4: pd.Timestamp('2023-07-11 00:00:00'), 5: pd.NaT, 6: pd.NaT, 7: pd.Timestamp('2026-06-22 00:00:00'), 8: pd.Timestamp('2027-06-22 00:00:00')}
})

任务说明 #

需按Company和Date列分组，当Actual_date_*Q列的值落在对应年度的季度区间时，统计各季度Sales_*Q列的总和。

我已编写代码生成年份到对应季度区间的字典：

min_year = df.Year[df.Year != 0].min()
max_year = df.Year.max()
intervals = {}

for year in range(min_year, max_year + 1):
    q_count = 0
    start_date = pd.to_datetime(f'{year}-01-01')
    end_date = pd.to_datetime(f'{year}-12-31')
    year_interval = pd.date_range(start=start_date, end=end_date, freq='Q')
    
    list_q_days = {}
    
    for per in range(len(year_interval)):
        q_count += 1 
        if per == 0:
            q_start = year_interval[per] - relativedelta(months=3) + relativedelta(days=1) 
        else:
            q_start = year_interval[per-1] + relativedelta(days=1) 
  
        q_days = pd.date_range(start=q_start, end=year_interval[per], freq='d')
        list_q_days[f'{q_count}Q'] = q_days
 
    intervals[year] = list_q_days

已尝试方案 #

我用循环实现了需求，但效率极低，大数据场景下耗时过长：

%%time
new_df = pd.DataFrame()
for group, data in df.groupby(['Company','Date']):  
    for date in intervals:
        for q in ['1Q','2Q','3Q', '4Q']: 
            data.loc[(data.Year == date), q] = (
                data.apply(lambda x: x['Sales_1Q'] if x['Actual_date_1Q'] in intervals[date][q] else 0, axis=1).sum() +
                data.apply(lambda x: x['Sales_2Q'] if x['Actual_date_2Q'] in intervals[date][q] else 0, axis=1).sum() +
                data.apply(lambda x: x['Sales_3Q'] if x['Actual_date_3Q'] in intervals[date][q] else 0, axis=1).sum() +
                data.apply(lambda x: x['Sales_4Q'] if x['Actual_date_4Q'] in intervals[date][q] else 0, axis=1).sum()
            )
            
    new_df = pd.concat([new_df, data])
df = new_df.copy()

核心问题是外层的groupby循环，尝试用groupby.apply时总是触发TypeError: unhashable type: 'Series'等错误，不知道如何正确结合两者实现高效计算。

期望输出 #

最终需要得到如下结构的DataFrame：

target_df = pd.DataFrame( {
    'Company': {0: 'A', 1: 'A', 2: 'A', 3: 'A', 4: 'A', 5: 'A', 6: 'A', 7: 'A', 8: 'A'}, 
    'Date': {0: pd.Timestamp('2022-10-10 00:00:00'), 1: pd.Timestamp('2022-10-10 00:00:00'), 2: pd.Timestamp('2022-10-10 00:00:00'), 3: pd.Timestamp('2022-10-10 00:00:00'), 4: pd.Timestamp('2023-10-10 00:00:00'), 5: pd.Timestamp('2023-10-10 00:00:00'), 6: pd.Timestamp('2023-10-10 00:00:00'), 7: pd.Timestamp('2023-10-10 00:00:00'), 8: pd.Timestamp('2023-10-10 00:00:00')}, 
    'Year': {0: 2021, 1: 2022, 2: 2023, 3: 2024, 4: 2022, 5: 2023, 6: 2024, 7: 2025, 8: 2026},
    'Sales_1Q': {0: 5.0, 1: float('nan'), 2: float('nan'), 3: 1.0, 4: float('nan'), 5: float('nan'), 6: float('nan'), 7: float('nan'), 8: float('nan')},
    'Sales_2Q': {0: float('nan'), 1: 10.0, 2: float('nan'), 3: float('nan'), 4: 10.0, 5: float('nan'), 6: float('nan'), 7: 10.0, 8: 20.0}, 
    'Sales_3Q': {0: 5.0, 1: float('nan'), 2: float('nan'), 3: 1.0, 4: float('nan'), 5: float('nan'), 6: float('nan'), 7: float('nan'), 8: float('nan')}, 
    'Sales_4Q': {0: float('nan'), 1: 10.0, 2: float('nan'), 3: float('nan'), 4: 10.0, 5: float('nan'), 6: float('nan'), 7: 10.0, 8: 20.0},
    'Actual_date_1Q': {0: pd.Timestamp('2021-01-01 00:00:00'), 1: pd.NaT, 2: pd.NaT, 3: pd.Timestamp('2024-10-12 00:00:00'), 4: pd.NaT, 5: pd.NaT, 6: pd.NaT, 7: pd.NaT, 8: pd.NaT},
    'Actual_date_2Q': {0: pd.NaT, 1: pd.Timestamp('2023-01-11 00:00:00'), 2: pd.NaT, 3: pd.NaT, 4: pd.Timestamp('2023-01-11 00:00:00'), 5: pd.NaT, 6: pd.NaT, 7: pd.Timestamp('2025-10-10 00:00:00'), 8: pd.Timestamp('2026-10-10 00:00:00')},
    'Actual_date_3Q': {0: pd.Timestamp('2021-01-19 00:00:00'), 1: pd.NaT, 2: pd.NaT, 3: pd.Timestamp('2025-01-01 00:00:00'), 4: pd.NaT, 5: pd.NaT, 6: pd.NaT, 7: pd.NaT, 8: pd.NaT},
    'Actual_date_4Q': {0: pd.NaT, 1: pd.Timestamp('2023-07-11 00:00:00'), 2: pd.NaT, 3: pd.NaT, 4: pd.Timestamp('2023-07-11 00:00:00'), 5: pd.NaT, 6: pd.NaT, 7: pd.Timestamp('2026-06-22 00:00:00'), 8: pd.Timestamp('2027-06-22 00:00:00')},
    '1Q': {0: 10.0, 1: 0.0, 2: 10.0, 3: 0.0, 4: 0.0, 5: 10.0, 6: 0.0, 7: 0.0, 8: 0.0}, 
    '2Q': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0, 5: 0.0, 6: 0.0, 7: 0.0, 8: 10.0},
    '3Q': {0: 0.0, 1: 0.0, 2: 10.0, 3: 0.0, 4: 0.0, 5: 10.0, 6: 0.0, 7: 0.0, 8: 0.0}, 
    '4Q': {0: 0.0, 1: 0.0, 2: 0.0, 3: 1.0, 4: 0.0, 5: 0.0, 6: 0.0, 7: 10.0, 8: 20.0}
} )

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