{"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":"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)\nimport json\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\nimport json\nimport gc\n\nfrom scipy.stats import uniform\n\nimport warnings\nwarnings.filterwarnings(\"ignore\")\n\nfrom sklearn.svm import SVR, LinearSVR\nfrom sklearn.decomposition import PCA\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.preprocessing import OneHotEncoder\nfrom sklearn.model_selection import train_test_split, RandomizedSearchCV\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-09-27T20:37:57.811790Z","iopub.execute_input":"2023-09-27T20:37:57.812122Z","iopub.status.idle":"2023-09-27T20:37:58.771318Z","shell.execute_reply.started":"2023-09-27T20:37:57.812095Z","shell.execute_reply":"2023-09-27T20:37:58.769906Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# The device, geonetwork, totals, trafficSource columns have multiple values within \n# their corresponding columns\n# Define the load_df function to substract these values\ndef load_df(csv_path='../input/train.csv', nrows=None):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    \n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'}, # Important!!\n                     nrows=nrows)\n    # Use json_normalize function to clean these columns which have the embedding structure\n    for column in JSON_COLUMNS:\n        column_as_df = pd.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    print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n    return df","metadata":{"execution":{"iopub.status.busy":"2023-09-27T20:23:15.200500Z","iopub.execute_input":"2023-09-27T20:23:15.200782Z","iopub.status.idle":"2023-09-27T20:23:15.210086Z","shell.execute_reply.started":"2023-09-27T20:23:15.200739Z","shell.execute_reply":"2023-09-27T20:23:15.208926Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = load_df('/kaggle/input/ga-customer-revenue-prediction/train.csv')","metadata":{"execution":{"iopub.status.busy":"2023-09-27T20:23:15.211699Z","iopub.execute_input":"2023-09-27T20:23:15.212085Z","iopub.status.idle":"2023-09-27T20:24:22.206488Z","shell.execute_reply.started":"2023-09-27T20:23:15.212060Z","shell.execute_reply":"2023-09-27T20:24:22.205455Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.columns","metadata":{"execution":{"iopub.status.busy":"2023-09-27T20:24:22.208270Z","iopub.execute_input":"2023-09-27T20:24:22.208524Z","iopub.status.idle":"2023-09-27T20:24:22.215668Z","shell.execute_reply.started":"2023-09-27T20:24:22.208504Z","shell.execute_reply":"2023-09-27T20:24:22.214798Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.info()","metadata":{"execution":{"iopub.status.busy":"2023-09-27T20:24:22.216951Z","iopub.execute_input":"2023-09-27T20:24:22.217247Z","iopub.status.idle":"2023-09-27T20:24:25.752339Z","shell.execute_reply.started":"2023-09-27T20:24:22.217220Z","shell.execute_reply":"2023-09-27T20:24:25.751020Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# The df.info shows that for columns in [totals.bounces, totlas, newVisits, transaction Revenue, trafficSource.keyword, trafficSource.isTrueDirect, trafficSource.referralPath,  50  trafficSource.adwordsClickInfo.gclId               21561 non-null   objtrafficSource.adwordsClickInfo.adNetworkType\n# trafficSource.adwordsClickInfo.isVideoAd, trafficSource.adContent, trafficSource.campaignCode]\n# There are NULL values.\n# First check columns contain nulls with their null percentage\n# Then CLean up the data\nnull_percentage = pd.DataFrame()\nfor col in df.columns:\n    if df[col].isnull().sum() > 0:\n        null_percentage.loc[col,'NullPercentage'] = (df[col].isnull().sum())/len(df) * 100 \nprint(null_percentage)","metadata":{"execution":{"iopub.status.busy":"2023-09-27T20:24:25.753666Z","iopub.execute_input":"2023-09-27T20:24:25.753957Z","iopub.status.idle":"2023-09-27T20:24:29.642372Z","shell.execute_reply.started":"2023-09-27T20:24:25.753932Z","shell.execute_reply":"2023-09-27T20:24:29.641433Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Note that there is 98.9% null percentage in our target column 'totals.transactionRevenue', while it is normal, meaning that most of the customers didn't make an trascation.\n'trafficSource.adContent' and 'trafficSource.campaignCode' contains too much NULL value, thus we consider to drop them.\n'totals.newVisits' & 'totals.pageviews' could be imputed and filled by mode.","metadata":{}},{"cell_type":"code","source":"def clean_up(df):\n    df.drop('trafficSource.adContent', axis=1, inplace=True)\n    df.drop('trafficSource.campaignCode', axis=1, inplace=True)\n    df[\"totals.transactionRevenue\"] = df['totals.transactionRevenue'].fillna(\"0\").astype(float)\n    \n    df[\"totals.pageviews\"] = df[\"totals.pageviews\"].fillna(0).astype(int)\n    df[\"totals.newVisits\"] = df[\"totals.newVisits\"].fillna(0).astype(int)\n    return df\n\ndf = clean_up(df)","metadata":{"execution":{"iopub.status.busy":"2023-09-27T20:24:29.643887Z","iopub.execute_input":"2023-09-27T20:24:29.644194Z","iopub.status.idle":"2023-09-27T20:24:31.898218Z","shell.execute_reply.started":"2023-09-27T20:24:29.644169Z","shell.execute_reply":"2023-09-27T20:24:31.896830Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.describe()","metadata":{"execution":{"iopub.status.busy":"2023-09-27T20:24:31.900050Z","iopub.execute_input":"2023-09-27T20:24:31.900475Z","iopub.status.idle":"2023-09-27T20:24:32.030713Z","shell.execute_reply.started":"2023-09-27T20:24:31.900438Z","shell.execute_reply":"2023-09-27T20:24:32.029376Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.head(5)","metadata":{"execution":{"iopub.status.busy":"2023-09-27T20:29:44.697062Z","iopub.execute_input":"2023-09-27T20:29:44.697347Z","iopub.status.idle":"2023-09-27T20:29:44.718728Z","shell.execute_reply.started":"2023-09-27T20:29:44.697321Z","shell.execute_reply":"2023-09-27T20:29:44.717581Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Select the top 10 countries with most transcation records\ntop_countries = df[[\"geoNetwork.country\"]].groupby(\"geoNetwork.country\").size().sort_values(ascending=False)[:10].index.to_numpy()\navg_totals_transactionRevenue = df.groupby('geoNetwork.country')['totals.transactionRevenue'].mean().reset_index()\ndf_top_countries = avg_totals_transactionRevenue[avg_totals_transactionRevenue[\"geoNetwork.country\"].isin(top_countries)]\ndf_top_countries\n\nplt.figure(figsize=(18,4))\nsns.barplot(data=df_top_countries, x=\"geoNetwork.country\", y=\"totals.transactionRevenue\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-09-27T20:45:41.903478Z","iopub.execute_input":"2023-09-27T20:45:41.903848Z","iopub.status.idle":"2023-09-27T20:45:42.633769Z","shell.execute_reply.started":"2023-09-27T20:45:41.903819Z","shell.execute_reply":"2023-09-27T20:45:42.632685Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"check the impact of geoNetwork.country, geoNetwork.region, geoNetwork.metro and geoNetwork.city","metadata":{}},{"cell_type":"code","source":"# Select the top 10 cities with most transcation records\ntop_cities = df[[\"geoNetwork.city\"]].groupby(\"geoNetwork.city\").size().sort_values(ascending=False)[:10].index.to_numpy()\navg_totals_transactionRevenue = df.groupby('geoNetwork.city')['totals.transactionRevenue'].mean().reset_index()\ndf_top_cities = avg_totals_transactionRevenue[avg_totals_transactionRevenue[\"geoNetwork.city\"].isin(top_cities)]\ndf_top_cities\n\nplt.figure(figsize=(18,4))\nsns.barplot(data=df_top_cities, x=\"geoNetwork.city\", y=\"totals.transactionRevenue\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-09-27T20:51:51.652708Z","iopub.execute_input":"2023-09-27T20:51:51.653087Z","iopub.status.idle":"2023-09-27T20:51:52.450466Z","shell.execute_reply.started":"2023-09-27T20:51:51.653059Z","shell.execute_reply":"2023-09-27T20:51:52.449601Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Select the top 10 regions with most transcation records\ntop_regions = df[[\"geoNetwork.region\"]].groupby(\"geoNetwork.region\").size().sort_values(ascending=False)[:10].index.to_numpy()\navg_totals_transactionRevenue = df.groupby('geoNetwork.region')['totals.transactionRevenue'].mean().reset_index()\ndf_top_regions = avg_totals_transactionRevenue[avg_totals_transactionRevenue[\"geoNetwork.region\"].isin(top_regions)]\ndf_top_regions\n\nplt.figure(figsize=(18,4))\nsns.barplot(data=df_top_regions, x=\"geoNetwork.region\", y=\"totals.transactionRevenue\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2023-09-27T20:53:15.659685Z","iopub.execute_input":"2023-09-27T20:53:15.660070Z","iopub.status.idle":"2023-09-27T20:53:16.440757Z","shell.execute_reply.started":"2023-09-27T20:53:15.660042Z","shell.execute_reply":"2023-09-27T20:53:16.439485Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}