SAS PROC SQL转Python DataFrame遇问题，请求协助实现等价逻辑

以下是将给定的SAS PROC SQL代码转换为Python pandas DataFrame的等价实现，完全对应原逻辑的表关联、分支判断、聚合计算等操作：

原SAS PROC SQL代码 #

create table pace_&YrMn. as
select a.hotel_cd, a.blk_dt, 
       case when a.arr_date <= '31DEC09'd then coalesce(c.mkt_seg_dsc, m.mkt_seg_dsc1) 
            else coalesce(s.quoteaccountmarketsegment, c.mkt_seg_dsc, m.mkt_seg_dsc1)
            end as mkt_seg_dsc,

       case when a.blk_dt-c.definite_dt<=365 then'  0-365'
            when a.blk_dt-c.definite_dt<=730 then'366-730'
            else '730+   '
            end as bkg_window format=$7.,

       case when a.blk_dt <= &monend. then 'in_pd'
            when a.blk_dt-&monend.<=365 then'  0-365'
            when a.blk_dt-&monend.<=730 then'366-730'
                                       else'730+   '
            end as arr_window format=$7.,

       case when b.peak_rm<=100 then'  1-100 '
            when b.peak_rm<=300 then'101-300 '
            when b.peak_rm<=500 then'301-500 '
            when b.peak_rm<=1000 then'501-1000'
            else'1000+   '
            end as peak_rm format=$8.,

       sum(proj_rms) as ty_rm, sum(proj_rms_rev) as ty_rev
       
from Bk_Rmblk_&YrMn. a
     inner join Bk_Rmblk_&YrMn._peak b
     on a.hotel_cd=b.hotel_cd
     and a.book_key=b.book_key
     inner join share.Bkdata2006 c
     on a.hotel_cd=c.hotel_cd
     and a.book_key=c.book_key
     left join share.mktsegcodes m
     on c.mkt_seg=m.mkt_seg
     left join share_gd.marketsegmentcurrent s
     on a.hotel_cd=s.marshacode
     and a.book_key=s.quoteid
where c.definite_dt <= &monend./*back out new bkg during the period end weekend*/
and not(c.lst_reas in ('ENTRY ERROR','CONVERSION DUPLICATE','SYSTEM UPDATE'))
group by a.hotel_cd, a.blk_dt, calculated mkt_seg_dsc, calculated bkg_window, calculated arr_window, calculated peak_rm;

Python pandas 等价实现代码 #

import pandas as pd
import numpy as np

# ---------------------- 1. 定义宏变量与日期常量 ----------------------
# 替换为实际年月值，例如'202406'
YrMn = '202406'
# 替换为&monend.对应的实际日期，转换为datetime类型
monend = pd.to_datetime('2024-06-30')
# SAS中'31DEC09'd对应的Python日期
dec_31_2009 = pd.to_datetime('2009-12-31')

# ---------------------- 2. 读取各表数据 ----------------------
# 根据实际存储方式调整（如csv、数据库、SAS文件等）
a = pd.read_csv(f'Bk_Rmblk_{YrMn}.csv')
b = pd.read_csv(f'Bk_Rmblk_{YrMn}_peak.csv')
c = pd.read_csv('share_Bkdata2006.csv')
m = pd.read_csv('share_mktsegcodes.csv')
s = pd.read_csv('share_gd_marketsegmentcurrent.csv')

# 转换日期字段为datetime类型（根据实际数据格式调整）
date_cols = ['blk_dt', 'arr_date', 'definite_dt']
for col in date_cols:
    if col in a.columns:
        a[col] = pd.to_datetime(a[col])
    if col in c.columns:
        c[col] = pd.to_datetime(c[col])

# ---------------------- 3. 多表关联 ----------------------
# 先执行inner join关联a、b、c
merged = a.merge(b, on=['hotel_cd', 'book_key'], how='inner')\
          .merge(c, on=['hotel_cd', 'book_key'], how='inner')
# 再执行left join关联m和s
merged = merged.merge(m, left_on='mkt_seg', right_on='mkt_seg', how='left')\
          .merge(s, left_on=['hotel_cd', 'book_key'], right_on=['marshacode', 'quoteid'], how='left')

# ---------------------- 4. 数据过滤 ----------------------
filter_mask = (merged['definite_dt'] <= monend) & \
              (~merged['lst_reas'].isin(['ENTRY ERROR', 'CONVERSION DUPLICATE', 'SYSTEM UPDATE']))
merged = merged[filter_mask]

# ---------------------- 5. 派生字段计算 ----------------------
# 1. mkt_seg_dsc：对应CASE WHEN + COALESCE逻辑
merged['mkt_seg_dsc'] = np.where(
    merged['arr_date'] <= dec_31_2009,
    merged['mkt_seg_dsc'].combine_first(merged['mkt_seg_dsc1']),
    merged['quoteaccountmarketsegment'].combine_first(merged['mkt_seg_dsc']).combine_first(merged['mkt_seg_dsc1'])
)

# 2. bkg_window：计算日期差并分段
bkg_diff = (merged['blk_dt'] - merged['definite_dt']).dt.days
merged['bkg_window'] = np.select(
    [bkg_diff <= 365, bkg_diff <= 730],
    ['  0-365', '366-730'],
    default='730+   '
)

# 3. arr_window：计算与monend的日期差并分段
arr_diff = (merged['blk_dt'] - monend).dt.days
merged['arr_window'] = np.select(
    [merged['blk_dt'] <= monend, arr_diff <= 365, arr_diff <= 730],
    ['in_pd', '  0-365', '366-730'],
    default='730+   '
)

# 4. peak_rm：数值分段
merged['peak_rm'] = np.select(
    [merged['peak_rm'] <= 100, merged['peak_rm'] <= 300, merged['peak_rm'] <= 500, merged['peak_rm'] <= 1000],
    ['  1-100 ', '101-300 ', '301-500 ', '501-1000'],
    default='1000+   '
)

# ---------------------- 6. 分组聚合 ----------------------
result = merged.groupby(
    ['hotel_cd', 'blk_dt', 'mkt_seg_dsc', 'bkg_window', 'arr_window', 'peak_rm'],
    as_index=False
).agg(
    ty_rm=('proj_rms', 'sum'),
    ty_rev=('proj_rms_rev', 'sum')
)

# ---------------------- 7. 保存结果 ----------------------
result.to_csv(f'pace_{YrMn}.csv', index=False)

关键实现说明 #

• 日期处理：将SAS数值型日期转换为Python datetime类型，通过dt.days计算日期差，等价于SAS直接相减的天数逻辑
• COALESCE替代：使用combine_first链式调用实现空值填充逻辑，比逐行apply性能更优
• CASE WHEN分支：用numpy.select实现多条件分支判断，适合大规模数据集
• 表关联逻辑：严格对应原SAS的关联顺序与类型，多条件关联通过merge的on参数传入列表实现
• 分组聚合：groupby指定所有分组字段，agg映射SUM聚合规则，as_index=False避免分组字段成为索引

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