{"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":"# Google Analytics Customer Revenue Prediction\n\n\n\n### Contents of this Kernel\n\n1. Problem Statement  \n2. Dataset Understanding  \n3. Exploration  \n4. Visitor Profile  \n5. Baseline Model  \n\n## 1. Problem Statement \n \nIn this [competition](https://www.kaggle.com/c/google-analytics-customer-revenue-prediction), the aim is to analyze a Google Merchandise Store (also known as GStore, where Google swag is sold) customer dataset to predict revenue per customer. The results of predictions and analysis might lead to more actionable operational changes and a better use of marketing budgets for those companies who choose to use data analysis on top of GA data. This is the starter baseline kernel, I will be updating it frequently. \n\nAs the first step, lets load the required libraries.\n","metadata":{"_uuid":"163a1a9c9d4fc3f61c766fdf860af902bd8e762c","execution":{"iopub.status.busy":"2021-09-01T04:31:38.397272Z","iopub.execute_input":"2021-09-01T04:31:38.397803Z","iopub.status.idle":"2021-09-01T04:31:38.411427Z","shell.execute_reply.started":"2021-09-01T04:31:38.397722Z","shell.execute_reply":"2021-09-01T04:31:38.410043Z"}}},{"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport json\nimport bq_helper\nfrom pandas.io.json import json_normalize\nimport seaborn as sns \nimport matplotlib.pyplot as plt \nfrom plotly.offline import init_notebook_mode, iplot\nimport plotly.graph_objs as go\nfrom plotly import tools\ninit_notebook_mode(connected=True)\nimport warnings\nwarnings.filterwarnings(\"ignore\")","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-09-01T04:33:31.439938Z","iopub.execute_input":"2021-09-01T04:33:31.440261Z","iopub.status.idle":"2021-09-01T04:33:31.448826Z","shell.execute_reply.started":"2021-09-01T04:33:31.440232Z","shell.execute_reply":"2021-09-01T04:33:31.447583Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 2. Dataset Understanding\n\nThe data is shared in big query and csv format. The csv files contains some filed with json objects. The description about dataset fields is given [here](https://www.kaggle.com/c/google-analytics-customer-revenue-prediction/data). Lets read the dataset in csv format and unwrap the json fields. I am using the [function](https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields/data) shared by @julian in his kernel.  \n\n### 2.1 Dataset Preparation","metadata":{"_uuid":"d53b5bdd24383e8eb78b163fa3047cd53febfc7d","trusted":true}},{"cell_type":"code","source":"json_cols = ['device', 'geoNetwork', 'totals', 'trafficSource']\ndef load_df(filename):\n    path = \"../input/\" + filename\n    df = pd.read_csv(path, converters={column: json.loads for column in json_cols}, \n                     dtype={'fullVisitorId': 'str'})\n    \n    for column in json_cols:\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    return df\n\ntrain = load_df(\"train.csv\")","metadata":{"_uuid":"5a88e31d7edecf4f92855192f0260f6884dc9da0","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-09-01T04:33:31.450105Z","iopub.execute_input":"2021-09-01T04:33:31.450398Z","iopub.status.idle":"2021-09-01T04:36:33.166369Z","shell.execute_reply.started":"2021-09-01T04:33:31.450337Z","shell.execute_reply":"2021-09-01T04:36:33.164105Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 2.2 Dataset Snapshot\n\nLets view the snapshot of the test dataset. ","metadata":{"_uuid":"673e7c7367a4a5980560baa125e43376d64cd5ff"}},{"cell_type":"code","source":"print (\"There are \" + str(train.shape[0]) + \" rows and \" + str(train.shape[1]) + \" raw columns in this dataset\")\n\nprint (\"Snapshot: \")\ntrain.head()","metadata":{"_kg_hide-input":true,"_uuid":"9e761c2ef85fd6c9c94d7a155d639d6a5424d209","execution":{"iopub.status.busy":"2021-09-01T04:36:33.171084Z","iopub.execute_input":"2021-09-01T04:36:33.171424Z","iopub.status.idle":"2021-09-01T04:36:33.214819Z","shell.execute_reply.started":"2021-09-01T04:36:33.171382Z","shell.execute_reply":"2021-09-01T04:36:33.213819Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 2.2 Missing Values Percentage\n\nFrom the snapshot we can observe that there are many missing values in the dataset. Let's plot the missing values percentage for columns having missing values. \n\n> The following graph shows only those columns having missing values, all other columns are fine. ","metadata":{"_uuid":"e4d55abc37cefb9a341b3271b1c6f703c347237e"}},{"cell_type":"code","source":"miss_per = {}\nfor k, v in dict(train.isna().sum(axis=0)).items():\n    if v == 0:\n        continue\n    miss_per[k] = 100 * float(v) / len(train)\n    \nimport operator \nsorted_x = sorted(miss_per.items(), key=operator.itemgetter(1), reverse=True)\nprint (\"There are \" + str(len(miss_per)) + \" columns with missing values\")\n\nkys = [_[0] for _ in sorted_x][::-1]\nvls = [_[1] for _ in sorted_x][::-1]\ntrace1 = go.Bar(y = kys, orientation=\"h\" , x = vls, marker=dict(color=\"#d6a5ff\"))\nlayout = go.Layout(title=\"Missing Values Percentage\", \n                   xaxis=dict(title=\"Missing Percentage\"), \n                   height=400, margin=dict(l=300, r=300))\nfigure = go.Figure(data = [trace1], layout = layout)\niplot(figure)","metadata":{"_kg_hide-input":true,"_uuid":"44dfb5f91100da2e89986be56c0ccab162e089e4","execution":{"iopub.status.busy":"2021-09-01T04:36:33.216231Z","iopub.execute_input":"2021-09-01T04:36:33.216509Z","iopub.status.idle":"2021-09-01T04:36:39.534279Z","shell.execute_reply.started":"2021-09-01T04:36:33.216483Z","shell.execute_reply":"2021-09-01T04:36:39.533167Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"> - So we can observe that there are some columns in the dataset having very large number of missing values. \n\n## 3. Exploration - Univariate Analysis \n\nLets perform the univariate analysis and plot some distributions of variables in the dataset\n\n### 3.1 Device Attributes\n\nLets plot the distribution of device attributes","metadata":{"_uuid":"129c51c109ffa9659c881549cf0b29568d3d2993"}},{"cell_type":"code","source":"device_cols = [\"device_browser\", \"device_deviceCategory\", \"device_operatingSystem\"]\n\ncolors = [\"#d6a5ff\", \"#fca6da\", \"#f4d39c\", \"#a9fcca\"]\ntraces = []\nfor i, col in enumerate(device_cols):\n    t = train[col].value_counts()\n    traces.append(go.Bar(marker=dict(color=colors[i]),orientation=\"h\", y = t.index[:15][::-1], x = t.values[:15][::-1]))\n\nfig = tools.make_subplots(rows=1, cols=3, subplot_titles=[\"Visits: Category\", \"Visits: Browser\",\"Visits: OS\"], print_grid=False)\nfig.append_trace(traces[1], 1, 1)\nfig.append_trace(traces[0], 1, 2)\nfig.append_trace(traces[2], 1, 3)\n\nfig['layout'].update(height=400, showlegend=False, title=\"Visits by Device Attributes\")\niplot(fig)\n\n## convert transaction revenue to float\ntrain[\"totals_transactionRevenue\"] = train[\"totals_transactionRevenue\"].astype('float')\n\ndevice_cols = [\"device_browser\", \"device_deviceCategory\", \"device_operatingSystem\"]\n\nfig = tools.make_subplots(rows=1, cols=3, subplot_titles=[\"Mean Revenue: Category\", \"Mean Revenue: Browser\",\"Mean Revenue: OS\"], print_grid=False)\n\ncolors = [\"red\", \"green\", \"purple\"]\ntrs = []\nfor i, col in enumerate(device_cols):\n    tmp = train.groupby(col).agg({\"totals_transactionRevenue\": \"mean\"}).reset_index().rename(columns={\"totals_transactionRevenue\" : \"Mean Revenue\"})\n    tmp = tmp.dropna().sort_values(\"Mean Revenue\", ascending = False)\n    tr = go.Bar(x = tmp[\"Mean Revenue\"][::-1], orientation=\"h\", marker=dict(opacity=0.5, color=colors[i]), y = tmp[col][::-1])\n    trs.append(tr)\n\nfig.append_trace(trs[1], 1, 1)\nfig.append_trace(trs[0], 1, 2)\nfig.append_trace(trs[2], 1, 3)\nfig['layout'].update(height=400, showlegend=False, title=\"Mean Revenue by Device Attributes\")\niplot(fig)","metadata":{"_kg_hide-input":true,"_uuid":"707893d0be746d4536b6252a26de7f8f357ea54c","execution":{"iopub.status.busy":"2021-09-01T04:36:39.535688Z","iopub.execute_input":"2021-09-01T04:36:39.536005Z","iopub.status.idle":"2021-09-01T04:36:41.962909Z","shell.execute_reply.started":"2021-09-01T04:36:39.535976Z","shell.execute_reply":"2021-09-01T04:36:41.961757Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"> - There is a significant difference in visits from mobile and tablets, but mean revenue for both of them is very close.  \n> - Interesting to note that maximum visits are from Chrome browser however maximum revenue is collected from visits throught firefox. \n> - Chrome OS users has generated maximum revenue though maximum visits are from windows and macintosh users  \n\n### 3.2 GeoNetwork Attributes ","metadata":{"_uuid":"fe8fe3d1a2019687fb4533a4a7f59f559becd992"}},{"cell_type":"code","source":"geo_cols = ['geoNetwork_city', 'geoNetwork_continent','geoNetwork_country',\n            'geoNetwork_metro', 'geoNetwork_networkDomain', 'geoNetwork_region','geoNetwork_subContinent']\ngeo_cols = ['geoNetwork_continent','geoNetwork_subContinent']\n\ncolors = [\"#d6a5ff\", \"#fca6da\"]\nfig = tools.make_subplots(rows=1, cols=2, subplot_titles=[\"Visits : GeoNetwork Continent\", \"Visits : GeoNetwork subContinent\"], print_grid=False)\ntrs = []\nfor i,col in enumerate(geo_cols):\n    t = train[col].value_counts()\n    tr = go.Bar(x = t.index[:20], marker=dict(color=colors[i]), y = t.values[:20])\n    trs.append(tr)\n\nfig.append_trace(trs[0], 1, 1)\nfig.append_trace(trs[1], 1, 2)\nfig['layout'].update(height=400, margin=dict(b=150), showlegend=False)\niplot(fig)\n\n\n\n\ngeo_cols = ['geoNetwork_continent','geoNetwork_subContinent']\nfig = tools.make_subplots(rows=1, cols=2, subplot_titles=[\"Mean Revenue: Continent\", \"Mean Revenue: SubContinent\"], print_grid=False)\n\ncolors = [\"blue\", \"orange\"]\ntrs = []\nfor i, col in enumerate(geo_cols):\n    tmp = train.groupby(col).agg({\"totals_transactionRevenue\": \"mean\"}).reset_index().rename(columns={\"totals_transactionRevenue\" : \"Mean Revenue\"})\n    tmp = tmp.dropna().sort_values(\"Mean Revenue\", ascending = False)\n    tr = go.Bar(y = tmp[\"Mean Revenue\"], orientation=\"v\", marker=dict(opacity=0.5, color=colors[i]), x= tmp[col])\n    trs.append(tr)\n\nfig.append_trace(trs[0], 1, 1)\nfig.append_trace(trs[1], 1, 2)\nfig['layout'].update(height=450, margin=dict(b=200), showlegend=False)\niplot(fig)","metadata":{"_uuid":"2fcf66951ad315244c75b7f5bbf68efcb2b2b23e","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-09-01T04:36:41.964111Z","iopub.execute_input":"2021-09-01T04:36:41.96442Z","iopub.status.idle":"2021-09-01T04:36:43.353476Z","shell.execute_reply.started":"2021-09-01T04:36:41.964387Z","shell.execute_reply":"2021-09-01T04:36:43.352476Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.3 Traffic Attributes\n\nLets now plot the traffic attributes","metadata":{"_uuid":"8f70cf1087d649b881072d60340424e6cbf2e218"}},{"cell_type":"code","source":"fig = tools.make_subplots(rows=1, cols=2, subplot_titles=[\"TrafficSource Campaign (not-set removed)\", \"TrafficSource Medium\"], print_grid=False)\n\ncolors = [\"#d6a5ff\", \"#fca6da\", \"#f4d39c\", \"#a9fcca\"]\nt1 = train[\"trafficSource_campaign\"].value_counts()\nt2 = train[\"trafficSource_medium\"].value_counts()\ntr1 = go.Bar(x = t1.index, y = t1.values, marker=dict(color=colors[3]))\ntr2 = go.Bar(x = t2.index, y = t2.values, marker=dict(color=colors[2]))\ntr3 = go.Bar(x = t1.index[1:], y = t1.values[1:], marker=dict(color=colors[0]))\ntr4 = go.Bar(x = t2.index[1:], y = t2.values[1:])\n\nfig.append_trace(tr3, 1, 1)\nfig.append_trace(tr2, 1, 2)\nfig['layout'].update(height=400, margin=dict(b=100), showlegend=False)\niplot(fig)","metadata":{"_uuid":"e7c28dc60b66ba44f74739f08adad051d9590e4e","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-09-01T04:36:43.356326Z","iopub.execute_input":"2021-09-01T04:36:43.356648Z","iopub.status.idle":"2021-09-01T04:36:44.012366Z","shell.execute_reply.started":"2021-09-01T04:36:43.35662Z","shell.execute_reply":"2021-09-01T04:36:44.01147Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.4 Channel Grouping","metadata":{"_uuid":"fa5abc7d9e091a3d86a494f48ebfd938ea7ba57e"}},{"cell_type":"code","source":"tmp = train[\"channelGrouping\"].value_counts()\ncolors = [\"#8d44fc\", \"#ed95d5\", \"#caadf7\", \"#6161b7\", \"#7e7eba\", \"#babad1\"]\ntrace = go.Pie(labels=tmp.index, values=tmp.values, marker=dict(colors=colors))\nlayout = go.Layout(title=\"Channel Grouping\", height=400)\nfig = go.Figure(data = [trace], layout = layout)\niplot(fig, filename='basic_pie_chart')","metadata":{"_kg_hide-input":true,"_uuid":"2ae83984b56f3e49857a5157ca05829d98bbbb0b","execution":{"iopub.status.busy":"2021-09-01T04:36:44.014257Z","iopub.execute_input":"2021-09-01T04:36:44.014681Z","iopub.status.idle":"2021-09-01T04:36:44.282341Z","shell.execute_reply.started":"2021-09-01T04:36:44.014646Z","shell.execute_reply":"2021-09-01T04:36:44.281476Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.5 Visits by date, month and day","metadata":{"_uuid":"36a0751e6fe11161b8a3f189306e5803ddb2f735"}},{"cell_type":"code","source":"def _add_date_features(df):\n    df['date'] = df['date'].astype(str)\n    df[\"date\"] = df[\"date\"].apply(lambda x : x[:4] + \"-\" + x[4:6] + \"-\" + x[6:])\n    df[\"date\"] = pd.to_datetime(df[\"date\"])\n    \n    df[\"month\"]   = df['date'].dt.month\n    df[\"day\"]     = df['date'].dt.day\n    df[\"weekday\"] = df['date'].dt.weekday\n    return df \n\ntrain = _add_date_features(train)\n\ntmp = train['date'].value_counts().to_frame().reset_index().sort_values('index')\ntmp = tmp.rename(columns = {\"index\" : \"dateX\", \"date\" : \"visits\"})\n\ntr = go.Scatter(mode=\"lines\", x = tmp[\"dateX\"].astype(str), y = tmp[\"visits\"])\nlayout = go.Layout(title=\"Visits by date\", height=400)\nfig = go.Figure(data = [tr], layout = layout)\niplot(fig)\n\n\ntmp = train.groupby(\"date\").agg({\"totals_transactionRevenue\" : \"mean\"}).reset_index()\ntmp = tmp.rename(columns = {\"date\" : \"dateX\", \"totals_transactionRevenue\" : \"mean_revenue\"})\ntr = go.Scatter(mode=\"lines\", x = tmp[\"dateX\"].astype(str), y = tmp[\"mean_revenue\"])\nlayout = go.Layout(title=\"MonthlyRevenue by date\", height=400)\nfig = go.Figure(data = [tr], layout = layout)\niplot(fig)","metadata":{"_kg_hide-input":true,"_uuid":"f764642db712ee0433b7adce08f7fe75cd5c879e","execution":{"iopub.status.busy":"2021-09-01T04:36:44.283621Z","iopub.execute_input":"2021-09-01T04:36:44.283913Z","iopub.status.idle":"2021-09-01T04:36:46.955627Z","shell.execute_reply.started":"2021-09-01T04:36:44.283884Z","shell.execute_reply":"2021-09-01T04:36:46.954275Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = tools.make_subplots(rows=1, cols=3, subplot_titles=[\"Visits by Month\", \"Visits by MonthDay\", \"Visits by WeekDay\"], print_grid=False)\ntrs = []\nfor i,col in enumerate([\"month\", \"day\", \"weekday\"]):\n    t = train[col].value_counts()\n    tr = go.Bar(x = t.index, marker=dict(color=colors[i]), y = t.values)\n    trs.append(tr)\n\nfig.append_trace(trs[0], 1, 1)\nfig.append_trace(trs[1], 1, 2)\nfig.append_trace(trs[2], 1, 3)\nfig['layout'].update(height=400, showlegend=False)\niplot(fig)\n\n\n\ntmp1 = train.groupby('month').agg({\"totals_transactionRevenue\" : \"mean\"}).reset_index()\ntmp2 = train.groupby('day').agg({\"totals_transactionRevenue\" : \"mean\"}).reset_index()\ntmp3 = train.groupby('weekday').agg({\"totals_transactionRevenue\" : \"mean\"}).reset_index()\n\nfig = tools.make_subplots(rows=1, cols=3, subplot_titles=[\"MeanRevenue by Month\", \"MeanRevenue by MonthDay\", \"MeanRevenue by WeekDay\"], print_grid=False)\ntr1 = go.Bar(x = tmp1.month, marker=dict(color=\"red\", opacity=0.5), y = tmp1.totals_transactionRevenue)\ntr2 = go.Bar(x = tmp2.day, marker=dict(color=\"orange\", opacity=0.5), y = tmp2.totals_transactionRevenue)\ntr3 = go.Bar(x = tmp3.weekday, marker=dict(color=\"green\", opacity=0.5), y = tmp3.totals_transactionRevenue)\n\nfig.append_trace(tr1, 1, 1)\nfig.append_trace(tr2, 1, 2)\nfig.append_trace(tr3, 1, 3)\nfig['layout'].update(height=400, showlegend=False)\niplot(fig)","metadata":{"_kg_hide-input":true,"_uuid":"e43270a70ea838268a8005392bb86e7b13e2a0d1","execution":{"iopub.status.busy":"2021-09-01T04:36:46.957468Z","iopub.execute_input":"2021-09-01T04:36:46.957839Z","iopub.status.idle":"2021-09-01T04:36:47.202901Z","shell.execute_reply.started":"2021-09-01T04:36:46.957804Z","shell.execute_reply":"2021-09-01T04:36:47.201475Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.6 Visit Number Frequency","metadata":{"_uuid":"e9284994f822fde33ae679f78310604a80635fd6"}},{"cell_type":"code","source":"vn = train[\"visitNumber\"].value_counts()\ndef vn_bins(x):\n    if x == 1:\n        return \"1\" \n    elif x < 5:\n        return \"2-5\"\n    elif x < 10:\n        return \"5-10\"\n    elif x < 50:\n        return \"10-50\"\n    elif x < 100:\n        return \"50-100\"\n    else:\n        return \"100+\"\n    \nvn = train[\"visitNumber\"].apply(vn_bins).value_counts()\n\ntrace1 = go.Bar(y = vn.index[::-1], orientation=\"h\" , x = vn.values[::-1], marker=dict(color=\"#7af9ad\"))\nlayout = go.Layout(title=\"Visit Numbers Distribution\", \n                   xaxis=dict(title=\"Frequency\"),yaxis=dict(title=\"VisitNumber\") ,\n                   height=400, margin=dict(l=300, r=300))\nfigure = go.Figure(data = [trace1], layout = layout)\niplot(figure)","metadata":{"_kg_hide-input":true,"_uuid":"c1889e56a301f467e68a979d4b189ec7e10e3da1","execution":{"iopub.status.busy":"2021-09-01T04:36:47.205057Z","iopub.execute_input":"2021-09-01T04:36:47.20554Z","iopub.status.idle":"2021-09-01T04:36:47.698765Z","shell.execute_reply.started":"2021-09-01T04:36:47.205491Z","shell.execute_reply":"2021-09-01T04:36:47.697595Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 4. Visitor Profile \n\nLets create the visitor profile by aggregating the rows for every customer. \n\n### 4.1 Visitor Profile Snapshot","metadata":{"_uuid":"02b61a16d6323e34bf8ca1bed937cfc97f9ae872","trusted":true}},{"cell_type":"code","source":"import warnings\nwarnings.simplefilter(action='ignore', category=FutureWarning)\n\nagg_dict = {}\nfor col in [\"totals_bounces\", \"totals_hits\", \"totals_newVisits\", \"totals_pageviews\", \"totals_transactionRevenue\"]:\n    train[col] = train[col].astype('float')\n    agg_dict[col] = \"sum\"\ntmp = train.groupby(\"fullVisitorId\").agg(agg_dict).reset_index()\ntmp.head()","metadata":{"_uuid":"46841c2028ea593e5d77538e7d9b6648cd1b5472","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-09-01T04:36:47.700448Z","iopub.execute_input":"2021-09-01T04:36:47.700854Z","iopub.status.idle":"2021-09-01T04:36:54.529483Z","shell.execute_reply.started":"2021-09-01T04:36:47.700809Z","shell.execute_reply":"2021-09-01T04:36:54.528524Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 4.2 Total Transactions Revenue","metadata":{"_uuid":"62a62c5484e808b19c79907c9f1590e95effc1f7"}},{"cell_type":"code","source":"non_zero = tmp[tmp[\"totals_transactionRevenue\"] > 0][\"totals_transactionRevenue\"]\nprint (\"There are \" + str(len(non_zero)) + \" visitors in the train dataset having non zero total transaction revenue\")\n\nplt.figure(figsize=(12,6))\nsns.distplot(non_zero)\nplt.title(\"Distribution of Non Zero Total Transactions\");\nplt.xlabel(\"Total Transactions\");","metadata":{"_kg_hide-input":true,"_uuid":"c60b224592c69e15e4e286d83cec1de595f317ab","execution":{"iopub.status.busy":"2021-09-01T04:36:54.53064Z","iopub.execute_input":"2021-09-01T04:36:54.530896Z","iopub.status.idle":"2021-09-01T04:36:55.161156Z","shell.execute_reply.started":"2021-09-01T04:36:54.53087Z","shell.execute_reply":"2021-09-01T04:36:55.15957Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Lets take the natural log on the transactions","metadata":{"_uuid":"e54cfc3f5751bb7e26bf46bac570e471809789c9"}},{"cell_type":"code","source":"plt.figure(figsize=(12,6))\nsns.distplot(np.log1p(non_zero))\nplt.title(\"Log Distribution of Non Zero Total Transactions\");\nplt.xlabel(\"Log - Total Transactions\");","metadata":{"_kg_hide-input":true,"_uuid":"e0e4b4c6a17803195d4c9f1ebedfddfb670a20a4","execution":{"iopub.status.busy":"2021-09-01T04:36:55.162494Z","iopub.execute_input":"2021-09-01T04:36:55.162911Z","iopub.status.idle":"2021-09-01T04:36:55.553161Z","shell.execute_reply.started":"2021-09-01T04:36:55.162878Z","shell.execute_reply":"2021-09-01T04:36:55.552219Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 4.3 Visitor Profile Attributes","metadata":{"_uuid":"30f66ab7332348bf1a6837dd272caf5090be0d8f"}},{"cell_type":"code","source":"def getbin_hits(x):\n    if x < 5:\n        return \"1-5\"\n    elif x < 10:\n        return \"5-10\"\n    elif x < 30:\n        return \"10-30\"\n    elif x < 50:\n        return \"30-50\"\n    elif x < 100:\n        return \"50-100\"\n    else:\n        return \"100+\"\n\ntmp[\"total_hits_bin\"] = tmp[\"totals_hits\"].apply(getbin_hits)\ntmp[\"totals_bounces_bin\"] = tmp[\"totals_bounces\"].apply(lambda x : str(x) if x <= 5 else \"5+\")\ntmp[\"totals_pageviews_bin\"] = tmp[\"totals_pageviews\"].apply(lambda x : str(x) if x <= 50 else \"50+\")\n\nt1 = tmp[\"total_hits_bin\"].value_counts()\nt2 = tmp[\"totals_bounces_bin\"].value_counts()\nt3 = tmp[\"totals_newVisits\"].value_counts()\nt4 = tmp[\"totals_pageviews_bin\"].value_counts()\n\nfig = tools.make_subplots(rows=2, cols=2, subplot_titles=[\"Total Hits per User\", \"Total Bounces per User\", \n                                                         \"Total NewVistits per User\", \"Total PageViews per User\"], print_grid=False)\n\ntr1 = go.Bar(x = t1.index[:20], y = t1.values[:20])\ntr2 = go.Bar(x = t2.index[:20], y = t2.values[:20])\ntr3 = go.Bar(x = t3.index[:20], y = t3.values[:20])\ntr4 = go.Bar(x = t4.index, y = t4.values)\n\nfig.append_trace(tr1, 1, 1)\nfig.append_trace(tr2, 1, 2)\nfig.append_trace(tr3, 2, 1)\nfig.append_trace(tr4, 2, 2)\n\nfig['layout'].update(height=700, showlegend=False)\niplot(fig)","metadata":{"_kg_hide-input":true,"_uuid":"15d9c375b9672344d4b320ed6cd918b3644c3cb0","execution":{"iopub.status.busy":"2021-09-01T04:36:55.554199Z","iopub.execute_input":"2021-09-01T04:36:55.554491Z","iopub.status.idle":"2021-09-01T04:36:57.447471Z","shell.execute_reply.started":"2021-09-01T04:36:55.554462Z","shell.execute_reply":"2021-09-01T04:36:57.446474Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 5. Baseline Model\n\n### 5.1 PreProcessing\n\nAs the preprocessing step, lets identify which columns can be removed. \n- Drop Columns with constant values  \n- Drop Ids and other non relevant columns  ","metadata":{"_uuid":"696837e6c7acbda716057da56b614d573da77758"}},{"cell_type":"code","source":"INPUT_TRAIN = \"../input/train.csv\"\nINPUT_TEST = \"../input/test.csv\"\n\nTRAIN='train-processed.csv'\nTEST='test-processed.csv'\nY='y.csv'","metadata":{"execution":{"iopub.status.busy":"2021-09-01T04:36:57.448607Z","iopub.execute_input":"2021-09-01T04:36:57.44887Z","iopub.status.idle":"2021-09-01T04:36:57.454242Z","shell.execute_reply.started":"2021-09-01T04:36:57.448845Z","shell.execute_reply":"2021-09-01T04:36:57.452809Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import os\nimport gc\nimport json\nimport time\nfrom datetime import datetime\nimport numpy as np\nimport pandas as pd\nfrom pandas.io.json import json_normalize\n\nimport warnings\nwarnings.filterwarnings('ignore')\n\n# Reference: https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields\n# 将json格式转换为字典格式并展平，输入：原始csv文件，输出：展平的csv文件\ndef load_df(csv_path=INPUT_TRAIN, nrows=None):\n    print(f\"Loading {csv_path}\")\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    \n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n#                      json.loads 将json格式内容转换为字典格式\n                     dtype={'fullVisitorId': 'str'}, # Important!!\n                     nrows=nrows)\n    for column in JSON_COLUMNS:\n        column_as_df = json_normalize(df[column])\n#         json_normalize将多层字典格式转化为df格式\n        column_as_df.columns = [f\"{column}.{subcolumn}\" for subcolumn in column_as_df.columns]\n#     对新产生的列进行重命名\n        df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)\n#     将原本字典格式的列抛弃，加入df格式的列\n    print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n    return df\n","metadata":{"execution":{"iopub.status.busy":"2021-09-01T04:36:57.455876Z","iopub.execute_input":"2021-09-01T04:36:57.4562Z","iopub.status.idle":"2021-09-01T04:36:57.467012Z","shell.execute_reply.started":"2021-09-01T04:36:57.456162Z","shell.execute_reply":"2021-09-01T04:36:57.465494Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# This function is just a packaged version of this kernel:\n# https://www.kaggle.com/fabiendaniel/lgbm-rf-starter-lb-1-70\ndef process_dfs(train_df, test_df):\n    print(\"Processing dfs...\")\n    print(\"Dropping repeated columns...\")\n    columns = [col for col in train_df.columns if train_df[col].nunique() > 1]\n#     去掉只有一种值的列\n    train_df = train_df[columns]\n    test_df = test_df[columns]\n\n#     trn_len记录训练集的长度，将训练集和测试集合在一起处理，然后再次分开\n    trn_len = train_df.shape[0]\n    merged_df = pd.concat([train_df, test_df])\n\n    merged_df['diff_visitId_time'] = merged_df['visitId'] - merged_df['visitStartTime']\n    merged_df['diff_visitId_time'] = (merged_df['diff_visitId_time'] != 0).astype(int)\n    del merged_df['visitId']\n\n    del merged_df['sessionId']\n\n    print(\"Generating date columns...\")\n#     添加有关时间的列\n    format_str = '%Y%m%d' \n    merged_df['formated_date'] = merged_df['date'].apply(lambda x: datetime.strptime(str(x), format_str))\n    merged_df['WoY'] = merged_df['formated_date'].apply(lambda x: x.isocalendar()[1])\n    merged_df['month'] = merged_df['formated_date'].apply(lambda x:x.month)\n    merged_df['quarter_month'] = merged_df['formated_date'].apply(lambda x:x.day//8)\n    merged_df['weekday'] = merged_df['formated_date'].apply(lambda x:x.weekday())\n\n    del merged_df['date']\n    del merged_df['formated_date']\n\n    merged_df['formated_visitStartTime'] = merged_df['visitStartTime'].apply(\n        lambda x: time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(x)))\n    merged_df['formated_visitStartTime'] = pd.to_datetime(merged_df['formated_visitStartTime'])\n    merged_df['visit_hour'] = merged_df['formated_visitStartTime'].apply(lambda x: x.hour)\n\n    del merged_df['visitStartTime']\n    del merged_df['formated_visitStartTime']\n\n    print(\"Encoding columns with pd.factorize()\")\n#     对非数字或者日期的列进行编码\n    for col in merged_df.columns:\n        if col in ['fullVisitorId', 'month', 'quarter_month', 'weekday', 'visit_hour', 'WoY']: continue\n        if merged_df[col].dtypes == object or merged_df[col].dtypes == bool:\n            merged_df[col], indexer = pd.factorize(merged_df[col])\n\n    print(\"Splitting back...\")\n    train_df = merged_df[:trn_len]\n    test_df = merged_df[trn_len:]\n    return train_df, test_df\n\ndef preprocess():\n    train_df = load_df()\n    test_df = load_df(INPUT_TEST)\n\n    target = train_df['totals.transactionRevenue'].fillna(0).astype(float)\n    target = target.apply(lambda x: np.log1p(x))\n#     使用log(x + 1)使目标值变得平滑\n    del train_df['totals.transactionRevenue']\n\n    train_df, test_df = process_dfs(train_df, test_df)\n    train_df.to_csv(TRAIN, index=False)\n    test_df.to_csv(TEST, index=False)\n    target.to_csv(Y, index=False)","metadata":{"execution":{"iopub.status.busy":"2021-09-01T04:36:57.468939Z","iopub.execute_input":"2021-09-01T04:36:57.469301Z","iopub.status.idle":"2021-09-01T04:36:57.488647Z","shell.execute_reply.started":"2021-09-01T04:36:57.469261Z","shell.execute_reply":"2021-09-01T04:36:57.487264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\npreprocess()\n# 把训练数据和测试数据展平，增加时间相关的列，对非数据值进行编码，拆分出target","metadata":{"execution":{"iopub.status.busy":"2021-09-01T04:36:57.492301Z","iopub.execute_input":"2021-09-01T04:36:57.492647Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import lightgbm as lgb\nimport xgboost as xgb\nfrom catboost import CatBoostRegressor","metadata":{"_uuid":"dbdaaa11f6bb61103b1e98c779ae3c2f23062592","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.model_selection import train_test_split\nfrom sklearn.metrics import mean_squared_error\n\ndef rmse(y_true, y_pred):\n    return round(np.sqrt(mean_squared_error(y_true, y_pred)), 5)\n\ndef load_preprocessed_dfs(drop_full_visitor_id=True):\n    \"\"\"\n    Loads files `TRAIN`, `TEST` and `Y` generated by preprocess() into variables\n    \"\"\"\n    X_train = pd.read_csv(TRAIN, converters={'fullVisitorId': str})\n    X_test = pd.read_csv(TEST, converters={'fullVisitorId': str})\n    y_train = pd.read_csv(Y, names=['LogRevenue']).T.squeeze()\n    \n    # This is the only `object` column, we drop it for train and evaluation\n    if drop_full_visitor_id: \n        X_train = X_train.drop(['fullVisitorId'], axis=1)\n        X_test = X_test.drop(['fullVisitorId'], axis=1)\n    return X_train, y_train, X_test","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X, y, X_test = load_preprocessed_dfs()\nX_train, X_val, y_train, y_val = train_test_split(X, y, test_size=0.15, random_state=1)\n\nprint(f\"Train shape: {X_train.shape}\")\nprint(f\"Validation shape: {X_val.shape}\")\nprint(f\"Test (submit) shape: {X_test.shape}\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def run_lgb(X_train, y_train, X_val, y_val, X_test):\n    \n    params = {\n        \"objective\" : \"regression\",\n        \"metric\" : \"rmse\",\n        \"num_leaves\" : 40,\n        \"learning_rate\" : 0.005,\n        \"bagging_fraction\" : 0.6,\n        \"feature_fraction\" : 0.6,\n        \"bagging_frequency\" : 6,\n        \"bagging_seed\" : 42,\n        \"verbosity\" : -1,\n        \"seed\": 42\n    }\n    \n    lgb_train_data = lgb.Dataset(X_train, label=y_train)\n    lgb_val_data = lgb.Dataset(X_val, label=y_val)\n\n    model = lgb.train(params, lgb_train_data, \n                      num_boost_round=5000,\n                      valid_sets=[lgb_train_data, lgb_val_data],\n                      early_stopping_rounds=100,\n                      verbose_eval=500)\n\n    y_pred_train = model.predict(X_train, num_iteration=model.best_iteration)\n    y_pred_val = model.predict(X_val, num_iteration=model.best_iteration)\n    y_pred_submit = model.predict(X_test, num_iteration=model.best_iteration)\n\n    print(f\"LGBM: RMSE val: {rmse(y_val, y_pred_val)}  - RMSE train: {rmse(y_train, y_pred_train)}\")\n    return y_pred_submit, model","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n# Train LGBM and generate predictions\nlgb_preds, lgb_model = run_lgb(X_train, y_train, X_val, y_val, X_test)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"LightGBM features importance...\")\ngain = lgb_model.feature_importance('gain')\nfeatureimp = pd.DataFrame({'feature': lgb_model.feature_name(), \n                   'split': lgb_model.feature_importance('split'), \n                   'gain': 100 * gain / gain.sum()}).sort_values('gain', ascending=False)\nprint(featureimp[:10])","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def run_xgb(X_train, y_train, X_val, y_val, X_test):\n    params = {'objective': 'reg:linear',\n              'eval_metric': 'rmse',\n              'eta': 0.001,\n              'max_depth': 10,\n              'subsample': 0.6,\n              'colsample_bytree': 0.6,\n              'alpha':0.001,\n              'random_state': 42,\n              'silent': True}\n\n    xgb_train_data = xgb.DMatrix(X_train, y_train)\n    xgb_val_data = xgb.DMatrix(X_val, y_val)\n    xgb_submit_data = xgb.DMatrix(X_test)\n\n    model = xgb.train(params, xgb_train_data, \n                      num_boost_round=2000, \n                      evals= [(xgb_train_data, 'train'), (xgb_val_data, 'valid')],\n                      early_stopping_rounds=100, \n                      verbose_eval=500\n                     )\n\n    y_pred_train = model.predict(xgb_train_data, ntree_limit=model.best_ntree_limit)\n    y_pred_val = model.predict(xgb_val_data, ntree_limit=model.best_ntree_limit)\n    y_pred_submit = model.predict(xgb_submit_data, ntree_limit=model.best_ntree_limit)\n\n    print(f\"XGB : RMSE val: {rmse(y_val, y_pred_val)}  - RMSE train: {rmse(y_train, y_pred_train)}\")\n    return y_pred_submit, model","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nxgb_preds, xgb_model = run_xgb(X_train, y_train, X_val, y_val, X_test)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def run_catboost(X_train, y_train, X_val, y_val, X_test):\n    model = CatBoostRegressor(iterations=1000,\n                             learning_rate=0.05,\n                             depth=10,\n                             eval_metric='RMSE',\n                             random_seed = 42,\n                             bagging_temperature = 0.2,\n                             od_type='Iter',\n                             metric_period = 50,\n                             od_wait=20)\n    model.fit(X_train, y_train,\n              eval_set=(X_val, y_val),\n              use_best_model=True,\n              verbose=True)\n    \n    y_pred_train = model.predict(X_train)\n    y_pred_val = model.predict(X_val)\n    y_pred_submit = model.predict(X_test)\n\n    print(f\"CatB: RMSE val: {rmse(y_val, y_pred_val)}  - RMSE train: {rmse(y_train, y_pred_train)}\")\n    return y_pred_submit, model","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n# Train Catboost and generate predictions\ncat_preds, cat_model = run_catboost(X_train, y_train, X_val, y_val,  X_test)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Note: this is currently being reconstructed!\nensemble_preds_70_30_00 = 0.7 * lgb_preds + 0.3 * cat_preds + 0.0 * xgb_preds \nensemble_preds_70_25_05 = 0.7 * lgb_preds + 0.25 * cat_preds + 0.05 * xgb_preds ","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def submit(predictions, filename='submit.csv'):\n    \"\"\"\n    Takes a (804684,) 1d-array of predictions and generates a submission file named filename\n    \"\"\"\n    _, _, X_submit = load_preprocessed_dfs(drop_full_visitor_id=False)\n    submission = X_submit[['fullVisitorId']].copy()\n    \n    submission.loc[:, 'PredictedLogRevenue'] = predictions\n    grouped_test = submission[['fullVisitorId', 'PredictedLogRevenue']].groupby('fullVisitorId').sum().reset_index()\n    grouped_test.to_csv(filename,index=False)\n\nsubmit(lgb_preds, \"submit-lgb.csv\")\n# Note: I disabled XGB to make the notebook run faster\nsubmit(xgb_preds, \"submit-xgb.csv\")\nsubmit(cat_preds, \"submit-cat.csv\")\nsubmit(ensemble_preds_70_30_00, \"submit-ensemble-70_30_00.csv\")\nsubmit(ensemble_preds_70_25_05, \"submit-ensemble-70_25_05.csv\")\n\nensemble_preds_70_30_00_pos = np.where(ensemble_preds_70_30_00 < 0, 0, ensemble_preds_70_30_00)\nsubmit(ensemble_preds_70_30_00_pos, \"submit-ensemble-70_30_00-positive.csv\")\n\nensemble_preds_70_25_05_pos = np.where(ensemble_preds_70_25_05 < 0, 0, ensemble_preds_70_25_05)\nsubmit(ensemble_preds_70_25_05_pos, \"submit-ensemble-70_25_05-positive.csv\")","metadata":{},"execution_count":null,"outputs":[]}]}