{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"\nimport os\n# for dirname, _, filenames in os.walk('/kaggle/input'):\n#     for filename in filenames:\n#         print(os.path.join(dirname, filename))\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","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-03-16T09:22:43.220924Z","iopub.execute_input":"2022-03-16T09:22:43.221455Z","iopub.status.idle":"2022-03-16T09:22:43.231368Z","shell.execute_reply.started":"2022-03-16T09:22:43.221408Z","shell.execute_reply":"2022-03-16T09:22:43.230485Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# get expedia & test csv files as a DataFrame\nexpedia_df = pd.read_csv('../input/expedia-hotel-recommendations/train.csv', nrows=10000)\ntest_df    = pd.read_csv('../input/expedia-hotel-recommendations/test.csv')\n\n# preview the data\nexpedia_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:43.245097Z","iopub.execute_input":"2022-03-16T09:22:43.245533Z","iopub.status.idle":"2022-03-16T09:22:49.218991Z","shell.execute_reply.started":"2022-03-16T09:22:43.245485Z","shell.execute_reply":"2022-03-16T09:22:49.218090Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"expedia_df.info()\nprint(\"----------------------------\")\ntest_df.info()","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:49.220429Z","iopub.execute_input":"2022-03-16T09:22:49.220676Z","iopub.status.idle":"2022-03-16T09:22:49.250665Z","shell.execute_reply.started":"2022-03-16T09:22:49.220647Z","shell.execute_reply":"2022-03-16T09:22:49.250081Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","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)","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:49.252018Z","iopub.execute_input":"2022-03-16T09:22:49.252483Z","iopub.status.idle":"2022-03-16T09:22:49.411041Z","shell.execute_reply.started":"2022-03-16T09:22:49.252443Z","shell.execute_reply":"2022-03-16T09:22:49.410149Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### check data outlook","metadata":{}},{"cell_type":"code","source":"expedia_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:49.413111Z","iopub.execute_input":"2022-03-16T09:22:49.413340Z","iopub.status.idle":"2022-03-16T09:22:49.426112Z","shell.execute_reply.started":"2022-03-16T09:22:49.413313Z","shell.execute_reply":"2022-03-16T09:22:49.425531Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"expedia_df[expedia_df['is_booking']==1]","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:49.427072Z","iopub.execute_input":"2022-03-16T09:22:49.427425Z","iopub.status.idle":"2022-03-16T09:22:49.452153Z","shell.execute_reply.started":"2022-03-16T09:22:49.427387Z","shell.execute_reply":"2022-03-16T09:22:49.451528Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"set(expedia_df['hotel_continent'].tolist())","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:49.453102Z","iopub.execute_input":"2022-03-16T09:22:49.453638Z","iopub.status.idle":"2022-03-16T09:22:49.458884Z","shell.execute_reply.started":"2022-03-16T09:22:49.453609Z","shell.execute_reply":"2022-03-16T09:22:49.458146Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.to_datetime(expedia_df['srch_ci'].tolist())","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:49.460147Z","iopub.execute_input":"2022-03-16T09:22:49.460399Z","iopub.status.idle":"2022-03-16T09:22:49.481396Z","shell.execute_reply.started":"2022-03-16T09:22:49.460372Z","shell.execute_reply":"2022-03-16T09:22:49.480547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"min(pd.to_datetime(expedia_df['srch_ci'].tolist()))","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:49.482833Z","iopub.execute_input":"2022-03-16T09:22:49.483057Z","iopub.status.idle":"2022-03-16T09:22:49.511373Z","shell.execute_reply.started":"2022-03-16T09:22:49.483031Z","shell.execute_reply":"2022-03-16T09:22:49.510624Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"max(pd.to_datetime(expedia_df['srch_ci'].tolist()))","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:49.512427Z","iopub.execute_input":"2022-03-16T09:22:49.512644Z","iopub.status.idle":"2022-03-16T09:22:49.543242Z","shell.execute_reply.started":"2022-03-16T09:22:49.512619Z","shell.execute_reply":"2022-03-16T09:22:49.542528Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","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)","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:49.546091Z","iopub.execute_input":"2022-03-16T09:22:49.546349Z","iopub.status.idle":"2022-03-16T09:22:51.646547Z","shell.execute_reply.started":"2022-03-16T09:22:49.546321Z","shell.execute_reply":"2022-03-16T09:22:51.645699Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### # Where do most of the customers from a country 66 travel?","metadata":{}},{"cell_type":"code","source":"\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))","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:51.647828Z","iopub.execute_input":"2022-03-16T09:22:51.648030Z","iopub.status.idle":"2022-03-16T09:22:53.577518Z","shell.execute_reply.started":"2022-03-16T09:22:51.648005Z","shell.execute_reply":"2022-03-16T09:22:53.576645Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Plot frequency for each hotel_clusters","metadata":{}},{"cell_type":"code","source":"expedia_df[\"hotel_cluster\"].value_counts().plot(kind='bar',colormap=\"Set3\",figsize=(15,5))","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:53.578977Z","iopub.execute_input":"2022-03-16T09:22:53.579450Z","iopub.status.idle":"2022-03-16T09:22:56.125386Z","shell.execute_reply.started":"2022-03-16T09:22:53.579402Z","shell.execute_reply":"2022-03-16T09:22:56.124640Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### most frequent hotel clusters booked by customers from a country 66","metadata":{}},{"cell_type":"code","source":"\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))","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:56.126458Z","iopub.execute_input":"2022-03-16T09:22:56.126747Z","iopub.status.idle":"2022-03-16T09:22:58.626680Z","shell.execute_reply.started":"2022-03-16T09:22:56.126717Z","shell.execute_reply":"2022-03-16T09:22:58.626010Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### most frequent hotel clusters in a country 50","metadata":{}},{"cell_type":"code","source":"\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))\n\n","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:22:58.627954Z","iopub.execute_input":"2022-03-16T09:22:58.628357Z","iopub.status.idle":"2022-03-16T09:23:00.950787Z","shell.execute_reply.started":"2022-03-16T09:22:58.628327Z","shell.execute_reply":"2022-03-16T09:23:00.949834Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","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)","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:00.952147Z","iopub.execute_input":"2022-03-16T09:23:00.952399Z","iopub.status.idle":"2022-03-16T09:23:01.862166Z","shell.execute_reply.started":"2022-03-16T09:23:00.952358Z","shell.execute_reply":"2022-03-16T09:23:01.861304Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### check frequency customer from mobile & with package with flight ","metadata":{}},{"cell_type":"code","source":"\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)","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:01.863597Z","iopub.execute_input":"2022-03-16T09:23:01.864053Z","iopub.status.idle":"2022-03-16T09:23:02.176973Z","shell.execute_reply.started":"2022-03-16T09:23:01.864004Z","shell.execute_reply":"2022-03-16T09:23:02.176064Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### most impactful channel","metadata":{}},{"cell_type":"code","source":"\nfig, (axis1) = plt.subplots(1,1,figsize=(15,3))\n\nsns.countplot(x='channel', order=list(range(0,10)), data=expedia_df, palette=\"Set3\")\n\n","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:02.178412Z","iopub.execute_input":"2022-03-16T09:23:02.178778Z","iopub.status.idle":"2022-03-16T09:23:02.450144Z","shell.execute_reply.started":"2022-03-16T09:23:02.178738Z","shell.execute_reply":"2022-03-16T09:23:02.449568Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### visualization check in date, Month , Week","metadata":{}},{"cell_type":"code","source":"expedia_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) ","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:02.453459Z","iopub.execute_input":"2022-03-16T09:23:02.454001Z","iopub.status.idle":"2022-03-16T09:23:03.715201Z","shell.execute_reply.started":"2022-03-16T09:23:02.453956Z","shell.execute_reply":"2022-03-16T09:23:03.714354Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### visualization booking date as YYYY-MM","metadata":{}},{"cell_type":"code","source":"\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)\n\n","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:03.716460Z","iopub.execute_input":"2022-03-16T09:23:03.716790Z","iopub.status.idle":"2022-03-16T09:23:04.239152Z","shell.execute_reply.started":"2022-03-16T09:23:03.716751Z","shell.execute_reply":"2022-03-16T09:23:04.238315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"expedia_df","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:04.240403Z","iopub.execute_input":"2022-03-16T09:23:04.240708Z","iopub.status.idle":"2022-03-16T09:23:04.267437Z","shell.execute_reply.started":"2022-03-16T09:23:04.240678Z","shell.execute_reply":"2022-03-16T09:23:04.266518Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### visualization of hotel country and YYYY-MM","metadata":{}},{"cell_type":"code","source":"\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()","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:04.268392Z","iopub.execute_input":"2022-03-16T09:23:04.268619Z","iopub.status.idle":"2022-03-16T09:23:04.312422Z","shell.execute_reply.started":"2022-03-16T09:23:04.268583Z","shell.execute_reply":"2022-03-16T09:23:04.311684Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### correlation between range of hotel country ID","metadata":{}},{"cell_type":"code","source":"\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\")","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:04.313371Z","iopub.execute_input":"2022-03-16T09:23:04.314086Z","iopub.status.idle":"2022-03-16T09:23:05.144242Z","shell.execute_reply.started":"2022-03-16T09:23:04.314052Z","shell.execute_reply":"2022-03-16T09:23:05.143362Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","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\")","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:05.145436Z","iopub.execute_input":"2022-03-16T09:23:05.146051Z","iopub.status.idle":"2022-03-16T09:23:05.783349Z","shell.execute_reply.started":"2022-03-16T09:23:05.146018Z","shell.execute_reply":"2022-03-16T09:23:05.782517Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Define training and testing sets\n","metadata":{}},{"cell_type":"code","source":"train_df = pd.read_csv('../input/expedia-hotel-recommendations/train.csv', usecols=['is_booking', 'srch_destination_id', 'hotel_cluster'])\ntest_df  = test_df[['id', 'srch_destination_id']]","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:05.784476Z","iopub.execute_input":"2022-03-16T09:23:05.785024Z","iopub.status.idle":"2022-03-16T09:23:49.872196Z","shell.execute_reply.started":"2022-03-16T09:23:05.784976Z","shell.execute_reply":"2022-03-16T09:23:49.871084Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### group of search id finding the sum of booking and number of click","metadata":{}},{"cell_type":"code","source":"train_df.columns","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:49.873568Z","iopub.execute_input":"2022-03-16T09:23:49.874391Z","iopub.status.idle":"2022-03-16T09:23:49.880869Z","shell.execute_reply.started":"2022-03-16T09:23:49.874343Z","shell.execute_reply":"2022-03-16T09:23:49.880014Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df[train_df['srch_destination_id']==0]","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:49.882078Z","iopub.execute_input":"2022-03-16T09:23:49.882700Z","iopub.status.idle":"2022-03-16T09:23:49.960231Z","shell.execute_reply.started":"2022-03-16T09:23:49.882658Z","shell.execute_reply":"2022-03-16T09:23:49.959653Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df[train_df['srch_destination_id']==1]","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:49.961445Z","iopub.execute_input":"2022-03-16T09:23:49.961871Z","iopub.status.idle":"2022-03-16T09:23:50.003837Z","shell.execute_reply.started":"2022-03-16T09:23:49.961843Z","shell.execute_reply":"2022-03-16T09:23:50.003137Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.loc[(train_df['srch_destination_id']==1) & (train_df['hotel_cluster']==20 )]","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:50.008500Z","iopub.execute_input":"2022-03-16T09:23:50.009105Z","iopub.status.idle":"2022-03-16T09:23:50.094126Z","shell.execute_reply.started":"2022-03-16T09:23:50.009071Z","shell.execute_reply":"2022-03-16T09:23:50.093489Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### sum is the sum of transaction that is booking\n### count is the total number of transaction it show up","metadata":{}},{"cell_type":"code","source":"\ntrain_df = train_df.groupby(['srch_destination_id','hotel_cluster'])['is_booking'].agg(['sum','count'])\ntrain_df","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:50.095358Z","iopub.execute_input":"2022-03-16T09:23:50.095755Z","iopub.status.idle":"2022-03-16T09:23:53.390509Z","shell.execute_reply.started":"2022-03-16T09:23:50.095725Z","shell.execute_reply":"2022-03-16T09:23:53.389661Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"### now we exclude itself from the count\n\ntrain_df['count'] = train_df['count'] - train_df['sum']\ntrain_df\n","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:53.391838Z","iopub.execute_input":"2022-03-16T09:23:53.392240Z","iopub.status.idle":"2022-03-16T09:23:53.410437Z","shell.execute_reply.started":"2022-03-16T09:23:53.392171Z","shell.execute_reply":"2022-03-16T09:23:53.409614Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_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)","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:53.411709Z","iopub.execute_input":"2022-03-16T09:23:53.411933Z","iopub.status.idle":"2022-03-16T09:23:53.421117Z","shell.execute_reply.started":"2022-03-16T09:23:53.411908Z","shell.execute_reply":"2022-03-16T09:23:53.420525Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:53.422496Z","iopub.execute_input":"2022-03-16T09:23:53.423266Z","iopub.status.idle":"2022-03-16T09:23:53.443431Z","shell.execute_reply.started":"2022-03-16T09:23:53.423228Z","shell.execute_reply":"2022-03-16T09:23:53.442606Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df_copy = train_df.copy()","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:23:53.444730Z","iopub.execute_input":"2022-03-16T09:23:53.445010Z","iopub.status.idle":"2022-03-16T09:23:53.452347Z","shell.execute_reply.started":"2022-03-16T09:23:53.444972Z","shell.execute_reply":"2022-03-16T09:23:53.451504Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df_copy2 = train_df_copy.reset_index()","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:24:31.717341Z","iopub.execute_input":"2022-03-16T09:24:31.717646Z","iopub.status.idle":"2022-03-16T09:24:31.731277Z","shell.execute_reply.started":"2022-03-16T09:24:31.717606Z","shell.execute_reply":"2022-03-16T09:24:31.730540Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df_copy2","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:24:43.044465Z","iopub.execute_input":"2022-03-16T09:24:43.044880Z","iopub.status.idle":"2022-03-16T09:24:43.057671Z","shell.execute_reply.started":"2022-03-16T09:24:43.044851Z","shell.execute_reply":"2022-03-16T09:24:43.056977Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### each destination we get the top 5 of relevence","metadata":{}},{"cell_type":"markdown","source":"### exploring each destination id","metadata":{}},{"cell_type":"code","source":"train_df_copy2[train_df_copy2['srch_destination_id']==1]","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:24:48.618400Z","iopub.execute_input":"2022-03-16T09:24:48.619132Z","iopub.status.idle":"2022-03-16T09:24:48.630251Z","shell.execute_reply.started":"2022-03-16T09:24:48.619095Z","shell.execute_reply":"2022-03-16T09:24:48.629650Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df_copy2[train_df_copy2['srch_destination_id']==4]","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:56:51.259477Z","iopub.execute_input":"2022-03-16T09:56:51.259773Z","iopub.status.idle":"2022-03-16T09:56:51.276925Z","shell.execute_reply.started":"2022-03-16T09:56:51.259742Z","shell.execute_reply":"2022-03-16T09:56:51.276009Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\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)","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:25:03.178897Z","iopub.execute_input":"2022-03-16T09:25:03.179682Z","iopub.status.idle":"2022-03-16T09:25:49.560576Z","shell.execute_reply.started":"2022-03-16T09:25:03.179586Z","shell.execute_reply":"2022-03-16T09:25:49.559839Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"top_clusters","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:25:49.561930Z","iopub.execute_input":"2022-03-16T09:25:49.562293Z","iopub.status.idle":"2022-03-16T09:25:49.569503Z","shell.execute_reply.started":"2022-03-16T09:25:49.562258Z","shell.execute_reply":"2022-03-16T09:25:49.568701Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create top_clusters_df\n\ntop_clusters_df = pd.DataFrame(top_clusters).rename(columns={0: 'hotel_cluster'})\ntop_clusters_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:25:49.572500Z","iopub.execute_input":"2022-03-16T09:25:49.572763Z","iopub.status.idle":"2022-03-16T09:25:49.589444Z","shell.execute_reply.started":"2022-03-16T09:25:49.572728Z","shell.execute_reply":"2022-03-16T09:25:49.588305Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"top_clusters_df['hotel_cluster'][0].split()","metadata":{"execution":{"iopub.status.busy":"2022-03-16T09:57:34.664896Z","iopub.execute_input":"2022-03-16T09:57:34.665681Z","iopub.status.idle":"2022-03-16T09:57:34.671258Z","shell.execute_reply.started":"2022-03-16T09:57:34.665635Z","shell.execute_reply":"2022-03-16T09:57:34.670626Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Merge test dataset with top_clusters_df","metadata":{}},{"cell_type":"code","source":"\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\ntest_df\n","metadata":{"execution":{"iopub.status.busy":"2022-03-16T10:00:19.712334Z","iopub.execute_input":"2022-03-16T10:00:19.712621Z","iopub.status.idle":"2022-03-16T10:00:20.011784Z","shell.execute_reply.started":"2022-03-16T10:00:19.712577Z","shell.execute_reply":"2022-03-16T10:00:20.010911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 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\"]","metadata":{"execution":{"iopub.status.busy":"2022-03-16T10:06:27.989441Z","iopub.execute_input":"2022-03-16T10:06:27.990010Z","iopub.status.idle":"2022-03-16T10:06:28.287850Z","shell.execute_reply.started":"2022-03-16T10:06:27.989967Z","shell.execute_reply":"2022-03-16T10:06:28.286970Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","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)","metadata":{"execution":{"iopub.status.busy":"2022-03-16T10:07:02.457525Z","iopub.execute_input":"2022-03-16T10:07:02.457865Z","iopub.status.idle":"2022-03-16T10:07:08.594707Z","shell.execute_reply.started":"2022-03-16T10:07:02.457833Z","shell.execute_reply":"2022-03-16T10:07:08.594055Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"##### ","metadata":{}}]}