{"cells":[{"metadata":{"_uuid":"6ebdfd69663b3be63646273f40aaf6920ea4d9c7"},"cell_type":"markdown","source":"# Google Analytics Customer Revenue Prediction - Simple Exploration + LGBM Model LB 1.291\n\n## Problem Statement\n\nThe 80/20 rule has proven true for many businesses–only a small percentage of customers produce most of the revenue. As such, marketing teams are challenged to make appropriate investments in promotional strategies.\n\n### GStore\n\nRStudio, the developer of free and open tools for R and enterprise-ready products for teams to scale and share work, has partnered with Google Cloud and Kaggle to demonstrate the business impact that thorough data analysis can have.\n\nIn this kaggle competition,we are challenged to analyze a Google Merchandise Store (also known as GStore, where Google swag is sold) customer dataset to predict revenue per customer. \n\n### File Descriptions\n- train.csv - the training set - contains the same data as the BigQuery rstudio_train_set.\n- test.csv - the test set - contains the same data as the BigQuery rstudio_test_set.\n- sampleSubmission.csv - a sample submission file in the correct format. Contains all fullVisitorIds in test.csv.\n\n### Data Fields\n- fullVisitorId- A unique identifier for each user of the Google Merchandise Store.\n- channelGrouping - The channel via which the user came to the Store.\n- date - The date on which the user visited the Store.\n- device - The specifications for the device used to access the Store.\n- geoNetwork - This section contains information about the geography of the user.\n- sessionId - A unique identifier for this visit to the store.\n- socialEngagementType - Engagement type, either \"Socially Engaged\" or \"Not Socially Engaged\".\n- totals - This section contains aggregate values across the session.\n- trafficSource - This section contains information about the Traffic Source from which the session originated.\n- visitId - An identifier for this session. This is part of the value usually stored as the _utmb cookie. This is only unique to the user. For a completely unique ID, we should use a combination of fullVisitorId and visitId.\n- visitNumber - The session number for this user. If this is the first session, then this is set to 1.\n- visitStartTime - The timestamp (expressed as POSIX time).\n\n"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"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 \nfrom plotly.offline import init_notebook_mode, iplot\nimport plotly.graph_objs as go\nfrom plotly import tools\nimport numpy as np\ninit_notebook_mode(connected=True)\nimport matplotlib as mpl\nimport matplotlib.pyplot as plt\n%matplotlib inline\nimport scipy.stats as st\nimport missingno as msno","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d65d0c0f1beb86169bd6959a1fe9add18b221cb9"},"cell_type":"markdown","source":"### Data Mining\n\nThe data is shared in big query and csv format. The csv files contains some filed with json objects. \nLet us first explore what features of the datasets are json fields and then use a function done by Julian in his kernel to convert them and explore the revised dataset."},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df_train = pd.read_csv(\"../input/train.csv\",sep=',')\ndf_test = pd.read_csv(\"../input/test.csv\",sep=',')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"43fc754816ba619461ddcbc53f50b94dd0e5f01f"},"cell_type":"markdown","source":"Let us first explore how many features in the datasets are JSON fields in both train and test datasets.\n\n**Train set:**"},{"metadata":{"trusted":true,"_uuid":"ca2c05c4347e4d85a08b4ecc4250703dcc2fab99","_kg_hide-input":true},"cell_type":"code","source":"df_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9eee7aaa9fbd9d1b44dfcb146ec2dba0b8dad4ea","_kg_hide-input":true},"cell_type":"code","source":"df_test.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"24f74197a2232df39ec483136e3a2970b388501c"},"cell_type":"markdown","source":"It is observed that there are four JSON features in the train & test datasets namely 'device', 'geoNetwork', 'totals', 'trafficSource.\nThese features need to be flattened out .For this  I will be using a function written by  julian in his kernel https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields/notebook.\n\nAlso, lets view the top rows of the dataset."},{"metadata":{"trusted":true,"_uuid":"4927734a545b38b80e3adbf2b39f82b633e9a964","_kg_hide-input":true},"cell_type":"code","source":"json_columns = ['device', 'geoNetwork','totals', 'trafficSource']\ndef load_dataframe(filename):\n    path = \"../input/\" + filename\n    df = pd.read_csv(path, converters={column: json.loads for column in json_columns}, \n                     dtype={'fullVisitorId': 'str'})\n   \n    for column in json_columns:\n        column_as_df = json_normalize(df[column])\n        column_as_df.columns = [f\"{column}_{subcolumn}\" for subcolumn in column_as_df.columns]\n        df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)\n    return df","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c77b6629a6e16b0ad3a2fbb7cc8bba231edd0863"},"cell_type":"markdown","source":"### Exploratory Data Analysis\n\nNow let us run the above function for both train and test datasets and view the first few rows to understand the dataset in detail and perform detailed exploratory data analysis."},{"metadata":{"trusted":true,"_uuid":"2ab2fa61e1df52e2ac743bc6c6dd3ec5a76da561","_kg_hide-input":true},"cell_type":"code","source":"train = load_dataframe(\"train.csv\")\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9981c14d8ee2de7e32533c1f5949c202b3b47de3","_kg_hide-input":true},"cell_type":"code","source":"test = load_dataframe(\"test.csv\")\ntest.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d45f68befab72bd3383452cd0348ca55d2756f01"},"cell_type":"markdown","source":"### Exploratory Data Analysis\n\nNow that we mined the train and test data sets its time to do some exploratory data analysis. "},{"metadata":{"trusted":true,"_uuid":"144412592342077976b8d629d8b466fd32079eb4","_kg_hide-input":false,"_kg_hide-output":true},"cell_type":"code","source":"print(train.info(),test.info())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e690e3112f25a22ce39b6da55977b697990f8aae","_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"print(train.shape,test.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"65cb3cdb29b4de64c625db28b36328a934e6a0b9"},"cell_type":"markdown","source":"Let us look at what all are the **numerical variables** for both train and test sets\n- Train Set:"},{"metadata":{"trusted":true,"_uuid":"1fd5dfe3318dec5ce49ef544f245ee55a6199a15","_kg_hide-input":true},"cell_type":"code","source":"numeric_features_train = train.select_dtypes(include=[np.number])\nnumeric_features_train.columns","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"84f8adc47e926182dd4de04c316d31e0ff776fb7"},"cell_type":"markdown","source":"- Test Set:"},{"metadata":{"trusted":true,"_uuid":"82c61774ece0405454df00f39ccf5b9b051a720b","_kg_hide-input":false,"_kg_hide-output":true},"cell_type":"code","source":"numeric_features_test = test.select_dtypes(include=[np.number])\n\nnumeric_features_test.columns","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"eaa57c658ddde9aaa59b639ecd0ef7619de2be92"},"cell_type":"markdown","source":"Let us look at what all are the **categorical variables** for both train and test sets\n- Train Set "},{"metadata":{"trusted":true,"_uuid":"4658a978c1dee205d1e0d4dc5fe62b11df7d6a56","_kg_hide-input":false,"_kg_hide-output":true},"cell_type":"code","source":"categorical_features_train = train.select_dtypes(include=[np.object])\ncategorical_features_train.columns","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bdc410d0192daf4ffbcb4340d83bd9d91cd61113"},"cell_type":"markdown","source":"- Test Set:\n"},{"metadata":{"trusted":true,"_uuid":"efac42b7023504b7d4466e652afc55b155ac0ab3","_kg_hide-input":false,"_kg_hide-output":true},"cell_type":"code","source":"categorical_features_test = test.select_dtypes(include=[np.object])\ncategorical_features_test.columns","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"16beb356e0433c14bee6029f4c74528463ab4cda"},"cell_type":"markdown","source":"Let us now explore the **missing values** in both train and test sets.\n"},{"metadata":{"_uuid":"2f40c092f592b8c53c087eb9f2859dc2912302a9"},"cell_type":"markdown","source":"It is observed that there are some columns that contains \"not available in demo dataset\" as constant values predominently.So it is not going to be effective if we use these columns in our model prediction.So we can safely delete these features from both train and test datasets as below."},{"metadata":{"trusted":true,"_uuid":"5466ec0188d1afcaafe3cbe7b6e5e9585009280e","_kg_hide-input":true},"cell_type":"code","source":"print (\"Before removing constant columns - shape of train & test datasets: \", train.shape,test.shape)\ntrain = train.loc[:, (train != train.iloc[0]).any()]\ntest = test.loc[:, (test != test.iloc[0]).any()]\nprint (\"After Removing Constant Columns - shape of train & test datasets: \", train.shape,test.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b578a2968766f548a33390ad2e81bf6c9977c10e"},"cell_type":"markdown","source":"#### Missing values for all categorical features in Bar chart Representation\n\n- **Train set**"},{"metadata":{"trusted":true,"_uuid":"812c0619b4faaa182cb1c43802b0a0a8fa20d009","_kg_hide-input":true},"cell_type":"code","source":"total_test = categorical_features_train.isnull().sum().sort_values(ascending=False)\npercent = (categorical_features_train.isnull().sum()/categorical_features_train.isnull().count()).sort_values(ascending=False)*100\nmissing_data = pd.concat([total_test, percent], axis=1,join='outer', keys=['Total Missing Count', ' % of Total Observations'])\nmissing_data.index.name ='Feature'\nmissing_data.head(14)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d3a07c5dc9faca50e8e66d4da67642773e71d66f"},"cell_type":"markdown","source":"**Let us visualise the missing categorical features for train set:**"},{"metadata":{"trusted":true,"_uuid":"8c1d4743fb410ae90b2dd1ee1ae896e3c5907964","_kg_hide-input":true},"cell_type":"code","source":"missing_values = categorical_features_train.isnull().sum(axis=0).reset_index()\nmissing_values.columns = ['column_name', 'missing_count']\nmissing_values = missing_values.loc[missing_values['missing_count']>0]\nmissing_values = missing_values.sort_values(by='missing_count')\nind = np.arange(missing_values.shape[0])\nwidth = 0.1\nfig, ax = plt.subplots(figsize=(12,3))\nrects = ax.barh(ind, missing_values.missing_count.values, color='b')\nax.set_yticks(ind)\nax.set_yticklabels(missing_values.column_name.values, rotation='horizontal')\nax.set_xlabel(\"Missing Observations Count\")\nax.set_title(\"Missing Categorical Observations in Train Dataset\")\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"17f015a209610eb8507640330aee1c737e59df11","_kg_hide-input":true},"cell_type":"code","source":"total_test = categorical_features_test.isnull().sum().sort_values(ascending=False)\npercent = (categorical_features_test.isnull().sum()/categorical_features_test.isnull().count()).sort_values(ascending=False)*100\nmissing_data = pd.concat([total_test, percent], axis=1,join='outer', keys=['Total Missing Count', ' % of Total Observations'])\nmissing_data.index.name ='Feature'\nmissing_data.head(12)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6046ceb17a6a79bafa464daa2cbcd45792397e51"},"cell_type":"markdown","source":"**Let us visualise the missing categorical features test:**"},{"metadata":{"trusted":true,"_uuid":"08ed0665bdb9471d1bd5022b8d73123260d7ba0a","_kg_hide-input":true},"cell_type":"code","source":"missing_values = categorical_features_test.isnull().sum(axis=0).reset_index()\nmissing_values.columns = ['column_name', 'missing_count']\nmissing_values = missing_values.loc[missing_values['missing_count']>0]\nmissing_values = missing_values.sort_values(by='missing_count')\nind = np.arange(missing_values.shape[0])\nwidth = 0.1\nfig, ax = plt.subplots(figsize=(12,3))\nrects = ax.barh(ind, missing_values.missing_count.values, color='b')\nax.set_yticks(ind)\nax.set_yticklabels(missing_values.column_name.values, rotation='horizontal')\nax.set_xlabel(\"Missing Observations Count\")\nax.set_title(\"Missing Categorical Observations in Test Dataset\")\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3c6f8f626bbefce87e5b5f9b44a64d047d6e1feb"},"cell_type":"markdown","source":"### Univariate Analysis:\n\nLets perform  univariate analysis on some of the variables in the dataset and plot their distributions to unfold patterns or insights about the data\n\n**Geo Network Attributes:**\n\nLet us see what all geonetwork attributes are \n\n- geoNetwork_city\n- geoNetwork_continent\n- geoNetwork_country\n- geoNetwork_metro\n- geoNetwork_networkDomain\n- geoNetwork_region\n- geoNetwork_subContinent\n\nSo among the above geoNetwork attributes let us consider 'geoNetwork_country' attribute and visualise customer revenue"},{"metadata":{"trusted":true,"_uuid":"454909ae1744e3fdeab14c5a35c11771ee83ffc9","_kg_hide-input":true},"cell_type":"code","source":"colorscale = [[0, 'rgb(102,194,165)'], [0.0005, 'rgb(102,194,165)'], \n              [0.01, 'rgb(171,221,164)'], [0.02, 'rgb(230,245,152)'], \n              [0.04, 'rgb(255,255,191)'], [0.05, 'rgb(254,224,139)'], \n              [0.10, 'rgb(253,174,97)'], [0.25, 'rgb(213,62,79)'], [1.0, 'rgb(158,1,66)']]\n\ndata = [ dict(\n        type = 'choropleth',\n        autocolorscale = False,\n        colorscale = colorscale,\n        showscale = True,\n        locations = train[\"geoNetwork_country\"].value_counts().index,\n        locationmode = 'country names',\n        z = train[\"geoNetwork_country\"].value_counts().values,\n        marker = dict(\n            line = dict(color = 'rgb(250,250,225)', width = 1)),\n            colorbar = dict( title = 'Customer Visits ')\n            ) \n       ]\n\nlayout = dict(\n    height=600,\n    title = 'World Wide Customer Visit Distribution',\n    geo = dict(\n        showframe = True,\n        showocean = True,\n        oceancolor = 'rgb(28,107,160)',\n        projection = dict(\n        type = 'orthographic',\n            rotation = dict(\n                    lon = 50,\n                    lat = 10),\n        ),\n        lonaxis =  dict(\n                showgrid = True,\n                gridcolor = 'rgb(12, 102, 102)'\n            ),\n        lataxis = dict(\n                showgrid = True,\n                gridcolor = 'rgb(12, 102, 102)'\n                )\n            ),\n        )\nfig = dict(data=data, layout=layout)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5001ced228d601557a147f3f03d6ebab499e3183"},"cell_type":"markdown","source":"Here are some  comprehensive listing of the ** world projections ** which we can visualise .\n- equirectangular\n- mercator\n- orthographic\n- natural earth\n- kavrayskiy7\n- miller\n- robinson\n- eckert4\n- azimuthal equal area\n- azimuthal equidistant\n- conic equal area\n- conic conformal\n- conic equidistant\n- gnomonic\n- stereographic\n- mollweide\n- hammer\n- transverse mercator\n\nTo see the different visualisation effects using the above parameter replace the value of type = 'conic equal area' in the layout geo section ."},{"metadata":{"trusted":true,"_uuid":"47d39a1884a8a8b7092153322ac8fb9b99350052","_kg_hide-input":true},"cell_type":"code","source":"colorscale = [[0, 'rgb(102,194,165)'], [0.0005, 'rgb(102,194,165)'], \n              [0.01, 'rgb(171,221,164)'], [0.02, 'rgb(230,245,152)'], \n              [0.04, 'rgb(255,255,191)'], [0.05, 'rgb(254,224,139)'], \n              [0.10, 'rgb(253,174,97)'], [0.25, 'rgb(213,62,79)'], [1.0, 'rgb(158,1,66)']]\n\ndata = [ dict(\n        type = 'choropleth',\n        autocolorscale = False,\n        colorscale = colorscale,\n        showscale = True,\n        locations = train[\"geoNetwork_country\"].value_counts().index,\n        locationmode = 'country names',\n        z = train[\"geoNetwork_country\"].value_counts().values,\n        marker = dict(\n            line = dict(color = 'rgb(250,250,225)', width = 1)),\n            colorbar = dict( title = 'Customer Visits ')\n            ) \n       ]\n\nlayout = dict(\n    height=600,\n    title = 'World Wide Customer Visit Distribution',\n    geo = dict(\n        showframe = True,\n        showocean = True,\n        oceancolor = 'rgb(28,107,160)',\n        projection = dict(\n        type = 'conic equal area',\n            rotation = dict(\n                    lon = 50,\n                    lat = 10),\n        ),\n        lonaxis =  dict(\n                showgrid = True,\n                gridcolor = 'rgb(12, 102, 102)'\n            ),\n        lataxis = dict(\n                showgrid = True,\n                gridcolor = 'rgb(12, 102, 102)'\n                )\n            ),\n        )\nfig = dict(data=data, layout=layout)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c4ab1c38c6e8cecedf1f92d72b7b4b4cf15c4c3b"},"cell_type":"markdown","source":"Let us now visualise by city, country ,sub continent and continent"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"62863e5bedc244f850459917a3519673d544b1d2"},"cell_type":"code","source":"geo_cols = [\"geoNetwork_city\", \"geoNetwork_country\", \"geoNetwork_subContinent\", \"geoNetwork_continent\"]\ncolors = [\"#d6a5ff\", \"#fca6da\", \"#f4d39c\", \"#a9fcca\"]\ntraces = []\nfor i, col in enumerate(geo_cols):\n    t = train[col].value_counts()\n    traces.append(go.Bar(marker=dict(color=colors[i]),orientation=\"h\", y = t.index[:15], x = t.values[:15]))\n\nfig = tools.make_subplots(rows=2, cols=2, \n                          subplot_titles=[\"Visits: City\", \"Visits: Country\",\"Visits: Sub Continent\",\"Visits: Continent\"]\n                          , print_grid=False)\nfig.append_trace(traces[0], 1, 1)\nfig.append_trace(traces[1], 1, 2)\nfig.append_trace(traces[2], 2, 1)\nfig.append_trace(traces[3], 2, 2)\n\nfig['layout'].update(height=600,width=1000, showlegend=False)\niplot(fig)\n\ntrain[\"totals_transactionRevenue\"] = train[\"totals_transactionRevenue\"].astype('float')\n\nfig = tools.make_subplots(rows=2, cols=2, subplot_titles=[\"Mean Revenue by City\", \"Mean Revenue by Country\",\"Mean Revenue by Sub Continent\",\"Mean Revenue by Continent\"], print_grid=False)\n\ncolors = [\"red\", \"green\", \"purple\",\"blue\"]\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()\n    tr = go.Bar(x = tmp[\"Mean Revenue\"], orientation=\"h\", marker=dict(opacity=0.5, color=colors[i]), y = tmp[col])\n    trs.append(tr)\n\nfig.append_trace(trs[0], 1, 1)\nfig.append_trace(trs[1], 1, 2)\nfig.append_trace(trs[2], 2, 1)\nfig.append_trace(trs[3], 2, 2)\n\nfig['layout'].update(height=600,width=1000, showlegend=False)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d366e19f0327fbf89665c8232041a3f5e8d4777b"},"cell_type":"markdown","source":"**Channel Grouping**"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"db0b89904565467d2993d5f85ea91d545d5813e3"},"cell_type":"code","source":"t = train['channelGrouping'].value_counts()\nvalues1 = t.values \nindex1 = t.index\ndomain1 = {'x': [0.2, 0.50], 'y': [0.0, 0.33]}\nfig = {\n  \"data\": [\n    {\n      \"values\": values1,\n      \"labels\": index1,\n      \"domain\": {\"x\": [0, .48]},\n    \"marker\" : dict(colors=[\"#f77b9c\" ,'#ab97db',  '#b0b1b2']),\n      \"name\": \"Channel Grouping\",\n      \"hoverinfo\":\"label+percent+name\",\n      \"hole\": .7,\n      \"type\": \"pie\"\n    }\n   ],\n  \"layout\": {\"title\":\"Channel Grouping\",\n      \"annotations\": [\n            {\n                \"font\": {\n                    \"size\": 20\n                },\n                \"showarrow\": False,\n                \"text\": \"Channel Grouping\",\n                \"x\": 0.11,\n                \"y\": 0.5\n            }\n        ]\n    }\n}\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7f91eb06df3b6ab31bbf2609271e572b3300ff7b"},"cell_type":"markdown","source":"**Traffic Attributes**"},{"metadata":{"trusted":true,"_uuid":"f3c93e3071dcc557459f641843212f2feffc0ff5"},"cell_type":"code","source":"fig = tools.make_subplots(rows=1, cols=2,subplot_titles=[\"Traffic Source Campaign (not-set removed)\", \"Traffic Source Medium\"], print_grid=False)\n \ncolors = [\"#d6a5ff\", \"#fca6da\", \"#f4d39c\", \"#a9fcca\"]\nt1 = train[\"trafficSource_campaign\"].value_counts()\nt2 = train[\"trafficSource_medium\"].value_counts()\n\ntr1 = go.Bar(x = t1.index, y = t1.values, marker=dict(color=colors[1]))\ntr2 = go.Bar(x = t2.index, y = t2.values, marker=dict(color=colors[2]))\n\nfig.append_trace(tr1, 1, 1)\nfig.append_trace(tr2, 1, 2)\n\n\nfig['layout'].update(height=400, margin=dict(b=100), showlegend=False)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2694a7c6adf47f89b7363e01e46de9007729002a"},"cell_type":"markdown","source":" **Visits by Date, Month and Day**\n \n Let us first create a function to create new features by month,day & weekday"},{"metadata":{"_kg_hide-input":false,"trusted":true,"_uuid":"9d05aee7ec9e2af1d067bd5b7af706d3295aa153"},"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 ","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7db18035bfa96386e57737dc0f6cfa1ae4d0c490"},"cell_type":"markdown","source":"Now let us pass the train dataset to the above function to extract day,month and weekday column"},{"metadata":{"trusted":true,"_uuid":"c3625a12b29e523faf8fa088351c8a34a8052d01"},"cell_type":"code","source":"train = add_date_features(train)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d552caabd4c6b8b266fc7b2051ad46fd0782243b"},"cell_type":"markdown","source":"Now let us visualise Visits by date & Monthly revenue by date using plotly "},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f98a374c28710a9a133930bc1ae8b3c5d0d21d7a"},"cell_type":"code","source":"# Visualization for Visits by date\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# Visualization for Visits by monthly revenue\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=\"Monthly Revenue by Date\", height=400)\nfig = go.Figure(data = [tr], layout = layout)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b432ab61f2a75e694ca60147c91dfa631fe2f2b4"},"cell_type":"markdown","source":"**Visits by Month, Month Day & Week Day**"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"403781c78bc3f71714533d9752d6d692a46b7fc0"},"cell_type":"code","source":"fig = tools.make_subplots(rows=1, cols=3, subplot_titles=[\"Visits by Month\", \"Visits by Month Day\", \"Visits by Week Day\"], 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)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8953d603fbc5672948abcb3a2d2bb9ef8f47ff3f"},"cell_type":"markdown","source":"**Mean Revenue by Month, Month Day & Week Day**"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"2feddfc0fa1e7adc0c928cf6e90df7ff77efb1c5"},"cell_type":"code","source":"tmp1 = 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=\"yellow\", opacity=0.5), y = tmp1.totals_transactionRevenue)\ntr2 = go.Bar(x = tmp2.day, marker=dict(color=\"blue\", opacity=0.5), y = tmp2.totals_transactionRevenue)\ntr3 = go.Bar(x = tmp3.weekday, marker=dict(color=\"violet\", 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)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b40f6a5d94e470251ab73274c9424c75cafbc69c"},"cell_type":"markdown","source":"### Visitor Profile\n\nLets create the visitor profile by aggregating the rows for every customer.\n"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f4efcf2b7a4a687f0c81d22285006e1f10e92dd6"},"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()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a0e67d6a5b25516c8b756ae426c1754c72d0e0f2"},"cell_type":"markdown","source":"**Total Revenue Distribution**\n\nLet us look at the distribution of total revenue without normalisation "},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5782c94b66e92ad78f0e3c8c284365ecef0d076c"},"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=(10,6))\nsns.distplot(non_zero)\nplt.title(\"Distribution of Non-Zero Total Transactions\");\nplt.xlabel(\"Total Transactions\");","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d01269cdfca90cfca6de8f2719d2d471f98e1d43"},"cell_type":"markdown","source":"Now let us apply natural log transformation on the transactions and visualise it."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"dd58794e5b0f936e22dc3f109156f7734cb98e2a"},"cell_type":"code","source":"plt.figure(figsize=(12,6))\nsns.distplot(np.log1p(non_zero))\nplt.title(\"Natural Log Distribution of Non Zero Total Transactions\");\nplt.xlabel(\"Natural Log - Total Transactions\");","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e80bdf70ac616ace2029da3c60549a5bc59c70e9"},"cell_type":"markdown","source":"## Baseline Model\n\n### PreProcessing\n\nDuring preprocessing step, let us identify which columns need to be removed considering the following factors.\n\n- Columns with constant values\n- Id columns and other non relevant columns"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"8ec347316e07a59d00b48b68f325de2319ec8493"},"cell_type":"code","source":"## find constant columns\nconstant_columns = []\nfor col in train.columns:\n    if len(train[col].value_counts()) == 1:\n        constant_columns.append(col)\n\n## non relevant columns\nnon_relevant = [\"visitNumber\", \"date\", \"fullVisitorId\", \"sessionId\", \"visitId\", \"visitStartTime\"]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"23609f062661022cac8756e669fb783b4fdc5a54"},"cell_type":"markdown","source":"Lets now load test dataset and apply the date function defined above so that it can be used to make predictions"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f71780d1dcb43a588e43fb9ce424b13d86af524f"},"cell_type":"code","source":"test = add_date_features(test)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"273249d4dfff0a0caf2a26c48b32b337c52f5329"},"cell_type":"markdown","source":"**Categorical Feature Handling**"},{"metadata":{"trusted":true,"_uuid":"74a2cb2c18334799664868274bae67aa3a9de6a4"},"cell_type":"code","source":"from sklearn.preprocessing import LabelEncoder\n\ncategorical_columns = [c for c in train.columns if not c.startswith(\"total\")]\ncategorical_columns = [c for c in categorical_columns if c not in constant_columns + non_relevant]\nfor c in categorical_columns:\n\n    le = LabelEncoder()\n    train_vals = list(train[c].values.astype(str))\n    test_vals = list(test[c].values.astype(str))\n    \n    le.fit(train_vals + test_vals)\n    \n    train[c] = le.transform(train_vals)\n    test[c] = le.transform(test_vals)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fae628f715c9ea7a10bb80f3fcbb491d40d58c59"},"cell_type":"markdown","source":"**Numerical Feature Handling**\n\nTo handle numerical features first let us create a function called \"normalize_numerical_columns\" that normalises the values "},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f88365451766e84f1563fcfa5514d208244cdf0f"},"cell_type":"code","source":"def normalize_numerical_columns(df, isTrain = True):\n    df[\"totals_hits\"] = df[\"totals_hits\"].astype(float)\n    df[\"totals_hits\"] = (df[\"totals_hits\"] - min(df[\"totals_hits\"])) / (max(df[\"totals_hits\"]) - min(df[\"totals_hits\"]))\n\n    df[\"totals_pageviews\"] = df[\"totals_pageviews\"].astype(float)\n    df[\"totals_pageviews\"] = (df[\"totals_pageviews\"] - min(df[\"totals_pageviews\"])) / (max(df[\"totals_pageviews\"]) - min(df[\"totals_pageviews\"]))\n    \n    if isTrain:\n        df[\"totals_transactionRevenue\"] = df[\"totals_transactionRevenue\"].fillna(0.0)\n    return df ","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9eba65f7ccb1eb78ab9875ce9dc179e8d52786cd"},"cell_type":"markdown","source":"Now let apply the above function on both train and test datasets."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"715f5afe3c5219a39a30631da1d94d8df5fee6a5"},"cell_type":"code","source":"train = normalize_numerical_columns(train)\ntest = normalize_numerical_columns(test, isTrain = False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"260c2a003ed6f6d68125445c259ac65a51f5bdf7"},"cell_type":"markdown","source":"**Create a train and validation sets **"},{"metadata":{"trusted":true,"_uuid":"0f89054214a35a624cac7f8163532cae2cfa7149"},"cell_type":"code","source":"from sklearn.model_selection import train_test_split\nfeatures = [c for c in train.columns if c not in constant_columns + non_relevant]\nfeatures.remove(\"totals_transactionRevenue\")\ntrain[\"totals_transactionRevenue\"] = np.log1p(train[\"totals_transactionRevenue\"].astype(float))\ntrain_x, valid_x, train_y, valid_y = train_test_split(train[features], train[\"totals_transactionRevenue\"], test_size=0.25, random_state=20)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0d594c11f35262cd85c422d41885ec313444443f"},"cell_type":"markdown","source":"**Create LGBM model and train it**"},{"metadata":{"trusted":true,"_uuid":"14e5e9b91ba7690666c4cccf6ec0c3fc3d2180ba"},"cell_type":"code","source":"import lightgbm as lgb \n\nlgb_params = {\"objective\" : \"regression\", \"metric\" : \"rmse\",\n              \"num_leaves\" : 50, \"learning_rate\" : 0.02, \n              \"bagging_fraction\" : 0.75, \"feature_fraction\" : 0.8, \"bagging_frequency\" : 9}\n    \nlgb_train = lgb.Dataset(train_x, label=train_y)\nlgb_val = lgb.Dataset(valid_x, label=valid_y)\nmodel = lgb.train(lgb_params, lgb_train, 700, valid_sets=[lgb_val], early_stopping_rounds=150, verbose_eval=20)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7c12a5d2a8d7c53c4021228a44577924d14b0344"},"cell_type":"markdown","source":"**Generate Predictions & Submission**"},{"metadata":{"trusted":true,"_uuid":"32091f60feb5d1aea26b201e607b5a6857175bde"},"cell_type":"code","source":"preds = model.predict(test[features], num_iteration=model.best_iteration)\ntest[\"PredictedLogRevenue\"] = np.expm1(preds)\nsubmission = test.groupby(\"fullVisitorId\").agg({\"PredictedLogRevenue\" : \"sum\"}).reset_index()\nsubmission[\"PredictedLogRevenue\"] = np.log1p(submission[\"PredictedLogRevenue\"])\nsubmission[\"PredictedLogRevenue\"] =  submission[\"PredictedLogRevenue\"].apply(lambda x : 0.0 if x < 0 else x)\nsubmission.to_csv(\"baseline.csv\", index=False)\nsubmission.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6b8722d88b3a0b8a6cfdf0df35af031db896878a"},"cell_type":"markdown","source":"\n\n# If you liked this kernel greatly appreciate to UPVOTE"},{"metadata":{"trusted":true,"_uuid":"e297e3cdb177531cd8201e4c11abc230f828583e"},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}