{"cells":[{"metadata":{"_uuid":"cbcab4545c7d7f37bf5b6d74a5191a8d0036db83"},"cell_type":"markdown","source":"# INTRO\n\n* I am currently working through this notebook.  The below is a basic trasnformation of the raw train.csv / test.csv files into a Machine Learning algorithm-friendly format (i.e. ints / floats only)\n\n* This data set is unique in that several of the columns including the TARGET are stored in a column of columns (stored via JSON)\n\n* I hope you find this helpful / interesting.  If you have any feedback please share in the comments or email me at jack.s.mengel@gmail.com\n\n* My next step is to try and improve my score with advanced feature engineering!"},{"metadata":{"_uuid":"d0d54300044502e4a16f3fc623ce5fc658eb4d4d"},"cell_type":"markdown","source":"# IMPORT LIBRARIES\n* Used in this notebook: pandas / sklearn\n* Have other libraries ready in case these are needed in upcoming EDA"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\n\nimport pandas as pd\npd.set_option('display.max_columns', None)\npd.set_option('display.max_rows', None)\nimport warnings\nwarnings.filterwarnings('ignore')\n\nfrom pandas.io.json import json_normalize\n\nimport json\n\nimport matplotlib.pyplot as plt\n%matplotlib inline\nplt.style.use('fivethirtyeight')\n\nfrom sklearn.preprocessing import Imputer\n\nfrom sklearn import preprocessing\n\nimport seaborn as sns\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.ensemble import RandomForestRegressor","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"96e428eb117458c03af01ccd699146436f3f3ff5"},"cell_type":"markdown","source":"# CREATE TRAIN / TEST DataFrames\n* There are only two files in this data set: train / test.  \n\n# IMPORTANT!! NEED TO CONVERT fullVisitorId to a string -> leading 0's get shaved off otherwise!  Learned this the hard way."},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"train = pd.read_csv('../input/train.csv',dtype={'fullVisitorId': 'str'})\ntest = pd.read_csv('../input/test.csv',dtype={'fullVisitorId': 'str'})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"51d92af900a2e47ed28c7fefc1aa35daade6dbf5"},"cell_type":"markdown","source":"# FLATTEN JSON COLUMNS\n\n* You will see below a few columns (i.e. device) represent additional columns!\n* Need to \"flatten\" this using json.loads"},{"metadata":{"trusted":true,"_uuid":"b0e5e04e5237a04b324cbfb81872f062a17a7ba4"},"cell_type":"code","source":"print(train.head())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f0f227f9a994b69b614045f6b6ecea97179d35e3"},"cell_type":"code","source":"train_device = pd.DataFrame(list(train.device.apply(json.loads)))\ntrain_geoNetwork = pd.DataFrame(list(train.geoNetwork.apply(json.loads)))\ntrain_totals = pd.DataFrame(list(train.totals.apply(json.loads)))\ntrain_trafficSource = pd.DataFrame(list(train.trafficSource.apply(json.loads)))\ntrain_trafficSource_adwordsClickInfo = pd.DataFrame(list(train_trafficSource['adwordsClickInfo'].apply(json.dumps).apply(json.loads)))\n\ntest_device = pd.DataFrame(list(test.device.apply(json.loads)))\ntest_geoNetwork = pd.DataFrame(list(test.geoNetwork.apply(json.loads)))\ntest_totals = pd.DataFrame(list(test.totals.apply(json.loads)))\ntest_trafficSource = pd.DataFrame(list(test.trafficSource.apply(json.loads)))\ntest_trafficSource_adwordsClickInfo = pd.DataFrame(list(test_trafficSource['adwordsClickInfo'].apply(json.dumps).apply(json.loads)))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0376eba3edf5217c404930268e9b90fd90b05cfc"},"cell_type":"markdown","source":"# ADD FLATTENED COLUMNS BACK TO TRAIN / TEST\n* Need to then bring these flattened columns back into the original train / test files and delete the columns you original JSON-formatted columns\n* NOTE: Some columns have no actual data -> deleting these as well\n* Given these are large-ish files, important to delete unnecessary objects / dataframes along the way here.  RAM gets full if you do not do so"},{"metadata":{"trusted":true,"_uuid":"dee156613506b3bed37c16974d88b80f40ba85cd"},"cell_type":"code","source":"train = pd.concat(\n    [\n        train[\n            [\n                'channelGrouping',\n                'date',\n                'fullVisitorId',\n                'sessionId',\n                'socialEngagementType',\n                'visitId',\n                'visitNumber',\n                'visitStartTime'\n            ]\n        ],\n        train_device,\n        train_geoNetwork,\n        train_totals,\n        train_trafficSource,\n        train_trafficSource_adwordsClickInfo\n    ],\n    axis = 1\n)\n\n# columns with no data or are JSON columns which have been flattened\ntrain = train.drop(\n    [\n        'socialEngagementType',\n        'browserSize',\n        'browserVersion',\n        'flashVersion',\n        'mobileDeviceBranding',\n        'mobileDeviceInfo',\n        'mobileDeviceMarketingName',\n        'mobileDeviceModel',\n        'mobileInputSelector',\n        'operatingSystemVersion',\n        'screenColors',\n        'screenResolution',\n        'cityId',\n        'latitude',\n        'longitude',\n        'networkLocation',\n        'adNetworkType',\n        'criteriaParameters',\n        'gclId',\n        'isVideoAd',\n        'page',\n        'slot',\n        'targetingCriteria',\n        'adwordsClickInfo'\n    ],\n    axis = 1\n)\n\ntest = pd.concat(\n    [\n        test[[\n                'channelGrouping',\n                'date',\n                'fullVisitorId',\n                'sessionId',\n                'socialEngagementType',\n                'visitId',\n                'visitNumber',\n                'visitStartTime'\n        ]],\n        test_device,\n        test_geoNetwork,\n        test_totals,\n        test_trafficSource,\n        test_trafficSource_adwordsClickInfo\n    ], \n    axis = 1\n)\n\n# columns with no data or are JSON columns which have been flattened\ntest = test.drop(\n    [\n        'socialEngagementType',\n        'browserSize',\n        'browserVersion',\n        'flashVersion',\n        'mobileDeviceBranding',\n        'mobileDeviceInfo',\n        'mobileDeviceMarketingName',\n        'mobileDeviceModel',\n        'mobileInputSelector',\n        'operatingSystemVersion',\n        'screenColors',\n        'screenResolution',\n        'cityId',\n        'latitude',\n        'longitude',\n        'networkLocation',\n        'adNetworkType',\n        'criteriaParameters',\n        'gclId',\n        'isVideoAd',\n        'page',\n        'slot',\n        'targetingCriteria',\n        'adwordsClickInfo'\n    ],\n    axis = 1\n)\n\ndel train_device\ndel train_geoNetwork\ndel train_totals\ndel train_trafficSource\ndel train_trafficSource_adwordsClickInfo\ndel test_device\ndel test_geoNetwork\ndel test_totals\ndel test_trafficSource\ndel test_trafficSource_adwordsClickInfo","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6b138d3d636f14201766defa7d0217f009b8622d"},"cell_type":"markdown","source":"# Let's convert text columns to numeric\n# keep as objects: sessionID\n# object columns that are obviously just numbers and should be converted straight up to ints: \n* visitStartTime\n* visitId\n* transactionRevenue\n* visitNumber\n* hits\n* pageviews"},{"metadata":{"trusted":true,"_uuid":"5cfa3beedd8a4ae461ee07db65ef2614692784f2","scrolled":false},"cell_type":"code","source":"train['visitStartTime'].astype(str).astype(int)\ntrain['visitId'].astype(str).astype(int)\ntrain['transactionRevenue'].fillna(value = '0', inplace = True)\ntrain['transactionRevenue'] = train['transactionRevenue'].astype(int)\ntrain['visitNumber'].astype(str).astype(int)\ntrain['hits'] = train['hits'].astype(int)\ntrain['pageviews'].fillna(value = '0', inplace = True)\ntrain['pageviews'] = train['pageviews'].astype(int)\n\ntest['visitStartTime'].astype(str).astype(int)\ntest['visitId'].astype(str).astype(int)\ntest['visitNumber'].astype(str).astype(int)\ntest['hits'] = test['hits'].astype(int)\ntest['pageviews'].fillna(value = '0', inplace = True)\ntest['pageviews'] = test['pageviews'].astype(int)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9712dd19deab6e64894447999fdf59f5e0255708"},"cell_type":"markdown","source":"# Next, find columns with large amounts of unique values..."},{"metadata":{"trusted":true,"_uuid":"787c9ab1a495ff55566fdde70ff6e717df98f4ed"},"cell_type":"code","source":"unique_vals = train.nunique().sort_values(ascending = False)\nunique_vals = unique_vals.to_frame()\nunique_vals = unique_vals.reset_index()\nunique_vals.columns = ['column', 'cnt']\n\ndtypes = train.dtypes.to_frame()\ndtypes = dtypes.reset_index()\ndtypes.columns = ['column', 'type']\n\nprofile = pd.merge(\n    unique_vals,\n    dtypes,\n    on = 'column',\n    how = 'inner'\n)\n\nprint(profile.loc[profile['type'] == 'object'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"530c3dc5f195513f989e9811ceca39cc68151793"},"cell_type":"markdown","source":" # ...and label encode them.  Columns to label encode:\n* networkDomain          28064\n* gclId                  17774\n* keyword                 3659\n* referralPath            1475\n* city                     649\n* visitNumber              384\n* source                   380\n* region                   376\n* date                     366\n* hits                     274\n* country                  222\n* pageviews                214\n* metro                     94\n* browser                   54\n* adContent                 44\n* subContinent              23\n* operatingSystem           20\n* campaign 10"},{"metadata":{"trusted":true,"_uuid":"268eb337ff890086a04543dbacec912fcdda0fde"},"cell_type":"code","source":"networkDomain_encoder =  preprocessing.LabelEncoder()\nkeyword_encoder =  preprocessing.LabelEncoder()\nreferralPath_encoder =  preprocessing.LabelEncoder()\ncity_encoder =  preprocessing.LabelEncoder()\nvisitNumber_encoder =  preprocessing.LabelEncoder()\nsource_encoder =  preprocessing.LabelEncoder()\nregion_encoder =  preprocessing.LabelEncoder()\ndate_encoder =  preprocessing.LabelEncoder()\ncountry_encoder =  preprocessing.LabelEncoder()\nmetro_encoder =  preprocessing.LabelEncoder()\nbrowser_encoder =  preprocessing.LabelEncoder()\nadContent_encoder =  preprocessing.LabelEncoder()\nsubContinent_encoder =  preprocessing.LabelEncoder()\noperatingSystem_encoder =  preprocessing.LabelEncoder()\ncampaign_encoder =  preprocessing.LabelEncoder()\n\nnetworkDomain_encoder.fit(train['networkDomain'])\ntrain['keyword'].fillna(value = '0', inplace = True)\nkeyword_encoder.fit(train['keyword'])\ntrain['referralPath'].fillna(value = '0', inplace = True)\nreferralPath_encoder.fit(train['referralPath'])\ncity_encoder.fit(train['city'])\nvisitNumber_encoder.fit(train['visitNumber'])\nsource_encoder.fit(train['source'])\nregion_encoder.fit(train['region'])\ndate_encoder.fit(train['date'])\ncountry_encoder.fit(train['country'])\nmetro_encoder.fit(train['metro'])\nbrowser_encoder.fit(train['browser'])\n\ntrain['adContent'].fillna(value = '0', inplace = True)\nadContent_encoder.fit(train['adContent'])\nsubContinent_encoder.fit(train['subContinent'])\noperatingSystem_encoder.fit(train['operatingSystem'])\ncampaign_encoder.fit(train['campaign'])\n\ntrain['networkDomain_encoder'] = networkDomain_encoder.transform(train['networkDomain'])\ntrain['keyword_encoder'] = keyword_encoder.transform(train['keyword'])\ntrain['referralPath_encoder'] = referralPath_encoder.transform(train['referralPath'])\ntrain['city_encoder'] = city_encoder.transform(train['city'])\ntrain['visitNumber_encoder'] = visitNumber_encoder.transform(train['visitNumber'])\ntrain['source_encoder'] = source_encoder.transform(train['source'])\ntrain['region_encoder'] = region_encoder.transform(train['region'])\ntrain['date_encoder'] = date_encoder.transform(train['date'])\ntrain['country_encoder'] = country_encoder.transform(train['country'])\ntrain['metro_encoder'] = metro_encoder.transform(train['metro'])\ntrain['browser_encoder'] = browser_encoder.transform(train['browser'])\ntrain['adContent_encoder'] = adContent_encoder.transform(train['adContent'])\ntrain['subContinent_encoder'] = subContinent_encoder.transform(train['subContinent'])\ntrain['operatingSystem_encoder'] = operatingSystem_encoder.transform(train['operatingSystem'])\ntrain['campaign_encoder'] = campaign_encoder.transform(train['campaign'])\n\ntest_networkDomain_encoder =  preprocessing.LabelEncoder()\ntest_keyword_encoder =  preprocessing.LabelEncoder()\ntest_referralPath_encoder =  preprocessing.LabelEncoder()\ntest_city_encoder =  preprocessing.LabelEncoder()\ntest_visitNumber_encoder =  preprocessing.LabelEncoder()\ntest_source_encoder =  preprocessing.LabelEncoder()\ntest_region_encoder =  preprocessing.LabelEncoder()\ntest_date_encoder =  preprocessing.LabelEncoder()\ntest_country_encoder =  preprocessing.LabelEncoder()\ntest_metro_encoder =  preprocessing.LabelEncoder()\ntest_browser_encoder =  preprocessing.LabelEncoder()\ntest_adContent_encoder =  preprocessing.LabelEncoder()\ntest_subContinent_encoder =  preprocessing.LabelEncoder()\ntest_operatingSystem_encoder =  preprocessing.LabelEncoder()\ntest_campaign_encoder =  preprocessing.LabelEncoder()\n\ntest['keyword'].fillna(value = '0', inplace = True)\ntest_keyword_encoder.fit(test['keyword'])\n\ntest['referralPath'].fillna(value = '0', inplace = True)\ntest_referralPath_encoder.fit(test['referralPath'])\ntest_city_encoder.fit(test['city'])\ntest_visitNumber_encoder.fit(test['visitNumber'])\ntest_source_encoder.fit(test['source'])\ntest_region_encoder.fit(test['region'])\ntest_date_encoder.fit(test['date'])\ntest_country_encoder.fit(test['country'])\ntest_metro_encoder.fit(test['metro'])\ntest_browser_encoder.fit(test['browser'])\ntest_networkDomain_encoder.fit(test['networkDomain'])\ntest['adContent'].fillna(value = '0', inplace = True)\ntest_adContent_encoder.fit(test['adContent'])\ntest_subContinent_encoder.fit(test['subContinent'])\ntest_operatingSystem_encoder.fit(test['operatingSystem'])\ntest_campaign_encoder.fit(test['campaign'])\n\ntest['networkDomain_encoder'] = test_networkDomain_encoder.transform(test['networkDomain'])\ntest['keyword_encoder'] = test_keyword_encoder.transform(test['keyword'])\ntest['referralPath_encoder'] = test_referralPath_encoder.transform(test['referralPath'])\ntest['city_encoder'] = test_city_encoder.transform(test['city'])\ntest['visitNumber_encoder'] = test_visitNumber_encoder.transform(test['visitNumber'])\ntest['source_encoder'] = test_source_encoder.transform(test['source'])\ntest['region_encoder'] = test_region_encoder.transform(test['region'])\ntest['date_encoder'] = test_date_encoder.transform(test['date'])\ntest['country_encoder'] = test_country_encoder.transform(test['country'])\ntest['metro_encoder'] = test_metro_encoder.transform(test['metro'])\ntest['browser_encoder'] = test_browser_encoder.transform(test['browser'])\ntest['adContent_encoder'] = test_adContent_encoder.transform(test['adContent'])\ntest['subContinent_encoder'] = test_subContinent_encoder.transform(test['subContinent'])\ntest['operatingSystem_encoder'] = test_operatingSystem_encoder.transform(test['operatingSystem'])\ntest['campaign_encoder'] = test_campaign_encoder.transform(test['campaign'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dc9e6c6d7c461d341a88ef7569615cbe28cffbf6"},"cell_type":"markdown","source":"# One-hot encode the rest..."},{"metadata":{"trusted":true,"_uuid":"f002557219b89a4c9dc30e7de579e31e4d75b701"},"cell_type":"code","source":"train_one_hot = train[\n    [\n        'channelGrouping',\n        'deviceCategory',\n        'isMobile',\n        'language',\n        'continent',\n        'medium',\n        'newVisits',\n        'visits',\n        'campaignCode',\n        'isTrueDirect',\n        'bounces'\n    ]\n]\n\ntrain_one_hot = pd.get_dummies(train_one_hot)\n\ntrain = pd.concat(\n    [\n        train,\n        train_one_hot\n    ],\n    axis = 1\n)\n\ntest_one_hot = test[\n    [\n        'channelGrouping',\n        'deviceCategory',\n        'isMobile',\n        'language',\n        'continent',\n        'medium',\n        'newVisits',\n        'visits',\n        'isTrueDirect',\n        'bounces'\n    ]\n]\n\ntest_one_hot = pd.get_dummies(test_one_hot)\n\ntest = pd.concat(\n    [\n        test,\n        test_one_hot\n    ],\n    axis = 1\n)\n\ndel train_one_hot\ndel test_one_hot","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"11d4c3b8a2fbf10ab6aa19263207da488d936c93"},"cell_type":"markdown","source":"# DATE COLUMNS \n* Might as well get meta-data of the dates (weekday / is_month_end etc) and see if this generates a signal"},{"metadata":{"trusted":true,"_uuid":"24feb6706fa88944dd70070cac97112c554d1f6d"},"cell_type":"code","source":"train['date'] = pd.to_datetime(train['date'], format = '%Y%m%d')\ntrain['month'] = pd.DatetimeIndex(train['date']).month\ntrain['year'] = pd.DatetimeIndex(train['date']).year\ntrain['day'] = pd.DatetimeIndex(train['date']).day\ntrain['quarter'] = pd.DatetimeIndex(train['date']).quarter\ntrain['weekday'] = pd.DatetimeIndex(train['date']).weekday\ntrain['weekofyear'] = pd.DatetimeIndex(train['date']).weekofyear\ntrain['is_month_start'] = pd.DatetimeIndex(train['date']).is_month_start\ntrain['is_month_end'] = pd.DatetimeIndex(train['date']).is_month_end\ntrain['is_quarter_start'] = pd.DatetimeIndex(train['date']).is_quarter_start\ntrain['is_quarter_end'] = pd.DatetimeIndex(train['date']).is_quarter_end\ntrain['is_year_start'] = pd.DatetimeIndex(train['date']).is_year_start\ntrain['is_year_end'] = pd.DatetimeIndex(train['date']).is_year_end\nprint(train[['month','day','year','quarter','weekday','weekofyear','date']].head())\n\ntest['date'] = pd.to_datetime(test['date'], format = '%Y%m%d')\ntest['month'] = pd.DatetimeIndex(test['date']).month\ntest['year'] = pd.DatetimeIndex(test['date']).year\ntest['day'] = pd.DatetimeIndex(test['date']).day\ntest['quarter'] = pd.DatetimeIndex(test['date']).quarter\ntest['weekday'] = pd.DatetimeIndex(test['date']).weekday\ntest['weekofyear'] = pd.DatetimeIndex(test['date']).weekofyear\ntest['is_month_start'] = pd.DatetimeIndex(test['date']).is_month_start\ntest['is_month_end'] = pd.DatetimeIndex(test['date']).is_month_end\ntest['is_quarter_start'] = pd.DatetimeIndex(test['date']).is_quarter_start\ntest['is_quarter_end'] = pd.DatetimeIndex(test['date']).is_quarter_end\ntest['is_year_start'] = pd.DatetimeIndex(test['date']).is_year_start\ntest['is_year_end'] = pd.DatetimeIndex(test['date']).is_year_end\nprint(test[['month','day','year','quarter','weekday','weekofyear','date']].head())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3719706383ea0f7ee58f13926bf920c2001f7b98"},"cell_type":"markdown","source":"# TIME COLUMNS\n* Same as date columns!"},{"metadata":{"trusted":true,"_uuid":"45d872f3854e1c3b7e097a72db13b78a9872161f"},"cell_type":"code","source":"train['visitStartTime'] = pd.to_datetime(train['visitStartTime'], unit = 's')\ntrain['hour'] = pd.DatetimeIndex(train['visitStartTime']).hour\ntrain['minute'] = pd.DatetimeIndex(train['visitStartTime']).minute\nprint(train[['visitStartTime','hour','minute']].head())\n\ntest['visitStartTime'] = pd.to_datetime(test['visitStartTime'], unit = 's')\ntest['hour'] = pd.DatetimeIndex(test['visitStartTime']).hour\ntest['minute'] = pd.DatetimeIndex(test['visitStartTime']).minute\nprint(test[['visitStartTime','hour','minute']].head())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0f4c891070559a2d9f7c5f091e1e4ccee747c91a"},"cell_type":"markdown","source":"# REMOVE ALL UNNECESSARY COLUMNS\n* Store in train_staging and test_staging"},{"metadata":{"trusted":true,"_uuid":"1e148d3e1241feef4688533f96997929b0e1d9d4","scrolled":false},"cell_type":"code","source":"train_staging = train.select_dtypes(exclude = 'object')\ntrain_staging = train_staging.select_dtypes(exclude = 'datetime')\ntrain_staging = train_staging.select_dtypes(exclude = 'bool')\n\nprint(train_staging.dtypes)\n\ntest_staging = test.select_dtypes(exclude = 'object')\ntest_staging = test_staging.select_dtypes(exclude = 'datetime')\ntest_staging = test_staging.select_dtypes(exclude = 'bool')\n\nprint(test_staging.dtypes)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"319b16895cc8638c78be0bbb942bd627da21883a"},"cell_type":"markdown","source":"# FILL NANS\n* Need to fill the NaN values for the Machine Learning algorithm!\n* Using Imputer from sci-kit learn"},{"metadata":{"trusted":true,"_uuid":"d90ac5120f35972a63a113e519ca4bd14a985f6e"},"cell_type":"code","source":"train_staging_columns = train_staging.columns\n\nfrom sklearn.preprocessing import Imputer\nimputer = Imputer(strategy = 'mean')\ntrain_staging = imputer.fit_transform(train_staging)\ntrain_staging = pd.DataFrame(\n    data = train_staging,\n    columns = train_staging_columns\n)\nprint(train_staging.isna().any())\n\ntest_staging_columns = test_staging.columns\n\ntest_staging = imputer.fit_transform(test_staging)\ntest_staging = pd.DataFrame(\n    data = test_staging,\n    columns = test_staging_columns\n)\nprint(test_staging.isna().any())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6755ce3cc19a98f1b11025070190b696754d2a04"},"cell_type":"markdown","source":"# ALIGN TEST / TRAIN"},{"metadata":{"trusted":true,"_uuid":"04dd88bae561a252e0037c6fc288cfbb3e4b9977"},"cell_type":"code","source":"train_staging, test_staging = train_staging.align(test_staging, join = 'inner', axis = 1)\ntrain_staging['transactionRevenue'] = train['transactionRevenue']\ntest_staging['fullVisitorId'] = test['fullVisitorId']\ntrain_staging['fullVisitorId'] = train['fullVisitorId']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d654a785c8a43b46f751ef25a1058505e0a63264"},"cell_type":"code","source":"print(train_staging.head())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"007fc2dfba93362782af221bf26eba1146d722e4"},"cell_type":"markdown","source":"# AGGREGATE fullVisitorId SESSIONS\n\n* The competition calls for the resulting predictions to be on a customer level, not a transaction level\n* Therefore need to aggregate train / test data on a customer level for training of model\n* Below I am using groupby on fullVisitorId, then using a handful of metrics to aggregate their sessions for EACH COLUMN"},{"metadata":{"trusted":true,"_uuid":"cc42322f10c46ca67c8410041aabba7433a4358c"},"cell_type":"code","source":"train_agg = train_staging \\\n    .groupby(['fullVisitorId']) \\\n    .agg(['count','mean','min','max','sum']) \\\n    .reset_index()\n\ntest_agg = test_staging \\\n    .groupby(['fullVisitorId']) \\\n    .agg(['count','mean','min','max','sum']) \\\n    .reset_index()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e99e33795c40c5e0425f43111a5835f61daa3c17"},"cell_type":"markdown","source":"# FLATTEN AGG() OUTPUT\n\n* Unfortunately the agg() method returns a dataframe with a multi-layer index.  We need to flatten this to make it useful\n* Basically just iterate through each column and name it using the column and metric on that column"},{"metadata":{"trusted":true,"_uuid":"2bfef391ca6c4455ee1c2926c9d66a3460c59b91"},"cell_type":"code","source":"columns_train = ['fullVisitorId']\n\n# Convert multi-level index from .agg() into clean columns\n# borrowing from: https://www.kaggle.com/willkoehrsen/introduction-to-manual-feature-engineering\nfor var in train_agg.columns.levels[0]:\n    if var != 'fullVisitorId':\n        for stat in train_agg.columns.levels[1][:-1]:\n            columns_train.append('%s_%s' % (var, stat))\n\ntrain_agg.columns = columns_train\n\ncolumns_test = ['fullVisitorId']\n\n# Convert multi-level index from .agg() into clean columns\n# borrowing from: https://www.kaggle.com/willkoehrsen/introduction-to-manual-feature-engineering\nfor var in test_agg.columns.levels[0]:\n    if var != 'fullVisitorId':\n        for stat in test_agg.columns.levels[1][:-1]:\n            columns_test.append('%s_%s' % (var, stat))\n\ntest_agg.columns = columns_test","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"159a31baf1081360ff72b31df71615775a61bac7"},"cell_type":"markdown","source":"# MORE MEMORY MANAGEMENT\n* Don't need train / test / train_staging / test_staging anymore as we aggregated using these DataFrames"},{"metadata":{"trusted":true,"_uuid":"e92606983855f1703d0dde4cace81e5c40c14b7f"},"cell_type":"code","source":"del train_staging\ndel train\n\ndel test_staging\ndel test","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"63a299ecb73fee4c1ab46c54f7a7954a865b39d5"},"cell_type":"markdown","source":"# NATURAL LOG\n* The competition calls for the TARGET to be the natural log of the actual amount spent\n* Using math library to convert the train data into natural log of itself"},{"metadata":{"trusted":true,"_uuid":"db9b59295510c35514a9c18f6d6067cbf1170268"},"cell_type":"code","source":"print(train_agg.dtypes)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0c88350f0fbc70a709264a281f9db76898b95e4f"},"cell_type":"code","source":"import math\n\ndef create_target(rev):\n    if rev == 0:\n        return 0\n    else:\n        return math.log(rev)\n\ntrain_agg['TARGET'] = train_agg['transactionRevenue_sum'].apply(create_target)\n\ntrain_agg = train_agg.drop(\n    [\n        'transactionRevenue_count',\n        'transactionRevenue_mean',\n        'transactionRevenue_min',\n        'transactionRevenue_max',\n        'transactionRevenue_sum'\n    ],\n    axis = 1\n)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"81d3bdb268cfd2a167be8ad008ee390171f48ae2"},"cell_type":"markdown","source":"# CORRELATION CHECK\n* Now that we've gotten the data all cleaned up, let's see what kind of signal is in this data set out of the box!\n* You will see pageviews / hits are strongest correlations to the revenue the customer spends.  \n* Intuitively this makes sense: if they click around the site more, it's more likely it will end up in a transaction"},{"metadata":{"trusted":true,"_uuid":"fab1e4ecd276f97a117a862ddbcfbcc5dd75e335"},"cell_type":"code","source":"train_agg_corr = train_agg.corr()\nprint(train_agg_corr['TARGET'].sort_values(ascending = False))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8ffebedc54c3669f32b0c0bb92c0b5f6b3faafde"},"cell_type":"markdown","source":"# TRAIN RANDOM FOREST MODEL\n\n* Using all features to inform the model"},{"metadata":{"trusted":true,"_uuid":"8f5472e79e102919641757ff8086d0d643ff915c"},"cell_type":"code","source":"from sklearn.metrics import mean_squared_error\nfrom math import sqrt\n\nid_train = train_agg['fullVisitorId']\nx = train_agg.drop(['TARGET','fullVisitorId'], axis = 1)\ny = train_agg['TARGET']\n\nx_train, x_test, y_train, y_test = train_test_split(x, y, test_size = 0.33, random_state = 0)\n\nmodel = RandomForestRegressor()\nmodel.fit(x_train, y_train)\npredictions = model.predict(x_test)\n\nrms = sqrt(mean_squared_error(y_test, predictions))\n\nprint('RMSE train:', rms)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bb4062652a59ddd8e97381f686f93c884ed4ac66"},"cell_type":"code","source":"importances = model.feature_importances_\nimportances_df = pd.DataFrame(\n    data = {'column' : x.columns, 'importance' : importances}\n)\n\nimportances_df = importances_df.sort_values(by = 'importance', ascending = False)\n\nimportances_df['weighted'] = importances_df['importance'] / importances_df['importance'].sum()\n\nplt.figure()\nplt.title('Feature Importances')\nplt.barh(\n    importances_df['column'].head(15),\n    importances_df['weighted'].head(15)\n)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2a58ccea4b1e2f06ad3284dcb1186d4e18a33eb1"},"cell_type":"markdown","source":"# DELETE UNNECESSARY OBJECTS"},{"metadata":{"trusted":true,"_uuid":"f80f0a975d1db3c3921bbbb323497c1cdfc4c520"},"cell_type":"code","source":"del train_agg\ndel Imputer\ndel RandomForestRegressor\ndel adContent_encoder\ndel auc\ndel browser_encoder\ndel campaign_encoder \ndel city_encoder \ndel columns_test \ndel columns_train\ndel country_encoder, create_target, date_encoder, imputer, json, json_normalize, keyword_encoder, math, metro_encoder, networkDomain_encoder, operatingSystem_encoder, parameters, plt, preprocessing\ndel referralPath_encoder\ndel region_encoder\ndel sns\ndel source_encoder\ndel stat\ndel subContinent_encoder\ndel test_adContent_encoder\ndel test_browser_encoder\ndel test_campaign_encoder\ndel test_city_encoder\ndel test_country_encoder\ndel test_date_encoder\ndel test_keyword_encoder\ndel test_metro_encoder\ndel test_networkDomain_encoder \ndel test_operatingSystem_encoder\ndel test_referralPath_encoder\ndel test_region_encoder\ndel test_source_encoder\ndel test_staging_columns\ndel test_subContinent_encoder\ndel test_visitNumber_encoder\ndel train_staging_columns\ndel train_test_split\ndel var\ndel visitNumber_encoder\ndel warnings","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"16c2a143fdb23c2885b0a3fcfaaef98b97b5157b"},"cell_type":"markdown","source":"# TRAIN LIGHTGBM\n* Did not result in better score, so disabling for now\n* Will continue experimenting with this..."},{"metadata":{"trusted":true,"_uuid":"507dfadccb862fa2cb895ff0291acdd35538cb9d"},"cell_type":"code","source":"#import lightgbm as lightgbm\n#from sklearn.model_selection import train_test_split\n#from math import sqrt\n#from sklearn.metrics import mean_squared_error\n\n#x_train = lightgbm.Dataset(x_train)\n#y_train = lightgbm.Dataset(y_train)\n\n#parameters = {\n#    'num_leaves':31,\n#    'colsample_bytree' : .9,\n#    'metric':'l2_root',\n#    'learning_rate':0.03,\n#    'subsample' : 0.9, \n#    'random_state' : 1,\n#    'n_estimators': 1000\n#}\n\n#lgbm = lightgbm.train(\n#    parameters,\n#    x_train,\n#    y_train\n#)\n\n#p = lgbm.predict(x_test)\n\n#rms = sqrt(mean_squared_error(y_test, p))\n\n#print('LGBM RMSE train:', rms)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7f2b730ce6c5edc5e48aa111890f714a84d0d0b2"},"cell_type":"markdown","source":"# SUBMIT TO COMPETITION"},{"metadata":{"trusted":true,"_uuid":"8beb7e3c3364ffc643d8d4f3e6b066938691b4ce"},"cell_type":"code","source":"predictions_test = model.predict(test_agg.drop(['fullVisitorId'], axis = 1))\n\nsubmission = pd.DataFrame({\n    \"fullVisitorId\": test_agg['fullVisitorId'].astype(str),\n    \"PredictedLogRevenue\": predictions_test\n    })\n\nsubmission['fullVisitorId'] = submission['fullVisitorId'].astype(str)\n\nimport csv\n\nsubmission.to_csv('submission_rf.csv', quoting=csv.QUOTE_NONNUMERIC, index = False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c11286ca3f985e69c45f80229af31b65e2b517db"},"cell_type":"markdown","source":"# AGAIN, COMMENTING OUT LGBM MODEL"},{"metadata":{"trusted":true,"_uuid":"ea2b48d7b05c9295f4b7262483edb47c8a6112ea"},"cell_type":"code","source":"#predictions_test_lgbm = lgbm.predict(test_agg.drop(['fullVisitorId'], axis = 1))\n\n#submission_lgbm = pd.DataFrame({\n#    \"fullVisitorId\": test_agg['fullVisitorId'].astype(str),\n#    \"PredictedLogRevenue\": predictions_test_lgbm\n#    })\n\n#submission_lgbm['fullVisitorId'] = submission_lgbm['fullVisitorId'].astype(str)\n\n#import csv\n\n#submission_lgbm.to_csv('submission_lgbm.csv', quoting=csv.QUOTE_NONNUMERIC, index = False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ace1d3e81b9244f7a0a0caf235ac90f52f8ed4b5"},"cell_type":"markdown","source":"# THANKS!!"}],"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}