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;
so basically it is used in sas language through proc sql and now being converted into python dataframe which im unable to various error coming, please help the result should be codes in python dataframes in place of tables
This is the python pandas code:
a = globals()['Bk_Rmblk_'+YrMn]
b = globals()['Bk_Rmblk_'+YrMn._peak]
c = globals()[share.Bkdata2006]
m = share.mktsegcodes
s = share_gd.marketsegmentcurrent
x = a[['hotel','blk_dt']]
globals()['pace_'+YrMn] = x[ x['arr_date'] <= '31DEC09'd]
r = globals()['pace_'+YrMn]
r = a[['hotel', 'blk_dt']].merge(b, how='inner', left_on=['hotel_cd', 'book_key'], right_on=['hotel_cd', 'book_key']).merge(c, left_on=['hotel_cd', 'book_key'], right_on=['hotel_cd','book_key'], how = 'inner').merge(m, on='mkt_seg', how='left').merge(s, left_on=['hotel', 'book_key'], right_on=['marshacode', 'quoteid'], how='left')
# back out new bkg during the period end weekend
# if else condition
r['mkt_seg_dsc'] =np.where((r['arr_date']<= '31DEC09', r.mkt_seg_dsc.combine_first(r.mkt_seg_dsc1), r.quoteaccountmarketsegment.combine_first(r.mkt_seg_dsc.combine_first(r.mkt_seg_dsc1)))
r['bkg_window'] =np.where((r['arr_date']- r['definite_df']<= 365, '0-365', np.where(r['blk_dt'] - r['definie_dt'] <= 730, '366-730', '730+')))
r['arr_window'] = np.where((r['blk_dt'] <= monend, 'in_pd', np.where(r['blk_dt'] - monend <= 365, '0-365', np.where(r['blk_dt']- monend <= 730,'366-730', '730+')))
r['peak_rm'] = np.where(r['peak_rm'] <= 100, '1-100', np.where(r['peak_rm']<= 300, '101-300', np.where(r['peak_rm'] <= 500, '301-500', np.where(r['peak_rm']<=10000, '501-1000','1000+'))))
r = r.groupby(['hotel_cd', 'blk_dt', 'mkt_seg_dsc', 'bkg_window', 'arr_window', 'peak_rm']).agg({'proj_rms':'sum','proj_rms_rev':'sum'})
r.rename(columns = {'proj_rms':'ty_rm'}, inplace = True)
r.rename(columns = {'proj_rms_rev': 'ty_rev'})
r = r[r['definite_dt'] <= monend]
r = r[r['lst_reas'] not in ['ENTRY ERROR','CONVERSION DUPLICATE','SYSTEM UPDATE']]
# final dataframe
globals()['pace_'+YrMn] = r['hotel_cd', 'blk_dt', 'mkt_seg_dsc', 'bkg_window', 'arr_window', 'peak_rm', 'ty_rm', 'ty_rev']