{"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":"# Loading the data and have a quick look","metadata":{}},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport matplotlib.pyplot as plt\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2021-12-07T02:01:58.195485Z","iopub.execute_input":"2021-12-07T02:01:58.196494Z","iopub.status.idle":"2021-12-07T02:01:58.216226Z","shell.execute_reply.started":"2021-12-07T02:01:58.196446Z","shell.execute_reply":"2021-12-07T02:01:58.2154Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Necessary librarys\nimport os # it's a operational system library, to set some informations\nimport random # random is to generate random values\n\nimport pandas as pd # to manipulate data frames \nimport numpy as np # to work with matrix\nfrom scipy.stats import kurtosis, skew # it's to explore some statistics of numerical values\nimport statistics as st \n\nimport matplotlib.pyplot as plt # to graphics plot\nimport seaborn as sns # a good library to graphic plots\ncolor = sns.color_palette()\nimport squarify # to better understand proportion of categorys - it's a treemap layout algorithm\n\nfrom sklearn.preprocessing import StandardScaler \nfrom scipy import stats #The sklearn. preprocessing package provides several common utility functions and transformer classes to change raw feature vectors into a representation that is more suitable for the downstream estimators.\n\n# Importing librarys to use on interactive graphs\nfrom plotly.offline import init_notebook_mode, iplot, plot \nimport plotly.graph_objs as go \n\nimport json # to convert json in df\nfrom pandas.io.json import json_normalize # to normalize the json file\n\nfrom plotly import tools\nimport plotly.offline as py\npy.init_notebook_mode(connected=True)\nimport plotly.graph_objs as go\n\nfrom sklearn import model_selection, preprocessing, metrics\nfrom sklearn.model_selection import train_test_split\nimport lightgbm as lgb\n\npd.options.mode.chained_assignment = None\npd.options.display.max_columns = 999\nfrom sklearn.metrics import roc_auc_score\nfrom sklearn.metrics import confusion_matrix\n\n# to set a style to all graphs\nplt.style.use('fivethirtyeight')\ninit_notebook_mode(connected=True)\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T02:02:00.858133Z","iopub.execute_input":"2021-12-07T02:02:00.85848Z","iopub.status.idle":"2021-12-07T02:02:00.873201Z","shell.execute_reply.started":"2021-12-07T02:02:00.858447Z","shell.execute_reply":"2021-12-07T02:02:00.872535Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"columns = ['device', 'geoNetwork', 'totals', 'trafficSource'] # Columns that have json format\n\ndir_path = \"/kaggle/input/ga-customer-revenue-prediction/\" \n\n# p is a fractional number to skiprows and read just a random sample of the our dataset. \np = 0.07 # *** In this case we will use 50% of data set *** #\n\n#Code to transform the json format columns in table\ndef json_read(df):\n    #joining the [ path + df received]\n    data_frame = dir_path + df\n    \n    #Importing the dataset\n    df = pd.read_csv(data_frame, \n                     converters={column: json.loads for column in columns}, # loading the json columns properly\n                     dtype={'fullVisitorId': 'str'}, # transforming this column to string\n                     skiprows=lambda i: i>0 and random.random() > p)# Number of rows that w\n    for column in columns: #loop to finally transform the columns in data frame\n        #It will normalize and set the json to a table\n        column_as_df = json_normalize(df[column]) \n        # here will be set the name using the category and subcategory of json columns\n        column_as_df.columns = [f\"{column}.{subcolumn}\" for subcolumn in column_as_df.columns] \n        # after extracting the values, let drop the original columns\n        df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)\n        \n    # Printing the shape of dataframes that was imported     \n    print(f\"Loaded {os.path.basename(data_frame)}. Shape: {df.shape}\")\n    return df # returning the df after importing and transforming\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T02:02:28.12433Z","iopub.execute_input":"2021-12-07T02:02:28.124842Z","iopub.status.idle":"2021-12-07T02:02:28.132411Z","shell.execute_reply.started":"2021-12-07T02:02:28.124794Z","shell.execute_reply":"2021-12-07T02:02:28.131807Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time \n# %%time is used to calculate the timing of code chunk execution #\n\n# We will import the data using the name and extension that will be concatenated with dir_path\ndf_train = json_read(\"train.csv\") \n# The same to test dataset\ndf_test = json_read(\"test.csv\") ","metadata":{"execution":{"iopub.status.busy":"2021-12-07T02:02:32.147592Z","iopub.execute_input":"2021-12-07T02:02:32.148189Z","iopub.status.idle":"2021-12-07T02:03:03.181393Z","shell.execute_reply.started":"2021-12-07T02:02:32.14815Z","shell.execute_reply":"2021-12-07T02:03:03.180383Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# This command shows the first 5 rows of our dataset\ndf_train.head()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T02:03:03.183465Z","iopub.execute_input":"2021-12-07T02:03:03.184014Z","iopub.status.idle":"2021-12-07T02:03:03.226351Z","shell.execute_reply.started":"2021-12-07T02:03:03.183966Z","shell.execute_reply":"2021-12-07T02:03:03.225594Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.info()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T02:03:03.556748Z","iopub.execute_input":"2021-12-07T02:03:03.557426Z","iopub.status.idle":"2021-12-07T02:03:03.882301Z","shell.execute_reply.started":"2021-12-07T02:03:03.557394Z","shell.execute_reply":"2021-12-07T02:03:03.8817Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#df_train.mean()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:09.379717Z","iopub.execute_input":"2021-12-07T01:41:09.379942Z","iopub.status.idle":"2021-12-07T01:41:09.383771Z","shell.execute_reply.started":"2021-12-07T01:41:09.379914Z","shell.execute_reply":"2021-12-07T01:41:09.382835Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#df_train.median()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:09.384823Z","iopub.execute_input":"2021-12-07T01:41:09.385075Z","iopub.status.idle":"2021-12-07T01:41:09.396218Z","shell.execute_reply.started":"2021-12-07T01:41:09.385048Z","shell.execute_reply":"2021-12-07T01:41:09.395406Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#df_train.min()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:09.397352Z","iopub.execute_input":"2021-12-07T01:41:09.397672Z","iopub.status.idle":"2021-12-07T01:41:09.409012Z","shell.execute_reply.started":"2021-12-07T01:41:09.397643Z","shell.execute_reply":"2021-12-07T01:41:09.408377Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#df_train.max()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:09.411611Z","iopub.execute_input":"2021-12-07T01:41:09.412082Z","iopub.status.idle":"2021-12-07T01:41:09.41923Z","shell.execute_reply.started":"2021-12-07T01:41:09.412046Z","shell.execute_reply":"2021-12-07T01:41:09.418396Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#df_train.stdev()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:09.420195Z","iopub.execute_input":"2021-12-07T01:41:09.420792Z","iopub.status.idle":"2021-12-07T01:41:09.431807Z","shell.execute_reply.started":"2021-12-07T01:41:09.42076Z","shell.execute_reply":"2021-12-07T01:41:09.431156Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train[\"totals.transactionRevenue\"] = df_train[\"totals.transactionRevenue\"].astype('float')\ndf_train['Has Revenue?'] = df_train['totals.transactionRevenue'].apply(lambda x: 1 if x>0 else 0)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T02:06:00.666347Z","iopub.execute_input":"2021-12-07T02:06:00.666663Z","iopub.status.idle":"2021-12-07T02:06:00.758171Z","shell.execute_reply.started":"2021-12-07T02:06:00.666632Z","shell.execute_reply":"2021-12-07T02:06:00.757208Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.head()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T02:06:14.960099Z","iopub.execute_input":"2021-12-07T02:06:14.960411Z","iopub.status.idle":"2021-12-07T02:06:15.003267Z","shell.execute_reply.started":"2021-12-07T02:06:14.96038Z","shell.execute_reply":"2021-12-07T02:06:15.002325Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 1. First thing first, analyzing Has Revenue?\nSince we are predicting the natural log of sum of all transactions of the user, let us sum up the transaction revenue at user level and take a log and visulize in a scatter plot","metadata":{}},{"cell_type":"code","source":"#df_train[\"totals.transactionRevenue\"] = df_train[\"totals.transactionRevenue\"].astype('float')\ngdf = df_train.groupby(\"fullVisitorId\")[\"totals.transactionRevenue\"].sum().reset_index()\n\nplt.figure(figsize=(8,6))\nplt.scatter(range(gdf.shape[0]), np.sort(np.log1p(gdf[\"totals.transactionRevenue\"].values)))\nplt.xlabel('index', fontsize=12)\nplt.ylabel('TransactionRevenue', fontsize=12)\nplt.show()\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:09.432711Z","iopub.execute_input":"2021-12-07T01:41:09.433228Z","iopub.status.idle":"2021-12-07T01:41:09.895257Z","shell.execute_reply.started":"2021-12-07T01:41:09.433196Z","shell.execute_reply":"2021-12-07T01:41:09.89462Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This visual has confirmed 80/20 rule that a small percentage of customers product most of the revenue. Therefore, the marketing team are in need of making smart marketing strategies to reach those high value customers. ","metadata":{}},{"cell_type":"code","source":"nzi = pd.notnull(df_train[\"totals.transactionRevenue\"]).sum()\nnzr = (gdf[\"totals.transactionRevenue\"]>0).sum()\nprint(\"Number of instances in train set with non-zero revenue : \", nzi, \" and ratio is : \", nzi / df_train.shape[0])\nprint(\"Number of unique customers with non-zero revenue : \", nzr, \"and the ratio is : \", nzr / gdf.shape[0])","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:09.896214Z","iopub.execute_input":"2021-12-07T01:41:09.896901Z","iopub.status.idle":"2021-12-07T01:41:09.904201Z","shell.execute_reply.started":"2021-12-07T01:41:09.896864Z","shell.execute_reply":"2021-12-07T01:41:09.903343Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"So the ratio of customers with revenue vs. customer with no revenue is 1.3% ","metadata":{}},{"cell_type":"markdown","source":"# 2. Data Reprocessing","metadata":{}},{"cell_type":"markdown","source":"Before doing any further analysis, I'm going to convert the NaN value in totals.transactionRevenue to 0.","metadata":{}},{"cell_type":"code","source":"df_train['totals.transactionRevenue'][df_train['totals.transactionRevenue'].isna()]=0","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:09.905789Z","iopub.execute_input":"2021-12-07T01:41:09.906364Z","iopub.status.idle":"2021-12-07T01:41:09.91679Z","shell.execute_reply.started":"2021-12-07T01:41:09.906322Z","shell.execute_reply":"2021-12-07T01:41:09.915971Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Before doing any further analysis, I'm going to find out the constant values that is not useful and save time on manipulating and processing","metadata":{}},{"cell_type":"code","source":"discovering_consts = [col for col in df_train.columns if df_train[col].nunique() == 1]\nprint(\"Columns with just one value: \", len(discovering_consts), \"columns\")\nprint(\"Name of constant columns: \\n\", discovering_consts)\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:09.917898Z","iopub.execute_input":"2021-12-07T01:41:09.91819Z","iopub.status.idle":"2021-12-07T01:41:10.569929Z","shell.execute_reply.started":"2021-12-07T01:41:09.918153Z","shell.execute_reply":"2021-12-07T01:41:10.569078Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.head()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:10.57108Z","iopub.execute_input":"2021-12-07T01:41:10.571332Z","iopub.status.idle":"2021-12-07T01:41:10.61523Z","shell.execute_reply.started":"2021-12-07T01:41:10.571301Z","shell.execute_reply":"2021-12-07T01:41:10.614313Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#dropping the constant colums\ndf_train = df_train.drop(columns = discovering_consts)\n#dropping \n#Next steps: \n#drop columns that are not meaningful that much\n#df_train.drop(columns = ['sessionId','visitId','trafficSource.referralPath','trafficSource.adwordsClickInfo.page','trafficSource.adwordsClickInfo.slot','trafficSource.adwordsClickInfo.gclId','trafficSource.adContent'])\n\n#Converting fields to categorical of the data\n#Step 1: check the portion of the missing values (if missing value exceeds 90%/80%, I should throw the field out)\n#Step 2: I need to check the number of occurances in order for each term, I want to see how fast it drops (). I decide a threshhold and I cut. \n#Step 3: \n\n#df_test = df_test.drop(columns = discovering_consts)\n\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:10.616518Z","iopub.execute_input":"2021-12-07T01:41:10.616751Z","iopub.status.idle":"2021-12-07T01:41:10.876191Z","shell.execute_reply.started":"2021-12-07T01:41:10.616724Z","shell.execute_reply":"2021-12-07T01:41:10.875579Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#drop columns that are not meaningful that much\ndf_train = df_train.drop(columns = ['sessionId','visitId','trafficSource.referralPath','trafficSource.adwordsClickInfo.page','trafficSource.adwordsClickInfo.slot','trafficSource.adwordsClickInfo.gclId','trafficSource.adContent'])\n#df_test = df_test.drop(columns = ['sessionId','visitId','trafficSource.referralPath','trafficSource.adwordsClickInfo.page','trafficSource.adwordsClickInfo.slot','trafficSource.adwordsClickInfo.gclId','trafficSource.adContent'])\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:10.877513Z","iopub.execute_input":"2021-12-07T01:41:10.877935Z","iopub.status.idle":"2021-12-07T01:41:10.926605Z","shell.execute_reply.started":"2021-12-07T01:41:10.877905Z","shell.execute_reply":"2021-12-07T01:41:10.925747Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n#Ask Yenson how to also count 'not available in demo dataset' and \"(not set)\"\n\n#percent_missing = (df_train.isnull().sum()+df_train['not available in demo dataset'].value_counts(normalize=True)) * 100 / len(df_train)\n#missing_value_df = pd.DataFrame({'column_name': df_train.columns,\n                                 #'percent_missing': percent_missing})\n#missing_value_df","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:10.927976Z","iopub.execute_input":"2021-12-07T01:41:10.928183Z","iopub.status.idle":"2021-12-07T01:41:10.931308Z","shell.execute_reply.started":"2021-12-07T01:41:10.928159Z","shell.execute_reply":"2021-12-07T01:41:10.930611Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:10.932406Z","iopub.execute_input":"2021-12-07T01:41:10.93264Z","iopub.status.idle":"2021-12-07T01:41:10.969162Z","shell.execute_reply.started":"2021-12-07T01:41:10.932612Z","shell.execute_reply":"2021-12-07T01:41:10.968535Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(df_train.apply(lambda col: col.unique()))","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:10.969993Z","iopub.execute_input":"2021-12-07T01:41:10.970536Z","iopub.status.idle":"2021-12-07T01:41:11.206654Z","shell.execute_reply.started":"2021-12-07T01:41:10.970506Z","shell.execute_reply":"2021-12-07T01:41:11.205771Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#print(df_train.apply(lambda col: col.unique()))\nprint(df_train.nunique())\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:11.207886Z","iopub.execute_input":"2021-12-07T01:41:11.208375Z","iopub.status.idle":"2021-12-07T01:41:11.425801Z","shell.execute_reply.started":"2021-12-07T01:41:11.208328Z","shell.execute_reply":"2021-12-07T01:41:11.424941Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"#drop visitStartTime that has large number of unique values and wouldn't contribute to the model\ndf_train = df_train.drop(columns = ['visitStartTime','geoNetwork.networkDomain'])\n#df_test = df_test.drop(columns = ['visitStartTime','geoNetwork.networkDomain'])\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:11.426851Z","iopub.execute_input":"2021-12-07T01:41:11.427124Z","iopub.status.idle":"2021-12-07T01:41:11.4541Z","shell.execute_reply.started":"2021-12-07T01:41:11.427095Z","shell.execute_reply":"2021-12-07T01:41:11.453229Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Adding logTotalRevenue as a new column","metadata":{}},{"cell_type":"code","source":"df_train['logTotalRevenue'] = df_train['totals.transactionRevenue'].apply(lambda x: np.log(1+x))","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:11.455108Z","iopub.execute_input":"2021-12-07T01:41:11.455317Z","iopub.status.idle":"2021-12-07T01:41:11.536402Z","shell.execute_reply.started":"2021-12-07T01:41:11.455293Z","shell.execute_reply":"2021-12-07T01:41:11.535487Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.corr()\nsns.heatmap(df_train.corr());","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:11.53758Z","iopub.execute_input":"2021-12-07T01:41:11.5378Z","iopub.status.idle":"2021-12-07T01:41:11.83744Z","shell.execute_reply.started":"2021-12-07T01:41:11.537776Z","shell.execute_reply":"2021-12-07T01:41:11.836581Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#making a new data frame excluding fullVisitorId before \ndf_train_corr_set=df_train.drop(columns = ['fullVisitorId'])","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:11.838987Z","iopub.execute_input":"2021-12-07T01:41:11.839307Z","iopub.status.idle":"2021-12-07T01:41:11.86747Z","shell.execute_reply.started":"2021-12-07T01:41:11.839266Z","shell.execute_reply":"2021-12-07T01:41:11.86659Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from pandas_profiling import ProfileReport\nprofile = ProfileReport(df_train_corr_set.sample(n=10000))\nprofile.to_widgets()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:41:11.868535Z","iopub.execute_input":"2021-12-07T01:41:11.868736Z","iopub.status.idle":"2021-12-07T01:42:49.088146Z","shell.execute_reply.started":"2021-12-07T01:41:11.868712Z","shell.execute_reply":"2021-12-07T01:42:49.08743Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nimport phik\nfrom phik import resources, report\n\ndf_train_corr_set.sample(n=10000).phik_matrix()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:42:49.092478Z","iopub.execute_input":"2021-12-07T01:42:49.093006Z","iopub.status.idle":"2021-12-07T01:43:04.366991Z","shell.execute_reply.started":"2021-12-07T01:42:49.092973Z","shell.execute_reply":"2021-12-07T01:43:04.36618Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**logTotalRevenue has 0 correlation with device.browser, device.operatingSystem,geoNetwork.continent,geoNetwork.subContinent,geoNetwork.country,trafficSource.source**\ngeoNetwork.region has very low correlation and too many missing values","metadata":{}},{"cell_type":"markdown","source":"# Converting discrete into categorical variables. In order to  use one hot\nWhy use Light GBM: best in class performance, fast training time, I don't have to do one-hot\ntraining time: the time for building the model\nRunning time: the time to get the prediction\n\nAlternative: XGBoost, CatBoost (Try CatBoost if it doesn't require one-hot)\n\n","metadata":{}},{"cell_type":"code","source":"df_train.info()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:04.368436Z","iopub.execute_input":"2021-12-07T01:43:04.368891Z","iopub.status.idle":"2021-12-07T01:43:04.503919Z","shell.execute_reply.started":"2021-12-07T01:43:04.368851Z","shell.execute_reply":"2021-12-07T01:43:04.503127Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# trafficSource.source","metadata":{}},{"cell_type":"code","source":"\nsource = df_train['trafficSource.source']\n\nsource_value_counts = source.value_counts(dropna=False)\ndisplay(source_value_counts) \nsource_value_counts.reset_index(drop=True)[:20].plot()\nplt.title(\"Source\")#reset_index is to convert indexes to numbers\n\nTop_5_Sources = source_value_counts[:5].index\nprint(Top_5_Sources)\n\n#transaction cities to missing value None\nsource[~source.isin(Top_5_Sources)] = None\nsource\n#write in the report: in the ideal world, I would consider merging some of the values\n#write a function ","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:04.505097Z","iopub.execute_input":"2021-12-07T01:43:04.50535Z","iopub.status.idle":"2021-12-07T01:43:04.684575Z","shell.execute_reply.started":"2021-12-07T01:43:04.505322Z","shell.execute_reply":"2021-12-07T01:43:04.683667Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(df_train.nunique())","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:04.685894Z","iopub.execute_input":"2021-12-07T01:43:04.686228Z","iopub.status.idle":"2021-12-07T01:43:04.894096Z","shell.execute_reply.started":"2021-12-07T01:43:04.686185Z","shell.execute_reply":"2021-12-07T01:43:04.893499Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#The method proved to be working, defining a function to accelerate the process\ndef plot_column(name,x):\n    myList = df_train[name]\n    value_counts = myList.value_counts(dropna=False)\n    display(value_counts) \n    value_counts.reset_index(drop=True)[:x].plot()\n    plt.title(name)\n    Top_x_Sources = value_counts[:x].index\n    print(Top_x_Sources)\n\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:04.89545Z","iopub.execute_input":"2021-12-07T01:43:04.89579Z","iopub.status.idle":"2021-12-07T01:43:04.901711Z","shell.execute_reply.started":"2021-12-07T01:43:04.89575Z","shell.execute_reply":"2021-12-07T01:43:04.900802Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def convert_to_none_after_threshold(name,y):\n    mycolumn = df_train[name]\n    column_value_counts = mycolumn.value_counts(dropna=False)\n    Top_y_Categories = column_value_counts[:y].index\n    print(Top_y_Categories)\n    mycolumn[~mycolumn.isin(Top_y_Categories)] = None\n    mycolumn","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:04.90294Z","iopub.execute_input":"2021-12-07T01:43:04.903168Z","iopub.status.idle":"2021-12-07T01:43:04.913918Z","shell.execute_reply.started":"2021-12-07T01:43:04.903142Z","shell.execute_reply":"2021-12-07T01:43:04.913112Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#def convert_to_none_after_threshold(name,y):\n    #mycolumn = df_train[name]\n    #threshold=y\n    #column_value_counts = mycolumn.value_counts(dropna=False)\n    #low_volumn_categories = mycolumn.value_counts()<threshold\n    #print(low_volumn_categories)\n    #mycolumn[mycolumn.isin(low_volumn_categories)] = None\n    #mycolumn","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:04.915113Z","iopub.execute_input":"2021-12-07T01:43:04.915575Z","iopub.status.idle":"2021-12-07T01:43:04.923936Z","shell.execute_reply.started":"2021-12-07T01:43:04.915536Z","shell.execute_reply":"2021-12-07T01:43:04.923283Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **channelGrouping**","metadata":{}},{"cell_type":"code","source":"plot_column('channelGrouping',20)\n\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:04.925003Z","iopub.execute_input":"2021-12-07T01:43:04.925211Z","iopub.status.idle":"2021-12-07T01:43:05.098836Z","shell.execute_reply.started":"2021-12-07T01:43:04.925186Z","shell.execute_reply":"2021-12-07T01:43:05.098292Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **device.browser**","metadata":{}},{"cell_type":"markdown","source":"channelGrouping only has 8 unique values although the plot is showing it's droping very fast","metadata":{}},{"cell_type":"code","source":"plot_column('device.browser',20)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:05.099724Z","iopub.execute_input":"2021-12-07T01:43:05.100259Z","iopub.status.idle":"2021-12-07T01:43:05.25627Z","shell.execute_reply.started":"2021-12-07T01:43:05.100219Z","shell.execute_reply":"2021-12-07T01:43:05.255322Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"convert_to_none_after_threshold('device.browser',11)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:05.2576Z","iopub.execute_input":"2021-12-07T01:43:05.258549Z","iopub.status.idle":"2021-12-07T01:43:05.288706Z","shell.execute_reply.started":"2021-12-07T01:43:05.258505Z","shell.execute_reply":"2021-12-07T01:43:05.288154Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#convert_to_none_after_threshold('device.browser',11)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:05.290257Z","iopub.execute_input":"2021-12-07T01:43:05.290808Z","iopub.status.idle":"2021-12-07T01:43:05.29471Z","shell.execute_reply.started":"2021-12-07T01:43:05.290764Z","shell.execute_reply":"2021-12-07T01:43:05.293997Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_column('device.operatingSystem',20)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:05.295646Z","iopub.execute_input":"2021-12-07T01:43:05.295958Z","iopub.status.idle":"2021-12-07T01:43:05.521758Z","shell.execute_reply.started":"2021-12-07T01:43:05.295933Z","shell.execute_reply":"2021-12-07T01:43:05.521233Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"convert_to_none_after_threshold('device.operatingSystem',6)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:05.523015Z","iopub.execute_input":"2021-12-07T01:43:05.523416Z","iopub.status.idle":"2021-12-07T01:43:05.549679Z","shell.execute_reply.started":"2021-12-07T01:43:05.523378Z","shell.execute_reply":"2021-12-07T01:43:05.548817Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_column('device.browser',20)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:05.551172Z","iopub.execute_input":"2021-12-07T01:43:05.551647Z","iopub.status.idle":"2021-12-07T01:43:05.760299Z","shell.execute_reply.started":"2021-12-07T01:43:05.551607Z","shell.execute_reply":"2021-12-07T01:43:05.759327Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train['device.operatingSystem']","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:05.761965Z","iopub.execute_input":"2021-12-07T01:43:05.762477Z","iopub.status.idle":"2021-12-07T01:43:05.770804Z","shell.execute_reply.started":"2021-12-07T01:43:05.762434Z","shell.execute_reply":"2021-12-07T01:43:05.76987Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"convert_to_none_after_threshold('device.operatingSystem',8)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:05.77191Z","iopub.execute_input":"2021-12-07T01:43:05.772224Z","iopub.status.idle":"2021-12-07T01:43:05.802038Z","shell.execute_reply.started":"2021-12-07T01:43:05.772179Z","shell.execute_reply":"2021-12-07T01:43:05.801179Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(df_train.nunique())","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:05.803349Z","iopub.execute_input":"2021-12-07T01:43:05.803986Z","iopub.status.idle":"2021-12-07T01:43:06.036954Z","shell.execute_reply.started":"2021-12-07T01:43:05.803941Z","shell.execute_reply":"2021-12-07T01:43:06.036066Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"# **geoNetwork.region**","metadata":{}},{"cell_type":"code","source":"plot_column('geoNetwork.region',20)\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:06.038359Z","iopub.execute_input":"2021-12-07T01:43:06.038739Z","iopub.status.idle":"2021-12-07T01:43:06.263941Z","shell.execute_reply.started":"2021-12-07T01:43:06.038697Z","shell.execute_reply":"2021-12-07T01:43:06.263099Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"geoNetwork.region has too many missing values and the correlation is low (0.11). Will eventually drop the column before modelling","metadata":{}},{"cell_type":"markdown","source":"# **trafficSource.keyword**","metadata":{}},{"cell_type":"code","source":"plot_column('trafficSource.keyword',20)\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:06.265197Z","iopub.execute_input":"2021-12-07T01:43:06.265437Z","iopub.status.idle":"2021-12-07T01:43:06.479391Z","shell.execute_reply.started":"2021-12-07T01:43:06.265409Z","shell.execute_reply":"2021-12-07T01:43:06.47855Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"trafficSource.keyword has too many missing values. Will eventually drop the column before modelling","metadata":{}},{"cell_type":"markdown","source":"**geoNetwork.continent **","metadata":{}},{"cell_type":"code","source":"plot_column('geoNetwork.continent',20)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:06.480618Z","iopub.execute_input":"2021-12-07T01:43:06.48086Z","iopub.status.idle":"2021-12-07T01:43:06.703129Z","shell.execute_reply.started":"2021-12-07T01:43:06.480831Z","shell.execute_reply":"2021-12-07T01:43:06.702316Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#get top 5 continent and convert the rest to none\nconvert_to_none_after_threshold('geoNetwork.continent',5)\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:06.704698Z","iopub.execute_input":"2021-12-07T01:43:06.706416Z","iopub.status.idle":"2021-12-07T01:43:06.739594Z","shell.execute_reply.started":"2021-12-07T01:43:06.70637Z","shell.execute_reply":"2021-12-07T01:43:06.738675Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **geoNetwork.subContinent**","metadata":{}},{"cell_type":"code","source":"plot_column('geoNetwork.subContinent',20)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:06.741054Z","iopub.execute_input":"2021-12-07T01:43:06.74157Z","iopub.status.idle":"2021-12-07T01:43:06.956853Z","shell.execute_reply.started":"2021-12-07T01:43:06.741526Z","shell.execute_reply":"2021-12-07T01:43:06.955991Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#getting the top 10 subContiment and convert the rest to none\nconvert_to_none_after_threshold('geoNetwork.subContinent',8)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:06.9583Z","iopub.execute_input":"2021-12-07T01:43:06.958693Z","iopub.status.idle":"2021-12-07T01:43:06.993592Z","shell.execute_reply.started":"2021-12-07T01:43:06.958639Z","shell.execute_reply":"2021-12-07T01:43:06.992813Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **geoNetwork.metro**","metadata":{}},{"cell_type":"code","source":"plot_column('geoNetwork.metro',20)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:06.994814Z","iopub.execute_input":"2021-12-07T01:43:06.995083Z","iopub.status.idle":"2021-12-07T01:43:07.211793Z","shell.execute_reply.started":"2021-12-07T01:43:06.995052Z","shell.execute_reply":"2021-12-07T01:43:07.210969Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"geoNetwork.metro has large volumn of missing value and will eventually to be dropped before modelling","metadata":{}},{"cell_type":"markdown","source":"# **geoNetwork.city**","metadata":{}},{"cell_type":"code","source":"plot_column('geoNetwork.city',20)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:07.213187Z","iopub.execute_input":"2021-12-07T01:43:07.213494Z","iopub.status.idle":"2021-12-07T01:43:07.434723Z","shell.execute_reply.started":"2021-12-07T01:43:07.213455Z","shell.execute_reply":"2021-12-07T01:43:07.434013Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"geoNetwork.city has large volumn of missing value and will eventually be dropped before modelling","metadata":{}},{"cell_type":"code","source":"#dimension_list=[col for col in df_train if type(col)!= int or type(col)!=float]\n#print(dimension_list)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:07.435679Z","iopub.execute_input":"2021-12-07T01:43:07.435912Z","iopub.status.idle":"2021-12-07T01:43:07.439215Z","shell.execute_reply.started":"2021-12-07T01:43:07.435885Z","shell.execute_reply":"2021-12-07T01:43:07.438582Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#channelGrouping\n#plot_column('channelGrouping',20)\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:07.440175Z","iopub.execute_input":"2021-12-07T01:43:07.440699Z","iopub.status.idle":"2021-12-07T01:43:07.451699Z","shell.execute_reply.started":"2021-12-07T01:43:07.440662Z","shell.execute_reply":"2021-12-07T01:43:07.451044Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# # Exploratory Data Analysis","metadata":{}},{"cell_type":"markdown","source":"**Seasonality?**\n**total transaction vs. Month**","metadata":{}},{"cell_type":"code","source":"df_train['month'] = df_train['date'].apply(lambda x: (x//100)%100)\n#df_train['logTotalRevenue'] = df_train['totals.transactionRevenue'].apply(lambda x: np.log(1+x))\ndisplay(df_train)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:07.452694Z","iopub.execute_input":"2021-12-07T01:43:07.453235Z","iopub.status.idle":"2021-12-07T01:43:07.514843Z","shell.execute_reply.started":"2021-12-07T01:43:07.453203Z","shell.execute_reply":"2021-12-07T01:43:07.514301Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ax = df_train.plot.scatter('month','logTotalRevenue', figsize=(15, 5))\ndf_train[['month', 'logTotalRevenue']].groupby('month')['logTotalRevenue'].median().plot(ax=ax)\n#visually, it seems there is a trend\n#by plotting the mean and median, it doesn't seem there is a seasonability\n\n#hits vs. transaction: scatter plots and then compute correlation","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:07.515876Z","iopub.execute_input":"2021-12-07T01:43:07.516187Z","iopub.status.idle":"2021-12-07T01:43:08.085317Z","shell.execute_reply.started":"2021-12-07T01:43:07.516162Z","shell.execute_reply":"2021-12-07T01:43:08.084699Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**totals.transactionRevenue vs. Device**","metadata":{}},{"cell_type":"code","source":"#totals.transactionRevenue correlation matrics\ndef horizontal_bar_chart(cnt_srs, color):\n    trace = go.Bar(\n        y=cnt_srs.index[::-1],\n        x=cnt_srs.values[::-1],\n        showlegend=False,\n        orientation = 'h',\n        marker=dict(\n            color=color,\n        ),\n    )\n    return trace\n\n# Device Browser\ncnt_srs = df_train.groupby('device.browser')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs = cnt_srs.sort_values(by=\"count\", ascending=False)\ntrace1 = horizontal_bar_chart(cnt_srs[\"count\"].head(10), 'rgba(50, 171, 96, 0.6)')\ntrace2 = horizontal_bar_chart(cnt_srs[\"count of non-zero revenue\"].head(10), 'rgba(50, 171, 96, 0.6)')\ntrace3 = horizontal_bar_chart(cnt_srs[\"mean\"].head(10), 'rgba(50, 171, 96, 0.6)')\n\n# Device Category\ncnt_srs = df_train.groupby('device.deviceCategory')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs = cnt_srs.sort_values(by=\"count\", ascending=False)\ntrace4 = horizontal_bar_chart(cnt_srs[\"count\"].head(10), 'rgba(71, 58, 131, 0.8)')\ntrace5 = horizontal_bar_chart(cnt_srs[\"count of non-zero revenue\"].head(10), 'rgba(71, 58, 131, 0.8)')\ntrace6 = horizontal_bar_chart(cnt_srs[\"mean\"].head(10), 'rgba(71, 58, 131, 0.8)')\n\n# Operating system\ncnt_srs = df_train.groupby('device.operatingSystem')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs = cnt_srs.sort_values(by=\"count\", ascending=False)\ntrace7 = horizontal_bar_chart(cnt_srs[\"count\"].head(10), 'rgba(246, 78, 139, 0.6)')\ntrace8 = horizontal_bar_chart(cnt_srs[\"count of non-zero revenue\"].head(10),'rgba(246, 78, 139, 0.6)')\ntrace9 = horizontal_bar_chart(cnt_srs[\"mean\"].head(10),'rgba(246, 78, 139, 0.6)')\n\n# Creating two subplots\nfig = tools.make_subplots(rows=3, cols=3, vertical_spacing=0.04, \n                          subplot_titles=[\"Device Browser - Count\", \"Device Browser - Non-zero Revenue Count\", \"Device Browser - Mean Revenue\",\n                                          \"Device Category - Count\",  \"Device Category - Non-zero Revenue Count\", \"Device Category - Mean Revenue\", \n                                          \"Device OS - Count\", \"Device OS - Non-zero Revenue Count\", \"Device OS - Mean Revenue\"])\n\nfig.append_trace(trace1, 1, 1)\nfig.append_trace(trace2, 1, 2)\nfig.append_trace(trace3, 1, 3)\nfig.append_trace(trace4, 2, 1)\nfig.append_trace(trace5, 2, 2)\nfig.append_trace(trace6, 2, 3)\nfig.append_trace(trace7, 3, 1)\nfig.append_trace(trace8, 3, 2)\nfig.append_trace(trace9, 3, 3)\n\nfig['layout'].update(height=1200, width=1200, paper_bgcolor='rgb(233,233,233)', title=\"Device Plots\")\npy.iplot(fig, filename='device-plots')","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:08.086445Z","iopub.execute_input":"2021-12-07T01:43:08.086776Z","iopub.status.idle":"2021-12-07T01:43:09.16652Z","shell.execute_reply.started":"2021-12-07T01:43:08.08675Z","shell.execute_reply":"2021-12-07T01:43:09.165235Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"* Device distribution looks similar on both the count and the count of non-zero revenue plots. And the majority of the traffic comes from Chrome for both zero and non-zero revenue traffic\n* For device category, desktop is getting way more traffic for both zero and non-zero traffic than mobile and desktop. And the average revenue on desktop is also higher\n* For device category, desktop is getting way more traffic for both zero and non-zero traffic than mobile and desktop. And the average revenue on desktop is also higher\n* For device operating system,  windows has the highest number of counts while macintosh has the highest non-zero revenue count.\n\n\n","metadata":{}},{"cell_type":"markdown","source":"**totals.transactionRevenue vs. Geo**","metadata":{}},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"# Continent\ncnt_srs = df_train.groupby('geoNetwork.continent')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs = cnt_srs.sort_values(by=\"count\", ascending=False)\ntrace1 = horizontal_bar_chart(cnt_srs[\"count\"].head(10), 'rgba(58, 71, 80, 0.6)')\ntrace2 = horizontal_bar_chart(cnt_srs[\"count of non-zero revenue\"].head(10), 'rgba(58, 71, 80, 0.6)')\ntrace3 = horizontal_bar_chart(cnt_srs[\"mean\"].head(10), 'rgba(58, 71, 80, 0.6)')\n\n# Sub-continent\ncnt_srs = df_train.groupby('geoNetwork.subContinent')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs = cnt_srs.sort_values(by=\"count\", ascending=False)\ntrace4 = horizontal_bar_chart(cnt_srs[\"count\"], 'orange')\ntrace5 = horizontal_bar_chart(cnt_srs[\"count of non-zero revenue\"], 'orange')\ntrace6 = horizontal_bar_chart(cnt_srs[\"mean\"], 'orange')\n\n# Country\ncnt_srs = df_train.groupby('geoNetwork.country')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs = cnt_srs.sort_values(by=\"count\", ascending=False)\ntrace7 = horizontal_bar_chart(cnt_srs[\"count\"].head(10), 'blue')\ntrace8 = horizontal_bar_chart(cnt_srs[\"count of non-zero revenue\"].head(10), 'blue')\ntrace9 = horizontal_bar_chart(cnt_srs[\"mean\"].head(10), 'blue')\n\n# Creating two subplots\nfig = tools.make_subplots(rows=3, cols=3, vertical_spacing=0.08, horizontal_spacing=0.15, \n                          subplot_titles=[\"Continent - Count\", \"Continent - Non-zero Revenue Count\", \"Continent - Mean Revenue\",\n                                          \"Sub Continent - Count\",  \"Sub Continent - Non-zero Revenue Count\", \"Sub Continent - Mean Revenue\",\n                                          \"Country - Count\", \"Country - Non-zero Revenue Count\", \"Country - Mean Revenue\"])\n\nfig.append_trace(trace1, 1, 1)\nfig.append_trace(trace2, 1, 2)\nfig.append_trace(trace3, 1, 3)\nfig.append_trace(trace4, 2, 1)\nfig.append_trace(trace5, 2, 2)\nfig.append_trace(trace6, 2, 3)\nfig.append_trace(trace7, 3, 1)\nfig.append_trace(trace8, 3, 2)\nfig.append_trace(trace9, 3, 3)\n\nfig['layout'].update(height=1500, width=1200, paper_bgcolor='rgb(233,233,233)', title=\"Geography Plots\")\npy.iplot(fig, filename='geo-plots')","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:09.168753Z","iopub.execute_input":"2021-12-07T01:43:09.169322Z","iopub.status.idle":"2021-12-07T01:43:09.364052Z","shell.execute_reply.started":"2021-12-07T01:43:09.169278Z","shell.execute_reply":"2021-12-07T01:43:09.363109Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Channelgrouping\ncnt_srs = df_train.groupby('channelGrouping')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs = cnt_srs.sort_values(by=\"count\", ascending=False)\ntrace1 = horizontal_bar_chart(cnt_srs[\"count\"].head(10), 'rgba(58, 71, 80, 0.6)')\ntrace2 = horizontal_bar_chart(cnt_srs[\"count of non-zero revenue\"].head(10), 'rgba(58, 71, 80, 0.6)')\ntrace3 = horizontal_bar_chart(cnt_srs[\"mean\"].head(10), 'rgba(58, 71, 80, 0.6)')\n\n# Medium\ncnt_srs = df_train.groupby('trafficSource.medium')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs = cnt_srs.sort_values(by=\"count\", ascending=False)\ntrace4 = horizontal_bar_chart(cnt_srs[\"count\"], 'orange')\ntrace5 = horizontal_bar_chart(cnt_srs[\"count of non-zero revenue\"], 'orange')\ntrace6 = horizontal_bar_chart(cnt_srs[\"mean\"], 'orange')\n\n# Source\ncnt_srs = df_train.groupby('trafficSource.source')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs = cnt_srs.sort_values(by=\"count\", ascending=False)\ntrace7 = horizontal_bar_chart(cnt_srs[\"count\"].head(10), 'blue')\ntrace8 = horizontal_bar_chart(cnt_srs[\"count of non-zero revenue\"].head(10), 'blue')\ntrace9 = horizontal_bar_chart(cnt_srs[\"mean\"].head(10), 'blue')\n\n# Creating two subplots\nfig = tools.make_subplots(rows=3, cols=3, vertical_spacing=0.08, horizontal_spacing=0.15, \n                          subplot_titles=[\"channelGrouping - Count\", \"channelGrouping - Non-zero Revenue Count\", \"channelGrouping - Mean Revenue\",\n                                          \"Medium - Count\",  \"Medium  - Non-zero Revenue Count\", \"Medium  - Mean Revenue\",\n                                          \"Source - Count\", \"Source - Non-zero Revenue Count\", \"Source - Mean Revenue\"])\n\nfig.append_trace(trace1, 1, 1)\nfig.append_trace(trace2, 1, 2)\nfig.append_trace(trace3, 1, 3)\nfig.append_trace(trace4, 2, 1)\nfig.append_trace(trace5, 2, 2)\nfig.append_trace(trace6, 2, 3)\nfig.append_trace(trace7, 3, 1)\nfig.append_trace(trace8, 3, 2)\nfig.append_trace(trace9, 3, 3)\n\nfig['layout'].update(height=1500, width=1200, paper_bgcolor='rgb(233,233,233)', title=\"Channel Plots\")\npy.iplot(fig, filename='channel-plots')","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:09.365389Z","iopub.execute_input":"2021-12-07T01:43:09.365731Z","iopub.status.idle":"2021-12-07T01:43:09.550906Z","shell.execute_reply.started":"2021-12-07T01:43:09.3657Z","shell.execute_reply":"2021-12-07T01:43:09.55005Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Organic has the highest number of count, while referral has the highest number of non-zero revenue count.Indicating referall is the most efficient chanel in driving revenue for G Store\n","metadata":{}},{"cell_type":"markdown","source":"* The majority of the zero and non-zero revenue traffic is coming from North America\n* Within North America, the majority of the zero and non-zero revenue traffic is coming from America","metadata":{}},{"cell_type":"markdown","source":"# **Relationship logTotalRevenue vs. totals.hits**","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(10, 6))\nax.scatter(x = df_train['totals.hits'], y = df_train['logTotalRevenue'])\nplt.xlabel(\"Total Hits\")\nplt.ylabel(\"LogTotalRevenue\")\n\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:09.552172Z","iopub.execute_input":"2021-12-07T01:43:09.552466Z","iopub.status.idle":"2021-12-07T01:43:10.933974Z","shell.execute_reply.started":"2021-12-07T01:43:09.552436Z","shell.execute_reply":"2021-12-07T01:43:10.9334Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train[['totals.hits', 'totals.pageviews']] = df_train[['totals.hits', 'totals.pageviews']].apply(pd.to_numeric)\ndf_train.info()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:10.934901Z","iopub.execute_input":"2021-12-07T01:43:10.935642Z","iopub.status.idle":"2021-12-07T01:43:11.169759Z","shell.execute_reply.started":"2021-12-07T01:43:10.935607Z","shell.execute_reply":"2021-12-07T01:43:11.168877Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"column_1=df_train['logTotalRevenue']\ncolumn_2=df_train['totals.hits']\ncorrelation = column_1.corr(column_2)\nprint(correlation)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:11.17114Z","iopub.execute_input":"2021-12-07T01:43:11.172435Z","iopub.status.idle":"2021-12-07T01:43:11.179583Z","shell.execute_reply.started":"2021-12-07T01:43:11.17239Z","shell.execute_reply":"2021-12-07T01:43:11.178683Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(10, 6))\nax.scatter(x = df_train['totals.pageviews'], y = df_train['logTotalRevenue'])\nplt.xlabel(\"Total Pageviews\")\nplt.ylabel(\"LogTotalRevenue\")\n\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:11.180947Z","iopub.execute_input":"2021-12-07T01:43:11.181359Z","iopub.status.idle":"2021-12-07T01:43:11.498952Z","shell.execute_reply.started":"2021-12-07T01:43:11.181327Z","shell.execute_reply":"2021-12-07T01:43:11.498202Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"column_1=df_train['logTotalRevenue']\ncolumn_2=df_train['totals.pageviews']\ncorrelation = column_1.corr(column_2)\nprint(correlation)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:11.500578Z","iopub.execute_input":"2021-12-07T01:43:11.501104Z","iopub.status.idle":"2021-12-07T01:43:11.508723Z","shell.execute_reply.started":"2021-12-07T01:43:11.501061Z","shell.execute_reply":"2021-12-07T01:43:11.507829Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# # **Modeling**","metadata":{}},{"cell_type":"code","source":"#dropping columns that have too many missing values and 0 correlation with logTotalRevenue\ndf_train = df_train.drop(columns = ['geoNetwork.region','geoNetwork.metro','geoNetwork.city','trafficSource.keyword'])","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:11.509892Z","iopub.execute_input":"2021-12-07T01:43:11.510395Z","iopub.status.idle":"2021-12-07T01:43:11.539851Z","shell.execute_reply.started":"2021-12-07T01:43:11.510349Z","shell.execute_reply":"2021-12-07T01:43:11.53908Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = df_train.drop(columns = ['month'])","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:11.541011Z","iopub.execute_input":"2021-12-07T01:43:11.541228Z","iopub.status.idle":"2021-12-07T01:43:11.573324Z","shell.execute_reply.started":"2021-12-07T01:43:11.541203Z","shell.execute_reply":"2021-12-07T01:43:11.572711Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"df_train.describe(include='all')","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:11.574271Z","iopub.execute_input":"2021-12-07T01:43:11.574825Z","iopub.status.idle":"2021-12-07T01:43:11.83321Z","shell.execute_reply.started":"2021-12-07T01:43:11.574796Z","shell.execute_reply":"2021-12-07T01:43:11.832614Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Create a Light GBM Model and evaluate performance","metadata":{}},{"cell_type":"markdown","source":"Separate the data into independent and dependent variables","metadata":{}},{"cell_type":"code","source":"#transform data in object format or string format to categorical type, as lgb won't accept object or string format\n\nobject_features = list(df_train.loc[:,df_train.dtypes == 'object'].columns.values)\nobject_features","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:11.834216Z","iopub.execute_input":"2021-12-07T01:43:11.83455Z","iopub.status.idle":"2021-12-07T01:43:11.865562Z","shell.execute_reply.started":"2021-12-07T01:43:11.834524Z","shell.execute_reply":"2021-12-07T01:43:11.86493Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for feature in object_features:\n    df_train[feature] = pd.Series(df_train[feature], dtype=\"category\")","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:11.866616Z","iopub.execute_input":"2021-12-07T01:43:11.866962Z","iopub.status.idle":"2021-12-07T01:43:12.23062Z","shell.execute_reply.started":"2021-12-07T01:43:11.866935Z","shell.execute_reply":"2021-12-07T01:43:12.229902Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"var_columns = [col for col in df_train.columns if col not in ['fullVisitorId','logTotalRevenue','totals.transactionRevenue']]\nx = df_train.loc[:,var_columns] #independent variables\ny = df_train.loc[:,'logTotalRevenue'] #dependent variable\n\nx_train, x_valid, y_train, y_valid = train_test_split(x,y,test_size=0.2)\n\nx_train.shape, x_valid.shape, y_train.shape, y_valid.shape","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:12.231723Z","iopub.execute_input":"2021-12-07T01:43:12.232067Z","iopub.status.idle":"2021-12-07T01:43:12.250086Z","shell.execute_reply.started":"2021-12-07T01:43:12.23204Z","shell.execute_reply":"2021-12-07T01:43:12.249089Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_data = lgb.Dataset(x_train,label=y_train,#categorical_feature=['channelGrouping', 'device.browser', 'device.operatingSystem', 'device.deviceCategory', 'geoNetwork.continent', 'geoNetwork.subContinent', 'geoNetwork.country', 'trafficSource.campaign', 'trafficSource.source', 'trafficSource.medium']\n)\nvalid_data = lgb.Dataset(x_valid, label=y_valid,#categorical_feature=['channelGrouping', 'device.browser', 'device.operatingSystem', 'device.deviceCategory', 'geoNetwork.continent', 'geoNetwork.subContinent', 'geoNetwork.country', 'trafficSource.campaign', 'trafficSource.source', 'trafficSource.medium']\n)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:12.251365Z","iopub.execute_input":"2021-12-07T01:43:12.251566Z","iopub.status.idle":"2021-12-07T01:43:12.255854Z","shell.execute_reply.started":"2021-12-07T01:43:12.251542Z","shell.execute_reply":"2021-12-07T01:43:12.25504Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"Specify the parameters for LightGBM","metadata":{}},{"cell_type":"code","source":"parameters = {'objective': 'regression',#change\n              'metric': 'auc', #\n              'is_unbalance': 'true',\n              'boosting': 'gbdt',\n              'num_leaves': 63, #how many leaves will be there \n              'feature_fraction': 0.5,\n              'bagging_fraction': 0.5,\n              'bagging_freq': 20,\n              'learning_rate': 0.01,\n              'verbose': -1\n            }","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:12.256804Z","iopub.execute_input":"2021-12-07T01:43:12.257409Z","iopub.status.idle":"2021-12-07T01:43:12.266278Z","shell.execute_reply.started":"2021-12-07T01:43:12.257373Z","shell.execute_reply":"2021-12-07T01:43:12.26568Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nmodel_lgbm = lgb.train(parameters,\n                      train_data,\n                     valid_sets=valid_data,\n                      num_boost_round=500\n                      \n                     )\n\n\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:12.267291Z","iopub.execute_input":"2021-12-07T01:43:12.268049Z","iopub.status.idle":"2021-12-07T01:43:15.499187Z","shell.execute_reply.started":"2021-12-07T01:43:12.268017Z","shell.execute_reply":"2021-12-07T01:43:15.49842Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"y_train_pred = model_lgbm.predict(x_train)\ny_valid_pred = model_lgbm.predict(x_valid)\n\nprint(\"AUC Train: {:.4f}\\nAUC Valid: {:.4f}\".format(roc_auc_score(y_train, y_train_pred),\n                                                    roc_auc_score(y_valid, y_valid_pred)))","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:15.50062Z","iopub.execute_input":"2021-12-07T01:43:15.501073Z","iopub.status.idle":"2021-12-07T01:43:17.043298Z","shell.execute_reply.started":"2021-12-07T01:43:15.501027Z","shell.execute_reply":"2021-12-07T01:43:17.041945Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(y_valid)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:46:17.374697Z","iopub.execute_input":"2021-12-07T01:46:17.375558Z","iopub.status.idle":"2021-12-07T01:46:17.38233Z","shell.execute_reply.started":"2021-12-07T01:46:17.375517Z","shell.execute_reply":"2021-12-07T01:46:17.381259Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(y_valid_pred)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:46:49.130743Z","iopub.execute_input":"2021-12-07T01:46:49.132279Z","iopub.status.idle":"2021-12-07T01:46:49.140072Z","shell.execute_reply.started":"2021-12-07T01:46:49.13218Z","shell.execute_reply":"2021-12-07T01:46:49.139448Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test.head()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:17.046278Z","iopub.status.idle":"2021-12-07T01:43:17.046559Z","shell.execute_reply.started":"2021-12-07T01:43:17.046416Z","shell.execute_reply":"2021-12-07T01:43:17.04643Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#var_columns = [col for col in df_test.columns if col not in ['fullVisitorId','logTotalRevenue','totals.transactionRevenue']]\n#x = df_train.loc[:,var_columns] #independent variables\n#y = df_train.loc[:,'logTotalRevenue'] #dependent variable","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:17.047544Z","iopub.status.idle":"2021-12-07T01:43:17.047841Z","shell.execute_reply.started":"2021-12-07T01:43:17.047691Z","shell.execute_reply":"2021-12-07T01:43:17.047707Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#submission_df=pf.DataFrame()","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:17.04865Z","iopub.status.idle":"2021-12-07T01:43:17.04893Z","shell.execute_reply.started":"2021-12-07T01:43:17.048789Z","shell.execute_reply":"2021-12-07T01:43:17.048803Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cm_lgbm = confusion_matrix(y_valid, y_valid_pred)\nsns.heatmap(cm_lgbm,annot=True)","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:48:06.198831Z","iopub.execute_input":"2021-12-07T01:48:06.199352Z","iopub.status.idle":"2021-12-07T01:48:06.223358Z","shell.execute_reply.started":"2021-12-07T01:48:06.199316Z","shell.execute_reply":"2021-12-07T01:48:06.22254Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#df_sample_submission = pd.read_csv('www.kaggle.com/anniehou/ga-sampe-submission')\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:17.051768Z","iopub.status.idle":"2021-12-07T01:43:17.052456Z","shell.execute_reply.started":"2021-12-07T01:43:17.052286Z","shell.execute_reply":"2021-12-07T01:43:17.052305Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#x_test = df_test.loc[:,var_columns]\n#df_sample_submission['target'] = model_lgbm.predict(x_test)\n#df_sample_submission","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:17.053342Z","iopub.status.idle":"2021-12-07T01:43:17.053913Z","shell.execute_reply.started":"2021-12-07T01:43:17.053736Z","shell.execute_reply":"2021-12-07T01:43:17.053754Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Create a XGBoost Model and Evaluate Performance\n","metadata":{}},{"cell_type":"code","source":"import xgboost as xgb","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:17.054825Z","iopub.status.idle":"2021-12-07T01:43:17.055358Z","shell.execute_reply.started":"2021-12-07T01:43:17.055155Z","shell.execute_reply":"2021-12-07T01:43:17.055173Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nmodel_xgboost = lgb.train(parameters,\n                      train_data,\n                     valid_sets=valid_data,\n                      num_boost_round=500\n                      \n                     )\n","metadata":{"execution":{"iopub.status.busy":"2021-12-07T01:43:17.056321Z","iopub.status.idle":"2021-12-07T01:43:17.056781Z","shell.execute_reply.started":"2021-12-07T01:43:17.05662Z","shell.execute_reply":"2021-12-07T01:43:17.056636Z"},"trusted":true},"execution_count":null,"outputs":[]}]}