{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"scrolled":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\"))\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","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"import matplotlib.pyplot as plt\nimport seaborn as sns\nimport math\n\nfrom sklearn import preprocessing\nfrom sklearn.preprocessing import LabelEncoder\nfrom lightgbm.sklearn import LGBMRegressor\nimport lightgbm as lgb \nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import mean_squared_error\n\nimport gc\ngc.enable()\n\n#json requirements\nimport json\nfrom pandas.io.json import json_normalize\n\nfrom plotly.offline import init_notebook_mode, iplot\nimport plotly.graph_objs as go\n\nimport warnings\nwarnings.filterwarnings(\"ignore\")\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b3ef0f56c28de776097bfee272b6f7874f960a82"},"cell_type":"markdown","source":"## Load dataset"},{"metadata":{"trusted":true,"_uuid":"dd9214b0b3fc998d9c8b0d3ece5f451667ca95d7"},"cell_type":"code","source":"def load_df(csv_path='../input/train.csv', nrows=None):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    \n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'}, # Important!!\n                     nrows=nrows)\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    print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n    return df\n\ntrain = load_df(\"../input/train.csv\")\ntest = load_df(\"../input/test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"014ceadbb9bca0bd960293346567785866bc4bd6"},"cell_type":"code","source":"train[:3]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"baf080ccd85b3ad6eba671eccc59ebdf537467f1"},"cell_type":"markdown","source":"## Investigating target variable"},{"metadata":{"trusted":true,"_uuid":"92634bd06b9b0364e930d4f2bc0a0e736680dc83"},"cell_type":"code","source":"#target variable\n#Since we are predicting the natural log of sum of all transactions of the user\n#let us sum up the transaction revenue at user level and take a log and then do a scatter plot.\ntrain[\"totals.transactionRevenue\"] = train[\"totals.transactionRevenue\"].astype('float')\ngdf = train.groupby(\"fullVisitorId\")[\"totals.transactionRevenue\"].sum().reset_index()\n\nplt.figure(figsize=(8,6))\nplt.scatter(range(gdf.shape[0]), np.sort(np.log1p(gdf[\"totals.transactionRevenue\"].values)))\nplt.xlabel('index', fontsize=12) \nplt.ylabel('TransactionRevenue', fontsize=12)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"079ea96c078fafba8fd4de8d57900098aed143dd"},"cell_type":"markdown","source":"This proves: The 80/20 rule has proven true for many businesses–only a small percentage of customers produce most of the revenue. As such, marketing teams are challenged to make appropriate investments in promotional strategies."},{"metadata":{"trusted":true,"_uuid":"00a447ff6adb7f7b053cb00eee0bcddc7ed9f20c"},"cell_type":"code","source":"#transforming target column\ntrain['totals.transactionRevenue'] = train['totals.transactionRevenue'].fillna(0)\ntrain['totals.transactionRevenue'] = np.log1p(train['totals.transactionRevenue'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9e3ce958899f4504abc42608d026cfe6f31238d3"},"cell_type":"code","source":"train[:3]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0a89ee1b8410dd14669a35cbc8695f49237ec6d2"},"cell_type":"markdown","source":"## Dimension Reduction (heuristic)"},{"metadata":{"_uuid":"18212050c613207ef058aceb7e19c5009f23d5aa"},"cell_type":"markdown","source":"### Drop constant columns and Session ID"},{"metadata":{"trusted":true,"_uuid":"dc150d5013680bbdb34c0447e2e33048dfc70d31","scrolled":true},"cell_type":"code","source":"const_cols = [c for c in train.columns if train[c].nunique(dropna=False)==1 ]\nconst_cols\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cd73ceb9cdc8cc05c92201d511492bfae6d6a9dd"},"cell_type":"code","source":"cols_to_drop = const_cols  #drop constant columns\n\ntrain = train.drop(cols_to_drop , axis=1)\ntest = test.drop(cols_to_drop, axis=1)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ffbf437b1220c9345542d507ed8320b05cc7e82d"},"cell_type":"markdown","source":"### Check if any any extra columns present in test or train"},{"metadata":{"trusted":true,"_uuid":"18e39c5212e9bdeffb4c98aaf65314bac96f5323"},"cell_type":"code","source":"(set(train.columns).difference(set(test.columns))) #variable which are not common in both test and train","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"224aacc6a6627ecd62415754f89afcb5fa74517f"},"cell_type":"code","source":"train = train.drop(['trafficSource.campaignCode'] , axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0ba067ba0014bf9bf8406644841d55e1d6ff056e"},"cell_type":"code","source":"train.shape #(903653, 34)\ntest.shape #(804684, 33)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4c97539fac0bb4ae7f13b32e6a3c86cea1926894"},"cell_type":"markdown","source":"### Check null data- drop columns with nulls>70%"},{"metadata":{"trusted":true,"_uuid":"6ee5cbbcf6638098c89ac74febb7454ce89cc26e"},"cell_type":"code","source":"train.head()\n\npercent = (100 * train.isnull().sum() / train.shape[0]).sort_values(ascending=False)\n\npercent[:20]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0280c4c85e1288bdcd2241700f4397071da6ec50"},"cell_type":"code","source":"percent = (100 * test.isnull().sum() / test.shape[0]).sort_values(ascending=False)\npercent[:10]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"76ab5e8e6ae4483b4d256b98c6a5d2fb74c0d3fc"},"cell_type":"code","source":"drop_cols_na = ['trafficSource.adContent', 'trafficSource.adwordsClickInfo.slot', 'trafficSource.adwordsClickInfo.page', 'trafficSource.adwordsClickInfo.isVideoAd',\n            'trafficSource.adwordsClickInfo.adNetworkType', 'trafficSource.adwordsClickInfo.gclId','trafficSource.referralPath']\n\ntrain.drop(drop_cols_na, axis=1, inplace=True)\ntest.drop(drop_cols_na, axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8cb5627052bac0bc3f482dd9cdde6e2b6ca31302"},"cell_type":"code","source":"print(train.shape)\nprint(test.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a787803124b1a59bc97257c643ae5bb76899a196"},"cell_type":"code","source":"test.info()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"28fdf382c254133f616fe59634c039e40c145e55"},"cell_type":"markdown","source":"## Null Value Treatment"},{"metadata":{"_uuid":"7dd90f778afc0575068029bd956d3017093d08d1"},"cell_type":"markdown","source":"### Identifying columns with nulls to be treated"},{"metadata":{"trusted":true,"_uuid":"5b35a465e1b6aff5aa3f99dbae55cc81a18a5189"},"cell_type":"code","source":"def find_missing(data):\n    # number of missing values\n    count_missing = data.isnull().sum().values\n    # total records\n    total = data.shape[0]\n    # percentage of missing\n    ratio_missing = count_missing/total\n    # return a dataframe to show: feature name, # of missing and % of missing\n    return pd.DataFrame(data={'missing_count':count_missing, 'missing_ratio':ratio_missing}, index=data.columns.values)\ntrain_missing = find_missing(train)\ntest_missing = find_missing(test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fa041d9094694edeb22c9a512b28527415a26a83"},"cell_type":"code","source":"train_missing.reset_index()[['index', 'missing_ratio']]\\\n    .merge(test_missing.reset_index()[['index', 'missing_ratio']], on='index', how='left')\\\n    .rename(columns={'index':'columns', 'missing_ratio_x':'train_missing_ratio', 'missing_ratio_y':'test_missing_ratio'})\\\n    .sort_values(['train_missing_ratio', 'test_missing_ratio'], ascending=False)\\\n    .query('train_missing_ratio>0')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a9b9dcd007ad982111233d07795dac5275fc22b6"},"cell_type":"code","source":"miss_per = {}\nfor k, v in dict(train.isna().sum(axis=0)).items():\n    if v == 0:\n        continue\n    miss_per[k] = 100 * float(v) / len(train)\n    \nimport operator \nsorted_x = sorted(miss_per.items(), key=operator.itemgetter(1), reverse=True)\nprint (\"There are \" + str(len(miss_per)) + \" columns with missing values\")\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"52be0a94186b23e1a6ee84d697ed2bc08751c4f6"},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"75219d8b4897032cc7513a83ab59426357601ef0"},"cell_type":"markdown","source":"### Treating values in each column"},{"metadata":{"_uuid":"2a1436379789f292a0600415a057662b2c2f9488"},"cell_type":"markdown","source":"#### trafficSource.isTrueDirect"},{"metadata":{"trusted":true,"_uuid":"92bc3b9a949f420d9a746c1e402b248ed96265d4"},"cell_type":"code","source":"# assuming missing values mean it is False, we fill NAs with false\ntrain['trafficSource.isTrueDirect'].fillna(False, inplace=True)\ntest['trafficSource.isTrueDirect'].fillna(False, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9b2fced538f3179d06b26f78e996249714a738a4"},"cell_type":"markdown","source":"#### trafficSource.keyword"},{"metadata":{"trusted":true,"_uuid":"02b6966277ec5ede10c81ec5867fdcb91deb65c9"},"cell_type":"code","source":"train['trafficSource.keyword'].fillna('nan', inplace=True)\ntest['trafficSource.keyword'].fillna('nan', inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"37e4fb9fe25ec1e3deb987720a30b494fb54841a"},"cell_type":"markdown","source":"#### totals.bounces"},{"metadata":{"trusted":true,"_uuid":"aedfdc48d5aa70bd774b55f031eac76568bae39a"},"cell_type":"code","source":"train['totals.bounces'] = train['totals.bounces'].astype('float')\ntrain['totals.bounces'] = train['totals.bounces'].fillna(0)\ntest['totals.bounces'] = test['totals.bounces'].astype('float')\ntest['totals.bounces'] = test['totals.bounces'].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ffdeb70dea36dcc6ec26b7b88b85ecadbff87c9b"},"cell_type":"markdown","source":"#### totals.newVisits"},{"metadata":{"trusted":true,"_uuid":"b447d2003dae2e8ccbfa3822f342566b58f8322b"},"cell_type":"code","source":"train['totals.newVisits'] = train['totals.newVisits'].astype('float')\ntrain['totals.newVisits'] = train['totals.newVisits'].fillna(0)\ntest['totals.newVisits'] = test['totals.newVisits'].astype('float')\ntest['totals.newVisits'] = test['totals.newVisits'].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1505f5d078013e4a2b32869139f70043390f155e"},"cell_type":"markdown","source":"#### \ttotals.pageviews"},{"metadata":{"trusted":true,"_uuid":"f942216e1484677faa1f9b256e10244b7fbf1991"},"cell_type":"code","source":"train['totals.pageviews'] = train['totals.pageviews'].astype('float')\ntrain['totals.pageviews'] = train['totals.pageviews'].fillna(0)\ntest['totals.pageviews'] = test['totals.pageviews'].astype('float')\ntest['totals.pageviews'] = test['totals.pageviews'].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"56795afc9aecd3c6495ebc4c4bcf28cee237730c"},"cell_type":"markdown","source":"## Further data treatment"},{"metadata":{"_uuid":"f3564112db7b4b2444227632e15d533384ce4dd1"},"cell_type":"markdown","source":"Here we will focus on:\n1. correcting datatypes "},{"metadata":{"trusted":true,"_uuid":"3346888fcb8903ae78d03523f2b34acaba3b7b64"},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6a3c7bee559657aed246898a90ef996699a2f5eb"},"cell_type":"markdown","source":"### Changing data types"},{"metadata":{"trusted":true,"_uuid":"02fa0bebb8863bbd32f8c34db735c3b5352872da"},"cell_type":"code","source":"#totals.hits\ntrain['totals.hits'] = train['totals.hits'].astype('float')\ntest['totals.hits'] = test['totals.hits'].astype('float')\n#visitNumber\ntrain['visitNumber'] = train['visitNumber'].astype('float')\ntest['visitNumber'] = test['visitNumber'].astype('float')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0a6389170a6ef200994827abd00fddd38355b998"},"cell_type":"markdown","source":"### Check correlation"},{"metadata":{"trusted":true,"_uuid":"043f9fd1fdbefb4e9ef3257c8aac116f88fe1763"},"cell_type":"code","source":"plt.figure(figsize=(10,10))\nsns.heatmap(train.corr(),annot=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"331f2070401904ad346e3479aa59082d74a26179"},"cell_type":"markdown","source":"We see that totals.hits and totals.pageviews are highly correlated. Removing one of them. Since total pageViews had  nulls. We shall remove totals.pageviews-- Maybe after feature engineering\n\nWe also see a high correlation between visitid and visitstarttime. We will remove one of them too.\n\nVisitid also is highly correlated with date. We will also remove multicollinearity from there."},{"metadata":{"_uuid":"c6362bc48710151f5279759e9aec75fa4481e842"},"cell_type":"markdown","source":"## Feature Engineering and Processing"},{"metadata":{"trusted":true,"_uuid":"f4054bf68bfbc521ac4c1a1fb596f1baa01843a4"},"cell_type":"code","source":"# Date-based features\n\n## on train data\n# transforming date\ntrain['date'] = train['date'].astype(str)\ntrain[\"date\"] = train[\"date\"].apply(lambda x : x[:4] + \"-\" + x[4:6] + \"-\" + x[6:])\ntrain[\"date\"] = pd.to_datetime(train[\"date\"])  \n\n#extracting features\ntrain['month'] = train['date'].dt.month\ntrain['day'] = train['date'].dt.day\ntrain['weekday'] = train['date'].dt.weekday\ntrain['weekofyear'] = train['date'].dt.weekofyear\n\n## similarly on test\n# transforming date\ntest['date'] = test['date'].astype(str)\ntest[\"date\"] = test[\"date\"].apply(lambda x : x[:4] + \"-\" + x[4:6] + \"-\" + x[6:])\ntest[\"date\"] = pd.to_datetime(test[\"date\"])  \n\n#extracting features\ntest['month'] = test['date'].dt.month\ntest['day'] = test['date'].dt.day\ntest['weekday'] = test['date'].dt.weekday\ntest['weekofyear'] = test['date'].dt.weekofyear","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"16e5981a9ddc3111df396182b09912bf6566c70b"},"cell_type":"code","source":"# using above features to create user count features\n\n## on train\ntrain['month_unique_user_count'] = train.groupby('month')['fullVisitorId'].transform('nunique')\ntrain['day_unique_user_count'] = train.groupby('day')['fullVisitorId'].transform('nunique')\ntrain['weekday_unique_user_count'] = train.groupby('weekday')['fullVisitorId'].transform('nunique')\ntrain['weekofyear_unique_user_count'] = train.groupby('weekofyear')['fullVisitorId'].transform('nunique')\n\n## on test\ntest['month_unique_user_count'] = test.groupby('month')['fullVisitorId'].transform('nunique')\ntest['day_unique_user_count'] = test.groupby('day')['fullVisitorId'].transform('nunique')\ntest['weekday_unique_user_count'] = test.groupby('weekday')['fullVisitorId'].transform('nunique')\ntest['weekofyear_unique_user_count'] = test.groupby('weekofyear')['fullVisitorId'].transform('nunique')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"45cbd1e92a3edb4d2b30a21c33f307a7d89a5c3d"},"cell_type":"code","source":"train[:3]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2e738822f58db53eb636b71eb39a3223e9b29e9e"},"cell_type":"code","source":"# device based features\n\ntrain['browser_category'] = train['device.browser'] + '_' + train['device.deviceCategory']\ntrain['browser_operatingSystem'] = train['device.browser'] + '_' + train['device.operatingSystem']\n\ntest['browser_category'] = test['device.browser'] + '_' + test['device.deviceCategory']\ntest['browser_operatingSystem'] = test['device.browser'] + '_' + test['device.operatingSystem']\n\ntrain['source_country'] = train['trafficSource.source'] + '_' + train['geoNetwork.country']\ntest['source_country'] = test['trafficSource.source'] + '_' + test['geoNetwork.country']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"01788cdece2bb9e373e23e42e0a79cefee15bdcd"},"cell_type":"code","source":"train['totals.hits'] = np.log1p(train['totals.hits'])\ntest['totals.hits'] = np.log1p(test['totals.hits'].astype(int))\n\ntrain['sum_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('sum')\ntrain['count_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('count')\ntrain['mean_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('mean')\ntrain['sum_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('sum')\ntrain['count_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('count')\ntrain['mean_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('mean')\n\ntest['sum_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('sum')\ntest['count_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('count')\ntest['mean_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('mean')\ntest['sum_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('sum')\ntest['count_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('count')\ntest['mean_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('mean')\n\ntrain['mean_hits_per_day'] = train.groupby(['day'])['totals.hits'].transform('mean')\ntrain['sum_hits_per_day'] = train.groupby(['day'])['totals.hits'].transform('sum')\ntest['mean_hits_per_day'] = test.groupby(['day'])['totals.hits'].transform('mean')\ntest['sum_hits_per_day'] = test.groupby(['day'])['totals.hits'].transform('sum')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7f323fe6ee6fc6e29e88fde8e8030e5c403339ce"},"cell_type":"code","source":"train['sum_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('sum')\ntrain['count_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('count')\ntrain['mean_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('mean')\n\ntrain['sum_pageviews_per_region'] = train.groupby('geoNetwork.region')['totals.pageviews'].transform('sum')\ntrain['count_pageviews_per_region'] = train.groupby('geoNetwork.region')['totals.pageviews'].transform('count')\ntrain['mean_pageviews_per_region'] = train.groupby('geoNetwork.region')['totals.pageviews'].transform('mean')\n\ntest['sum_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('sum')\ntest['count_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('count')\ntest['mean_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('mean')\n\ntest['sum_pageviews_per_region'] = test.groupby('geoNetwork.region')['totals.pageviews'].transform('sum')\ntest['count_pageviews_per_region'] = test.groupby('geoNetwork.region')['totals.pageviews'].transform('count')\ntest['mean_pageviews_per_region'] = test.groupby('geoNetwork.region')['totals.pageviews'].transform('mean')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d44c5fbb753988a97cd0a1fe89a9ed7eba2e3fd9"},"cell_type":"code","source":"train['sum_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('sum')\ntrain['count_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('count')\ntrain['mean_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('mean')\n\ntrain['sum_hits_per_region'] = train.groupby('geoNetwork.region')['totals.hits'].transform('sum')\ntrain['count_hits_per_region'] = train.groupby('geoNetwork.region')['totals.hits'].transform('count')\ntrain['mean_hits_per_region'] = train.groupby('geoNetwork.region')['totals.hits'].transform('mean')\n\ntrain['sum_hits_per_country'] = train.groupby('geoNetwork.country')['totals.hits'].transform('sum')\ntrain['count_hits_per_country'] = train.groupby('geoNetwork.country')['totals.hits'].transform('count')\ntrain['mean_hits_per_country'] = train.groupby('geoNetwork.country')['totals.hits'].transform('mean')\n\ntest['sum_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('sum')\ntest['count_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('count')\ntest['mean_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('mean')\n\ntest['sum_hits_per_region'] = test.groupby('geoNetwork.region')['totals.hits'].transform('sum')\ntest['count_hits_per_region'] = test.groupby('geoNetwork.region')['totals.hits'].transform('count')\ntest['mean_hits_per_region'] = test.groupby('geoNetwork.region')['totals.hits'].transform('mean')\n\ntest['sum_hits_per_country'] = test.groupby('geoNetwork.country')['totals.hits'].transform('sum')\ntest['count_hits_per_country'] = test.groupby('geoNetwork.country')['totals.hits'].transform('count')\ntest['mean_hits_per_country'] = test.groupby('geoNetwork.country')['totals.hits'].transform('mean')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f2fbf456efcbc855f8e677f9e1a8349301f1776a"},"cell_type":"code","source":"train['user_pageviews_sum'] = train.groupby('fullVisitorId')['totals.pageviews'].transform('sum')\ntrain['user_hits_sum'] = train.groupby('fullVisitorId')['totals.hits'].transform('sum')\ntest['user_pageviews_sum'] = test.groupby('fullVisitorId')['totals.pageviews'].transform('sum')\ntest['user_hits_sum'] = test.groupby('fullVisitorId')['totals.hits'].transform('sum')\n\ntrain['user_pageviews_count'] = train.groupby('fullVisitorId')['totals.pageviews'].transform('count')\ntrain['user_hits_count'] = train.groupby('fullVisitorId')['totals.hits'].transform('count')\ntest['user_pageviews_count'] = test.groupby('fullVisitorId')['totals.pageviews'].transform('count')\ntest['user_hits_count'] = test.groupby('fullVisitorId')['totals.hits'].transform('count')\n\ntrain['user_pageviews_sum_to_mean'] = train['user_pageviews_sum'] / train['user_pageviews_sum'].mean()\ntrain['user_hits_sum_to_mean'] = train['user_hits_sum'] / train['user_hits_sum'].mean()\ntest['user_pageviews_sum_to_mean'] = test['user_pageviews_sum'] / test['user_pageviews_sum'].mean()\ntest['user_hits_sum_to_mean'] = test['user_hits_sum'] / test['user_hits_sum'].mean()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c12bc851dcdf95a0d78dfd6e53afb24fc72859d4"},"cell_type":"code","source":"train['user_pageviews_to_region'] = train['user_pageviews_sum'] / train['mean_pageviews_per_region']\ntrain['user_hits_to_region'] = train['user_hits_sum'] / train['mean_hits_per_region']\n\ntest['user_pageviews_to_region'] = test['user_pageviews_sum'] / test['mean_pageviews_per_region']\ntest['user_hits_to_region'] = test['user_hits_sum'] / test['mean_hits_per_region']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4f293a929748e257861180999f034f82d92f6e83"},"cell_type":"code","source":"train['trafficSource.keyword'].value_counts()\ndef kw_category(x):\n    x = str(x).lower()\n    if x == 'nan':\n        return 'nan'\n    \n    x = ''.join(x.split())\n    \n    if 'youtube' in x or 'you' in x or 'yo' in x or 'tub' in x:\n        return 'youtube'\n    elif 'google' in x or 'goo' in x or 'gle' in x:\n        return 'google'\n    elif 'content' in x or 'targe' in x or 'cont' in x or 'target' in x or 'remarket' in x:\n        return 'content_target'\n    elif 'auto' in x or 'match' in x or 'tching' in x:\n        return 'auto_matching'\n    elif 'art' in x or 'entertainment' in x or 'game' in x or 'gamin' in x or 'play' in x:\n        return 'art_entertainment_play'\n    else:\n        return 'other'\n    \ntrain['KW_category'] = train['trafficSource.keyword'].apply(kw_category)\ntest['KW_category'] = test['trafficSource.keyword'].apply(kw_category)    ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2be0e704dbc0705988ec7727b358b2c1aac46f3c"},"cell_type":"code","source":"train['KW_category'].value_counts()\ntrain['trafficSource.keyword'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"355b009e17eb7f4aa597556a07fea79061a81b73"},"cell_type":"code","source":"# generating squared columns\ncol_x = ['sum_pageviews_per_network_domain', 'sum_hits_per_network_domain',\n       'mean_hits_per_network_domain',\n       'mean_pageviews_per_network_domain', 'totals.hits',\n       'totals.pageviews']\n\nfor i in col_x:\n    train[i+'_square'] = train[i] ** 2\n    test[i+'_square'] = test[i] ** 2","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5fb1546213ce7fe4af8ad653fb04c734467fcf1d"},"cell_type":"code","source":"#generating root columns\nfor i in col_x:\n    train[i+'_root'] = train[i] ** 0.5\n    test[i+'_root'] = test[i] ** 2","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"53a3abdc7a091d301bcc91f7b177953085f50941"},"cell_type":"markdown","source":"## Final Feature Selection"},{"metadata":{"trusted":true,"_uuid":"684a773af851229a35f1664009a3e431c8476d31"},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"20738c068679997388ac103e714aa4964cb84673"},"cell_type":"code","source":"train[:3]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bf740e590c8870ed7d18002c7e0bfe7f99bbb1a8"},"cell_type":"markdown","source":"### Feature removal on the basis of instincts and variable meaning"},{"metadata":{"trusted":true,"_uuid":"60ea09257a1ee91d37d4b882f2f7b28356cd6829"},"cell_type":"code","source":"train_fullVisitorId = train['fullVisitorId']\ntrain_sessionId = train['sessionId']\ntrain_visitId = train['visitId']\n\ntest_fullVisitorId = test['fullVisitorId']\ntest_id= test_id = test_fullVisitorId.values\ntest_sessionId = test['sessionId']\ntest_visitId = test['visitId']\n\nid_cols_to_drop= [\"sessionId\",\"fullVisitorId\",\"visitId\"]\nvisit_traits= [\"date\",\"visitNumber\",\"visitStartTime\"]\nbase_cat_cols_to_drop = ['trafficSource.keyword']\n\nall_cols_to_drop= id_cols_to_drop + visit_traits + base_cat_cols_to_drop\n#dropping these columns from train and test\ntrain.drop(all_cols_to_drop, axis=1, inplace=True)\ntest.drop(all_cols_to_drop, axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b7300b2b8fa2972821faeaf8b3b2c2f8a5502f20"},"cell_type":"code","source":"corr_matrix = train.corr().abs()\ncorr_matrix","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1545f4c241e34ca8198aeee8f110d643f5081fe5"},"cell_type":"markdown","source":"### Feature removal on the basis of correlation"},{"metadata":{"trusted":true,"_uuid":"d1ec1036c3bc85d71cb312d34fe198d1885f7ba3"},"cell_type":"code","source":"# remove columns due to correlation\n# removing one of totals.hits, totals.bounces and totals.pageviews\ncorr_columns_to_remove= [\"totals.pageviews\"]\n\n#removing them from train and test\ntrain.drop(corr_columns_to_remove, axis=1, inplace=True)\ntest.drop(corr_columns_to_remove, axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"46f38f72ebfad0c72598fa949b48c48c3656808f"},"cell_type":"code","source":"train[:3]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5699ef02dd074e3d1cc5d7331293e0bcabc8c862"},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bf408610bd7d333d936c70952e63c1583e5ad2fe"},"cell_type":"code","source":"num_cols = ['totals.bounces',\n             'totals.hits',\n             'totals.newVisits',\n             'month_unique_user_count',\n             'day_unique_user_count',\n             'weekday_unique_user_count',\n             'weekofyear_unique_user_count',\n             'sum_pageviews_per_network_domain',\n             'count_pageviews_per_network_domain',\n             'mean_pageviews_per_network_domain',\n             'sum_hits_per_network_domain',\n             'count_hits_per_network_domain',\n             'mean_hits_per_network_domain',\n             'mean_hits_per_day',\n             'sum_hits_per_day',\n             'sum_pageviews_per_region',\n             'count_pageviews_per_region',\n             'mean_pageviews_per_region',\n             'sum_hits_per_region',\n             'count_hits_per_region',\n             'mean_hits_per_region',\n             'sum_hits_per_country',\n             'count_hits_per_country',\n             'mean_hits_per_country',\n             'user_pageviews_sum',\n             'user_hits_sum',\n             'user_pageviews_count',\n             'user_hits_count',\n             'user_pageviews_sum_to_mean',\n             'user_hits_sum_to_mean',\n             'user_pageviews_to_region',\n             'user_hits_to_region',\n             'sum_pageviews_per_network_domain_square',\n             'sum_hits_per_network_domain_square',\n             'mean_hits_per_network_domain_square',\n             'mean_pageviews_per_network_domain_square',\n             'totals.hits_square',\n             'totals.pageviews_square',\n             'sum_pageviews_per_network_domain_root',\n             'sum_hits_per_network_domain_root',\n             'mean_hits_per_network_domain_root',\n             'mean_pageviews_per_network_domain_root',\n             'totals.hits_root',\n             'totals.pageviews_root']\n\ntarget= ['totals.transactionRevenue']\n\ncat_cols= set(list(train)) - set(target) - set(num_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"99b28868f0da9e4fee52bfa1d7e22ab045c2a020"},"cell_type":"markdown","source":"### Separating target variable"},{"metadata":{"trusted":true,"_uuid":"452465135622e2baa5990c4d0664ae54faf6809b"},"cell_type":"code","source":"y = train['totals.transactionRevenue']\ntrain = train.drop('totals.transactionRevenue', axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7cf743c1fb9c955e39a7ac4894917b4d30ee78e7"},"cell_type":"code","source":"train","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"793b6032c0ef40cf21ec441ab62cf8d4e1a366ca"},"cell_type":"code","source":"test","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"58d74f26df99833339575668d9879d6874fa8f36"},"cell_type":"markdown","source":"### Transforming features"},{"metadata":{"trusted":true,"_uuid":"2dd7795ff87d36d232fa422d17ecf7c503e9b86c"},"cell_type":"code","source":"cat_cols","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"36a8c939a178d8b1e3ff8f4f9815500086391627"},"cell_type":"code","source":"# label encoder\nle = preprocessing.LabelEncoder()\n\nfor c in cat_cols:\n    le.fit(train[c])\n    train[c] = le.transform(train[c])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2844df74dca8ea3a9245191b2d1361e604134daf"},"cell_type":"code","source":"#label encoding for test\nfor c in cat_cols:\n    le.fit(test[c])\n    test[c] = le.transform(test[c])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a6aca2bb5662c2c6abf79767291c85afb160b302"},"cell_type":"markdown","source":"## Model Building"},{"metadata":{"_uuid":"9199f21b2ba520c9b015be6f961e074c285f221d"},"cell_type":"markdown","source":"### create and train model"},{"metadata":{"trusted":true,"_uuid":"44352b3ac542b3b9e98bf39221182e5a9d46c57a"},"cell_type":"code","source":"train_x, val_x, train_y, val_y = train_test_split(train, y, test_size=0.25, random_state=20)\n\nlgb_params = {\"objective\" : \"regression\", \"metric\" : \"rmse\",\n              \"num_leaves\" : 36, \"learning_rate\" : 0.05, \"bagging_fraction\" : 0.75, \"feature_fraction\" : 0.6, \"bagging_frequency\" : 7}\n    \nlgb_train = lgb.Dataset(train_x, label=train_y)\nlgb_val = lgb.Dataset(val_x, label=val_y)\nmodel = lgb.train(lgb_params, lgb_train, 300, valid_sets=[lgb_val], early_stopping_rounds=50, verbose_eval=100)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"afd73ddad01b300ae16c5e2ee7bc8b94bfc553fa"},"cell_type":"markdown","source":"### predictions"},{"metadata":{"trusted":true,"_uuid":"424b4d49b8289d0a5111487f60134baaae88812c"},"cell_type":"code","source":"preds = model.predict(test, num_iteration=model.best_iteration)\ntest[\"PredictedLogRevenue\"] = np.expm1(preds)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6ea21d6d6b023bab50cc7cbc1efcb276b79336a5"},"cell_type":"code","source":"sub_df = pd.DataFrame({\"fullVisitorId\":test_id})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"36fe6cca3129ee2a35f28fa95360ac00f7203294"},"cell_type":"code","source":"sub_df[\"PredictedLogRevenue\"] = np.expm1(preds)\nsub_df = sub_df.groupby(\"fullVisitorId\")[\"PredictedLogRevenue\"].sum().reset_index()\nsub_df.columns = [\"fullVisitorId\", \"PredictedLogRevenue\"]\nsub_df[\"PredictedLogRevenue\"] = np.log1p(sub_df[\"PredictedLogRevenue\"])\nsub_df.to_csv(\"predictions_test_lgb.csv\", index=False)","execution_count":null,"outputs":[]}],"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}