聚合DataFrame子集：保留原索引，以年份为列名处理翻新成本

数据集 #

我有如下数据集：

import pandas as pd
import numpy as np

df0 = (pd.DataFrame({'year_minor_renovation': ['2023', '2025', np.nan, '2026'],
               'year_intermediate_renovation': [np.nan, '2025', '2027', '2030'],
               'year_major_renovation': ['2030', np.nan, np.nan, np.nan],
               'costs_minor_renovation': [1000, 3000, np.nan, 2000],
               'costs_intermediate_renovation': [np.nan, 5000, 5000, 10000],
               'costs_major_renovation': [75000, np.nan, np.nan, np.nan]}))

每行代表一栋待翻新建筑，该DataFrame可视为两个同索引子集的拼接：

• 左半部分df.iloc[:, :3]：记录2023-2030年间单栋建筑（对应索引）需进行一次或多次翻新的年份
• 右半部分df.iloc[:, 3:]：对应翻新的成本

需求目标 #

部分建筑需在不同年份进行不同类型的翻新（如df.iloc[[1]]）。我需要聚合生成新列，每列对应一个年份，值为单栋建筑在该年份的翻新总成本，无需区分翻新类型，最终结果如下：

result_df = (pd.DataFrame({'2023': [1000, np.nan, np.nan, np.nan],
              '2024': [np.nan, np.nan, np.nan, np.nan],
              '2025': [np.nan, 8000, np.nan, np.nan],
              '2026': [np.nan, np.nan, np.nan, 2000],
              '2027': [np.nan, np.nan, 5000, np.nan],
              '2028': [np.nan, np.nan, np.nan, np.nan],
              '2029': [np.nan, np.nan, np.nan, np.nan],
              '2030': [75000, np.nan, 5000, 10000]}))

已尝试方案 #

我尝试编写了一个groupby函数来生成新列，但该结果虽包含后续可用数据，却过度聚合，不符合当前需求：

def costs_per_year(df):
    dfs = []
    for i in ['year_minor_renovation',
              'year_intermediate_renovation',
              'year_major_renovation']:
        j =  'costs' + str(i[4:])
        df_ = (df.groupby(i)
               .agg({j : 'sum' })
               .reset_index()
               .rename({i:'year'}, axis =1)
              )
        dfs.append(df_)
        
        # merge the dataframes 
        merged_df = dfs[0]
    for df_ in dfs[1:]:
        merged_df = merged_df.merge(df_, on='year', how='outer')
    
    merged_df = (merged_df
                 .set_index('year')
                 .transpose()
                 .reset_index()
                )
   
    return merged_df

通过重塑数据结构即可实现需求，步骤如下：

1. 将每种翻新类型的年份与成本配对，转换为长格式数据
2. 按建筑索引和年份分组，汇总单栋建筑对应年份的总成本
3. 转换回宽格式，并补全2023-2030的所有年份

代码实现 #

import pandas as pd
import numpy as np

# 原始数据集
df0 = (pd.DataFrame({'year_minor_renovation': ['2023', '2025', np.nan, '2026'],
               'year_intermediate_renovation': [np.nan, '2025', '2027', '2030'],
               'year_major_renovation': ['2030', np.nan, np.nan, np.nan],
               'costs_minor_renovation': [1000, 3000, np.nan, 2000],
               'costs_intermediate_renovation': [np.nan, 5000, 5000, 10000],
               'costs_major_renovation': [75000, np.nan, np.nan, np.nan]}))

# 1. 拆分各类型翻新的年份与成本，转换为长格式
temp_dfs = []
for renovation_type in ['minor', 'intermediate', 'major']:
    year_col = f'year_{renovation_type}_renovation'
    cost_col = f'costs_{renovation_type}_renovation'
    # 生成临时数据，保留原始建筑索引
    temp_df = df0[[year_col, cost_col]].rename(columns={year_col: 'year', cost_col: 'cost'})
    temp_df['building_id'] = df0.index
    temp_dfs.append(temp_df)

# 合并并过滤无效数据
long_format_df = pd.concat(temp_dfs).dropna(subset=['year', 'cost'])

# 2. 按建筑和年份汇总成本
summary = long_format_df.groupby(['building_id', 'year'])['cost'].sum().unstack(fill_value=np.nan)

# 3. 补全2023-2030所有年份，重置索引
all_years = [str(y) for y in range(2023, 2031)]
final_result = summary.reindex(columns=all_years).reset_index(drop=True)

print(final_result)

输出结果 #

2023  2024   2025   2026   2027  2028  2029    2030
0  1000.0   NaN    NaN    NaN    NaN   NaN   NaN  75000.0
1     NaN   NaN  8000.0    NaN    NaN   NaN   NaN      NaN
2     NaN   NaN    NaN    NaN  5000.0   NaN   NaN   5000.0
3     NaN   NaN    NaN  2000.0    NaN   NaN   NaN  10000.0

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