PROJECT 104 · PYTHON SOURCE

Hotel cancellation decision system

Study-specific code. Shared modules, dependency versions, and reproduction instructions are included in all project files.

from pathlib import Path
import sys
sys.path.insert(0, str(Path(__file__).resolve().parents[2]))
"""Hotel cancellation research with booking-time splits and mature labels."""
from capstones.common import *
from capstones.fetch import SOURCES
from sklearn.compose import ColumnTransformer
from sklearn.preprocessing import OneHotEncoder, OrdinalEncoder, StandardScaler
from sklearn.impute import SimpleImputer
from sklearn.pipeline import make_pipeline
from sklearn.linear_model import LogisticRegression
from sklearn.ensemble import HistGradientBoostingClassifier
from sklearn.metrics import roc_auc_score, average_precision_score, brier_score_loss, log_loss
from threadpoolctl import threadpool_limits
import duckdb

SLUG='104-hotel-cancellation-decision-system'

def run():
    p=project_dir(SLUG);u,n=SOURCES[5];archive=download(u,n);provenance([(archive,u)],p)
    with zipfile.ZipFile(archive) as z:
        frames=[pd.read_csv(z.open(name)).assign(Hotel=hotel) for name,hotel in [('H1.csv','Resort'),('H2.csv','City')]]
    data=pd.concat(frames,ignore_index=True)
    for c in data.select_dtypes(include=['object','string']): data[c]=data[c].str.strip()
    data['ArrivalDate']=pd.to_datetime(data.ArrivalDateYear.astype(str)+'-'+data.ArrivalDateMonth+'-'+data.ArrivalDateDayOfMonth.astype(str),format='%Y-%B-%d')
    data['BookingDate']=data.ArrivalDate-pd.to_timedelta(data.LeadTime,unit='D')
    data['KnownOutcomeDate']=pd.to_datetime(data.ReservationStatusDate)
    data['ArrivalMonth']=data.ArrivalDate.dt.month;data['ArrivalWeekday']=data.ArrivalDate.dt.dayofweek
    data['Nights']=data.StaysInWeekendNights+data.StaysInWeekNights
    # These source fields are snapshots, so availability at original booking is an assumption.
    numeric=['LeadTime','ArrivalMonth','ArrivalWeekday','StaysInWeekendNights','StaysInWeekNights','Adults','Children','Babies']
    cats=['Hotel','Meal','MarketSegment','DistributionChannel','ReservedRoomType']
    features=numeric+cats
    train=data[(data.BookingDate<'2016-07-01')&(data.KnownOutcomeDate<'2016-07-01')].copy()
    val=data[(data.BookingDate>='2016-07-01')&(data.BookingDate<'2016-10-01')&(data.KnownOutcomeDate<'2017-01-01')].copy()
    test=data[(data.BookingDate>='2017-01-01')&(data.BookingDate<'2017-05-01')].copy()
    # The last booking cohorts are excluded because the source ends in August 2017.
    target='IsCanceled'; prevalence=float(train[target].mean())
    logistic=make_pipeline(ColumnTransformer([('number',make_pipeline(SimpleImputer(strategy='median'),StandardScaler()),numeric),('category',OneHotEncoder(handle_unknown='ignore'),cats)]),LogisticRegression(max_iter=600,C=1,random_state=42))
    encoder=ColumnTransformer([('number',SimpleImputer(strategy='median'),numeric),('category',OrdinalEncoder(handle_unknown='use_encoded_value',unknown_value=np.nan),cats)])
    choices=[('Logistic regression',logistic)]
    for leaves in [15,31]:
        choices.append((f'Gradient boosting {leaves}',make_pipeline(encoder,HistGradientBoostingClassifier(max_iter=130,max_leaf_nodes=leaves,learning_rate=.08,l2_regularization=3,categorical_features=[False]*len(numeric)+[True]*len(cats),early_stopping=False,random_state=42))))
    validation=[]
    with threadpool_limits(limits=3):
        for label,model in choices:
            model.fit(train[features],train[target]);prob=model.predict_proba(val[features])[:,1]
            validation.append({'model':label,'validation_log_loss':log_loss(val[target],prob),'validation_brier':brier_score_loss(val[target],prob)})
        best=int(np.argmin([v['validation_log_loss'] for v in validation]));name,model=choices[best]
        # Freeze the selected fit before accessing the test labels.
        valprob=model.predict_proba(val[features])[:,1]
        test['Probability']=model.predict_proba(test[features])[:,1]
        logistic_test=choices[0][1].predict_proba(test[features])[:,1]
    results=[]
    comparisons=[('Training prevalence',np.repeat(prevalence,len(test))),('Logistic regression',logistic_test)]
    if name!='Logistic regression': comparisons.append((name,test.Probability))
    for label,prob in comparisons:
        results.append({'model':label,'test_bookings':len(test),'roc_auc':roc_auc_score(test[target],prob),'average_precision':average_precision_score(test[target],prob),'brier':brier_score_loss(test[target],prob),'log_loss':log_loss(test[target],prob)})
    save_csv(pd.DataFrame(results),p/'outputs/results.csv')
    # Fix a threshold from the validation probability distribution, without optimizing test outcomes.
    threshold=float(np.quantile(valprob,.9));test['Flagged']=(test.Probability>=threshold).astype(int)
    test['Month']=test.BookingDate.dt.strftime('%Y-%m');test['PredictedCancellations']=test.Probability
    test['SquaredError']=(test.Probability-test[target])**2
    test['Caught']=test.Flagged*test[target]
    test['RiskBand']=pd.cut(test.Probability,[-.001,.2,.4,.6,.8,1],labels=['0–20%','20–40%','40–60%','60–80%','80–100%']).astype(str)
    con=duckdb.connect();con.register('test_bookings',test)
    sql="""-- Grain: booking-month × hotel × market segment × probability band.
-- Counts and probability sums are additive; divide only after filtering.
SELECT Month AS month, Hotel AS hotel, MarketSegment AS segment, RiskBand AS risk_band,
count(*) AS bookings, sum(IsCanceled) AS cancellations,
sum(PredictedCancellations) AS predicted_cancellations, sum(SquaredError) AS squared_error,
sum(Flagged) AS flagged, sum(Caught) AS caught
FROM test_bookings GROUP BY 1,2,3,4 ORDER BY 1,2,3,4;
"""
    (p/'sql/decision_mart.sql').write_text(sql,encoding='utf-8');mart=con.execute(sql).df();con.close()
    save_csv(mart,p/'outputs/decision_mart.csv')
    bins=test.assign(Bin=pd.cut(test.Probability,np.linspace(0,1,11),include_lowest=True)).groupby('Bin',observed=True).agg(bookings=(target,'size'),predicted=('Probability','mean'),observed=(target,'mean')).reset_index(drop=True)
    save_csv(bins,p/'outputs/calibration.csv')
    # Scenario arithmetic only; no intervention outcomes exist in the historical data.
    flagged=int(test.Flagged.sum());caught=int(test.Caught.sum());scenario=[]
    for effect in [.0,.05,.10,.20]:
        for contribution in [30,60,100]:
            scenario.append({'assumed_preventable_fraction':effect,'assumed_contribution_per_prevented_cancellation_eur':contribution,'assumed_contact_cost_eur':2,'flagged_bookings':flagged,'historical_cancellations_in_flagged':caught,'illustrative_net_eur':caught*effect*contribution-flagged*2})
    save_csv(pd.DataFrame(scenario),p/'outputs/scenarios.csv')
    metric={'source_rows':len(data),'train_rows':len(train),'validation_rows':len(val),'test_rows':len(test),'test_cancellation_rate':float(test[target].mean()),'model':name,'roc_auc':results[-1]['roc_auc'],'average_precision':results[-1]['average_precision'],'brier':results[-1]['brier'],'baseline_brier':results[0]['brier'],'fixed_threshold':threshold,'flagged':flagged,'caught':caught,'precision':caught/flagged,'recall':caught/int(test[target].sum()),'flagged_share':flagged/len(test),'source_snapshot_duplicates':int(data.duplicated().sum()),'maturity_excluded_training':int(((data.BookingDate<'2016-07-01')&(data.KnownOutcomeDate>='2016-07-01')).sum())}
    js(p/'outputs/metrics.json',metric)
    js(p/'outputs/evaluation.json',{'split':'Train: booking before July 2016 and outcome known before July 2016. Validate: July–September 2016 bookings with outcomes known before January 2017. Test: January–April 2017 bookings, all recorded outcomes available in the published August 2017 extract.','train_rows':len(train),'test_rows':len(test),'selection':f'{name} selected by validation log loss. Fixed validation 90th-percentile score threshold. No test tuning.','features':features,'target':'Recorded cancellation indicator','validation':validation,'feature_availability':'Conservative snapshot predictors; arrival, party, channel and reserved room may still have changed after booking. Event-versioned source data are required before deployment.','excluded_features':['ReservationStatus','ReservationStatusDate','AssignedRoomType','BookingChanges','DepositType','ADR','Agent','Company','Country','DaysInWaitingList','TotalOfSpecialRequests','RequiredCarParkingSpaces','CustomerType','PreviousCancellations','PreviousBookingsNotCanceled'],'limits':'No customer identifier for entity-separated evaluation. Repeated source records retained because groups can contain legitimately identical bookings. Cohort composition changes. No treatment or profit data.'})
    quality(p,{'original_counts':len(data)==119390,'binary_target':set(data[target])=={0,1},'train_labels_mature':bool((train.KnownOutcomeDate<'2016-07-01').all()),'validation_labels_mature':bool((val.KnownOutcomeDate<'2017-01-01').all()),'booking_order':train.BookingDate.max()<val.BookingDate.min()<test.BookingDate.min(),'disjoint_rows':not(set(train.index)&set(val.index) or set(train.index)&set(test.index) or set(val.index)&set(test.index)),'no_outcome_features':not set(features)&{'ReservationStatus','ReservationStatusDate','KnownOutcomeDate','IsCanceled','AssignedRoomType','BookingChanges'},'probabilities_bounded':bool(test.Probability.between(0,1).all()),'mart_reconciles':int(mart.bookings.sum())==len(test) and int(mart.cancellations.sum())==int(test[target].sum()),'caught_bounded':caught<=flagged and caught<=int(test[target].sum()),'source_has_both_hotels':set(data.Hotel)=={'City','Resort'}})
    save_csv(test[['BookingDate','Hotel','MarketSegment','IsCanceled','Probability','Flagged']],p/'data/test_predictions.csv')
    fig,axes=plt.subplots(2,2,figsize=(12,7.8))
    trend=data.groupby(data.ArrivalDate.dt.to_period('M')).IsCanceled.agg(['size','mean']);axes[0,0].plot(trend.index.astype(str),trend['mean']*100,color=BLUE);axes[0,0].set_xticks(np.arange(0,len(trend),4),trend.index.astype(str)[::4],rotation=25);axes[0,0].set(title='Historical arrival cohorts',ylabel='Recorded cancellation rate · %')
    axes[0,1].plot([0,1],[0,1],color='#b5b8c3',linestyle='--');axes[0,1].plot(bins.predicted,bins.observed,'o-',color=BLUE);axes[0,1].set(title='Untouched-test probability calibration',xlabel='Mean predicted probability',ylabel='Observed cancellation share')
    h=test.groupby('Hotel').IsCanceled.mean();axes[1,0].bar(h.index,h*100,color=[BLUE,TEAL]);axes[1,0].set(title='January–April 2017 booking cohorts',ylabel='Cancellation rate · %')
    modelres=pd.DataFrame(results);axes[1,1].barh(modelres.model,modelres.brier,color=[ORANGE,BLUE,TEAL][:len(results)]);axes[1,1].set(title='Probability quality',xlabel='Brier score · lower is better')
    fig.suptitle('Hotel cancellation decision system | Two Portuguese hotels',fontsize=17,fontweight='bold');fig.tight_layout();fig.savefig(p/'outputs/chart.png',dpi=150);plt.close(fig)
    js(p/'outputs/dashboard.json',{'kind':'hotel','metrics':metric,'mart':json.loads(mart.to_json(orient='records')),'calibration':json.loads(bins.to_json(orient='records')),'scenarios':scenario})
    return metric

if __name__=='__main__': print(json.dumps(run(),indent=2))