{"cells":[{"metadata":{"_uuid":"b2f38bebc11e4b29f6b70e73b0571fd4a8532280"},"cell_type":"markdown","source":"# Data Preprocessing on Expedia Hotel Dataset\n\n\n"},{"metadata":{"_uuid":"02b3a415f259bbff9c660664756c94723b163a88"},"cell_type":"markdown","source":"# Preprocessing\nData preprocessing is a data mining technique that involves transforming raw data into an understandable format. Real-world data is often incomplete, inconsistent, and/or lacking in certain behaviors or trends, and is likely to contain many errors. Data preprocessing is a proven method of resolving such issues. Data preprocessing prepares raw data for further processing.\n\n### Why preprocessing?\n\nReal-world data are generally:\n\n- **Incomplete**: lacking attribute values, lacking certain attributes of interest, or containing only aggregate data\n- **Noisy**: containing errors or outliers\n- **Inconsistent**: containing discrepancies in codes or names\n\nTasks in data preprocessing:\n\n- **Data cleaning**: fill in missing values, smooth noisy data, identify or remove outliers, and resolve inconsistencies.\n- **Data integration**: using multiple databases, data cubes, or files.\n- **Data transformation**: normalization and aggregation.\n- **Data reduction**: reducing the volume but producing the same or similar analytical results.\n- **Data discretization**: part of data reduction, replacing numerical attributes with nominal ones.\n\n\n"},{"metadata":{"_uuid":"61a2dd16213bd9409fa465d911a61b8ed7bfbc8b"},"cell_type":"markdown","source":"#### What is Exploratory data analysis?\nIn statistics, exploratory data analysis (EDA) is an approach to analyzing data sets to summarize their main characteristics, often with visual methods. A statistical model can be used or not, but primarily EDA is for seeing what the data can tell us beyond the formal modeling or hypothesis testing task."},{"metadata":{"_uuid":"5ac50e0766879425727ddfd730a71de7e2b9e25c"},"cell_type":"markdown","source":"#### Data Cleaning\nData cleaning is the process of cleaning / standardising the data to make it ready for analysis. Most of times, there will be discrepancies in the captured data such as incorrect data formats, missing data, errors while capturing the data. This is an important step in any given data science project because the accuracy of the results depends heavily on the data we use."},{"metadata":{"_uuid":"a761c3de6fbf573fbc6e823464482f8ba5f684bb"},"cell_type":"markdown","source":"## Import  Libraries"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nimport os\nprint(os.listdir(\"../input\"))\nimport pandas as pd\nimport boto3\nfrom io import StringIO\nimport io\nimport string\nimport random\nimport json\nimport pickle\nimport numpy as np \nimport pandas as pd\nimport matplotlib.pyplot as plt\n%matplotlib inline\nimport seaborn as sns\nfrom scipy import stats\nfrom scipy.stats import norm\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"### Dataset\n\n- Dataset 2013-1014 time frame\n- Train Data: 37 million entires\n- Test Data: 2.5 million entries"},{"metadata":{"trusted":true,"_uuid":"ccffca67351a1873910e75183f9ef7cdd5229b00"},"cell_type":"code","source":"# Loading 100k data rows\n# Load train data\ntrain = pd.read_csv('../input/train.csv', nrows=100000)\n\n# Load test data\ntest = pd.read_csv('../input/test.csv', nrows=100000)\n\n# Load destination data\ndestination = pd.read_csv('../input/destinations.csv', nrows=100000)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"80b0bb01a605ca576a4f74a8765bf7d5f5ae3679"},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2414038b41689866c070b4dc35f2b899401f6c07"},"cell_type":"code","source":"train.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9fc4e34c4861ceab5e509d73bec12d871ca065c0"},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"012455486e23e6d17e292207ae80faa96cb10e47"},"cell_type":"markdown","source":"#### Features\n\n|  Feature |  Description | \n|----------|:---------|\n| date_time  | Timestamp     |\n| site_name | ID of Expedia point of sale|\n| posa_continent | ID of site’s continent |\n| user_location_country |ID of customer’s country |\n| user_location_region|ID of customer’s region |\n| user_location_city| ID of customer’s city|\n| orig_destination_distance| Physical distance between a hotel and a customer|\n| user_id| ID of user|\n| is_mobile| 1 for mobile device, 0 otherwise|\n| is_package| 1 if booking/click was part of package, 0 otherwise|\n| channel| ID of a marketing channel|\n| srch_ci| Check-in date|\n| srch_co| Check-out date|\n| srch_adults_cnt| Number of adults|\n| srch_children_cnt| Number of children|\n| srch_rm_cnt| Number of rooms|\n| srch_destination_id| ID of the destination|\n| srch_destination_type_id| Type of destination|\n| is_booking | 1 if a booking, 0 if a click|\n| cnt| Number of similar events in the context of the same user sessiont|\n| hotel_continent| Hotel continent|\n| hotel_country| Hotel country|\n| hotel_market| Hotel market|\n| hotel_cluster| ID of hotel cluster|\n"},{"metadata":{"_uuid":"367ad2a46c27416516153727d3cc99e87e0122b9"},"cell_type":"markdown","source":"## Steps\n\nfirst step was to clean and pre-process the data and perform exploratory analysis to get some interesting insights into the process of choosing a hotel.\n\n- Remove the users who did not booked the hotel\n- Identify the searches by each user belonging to a specific type of destination\n- orig_destination_distance contains Nan values\n- The  check-in  and  check-out  dates  to find the duration of the stay for each of the entries in the training set.\n\n"},{"metadata":{"trusted":true,"_uuid":"155b68f2708e1fa9419bf920e8dc90a49577266b"},"cell_type":"code","source":"# Check the percentage of Nan in dataset\ntotal = train.isnull().sum().sort_values(ascending=False)\npercent = (train.isnull().sum()/train['hotel_cluster'].count()).sort_values(ascending=False)\nmissing_data = pd.concat([total, percent], axis=1, keys=['Total', 'Percent'])\nmissing_data.head(20)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2e4dc83541b2b757766ba8753b4d0a9cd7fb36e4"},"cell_type":"markdown","source":"## Visualization of Data\n"},{"metadata":{"trusted":true,"_uuid":"1d7ff389b5d27fd4785ed138197cae6d91ac3b1d"},"cell_type":"code","source":"fig, ax = plt.subplots()\nfig.set_size_inches(15, 10)\nsns.heatmap(train.corr(),cmap='coolwarm',ax=ax,annot=True,linewidths=2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"75e6bc30550e0baf36b5a6c2171e2054686dbce6"},"cell_type":"code","source":"# Frequency of posa continent\nfig, ax = plt.subplots()\nfig.set_size_inches(13, 8)\nsns.countplot('posa_continent', data=train,order=[0,1,2,3,4],ax=ax)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cc330c5432c6f6fab307137e3c5a7da914d3bc15"},"cell_type":"code","source":"# frequency of hotel continent\nfig, ax = plt.subplots()\nfig.set_size_inches(13, 8)\nsns.countplot('hotel_continent', data=train,order=[0,2,3,4,5,6],ax=ax)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d78263247e5b62156ef7c5d60d0a83cbac1952a1"},"cell_type":"code","source":"# Frequency of booking through mobile\nfig, ax = plt.subplots()\nfig.set_size_inches(13, 8)\nsns.countplot(x='is_mobile',data=train, order=[0,1],ax=ax)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fff4733b4d722ee17c8bc5c5ecf92a8c1444a614"},"cell_type":"code","source":"# frequency of bookings with package\nfig, ax = plt.subplots()\nfig.set_size_inches(13, 8)\nsns.countplot(x='is_package',data=train, order=[0,1], ax=ax)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"08d34f4e5fc06fb8539bac0e015ad5d5f196a670"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2b7bd539d56b79c28dd54a0c9c812f02a38b27f0"},"cell_type":"markdown","source":"## Clean the Data"},{"metadata":{"trusted":true,"_uuid":"cb63153759013362729bc63891460f90ec57c0b9"},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0f73a93553010c004a4fd263f1850f32bdf440fb"},"cell_type":"markdown","source":"### Convert it into numerical values which will be relevant to our model.\n- date_time\n- srch_ci\n- srch_co\n\n\n"},{"metadata":{"_uuid":"63056ab67dcbef09581def2ccd5d8b99914497a3"},"cell_type":"markdown","source":"## Add Extra features\nExtract relevant information from date columns\n### Additional attributes\n- stay_dur: number of duration of stay\n- no_of_days_bet_booking: number of days between the booking and \n- Cin_day: Check-in day\n- Cin_month: Check-in month\n- Cin_year: Check-out year"},{"metadata":{"trusted":true,"_uuid":"92c90e5f29091f15a6ea7cd537e64bd98a6e98a9"},"cell_type":"code","source":"# Function to convert date object into relevant attributes\ndef convert_date_into_days(df):\n    df['srch_ci'] = pd.to_datetime(df['srch_ci'])\n    df['srch_co'] = pd.to_datetime(df['srch_co'])\n    df['date_time'] = pd.to_datetime(df['date_time'])\n    \n    df['stay_dur'] = (df['srch_co'] - df['srch_ci']).astype('timedelta64[D]')\n    df['no_of_days_bet_booking'] = (df['srch_ci'] - df['date_time']).astype('timedelta64[D]')\n    \n    # For hotel check-in\n    # Month, Year, Day\n    df['Cin_day'] = df[\"srch_ci\"].apply(lambda x: x.day)\n    df['Cin_month'] = df[\"srch_ci\"].apply(lambda x: x.month)\n    df['Cin_year'] = df[\"srch_ci\"].apply(lambda x: x.year)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a85d8ce5569771547285e38360fb39882d4287db"},"cell_type":"code","source":"convert_date_into_days(train)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8cda4efb54d5dfc9ffa23d5f4830583c8cb806d4"},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ef8b992ad1612dd187f011e4d0330a95334ff88f"},"cell_type":"code","source":"# Count the bookings in each month\nfig, ax = plt.subplots()\nfig.set_size_inches(13, 8)\nsns.countplot('Cin_month',data=train[train[\"is_booking\"] == 1],order=list(range(1,13)),ax=ax)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9470031484cff52d01f0a9ae1f21290839423577"},"cell_type":"code","source":"# Count the bookings as per the day\nfig, ax = plt.subplots()\nfig.set_size_inches(13, 8)\nsns.countplot('Cin_day',data=train[train[\"is_booking\"] == 1],order=list(range(1,32)),ax=ax)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5aedcaf92721476b8e68b41b0985d468acda12ca"},"cell_type":"code","source":"# Count the bookings as per the stay_duration\nfig, ax = plt.subplots()\nfig.set_size_inches(13, 8)\nsns.countplot('stay_dur',data=train[train[\"is_booking\"] == 1],ax=ax)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"daa6a4d890b23892e94d2cecfe01b743eb2d2422"},"cell_type":"code","source":"# Check the percentage of Nan in dataset\ntotal = train.isnull().sum().sort_values(ascending=False)\npercent = (train.isnull().sum()/train['hotel_cluster'].count()).sort_values(ascending=False)\nmissing_data = pd.concat([total, percent], axis=1, keys=['Total', 'Percent'])\nmissing_data","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7c870cdf136ee5db4ebd38bd10e3d21f99f2431c"},"cell_type":"markdown","source":"### Fill nan with the day which has max occurence\n"},{"metadata":{"trusted":true,"_uuid":"5e58ec845665242b4296ff927ce59247123af953"},"cell_type":"code","source":"#train['Cin_day'].value_counts() = 26\n#train['Cin_month'].value_counts() = 8\n#train['Cin_year'].value_counts() = 2014\n#train['stay_dur'].value_counts() = 1\n#train['no_of_days_bet_booking'].value_counts() = 0","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9648b728ceabca8584a473251b91373234c270c5"},"cell_type":"code","source":"train['Cin_day'] = train['Cin_day'].fillna(26.0)\ntrain['Cin_month'] = train['Cin_month'].fillna(8.0)\ntrain['Cin_year'] = train['Cin_year'].fillna(2014.0)\ntrain['stay_dur'] = train['stay_dur'].fillna(1.0)\ntrain['no_of_days_bet_booking'] = train['no_of_days_bet_booking'].fillna(0.0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1066f40c6ab6975a638864a0cf2c9671689aefbb"},"cell_type":"code","source":"# Fill average values in place for nan, fill with mean\ntrain['orig_destination_distance'].fillna(train['orig_destination_distance'].mean(), inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d8e726fd4a542663e7fc3e49b31a25b06bfbc9c4"},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1a7bfe0419b4492038e99f3ec11cc4aef2b136a7"},"cell_type":"code","source":"## Remove datetime object from the dataset\n#columns to remove\nuser_id = train['user_id']\ncolumns = ['date_time', 'srch_ci', 'srch_co','user_id','srch_destination_type_id','srch_destination_id']\ntrain.drop(columns=columns,axis=1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"556c2959c0838c843708e91f858db3847bf39b73"},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"60339a67fb0105f2643f9f97a6a4b3bd5251ccde"},"cell_type":"markdown","source":"We have preprocessed our data and it is ready to fit into the model.\nAll the object values are converted into numerical values. Also, we have more insights of the data."},{"metadata":{"trusted":true,"_uuid":"6e4842c8d909cf62ffa9fd96082a3784a4c2ac2f"},"cell_type":"markdown","source":"Reference : http://www.cs.ccsu.edu/~markov/ccsu_courses/datamining-3.html"}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}