{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"Thanks Stan Wang, Dale Euinton, and Hang Zhao ","metadata":{}},{"cell_type":"code","source":"from __future__ import division\nimport numpy as np\nimport pandas as pd \nimport os\nimport timeit\nimport warnings\nwarnings.filterwarnings(\"ignore\")\nfrom tqdm import tqdm","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:20:56.128139Z","iopub.execute_input":"2021-06-23T05:20:56.128792Z","iopub.status.idle":"2021-06-23T05:20:56.167624Z","shell.execute_reply.started":"2021-06-23T05:20:56.128711Z","shell.execute_reply":"2021-06-23T05:20:56.166732Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import matplotlib.pyplot as plt\nimport seaborn as sns\nfrom plotly.offline import init_notebook_mode, iplot\nimport plotly.graph_objs as go\nfrom plotly import tools\n# link pandas and plotly\n# import cufflinks as cf\n\ninit_notebook_mode(connected=True)\n# cf.set_config_file(offline=True, world_readable=True, theme='ggplot')\n%matplotlib inline","metadata":{"_uuid":"5770f2af736df0eb707567f9cd2b2a9c91876410","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:20:56.169274Z","iopub.execute_input":"2021-06-23T05:20:56.169611Z","iopub.status.idle":"2021-06-23T05:20:58.566247Z","shell.execute_reply.started":"2021-06-23T05:20:56.169542Z","shell.execute_reply":"2021-06-23T05:20:58.564905Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from matplotlib import rcParams\nrcParams['figure.figsize'] = 22, 14\nrcParams['axes.titlesize'] = 24\nrcParams['axes.labelsize'] = 20\nrcParams['xtick.labelsize'] = 16\nrcParams['ytick.labelsize'] = 16\nrcParams['legend.fontsize'] = 14","metadata":{"_uuid":"8d2c72bd4ac553dd5c5b6147d7f14ffc1df0813b","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:20:58.570578Z","iopub.execute_input":"2021-06-23T05:20:58.57094Z","iopub.status.idle":"2021-06-23T05:20:58.577507Z","shell.execute_reply.started":"2021-06-23T05:20:58.570876Z","shell.execute_reply":"2021-06-23T05:20:58.576536Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def sub_boxenplots(x, data, y=\"totals.transactionRevenue_ln\", rot=15):\n order = data[x].unique()\n fig, axes = plt.subplots(ncols=2, nrows=1, squeeze=False, figsize=(22, 8))\n sns.boxenplot(x=x, y=y, data=data, ax=axes[0, 0], order=order)\n axes[0, 0].set_title(\"All Instances\")\n axes[0, 0].set_xticklabels(axes[0, 0].get_xticklabels(), rotation=rot, ha='right')\n sns.boxenplot(x=x, y=y, data=data.loc[data[y] > 0, :], ax=axes[0, 1], order=order)\n axes[0, 1].set_title(\"Instances With Non-zero Revenue\")\n axes[0, 1].set_xticklabels(axes[0, 1].get_xticklabels(), rotation=rot, ha='right')\n fig.tight_layout()","metadata":{"_uuid":"63628dc1d047ac18ee64c6f5ecd54521d6c95e79","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:20:58.579784Z","iopub.execute_input":"2021-06-23T05:20:58.58009Z","iopub.status.idle":"2021-06-23T05:20:58.59397Z","shell.execute_reply.started":"2021-06-23T05:20:58.580037Z","shell.execute_reply":"2021-06-23T05:20:58.592871Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"prefix = \"../input/ga-customer-revenue-prediction/\"","metadata":{"_uuid":"9a3c2ff75ba4620bb5415435db1756df6006933c","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:20:58.595464Z","iopub.execute_input":"2021-06-23T05:20:58.595834Z","iopub.status.idle":"2021-06-23T05:20:58.605789Z","shell.execute_reply.started":"2021-06-23T05:20:58.595753Z","shell.execute_reply":"2021-06-23T05:20:58.605042Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import json\nfrom pandas.io.json import json_normalize\ndef load_df(csv_path='../input/train.csv', nrows=None):\n JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n \n read_start = timeit.default_timer()\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 parse_dates=['date']\n )\n read_end = timeit.default_timer()\n print(\"Finish reading {0}, time usage: {1}\".format(csv_path, read_end - read_start))\n \n process_start = timeit.default_timer()\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 process_end = timeit.default_timer()\n print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}, time usage: \", process_end - process_start)\n return df","metadata":{"_uuid":"f674eb19ad7a186a9cda85496b33f736f257346c","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:20:58.606664Z","iopub.execute_input":"2021-06-23T05:20:58.606882Z","iopub.status.idle":"2021-06-23T05:20:58.618831Z","shell.execute_reply.started":"2021-06-23T05:20:58.606849Z","shell.execute_reply":"2021-06-23T05:20:58.618106Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Just for convinence, uncomment before submite the codes\nX_train_df = load_df(prefix + \"train.csv\")\nX_test_df = load_df(prefix + \"test.csv\")\n\nprint(\"Done!\")","metadata":{"_uuid":"58e6796396ffead7b6da1e38608136af5642964e","execution":{"iopub.status.busy":"2021-06-23T05:20:58.62Z","iopub.execute_input":"2021-06-23T05:20:58.620514Z","iopub.status.idle":"2021-06-23T05:28:23.272374Z","shell.execute_reply.started":"2021-06-23T05:20:58.620456Z","shell.execute_reply":"2021-06-23T05:28:23.271387Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train_df.head(1)","metadata":{"_uuid":"4cb600b7670c61e015703c9cf35d541e7001276a","execution":{"iopub.status.busy":"2021-06-23T05:28:23.276263Z","iopub.execute_input":"2021-06-23T05:28:23.276606Z","iopub.status.idle":"2021-06-23T05:28:23.394888Z","shell.execute_reply.started":"2021-06-23T05:28:23.276547Z","shell.execute_reply":"2021-06-23T05:28:23.393756Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submit_id = X_test_df[\"fullVisitorId\"].unique()","metadata":{"_uuid":"6e67cc70fafbc6efc57db54f56bc33b9b8ce46b5","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:28:23.399015Z","iopub.execute_input":"2021-06-23T05:28:23.399377Z","iopub.status.idle":"2021-06-23T05:28:23.641306Z","shell.execute_reply.started":"2021-06-23T05:28:23.399317Z","shell.execute_reply":"2021-06-23T05:28:23.638775Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train_df.loc[:, \"totals.transactionRevenue_ln\"] = np.log1p(X_train_df[\"totals.transactionRevenue\"].fillna(0).astype(\"float\"))","metadata":{"_uuid":"a761a719961b6cd5c3d0286e7ca2ceca08475761","execution":{"iopub.status.busy":"2021-06-23T05:28:23.642505Z","iopub.execute_input":"2021-06-23T05:28:23.642781Z","iopub.status.idle":"2021-06-23T05:28:27.787566Z","shell.execute_reply.started":"2021-06-23T05:28:23.642742Z","shell.execute_reply":"2021-06-23T05:28:27.786218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"labels = ['Zero revenue instance', 'Non-zero revenue instance']\nvalues = [X_train_df.loc[X_train_df[\"totals.transactionRevenue_ln\"] == 0, \"totals.transactionRevenue_ln\"].count(),\n X_train_df.loc[X_train_df[\"totals.transactionRevenue_ln\"] != 0, \"totals.transactionRevenue_ln\"].count()]\ntrace = go.Pie(labels=labels, values=values)\nlayout = dict(\n title = 'Instance Revenue'\n)\n\nfig = dict(data=[trace], layout=layout)\niplot(fig, filename='basic_pie_chart')","metadata":{"_uuid":"63b54c6addc7068d906ee96383ee374844cc36ca","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:28:27.788816Z","iopub.execute_input":"2021-06-23T05:28:27.789088Z","iopub.status.idle":"2021-06-23T05:28:28.891186Z","shell.execute_reply.started":"2021-06-23T05:28:27.78903Z","shell.execute_reply":"2021-06-23T05:28:28.890286Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sum_target_series = X_train_df.groupby(\"fullVisitorId\")[\"totals.transactionRevenue_ln\"].sum()\n\nlabels = ['Zero revenue customer', 'Non-zero customer']\nvalues = [sum_target_series[sum_target_series == 0].count(),\n sum_target_series[sum_target_series != 0].count()]\ntrace = go.Pie(labels=labels, values=values)\nlayout = dict(\n title = 'Unique Customer Revenue'\n)\n\nfig = dict(data=[trace], layout=layout)\niplot(fig, filename='basic_pie_chart')","metadata":{"_uuid":"1ddc182d565c35d2711a08580f931d27b4ee9cdf","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:28:28.892116Z","iopub.execute_input":"2021-06-23T05:28:28.892337Z","iopub.status.idle":"2021-06-23T05:28:32.039061Z","shell.execute_reply.started":"2021-06-23T05:28:28.892301Z","shell.execute_reply":"2021-06-23T05:28:32.037754Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"trace = go.Box(y=X_train_df.loc[X_train_df[\"totals.transactionRevenue_ln\"] > 0, \"totals.transactionRevenue_ln\"],\n name='Instance')\nlayout = dict(\n title = 'Instance With Revenue Greater Than Zero'\n)\n\nfig = dict(data=[trace], layout=layout)\niplot(fig, filename='basic_pie_chart')","metadata":{"_uuid":"ee1d1519eae422ab634ce4937715b1ae6af295c9","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:28:32.040075Z","iopub.execute_input":"2021-06-23T05:28:32.040337Z","iopub.status.idle":"2021-06-23T05:28:32.303477Z","shell.execute_reply.started":"2021-06-23T05:28:32.040284Z","shell.execute_reply":"2021-06-23T05:28:32.302584Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"trace = go.Box(y=sum_target_series[sum_target_series > 0], name=\"Unique User\")\niplot([trace])","metadata":{"_uuid":"16dc96b6a9d9146ea7d79f61de0d40bd66346dd2","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:28:32.304428Z","iopub.execute_input":"2021-06-23T05:28:32.304762Z","iopub.status.idle":"2021-06-23T05:28:32.466362Z","shell.execute_reply.started":"2021-06-23T05:28:32.304722Z","shell.execute_reply":"2021-06-23T05:28:32.46502Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"to_drop_cols = [c for c in X_train_df.columns if X_train_df[c].nunique(dropna=False)==1 ]\n\nX_train_df.drop(to_drop_cols, axis=1, inplace=True)\nX_test_df.drop(to_drop_cols, axis=1, inplace=True)","metadata":{"scrolled":true,"_uuid":"29b3fae4b2bd0a31194cf4b71a57ad3fcda7603a","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:28:32.468455Z","iopub.execute_input":"2021-06-23T05:28:32.468834Z","iopub.status.idle":"2021-06-23T05:28:49.859881Z","shell.execute_reply.started":"2021-06-23T05:28:32.46877Z","shell.execute_reply":"2021-06-23T05:28:49.858912Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Training set shape: \", X_train_df.shape, \" Testing set shape: \", X_test_df.shape)\nprint(\"Difference features between two sets:\")\nfor col in X_train_df.columns:\n if col not in X_test_df:\n print(col)","metadata":{"_uuid":"9d57ad7f52a0fac071e2f239ef4d6a8382bcd2ff","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:28:49.860915Z","iopub.execute_input":"2021-06-23T05:28:49.861158Z","iopub.status.idle":"2021-06-23T05:28:49.868671Z","shell.execute_reply.started":"2021-06-23T05:28:49.861112Z","shell.execute_reply":"2021-06-23T05:28:49.867081Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train_df[\"trafficSource.campaignCode\"].unique()","metadata":{"_uuid":"f7ac5f599a20b2f43dc85a33b1c87a75ad15a160","_kg_hide-input":true,"scrolled":true,"execution":{"iopub.status.busy":"2021-06-23T05:28:49.869772Z","iopub.execute_input":"2021-06-23T05:28:49.87001Z","iopub.status.idle":"2021-06-23T05:28:49.960435Z","shell.execute_reply.started":"2021-06-23T05:28:49.869966Z","shell.execute_reply":"2021-06-23T05:28:49.959054Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train_df.drop(\"trafficSource.campaignCode\", axis=1, inplace=True)\n\nX_test_df = pd.concat([X_test_df, \n pd.Series(np.nan, name='totals.transactionRevenue'), \n pd.Series(np.nan, name='totals.transactionRevenue_ln')], \n axis=1)\n\nX_all_df = pd.concat([X_train_df, X_test_df], ignore_index=True, sort=False)","metadata":{"_uuid":"63155794b6b0708353b5716625a15051e632b852","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:28:49.961545Z","iopub.execute_input":"2021-06-23T05:28:49.961796Z","iopub.status.idle":"2021-06-23T05:28:56.245597Z","shell.execute_reply.started":"2021-06-23T05:28:49.96175Z","shell.execute_reply":"2021-06-23T05:28:56.244714Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.drop(['sessionId', 'visitId'], axis=1, inplace=True)","metadata":{"_uuid":"f7e16b85556fcc8a33c78cdd518a1c1a0cd2fce5","_kg_hide-input":false,"scrolled":true,"execution":{"iopub.status.busy":"2021-06-23T05:28:56.247209Z","iopub.execute_input":"2021-06-23T05:28:56.247907Z","iopub.status.idle":"2021-06-23T05:29:02.557383Z","shell.execute_reply.started":"2021-06-23T05:28:56.247839Z","shell.execute_reply":"2021-06-23T05:29:02.556578Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"obj_cols = [column for column in X_all_df.columns if X_all_df[column].dtype == object]","metadata":{"_uuid":"f627f9ebe9b20061114cfb66cb4995e65e51384d","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:29:02.559088Z","iopub.execute_input":"2021-06-23T05:29:02.559647Z","iopub.status.idle":"2021-06-23T05:29:02.568175Z","shell.execute_reply.started":"2021-06-23T05:29:02.559583Z","shell.execute_reply":"2021-06-23T05:29:02.566888Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in obj_cols:\n try:\n X_all_df.loc[:, col] = pd.to_numeric(X_all_df.loc[:, col], errors='raise')\n# print(\"Successfully parse column: \", col)\n except ValueError as e:\n # mute the string column that cannot be converted to numeric values.\n pass\n# print(\"Unable to parse column: \", col)","metadata":{"_uuid":"54575f6409998dd84e769cee98d09224a805f40f","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:29:02.570001Z","iopub.execute_input":"2021-06-23T05:29:02.570441Z","iopub.status.idle":"2021-06-23T05:29:21.686125Z","shell.execute_reply.started":"2021-06-23T05:29:02.570346Z","shell.execute_reply":"2021-06-23T05:29:21.684872Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in X_all_df.select_dtypes(include='object'):\n if col == \"fullVisitorId\":\n continue\n try:\n X_all_df.loc[:, col] = pd.Categorical(X_all_df.loc[:, col])\n# print(\"Successfully parse column: \", col)\n except ValueError as e:\n print(\"Unable to parse column: \", col)","metadata":{"_uuid":"7cf0b7128e5973de488fcf52ecf35171fe93626c","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:29:21.687602Z","iopub.execute_input":"2021-06-23T05:29:21.687864Z","iopub.status.idle":"2021-06-23T05:29:45.562539Z","shell.execute_reply.started":"2021-06-23T05:29:21.687825Z","shell.execute_reply":"2021-06-23T05:29:45.561347Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"bool_cols = []\nfor col in X_all_df:\n if X_all_df[col].nunique() == 2:\n bool_cols.append(col)\nX_all_df.loc[:, bool_cols] = X_all_df.loc[:, bool_cols].astype('bool')","metadata":{"_uuid":"f5eb065da631abbf39ccd5a0a11c7acbe4503a08","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:29:45.563751Z","iopub.execute_input":"2021-06-23T05:29:45.564032Z","iopub.status.idle":"2021-06-23T05:29:46.735944Z","shell.execute_reply.started":"2021-06-23T05:29:45.563982Z","shell.execute_reply":"2021-06-23T05:29:46.734924Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def investigate(col_name, data):\n print(\"The number of unique category\")\n print(col_name, \": \", data[col_name].nunique())\n print(\"*\" * 20)\n print(\"Value count\")\n print(data[col_name].value_counts())","metadata":{"_kg_hide-input":true,"_uuid":"15bdf92c0f348c1665c4b17df791c1b862f4eeae","execution":{"iopub.status.busy":"2021-06-23T05:29:46.737149Z","iopub.execute_input":"2021-06-23T05:29:46.737414Z","iopub.status.idle":"2021-06-23T05:29:46.746108Z","shell.execute_reply.started":"2021-06-23T05:29:46.737355Z","shell.execute_reply":"2021-06-23T05:29:46.744846Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def ratio_nan(data):\n num_data = X_all_df.shape[0]\n null_sum = data.isnull().sum()\n null_val_features = null_sum[null_sum > 0]\n if \"totals.transactionRevenue_ln\" in null_val_features:\n null_val_features.drop([\"totals.transactionRevenue\", \"totals.transactionRevenue_ln\"], inplace=True)\n print(null_val_features/ num_data)","metadata":{"_kg_hide-input":true,"_uuid":"d1735d088c2a70f9616b3fbcce10b23751af3686","execution":{"iopub.status.busy":"2021-06-23T05:29:46.747497Z","iopub.execute_input":"2021-06-23T05:29:46.747773Z","iopub.status.idle":"2021-06-23T05:29:46.759587Z","shell.execute_reply.started":"2021-06-23T05:29:46.747726Z","shell.execute_reply":"2021-06-23T05:29:46.758278Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"The features with NaN value percentage: \")\nratio_nan(X_all_df)","metadata":{"_uuid":"4a2d10cf99b4a64733018306fb7d0218f173bf71","execution":{"iopub.status.busy":"2021-06-23T05:29:46.760891Z","iopub.execute_input":"2021-06-23T05:29:46.761154Z","iopub.status.idle":"2021-06-23T05:29:48.212078Z","shell.execute_reply.started":"2021-06-23T05:29:46.761096Z","shell.execute_reply":"2021-06-23T05:29:48.210976Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[:, \"totals.bounces\"].fillna(0, inplace=True)\nX_all_df.loc[:, \"totals.bounces\"] = X_all_df.loc[:, \"totals.bounces\"].astype('bool')","metadata":{"_uuid":"136c28b7803c05e85ab2d5610708fe10db500cff","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:29:48.213569Z","iopub.execute_input":"2021-06-23T05:29:48.2139Z","iopub.status.idle":"2021-06-23T05:29:48.304215Z","shell.execute_reply.started":"2021-06-23T05:29:48.21384Z","shell.execute_reply":"2021-06-23T05:29:48.303289Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sub_boxenplots(data=X_all_df.loc[X_all_df[\"totals.transactionRevenue_ln\"].notnull(), :], x=\"totals.bounces\")","metadata":{"_uuid":"c506338a7fca20648ee9a6c93ac60e84e32611d5","execution":{"iopub.status.busy":"2021-06-23T05:29:48.305441Z","iopub.execute_input":"2021-06-23T05:29:48.305745Z","iopub.status.idle":"2021-06-23T05:29:49.24079Z","shell.execute_reply.started":"2021-06-23T05:29:48.30569Z","shell.execute_reply":"2021-06-23T05:29:49.239439Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.drop(X_all_df.loc[X_all_df[\"totals.bounces\"], \"fullVisitorId\"].index, axis=0, inplace=True)\n\n# We don't need this feature anymore\nX_all_df.drop(\"totals.bounces\", axis=1, inplace=True)\n\nX_all_df.reset_index(drop=True, inplace=True)","metadata":{"_uuid":"aa88d198e9b3a9b967e5d2104c7d6956e2a1676d","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:29:49.256489Z","iopub.execute_input":"2021-06-23T05:29:49.257027Z","iopub.status.idle":"2021-06-23T05:29:49.828273Z","shell.execute_reply.started":"2021-06-23T05:29:49.256934Z","shell.execute_reply":"2021-06-23T05:29:49.827136Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[:, \"totals.newVisits\"].fillna(0, inplace=True)\nX_all_df.loc[:, \"totals.newVisits\"] = X_all_df.loc[:, \"totals.newVisits\"].astype('bool')","metadata":{"_uuid":"4a65b0a0eb73a8fa84d2bd8f67849a887d0f2044","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:29:49.835108Z","iopub.execute_input":"2021-06-23T05:29:49.835668Z","iopub.status.idle":"2021-06-23T05:29:49.863839Z","shell.execute_reply.started":"2021-06-23T05:29:49.835363Z","shell.execute_reply":"2021-06-23T05:29:49.862607Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[:, \"totals.pageviews\"].fillna(0, inplace=True)","metadata":{"_uuid":"613f93de5c0c4ce279fe4db060456e3390eee471","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:29:49.865101Z","iopub.execute_input":"2021-06-23T05:29:49.865372Z","iopub.status.idle":"2021-06-23T05:29:49.872455Z","shell.execute_reply.started":"2021-06-23T05:29:49.865323Z","shell.execute_reply":"2021-06-23T05:29:49.871369Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def plot_cmp_stack(data, col_name):\n \n total_null_num = data[col_name].isnull().sum()\n total_not_null_num = data[col_name].notnull().sum()\n \n null_zero_num = (data.loc[data[col_name].isnull(), \"totals.transactionRevenue_ln\"] == 0).sum()\n null_non_zero_num = (data.loc[data[col_name].isnull(), \"totals.transactionRevenue_ln\"] != 0).sum()\n not_null_zero_num = (data.loc[data[col_name].notnull(), \"totals.transactionRevenue_ln\"] == 0).sum()\n not_null_non_zero_num = (data.loc[data[col_name].notnull(), \"totals.transactionRevenue_ln\"] != 0).sum()\n \n trace1 = go.Bar(\n x=['NULL', 'Not NULL'],\n y=[null_zero_num/ total_null_num, not_null_zero_num/ total_not_null_num],\n name='Instances With Zero Revenue',\n marker=dict(\n color='rgb(158,202,225)',\n line=dict(\n color='rgb(8,48,107)',\n width=1.5),\n ),\n opacity=0.6\n )\n \n trace2 = go.Bar(\n x=['NULL', 'Not NULL'],\n y=[null_non_zero_num/ total_null_num, not_null_non_zero_num/ total_not_null_num],\n name='Instances With Non-Zero Revenue',\n marker=dict(\n color='rgb(58,200,225)',\n line=dict(\n color='rgb(8,48,107)',\n width=1.5),\n ),\n opacity=0.6 \n )\n \n trace3 = go.Pie(\n labels = ['Ratio Of Non-zero Revenue With This Feature Is Null', 'Ratio Of Non-zero Revenue With This Feature Is Not NULL'],\n values = [null_non_zero_num, not_null_non_zero_num],\n domain = {\"x\": [0.5, 1]},\n hole = .4,\n name = 'Instances With Non-zero Revenue',\n text = ['Non-zero Ratio']\n )\n \n data=[trace1, trace2, trace3]\n \n layout=go.Layout(\n barmode='stack',\n title = col_name + \": Revenue Comparison Between NULL and Not NULL Value\",\n yaxis = {'title': 'Percentage'},\n xaxis = {\n 'domain': [0, 0.5]\n },\n showlegend=False\n )\n \n fig = go.Figure(data=data, layout=layout)\n iplot(fig, filename=col_name + '_stack_bar')","metadata":{"_kg_hide-input":true,"_uuid":"86fc0af8daf9906b834b8d7f2b31fec7bad6482c","execution":{"iopub.status.busy":"2021-06-23T05:29:49.873574Z","iopub.execute_input":"2021-06-23T05:29:49.874041Z","iopub.status.idle":"2021-06-23T05:29:49.891777Z","shell.execute_reply.started":"2021-06-23T05:29:49.873977Z","shell.execute_reply":"2021-06-23T05:29:49.891004Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_cmp_stack(data=X_train_df, col_name=\"trafficSource.adwordsClickInfo.gclId\")","metadata":{"_uuid":"38eea165fcc3ea5ff773ae369514b887c2194916","execution":{"iopub.status.busy":"2021-06-23T05:29:49.892671Z","iopub.execute_input":"2021-06-23T05:29:49.892912Z","iopub.status.idle":"2021-06-23T05:29:50.459054Z","shell.execute_reply.started":"2021-06-23T05:29:49.892877Z","shell.execute_reply":"2021-06-23T05:29:50.457979Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_cmp_stack(data=X_train_df, col_name=\"trafficSource.isTrueDirect\")","metadata":{"_uuid":"7960c22abb332ee2686d311fad2efd85e169da7f","execution":{"iopub.status.busy":"2021-06-23T05:29:50.460221Z","iopub.execute_input":"2021-06-23T05:29:50.460481Z","iopub.status.idle":"2021-06-23T05:29:50.87932Z","shell.execute_reply.started":"2021-06-23T05:29:50.460443Z","shell.execute_reply":"2021-06-23T05:29:50.878613Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_cmp_stack(data=X_train_df, col_name=\"trafficSource.keyword\")","metadata":{"_uuid":"22b379a0ee5eb0fc3a0e7bfeaffa44576e5eee16","execution":{"iopub.status.busy":"2021-06-23T05:29:50.880234Z","iopub.execute_input":"2021-06-23T05:29:50.880469Z","iopub.status.idle":"2021-06-23T05:29:51.601178Z","shell.execute_reply.started":"2021-06-23T05:29:50.880432Z","shell.execute_reply":"2021-06-23T05:29:51.600007Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_cmp_stack(data=X_train_df, col_name=\"trafficSource.referralPath\")","metadata":{"_uuid":"622e4b6e1fb45ff05d6fcb1cfff045f10d9e33f0","execution":{"iopub.status.busy":"2021-06-23T05:29:51.602433Z","iopub.execute_input":"2021-06-23T05:29:51.602696Z","iopub.status.idle":"2021-06-23T05:29:52.217587Z","shell.execute_reply.started":"2021-06-23T05:29:51.602651Z","shell.execute_reply":"2021-06-23T05:29:52.21616Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"to_drop_cols = [\"trafficSource.adContent\", \"trafficSource.adwordsClickInfo.adNetworkType\", \n \"trafficSource.adwordsClickInfo.gclId\", \"trafficSource.adwordsClickInfo.isVideoAd\",\n \"trafficSource.adwordsClickInfo.page\", \"trafficSource.adwordsClickInfo.slot\"]\n\nX_all_df.drop(to_drop_cols, axis=1, inplace=True)\n\nX_all_df.loc[:, \"trafficSource.isTrueDirect\"] = X_all_df.loc[:, \"trafficSource.isTrueDirect\"].fillna(0).astype('bool')","metadata":{"_uuid":"e9110448dc744b22bdc2bfc802945118c42e4616","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:29:52.219346Z","iopub.execute_input":"2021-06-23T05:29:52.219798Z","iopub.status.idle":"2021-06-23T05:29:52.309493Z","shell.execute_reply.started":"2021-06-23T05:29:52.219719Z","shell.execute_reply":"2021-06-23T05:29:52.308008Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ratio_nan(X_all_df)","metadata":{"_uuid":"5c04c69854dad54f9ad33667496bc057b079b66a","execution":{"iopub.status.busy":"2021-06-23T05:29:52.311163Z","iopub.execute_input":"2021-06-23T05:29:52.3115Z","iopub.status.idle":"2021-06-23T05:29:52.767428Z","shell.execute_reply.started":"2021-06-23T05:29:52.311437Z","shell.execute_reply":"2021-06-23T05:29:52.766191Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Note that the visit number of an instance is not neccessarily start from 1**","metadata":{"_uuid":"f337cb8520920832b6f49201ca9493818d7a167a"}},{"cell_type":"code","source":"X_all_df.loc[X_all_df[\"fullVisitorId\"] == \"0824839726118485274\", \"visitNumber\"].sort_values().head(3)","metadata":{"_uuid":"250272113f3fd93a1316b92eb0761a54c1f2e67b","execution":{"iopub.status.busy":"2021-06-23T05:29:52.768668Z","iopub.execute_input":"2021-06-23T05:29:52.768916Z","iopub.status.idle":"2021-06-23T05:29:52.942007Z","shell.execute_reply.started":"2021-06-23T05:29:52.768877Z","shell.execute_reply":"2021-06-23T05:29:52.940706Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.groupby(\"fullVisitorId\")[\"visitNumber\"].min().unique()","metadata":{"_uuid":"d1e70250fb42b412e14bc114abfb0a9e91e2a0f4","execution":{"iopub.status.busy":"2021-06-23T05:29:52.943321Z","iopub.execute_input":"2021-06-23T05:29:52.943711Z","iopub.status.idle":"2021-06-23T05:29:55.841195Z","shell.execute_reply.started":"2021-06-23T05:29:52.943652Z","shell.execute_reply":"2021-06-23T05:29:55.840276Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[X_all_df[\"channelGrouping\"] == \"Direct\", \"trafficSource.isTrueDirect\"].value_counts()","metadata":{"_uuid":"4ef26c75debf8810bdac5d1dd7b2c0d782357df2","execution":{"iopub.status.busy":"2021-06-23T05:29:55.842366Z","iopub.execute_input":"2021-06-23T05:29:55.842678Z","iopub.status.idle":"2021-06-23T05:29:55.865153Z","shell.execute_reply.started":"2021-06-23T05:29:55.842622Z","shell.execute_reply":"2021-06-23T05:29:55.86392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[X_all_df[\"trafficSource.isTrueDirect\"] == 1, \"channelGrouping\"].value_counts()","metadata":{"_uuid":"7f0c4ca50f17828b7ea4974493d6ceb1222205b9","execution":{"iopub.status.busy":"2021-06-23T05:29:55.866523Z","iopub.execute_input":"2021-06-23T05:29:55.866826Z","iopub.status.idle":"2021-06-23T05:29:55.888783Z","shell.execute_reply.started":"2021-06-23T05:29:55.86677Z","shell.execute_reply":"2021-06-23T05:29:55.88774Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[X_all_df[\"trafficSource.isTrueDirect\"] == 0, \"channelGrouping\"].value_counts()","metadata":{"_uuid":"03b4b9e67fc2c35ff734426dd82fb11d99ee1ff1","execution":{"iopub.status.busy":"2021-06-23T05:29:55.889819Z","iopub.execute_input":"2021-06-23T05:29:55.890143Z","iopub.status.idle":"2021-06-23T05:29:55.914982Z","shell.execute_reply.started":"2021-06-23T05:29:55.890082Z","shell.execute_reply":"2021-06-23T05:29:55.913821Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Here is the description from **Google**\n\n \n \n \n \n \n \n \n
trafficSource.isTrueDirectBOOLEANTrue if the source of the session was Direct (meaning the user typed the name of your website URL into the browser or came to your site via a bookmark), This field will also be true if 2 successive but distinct sessions have exactly the same campaign details. Otherwise NULL.
","metadata":{"_uuid":"73b23b2da16aecb8ccc6621eaa3f6989bd637ae1"}},{"cell_type":"code","source":"X_all_df.loc[X_all_df[\"trafficSource.isTrueDirect\"] == 1, \"trafficSource.medium\"].value_counts()","metadata":{"_uuid":"93e29532583afc068363246077272021c50344a4","execution":{"iopub.status.busy":"2021-06-23T05:29:55.916714Z","iopub.execute_input":"2021-06-23T05:29:55.917116Z","iopub.status.idle":"2021-06-23T05:29:55.93876Z","shell.execute_reply.started":"2021-06-23T05:29:55.917039Z","shell.execute_reply":"2021-06-23T05:29:55.937714Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[X_all_df[\"trafficSource.isTrueDirect\"] == 0, \"trafficSource.medium\"].value_counts()","metadata":{"_uuid":"c91410b854241b236588690e2db2e82abc2952e9","execution":{"iopub.status.busy":"2021-06-23T05:29:55.940026Z","iopub.execute_input":"2021-06-23T05:29:55.940326Z","iopub.status.idle":"2021-06-23T05:29:55.965667Z","shell.execute_reply.started":"2021-06-23T05:29:55.940271Z","shell.execute_reply":"2021-06-23T05:29:55.964578Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_df = X_all_df.loc[X_all_df[\"trafficSource.isTrueDirect\"] == 1, :]","metadata":{"_uuid":"7565df9e253af87377770e9285580b5a4fb8a9d5","execution":{"iopub.status.busy":"2021-06-23T05:29:55.966927Z","iopub.execute_input":"2021-06-23T05:29:55.96726Z","iopub.status.idle":"2021-06-23T05:29:56.022863Z","shell.execute_reply.started":"2021-06-23T05:29:55.967202Z","shell.execute_reply":"2021-06-23T05:29:56.021511Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_df.loc[ temp_df[\"trafficSource.medium\"] == 'referral', 'channelGrouping'].value_counts()","metadata":{"_uuid":"1da9e6e49c45a64dd61b3f24774bd88f895b2087","execution":{"iopub.status.busy":"2021-06-23T05:29:56.02416Z","iopub.execute_input":"2021-06-23T05:29:56.024445Z","iopub.status.idle":"2021-06-23T05:29:56.041602Z","shell.execute_reply.started":"2021-06-23T05:29:56.024381Z","shell.execute_reply":"2021-06-23T05:29:56.040513Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Conclusion: `trafficSource.isTrueDirect` have a lot wrong values and `trafficSource.medium` is highly redundant to `channelGrouping`.","metadata":{"_uuid":"68354fa0b91906a214b3b18fa06fa0776ab90074"}},{"cell_type":"code","source":"X_all_df.loc[X_all_df[\"trafficSource.keyword\"].notnull(), \"channelGrouping\"].value_counts()","metadata":{"scrolled":true,"_uuid":"dd83f5312777fdc2b84df2abb8641a11c45747d8","execution":{"iopub.status.busy":"2021-06-23T05:29:56.042568Z","iopub.execute_input":"2021-06-23T05:29:56.042799Z","iopub.status.idle":"2021-06-23T05:29:56.065862Z","shell.execute_reply.started":"2021-06-23T05:29:56.042762Z","shell.execute_reply":"2021-06-23T05:29:56.064916Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[X_all_df[\"trafficSource.keyword\"].isnull(), \"channelGrouping\"].value_counts()","metadata":{"_uuid":"985df20a1d8127691426ffe2e2541d771ffeec07","execution":{"iopub.status.busy":"2021-06-23T05:29:56.067105Z","iopub.execute_input":"2021-06-23T05:29:56.067639Z","iopub.status.idle":"2021-06-23T05:29:56.087906Z","shell.execute_reply.started":"2021-06-23T05:29:56.067581Z","shell.execute_reply":"2021-06-23T05:29:56.08678Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_df = X_all_df.loc[X_all_df[\"trafficSource.keyword\"].isnull(), :]\ntemp_df.loc[(temp_df[\"channelGrouping\"] == \"Organic Search\") | (temp_df[\"channelGrouping\"] == \"Paid Search\"), \"trafficSource.medium\"].value_counts()","metadata":{"_uuid":"758d04bd4f849aa84a641bb1bab1928b08ba22ff","execution":{"iopub.status.busy":"2021-06-23T05:29:56.089283Z","iopub.execute_input":"2021-06-23T05:29:56.089614Z","iopub.status.idle":"2021-06-23T05:29:56.175598Z","shell.execute_reply.started":"2021-06-23T05:29:56.089558Z","shell.execute_reply":"2021-06-23T05:29:56.174639Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[temp_df.index, \"trafficSource.keyword\"] = \"(not provided)\"","metadata":{"_uuid":"820fbd9a4c1b01255c613e63fba1915b38a80d84","execution":{"iopub.status.busy":"2021-06-23T05:29:56.177094Z","iopub.execute_input":"2021-06-23T05:29:56.177744Z","iopub.status.idle":"2021-06-23T05:29:56.253079Z","shell.execute_reply.started":"2021-06-23T05:29:56.177682Z","shell.execute_reply":"2021-06-23T05:29:56.251783Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[X_all_df[\"trafficSource.referralPath\"].isnull(), \"channelGrouping\"].value_counts()","metadata":{"_uuid":"0a3d2f1531fb3942f7984d3478115797854ab33a","execution":{"iopub.status.busy":"2021-06-23T05:29:56.25462Z","iopub.execute_input":"2021-06-23T05:29:56.255002Z","iopub.status.idle":"2021-06-23T05:29:56.279865Z","shell.execute_reply.started":"2021-06-23T05:29:56.254931Z","shell.execute_reply":"2021-06-23T05:29:56.278768Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_df = X_all_df.loc[X_all_df[\"trafficSource.referralPath\"].isnull(), :]","metadata":{"_uuid":"e13064a6d1cb50941c7d755abb2be0ff38846662","execution":{"iopub.status.busy":"2021-06-23T05:29:56.2811Z","iopub.execute_input":"2021-06-23T05:29:56.28137Z","iopub.status.idle":"2021-06-23T05:29:56.363252Z","shell.execute_reply.started":"2021-06-23T05:29:56.281323Z","shell.execute_reply":"2021-06-23T05:29:56.361647Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[temp_df.loc[(temp_df[\"channelGrouping\"] == \"Referral\") | (temp_df[\"channelGrouping\"] == \"Social\")].index, \"trafficSource.referralPath\"] = X_all_df[\"trafficSource.referralPath\"].value_counts().index[0]","metadata":{"_uuid":"c06fec41fb38bb3b5702bf5528228fb4e6fa3b91","execution":{"iopub.status.busy":"2021-06-23T05:29:56.364631Z","iopub.execute_input":"2021-06-23T05:29:56.364913Z","iopub.status.idle":"2021-06-23T05:29:56.39738Z","shell.execute_reply.started":"2021-06-23T05:29:56.364863Z","shell.execute_reply":"2021-06-23T05:29:56.396207Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[X_all_df[\"trafficSource.source\"] == \"(direct)\", \"channelGrouping\"].value_counts()","metadata":{"_uuid":"babb9af4d596de044184c57bc2256cf1a8abc7f4","execution":{"iopub.status.busy":"2021-06-23T05:29:56.398565Z","iopub.execute_input":"2021-06-23T05:29:56.398846Z","iopub.status.idle":"2021-06-23T05:29:56.4213Z","shell.execute_reply.started":"2021-06-23T05:29:56.398794Z","shell.execute_reply":"2021-06-23T05:29:56.420426Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df.loc[X_all_df[\"trafficSource.source\"] == \"(direct)\", \"channelGrouping\"] = \"Direct\"","metadata":{"_uuid":"8f49a6c857ce662f4b2277f0f270a7b7732fd811","execution":{"iopub.status.busy":"2021-06-23T05:29:56.422268Z","iopub.execute_input":"2021-06-23T05:29:56.422566Z","iopub.status.idle":"2021-06-23T05:29:56.441579Z","shell.execute_reply.started":"2021-06-23T05:29:56.4225Z","shell.execute_reply":"2021-06-23T05:29:56.44028Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_all_df[\"trafficSource.referralPath\"] = X_all_df[\"trafficSource.referralPath\"].cat.add_categories(['Not Referral'])\n\nX_all_df.loc[:, \"trafficSource.referralPath\"] = X_all_df.loc[:, \"trafficSource.referralPath\"].fillna('Not Referral')","metadata":{"_uuid":"a02f5b5ceb4d4a78316054a3d7ac94d3819d6314","execution":{"iopub.status.busy":"2021-06-23T05:29:56.442962Z","iopub.execute_input":"2021-06-23T05:29:56.443269Z","iopub.status.idle":"2021-06-23T05:29:56.497205Z","shell.execute_reply.started":"2021-06-23T05:29:56.443213Z","shell.execute_reply":"2021-06-23T05:29:56.496013Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def percentage_counts(data, col_name):\n total = data[col_name].count()\n return data[col_name].value_counts()/ total * 100","metadata":{"_uuid":"8b5cced822495d69f2366ebd0ca7081046387b1f","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:29:56.498371Z","iopub.execute_input":"2021-06-23T05:29:56.498642Z","iopub.status.idle":"2021-06-23T05:29:56.504628Z","shell.execute_reply.started":"2021-06-23T05:29:56.498596Z","shell.execute_reply":"2021-06-23T05:29:56.503433Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def keep_greater_than_percentage_entries(data, col_name, percentage=0.1, new_entry=\"Others\"):\n percentage_counts_series = percentage_counts(temp_df, col_name)\n entries_to_keep = percentage_counts_series[percentage_counts_series >= percentage].index\n data[col_name] = data[col_name].cat.add_categories([new_entry])\n data.loc[~data[col_name].isin(entries_to_keep), col_name] = new_entry\n data[col_name] = data[col_name].cat.remove_unused_categories()","metadata":{"_uuid":"6fe8c68f7c62e5cbf53cae714a6d0d68f415f713","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:29:56.506026Z","iopub.execute_input":"2021-06-23T05:29:56.506468Z","iopub.status.idle":"2021-06-23T05:29:56.517386Z","shell.execute_reply.started":"2021-06-23T05:29:56.506371Z","shell.execute_reply":"2021-06-23T05:29:56.51642Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"features_to_merge = [\n 'device.browser', 'geoNetwork.city', 'geoNetwork.country',\n 'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region',\n 'geoNetwork.subContinent', 'trafficSource.campaign', 'trafficSource.keyword',\n 'trafficSource.referralPath', 'trafficSource.source', 'device.operatingSystem'\n ]","metadata":{"_uuid":"d76355208e381402d3137dc5991cb1375262adb8","execution":{"iopub.status.busy":"2021-06-23T05:29:56.518659Z","iopub.execute_input":"2021-06-23T05:29:56.518957Z","iopub.status.idle":"2021-06-23T05:29:56.53052Z","shell.execute_reply.started":"2021-06-23T05:29:56.518905Z","shell.execute_reply":"2021-06-23T05:29:56.529358Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for feature in features_to_merge:\n keep_greater_than_percentage_entries(data=X_all_df, col_name=feature)","metadata":{"_uuid":"97201aa26a3681b4dec44fe2df2cfc55eb0c3cfd","execution":{"iopub.status.busy":"2021-06-23T05:29:56.531629Z","iopub.execute_input":"2021-06-23T05:29:56.531874Z","iopub.status.idle":"2021-06-23T05:29:58.050311Z","shell.execute_reply.started":"2021-06-23T05:29:56.531828Z","shell.execute_reply":"2021-06-23T05:29:58.04936Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train_df = X_all_df.loc[X_all_df[\"totals.transactionRevenue_ln\"].notnull(), :].reset_index(drop=True)\nX_test_df = X_all_df.loc[X_all_df[\"totals.transactionRevenue_ln\"].isnull(), :].reset_index(drop=True)","metadata":{"_uuid":"8de9712c48e9a23a4b59f7ea7b96893d2407be21","execution":{"iopub.status.busy":"2021-06-23T05:29:58.051411Z","iopub.execute_input":"2021-06-23T05:29:58.051687Z","iopub.status.idle":"2021-06-23T05:30:04.684307Z","shell.execute_reply.started":"2021-06-23T05:29:58.051637Z","shell.execute_reply":"2021-06-23T05:30:04.683159Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Features Analysis\n---\nAfter all efforts in preprocessing, we can now look into the features we will deal with. As this is a iterative developing workflow, we will go through some features at first time and focus on the top three most important features.","metadata":{"_uuid":"0d56bec4d77c99126257cc8b12d0b9d79344edd3"}},{"cell_type":"markdown","source":"## Analysis Base On Date","metadata":{"_uuid":"4dbbb7b77c3987841d33e8c1a6be4cebd96dc47a"}},{"cell_type":"code","source":"X_train_df.groupby(\"date\")[\"totals.transactionRevenue_ln\"].agg(['sum', 'count', 'mean']).plot(subplots=True, sharex=True, title=\"Revenue Base On Date\", linewidth=2)","metadata":{"_uuid":"6626ec4b8e5868c61ebe23a775d46cdbcbe79080","execution":{"iopub.status.busy":"2021-06-23T05:30:04.685513Z","iopub.execute_input":"2021-06-23T05:30:04.685785Z","iopub.status.idle":"2021-06-23T05:30:05.966413Z","shell.execute_reply.started":"2021-06-23T05:30:04.685733Z","shell.execute_reply":"2021-06-23T05:30:05.964997Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"From the data revenue plots we can tell that, from the middle of November till near the Christmas, there is a surge both in visit counts and sum up revenue. The visit counts drop significantly after the Christmas.","metadata":{"_uuid":"a29da1537d3a719b00e5702bdadec40905940b05","trusted":true}},{"cell_type":"markdown","source":"## Traffic Source Analysis","metadata":{"_uuid":"f7eeeb039ff3369032e284fe7b64200171eed18c"}},{"cell_type":"code","source":"sub_boxenplots(x='channelGrouping', data=X_train_df)","metadata":{"_uuid":"5b77e4017ce6a01b72ad1eca9766b3669aaf2820","execution":{"iopub.status.busy":"2021-06-23T05:30:05.968042Z","iopub.execute_input":"2021-06-23T05:30:05.968433Z","iopub.status.idle":"2021-06-23T05:30:06.796915Z","shell.execute_reply.started":"2021-06-23T05:30:05.968338Z","shell.execute_reply":"2021-06-23T05:30:06.795783Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"From the plot we can infer that the instances who comes from `referral` website has the highest ritio of non-zero revenue, while the instances come from `Display` channel have the highest median non-zero revenue.","metadata":{"_uuid":"4459009d383325e32cc97534f1f285f346ac5048"}},{"cell_type":"code","source":"sub_boxenplots(x='device.deviceCategory', data=X_train_df)","metadata":{"_uuid":"a5bba1ce30b1602d440930fc1f568dba78f4da79","execution":{"iopub.status.busy":"2021-06-23T05:30:06.798307Z","iopub.execute_input":"2021-06-23T05:30:06.798615Z","iopub.status.idle":"2021-06-23T05:30:07.431958Z","shell.execute_reply.started":"2021-06-23T05:30:06.798558Z","shell.execute_reply":"2021-06-23T05:30:07.43093Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"People who use the desktop are still the main income source of GA store.","metadata":{"_uuid":"563b3ad987c4312161d2e4e290f8c6ff1bddbdab"}},{"cell_type":"code","source":"sub_boxenplots(x='device.isMobile', data=X_train_df)","metadata":{"_uuid":"036ccc6c786018ea019cac6be5c0cccc66609867","execution":{"iopub.status.busy":"2021-06-23T05:30:07.433084Z","iopub.execute_input":"2021-06-23T05:30:07.433377Z","iopub.status.idle":"2021-06-23T05:30:08.012268Z","shell.execute_reply.started":"2021-06-23T05:30:07.433321Z","shell.execute_reply":"2021-06-23T05:30:08.01102Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This information might be redundant to the previous one `device.deviceCategory`. Drop this feature if the feature importance is not high.","metadata":{"_uuid":"9c687ccb336c3e78157e3f20644781f17d74eace"}},{"cell_type":"code","source":"sub_boxenplots(x='geoNetwork.continent', data=X_train_df)","metadata":{"_uuid":"b841d5866da6b614a26b9851d1af04c4f4b129b4","execution":{"iopub.status.busy":"2021-06-23T05:30:08.013591Z","iopub.execute_input":"2021-06-23T05:30:08.013894Z","iopub.status.idle":"2021-06-23T05:30:08.775654Z","shell.execute_reply.started":"2021-06-23T05:30:08.013838Z","shell.execute_reply":"2021-06-23T05:30:08.774165Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Americas are still the main market for the GA store but among the group who spends the money that `Africa` has the most dense non-zero nature log revenue within the range of 18-22.","metadata":{"_uuid":"ae52e6af91c3180e372cd2ec5e54955ef956c024"}},{"cell_type":"code","source":"sub_boxenplots(x='totals.newVisits', data=X_train_df)","metadata":{"_uuid":"ab9929f1ad56794a64c9bd1ea60423430ce07089","execution":{"iopub.status.busy":"2021-06-23T05:30:08.777317Z","iopub.execute_input":"2021-06-23T05:30:08.777629Z","iopub.status.idle":"2021-06-23T05:30:09.376327Z","shell.execute_reply.started":"2021-06-23T05:30:08.777579Z","shell.execute_reply":"2021-06-23T05:30:09.37515Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sub_boxenplots(x='trafficSource.isTrueDirect', data=X_train_df)","metadata":{"_uuid":"c03142da3c7ed98e2de4605494753b521f36bf97","execution":{"iopub.status.busy":"2021-06-23T05:30:09.377635Z","iopub.execute_input":"2021-06-23T05:30:09.377895Z","iopub.status.idle":"2021-06-23T05:30:09.964764Z","shell.execute_reply.started":"2021-06-23T05:30:09.377847Z","shell.execute_reply":"2021-06-23T05:30:09.96348Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Above two binary features are quite important and intuitive as well. An old user is more likely to make a purchase than a new user. Those who visit website directly are more objective than the other means.","metadata":{"_uuid":"a5e583ac2b8709c04bec70d7482cf917cc92bfc1"}},{"cell_type":"code","source":"sub_boxenplots(x='trafficSource.campaign', data=X_train_df, rot=20)","metadata":{"_uuid":"f42730d654e7003ede1c73953d3446474b4a8eb5","execution":{"iopub.status.busy":"2021-06-23T05:30:09.966095Z","iopub.execute_input":"2021-06-23T05:30:09.966366Z","iopub.status.idle":"2021-06-23T05:30:10.666191Z","shell.execute_reply.started":"2021-06-23T05:30:09.966318Z","shell.execute_reply":"2021-06-23T05:30:10.664972Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sub_boxenplots(x='trafficSource.medium', data=X_train_df)","metadata":{"_uuid":"543c5eded611ace6123186b4d563db908719df79","execution":{"iopub.status.busy":"2021-06-23T05:30:10.667456Z","iopub.execute_input":"2021-06-23T05:30:10.66772Z","iopub.status.idle":"2021-06-23T05:30:11.425089Z","shell.execute_reply.started":"2021-06-23T05:30:10.667673Z","shell.execute_reply":"2021-06-23T05:30:11.424046Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sub_boxenplots(x='device.operatingSystem', data=X_train_df, rot=20)","metadata":{"_uuid":"c2f6c0282ffcc240523c6a19618de4eafe8f6590","execution":{"iopub.status.busy":"2021-06-23T05:30:11.426258Z","iopub.execute_input":"2021-06-23T05:30:11.426553Z","iopub.status.idle":"2021-06-23T05:30:12.202748Z","shell.execute_reply.started":"2021-06-23T05:30:11.426499Z","shell.execute_reply":"2021-06-23T05:30:12.201504Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We can tell that the people who choose `Chrome OS` will contribute slightly more than other operating system. ","metadata":{"_uuid":"708c2db7a56c9e37c1331d99862a8b2f2209227f","trusted":true}},{"cell_type":"markdown","source":"","metadata":{"_uuid":"7549d10826ed9fa58429ee9b66c3cef6a609ed48"}},{"cell_type":"markdown","source":"## Feature Explore On Import Features\nThe features we explore below will be the features that _LightGBM_ thinks it is important. Let's find out are they make sense to us.","metadata":{"_uuid":"44d26112e4c963898dcf837ecbe06603f6c98e76"}},{"cell_type":"code","source":"g = sns.jointplot('totals.hits', 'totals.transactionRevenue_ln', data=X_train_df[X_train_df['totals.transactionRevenue_ln'] > 0],\n kind='reg', height=10)","metadata":{"_uuid":"fbbe88addd778277eb8d0bec8143c00e31ef20cc","execution":{"iopub.status.busy":"2021-06-23T05:30:12.203927Z","iopub.execute_input":"2021-06-23T05:30:12.204173Z","iopub.status.idle":"2021-06-23T05:30:13.826947Z","shell.execute_reply.started":"2021-06-23T05:30:12.204128Z","shell.execute_reply":"2021-06-23T05:30:13.825993Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"g = sns.jointplot('visitStartTime', 'totals.transactionRevenue_ln', data=X_train_df[X_train_df['totals.transactionRevenue_ln'] > 0],\n kind='reg', height=10)","metadata":{"_uuid":"9040b62769dab4d2478667b79561cdefe31a0a69","execution":{"iopub.status.busy":"2021-06-23T05:30:13.828356Z","iopub.execute_input":"2021-06-23T05:30:13.828838Z","iopub.status.idle":"2021-06-23T05:30:15.379566Z","shell.execute_reply.started":"2021-06-23T05:30:13.828782Z","shell.execute_reply":"2021-06-23T05:30:15.378631Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"g = sns.jointplot('totals.pageviews', 'totals.transactionRevenue_ln', data=X_train_df[X_train_df['totals.transactionRevenue_ln'] > 0],\n kind='reg', height=10)","metadata":{"_uuid":"83015db0594f4a4a2a33c5dca234f53258eb554d","execution":{"iopub.status.busy":"2021-06-23T05:30:15.380924Z","iopub.execute_input":"2021-06-23T05:30:15.381455Z","iopub.status.idle":"2021-06-23T05:30:16.916925Z","shell.execute_reply.started":"2021-06-23T05:30:15.381375Z","shell.execute_reply":"2021-06-23T05:30:16.916097Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Observation:** Above are the most three important features that _LightGBM_ thinks that are important. `pageviews` and `hits` make sense while `visitStartTime` seems a big mistake. Like Teo mentioned during our presentation that, a single algorithm will have its bias.","metadata":{"_uuid":"22ecfd888bad6cbd9592df157dab04d3ef8d840e","trusted":true}},{"cell_type":"markdown","source":"## Feature Encoding\n---\nString feature is not usable for machine learning algorithm. We need to encode them to either numeric featuers or one-hot encoding features. This deponds on the machine learning algorithm you choose. Before we start, we need to find out the category of the features. Unfortinately, it has to be done by hand pick.","metadata":{"_uuid":"637b12c5f98d94b71d27616842e076313c39fe90"}},{"cell_type":"code","source":"cat_feature_list = ['channelGrouping', 'device.browser', 'device.deviceCategory', 'device.operatingSystem', \n 'geoNetwork.city', 'geoNetwork.continent', 'geoNetwork.country', 'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region', 'geoNetwork.subContinent',\n 'trafficSource.campaign', 'trafficSource.isTrueDirect', 'trafficSource.keyword', 'trafficSource.medium', 'trafficSource.referralPath', 'trafficSource.source'\n ]\nbool_feature_list = ['device.isMobile', 'totals.newVisits']\nid_feature_list = ['fullVisitorId']\nnum_feature_list = ['visitNumber', 'totals.hits', 'totals.pageviews', 'visitStartTime']\ntime_feature_list = ['date']\nlabel_list = ['totals.transactionRevenue', 'totals.transactionRevenue_ln']","metadata":{"_uuid":"eeee3077d428017036180d83e09b8816433fe8c8","_kg_hide-input":false,"execution":{"iopub.status.busy":"2021-06-23T05:30:16.918369Z","iopub.execute_input":"2021-06-23T05:30:16.91895Z","iopub.status.idle":"2021-06-23T05:30:16.927387Z","shell.execute_reply.started":"2021-06-23T05:30:16.918886Z","shell.execute_reply":"2021-06-23T05:30:16.926493Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Check if any features are missing.","metadata":{"_uuid":"c6b5770a7a992285850ed4cea0f16528b1dac3e1"}},{"cell_type":"code","source":"def check_features():\n found = True\n total_feature_list = id_feature_list + label_list + cat_feature_list + num_feature_list + bool_feature_list + time_feature_list\n for feature in X_train_df.columns:\n if feature not in total_feature_list:\n found = False\n assert found, \"You forgot \" + feature\n total_feature_list.remove(feature)\n if found:\n if total_feature_list == []:\n print(\"All the features are found!\")\n else:\n print(\"There are features left: \", total_feature_list)\ntry:\n check_features()\nexcept AssertionError as e:\n print(e)","metadata":{"_uuid":"7ea8e369f6abd6d0a8560e3108a372aae4560c5b","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:30:16.928699Z","iopub.execute_input":"2021-06-23T05:30:16.928984Z","iopub.status.idle":"2021-06-23T05:30:16.952266Z","shell.execute_reply.started":"2021-06-23T05:30:16.92893Z","shell.execute_reply":"2021-06-23T05:30:16.950678Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Rearrange the features by column categories.","metadata":{"_uuid":"b13ba016cb1d44e8bda61ec4ee0592ccca3e0355"}},{"cell_type":"code","source":"X_all_df = X_all_df[id_feature_list + label_list + cat_feature_list + num_feature_list + bool_feature_list + time_feature_list]","metadata":{"_uuid":"e22d364cf3f4f97a1b62337cbce6663ca7738d87","execution":{"iopub.status.busy":"2021-06-23T05:30:16.953515Z","iopub.execute_input":"2021-06-23T05:30:16.953946Z","iopub.status.idle":"2021-06-23T05:30:17.013917Z","shell.execute_reply.started":"2021-06-23T05:30:16.953875Z","shell.execute_reply":"2021-06-23T05:30:17.013126Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.preprocessing import LabelEncoder","metadata":{"_uuid":"6302415e5cd0b6f91cb222eaded0e18ceac8c8d3","scrolled":true,"execution":{"iopub.status.busy":"2021-06-23T05:30:17.01495Z","iopub.execute_input":"2021-06-23T05:30:17.015343Z","iopub.status.idle":"2021-06-23T05:30:17.109284Z","shell.execute_reply.started":"2021-06-23T05:30:17.015301Z","shell.execute_reply":"2021-06-23T05:30:17.108557Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def cat_feature_encoding(data, cat_feature_list):\n feature_encoder_dict = {}\n with tqdm(cat_feature_list, desc=cat_feature_list[0]) as t:\n for cat_feature in t:\n if cat_feature == \"fullVisitorId\": # leave the ID as it is \n continue\n t.set_description_str(cat_feature)\n my_label_encoder = LabelEncoder()\n encoded_col = my_label_encoder.fit_transform(data[cat_feature])\n data.loc[:, cat_feature] = encoded_col\n feature_encoder_dict[cat_feature] = my_label_encoder\n return feature_encoder_dict","metadata":{"_uuid":"b4ebee1c4e2f22aa259317b16de024584c538ac5","execution":{"iopub.status.busy":"2021-06-23T05:30:17.110286Z","iopub.execute_input":"2021-06-23T05:30:17.110748Z","iopub.status.idle":"2021-06-23T05:30:17.117096Z","shell.execute_reply.started":"2021-06-23T05:30:17.110705Z","shell.execute_reply":"2021-06-23T05:30:17.116229Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now split the X_all_df to training and testing dataset again.","metadata":{"_uuid":"01d83e8cc6ba63ce1af51d1583053add8f5b4640"}},{"cell_type":"code","source":"X_train_df = X_all_df.loc[X_all_df[\"totals.transactionRevenue_ln\"].notnull(), :].reset_index(drop=True)\nX_test_df = X_all_df.loc[X_all_df[\"totals.transactionRevenue_ln\"].isnull(), :].reset_index(drop=True)","metadata":{"_uuid":"6911f40c453f48b1cf48e4e78a24a6bd44b61e78","execution":{"iopub.status.busy":"2021-06-23T05:30:17.118317Z","iopub.execute_input":"2021-06-23T05:30:17.118724Z","iopub.status.idle":"2021-06-23T05:30:17.302244Z","shell.execute_reply.started":"2021-06-23T05:30:17.118683Z","shell.execute_reply":"2021-06-23T05:30:17.300791Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"_uuid":"3e304f199ddb74ca1f2c21df9a868ddde2ce85df","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Feature Engineering \n**What is going to be achieved in this section:**\n\n* Create custom features\n* Utilize the [featuretools library](https://www.featuretools.com/) to create useful features\n* Merge them together ","metadata":{"_uuid":"3850c852d434e774d230105febbc0e2d657f5521","trusted":true}},{"cell_type":"code","source":"train_df = X_train_df\ntest_df = X_test_df","metadata":{"_uuid":"72598f6142a888b87940dd624c63b38766e2af1b","execution":{"iopub.status.busy":"2021-06-23T05:30:17.30369Z","iopub.execute_input":"2021-06-23T05:30:17.303956Z","iopub.status.idle":"2021-06-23T05:30:17.308338Z","shell.execute_reply.started":"2021-06-23T05:30:17.30391Z","shell.execute_reply":"2021-06-23T05:30:17.307259Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def printShapes(name1, df1, name2, df2):\n print(name1, df1.shape)\n print(name2, df2.shape)\n \ndef ratio_nan(data):\n num_data = data.shape[0]\n null_sum = data.isnull().sum()\n null_val_features = null_sum[null_sum > 0]\n print(null_val_features/num_data)\n \ndef compare_nan(name1, df1, name2, df2):\n print(name1)\n print(ratio_nan(df1))\n print(\"\\n\" + name2)\n print(ratio_nan(df2))\n \nimport datetime as dt\ndef toPandasTimestamp(val):\n lDate = dt.datetime.fromtimestamp(val)\n lTimestamp = pd.Timestamp(lDate)\n return lTimestamp\n \ndef printTTShapes():\n printShapes(\"Train Shape:\", train_df, \"Test Shape:\", test_df)","metadata":{"_kg_hide-input":true,"_uuid":"fa14751b250c442d5195a44650bd627a3f2d0ba8","execution":{"iopub.status.busy":"2021-06-23T05:30:17.309798Z","iopub.execute_input":"2021-06-23T05:30:17.31006Z","iopub.status.idle":"2021-06-23T05:30:17.32494Z","shell.execute_reply.started":"2021-06-23T05:30:17.310012Z","shell.execute_reply":"2021-06-23T05:30:17.324138Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It is useful to create a category that is a pandas timestamp for feature tools library to use. Here we do that.","metadata":{"_uuid":"d36453b0d2690e034e75e45959c743a73a13b6ce"}},{"cell_type":"code","source":"train_df[\"visitStartTimestamp\"] = train_df[\"visitStartTime\"].apply(toPandasTimestamp)\ntest_df[\"visitStartTimestamp\"] = test_df[\"visitStartTime\"].apply(toPandasTimestamp)","metadata":{"_kg_hide-input":false,"_uuid":"91e3ec4067f87259137e1904ea5081a1dd11e499","execution":{"iopub.status.busy":"2021-06-23T05:30:17.326199Z","iopub.execute_input":"2021-06-23T05:30:17.326686Z","iopub.status.idle":"2021-06-23T05:30:22.170936Z","shell.execute_reply.started":"2021-06-23T05:30:17.326641Z","shell.execute_reply":"2021-06-23T05:30:22.170071Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"printTTShapes()","metadata":{"_kg_hide-input":true,"_uuid":"c25ac357749b2a4b2c451520303b41165c044fa1","execution":{"iopub.status.busy":"2021-06-23T05:30:22.172265Z","iopub.execute_input":"2021-06-23T05:30:22.172793Z","iopub.status.idle":"2021-06-23T05:30:22.178441Z","shell.execute_reply.started":"2021-06-23T05:30:22.172745Z","shell.execute_reply":"2021-06-23T05:30:22.17683Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"compare_nan(\"Train\", train_df, \"Test\", test_df)","metadata":{"_kg_hide-input":true,"_uuid":"8984c02f148e54cd2b41d92d2fba49c6b660899c","execution":{"iopub.status.busy":"2021-06-23T05:30:22.180104Z","iopub.execute_input":"2021-06-23T05:30:22.180539Z","iopub.status.idle":"2021-06-23T05:30:22.673509Z","shell.execute_reply.started":"2021-06-23T05:30:22.180441Z","shell.execute_reply":"2021-06-23T05:30:22.672525Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df[\"totals.transactionRevenue\"] = train_df[\"totals.transactionRevenue\"].apply(lambda x: 0.0 if np.isnan(x) else x) \ntest_df[\"totals.transactionRevenue\"] = test_df[\"totals.transactionRevenue\"].apply(lambda x: 0.0 if np.isnan(x) else x)","metadata":{"_uuid":"faeb0a4aa6e093acd4d4fd851c44640b95d2c693","execution":{"iopub.status.busy":"2021-06-23T05:30:22.674747Z","iopub.execute_input":"2021-06-23T05:30:22.675024Z","iopub.status.idle":"2021-06-23T05:30:24.436136Z","shell.execute_reply.started":"2021-06-23T05:30:22.67497Z","shell.execute_reply":"2021-06-23T05:30:24.434909Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Custom Feature Engineering\n\nIn trying to engineer some new features, I came up with these ideas:\n\n1. Difference in time between min and max visitStartTimes\n2. Number of seconds between hits (a lot will just be zero)\n3. Number of seconds between visits\n4. Number of seconds between pageviews\n\nThe following section will focus on creating these features.","metadata":{"_uuid":"56097222b08d8c73d02de1549201885980280f7b"}},{"cell_type":"code","source":"columnsOfInterest = [\"fullVisitorId\", \"visitStartTime\", \"totals.hits\", \"totals.pageviews\", \"visitNumber\"]\ntrain_grouped = train_df[columnsOfInterest].groupby(\"fullVisitorId\")\ntest_grouped = test_df[columnsOfInterest].groupby(\"fullVisitorId\")","metadata":{"_uuid":"4f7e15fa975a8fe1151fd13b6068d4062d0c7b6e","execution":{"iopub.status.busy":"2021-06-23T05:30:24.437542Z","iopub.execute_input":"2021-06-23T05:30:24.43785Z","iopub.status.idle":"2021-06-23T05:30:24.479788Z","shell.execute_reply.started":"2021-06-23T05:30:24.437793Z","shell.execute_reply":"2021-06-23T05:30:24.478896Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_ds = train_grouped[\"visitStartTime\"].max() - train_grouped[\"visitStartTime\"].min() # ds difference in seconds\ntest_ds = test_grouped[\"visitStartTime\"].max() - test_grouped[\"visitStartTime\"].min()\n\ntrain_sh = train_grouped[\"totals.hits\"].sum() # sh - summed hits\ntrain_spv = train_grouped[\"totals.pageviews\"].sum() # spv - summed page views\ntrain_lv = train_grouped[\"visitNumber\"].max() #lv - largestVisit number\ntest_sh = test_grouped[\"totals.hits\"].sum() # sh - summed hits\ntest_spv = test_grouped[\"totals.pageviews\"].sum() # spv - summed page views\ntest_lv = test_grouped[\"visitNumber\"].max() #lv - largestVisit number\n\ntrain_seconds_per_hit = train_ds / train_sh\ntrain_seconds_per_pageview = train_ds / train_spv \ntrain_seconds_per_visit = train_ds / train_lv\ntest_seconds_per_hit = test_ds / test_sh\ntest_seconds_per_pageview = test_ds / test_spv \ntest_seconds_per_visit = test_ds / test_lv\n\ntrain_nf = pd.concat([train_ds, train_seconds_per_hit, train_seconds_per_pageview, train_seconds_per_visit], \n axis = 1, join = \"outer\", \n join_axes = [train_ds.reset_index()[\"fullVisitorId\"]])\ntest_nf= pd.concat([test_ds, test_seconds_per_hit, test_seconds_per_pageview, test_seconds_per_visit], \n axis = 1, join = \"outer\", \n join_axes = [test_ds.reset_index()[\"fullVisitorId\"]])\n\ntrain_nf.reset_index(inplace = True)\ntrain_nf.columns = [\"fullVisitorId\", \"visitTimeRange\", \"secondsPerHit\", \"secondsPerPageview\", \"secondsPerVisit\"]\ntest_nf.reset_index(inplace = True)\ntest_nf.columns = [\"fullVisitorId\", \"visitTimeRange\", \"secondsPerHit\", \"secondsPerPageview\", \"secondsPerVisit\"]","metadata":{"_kg_hide-input":true,"_uuid":"d84c7391da8d5918f5764005a7d6835a40325b1d","execution":{"iopub.status.busy":"2021-06-23T05:30:24.481813Z","iopub.execute_input":"2021-06-23T05:30:24.482221Z","iopub.status.idle":"2021-06-23T05:30:27.689102Z","shell.execute_reply.started":"2021-06-23T05:30:24.482143Z","shell.execute_reply":"2021-06-23T05:30:27.688024Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"printShapes(\"Train New Features:\", train_nf, \"Test New Features:\", test_nf)","metadata":{"_uuid":"0c80a1b094b331c964b073478637546d2023472c","execution":{"iopub.status.busy":"2021-06-23T05:30:27.690221Z","iopub.execute_input":"2021-06-23T05:30:27.690527Z","iopub.status.idle":"2021-06-23T05:30:27.696364Z","shell.execute_reply.started":"2021-06-23T05:30:27.69047Z","shell.execute_reply":"2021-06-23T05:30:27.694965Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Ensure that we get rid of all NaNs","metadata":{"_uuid":"70fa15d0a76c126a2e743c0486a398019578ac6b"}},{"cell_type":"code","source":"compare_nan(\"Train New Features\", train_nf, \"Test New Features\", test_nf)","metadata":{"_uuid":"115727012c30c77d1d0d08600adebf3aede9ab42","execution":{"iopub.status.busy":"2021-06-23T05:30:27.69804Z","iopub.execute_input":"2021-06-23T05:30:27.698524Z","iopub.status.idle":"2021-06-23T05:30:27.936627Z","shell.execute_reply.started":"2021-06-23T05:30:27.698445Z","shell.execute_reply":"2021-06-23T05:30:27.935083Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_nf = train_nf.apply(lambda x: x.fillna(0) if x.dtype.kind in 'biufc' else x.fillna('.'))\ntest_nf = test_nf.apply(lambda x: x.fillna(0) if x.dtype.kind in 'biufc' else x.fillna('.'))","metadata":{"_uuid":"d66a8b93dfc281e5a56358d5fbcdc7a0ff24353a","execution":{"iopub.status.busy":"2021-06-23T05:30:27.938247Z","iopub.execute_input":"2021-06-23T05:30:27.938651Z","iopub.status.idle":"2021-06-23T05:30:28.683198Z","shell.execute_reply.started":"2021-06-23T05:30:27.938574Z","shell.execute_reply":"2021-06-23T05:30:28.681825Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"compare_nan(\"Train New Features\", train_nf, \"Test New Features\", test_nf)","metadata":{"_uuid":"8d4f79307b8b0e030d840a31ac94a8b91f58489f","execution":{"iopub.status.busy":"2021-06-23T05:30:28.68453Z","iopub.execute_input":"2021-06-23T05:30:28.684771Z","iopub.status.idle":"2021-06-23T05:30:28.912276Z","shell.execute_reply.started":"2021-06-23T05:30:28.684734Z","shell.execute_reply":"2021-06-23T05:30:28.911003Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_nf.head()","metadata":{"_uuid":"7afbb123ea84b9f372f944195a642312efa78bfb","execution":{"iopub.status.busy":"2021-06-23T05:30:28.913582Z","iopub.execute_input":"2021-06-23T05:30:28.91382Z","iopub.status.idle":"2021-06-23T05:30:28.938391Z","shell.execute_reply.started":"2021-06-23T05:30:28.913784Z","shell.execute_reply":"2021-06-23T05:30:28.936641Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_nf.head()","metadata":{"_uuid":"2edcd9c4c75c4ff941ac42a531f166966ba0c3c2","execution":{"iopub.status.busy":"2021-06-23T05:30:28.939902Z","iopub.execute_input":"2021-06-23T05:30:28.94031Z","iopub.status.idle":"2021-06-23T05:30:28.967742Z","shell.execute_reply.started":"2021-06-23T05:30:28.940233Z","shell.execute_reply":"2021-06-23T05:30:28.966239Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Feature Tools\n\nHere we utilize the feature tools library to generate some features we know are good. Previously, we have trained on all columns with all entries. However, that takes over two hours to run. So, once we did that and found the best features, we have tried to narrow it down to reduce run time and make our lives easier. ","metadata":{"_uuid":"5f06cd0b76d532add48b10b3671f7cfcf15238c0"}},{"cell_type":"code","source":"fm_columns = [\"fullVisitorId\", \"totals.pageviews\", \"totals.hits\", \"visitStartTimestamp\", \"geoNetwork.city\"]","metadata":{"_uuid":"2865a86d740698a02c358b83a91f6f87291adf0d","execution":{"iopub.status.busy":"2021-06-23T05:30:28.969378Z","iopub.execute_input":"2021-06-23T05:30:28.969693Z","iopub.status.idle":"2021-06-23T05:30:28.97507Z","shell.execute_reply.started":"2021-06-23T05:30:28.969644Z","shell.execute_reply":"2021-06-23T05:30:28.973788Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ftrain_df = train_df[fm_columns].reset_index()\nftest_df = test_df[fm_columns].reset_index()","metadata":{"_uuid":"474b7a42ed36d3007e57cfe2f715b3aab47f90c5","execution":{"iopub.status.busy":"2021-06-23T05:30:28.976247Z","iopub.execute_input":"2021-06-23T05:30:28.976702Z","iopub.status.idle":"2021-06-23T05:30:29.044324Z","shell.execute_reply.started":"2021-06-23T05:30:28.976607Z","shell.execute_reply":"2021-06-23T05:30:29.043096Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ftrain_df.head()","metadata":{"_uuid":"1730ce6f599ff5d5ce210cd9c5d5f2bea7484d2d","execution":{"iopub.status.busy":"2021-06-23T05:30:29.04565Z","iopub.execute_input":"2021-06-23T05:30:29.04597Z","iopub.status.idle":"2021-06-23T05:30:29.072281Z","shell.execute_reply.started":"2021-06-23T05:30:29.045912Z","shell.execute_reply":"2021-06-23T05:30:29.070778Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ftest_df.head()","metadata":{"_uuid":"f45f7237ee1b5ef7791c08a5a075da823e2aace7","execution":{"iopub.status.busy":"2021-06-23T05:30:29.074132Z","iopub.execute_input":"2021-06-23T05:30:29.074486Z","iopub.status.idle":"2021-06-23T05:30:29.101631Z","shell.execute_reply.started":"2021-06-23T05:30:29.074421Z","shell.execute_reply":"2021-06-23T05:30:29.100151Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### EntitySets\nFeaturetools uses EntitySets to represent dataframes and the relationships between them. Here we create an entity set from our whole dataframe and then pull another dataframe out from that (using fullVisitorId). This is very powerful and later allows us to automatically use grouped information. ","metadata":{"_uuid":"e406a018544f208220331e2ee355512edc42745d"}},{"cell_type":"code","source":"import featuretools as ft\nfrom featuretools import variable_types as vtype\n\nmy_variable_types = {\n \"fullVisitorId\" : vtype.Id,\n \"geoNetwork.city\" : vtype.Categorical\n}","metadata":{"_uuid":"907c140038fa5357d7877f8ae3b5ffdf97d100e2","execution":{"iopub.status.busy":"2021-06-23T05:30:29.103072Z","iopub.execute_input":"2021-06-23T05:30:29.103486Z","iopub.status.idle":"2021-06-23T05:30:29.502973Z","shell.execute_reply.started":"2021-06-23T05:30:29.103394Z","shell.execute_reply":"2021-06-23T05:30:29.501736Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_es = ft.EntitySet(id = \"train_data\")\ntest_es = ft.EntitySet(id = \"test_data\")","metadata":{"_uuid":"c5170ed3235e0c29742bc5619c3120b68b926a4c","execution":{"iopub.status.busy":"2021-06-23T05:30:29.504136Z","iopub.execute_input":"2021-06-23T05:30:29.504414Z","iopub.status.idle":"2021-06-23T05:30:29.50965Z","shell.execute_reply.started":"2021-06-23T05:30:29.50435Z","shell.execute_reply":"2021-06-23T05:30:29.507938Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_es.entity_from_dataframe(entity_id = \"train_log\",\n dataframe = ftrain_df,\n index = \"index\",\n time_index = \"visitStartTimestamp\",\n variable_types = my_variable_types)\n\ntest_es.entity_from_dataframe(entity_id = \"test_log\",\n dataframe = ftest_df,\n index = \"index\",\n time_index = \"visitStartTimestamp\",\n variable_types = my_variable_types)\n\nprint(train_es, test_es)","metadata":{"_uuid":"c8280ec1a3ad01c94c0997ef353acf626a0485b3","execution":{"iopub.status.busy":"2021-06-23T05:30:29.511346Z","iopub.execute_input":"2021-06-23T05:30:29.511892Z","iopub.status.idle":"2021-06-23T05:30:30.26501Z","shell.execute_reply.started":"2021-06-23T05:30:29.51166Z","shell.execute_reply":"2021-06-23T05:30:30.263388Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This is where we pull an entity from the original (train/test_log) and make a new entity. Note: Relationships is no longer empty in the output. ","metadata":{"_uuid":"c876b733df65219e77dc3cf4e29697a2e5111b2c"}},{"cell_type":"code","source":"train_es.normalize_entity(base_entity_id = \"train_log\",\n new_entity_id = \"visitors\",\n index = \"fullVisitorId\")\n\ntest_es.normalize_entity(base_entity_id = \"test_log\",\n new_entity_id = \"visitors\",\n index = \"fullVisitorId\")\n\nprint(train_es, test_es)","metadata":{"_uuid":"5906c080a4c4fae2601fd448675ddfe7d6f2456c","execution":{"iopub.status.busy":"2021-06-23T05:30:30.266618Z","iopub.execute_input":"2021-06-23T05:30:30.267013Z","iopub.status.idle":"2021-06-23T05:30:34.990333Z","shell.execute_reply.started":"2021-06-23T05:30:30.266937Z","shell.execute_reply":"2021-06-23T05:30:34.989039Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In an attempt to make things quicker we have also narrowed down the aggregation and transformation functions to be applied. In the output, you can see the features that will be generated.","metadata":{"_uuid":"3f519190ab7ac1510e04418e1145da31b7ba11dd"}},{"cell_type":"code","source":"my_aggs = [\"sum\", \"max\", \"min\", \"mean\", \"std\", \"mode\"]\nmy_trans = [\"month\", \"day\"]\n\ntrain_features = ft.dfs(entityset = train_es,\n target_entity = \"visitors\",\n max_depth = 2,\n agg_primitives = my_aggs,\n trans_primitives = my_trans,\n verbose = 1,\n max_features = 100,\n n_jobs = 4,\n features_only = True)\n\ntest_features = ft.dfs(entityset = test_es,\n target_entity = \"visitors\",\n max_depth = 2,\n agg_primitives = my_aggs,\n trans_primitives = my_trans,\n verbose = 1,\n max_features = 100,\n n_jobs = 4,\n features_only = True)\n\nprint(\"We're generating %d features for train and %d for test\" % (len(train_features), len(test_features)))\nfor feat in train_features:\n print(feat)","metadata":{"_uuid":"e9fafb1deb869ef9abdd8fd4621e44d6a03a274a","execution":{"iopub.status.busy":"2021-06-23T05:30:34.991996Z","iopub.execute_input":"2021-06-23T05:30:34.992391Z","iopub.status.idle":"2021-06-23T05:30:35.084663Z","shell.execute_reply.started":"2021-06-23T05:30:34.992331Z","shell.execute_reply":"2021-06-23T05:30:35.082316Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Here is where the actual functions are used and applied. This part takes around 5 minutes.","metadata":{"_uuid":"9d90091158cff4ea2f57a1937958777c59284df5"}},{"cell_type":"code","source":"train_feature_matrix, train_features = ft.dfs(entityset = train_es,\n target_entity = \"visitors\",\n max_depth = 2,\n agg_primitives = my_aggs,\n trans_primitives = my_trans,\n verbose = 1,\n max_features = 100,\n n_jobs = 4)\n\ntest_feature_matrix, test_features = ft.dfs(entityset = test_es,\n target_entity = \"visitors\",\n max_depth = 2,\n agg_primitives = my_aggs,\n trans_primitives = my_trans,\n verbose = 1,\n max_features = 100,\n n_jobs = 4)","metadata":{"_uuid":"40562c6d465662bbfeabeb4186bccb812789ee9a","execution":{"iopub.status.busy":"2021-06-23T05:30:35.086153Z","iopub.execute_input":"2021-06-23T05:30:35.086483Z","iopub.status.idle":"2021-06-23T05:36:44.609952Z","shell.execute_reply.started":"2021-06-23T05:30:35.086416Z","shell.execute_reply":"2021-06-23T05:36:44.60861Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_feature_matrix.head()","metadata":{"_uuid":"c60ccc46b28896711a582b688a744926a384ceab","execution":{"iopub.status.busy":"2021-06-23T05:36:44.611705Z","iopub.execute_input":"2021-06-23T05:36:44.612023Z","iopub.status.idle":"2021-06-23T05:36:44.656605Z","shell.execute_reply.started":"2021-06-23T05:36:44.61196Z","shell.execute_reply":"2021-06-23T05:36:44.655623Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_feature_matrix.head()","metadata":{"_uuid":"5cb6a8a0d7525bb4a5453d6ac02ca9dbe2c2473e","execution":{"iopub.status.busy":"2021-06-23T05:36:44.657875Z","iopub.execute_input":"2021-06-23T05:36:44.658156Z","iopub.status.idle":"2021-06-23T05:36:44.699099Z","shell.execute_reply.started":"2021-06-23T05:36:44.658103Z","shell.execute_reply":"2021-06-23T05:36:44.697882Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Here we clean up the data we have generated.","metadata":{"_uuid":"35b1a2d0c9075744892d710c65e17e852ca914f4"}},{"cell_type":"code","source":"printShapes(\"Train Feature Matrix:\", train_feature_matrix,\n \"Test Feature Matrix\", test_feature_matrix)","metadata":{"_uuid":"dae12968a112321f474fd99d2893559ea3477e76","execution":{"iopub.status.busy":"2021-06-23T05:36:44.700009Z","iopub.execute_input":"2021-06-23T05:36:44.700254Z","iopub.status.idle":"2021-06-23T05:36:44.705351Z","shell.execute_reply.started":"2021-06-23T05:36:44.700209Z","shell.execute_reply":"2021-06-23T05:36:44.70471Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"compare_nan(\"Train FM\", train_feature_matrix,\n \"Test FM\", test_feature_matrix)","metadata":{"_uuid":"450d24bf11dfc2d09753eb141f66a60255ae4092","execution":{"iopub.status.busy":"2021-06-23T05:36:44.706171Z","iopub.execute_input":"2021-06-23T05:36:44.706375Z","iopub.status.idle":"2021-06-23T05:36:44.939551Z","shell.execute_reply.started":"2021-06-23T05:36:44.706342Z","shell.execute_reply":"2021-06-23T05:36:44.938779Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_feature_matrix = train_feature_matrix.apply(lambda x: x.fillna(0) if x.dtype.kind in 'biufc' else x.fillna('.'))\ntest_feature_matrix = test_feature_matrix.apply(lambda x: x.fillna(0) if x.dtype.kind in 'biufc' else x.fillna('.'))","metadata":{"_uuid":"9c32b251a728ff33422693ba9de4bf08710f0322","execution":{"iopub.status.busy":"2021-06-23T05:36:44.940513Z","iopub.execute_input":"2021-06-23T05:36:44.94078Z","iopub.status.idle":"2021-06-23T05:36:45.582385Z","shell.execute_reply.started":"2021-06-23T05:36:44.940729Z","shell.execute_reply":"2021-06-23T05:36:45.581229Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"compare_nan(\"Train FM\", train_feature_matrix,\n \"Test FM\", test_feature_matrix)","metadata":{"_uuid":"1eeda7bce854f95c456691370948f8e6bf2b8b18","execution":{"iopub.status.busy":"2021-06-23T05:36:45.583654Z","iopub.execute_input":"2021-06-23T05:36:45.583944Z","iopub.status.idle":"2021-06-23T05:36:45.812886Z","shell.execute_reply.started":"2021-06-23T05:36:45.58389Z","shell.execute_reply":"2021-06-23T05:36:45.811624Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Merging Data","metadata":{"_uuid":"dca81f9a3340ec0b4a169e40be8658880092371c"}},{"cell_type":"code","source":"train_feature_matrix.reset_index(inplace = True)\ntest_feature_matrix.reset_index(inplace = True)","metadata":{"_uuid":"dfafed4bb8e54c0d733746d3136aa529aa356a3c","execution":{"iopub.status.busy":"2021-06-23T05:36:45.814337Z","iopub.execute_input":"2021-06-23T05:36:45.814611Z","iopub.status.idle":"2021-06-23T05:36:45.83068Z","shell.execute_reply.started":"2021-06-23T05:36:45.814563Z","shell.execute_reply":"2021-06-23T05:36:45.829811Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combined_train = train_nf.merge(train_feature_matrix, how = \"outer\", on = \"fullVisitorId\")\ncombined_test = test_nf.merge(test_feature_matrix, how = \"outer\", on = \"fullVisitorId\")","metadata":{"_uuid":"f87a65a4a2292e6ff4326110c596bbe0133f44b0","execution":{"iopub.status.busy":"2021-06-23T05:36:45.831799Z","iopub.execute_input":"2021-06-23T05:36:45.832079Z","iopub.status.idle":"2021-06-23T05:36:46.7836Z","shell.execute_reply.started":"2021-06-23T05:36:45.832027Z","shell.execute_reply":"2021-06-23T05:36:46.782672Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combined_train.head()","metadata":{"_uuid":"73f57f6c36f289b1b3fe249189d607b9afc7036a","execution":{"iopub.status.busy":"2021-06-23T05:36:46.784705Z","iopub.execute_input":"2021-06-23T05:36:46.784971Z","iopub.status.idle":"2021-06-23T05:36:46.831994Z","shell.execute_reply.started":"2021-06-23T05:36:46.78492Z","shell.execute_reply":"2021-06-23T05:36:46.831211Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combined_test.head()","metadata":{"_uuid":"d5e248990c3c5897d7822ff1b3dbda691a7d40a5","execution":{"iopub.status.busy":"2021-06-23T05:36:46.833084Z","iopub.execute_input":"2021-06-23T05:36:46.833369Z","iopub.status.idle":"2021-06-23T05:36:46.887153Z","shell.execute_reply.started":"2021-06-23T05:36:46.833315Z","shell.execute_reply":"2021-06-23T05:36:46.88576Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"compare_nan(\"CTrain\", combined_train, \"CTest\", combined_test)","metadata":{"_uuid":"fbde924679af07ff9cf6ca5320d5c5b9c4a1dca3","execution":{"iopub.status.busy":"2021-06-23T05:36:46.888413Z","iopub.execute_input":"2021-06-23T05:36:46.888687Z","iopub.status.idle":"2021-06-23T05:36:47.54466Z","shell.execute_reply.started":"2021-06-23T05:36:46.888639Z","shell.execute_reply":"2021-06-23T05:36:47.542901Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_rev = train_df[[\"fullVisitorId\", \"totals.transactionRevenue\"]].groupby(\"fullVisitorId\").sum().reset_index()\ntest_rev = test_df[[\"fullVisitorId\", \"totals.transactionRevenue\"]].groupby(\"fullVisitorId\").sum().reset_index()","metadata":{"_uuid":"a28bfe158aacc4b68af3a10c57100c5bf1a31459","execution":{"iopub.status.busy":"2021-06-23T05:36:47.546095Z","iopub.execute_input":"2021-06-23T05:36:47.546375Z","iopub.status.idle":"2021-06-23T05:36:49.802987Z","shell.execute_reply.started":"2021-06-23T05:36:47.546326Z","shell.execute_reply":"2021-06-23T05:36:49.802199Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combined_train_wrev = combined_train.merge(train_rev, how = \"outer\", on = \"fullVisitorId\")\ncombined_test_wrev = combined_test.merge(test_rev, how = \"outer\", on = \"fullVisitorId\")","metadata":{"_uuid":"ee388aae601bc7262ad1bef6a2dbaf3d1f3a3cd0","execution":{"iopub.status.busy":"2021-06-23T05:36:49.816193Z","iopub.execute_input":"2021-06-23T05:36:49.816652Z","iopub.status.idle":"2021-06-23T05:36:50.722369Z","shell.execute_reply.started":"2021-06-23T05:36:49.81661Z","shell.execute_reply":"2021-06-23T05:36:50.721489Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combined_train_wrev = combined_train_wrev.rename(columns = {\"totals.transactionRevenue\" : \"SUM(transactionRevenue)\"})\ncombined_test_wrev = combined_test_wrev.rename(columns = {\"totals.transactionRevenue\" : \"SUM(transactionRevenue)\"})","metadata":{"_uuid":"cb1f9e441f3f940323188ebdbf7531a99117c6b9","execution":{"iopub.status.busy":"2021-06-23T05:36:50.723762Z","iopub.execute_input":"2021-06-23T05:36:50.724193Z","iopub.status.idle":"2021-06-23T05:36:51.176185Z","shell.execute_reply.started":"2021-06-23T05:36:50.724147Z","shell.execute_reply":"2021-06-23T05:36:51.175383Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# LightGBM\n---","metadata":{"_uuid":"5ee0265df4e80432531ee7d5be0ae962249f901f"}},{"cell_type":"code","source":"import lightgbm as lgb\nfrom sklearn.model_selection import KFold","metadata":{"_uuid":"c22549dba74c7a950cd3ed9947b9161e6e7d7ab6","execution":{"iopub.status.busy":"2021-06-23T05:36:51.177347Z","iopub.execute_input":"2021-06-23T05:36:51.177752Z","iopub.status.idle":"2021-06-23T05:36:51.296506Z","shell.execute_reply.started":"2021-06-23T05:36:51.177693Z","shell.execute_reply":"2021-06-23T05:36:51.29558Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_train_features_encoding_dict = cat_feature_encoding(data=combined_train_wrev, cat_feature_list=combined_train_wrev.select_dtypes(include='object').columns)","metadata":{"_uuid":"a80b000274d08ecc6905d2170711ceac95a14750","execution":{"iopub.status.busy":"2021-06-23T05:36:51.297725Z","iopub.execute_input":"2021-06-23T05:36:51.298002Z","iopub.status.idle":"2021-06-23T05:36:51.957847Z","shell.execute_reply.started":"2021-06-23T05:36:51.297948Z","shell.execute_reply":"2021-06-23T05:36:51.956729Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_test_features_encoding_dict = cat_feature_encoding(data=combined_test_wrev, cat_feature_list=combined_test_wrev.select_dtypes(include='object').columns)","metadata":{"_uuid":"39418116e6dc4ede47b4b937fd529d76de9fed5d","execution":{"iopub.status.busy":"2021-06-23T05:36:51.959139Z","iopub.execute_input":"2021-06-23T05:36:51.959446Z","iopub.status.idle":"2021-06-23T05:36:52.511884Z","shell.execute_reply.started":"2021-06-23T05:36:51.959376Z","shell.execute_reply":"2021-06-23T05:36:52.510681Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"y = np.log1p(combined_train_wrev.iloc[:, -1])\n\nX = combined_train_wrev.iloc[:, 1:-1]\n\nX_pred = combined_test_wrev.iloc[:, 1:-1]","metadata":{"_uuid":"1c5bb42ce72680b0ed632f2902bfd40dad0d2760","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:36:52.513012Z","iopub.execute_input":"2021-06-23T05:36:52.513285Z","iopub.status.idle":"2021-06-23T05:36:52.601776Z","shell.execute_reply.started":"2021-06-23T05:36:52.513235Z","shell.execute_reply":"2021-06-23T05:36:52.600549Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This parameters comes from this [kernel](http://https://www.kaggle.com/ogrellier/user-level-lightgbm-lb-1-4480/code). As we don't want to tune the parameters to control the experiment variables.","metadata":{"_uuid":"5560f2e5bfc7ba926f88fde7541ea3ecfa03f80f"}},{"cell_type":"code","source":"lgb_clf = lgb.LGBMRegressor(learning_rate=0.05, n_estimators=1000, min_child_weight=np.power(10.0, 2), metric='rmse', \n num_leaves=128, reg_alpha=np.power(10.0, -3.2454), reg_lambda = np.power(10.0, -4.8571), silent=True, n_jobs=-1,\n colsample_bytree = 0.6810, min_child_samples = 95, subsample = 0.2217, min_split_gain=np.power(10.0, -4.9380))","metadata":{"_uuid":"ddc9cd277879d6b171a8d1b3969fdad030a7c9a0","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:36:52.603101Z","iopub.execute_input":"2021-06-23T05:36:52.603414Z","iopub.status.idle":"2021-06-23T05:36:52.611687Z","shell.execute_reply.started":"2021-06-23T05:36:52.603349Z","shell.execute_reply":"2021-06-23T05:36:52.610366Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.metrics import mean_squared_error\nkfold = 5\nkf = KFold(n_splits=kfold, shuffle=True)\n\npredicts_result = []\ntest_result = []\nfor train_index, test_index in kf.split(X, y):\n print(\"#\"*10)\n X_train, X_val = X.iloc[train_index], X.iloc[test_index]\n y_train, y_val = y.iloc[train_index], y.iloc[test_index]\n lgb_clf.fit(X_train, y_train, eval_set=[(X_val, y_val)], early_stopping_rounds=400, verbose=200, eval_metric='rmse') #eval_metric=f1_macro_evaluation)\n# test_result.append(f1_score(y_pred=lgb_clf.predict(X_val), y_true=y_val, average=\"macro\"))\n y_val_pred = lgb_clf.predict(X_val)\n # Modify the value that is below the zero\n y_val_pred[y_val_pred<0] = 0\n test_result.append(mean_squared_error(y_true=y_val, y_pred=y_val_pred))\n y_pred = lgb_clf.predict(X_pred, lgb_clf.best_iteration_)\n y_pred[y_pred<0] = 0\n predicts_result.append(y_pred)","metadata":{"_uuid":"035cc3ca44f33c70c41673ecee1e29ad766e8545","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:36:52.612966Z","iopub.execute_input":"2021-06-23T05:36:52.613211Z","iopub.status.idle":"2021-06-23T05:39:50.508615Z","shell.execute_reply.started":"2021-06-23T05:36:52.613165Z","shell.execute_reply":"2021-06-23T05:39:50.507324Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The average test RMSE will much higher than our actual prediction is because we filter up all the bouce instance to reduce the work during the feature engineering.","metadata":{"_uuid":"38c18f14ce794cb554c21955655cb1f1069b5693"}},{"cell_type":"code","source":"print(\"The average test RMSE is: \", np.mean(np.sqrt(test_result)))","metadata":{"_uuid":"b7b3957a09b66bd83c13e9e0fb373acda74d6931","execution":{"iopub.status.busy":"2021-06-23T05:39:50.510122Z","iopub.execute_input":"2021-06-23T05:39:50.510458Z","iopub.status.idle":"2021-06-23T05:39:50.517035Z","shell.execute_reply.started":"2021-06-23T05:39:50.510388Z","shell.execute_reply":"2021-06-23T05:39:50.515816Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def plot_features(col_list, feature_importances, index, most_important=True):\n indices = np.argsort(feature_importances)[::-1]\n indices = indices[:index]\n\n # Visualise these with a barplot\n plt.subplots(figsize=(20, 15))\n g = sns.barplot(y=col_list[indices], x = lgb_clf.feature_importances_[indices], orient='h')\n g.set_xlabel(\"Relative importance\",fontsize=20)\n g.set_ylabel(\"Features\",fontsize=20)\n g.tick_params(labelsize=15)\n g.set_title(\"LightGBM feature importance\", fontsize=20);","metadata":{"_uuid":"50f5e558d72c10328e84c30780e39ea9a62bb8f6","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:39:50.518294Z","iopub.execute_input":"2021-06-23T05:39:50.518638Z","iopub.status.idle":"2021-06-23T05:39:50.538116Z","shell.execute_reply.started":"2021-06-23T05:39:50.51858Z","shell.execute_reply":"2021-06-23T05:39:50.536638Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_features(col_list=X.columns, feature_importances=lgb_clf.feature_importances_, index=-1)","metadata":{"_uuid":"36e1aa3bc28a4e6fc884c72068ced1907d143615","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:39:50.539456Z","iopub.execute_input":"2021-06-23T05:39:50.539749Z","iopub.status.idle":"2021-06-23T05:39:51.358672Z","shell.execute_reply.started":"2021-06-23T05:39:50.539687Z","shell.execute_reply":"2021-06-23T05:39:51.357669Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Merge the prediction with the bounce instance we filter.","metadata":{"_uuid":"7f7afd97548b13919f42266ed33ec8417fea23f9"}},{"cell_type":"code","source":"no_bounce_pred_df = pd.DataFrame({\"fullVisitorId\": combined_test_wrev.fullVisitorId, \"PredictedLogRevenue\": np.mean(predicts_result, axis=0)})\n\nsubmit_df = pd.DataFrame({\"fullVisitorId\": submit_id})\n\nsubmit_df = submit_df.merge(no_bounce_pred_df, on=\"fullVisitorId\", how='left').fillna(0)\n\nsubmit_df.to_csv(\"submission.csv\", index=False)","metadata":{"_uuid":"8e5493a5e2f5f47c7f93a9d4c9b77f123907436b","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:39:51.360029Z","iopub.execute_input":"2021-06-23T05:39:51.360617Z","iopub.status.idle":"2021-06-23T05:39:54.809632Z","shell.execute_reply.started":"2021-06-23T05:39:51.360553Z","shell.execute_reply":"2021-06-23T05:39:54.808607Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Take a look what our prediction looks like.","metadata":{"_uuid":"87224e7b944b69e135655d68d10920f69f3826e9"}},{"cell_type":"code","source":"submit_df[submit_df.PredictedLogRevenue != 0].hist()","metadata":{"_uuid":"48e5dffc4e2cea01ab4b39b0ee708cbb9d99707b","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:39:54.810808Z","iopub.execute_input":"2021-06-23T05:39:54.811135Z","iopub.status.idle":"2021-06-23T05:39:55.40251Z","shell.execute_reply.started":"2021-06-23T05:39:54.811077Z","shell.execute_reply":"2021-06-23T05:39:55.401234Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"labels = ['Zero revenue customer', 'Non-zero customer']\nvalues = [submit_df[submit_df.PredictedLogRevenue == 0].PredictedLogRevenue.count(),\n submit_df[submit_df.PredictedLogRevenue != 0].PredictedLogRevenue.count()]\ntrace = go.Pie(labels=labels, values=values)\nlayout = dict(\n title = 'Unique Customer Revenue Prediction'\n)\n\nfig = dict(data=[trace], layout=layout)\niplot(fig, filename='basic_pie_chart')","metadata":{"_uuid":"d60badb7d9188f333b027d5acecd661ed07d38ae","scrolled":true,"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-23T05:39:55.403693Z","iopub.execute_input":"2021-06-23T05:39:55.403975Z","iopub.status.idle":"2021-06-23T05:39:55.517387Z","shell.execute_reply.started":"2021-06-23T05:39:55.403926Z","shell.execute_reply":"2021-06-23T05:39:55.516346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"1. # Conclusion\nIn this kernel we have tried three different iterations. \n\n1. Naive - Score : 1.7340\n2. Feature Engineering - Score : 1.5665\n3. Reduced Feature Engineering (for runtime) with custom features added - Score : 1.5372\n\nClearly, 1.5665 was the best iteration. \n\nThis kernel has highlighted the usefulness of Plotly, Featuretools, lightGBM. We would use these all again. ","metadata":{"_uuid":"6bfa441c23edf64aaa60ec51cd3c697a90a4ca15","trusted":true}},{"cell_type":"code","source":"","metadata":{"_uuid":"1db1580a4e7decfd48e4db48c0deae58e106b9d4","trusted":true},"execution_count":null,"outputs":[]}]}