{"cells":[
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "# Postgresql\n\n# Create table 'train_expedia' and import the data from csv to the table\ncreate table train_expedia (\ndate_time timestamp,                 \nsite_name int,                \nposa_continent int,           \nuser_location_country int,    \nuser_location_region int,\nuser_location_city int,     \norig_destination_distance double precision,\nuser_id int,                \nis_mobile smallint,               \nis_package int,              \nchannel int,           \nsrch_ci char(25),            \nsrch_co char(25),               \nsrch_adults_cnt int,         \nsrch_children_cnt int,         \nsrch_rm_cnt int,             \nsrch_destination_id int,       \nsrch_destination_type_id int,\nis_booking smallint,              \ncnt bigint,\nhotel_continent int,          \nhotel_country int,            \nhotel_market int,                       \nhotel_cluster int\n);\n\nCOPY train_expedia from '/.../train.csv' WITH (FORMAT CSV, DELIMITER ',', HEADER);\n\n"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "# Create new table 'train' from train_expedia and calculate total number of bookings and clicks\n\ncreate table train as SELECT srch_destination_id, hotel_cluster, \nSUM (is_booking) as bookings,COUNT(is_booking) AS clicks                                          \nfrom train_expedia\nGROUP BY srch_destination_id, hotel_cluster\norder by srch_destination_id, hotel_cluster\n\n# Create new table 'train1' from train and calculate relevance\ncreate table train1 as select srch_destination_id, hotel_cluster,bookings,clicks, \n(sum(bookings) + 0.05*sum(clicks)) as relevance                                          \nfrom train\nGROUP BY srch_destination_id, hotel_cluster,bookings,clicks\norder by srch_destination_id, hotel_cluster\n\n# Create new table 'train2' from train1 with 3 columns we need\n# This is the final table we are going to use in python\n\ncreate table train2 as select srch_destination_id, hotel_cluster,relevance from train1\ngroup by srch_destination_id, hotel_cluster,relevance\norder by srch_destination_id,relevance desc\n\nselect * from train2\n\n# Export train2 as csv to use in python"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "# Thanks to \"dune_dweller\" :)\nimport numpy as np\nimport pandas as pd\n\n# Read the csv we created using postgresql and the test csv with 1 column\ntrain = pd.read_csv('train2.csv')\ntest = pd.read_csv('test.csv',\n                    dtype={'srch_destination_id':np.int32},\n                    usecols=['srch_destination_id'],)"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "# Function to find most popular hotel clusters by destination\ndef most_popular(group, n_max=5):\n    relevance = group['relevance'].values\n    hotel_cluster = group['hotel_cluster'].values\n    most_popular = hotel_cluster[np.argsort(relevance)[::-1]][:n_max]\n    return np.array_str(most_popular)[1:-1]"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "# Get most popular hotel clusters for all destinations.\nmost_pop = train.groupby(['srch_destination_id']).apply(most_popular)\nmost_pop = pd.DataFrame(most_pop).rename(columns={0:'hotel_cluster'})\nmost_pop.head()"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "# Predict for test data\ntest = test.merge(most_pop, how='left',left_on='srch_destination_id',right_index=True)\ntest.head()"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "# Check for  null values\ntest.hotel_cluster.isnull().sum()"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "# Fill nas with hotel clusters that are most popular overall\nmost_pop_all = train.groupby('hotel_cluster')['relevance'].sum().nlargest(5).index\nmost_pop_all = np.array_str(most_pop_all)[1:-1]\nmost_pop_all"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "test.hotel_cluster.fillna(most_pop_all,inplace=True)\ntest.hotel_cluster.isnull().sum()"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "# Save the submission file\ntest.hotel_cluster.to_csv('postgresql_and_pandas.csv',header=True, index_label='id')"
 }
],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"}}, "nbformat": 4, "nbformat_minor": 0}