{"cells":[{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"d61546ff-9aa0-4d30-bf29-0d2ae9744590"},"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":"e624b0b2-2d71-435f-8b94-86ed1e22d4a1"},"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":"5f48828d-56f0-480a-ad6a-ca085d30a7ff"},"outputs":[],"source":"expedia_df.info()\nprint(\"----------------------------\")\ntest_df.info()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"4aac8732-6f0c-4313-86e8-c5d5dc6c6d42"},"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":"3c5000ac-d8ef-405b-9ebc-79f0e191fb07"},"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":"13233804-7f3e-4f0b-a009-0ad13312faa8"},"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":"6245ccd1-d891-4b3f-855e-22b2a2a5ea68"},"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":"9ee3eb6d-5801-4b22-b142-faeddd53375c"},"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":"66a5f536-3690-44bd-8bb8-ba8e2c172ae0"},"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":"ce6b93eb-7d09-44dc-8bbd-f311864273da"},"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":"4909638b-c235-42a9-92b6-7ea387838102"},"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":"96b9cda1-3d39-444a-93cd-efa423aafd40"},"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":"967fa2d6-6689-4e24-85a7-dc8db343c2ef"},"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":"e0004471-9d16-45f8-819b-2dca39486860"},"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":"733ab297-965a-4285-aa32-8e55598237e5"},"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":"76792c3e-8667-4795-8992-36143eac23db"},"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":"9cf4f501-4656-44f8-9755-c05cc3068919"},"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":"9a87385b-6305-4d84-bcb6-84316fc9646f"},"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":"3a945757-b0cd-4b2f-aec4-54449a9a6295"},"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":"994b56e4-b93e-4811-814c-01ad81c50a30"},"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":"c220803c-4f93-493f-8716-aaa8db695dbd"},"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":"377ea763-6338-42d7-86d9-bd58dccc557f"},"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":"6aaf58a5-67db-491b-8b7a-87d1d006b0dc"},"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":"19342510-584c-491b-b4fc-a464da9927e7"},"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":"f99d445e-71f3-4d30-9239-f9362ebbd8dd"},"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"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"4a6371ee-79ef-6910-99c0-978f7b5ce45e"},"outputs":[],"source":"train_df.groupby(['srch_destination_id']).ix(0).apply(get_top_clusters)"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"77bbdb06-94cf-c03d-2784-311b38e9cd80"},"outputs":[],"source":""}],"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}