{"cells":[{"metadata":{"_uuid":"eb0330387b86fda9e9153af0041a1daace18e354"},"cell_type":"markdown","source":"## Feature Engineering for Customer Revenue Prediction\n\nThe purpose of this Kernel is to ingest the raw data, extract features, and engineer new ones to provide community with a ready-to-model flat dataset. The kernel is split into three part:\n1. [Data Ingestion](#Data Ingestion)\n2. [Feature Extraction](#Feature Extraction)\n3. Feature Engineering - WIP\n<a id='Data Ingestion'></a>\n### 1. Data Ingestion\nFirst, I import the libraries required to extraction."},{"metadata":{"trusted":true,"_uuid":"40906aebd1850aee42fff1db6ec628a47d6972ee"},"cell_type":"code","source":"# Import libraries\nimport pandas as pd\nimport numpy as np\nimport json\nfrom pandas.io.json import json_normalize","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8b0daefa8d6a1d6e47427228548ec0b25411e2c2"},"cell_type":"markdown","source":"Next, I use the function defined by [Julian Peller](https://www.kaggle.com/julian3833) to read ingest the raw data. Note that the function will automatically process nested JSON data elements and populate each into a separate column. "},{"metadata":{"trusted":true,"_uuid":"7b9398ba05e7ec35c2226c79a6ab998725a7ba92"},"cell_type":"code","source":"# Read in the raw traininig dataset\n# Credit: https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields\nJSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\ndef load_df(csv_path='../train.csv'):\n\n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'},\n                     parse_dates=['date']) # Note: added this line to Julian's code to parse dates on ingestion. It slows the process a bit\n    \n    for column in JSON_COLUMNS:\n        column_as_df = json_normalize(df[column])\n        column_as_df.columns = [f\"{column}.{subcolumn}\" for subcolumn in column_as_df.columns]\n        df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)\n\n    return df\n\nraw_train = load_df(\"../input/train.csv\")","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_uuid":"242f23f1cdbf85510a7df10ba59dc8bfdca5fc98"},"cell_type":"markdown","source":"Similar to other Kernels, I drop the columns that are either all missing values or have just one unique value filled in (looking at you _trafficSource.campaign_ :) )"},{"metadata":{"trusted":true,"_uuid":"2ddbee21c23a9cacebe02f6b667ca4ac3019d7f6"},"cell_type":"code","source":"# Drop columns with just one value or all unknown\ncols_to_drop = [col for col in raw_train.columns if raw_train[col].nunique() == 1]\nraw_train.drop(columns = cols_to_drop, inplace=True)\n\n# Drop campaign colum as it only has one non-null value\nraw_train.drop(['trafficSource.campaign'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"377f572eea0c426664683c8bd13e10bcac70df9b"},"cell_type":"markdown","source":"To make column names nicer, I'll rename a few to avoid the nesting dot notation. To do this I will split each column name on the dot (.) character and select the last string in the list to be the new column name."},{"metadata":{"trusted":true,"_uuid":"2504ceb1104496780356308690c9f93be5e9a596"},"cell_type":"code","source":"# Rename long column names to be more concise\nraw_train.rename(columns={col_name: col_name.split('.')[-1] for col_name in raw_train.columns}, inplace = True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1cb9715d64c9a623957c0a6d21febd71c2a55ef9"},"cell_type":"markdown","source":"At the end, I am left with 30 columns, in a dataset of 903,653 rows. One of the columns is the dependent variable we are interested in predicting the log of accross all user visits - _transactionRevenue_."},{"metadata":{"trusted":true,"_uuid":"c9c67c0867730e3e91fae3a32b3b64733c2f6e49"},"cell_type":"code","source":"print('Number of columns: {:}\\nNumber of rows: {:}'.format(raw_train.shape[1], raw_train.shape[0]))\n\n# Fill transactionRevenue with zeroes and convert its type to numeric\nraw_train['transactionRevenue'].fillna(0, inplace=True)\nraw_train['transactionRevenue'] = pd.to_numeric(raw_train['transactionRevenue'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"672f45aa51ee57779632f0a8f56e22b9ff974039"},"cell_type":"markdown","source":"<a id='Feature Extraction'></a>\n### 2. Feature Extraction & Cleaning\nIn this section I will constuct a dataset with the grain of sessionId, which in turn is just a concatenation of _fullVisitorId_ and _visitId_. The following table gives the names of the features I extract, the type of extraction (numeric or converting to dummy variables), and the source column. You can learn more about the meaning of each column [here](https://support.google.com/analytics/answer/3437719?hl=en).\n\n\n| Feature | Type | Description | Source Column |\n|:------|:------|:---|:---|\n|Month  | Numeric | Month of the visit. Values 1 to 12. | _date_ |\n|Week   | Numeric | Week of the year of the visit. Values 1 to 52. | _date_ |\n|Weekday| Numeric | Day of the week of the visit. Values 1 to 7. | _date_|\n|Hour| Numeric | Hour of day of the start of the visit. Values 0 to 24. | _visitStartTime_|\n|Channel_X| Dummy | Three dummy columns indicating the channel the visit came in from. Values 0 or 1. | _channelGrouping_ |\n|visitNumber| Numeric | The count of the current visit for this user. Values 1 to n. | _vistiNumber_ |\n|Browser_X| Dummy | Dummy columns for each of the major browsers and additional column for \"other\". Values 0 or 1. | _browser_ |\n|Device| Dummy | Three dummy columns indicating type of device. Values 'desktop', 'mobile', 'tablet'. | _deviceCategory_ |\n|OS| Dummy | Dummy columns for each major operating system and additional column for \"other\". Values 0 or 1. |_operatingSystem_|\n|SubContinent| Dummy | Dummy columns for each subcontient, combinig some of the smaller ones. Values 0 or 1. |_subContinent_|\n|pageViews| Numeric | Number of pageviews that are generated by user thus far. Every row in the dataset adds 1 to user count. Values 1 to n. |_pageviews_|\n|hits| Numeric| Superset of user activity count that also includes _pageviews_. Captures district interactions between user and webpage. Values 1 to n. |_hits_|\n|Medium| Dummy | Dummy columns to indicate what type of marketing brought the user to the site. Values 0 or 1.|_medium_|\n\n\nNote that the following columns I did not include in the feature extraction, and the reasons are as follows:\n* _isMobile_ is already encoded as either mobile or tablet, so there is little need for a separate column.\n* _sessionId_ is nothing more than a concatenation of _fullVisitorId_ and _visitId_\n* _Year_ - there is only 12 months worth of data in both train and test datasets, so Month and Week are plenty to capture the timeframe.\n* _city_ has almost 650 unique values, which is too many to encode as dummy variables. In the Feature engineering part I'll see what can be done with this information. \n* _continent_ is a superset of _subContinent_ which I'm using as the base location variable. So, _continent_ is redundant.\n* _country_ likely provides a good amount of information about the demographic of the user, but I'll tackle than in feature engineering section.\n* _metro_ is rarely filled in and is highly correlated with _city_, so it's a good candidate to drop entirely.\n* _networkDomain_ could be interesting from feature engineering perspective, but I'll drop it for now.\n* _region_ can be through of as a \"state\" or \"province\", but I will again leave that for feature engineering.\n* _adNetwork_ only appears to be filled in when _adContent_ is filled in. Since the vast majority of _adNetwork_ values are \"Google Search\", this becomes a redundant feature.\n* _gclId_ seems to be an internal ID used by google for tracking purposes. I don't think it will be of any use for modeling.\n* _page_ onlye appears to be filled in when _adContent_ is filled in. Vast majority of values are 1 (i.e. ad appeared on page 1). I will drop the column for now.\n* _slot_ means either an ad on top of the screen or RHS (right hand side) ad. I'll drop it for now.\n* _keyword_ may be useful in the feature engineering, but in its raw form it's too cumbersome to include.\n* _referralPath_ requires some digging and feature engineering, but it will probably be a useful column to explore.\n* _source_ same as above - requires some digging and feature engineering.\n\n\nPhew, that took a while to writeup. Hopefully, it'll save you some work and you would have learned something in the process. Let's get \"extracting\"! I will use the existing dataframe as the base for pulling out the features listed in the table above. I will not drop the columns that would not be used for model training (e.g. date), but I'll add comented out code at the bottom of this Kernel to remove them if you wish.\n\nStart with the date fields that are relatively straightforward."},{"metadata":{"trusted":true,"_uuid":"f4055dd82d5d7182db058125e707667288656838"},"cell_type":"code","source":"# Get the month value from date\nraw_train['Month'] = raw_train['date'].dt.month\n\n# Get the week value from date\nraw_train['Week'] = raw_train['date'].dt.week\n\n# Get the weekday value from date\nraw_train['Weekday'] = raw_train['date'].dt.weekday\n\n# Get the hour value from visitStartTime\nraw_train['Hour'] = pd.to_datetime(raw_train['visitStartTime'], unit='s').dt.hour","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0983df26e03364018920bf88303aef0f745b121d"},"cell_type":"markdown","source":"Next, let's dummify the _challenGrouping_ and _device_ variables. This is a grouping of the sources of web traffic that lead to the GStore and the device that the user is on. The dummifying operation is pretty straightforward here."},{"metadata":{"trusted":true,"_uuid":"2f5f2786a5e357e6b37cba28ea9199af4d2fbfe7"},"cell_type":"code","source":"# Dummify challenGrouping into 8 separate binary columns\nraw_train = pd.get_dummies(raw_train, columns = ['channelGrouping', 'deviceCategory'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7f12aa574a8ac20a6965dfac36d509f00b6f78a8"},"cell_type":"markdown","source":"Who would have though that there are so many different browsers out there. I mean, have you ever heard of [Puffin](https://www.puffinbrowser.com/) or [Lunascape](https://www.lunascape.tv/)? For the purposes of training a model, I believe it will suffice to lump all of the little known browsers into one bucket called \"Other\" and call it a day. The major browsers I leave in their own dummy columns are Chrome, Safari, Firefox, IE, Edge, Android, Safari, Opera, UC Browser (marker for Asian market), Coc coc (marker for Vietnameese market)."},{"metadata":{"trusted":true,"_uuid":"62f0bd12636f09dcbaf8dd29ec489a40783fc3d8"},"cell_type":"code","source":"# Group all little known broswers into \"Other\" bucket\nraw_train.loc[~raw_train['browser'].isin(['Chrome', 'Safari', 'Firefox', 'Internet Explorer', \n                                          'Edge', 'Android Webview', 'Safari (in-app)', 'Opera Mini', \n                                          'Opera', 'UC Browser', 'Coc Coc']), ['browser']] = 'Other'\n\n# Dummify browser into separage binary columns\nraw_train = pd.get_dummies(raw_train, columns = ['browser'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3fcb99ef1f1bcae0fa8d5ededbf9fc6fb6f7be23"},"cell_type":"markdown","source":"For operating system, again I will take just the top 7 values and set the rest to \"Other\"."},{"metadata":{"trusted":true,"_uuid":"07ba53b4545b0b75a326793697f0382030922fa4"},"cell_type":"code","source":"# Group all less common operating systems into \"Other\" bucket, including where it's (not set)\nraw_train.loc[~raw_train['operatingSystem'].isin(['Windows', 'Macintosh', 'Android', 'iOS', \n                                                   'Linux', 'Chrome OS', 'Windows Phone']), ['operatingSystem']] = 'Other'\n\n# Dummify operatingSytem into separate binary columns\nraw_train = pd.get_dummies(raw_train, columns = ['operatingSystem'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"449c1955bad65d6f9531ee269cf6da9e54b43b7b"},"cell_type":"markdown","source":"Similarly, I'll combine Polynesia, Micronesia, Melanesia into \"Other\", cutting down on a few unnecessary dummy columns."},{"metadata":{"trusted":true,"_uuid":"600fa86da3910f6ce146e1dd59374797ffb3e72c"},"cell_type":"code","source":"# Group all less populated parts of the world into \"Other\" bucket, including where it's (not set)\nraw_train.loc[raw_train['subContinent'].isin(['Polynesia', 'Micronesian Region', \n                                              'Melanesia', '(not set)']), ['subContinent']] = 'Other'\n\n# Dummify subContinent into separate binary columns\nraw_train = pd.get_dummies(raw_train, columns = ['subContinent'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1bf1fa47e8573cd0bfbc8b0b12ba09a6a6b4e184"},"cell_type":"markdown","source":"Finally, let's look at the marketing medium. Here we have values like CPM (cost per thousand impressions), CPC (cost per click), affiliate (from affiliate site), referral (more targeted link (e.g. share)), organic (user finds page themselves). I will turn each of these into dummy variables and combine (not set) and (none) into \"Other\" bucket."},{"metadata":{"trusted":true,"_uuid":"2048db3dd0a6fc185f8a6bebf530101a930fddec"},"cell_type":"code","source":"# Group unknown marketing mediums into \"Other\" bucket\nraw_train.loc[raw_train['medium'].isin(['(not set)', '(none)']), ['medium']] = 'Other'\n\n# Dummify operatingSytem into separate binary columns\nraw_train = pd.get_dummies(raw_train, columns = ['medium'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b1404560e00f6e78e21cbb42ce8321b8e3ec3028"},"cell_type":"markdown","source":"With that the dataset grew quite a bit in its width: from 30 columns to 85. However, remember that there are some columns I won't be using at this time (they require further engineering). So, I'll leave you with the code that will drop these columns from the dataframe. That way there is a clean dataset to plug and plan into the model. Of course, normalization will need to be done on the variables and the log transform on the target variable"},{"metadata":{"trusted":true,"_uuid":"d6e8ee68ff2257ac494b18206298256a5429a433"},"cell_type":"code","source":"print('Number of columns: {:}'.format(raw_train.shape[1]))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b9459be9671bbf8d4e4b1bdf6b306e619ddfc365"},"cell_type":"code","source":"# Drop columns that will not be used at this point in time\nraw_train.drop(['date', 'isMobile', 'sessionId', 'visitStartTime', \n                'city', 'continent', 'country', 'metro', 'networkDomain', \n                'region', 'adContent', 'adNetworkType', 'gclId', 'page', \n                'slot', 'keyword', 'referralPath', 'source'], axis=1, inplace = True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4950f4b3f57918a4f2e1c4a6dff7faf0eaeb8951"},"cell_type":"markdown","source":"### 3. Feature Engineering - Work in Progress...\nIn this section I will derive more advanced features such as the following:\n1. Hours/minutes/seconds since last visit\n2. Combine continent and country into a useful set of dummy variables\n3. Group adContnet into useful categories \n4. Further explore keyword, medium, referral path, and source"}],"metadata":{"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.6.1"}},"nbformat":4,"nbformat_minor":1}