基于年份条件修正DataFrame中增长率异常的数值

示例需求与预期 #

原始示例数据代码 #

sample = {"Location+Type": ["A", "A", "A", "A", "B", "B", "B", "B"],
         "Year": ["2010", "2011", "2012", "2013", "2010", "2011", "2012", "2013"],
         "Price": [1, 2, 1, 3, 1, 1, 1, 1]}
df_sample = pd.DataFrame(data=sample)
df_sample['pct_pop'] = df_sample['Price'].pct_change()
df_sample.head(8)

核心需求 #

从指定年份（示例为2012年，实际为2023年）开始：

• 若当前年份价格相对上一年的增长率绝对值超过30%
• 则按Location+Type分组，取该组此前所有年份的价格平均值替换当前价格

预期修正结果 #

sample = {"Location+Type": ["A", "A", "A", "A", "B", "B", "B", "B"],
         "Year": ["2010", "2011", "2012", "2013", "2010", "2011", "2012", "2013"],
         "Price": [1, 2, 1.5, 1.33, 1, 1, 1, 1]}
df_sample = pd.DataFrame(data=sample)
df_sample['pct_pop'] = df_sample['Price'].pct_change()
df_sample.head(8)

实际代码问题与修正 #

原始问题代码 #

df_complete['Year'] = df_complete['Year'].astype(int) #  just in case if "Year" holds strings

def calculate_new_price(row, df):
    current_index = row.name  # row number will be used to check if value is in the first of the DF
    # Check if row is not the first
    if current_index > 0:
        # assign previous pct_pop value to variable: previous_pct_pop
        previous_pct_pop = df.loc[current_index - 1, 'pct_pop']
        # Check if the year is 2023 or later and if the difference in pct_pop is 0.3 or more
        if row['Year'] >= 2023 and abs(row['pct_pop'] - previous_pct_pop) >= 0.3:
            # Filter previous years
            previous_data = df[(df['Year'] < row['Year']) & (df['Location+Type'] == row['Location+Type'])]
            # Calculate average if row above is not empty
            if not previous_data.empty:
                return previous_data['Median_Home_Value_prediction'].mean()
        else:
            return row['Median_Home_Value_prediction']  # if pct_pop change was smaller than 0.3 or year is older than 2012, - use default price.
    else:
        return row['Median_Home_Value_prediction']   # if index 0 (first row) use value from first row of col: Price
    return None

# Apply the function to create the new_price column
df_complete['Median_Home_Value_prediction_new'] = df_complete.apply(lambda row: calculate_new_price(row, df_complete), axis=1)

核心问题点 #

1. 增长率判断逻辑错误：需求是判断当前价格相对上一年的增长率绝对值超30%，但代码错误计算了当前行与上一行的增长率差值，完全偏离需求。
2. 分组处理逻辑缺失：未按Location+Type分组排序，直接用全局索引取上一行数据，会导致跨组取数的错误。
3. 未支持迭代修正：若某一年价格被替换，后续年份的增长率仍基于原始值计算，不符合实际业务逻辑。

修正后的代码 #

import pandas as pd

# 关键前提：先按Location+Type分组，再按Year升序排序，避免跨组错误
df_complete = df_complete.sort_values(['Location+Type', 'Year']).reset_index(drop=True)
df_complete['Year'] = df_complete['Year'].astype(int)

# 按组计算原始增长率（基于原始预测值）
df_complete['pct_pop_original'] = df_complete.groupby('Location+Type')['Median_Home_Value_prediction'].pct_change()

# 初始化修正后的列，先复制原始值
df_complete['Median_Home_Value_prediction_new'] = df_complete['Median_Home_Value_prediction'].copy()

# 按分组迭代处理2023年及以后的数据
for group_name, group_df in df_complete.groupby('Location+Type'):
    group_indices = group_df.index
    # 从第2行开始遍历（跳过每组的第一年）
    for i in range(1, len(group_indices)):
        current_idx = group_indices[i]
        current_year = df_complete.loc[current_idx, 'Year']
        
        # 只处理2023年及以后的数据
        if current_year < 2023:
            continue
        
        # 获取当前行的原始增长率（相对上一年的原始值）
        current_pct = df_complete.loc[current_idx, 'pct_pop_original']
        # 判断增长率绝对值是否超过30%
        if abs(current_pct) >= 0.3:
            # 取该组当前年份之前所有修正后的值的平均值
            prior_values = df_complete.loc[group_indices[:i], 'Median_Home_Value_prediction_new']
            df_complete.loc[current_idx, 'Median_Home_Value_prediction_new'] = prior_values.mean()

# 可选：基于修正后的值重新计算增长率，用于验证
df_complete['pct_pop_new'] = df_complete.groupby('Location+Type')['Median_Home_Value_prediction_new'].pct_change()

修正说明 #

• 先排序分组：确保每个Location+Type组内数据按年份升序排列，彻底避免跨组取数错误。
• 修正增长率判断：直接使用按组计算的单年增长率，符合需求中"增长率变化超过30%"的判断逻辑。
• 按组迭代处理：针对每个分组单独遍历，确保取到的是当前组历史数据的平均值。
• 支持迭代修正：若某一年价格被替换，后续年份的计算可基于修正后的值（如需调整后续增长率计算逻辑，直接修改即可）。

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