{"cells":[{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"f13fcf7b-f722-44f4-89d2-e14848250b41"},"outputs":[],"source":"# Imports\n\n# pandas\nimport pandas as pd\nfrom pandas import Series,DataFrame\n\n# numpy, matplotlib, seaborn\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nsns.set_style('whitegrid')\n%matplotlib inline\n\n# machine learning\nfrom sklearn.linear_model import LogisticRegression\nfrom sklearn.svm import SVC, LinearSVC\nfrom sklearn.ensemble import RandomForestClassifier\nfrom sklearn.neighbors import KNeighborsClassifier\nfrom sklearn.naive_bayes import GaussianNB\nimport xgboost as xgb"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"c813d32a-4398-41e5-9597-4cec7dc044bf"},"outputs":[],"source":"# get expedia & test csv files as a DataFrame\nexpedia_df = pd.read_csv('../input/train.csv', nrows=10000)\ntest_df    = pd.read_csv('../input/test.csv')\n\n# preview the data\nexpedia_df.head()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"4d03a051-e10b-47b5-ae2e-e0df304e8c78"},"outputs":[],"source":"expedia_df.info()\nprint(\"----------------------------\")\ntest_df.info()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"558556bd-874b-469c-ac42-4cc00ba2592e"},"outputs":[],"source":"# drop unnecessary columns, these columns won't be useful in analysis and prediction\nexpedia_df = expedia_df.drop(['date_time','site_name', 'user_location_region', 'user_location_city', 'orig_destination_distance', \n                              'user_id', 'srch_co', 'srch_adults_cnt', 'srch_children_cnt', 'srch_rm_cnt', 'cnt'], axis=1)\ntest_df    = test_df.drop(['date_time','site_name', 'user_location_region', 'user_location_city', 'orig_destination_distance', \n                              'user_id', 'srch_co', 'srch_adults_cnt', 'srch_children_cnt', 'srch_rm_cnt'], axis=1)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"8de0c910-bf9b-4f56-815b-b44c1cbe5e93"},"outputs":[],"source":"# Plot \n\nfig, (axis1,axis2) = plt.subplots(2,1,figsize=(15,10))\n\nbookings_df = expedia_df[expedia_df[\"is_booking\"] == 1]\n\n# What are the most countries the customer travel from?\nsns.countplot('user_location_country',data=bookings_df.sort_values(by=['user_location_country']),ax=axis1,palette=\"Set3\")\n\n# What are the most countries the customer travel to?\nsns.countplot('hotel_country',data=bookings_df.sort_values(by=['hotel_country']),ax=axis2,palette=\"Set3\")\n\n# Combine both plots\n# fig, (axis1) = plt.subplots(1,1,figsize=(15,5))\n\n# sns.distplot(bookings_df[\"hotel_country\"], kde=False, rug=False, bins=25, ax=axis1)\n# sns.distplot(bookings_df[\"user_location_country\"], kde=False, rug=False, bins=25, ax=axis1)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"4751f8d9-05bf-4ddf-8214-1ed8566115e6"},"outputs":[],"source":"# Where do most of the customers from a country travel?\nuser_country_id = 66\n\nfig, (axis1) = plt.subplots(1,1,figsize=(15,10))\n\ncountry_customers = expedia_df[expedia_df[\"user_location_country\"] == user_country_id]\ncountry_customers[\"hotel_country\"].value_counts().plot(kind='bar',colormap=\"Set3\",figsize=(15,5))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b8f89d86-5573-4547-9684-9b719fbe080c"},"outputs":[],"source":"# Plot frequency for each hotel_clusters\n\nexpedia_df[\"hotel_cluster\"].value_counts().plot(kind='bar',colormap=\"Set3\",figsize=(15,5))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"056e1b74-1eab-443e-aae1-3d7c47c12116"},"outputs":[],"source":"# What are the most frequent hotel clusters booked by customers from a country?\nuser_country_id = 66\n\nfig, (axis1) = plt.subplots(1,1,figsize=(15,10))\n\ncustomer_clusters = expedia_df[expedia_df[\"user_location_country\"] == user_country_id][\"hotel_cluster\"]\ncustomer_clusters.value_counts().plot(kind='bar',colormap=\"Set3\",figsize=(15,5))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"b513a343-a4f0-4097-af25-54a3cf3f4f45"},"outputs":[],"source":"# What are the most frequent hotel clusters in a country?\ncountry_id = 50\n\nfig, (axis1) = plt.subplots(1,1,figsize=(15,10))\n\ncountry_clusters = expedia_df[expedia_df[\"hotel_country\"] == country_id][\"hotel_cluster\"]\ncountry_clusters.value_counts().plot(kind='bar',colormap=\"Set3\",figsize=(15,5))"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"546df860-e9d8-4b2a-88a2-d04796b2d2ef"},"outputs":[],"source":"# Plot post_continent & hotel_continent\n\nfig, ((axis1,axis2),(axis3,axis4)) = plt.subplots(2,2,figsize=(15,10))\n\n# Plot frequency for each posa_continent\nsns.countplot('posa_continent', data=expedia_df,order=[0,1,2,3,4],palette=\"Set3\",ax=axis1)\n\n# Plot frequency for each posa_continent decomposed by hotel_continent\nsns.countplot('posa_continent', hue='hotel_continent',data=expedia_df,order=[0,1,2,3,4],palette=\"Set3\",ax=axis2)\n\n# Plot frequency for each hotel_continent\nsns.countplot('hotel_continent', data=expedia_df,order=[0,2,3,4,5,6],palette=\"Set3\",ax=axis3)\n\n# Plot frequency for each hotel_continent decomposed by posa_continent\nsns.countplot('hotel_continent', hue='posa_continent', data=expedia_df, order=[0,2,3,4,5,6],palette=\"Set3\",ax=axis4)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"a1d4f0ba-2684-4778-af26-a4a7906a9f0e"},"outputs":[],"source":"# Plot frequency of is_mobile & is_package\n\nfig, (axis1,axis2) = plt.subplots(1,2,figsize=(15,3))\n\n# What's the frequency of bookings through mobile?\nsns.countplot(x='is_mobile',data=bookings_df, order=[0,1], palette=\"Set3\", ax=axis1)\n\n# What's the frequency of bookings with package?\nsns.countplot(x='is_package',data=bookings_df, order=[0,1], palette=\"Set3\", ax=axis2)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"3b5aaf1a-3600-47c7-ad5c-a78e74ba60a6"},"outputs":[],"source":"# What's the most impactful channel?\n\nfig, (axis1) = plt.subplots(1,1,figsize=(15,3))\n\nsns.countplot(x='channel', order=list(range(0,10)), data=expedia_df, palette=\"Set3\")"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"99d0f782-8b4d-4716-8a08-3421557eccab"},"outputs":[],"source":"# Convert srch_ci to Year, Month, and Week\n\nexpedia_df['Year']   = expedia_df['srch_ci'].apply(lambda x: int(str(x)[:4]) if x == x else np.nan)\nexpedia_df['Month']  = expedia_df['srch_ci'].apply(lambda x: int(str(x)[5:7]) if x == x else np.nan)\nexpedia_df['Week']   = expedia_df['srch_ci'].apply(lambda x: int(str(x)[8:10]) if x == x else np.nan)\n\nfig, (axis1,axis2,axis3) = plt.subplots(1,3,sharex=True,figsize=(15,5))\n\n# Plot How many bookings in each month\nsns.countplot('Month',data=expedia_df[expedia_df[\"is_booking\"] == 1],order=list(range(1,13)),palette=\"Set3\",ax=axis1)\n\n# Plot The percentage of bookings of each month(sum of month bookings / count of bookings(=1 OR =0) of a month)\n# sns.factorplot('Month',\"is_booking\",data=expedia_df, order=list(range(1,13)), palette=\"Set3\",ax=axis2)\nsns.barplot('Month',\"is_booking\",data=expedia_df, order=list(range(1,13)), palette=\"Set3\",ax=axis2)\n\n# Plot The percentage of bookings of each month compared to all bookings(sum of month bookings / count of bookings(=1) of all months)\nmonth_sum = expedia_df[['Month', 'is_booking']].groupby(['Month'],as_index=False).sum()\nmonth_sum['is_booking'] = month_sum['is_booking'] / len(expedia_df[expedia_df['is_booking'] == 1])\n\nsns.barplot(x='Month', y='is_booking', order=list(range(1,13)), data=month_sum,ax=axis3) "},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"e6cb9858-635c-4eb5-a9d8-b24e46a0ddd7"},"outputs":[],"source":"# Convert srch_ci column to Date(Y-M)\nexpedia_df['Date']  = expedia_df['srch_ci'].apply(lambda x: (str(x)[:7]) if x == x else np.nan)\n\n# Plot number of bookings over Date\ndate_bookings  = expedia_df.groupby('Date')[\"is_booking\"].sum()\nax1 = date_bookings.plot(legend=True,marker='o',title=\"Total Bookings\", figsize=(15,5)) \nax1.set_xticks(range(len(date_bookings)))\nxlabels = ax1.set_xticklabels(date_bookings.index.tolist(), rotation=90)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"5f68d9f8-9acb-4b38-900e-e98244dc8da3"},"outputs":[],"source":"# .... continue with plot Date column\n\n# Plot important values(min,max,quartiles) for number of bookings over Date\nfig, (axis1) = plt.subplots(1,1,figsize=(15,3))\n\nax2 = sns.boxplot([date_bookings.values], whis=np.inf,ax=axis1)\nax2.set_title('Important Values')"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"fc5bdc02-e980-4b95-aa29-996ada94f999"},"outputs":[],"source":"# Correlation between hotel_country in number of bookings through 2013, 2014, & 2015\n\nhotel_country_piv       = pd.pivot_table(expedia_df,values='is_booking', index='Date', columns=['hotel_country'],aggfunc='sum')\nhotel_country_piv       = hotel_country_piv.fillna(0)\nhotel_country_piv.head()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"61709957-4225-4208-b400-29aebbf645af"},"outputs":[],"source":"# .... continue Correlation\n\n# Plot correlation between range of hotel_country\ncountry_ids = [1,5,7,8,47,50,182,185]\n\nfig, (axis1) = plt.subplots(1,1,figsize=(15,5))\n\n# using summation of booking values for each hotel_country \nsns.heatmap(hotel_country_piv[country_ids].corr(),annot=True,linewidths=2,cmap=\"YlGnBu\")"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"7131683c-9aa0-4b5d-bd37-d727610d3ab6"},"outputs":[],"source":"# .... continue Correlation\n\n# Reformat the heatmap so similar hotel_country are next to each other\nsns.clustermap(hotel_country_piv[country_ids].corr(), cmap=\"YlGnBu\")"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"7296dc9c-251c-4dd3-bd4a-55d7021ceaa4"},"outputs":[],"source":"# Define training and testing sets\n\ntrain_df = pd.read_csv('../input/train.csv', usecols=['is_booking', 'srch_destination_id', 'hotel_cluster'])\ntest_df  = test_df[['id', 'srch_destination_id']]"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"f60b2966-3963-4f40-bf4d-a2ef12926451"},"outputs":[],"source":"# Group by srch_destination_id & hotel_cluster\n# Then for each destination id & hotel cluster, compute summation of bookings, and count number of clicks(no-booking)\n\ntrain_df = train_df.groupby(['srch_destination_id','hotel_cluster'])['is_booking'].agg(['sum','count'])\ntrain_df['count'] = train_df['count'] - train_df['sum']\ntrain_df.rename(columns={'sum': 'sum_bookings', 'count': 'clicks'}, inplace=True)\n\n# For each destination id & hotel cluster, \n# the relevance will be the number of bookings made + number of clicks(no-bookings) * 0.1\n# meaning for every 10 clicks, they will be counted as 1 booking\n\ntrain_df['relevance'] = train_df['sum_bookings'] + (train_df['clicks'] * 0.1)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"5eb61f6c-6fc1-4659-bf91-26be42d5999d"},"outputs":[],"source":"# For each srch_destination_id group, get top 5 hotel clusters with max relevance\n\ndef get_top_clusters(group):\n    indexes      = group.relevance.nlargest(5).index\n    top_clusters = group.hotel_cluster[indexes].values\n    if(len(top_clusters) < 5):\n        top_clusters = (list(top_clusters) + list(ferq_clusters.index))[:5]\n    return np.array_str(np.array(top_clusters))[1:-1]\n\ntrain_df      = train_df.reset_index()\nferq_clusters = train_df['hotel_cluster'].value_counts()[:5]\ntop_clusters  = train_df.groupby(['srch_destination_id']).apply(get_top_clusters)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"3cd74fe9-e216-4eb4-b029-fd7ff00f9150"},"outputs":[],"source":"# Create top_clusters_df\n\ntop_clusters_df = pd.DataFrame(top_clusters).rename(columns={0: 'hotel_cluster'})\ntop_clusters_df.head()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"32a4e543-a9cd-4bb6-a727-5593fbfe3b04"},"outputs":[],"source":"# Merge test_df with top_clusters_df\n\n# For every destination id in test_df, merge it with the corresponding id in top_clusters_df \ntest_df = pd.merge(test_df, top_clusters_df, how='left',left_on='srch_destination_id', right_index=True)\n\n# Fill NaN values with most frequent clusters\ntest_df.hotel_cluster.fillna(np.array_str(ferq_clusters.index)[1:-1],inplace=True)\n\nY_pred = test_df[\"hotel_cluster\"]"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"01085808-6d68-4c48-ba6e-1a6c143b1c6f"},"outputs":[],"source":"# Create submission\n\nsubmission = pd.DataFrame()\nsubmission[\"id\"]            = test_df[\"id\"]\nsubmission[\"hotel_cluster\"] = Y_pred\n\nsubmission.to_csv('expedia.csv', index=False)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"6970c3ad-d338-404c-b63f-f3dc7e4a00a2"},"outputs":[],"source":"# IMPORTANT! - Another Method for Hotel Cluster Prediction\n# Expedia Hotel Cluster Predictions\n# Link: https://www.kaggle.com/omarelgabry/expedia-hotel-recommendations/expedia-hotel-cluster-predictions"}],"metadata":{"_change_revision":0,"_is_fork":false,"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.5.2"}},"nbformat":4,"nbformat_minor":0}