{"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":"\n
\n

GA Customer Revenue Prediction: Data EDA (Exploratory Data Analysis)

","metadata":{}},{"cell_type":"markdown","source":"## Each row in the dataset is one visit to the store. We are predicting the natural log of the sum of all transactions per user.\n\nThe data fields in the given files are\n\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, you 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#### Also it is important to note that some of the fields are in json format.\n","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 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":"2023-02-07T03:50:53.363295Z","iopub.execute_input":"2023-02-07T03:50:53.363579Z","iopub.status.idle":"2023-02-07T03:50:53.373657Z","shell.execute_reply.started":"2023-02-07T03:50:53.363551Z","shell.execute_reply":"2023-02-07T03:50:53.37241Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Introduction\n- Analysis feature on this competitions\n- Analysis all feature that used and do data visualize\n- Hep to find the key features on this competitions","metadata":{}},{"cell_type":"code","source":"import os\nprint(os.listdir(\"../input\"))\nimport numpy as np \nimport pandas as pd \nimport json\nimport bq_helper\nfrom pandas.io.json import json_normalize\nimport warnings\n\nimport gc\ngc.enable()\nwarnings.filterwarnings(\"ignore\")","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:50:53.37574Z","iopub.execute_input":"2023-02-07T03:50:53.376007Z","iopub.status.idle":"2023-02-07T03:50:53.390663Z","shell.execute_reply.started":"2023-02-07T03:50:53.375976Z","shell.execute_reply":"2023-02-07T03:50:53.389536Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#import module of matplot\nimport matplotlib.pyplot as plt\nimport seaborn as sns \nfrom plotly.offline import init_notebook_mode, iplot\nimport plotly.graph_objs as go\nfrom plotly import tools\nimport matplotlib as mpl\nimport matplotlib.pyplot as plt\nimport scipy.stats as st\n%matplotlib inline","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:50:53.391682Z","iopub.execute_input":"2023-02-07T03:50:53.391965Z","iopub.status.idle":"2023-02-07T03:50:53.404869Z","shell.execute_reply.started":"2023-02-07T03:50:53.391933Z","shell.execute_reply":"2023-02-07T03:50:53.403681Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_submission = pd.read_csv('/kaggle/input/ga-customer-revenue-prediction/sample_submission.csv')\n# train_data = pd.read_csv('/kaggle/input/ga-customer-revenue-prediction/train_v2.csv')\n# test_data = pd.read_csv('/kaggle/input/ga-customer-revenue-prediction/test_v2.csv')\n\n\njson_columns = ['device', 'geoNetwork','totals', 'trafficSource']\n\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\n","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:50:53.407328Z","iopub.execute_input":"2023-02-07T03:50:53.407608Z","iopub.status.idle":"2023-02-07T03:50:54.227284Z","shell.execute_reply.started":"2023-02-07T03:50:53.407578Z","shell.execute_reply":"2023-02-07T03:50:54.226387Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_submission.head()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:50:54.230483Z","iopub.execute_input":"2023-02-07T03:50:54.230816Z","iopub.status.idle":"2023-02-07T03:50:54.253071Z","shell.execute_reply.started":"2023-02-07T03:50:54.230775Z","shell.execute_reply":"2023-02-07T03:50:54.252139Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = load_dataframe(\"ga-customer-revenue-prediction/train.csv\")\nprint(train.shape)\ntrain.head()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:50:54.254339Z","iopub.execute_input":"2023-02-07T03:50:54.254679Z","iopub.status.idle":"2023-02-07T03:52:38.334754Z","shell.execute_reply.started":"2023-02-07T03:50:54.25458Z","shell.execute_reply":"2023-02-07T03:52:38.33348Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test = load_dataframe(\"ga-customer-revenue-prediction/test.csv\")\nprint(test.shape)\ntest.head()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:52:38.338342Z","iopub.execute_input":"2023-02-07T03:52:38.338651Z","iopub.status.idle":"2023-02-07T03:54:13.267441Z","shell.execute_reply.started":"2023-02-07T03:52:38.338614Z","shell.execute_reply":"2023-02-07T03:54:13.266475Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# check diff columns b/w train and test dataset\n\nnot_in_test_feature = [i for i in train.columns if i not in test.columns]\nprint (not_in_test_feature)","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:54:13.268704Z","iopub.execute_input":"2023-02-07T03:54:13.26898Z","iopub.status.idle":"2023-02-07T03:54:13.274914Z","shell.execute_reply.started":"2023-02-07T03:54:13.268946Z","shell.execute_reply":"2023-02-07T03:54:13.274036Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# drop additional columns which test data set havn't had it \ntrain = train.drop(['trafficSource_campaignCode'], axis = 1)\ntrain.shape, test.shape","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:54:13.277476Z","iopub.execute_input":"2023-02-07T03:54:13.277868Z","iopub.status.idle":"2023-02-07T03:54:17.655185Z","shell.execute_reply.started":"2023-02-07T03:54:13.277838Z","shell.execute_reply":"2023-02-07T03:54:17.654108Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_test_data = pd.concat([train, test],axis=0,ignore_index=True)\ntrain_test_data.describe()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:54:17.65651Z","iopub.execute_input":"2023-02-07T03:54:17.656789Z","iopub.status.idle":"2023-02-07T03:54:29.139454Z","shell.execute_reply.started":"2023-02-07T03:54:17.656757Z","shell.execute_reply":"2023-02-07T03:54:29.138453Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.isnull().sum()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:54:29.140647Z","iopub.execute_input":"2023-02-07T03:54:29.140876Z","iopub.status.idle":"2023-02-07T03:54:33.262126Z","shell.execute_reply.started":"2023-02-07T03:54:29.140848Z","shell.execute_reply":"2023-02-07T03:54:33.261243Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Data explosure","metadata":{}},{"cell_type":"code","source":"# Missing ratio of each feature\nmissing_ratio = train_test_data.isnull().sum() / len(train_test_data)\nmissing_ratio = missing_ratio[missing_ratio.values>0]\nmissing_ratio_low = missing_ratio[missing_ratio.values<=0.1]\nmissing_ratio_high = missing_ratio[missing_ratio.values>0.1]\n\nfig = plt.figure(figsize=(10,4),dpi=100)\nplt.subplot(1,2,1)\nsns.barplot(x=missing_ratio_low.index,y=missing_ratio_low.values)\nplt.xticks(rotation=90)\nplt.subplot(1,2,2)\nsns.barplot(x=missing_ratio_high.index,y=missing_ratio_high.values, color='blue')\nplt.xticks(rotation=90);\n\n","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:54:33.263288Z","iopub.execute_input":"2023-02-07T03:54:33.263505Z","iopub.status.idle":"2023-02-07T03:54:42.112035Z","shell.execute_reply.started":"2023-02-07T03:54:33.263481Z","shell.execute_reply":"2023-02-07T03:54:42.111178Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#analysis count of each features for train\nunique_count_train = []\nfor i in train.columns:\n unique_count_train.append(len(train[str(i)].value_counts()))\nunique_datafram_train = {\n \"Feature\":train.columns,\n \"unique_count\":unique_count_train\n}\nunique_train = pd.DataFrame(unique_datafram_train)\nunique_train\n#=========================or you can use unique to do this =================================#\n#train.nunique()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:54:42.113386Z","iopub.execute_input":"2023-02-07T03:54:42.11364Z","iopub.status.idle":"2023-02-07T03:54:50.067113Z","shell.execute_reply.started":"2023-02-07T03:54:42.113599Z","shell.execute_reply":"2023-02-07T03:54:50.066202Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#analysis count of each features for test\nunique_count_test = []\nfor i in test.columns:\n unique_count_test.append(len(test[str(i)].value_counts()))\nunique_datafram_test = {\n \"Feature\":test.columns,\n \"unique_count\":unique_count_test\n}\nunique_test = pd.DataFrame(unique_datafram_test)\nunique_test\n#=========================or you can use unique to do this =================================#\n#train.nunique()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:54:50.068223Z","iopub.execute_input":"2023-02-07T03:54:50.068475Z","iopub.status.idle":"2023-02-07T03:54:57.46602Z","shell.execute_reply.started":"2023-02-07T03:54:50.068446Z","shell.execute_reply":"2023-02-07T03:54:57.465001Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# check unique value in train and test data\ntrain_test_data.nunique()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:54:57.467247Z","iopub.execute_input":"2023-02-07T03:54:57.4675Z","iopub.status.idle":"2023-02-07T03:55:15.933118Z","shell.execute_reply.started":"2023-02-07T03:54:57.467473Z","shell.execute_reply":"2023-02-07T03:55:15.932152Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# drop unique values in train data\nconst_columns_train = unique_train.loc[unique_train['unique_count'] == 1,\"Feature\"]\nconst_columns_train = list(const_columns_train)\n#=========================or you can use unique to do this =================================#\n#const_columns_train = [col for col in train.columns if train[col].nunique() == 1]\nprint (\"Shape before remove constant feature on train set = \" + str(train.shape))\ntrain = train.drop(const_columns_train,axis = 1)\nprint (\"Shape after remove constant feature on train set = \" + str(train.shape))","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:15.934369Z","iopub.execute_input":"2023-02-07T03:55:15.934597Z","iopub.status.idle":"2023-02-07T03:55:16.889351Z","shell.execute_reply.started":"2023-02-07T03:55:15.934571Z","shell.execute_reply":"2023-02-07T03:55:16.888586Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# drop unique values in test data\nconst_columns_test = unique_test.loc[unique_test['unique_count'] == 1,\"Feature\"]\nconst_columns_test = list(const_columns_test)\n#=========================or you can use unique to do this =================================#\n#const_columns_test = [col for col in test.columns if test[col].nunique() == 1]\nprint (\"Shape before remove constant feature on train set = \" + str(test.shape))\ntest = test.drop(const_columns_test,axis = 1)\nprint (\"Shape after remove constant feature on train set = \" + str(test.shape))","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:16.890454Z","iopub.execute_input":"2023-02-07T03:55:16.891281Z","iopub.status.idle":"2023-02-07T03:55:20.463008Z","shell.execute_reply.started":"2023-02-07T03:55:16.891242Z","shell.execute_reply":"2023-02-07T03:55:20.461907Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# train['totals_transactionRevenue']\n\n# check totals_transactionRevenue distribution by scatter plot\n\ntrain[\"totals_transactionRevenue\"] = train[\"totals_transactionRevenue\"].astype('float')\ngdf = train.groupby(\"fullVisitorId\")[\"totals_transactionRevenue\"].sum().reset_index()\n\nplt.figure(figsize=(8,6))\nplt.scatter(range(gdf.shape[0]), np.sort(np.log1p(gdf[\"totals_transactionRevenue\"].values)))\nplt.xlabel('index', fontsize=12)\nplt.ylabel('TransactionRevenue', fontsize=12)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:20.464098Z","iopub.execute_input":"2023-02-07T03:55:20.464339Z","iopub.status.idle":"2023-02-07T03:55:24.608946Z","shell.execute_reply.started":"2023-02-07T03:55:20.464312Z","shell.execute_reply":"2023-02-07T03:55:24.608029Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# check totals_transactionRevenue distribution by historgram\nplt.rcParams['figure.figsize'] = (24, 6.0)\nprices = pd.DataFrame({\"price\":gdf[\"totals_transactionRevenue\"], \"log(price + 1)\":np.log1p(gdf[\"totals_transactionRevenue\"])})\nprices.hist()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:24.610378Z","iopub.execute_input":"2023-02-07T03:55:24.611275Z","iopub.status.idle":"2023-02-07T03:55:25.172096Z","shell.execute_reply.started":"2023-02-07T03:55:24.611226Z","shell.execute_reply":"2023-02-07T03:55:25.171018Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* The 80/20 rule has proven true for many businesses–only a small percentage of customers produce most of the revenue. As such, marketing teams are challenged to make appropriate investments in promotional strategies.","metadata":{}},{"cell_type":"code","source":"# actual ratio \nari = pd.notnull(train[\"totals_transactionRevenue\"]).sum()\nar = (gdf[\"totals_transactionRevenue\"]>0).sum()\nprint(\"Number of instances in train set with non-zero revenue : \", ari, \" and ratio is : \", ari / train.shape[0])\nprint(\"Number of unique customers with non-zero revenue : \", ar, \"and the ratio is : \", ar / gdf.shape[0])","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:25.173266Z","iopub.execute_input":"2023-02-07T03:55:25.173926Z","iopub.status.idle":"2023-02-07T03:55:25.186977Z","shell.execute_reply.started":"2023-02-07T03:55:25.173887Z","shell.execute_reply":"2023-02-07T03:55:25.186107Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Number of visitors and common visitors:\n\nNow let us look at the number of unique visitors in the train and test set and also the number of common visitors.","metadata":{}},{"cell_type":"code","source":"print(\"Number of unique visitors in train set : \",train.fullVisitorId.nunique(), \" out of rows : \",train.shape[0])\nprint(\"Number of unique visitors in test set : \",test.fullVisitorId.nunique(), \" out of rows : \",test.shape[0])\nprint(\"Number of common visitors in train and test set : \",len(set(train.fullVisitorId.unique()).intersection(set(test.fullVisitorId.unique())) ))","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:25.188206Z","iopub.execute_input":"2023-02-07T03:55:25.188487Z","iopub.status.idle":"2023-02-07T03:55:26.525081Z","shell.execute_reply.started":"2023-02-07T03:55:25.188458Z","shell.execute_reply":"2023-02-07T03:55:26.524014Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Data engineering\n\n- Analysis Date feature\n- Extracting new feature from date\n- Analysis new feature","metadata":{}},{"cell_type":"code","source":"def extract_new_feature(df): \n print(\"Start extract date...\")\n df['date'] = pd.to_datetime(df['visitStartTime'], unit='s')\n df['day_of_week'] = df['date'].dt.dayofweek\n df['hour'] = df['date'].dt.hour\n df['day'] = df['date'].dt.day\n df['month'] = df['date'].dt.month\n print(\"Finished extract date...\")\nextract_new_feature(train)\nextract_new_feature(test)","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:26.526609Z","iopub.execute_input":"2023-02-07T03:55:26.526946Z","iopub.status.idle":"2023-02-07T03:55:27.242221Z","shell.execute_reply.started":"2023-02-07T03:55:26.526903Z","shell.execute_reply":"2023-02-07T03:55:27.241036Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## data vis\nt1 = train['day_of_week'].value_counts()\nt2 = train['hour'].value_counts()\nt3 = train['day'].value_counts()\nt4 = train['month'].value_counts()\n\nplt.subplots(figsize=(15,10))\nplt.subplot(2,2,1)\nplt.title('Visits by day of week',color='b',fontsize=12)\nplt.bar(t1.index,t1.values,color='orange')\nplt.xticks(t1.index,rotation='horizontal',fontsize=12)\nplt.yticks(fontsize=12)\n\nplt.subplot(2,2,2)\nplt.title('Visits by hour',color='b',fontsize=12)\nplt.bar(t2.index,t2.values,color='pink')\nplt.xticks(t2.index,rotation='horizontal',fontsize=10)\nplt.yticks(fontsize=12)\n\nplt.subplot(2,2,3)\nplt.title('Visits by day',color='b',fontsize=12)\nplt.bar(t3.index,t3.values,color='blue')\nplt.xticks(t3.index,rotation='horizontal',fontsize=10)\nplt.yticks(fontsize=12)\n\nplt.subplot(2,2,4)\nplt.title('Visits by month',color='b',fontsize=12)\nplt.bar(t4.index,t4.values,color='purple')\nplt.xticks(t4.index,rotation='horizontal',fontsize=12)\nplt.yticks(fontsize=12)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2023-02-07T03:55:27.246624Z","iopub.execute_input":"2023-02-07T03:55:27.246887Z","iopub.status.idle":"2023-02-07T03:55:28.440213Z","shell.execute_reply.started":"2023-02-07T03:55:27.24686Z","shell.execute_reply":"2023-02-07T03:55:28.439112Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Plot total trascations revenue of extracted date feature","metadata":{}},{"cell_type":"code","source":"train['totals_transactionRevenue'] = train['totals_transactionRevenue'].astype('float64')\na = train[['day_of_week','totals_transactionRevenue']].groupby('day_of_week').mean().reset_index()\na1 = train[['hour','totals_transactionRevenue']].groupby('hour').mean().reset_index()\na2 = train[['day','totals_transactionRevenue']].groupby('day').mean().reset_index()\na3 = train[['month','totals_transactionRevenue']].groupby('month').mean().reset_index()\n\nplt.subplots(figsize=(15,10))\nplt.subplot(2,2,1)\nplt.title('Total transactionRevenue mean by day of week',color='b',fontsize=12)\nplt.bar(a['day_of_week'],a['totals_transactionRevenue'],color='orange')\nplt.xticks(a['day_of_week'],rotation='horizontal',fontsize=12)\nplt.yticks(fontsize=12)\n\nplt.subplot(2,2,2)\nplt.title('Total transactionRevenue mean by hour',color='b',fontsize=12)\nplt.bar(a1['hour'],a1['totals_transactionRevenue'],color='pink')\nplt.xticks(a1['hour'],rotation='horizontal',fontsize=10)\nplt.yticks(fontsize=12)\n\nplt.subplot(2,2,3)\nplt.title('Total transactionRevenue mean by day',color='b',fontsize=12)\nplt.bar(a2['day'],a2['totals_transactionRevenue'],color='blue')\nplt.xticks(a2['day'],rotation='horizontal',fontsize=10)\nplt.yticks(fontsize=12)\n\nplt.subplot(2,2,4)\nplt.title('Total transactionRevenue mean by month',color='b',fontsize=12)\nplt.bar(a3['month'],a3['totals_transactionRevenue'],color='purple')\nplt.xticks(a3['month'],rotation='horizontal',fontsize=12)\nplt.yticks(fontsize=12)","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:28.443533Z","iopub.execute_input":"2023-02-07T03:55:28.443887Z","iopub.status.idle":"2023-02-07T03:55:29.815974Z","shell.execute_reply.started":"2023-02-07T03:55:28.443852Z","shell.execute_reply":"2023-02-07T03:55:29.814874Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Find the time period of same ID revisit","metadata":{}},{"cell_type":"code","source":"def add_time_period_of_same_ID(df): \n print(\"Start add time period feature...\")\n df.sort_values(['fullVisitorId', 'date'], ascending=True, inplace=True)\n df['next_revisit_time'] = (\n df['date'] - df[['fullVisitorId', 'date']].groupby('fullVisitorId')['date'].shift(1)\n ).astype(np.int64) // 1e9 // 60 // 60\n df['prev_revisit_time'] = (\n df['date'] - df[['fullVisitorId', 'date']].groupby('fullVisitorId')['date'].shift(-1)\n ).astype(np.int64) // 1e9 // 60 // 60\n print(\"Finished time periodfeature...\")\nadd_time_period_of_same_ID(train)\nadd_time_period_of_same_ID(test)\n","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:29.817462Z","iopub.execute_input":"2023-02-07T03:55:29.81771Z","iopub.status.idle":"2023-02-07T03:55:47.36781Z","shell.execute_reply.started":"2023-02-07T03:55:29.817674Z","shell.execute_reply":"2023-02-07T03:55:47.366726Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- Plot the epriod time that revisit cause totals_transactionRevenue\n- We found that the totals_transactionRevenue are locate at small revisit time portion ### We keep the feature of next and previous revisit time","metadata":{}},{"cell_type":"code","source":"b = train[['next_revisit_time','totals_transactionRevenue']].groupby('next_revisit_time').mean().reset_index()\nb[b['next_revisit_time']== -2562048] = 0 #Process no revisit ID to 0\nb.sort_values(by = \"next_revisit_time\",ascending=False).fillna(0)\n\nb2 = train[['prev_revisit_time','totals_transactionRevenue']].groupby('prev_revisit_time').mean().reset_index()\nb2[b2['prev_revisit_time']== -2562048] = 0\nb2.sort_values(by = \"prev_revisit_time\",ascending=False).fillna(0)\n\nplt.subplots(figsize=(15,10))\nplt.subplot(2,1,1)\nplt.title('Total transactionRevenue mean by sort of next revisit time',color='b',fontsize=12)\nplt.scatter(b['next_revisit_time'],b['totals_transactionRevenue'],color='orange')\n#plt.xticks(b.index,rotation='horizontal',fontsize=12)\nplt.yticks(fontsize=12)\n\nplt.subplot(2,1,2)\nplt.title('Total transactionRevenue mean by sort of previous revisit time',color='b',fontsize=12)\nplt.scatter(b2['prev_revisit_time'],b2['totals_transactionRevenue'],color='orange')\n#plt.xticks(b.index,rotation='horizontal',fontsize=12)\nplt.yticks(fontsize=12)","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:47.369181Z","iopub.execute_input":"2023-02-07T03:55:47.369458Z","iopub.status.idle":"2023-02-07T03:55:47.949761Z","shell.execute_reply.started":"2023-02-07T03:55:47.369426Z","shell.execute_reply":"2023-02-07T03:55:47.948692Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Analysis Visit count and Revenue relation\n- Result show the ditribution have no any relation with Revenue, No need to add this feature","metadata":{}},{"cell_type":"code","source":"c = train[['fullVisitorId','totals_transactionRevenue']]\nc['counts'] = c['fullVisitorId']\nc = c.groupby(['fullVisitorId']).agg({'totals_transactionRevenue':'mean','counts':'count'}).reset_index()\nc = c.sort_values(by='counts').fillna(0).groupby('counts').mean().reset_index()\n\nlines = c.plot.scatter(x='counts', y='totals_transactionRevenue',figsize=(15,5))","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:47.951229Z","iopub.execute_input":"2023-02-07T03:55:47.951671Z","iopub.status.idle":"2023-02-07T03:55:50.418271Z","shell.execute_reply.started":"2023-02-07T03:55:47.951613Z","shell.execute_reply":"2023-02-07T03:55:50.4172Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Find the categorical feature and process data\n- We can see there are missing value \"NaN\" on the data\n- Find the categorical feature and visulize the data to analysis\n- Analysis Unique count < 15, and unique count>15","metadata":{}},{"cell_type":"markdown","source":"Find the categorical feature","metadata":{}},{"cell_type":"code","source":"categorical_features_train = train.select_dtypes(include=[np.object])\ncategorical_features_test = test.select_dtypes(include=[np.object])\ncategorical_features_train.columns","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:50.419622Z","iopub.execute_input":"2023-02-07T03:55:50.419881Z","iopub.status.idle":"2023-02-07T03:55:52.011816Z","shell.execute_reply.started":"2023-02-07T03:55:50.419851Z","shell.execute_reply":"2023-02-07T03:55:52.010643Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Analysis missing values for all categorical features","metadata":{}},{"cell_type":"markdown","source":"- Training Set","metadata":{}},{"cell_type":"code","source":"Nan_value_train = categorical_features_train.isnull().sum().sort_values(ascending=False)\nPercent_Nan_value_train = Nan_value_train/categorical_features_train.shape[0]\nNan_train_data = pd.concat([Nan_value_train,Percent_Nan_value_train], axis= 1,keys = ['Missing value count','Percent'])\nNan_train_data.head(10)","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:52.013899Z","iopub.execute_input":"2023-02-07T03:55:52.014387Z","iopub.status.idle":"2023-02-07T03:55:55.830356Z","shell.execute_reply.started":"2023-02-07T03:55:52.014337Z","shell.execute_reply":"2023-02-07T03:55:55.829496Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- Test Set","metadata":{}},{"cell_type":"code","source":"Nan_value_test = categorical_features_test.isnull().sum().sort_values(ascending=False)\nPercent_Nan_value_test = Nan_value_test/categorical_features_test.shape[0]\nNan_test_data = pd.concat([Nan_value_test,Percent_Nan_value_test], axis= 1,keys = ['Missing value count','Percent'])\nNan_test_data.head(10)","metadata":{"execution":{"iopub.status.busy":"2023-02-07T03:55:55.831324Z","iopub.execute_input":"2023-02-07T03:55:55.831573Z","iopub.status.idle":"2023-02-07T03:55:59.25507Z","shell.execute_reply.started":"2023-02-07T03:55:55.831538Z","shell.execute_reply":"2023-02-07T03:55:59.25419Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Analysis Total feature","metadata":{}},{"cell_type":"markdown","source":"- Analysis total feature by bropuby full ID","metadata":{}},{"cell_type":"code","source":"Totals_features = [i for i in train.columns if i.find(\"totals\")>=0 ]\nTotals_features","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:06:30.411349Z","iopub.execute_input":"2023-02-07T04:06:30.411853Z","iopub.status.idle":"2023-02-07T04:06:30.419944Z","shell.execute_reply.started":"2023-02-07T04:06:30.411817Z","shell.execute_reply":"2023-02-07T04:06:30.418941Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train['totals_pageviews']=train['totals_pageviews'].astype('float64')\ntrain['totals_hits']=train['totals_hits'].astype('float64')\ntest['totals_pageviews']=test['totals_pageviews'].astype('float64')\ntest['totals_hits']=test['totals_hits'].astype('float64')","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:09:07.668396Z","iopub.execute_input":"2023-02-07T04:09:07.668703Z","iopub.status.idle":"2023-02-07T04:09:10.54977Z","shell.execute_reply.started":"2023-02-07T04:09:07.668672Z","shell.execute_reply":"2023-02-07T04:09:10.548609Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"agg_dict = {}\nfor col in Totals_features:\n train[col] = train[col].astype('float')\n if col == 'totals_transactionRevenue':\n agg_dict[col] = 'mean'\n agg_dict[col] = \"sum\"\ntmp = train.groupby(\"fullVisitorId\").agg(agg_dict).reset_index()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:09:13.602413Z","iopub.execute_input":"2023-02-07T04:09:13.602789Z","iopub.status.idle":"2023-02-07T04:09:15.418883Z","shell.execute_reply.started":"2023-02-07T04:09:13.60275Z","shell.execute_reply":"2023-02-07T04:09:15.417406Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.subplots(figsize=(12,6))\nplt.subplot(1,2,1)\nplt.title('Revenue mean by totals hits',color='b',fontsize=12)\nplt.scatter(tmp['totals_hits'],tmp['totals_transactionRevenue'],color='b')\n#plt.xticks(b.index,rotation='horizontal',fontsize=12)\nplt.yticks(fontsize=12)\n\nplt.subplot(1,2,2)\nplt.title('Revenue mean by totals pageviews',color='b',fontsize=12)\nplt.scatter(tmp['totals_pageviews'],tmp['totals_transactionRevenue'],color='b')\n#plt.xticks(b.index,rotation='horizontal',fontsize=12)\nplt.yticks(fontsize=12)","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:09:15.420655Z","iopub.execute_input":"2023-02-07T04:09:15.421376Z","iopub.status.idle":"2023-02-07T04:09:20.724391Z","shell.execute_reply.started":"2023-02-07T04:09:15.421276Z","shell.execute_reply":"2023-02-07T04:09:20.723375Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Analysis unique count <15 on categorical features","metadata":{}},{"cell_type":"markdown","source":"- Cause we have different unique count of test set and train set, lets concate it to do one hot encoding","metadata":{}},{"cell_type":"markdown","source":"categorical_unique = train[list(categorical_features_test)].nunique().reset_index()\ncategorical_unique.columns = ['features','unique_count']\ncategorical_unique_less_15 = categorical_unique.loc[categorical_unique['unique_count'] < 15,\"features\"]\ncategorical_unique_less_15 = list(categorical_unique_less_15)\ncategorical_unique_less_15","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:10:33.431214Z","iopub.execute_input":"2023-02-07T04:10:33.43155Z","iopub.status.idle":"2023-02-07T04:10:42.448812Z","shell.execute_reply.started":"2023-02-07T04:10:33.43152Z","shell.execute_reply":"2023-02-07T04:10:42.447826Z"}}},{"cell_type":"markdown","source":"## Analysis the feature that have unique count >= 15","metadata":{}},{"cell_type":"markdown","source":"- Anaysis the revenue of these feature\n- Process different type feature to be training feature","metadata":{}},{"cell_type":"code","source":"excluded_features = [\n 'date', 'fullVisitorId', 'sessionId', 'totals.transactionRevenue', \n 'visitId', 'visitStartTime']\ncategorical_larger_15_feature = [i for i in categorical_features_train if i not in categorical_unique_less_15 and i not in excluded_features]\nprint(categorical_larger_15_feature)","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:11:23.374941Z","iopub.execute_input":"2023-02-07T04:11:23.375907Z","iopub.status.idle":"2023-02-07T04:11:23.385898Z","shell.execute_reply.started":"2023-02-07T04:11:23.375864Z","shell.execute_reply":"2023-02-07T04:11:23.384392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Analysis Revene correlation on geoNetwork features\n- Plot the Count / Revenue correlation for all geoNetwork features","metadata":{}},{"cell_type":"code","source":"geoNetwork_feature = [i for i in categorical_larger_15_feature if i.find('geoNetwork')>=0]\nprint(geoNetwork_feature)","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:15:45.279651Z","iopub.execute_input":"2023-02-07T04:15:45.27998Z","iopub.status.idle":"2023-02-07T04:15:45.285962Z","shell.execute_reply.started":"2023-02-07T04:15:45.279946Z","shell.execute_reply":"2023-02-07T04:15:45.284837Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.subplots(figsize=(20,60))\nfor k,i in enumerate(geoNetwork_feature): \n train_geoNetwork = train[[i]+[\"totals_transactionRevenue\"]]\n train_geoNetwork['totals_transactionRevenue']=train_geoNetwork['totals_transactionRevenue'].fillna(0).astype('float64')\n train_geoNetwork[i+'_counts'] = train_geoNetwork[i].fillna('others')\n train_geoNetwork = train_geoNetwork.groupby(i).agg({'totals_transactionRevenue':'mean',i+'_counts':'count'}).reset_index()\n train_geoNetwork = train_geoNetwork.sort_values(by='totals_transactionRevenue',ascending=False)\n \n\n plt.subplot(len(geoNetwork_feature),2,(2*k)+1)\n plt.title('mean revenue by '+i,color='b',fontsize=12)\n plt.xlabel(str(i),color='b',fontsize=12)\n plt.barh(range(10),train_geoNetwork.totals_transactionRevenue[:10],color='grey')\n plt.yticks(range(10),train_geoNetwork[i][:10],rotation='horizontal',fontsize=10)\n plt.xticks(fontsize=12)\n \n plt.subplot(len(geoNetwork_feature),2,(2*k)+2)\n plt.title('Count by '+i,color='b',fontsize=12)\n plt.xlabel(str(i),color='b',fontsize=12)\n plt.barh(range(10),train_geoNetwork[i+'_counts'][:10],color='orange')\n plt.yticks(range(10),train_geoNetwork[i][:10],rotation='horizontal',fontsize=10)\n plt.xticks(fontsize=12)\n \nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:15:47.648766Z","iopub.execute_input":"2023-02-07T04:15:47.649818Z","iopub.status.idle":"2023-02-07T04:15:57.497316Z","shell.execute_reply.started":"2023-02-07T04:15:47.649759Z","shell.execute_reply":"2023-02-07T04:15:57.496439Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Analysis Revene correlation on device and traffic features\n- Plot the Count / Revenue correlation for device and traffic features","metadata":{}},{"cell_type":"markdown","source":"- Device features","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:13:07.369808Z","iopub.execute_input":"2023-02-07T04:13:07.370296Z","iopub.status.idle":"2023-02-07T04:13:07.377297Z","shell.execute_reply.started":"2023-02-07T04:13:07.370255Z","shell.execute_reply":"2023-02-07T04:13:07.375772Z"}}},{"cell_type":"code","source":"device_feature = [i for i in categorical_larger_15_feature if i.find('device')>=0]\nprint(device_feature)","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:16:09.853194Z","iopub.execute_input":"2023-02-07T04:16:09.853805Z","iopub.status.idle":"2023-02-07T04:16:09.858977Z","shell.execute_reply.started":"2023-02-07T04:16:09.853755Z","shell.execute_reply":"2023-02-07T04:16:09.858311Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.subplots(figsize=(20,30))\nfor k,i in enumerate(device_feature): \n train_device = train[[i]+[\"totals_transactionRevenue\"]]\n train_device['totals_transactionRevenue']=train_device['totals_transactionRevenue'].fillna(0).astype('float64')\n train_device[i+'_counts'] = train_device[i].fillna('others')\n train_device = train_device.groupby(i).agg({'totals_transactionRevenue':'mean',i+'_counts':'count'}).reset_index()\n train_device = train_device.sort_values(by='totals_transactionRevenue',ascending=False)\n \n\n plt.subplot(len(device_feature),2,(2*k)+1)\n plt.title('mean revenue by '+i,color='b',fontsize=12)\n plt.xlabel(str(i),color='b',fontsize=12)\n plt.barh(range(10),train_device.totals_transactionRevenue[:10],color='grey')\n plt.yticks(range(10),train_device[i][:10],rotation='horizontal',fontsize=10)\n plt.xticks(fontsize=12)\n \n plt.subplot(len(device_feature),2,(2*k)+2)\n plt.title('Count by '+i,color='b',fontsize=12)\n plt.xlabel(str(i),color='b',fontsize=12)\n plt.barh(range(10),train_device[i+'_counts'][:10],color='orange')\n plt.yticks(range(10),train_device[i][:10],rotation='horizontal',fontsize=10)\n plt.xticks(fontsize=12)\n \nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:16:11.664619Z","iopub.execute_input":"2023-02-07T04:16:11.665247Z","iopub.status.idle":"2023-02-07T04:16:14.964378Z","shell.execute_reply.started":"2023-02-07T04:16:11.665209Z","shell.execute_reply":"2023-02-07T04:16:14.963269Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- Traffic features Remove path and gclId feature","metadata":{}},{"cell_type":"code","source":"Traffic_feature = [i for i in categorical_larger_15_feature if i.find('traffic')>=0 and i !='trafficSource_adwordsClickInfo.gclId' and i!='trafficSource_referralPath']\nprint(Traffic_feature)","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:16:20.577116Z","iopub.execute_input":"2023-02-07T04:16:20.577421Z","iopub.status.idle":"2023-02-07T04:16:20.583933Z","shell.execute_reply.started":"2023-02-07T04:16:20.577384Z","shell.execute_reply":"2023-02-07T04:16:20.582864Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.subplots(figsize=(20,60))\nfor k,i in enumerate(Traffic_feature): \n if i !='trafficSource_adwordsClickInfo.gclId' and i!='trafficSource_referralPath':\n train_traffic = train[[i]+[\"totals_transactionRevenue\"]]\n train_traffic['totals_transactionRevenue']=train_traffic['totals_transactionRevenue'].fillna(0).astype('float64')\n train_traffic[i+'_counts'] = train_traffic[i].fillna('others')\n train_traffic = train_traffic.groupby(i).agg({'totals_transactionRevenue':'mean',i+'_counts':'count'}).reset_index()\n train_traffic = train_traffic.sort_values(by='totals_transactionRevenue',ascending=False)\n\n\n plt.subplot(len(geoNetwork_feature),2,(2*k)+1)\n plt.title('mean revenue by '+i,color='b',fontsize=12)\n plt.xlabel(str(i),color='b',fontsize=12)\n plt.barh(range(10),train_traffic.totals_transactionRevenue[:10],color='grey')\n plt.yticks(range(10),train_traffic[i][:10],rotation='horizontal',fontsize=10)\n plt.xticks(fontsize=12)\n\n plt.subplot(len(geoNetwork_feature),2,(2*k)+2)\n plt.title('Count by '+i,color='b',fontsize=12)\n plt.xlabel(str(i),color='b',fontsize=12)\n plt.barh(range(10),train_traffic[i+'_counts'][:10],color='orange')\n plt.yticks(range(10),train_traffic[i][:10],rotation='horizontal',fontsize=10)\n plt.xticks(fontsize=12)\n \nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:17:02.60452Z","iopub.execute_input":"2023-02-07T04:17:02.605006Z","iopub.status.idle":"2023-02-07T04:17:05.918943Z","shell.execute_reply.started":"2023-02-07T04:17:02.604967Z","shell.execute_reply":"2023-02-07T04:17:05.918148Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Analysis Revenue\n- First we analysis the assist value of Transations Revenue\n- Plot Distribution of total Revenue\n- Add visiter level Revenue","metadata":{}},{"cell_type":"code","source":"Revenue_assist_count = train[\"totals_transactionRevenue\"].count()\nRevenue_nonassist_count = train.shape[0]-Revenue_assist_count\nlabel_name = [\"Revenue_assist_count\",\"Revenue_nonassist_count\"]\n\nRevenue_assist_list ={\n \"Revenue\":label_name,\n \"Revenue_count\":[Revenue_assist_count,Revenue_nonassist_count]\n}\n\nRevenue_assist_dataframe = pd.DataFrame(Revenue_assist_list)\n\n#Revenue_non_zero = train[train[\"totals_transactionRevenue\"]>0][\"totals_transactionRevenue\"]\nRevenue_non_zero = train[\"totals_transactionRevenue\"].fillna(0).reset_index().astype('float64')\nRevenue_non_zero = Revenue_non_zero[Revenue_non_zero[\"totals_transactionRevenue\"]>0][\"totals_transactionRevenue\"]\n\n\nplt.figure(figsize=(18,18))\nax1=plt.subplot(221)\nRevenue_assist_dataframe.plot(kind='pie', y = 'Revenue_count', ax=ax1,autopct='%1.1f%%',\n startangle=90, shadow=False, labels=Revenue_assist_dataframe['Revenue'], legend = False, fontsize=14)\nplt.subplot(222)\nsns.distplot(Revenue_non_zero)\nplt.title(\"Distribution of Non-Zero Total Revenue\")\nplt.xlabel('Total TransactionRevenue')\nplt.subplot(212)\nsns.distplot(np.log1p(Revenue_non_zero))\nplt.title(\"Natural Log Distribution of Non Zero Total Transactions\");\nplt.xlabel(\"Natural Log - Total Transactions\");","metadata":{"execution":{"iopub.status.busy":"2023-02-07T04:19:58.470789Z","iopub.execute_input":"2023-02-07T04:19:58.471355Z","iopub.status.idle":"2023-02-07T04:19:59.637693Z","shell.execute_reply.started":"2023-02-07T04:19:58.471302Z","shell.execute_reply":"2023-02-07T04:19:59.636726Z"},"trusted":true},"execution_count":null,"outputs":[]}]}