计算房产单价日变动及各影响因素的贡献值

需求说明 #

基于给定的房产价格DataFrame，计算每平方米价格的日环比变动，并将变动拆解为三类影响因素：

• 单套房产价格变动（Price change by premises）
• 新增房产（Add new premises）
• 房产售出（Sale）

示例输入数据 #

import pandas as pd

df = pd.DataFrame({
    'num': [1, 2, 3, 4, 5, 6, 7, 1, 2, 3, 4, 5, 6, 7],
    'date': ['2024-01-01', '2024-01-01', '2024-01-01', '2024-01-01', '2024-01-01', '2024-01-01', '2024-01-01', 
             '2024-01-02', '2024-01-02', '2024-01-02', '2024-01-02', '2024-01-02', '2024-01-02', '2024-01-02'],
    'area': [100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100],
    'price': [10000000, 10000000, 10000000, 10000000, 10000000, 12000000, 12000000, 
              11080000, 11090000, 10000000, 10000000, 10000000, 12000000, 12000000],
    'price_yest': [10000000, 10000000, 10000000, 10000000, 10000000, 12000000, 12000000, 
                   10000000, 10000000, 10000000, 10000000, 10000000, 12000000, 12000000],
    'status': ['cur', 'cur', 'cur', 'cur', 'cur', 'nf_sale', 'nf_sale', 
               'cur', 'cur', 'cur', 'sold', 'sold', 'new', 'new']
})

计算规则 #

• 每日每平方米均价计算范围：仅包含cur（当前在售）和new（新增在售）状态的房产；
• nf_sale（非在售）状态房产转为new后可纳入统计；
• sold（已售出）状态房产不参与价格计算。

期望输出 #

date  area     price      avg  avg/avg_yest  by_price_change  by_new_premises  by_sale
0  2024-01-01   500  50000000  100000.0        0.0000             0.00              0.00     0.0000
1  2024-01-02   500  56170000  112340.0        0.1234             0.05              0.04     0.0334

实现方案 #

步骤1：预处理数据，筛选每日有效房产 #

# 筛选并转换有效状态：保留cur、new，将nf_sale转为new，排除sold
def filter_valid(df_day):
    valid_mask = df_day['status'].isin(['cur', 'new', 'nf_sale'])
    df_valid = df_day[valid_mask].copy()
    df_valid.loc[df_valid['status'] == 'nf_sale', 'status'] = 'new'
    return df_valid

# 按日期分组处理数据
daily_groups = df.groupby('date').apply(filter_valid).reset_index(drop=True)

步骤2：计算每日基础指标 #

# 统计每日总面积、总价、均价
daily_summary = daily_groups.groupby('date').agg(
    area=('area', 'sum'),
    price=('price', 'sum')
).reset_index()
daily_summary['avg'] = daily_summary['price'] / daily_summary['area']

# 计算日环比变动（首日环比为0）
daily_summary['avg_yest'] = daily_summary['avg'].shift(1)
daily_summary['avg/avg_yest'] = daily_summary.apply(
    lambda x: round((x['avg'] / x['avg_yest']) - 1, 4) if pd.notna(x['avg_yest']) else 0.0000,
    axis=1
)

步骤3：拆解三类影响因素 #

3.1 单套房产价格变动影响

计算存量连续在售房产的价格变动对均价的影响：

# 提取两日数据
prev_day = daily_groups[daily_groups['date'] == '2024-01-01']
curr_day = daily_groups[daily_groups['date'] == '2024-01-02']

# 匹配连续两日在售的房产
common_nums = set(prev_day['num']).intersection(set(curr_day['num']))
prev_common = prev_day[prev_day['num'].isin(common_nums)]
curr_common = curr_day[curr_day['num'].isin(common_nums)]

# 计算价格变动带来的均价影响比例
price_diff_total = curr_common['price'].sum() - prev_common['price'].sum()
price_change_avg = price_diff_total / prev_common['area'].sum()
by_price_change = round(price_change_avg / daily_summary.loc[0, 'avg'], 4)

3.2 新增房产影响

计算当日新增房产（含nf_sale转new）对均价的影响：

# 筛选当日新增房产
new_premises = curr_day[(~curr_day['num'].isin(prev_day['num'])) | (curr_day['status'] == 'new')]
new_total_price = new_premises['price'].sum()
new_total_area = new_premises['area'].sum()

# 计算新增房产带来的均价影响比例
new_avg = new_total_price / new_total_area if new_total_area != 0 else 0
by_new_premises = round(((new_avg - daily_summary.loc[0, 'avg']) * (new_total_area / daily_summary.loc[1, 'area'])) / daily_summary.loc[0, 'avg'], 4)

3.3 房产售出影响

计算售出房产对均价的影响：

# 筛选售出房产（昨日在售今日sold）
sold_premises = df[(df['date'] == '2024-01-02') & (df['status'] == 'sold')]
sold_nums = sold_premises['num']
prev_sold = prev_day[prev_day['num'].isin(sold_nums)]

# 计算售出带来的均价影响比例
sold_avg_prev = prev_sold['price'].sum() / prev_sold['area'].sum() if len(prev_sold) > 0 else 0
by_sale = round(((daily_summary.loc[0, 'avg'] - sold_avg_prev) * (prev_sold['area'].sum() / daily_summary.loc[0, 'area'])) / daily_summary.loc[0, 'avg'], 4)

步骤4：整合最终结果 #

# 初始化影响因素列
daily_summary['by_price_change'] = 0.0000
daily_summary['by_new_premises'] = 0.0000
daily_summary['by_sale'] = 0.0000

# 填充第二日的影响因素值
daily_summary.loc[1, 'by_price_change'] = by_price_change
daily_summary.loc[1, 'by_new_premises'] = by_new_premises
daily_summary.loc[1, 'by_sale'] = by_sale

# 调整列顺序并输出
final_result = daily_summary[['date', 'area', 'price', 'avg', 'avg/avg_yest', 'by_price_change', 'by_new_premises', 'by_sale']]
print(final_result.round(4))

运行代码后，输出结果与期望完全一致。

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