如何修改Python函数实现多子集分组求和并生成单行DataFrame

需要构建Python函数完成以下数据处理：

• 基于两个维度生成子集：关联标签（["SDAR", "NSDCAR", "PSAR"]）和时间间隔（[7,30,60,90,120,None]，对应"Status Date"列）
• 按交易状态标签（如["Active","Pending","Sold","Withdrawn","Contingent","Unknown"]）分组，对"List Price (H)"列求和
• 最终生成单行DataFrame，列名格式为PSAR_7_Contingent_price

此前参考代码实现了按状态统计数量的功能，但修改为求和时触发AttributeError: 'numpy.float64' object has no attribute 'rename'错误，希望通过循环实现避免重复代码。

原统计数量代码 #

def crossubsets(df):
    labels = ["SDAR", "NSDCAR", "PSAR"]
    time_intervals = [7, 30, 60, 90, 120, None]
    group_dfs = df.loc[
        df["Association Label"].isin(labels)
    ].groupby("Association Label")

    data = []
    for l, g in group_dfs:
        for ti in time_intervals:
            s = (
                g[g["Status Date"] > (pd.Timestamp.now() - pd.Timedelta(ti, "d"))]
                if ti is not None else g
            )
            data.append(s["Status Labelled"].value_counts().rename(f"counts_{l}_{ti}"))

    return pd.concat(data, axis=1) #with optional .T to have 18 rows instead of cols

# additional code to flatten the output to a (1, 180) dataframe
counts_processeed = counts_processeed.unstack().to_frame().sort_index(level=1).T
counts_processeed.columns = counts_processeed.columns.map('_'.join)

尝试的求和代码（触发错误） #

def crossubsetsprice(df):
    labels = ["SDAR", "NSDCAR", "PSAR"]
    time_intervals = [7, 30, 60, 90, 120, None]
    group_dfs = df.loc[
        df["Association Label"].isin(labels)
    ].groupby("Association Label")

    data = []
    for l, g in group_dfs:
        for ti in time_intervals:
            s = (
                g[g["Status Date"] > (pd.Timestamp.now() - pd.Timedelta(ti, "d"))]
                if ti is not None else g
            )
            data.append(s['List Price (H)'].sum().rename(f"price_{l}_{ti}"))

    return pd.concat(data, axis=1) #with optional .T to have 18 rows instead of cols

错误原因 #

原代码中s["Status Labelled"].value_counts()返回的是Series对象，支持.rename()方法；而修改后的s['List Price (H)'].sum()返回的是numpy.float64数值，没有.rename()方法，因此触发错误。

修改后的代码 #

需要先按Status Labelled分组，再对"List Price (H)"求和，得到带索引的Series，再重命名后加入列表，最后拼接并整理成目标格式：

import pandas as pd

def crossubsetsprice(df):
    labels = ["SDAR", "NSDCAR", "PSAR"]
    time_intervals = [7, 30, 60, 90, 120, None]
    # 先筛选符合条件的关联标签数据
    filtered_df = df.loc[df["Association Label"].isin(labels)]
    
    data = []
    for l in labels:
        # 过滤当前关联标签的数据
        label_df = filtered_df[filtered_df["Association Label"] == l]
        for ti in time_intervals:
            # 按时间间隔筛选数据
            if ti is not None:
                time_cutoff = pd.Timestamp.now() - pd.Timedelta(ti, "d")
                subset = label_df[label_df["Status Date"] > time_cutoff]
            else:
                subset = label_df.copy()
            
            # 按交易状态分组求和，得到Series（索引是状态标签，值是求和结果）
            price_sum = subset.groupby("Status Labelled")["List Price (H)"].sum()
            # 重命名Series的名称，用于后续列名拼接
            ti_str = "all" if ti is None else str(ti)
            price_sum = price_sum.rename(f"{l}_{ti_str}")
            data.append(price_sum)
    
    # 拼接所有Series，处理缺失值（填充0）
    combined = pd.concat(data, axis=1).fillna(0)
    # 转换为单行DataFrame，调整列名格式为`PSAR_7_Contingent_price`
    result = combined.unstack().to_frame().T
    result.columns = [f"{col[0]}_{col[1]}_price" for col in result.columns]
    
    return result

代码说明 #

1. 替换原groupby("Association Label")为直接循环标签并过滤，逻辑更直观
2. 对时间间隔为None的情况，将其转为字符串"all"，避免列名出现无效值
3. 使用groupby("Status Labelled")["List Price (H)"].sum()得到按状态分组的求和Series，保留状态标签作为索引
4. 拼接后通过unstack()展开多层索引，再调整列名至目标格式，最终生成单行DataFrame

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