{"cells":[{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"01e9ebe8-8b83-4f84-be9d-d52b32034745"},"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":"9e24d07a-d2bc-4d57-abde-30a0f4cc5584"},"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":"e58f3d80-79cc-40f1-a116-79d0f127f306"},"outputs":[],"source":"expedia_df.info()\nprint(\"----------------------------\")\ntest_df.info()"},{"cell_type":"code","execution_count":null,"metadata":{"_cell_guid":"7ad39bcb-8b1b-44f1-aa29-6e29cd909736"},"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":"98b35a52-e27e-4627-a177-b41533df61f6"},"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":"f1932163-f204-4729-8eb7-242db9c23bac"},"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":"c48e50e8-cfb5-4866-b579-6156931411b5"},"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":"ba1e6989-9414-43ca-854e-ff00356eedfb"},"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":"c9e1a2e5-0b35-46f7-9407-c302ee6efe77"},"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":"efa13d69-a32c-47b8-b590-0e7efa3e352c"},"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":"baacc1bd-e7a5-471d-b10c-f5d81cf48798"},"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":"ec857941-e0c4-4cca-836e-db983fab934f"},"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":"b59f7de8-db87-4ae5-8624-c4811ba04df0"},"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":"7903d397-a9a7-43a6-89b1-02918cf039b9"},"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":"ee1cdf74-a4f7-4d0e-b60c-f9d3f53deedd"},"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":"6c9c0fbd-a7c8-4066-997a-bc623b356f22"},"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":"4ecc50a4-e6b6-452f-a9ba-ac02cdb66b7f"},"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":"bb14342d-a04b-4c1b-90c2-9f766b0d85d4"},"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":"2cba9543-0440-4f0d-91d3-bae648f65108"},"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":"905e768f-eec0-44ea-882e-6bb5073d9da9"},"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":"616e86ab-5010-4948-b449-ca2c78ae1429"},"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":"f9efca9d-78b9-4a66-9dea-8e68be5563a0"},"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":"b1dfca99-7bbe-43b4-abf5-78919785b8d7"},"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":"842da048-1562-4a70-a3df-bcf066c36600"},"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":"f73b71fa-1b8c-4b49-b402-9c9e9c04c191"},"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}