如何在Pandas多索引DataFrame中筛选符合季度数据要求的设施并汇总花费

我有一个以日期（月份）和FacilityID为多索引的Pandas DataFrame，包含每个设施的TotalSpend字段。需要汇总满足以下条件的设施的TotalSpend：

• 该设施在当前季度的三个月均有数据
• 该设施在上一年同季度的三个月也均有数据

尝试提取4、5、6月的子集并内连接时，报错提示对象不是DataFrame。希望筛选出在季度所有三个月都有数据的FacilityID，仅保留这些数据。

期望输出：汇总2024年Q2所有符合条件的设施的花费总和，以及2023年Q2这些相同设施的花费总和。示例中仅Facility 1符合要求，2024年Q2总和为450，2023年Q2总和为300。

当前问题代码：

import pandas as pd
import datetime

def open_file(path, quarter_number, months):
    df_raw = pd.DataFrame({'Date':["2024-04-01","2024-05-01","2024-06-01", "2024-06-01","2024-05-01","2023-04-01","2023-05-01","2023-06-01","2024-05-01","2024-06-01","2023-05-01","2023-06-01", "2023-04-01","2024-05-01","2024-06-01"],
                         'FacilityID': [1,1,1,1,1,1,1,1,2,2,2,2,3,4,4],
                         'TotalSpend': [100,110,120,50,70,90,100,110,150,140,120,60,90,190,150]
    }).set_index('Date')
    df = df_raw.groupby(['Date', 'FacilityID'])['TotalSpend'].sum()
    # print(df)

    cur_dates = []
    prev_dates = []

    for month in months:
        cur_date = datetime.date(2024, month, 1)
        prev_date = datetime.date(cur_date.year - 1, month, 1)
        cur_dates.append(cur_date.strftime('%Y-%m-%d'))
        prev_dates.append(prev_date.strftime('%Y-%m-%d'))

    #this is where i'm having issues
    cur_data =df.loc[[cur_dates[1]]].join(df.loc[[cur_dates[1]]], on='FacilityID' ,join = "inner")
    prev_data = df.loc[prev_dates[0]:prev_dates[-1]]

    # print(cur_data)
    # print(prev_data)

if __name__ == "__main__":
    change = open_file("path",2 ,[4,5,6])
    print(change)

问题核心是筛选出连续两个季度都完整拥有三个月数据的设施，原代码错误在于groupby后得到的是Series而非DataFrame，导致join操作失败。以下是修正后的实现：

步骤1：修正数据结构 #

groupby后添加.reset_index()，让结果保持DataFrame格式，方便后续操作：

df = df_raw.groupby(['Date', 'FacilityID'])['TotalSpend'].sum().reset_index()

步骤2：提取目标季度数据 #

从日期中解析年份和月份，快速筛选当前季度与上年同季度的数据：

# 解析年份和月份
df['Year'] = pd.to_datetime(df['Date']).dt.year
df['Month'] = pd.to_datetime(df['Date']).dt.month

# 定义目标季度
cur_year, target_months = 2024, [4,5,6]
prev_year = cur_year - 1

# 筛选两个季度的数据子集
cur_q_data = df[(df['Year'] == cur_year) & (df['Month'].isin(target_months))]
prev_q_data = df[(df['Year'] == prev_year) & (df['Month'].isin(target_months))]

步骤3：筛选符合条件的设施 #

对每个季度的数据按FacilityID分组，统计每个设施的月份覆盖数，保留覆盖完整三个月的设施：

# 当前季度符合条件的设施
valid_cur_fac = cur_q_data.groupby('FacilityID')['Month'].nunique()
valid_cur_fac = valid_cur_fac[valid_cur_fac == 3].index.tolist()

# 上年同季度符合条件的设施
valid_prev_fac = prev_q_data.groupby('FacilityID')['Month'].nunique()
valid_prev_fac = valid_prev_fac[valid_prev_fac == 3].index.tolist()

# 取两者交集，即两个季度都满足条件的设施
final_valid_fac = list(set(valid_cur_fac) & set(valid_prev_fac))

步骤4：计算总花费 #

用筛选后的设施列表，分别计算两个季度的总花费：

cur_total = cur_q_data[cur_q_data['FacilityID'].isin(final_valid_fac)]['TotalSpend'].sum()
prev_total = prev_q_data[prev_q_data['FacilityID'].isin(final_valid_fac)]['TotalSpend'].sum()

完整修正代码 #

import pandas as pd
import datetime

def open_file(path, quarter_number, months):
    df_raw = pd.DataFrame({'Date':["2024-04-01","2024-05-01","2024-06-01", "2024-06-01","2024-05-01","2023-04-01","2023-05-01","2023-06-01","2024-05-01","2024-06-01","2023-05-01","2023-06-01", "2023-04-01","2024-05-01","2024-06-01"],
                         'FacilityID': [1,1,1,1,1,1,1,1,2,2,2,2,3,4,4],
                         'TotalSpend': [100,110,120,50,70,90,100,110,150,140,120,60,90,190,150]
    }).set_index('Date').reset_index()
    
    # 按日期和设施分组求和，转为DataFrame
    df = df_raw.groupby(['Date', 'FacilityID'])['TotalSpend'].sum().reset_index()
    
    # 解析年份和月份
    df['Year'] = pd.to_datetime(df['Date']).dt.year
    df['Month'] = pd.to_datetime(df['Date']).dt.month
    
    cur_year = 2024
    prev_year = cur_year -1
    
    # 筛选两个季度的数据
    cur_q_data = df[(df['Year'] == cur_year) & (df['Month'].isin(months))]
    prev_q_data = df[(df['Year'] == prev_year) & (df['Month'].isin(months))]
    
    # 筛选季度内三个月全有数据的设施
    valid_cur = cur_q_data.groupby('FacilityID')['Month'].nunique() == 3
    valid_cur_fac = valid_cur[valid_cur].index.tolist()
    
    valid_prev = prev_q_data.groupby('FacilityID')['Month'].nunique() ==3
    valid_prev_fac = valid_prev[valid_prev].index.tolist()
    
    # 取交集
    final_fac = list(set(valid_cur_fac) & set(valid_prev_fac))
    
    # 计算总和
    cur_total = cur_q_data[cur_q_data['FacilityID'].isin(final_fac)]['TotalSpend'].sum()
    prev_total = prev_q_data[prev_q_data['FacilityID'].isin(final_fac)]['TotalSpend'].sum()
    
    return {'2024Q2总花费': cur_total, '2023Q2总花费': prev_total}

if __name__ == "__main__":
    result = open_file("path",2 ,[4,5,6])
    print(result)

运行结果：

{'2024Q2总花费': 450, '2023Q2总花费': 300}

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