{"cells":[{"metadata":{"_uuid":"515ec234e6b8ca8d80e2bc20f667c653ebac116b"},"cell_type":"markdown","source":"# * Load the Libraries*#"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\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 \"../input/\" directory.\nimport os\nprint(os.listdir(\"../input\"))\nimport json\nfrom pandas.io.json import json_normalize\nimport matplotlib.pyplot as plt\nfrom sklearn import preprocessing\n# Any results you write to the current directory are saved as output.\n# Baby Nian will get started now.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b0d6fc461bddb41d0220c2e74894af5ce9d98287"},"cell_type":"markdown","source":"# **1. Import and Preprocess the Data**#"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"def load_df(path, nrows=None):\n    json_columns = ['device','geoNetwork','totals','trafficSource']\n    \n    df = pd.read_csv(path,\n                     #make sure that the json in csv will be converted as dict, otherwise, it will be string\n                     converters = {json_column: json.loads for json_column in json_columns},\n\n                     #make sure 'fullVisitorId' is string\n                     dtype = {'fullVisitorId':'str'},\n                     nrows=nrows)\n\n    for json_column in json_columns:\n        #conver the dict as a dataframe\n        converted_df = json_normalize(df[json_column])\n\n        #format the name, f'{json_column}.{subcolumn}' = '{}.{}'.format(json_column,subcolumn)\n        converted_df.columns = [f'{json_column}.{subcolumn}' for subcolumn in converted_df.columns]\n\n        #remove the origin columns that are dict, and add the dataframe made of the new columns, keep both the ids\n        df = df.drop(json_column, axis = 1).merge(converted_df, right_index=True, left_index=True)\n        \n    print(f'file:{path},shape:{df.shape}')\n    #output the loaded and processed df\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"4f1e6981a067a6b82a95c06e7d7b8d2cc2895724"},"cell_type":"code","source":"%%time\ntrain_df = load_df('../input/train.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f5c5d884ed1dfc2ce6aa6e273dd00318e24f5a06"},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c5e58d45822cb6d708965fb0e1f1676bb848debf"},"cell_type":"code","source":"%%time\ntest_df = load_df('../input/test.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"1a60971d37a4aaf76766ddd3e03302bdfbbf4b91"},"cell_type":"code","source":"test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"06bcb88ad8ae45532761fbbbde9543aac3fd684a"},"cell_type":"code","source":"print('There are 2 columns that are in training set but not in the test set. They are',set(train_df.columns).difference(set(test_df.columns)))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"007e6144c4478210e3e2a7aec803332264ed5be8"},"cell_type":"markdown","source":"totals.transactionRevenue which we call it **\"revenue\"** from now on, is what we need to predict, and it is supposed not to be in the test set. We will also remove the columnd of 'trafficSource.campaignCode' in the training set later."},{"metadata":{"_uuid":"4713e379d3f6f613b89a52f69408619db7633b2a"},"cell_type":"markdown","source":"***You also skip the rest of the part from here, directly go to 2.0 Feature Engineering***"},{"metadata":{"trusted":true,"_uuid":"6f5bd918c5c31d776abc460ae700c0270bd9bae9"},"cell_type":"code","source":"#make the revenue all floats\ntrain_df['totals.transactionRevenue'] = train_df['totals.transactionRevenue'].astype('float')\ntrain_df['totals.transactionRevenue'] = np.log1p(train_df['totals.transactionRevenue'].values)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d36d6aa8e8015273d4ad012ee913c9a8cdeba830"},"cell_type":"code","source":"#we want to explore how many people purchase repetitively first, to see we can use user indead of session as our index,.reset_index() will return a new dataframe instead of just an array\nvisitor_revenue_count = train_df.groupby('fullVisitorId')['totals.transactionRevenue'].count().sort_values(ascending=False).reset_index()\nvisitor_revenue_count.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f890d79d23a3365d28f3f9e7ca644f6a3e89afb1"},"cell_type":"markdown","source":"Wow, people who purchased frequently will do this more than 200 times!<br>\nLooks like the most frequent users purchased too many times. If we use user as index, that would introduce way too many features for one user. "},{"metadata":{"trusted":true,"_uuid":"5c496f56f90b908b94679d5200f86b693720ed1a"},"cell_type":"code","source":"#purchase time count chart\nvisitor_purchase_count = visitor_revenue_count.groupby('totals.transactionRevenue').count()\n\nplt.figure(figsize=(12,5))\nx_visitor = visitor_purchase_count.index[:20]\ny_visitor = visitor_purchase_count ['fullVisitorId'][:20]\nplt.title('Purchase time count chart')\nplt.xlabel('Purchase time')\nplt.ylabel('Count')\nplt.bar(x_visitor,y_visitor)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c606a62538450ee6b87e38dc40a55328907e2469"},"cell_type":"markdown","source":"Also, most people who buy only do it once. So for now, we still use session as the index.<br>\nWe will predict test_df's revenue based on sessions, and then group the revenue result based on user id.\n"},{"metadata":{"trusted":true,"_uuid":"df258b6a5c59d6c58b4dc9982232353e57292838"},"cell_type":"code","source":"#Before we process the data, we can also check what's the ratio of epople who make purchase in a session.\nall_user_revenue = train_df.groupby(\"fullVisitorId\")[\"totals.transactionRevenue\"].sum().sort_values(ascending=False).reset_index()\nplt.figure(figsize=(12,5))\nplt.title('All user revenue')\nx_all_revenue = all_user_revenue.index\ny_all_revenue = all_user_revenue['totals.transactionRevenue'].values\nplt.scatter(x_all_revenue, y_all_revenue)\nplt.xlabel('user')\nplt.ylabel('Revenue')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"558f80c1b374ae79674f83aa05463b42cf49c5c3"},"cell_type":"markdown","source":"The distribution of the revenue from each user is also quite imbalanced."},{"metadata":{"trusted":true,"_uuid":"02f01f0164840e3bbb57fcf782dc0362d9cb3a43"},"cell_type":"code","source":"all_user = all_user_revenue.shape[0]\n\npurchased_user = all_user_revenue[all_user_revenue['totals.transactionRevenue'] > 0].shape[0]\n\nall_session = train_df.shape[0]\n\n#pd.notnull() will return a list of boolearn to check if each row is null.But if you apply a .sum(), False will be 0, True will be 1, and you get the total number of True\npurchased_session = train_df[train_df['totals.transactionRevenue'] > 0].shape[0]\n\nprint('There are',purchased_user,'users with the purchase, which is {percent:.2%} of all the users. There are'.format(percent=purchased_user/all_user),purchased_session,'sessions with the purchase, which is {percent:.2%} of all the sessions.'.format(percent=purchased_session/all_session))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5e278b23d46660dcf0dfd3bf4ad1d726b72e5561"},"cell_type":"markdown","source":"We understand that no matter from the user level or from the session level, the ones with purchse only consist a very small part. Then let's do the feature engineering."},{"metadata":{"_uuid":"f9e6ff174ce5948d2fd485232f0bc629e8aa1b0d"},"cell_type":"markdown","source":"First of all, let check what features(columns) having identical values, which makes them not useful."},{"metadata":{"trusted":true,"_uuid":"af3902de6377279dbf3ad1d07b7f8f78f9dde0fb"},"cell_type":"code","source":"#nunique() will return the unique numbers of a column, by default, dropna = False, but here, we make it True, because we don't want to ignore any null values\nunhelpful_columns = []\n\nfor column in train_df.columns:\n    if train_df[column].nunique(dropna = False) == 1:\n        unhelpful_columns.append(column)\n\nprint('There are',train_df.shape[1],'columns.',len(unhelpful_columns),'of them have identical values for all rows. And they are as follows:')\nprint(unhelpful_columns)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ec4d7dc043886e9789d9f3a0382b7bdbcfb48c87"},"cell_type":"markdown","source":"Since they are not helpful in predict the revenue, we are removing them from the training set, together with 'trafficSource.campaignCode' which does not exist in the test set."},{"metadata":{"trusted":true,"_uuid":"989966896d9255b3c2bdf11c9461fa07d3c1c18a"},"cell_type":"code","source":"unhelpful_columns.append('trafficSource.campaignCode')\ntrain_df = train_df.drop(unhelpful_columns, axis = 1)\nprint('After removing the unhelpful columns, we have',train_df.shape[1],'columns, and they are',train_df.columns)\nlen(train_df.columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a8a8f42fd63a2a62706f2ba495ead8e9c94d5357"},"cell_type":"code","source":"for column in unhelpful_columns:\n    if column in test_df.columns:\n        test_df = test_df.drop(column, axis = 1)\ntest_df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"db775aa105ac7d100b8391dbbcf54b46deb5d8d3"},"cell_type":"code","source":"print(test_df.shape,train_df.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a660f47bf2a07f591eaca439ed18b83556e68fb3"},"cell_type":"markdown","source":"# ** 2. Check the Data**#"},{"metadata":{"_uuid":"1052382159aaa86df6fe2d7cf52c51184e1ff8df"},"cell_type":"markdown","source":"Now, let's check how differernt features (columns) affect the result (totals.transactionRevenue)."},{"metadata":{"_uuid":"f8f04276313d3c8942fc1de93f3fa1840a48190b"},"cell_type":"markdown","source":"## **2.1 Check Device Information**##"},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"2b45f53857042c44eafccc9f37eeb1e45d268199"},"cell_type":"code","source":"#sort the device transaction value by grouping device browser (if you use .count to only check the numerb, you don't need to put the 'by' parameter in the sort_values() function)\n#.agg() let you check multiple info at one time, size is the total number of rows of a browser, count is number of rows that has revunue, mean is the average revenue\ntrain_df['device.browser'] = train_df['device.browser'].fillna('NA')\ndevice_browser = train_df.groupby('device.browser')['totals.transactionRevenue'].agg(['size','count','min','max','mean']).sort_values(by='mean',ascending = False)\ndevice_browser.columns = ['total rows','rows with revenue','min','max','average revenue']\n\n#type(device_browser)\n#print(device_browser)\n#below in comment is the simple pandas way to draw bar charts\n# ax_device_browser1=device_browser.head(10).plot.bar(y='total rows')\n# ax_device_browser2=device_browser.head(10).plot.bar(y='rows with revenue')\n\n# we are still going to use plt to do the bar chart, making them line up in one row and saving space\nx = device_browser.index[:20]\ny1 = device_browser['total rows'][:20]\ny2 = device_browser['rows with revenue'][:20]\ny3 = device_browser['min'][:20]\ny4 = device_browser['max'][:20]\ny5 = device_browser['average revenue'][:20]\n\n#subplot(3,1,3)means this plot has one row, 3 plots in a row,  this subplot is the first one\nprint('The effect of device browser on revenue')\nplt.figure(figsize=(25,12))\n\nplt.subplot(2,3,1)\nplt.bar(x,y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x,y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x,y3)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x,y4)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x,y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ndevice_browser.head(20)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1fc7d99c7c10c046bb0a21e1b4bde6c366e2e44c"},"cell_type":"markdown","source":"Looks like the browser type has a significant effect on the revenue. Our feature: Firefox, Chrome, Edge, Internet Explorer, Safar, Amazon Silk, Opera, Safari (in-app), Android Webview, others."},{"metadata":{"trusted":true,"_uuid":"e441d62f0e6e431c657204a326d7f6bb7116744b"},"cell_type":"code","source":"train_df['device.deviceCategory'] = train_df['device.deviceCategory'].fillna('NA')\ndevice_category = train_df.groupby('device.deviceCategory')['totals.transactionRevenue'].agg(['size','count','min','max','mean']).sort_values(by='count',ascending = False)\ndevice_category.columns = ['total rows','rows with revenue','min','max','average revenue']\n\nx = device_category.index[:20]\ny1 = device_category['total rows'][:20]\ny2 = device_category['rows with revenue'][:20]\ny3 = device_category['min'][:20]\ny4 = device_category['max'][:20]\ny5 = device_category['average revenue'][:20]\n\nprint('The effect of device category on revenue')\nplt.figure(figsize=(25,12))\n\nplt.subplot(2,3,1)\nplt.bar(x,y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x,y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x,y3)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x,y4)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x,y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ndevice_category.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"a63ef6be8049888c6b4b7bb48a3d0725cacefa92"},"cell_type":"code","source":"#train_df['device_isMobile'] = train_df['device_isMobile'].fillna('NA')\ndevice_isMobile = train_df.groupby('device.isMobile')['totals.transactionRevenue'].agg(['size','count','mean']).sort_values(by='count',ascending = False)\ndevice_isMobile.columns = ['total rows','rows with revenue','average revenue']\n\nx = device_isMobile.index\ny1 = device_isMobile['total rows']\ny2 = device_isMobile['rows with revenue']\ny3 = device_isMobile['average revenue']\n\nprint('The effect of device category on revenue')\nplt.figure(figsize=(16,5))\n\nplt.subplot(1,3,1)\nplt.bar(x,y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(1,3,2)\nplt.bar(x,y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(1,3,3)\nplt.bar(x,y3)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ndevice_isMobile.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1990dc93f7306b7321debcfc1096070381a88be5"},"cell_type":"markdown","source":"The affect of device category deos make a difference on revenue, but mobile and tablet are pretty close. Therefore, we only need to consider wheterh the device is \"moble\" or not to predict the revenue. This info is already given in another column."},{"metadata":{"trusted":true,"_uuid":"4269bf3da288b5d5beb51ff1c8fb8a9825e5809c"},"cell_type":"code","source":"train_df['device.operatingSystem'] = train_df['device.operatingSystem'].fillna('NA')\ndevice_operatingSystem = train_df.groupby('device.operatingSystem')['totals.transactionRevenue'].agg(['size','count','min','max','mean']).sort_values(by='mean',ascending = False)\ndevice_operatingSystem.columns = ['total rows','rows with revenue','min','max','average revenue']\n\nx = device_operatingSystem.index[:20]\ny1 = device_operatingSystem['total rows'][:20]\ny2 = device_operatingSystem['rows with revenue'][:20]\ny3 = device_operatingSystem['min'][:20]\ny4 = device_operatingSystem['max'][:20]\ny5 = device_operatingSystem['average revenue'][:20]\n\nprint('The effect of device category on revenue')\nplt.figure(figsize=(25,12))\n\nplt.subplot(2,3,1)\nplt.bar(x,y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x,y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x,y3)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x,y4)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x,y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\nplt.tight_layout()\nplt.show()\n\ndevice_operatingSystem.head(40)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"77c91eafa2b4b13713374004f19a86038fc671bf"},"cell_type":"markdown","source":"Looks like the browser operating system has a significant effect on the revenue. Features: Chrome OS, Macintosh, Linux, Windows, Android, iOS, others"},{"metadata":{"_uuid":"cabc936c712db826b539b8924fd1e468e7bd5cc2"},"cell_type":"markdown","source":"## **2.2 Check Geoinformation**##"},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":false,"trusted":true,"_uuid":"613e3e38aaf1c48e101322003cfe7a669668af07","scrolled":false},"cell_type":"code","source":"train_df['geoNetwork.city'] = train_df['geoNetwork.city'].fillna('NA')\ncity = train_df.groupby('geoNetwork.city')['totals.transactionRevenue'].agg(['size','count','min','max','mean']).sort_values(by='count',ascending = True)\ncity.columns = ['total rows','rows with revenue','min','max','average revenue']\n\nx = city.index[:100]\ny1 = city['total rows'][:100]\ny2 = city['rows with revenue'][:100]\ny3 = city['min'][:100]\ny4 = city['max'][:100]\ny5 = city['average revenue'][:100]\n\nprint('The effect of city')\nplt.figure(figsize=(24,35))\n\nplt.subplot(5,1,1)\nplt.bar(x,y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,2)\nplt.bar(x,y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,3)\nplt.bar(x,y3)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,4)\nplt.bar(x,y4)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,5)\nplt.bar(x,y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ncity.head(100)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"222bf2522bfede2d65cb71b39b046da029b3ea9a"},"cell_type":"markdown","source":"The city also has a significant impact on the revenu. If a user is from a hot city like Chicago or New York, he/she would have higher chances to purchase more. There are huge amount of users who purchase not a lot and is not trackable in terms of city. <br>\nIndeed, in Google Analytics, many cities would not be listed, like Singapore which is a city state. This can be compensated by another geological information - country. But most of the data doesn't have related info, we are not going to use this feature."},{"metadata":{"trusted":true,"_uuid":"b5db95af8de5acba44371381e706215fe928c0e8"},"cell_type":"code","source":"#country\ntrain_df['geoNetwork.country'] = train_df['geoNetwork.country'].fillna('NA')\ncountry_or_region = train_df.groupby('geoNetwork.country')['totals.transactionRevenue'].agg(['size','count','min','max','mean']).sort_values(by='count',ascending = True)\ncountry_or_region.columns = ['total rows','rows with revenue','min','max','average revenue']\n\nx = country_or_region.index[:100]\ny1 = country_or_region['total rows'][:100]\ny2 = country_or_region['rows with revenue'][:100]\ny3 = country_or_region['min'][:100]\ny4 = country_or_region['max'][:100]\ny5 = country_or_region['average revenue'][:100]\n\nprint('The effect of country')\nplt.figure(figsize=(24,35))\n\nplt.subplot(5,1,1)\nplt.bar(x,y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,2)\nplt.bar(x,y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,3)\nplt.bar(x,y3)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,4)\nplt.bar(x,y4)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,5)\nplt.bar(x,y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ncountry_or_region.head(150)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"93ce69e3969b1458c841c54ef60431672d047ca6"},"cell_type":"markdown","source":"We can clear see some countries don't contribute the the purchase at all. But there are just too many of them, and each country doesn't have many rows. So we can check the subcontinents' effect."},{"metadata":{"trusted":true,"_uuid":"72f1fb2da42bb0faef9119933026111d459a438c"},"cell_type":"code","source":"#country\ntrain_df['geoNetwork.subContinent'] = train_df['geoNetwork.subContinent'].fillna('NA')\ncountry_or_region = train_df.groupby('geoNetwork.subContinent')['totals.transactionRevenue'].agg(['size','count','min','max','mean']).sort_values(by='count',ascending = True)\ncountry_or_region.columns = ['total rows','rows with revenue','min','max','average revenue']\n\nx = country_or_region.index[:100]\ny1 = country_or_region['total rows'][:100]\ny2 = country_or_region['rows with revenue'][:100]\ny3 = country_or_region['min'][:100]\ny4 = country_or_region['max'][:100]\ny5 = country_or_region['average revenue'][:100]\n\nprint('The effect of sub continent')\nplt.figure(figsize=(24,35))\n\nplt.subplot(5,1,1)\nplt.bar(x,y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,2)\nplt.bar(x,y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,3)\nplt.bar(x,y3)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,4)\nplt.bar(x,y4)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(5,1,5)\nplt.bar(x,y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ncountry_or_region.head(150)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f86e10a7ce652c13b6887780bbd53b8e2af097c0"},"cell_type":"code","source":"train_df['geoNetwork.networkDomain'] = train_df['geoNetwork.networkDomain'].fillna('NA')\nnetwork_domain = train_df.groupby('geoNetwork.networkDomain')['totals.transactionRevenue'].agg(['size','count','mean']).sort_values(by='count',ascending = True)\nnetwork_domain.columns = ['total rows','rows with revenue','average revenue']\n\nx = network_domain.index[:100]\ny1 = network_domain['total rows'][:100]\ny2 = network_domain['rows with revenue'][:100]\ny3 = network_domain['average revenue'][:100]\n\nprint('The effect of network domain')\nplt.figure(figsize=(24,15))\n\nplt.subplot(3,1,1)\nplt.bar(x,y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(3,1,2)\nplt.bar(x,y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(3,1,3)\nplt.bar(x,y3)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nnetwork_domain.head(50)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4fd276fc5b4cf24749c985d03a1dc9d181a378e7"},"cell_type":"markdown","source":"## **2.3 Check User Behaviors**##"},{"metadata":{"trusted":true,"_uuid":"b1adce8c2a91115fa684adab8cf623374adef402"},"cell_type":"code","source":"train_df['visitNumber'] = train_df['visitNumber'].fillna(0)\nvisit_number = train_df.groupby('visitNumber')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = False)\nvisit_number.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = visit_number.index[:40]\ny1 = visit_number['total rows'][:40]\ny2 = visit_number['rows with revenue'][:40]\ny3 = visit_number['max'][:40]\ny4 = visit_number['min'][:40]\ny5 = visit_number['average revenue'][:40]\n\nprint('The effect of visit number')\nplt.figure(figsize=(24,16))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average avenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nvisit_number.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5c06693e137ac12909909e22e45ccfe3455e7ccf"},"cell_type":"markdown","source":"Visit number is a good feature we should keep it an convert to float."},{"metadata":{"trusted":true,"_uuid":"bb83a5233e953c5c33ad06a2ed929dc12e10d092"},"cell_type":"code","source":"train_df['totals.bounces'] = train_df['totals.bounces'].fillna(0)\ntotal_bounces = train_df.groupby('totals.bounces')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = False)\ntotal_bounces.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = total_bounces.index[:40]\ny1 = total_bounces['total rows'][:40]\ny2 = total_bounces['rows with revenue'][:40]\ny3 = total_bounces['max'][:40]\ny4 = total_bounces['max'][:40]\ny5 = total_bounces['average revenue'][:40]\n\nprint('The effect of total bounces')\nplt.figure(figsize=(24,8))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ntotal_bounces.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5a8a6f740a718b1d253efa1c39912028c4009897"},"cell_type":"markdown","source":"This is quite interesting, only the rows when totals.bounces = 0, there are purchases. This is not hard to understand intuitively - bounce means people leave the site without visiting a second page. So if a customer only visited the landing page (whatever it is) of the Google store but left without going anywhere else in the site, he/she of course wouldn't proceed to the check out page to buy anything. <br>\nThis findings will make our task easier - we only fit the training set and predict the test set when totals.bounces= 0, and then don't consider the effect of this feature. In the test set, when totals.bounces = 1, we directly make the revenue 0."},{"metadata":{"trusted":true,"_uuid":"af40109483daca1a2ebc29e4e389793a7af65469"},"cell_type":"code","source":"train_df['totals.hits'] = train_df['totals.hits'].fillna(0)\ntotal_hits = train_df.groupby('totals.hits')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = True)\ntotal_hits.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = total_hits.index[:65]\ny1 = total_hits['total rows'][:65]\ny2 = total_hits['rows with revenue'][:65]\ny3 = total_hits['max'][:65]\ny4 = total_hits['min'][:65]\ny5 = total_hits['average revenue'][:65]\n\nprint('The effect of total_hits')\nplt.figure(figsize=(24,15))\n\nplt.subplot(3,2,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ntotal_hits.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f08f59a80b19bdddb0bd747c4e88f905f427bba7"},"cell_type":"markdown","source":"It's not surprising that in a session, the more a user clicks, the higher possiblities he/she would buy more. People who clicked once apparently won't buy anything. So, we won't include data rows where total.hits=1, and predict zero revenue when this happens."},{"metadata":{"trusted":true,"_uuid":"1febb3d3c5b94dcd0cd6420e0474bea233c2850c"},"cell_type":"code","source":"train_df['totals.newVisits'] = train_df['totals.newVisits'].fillna(0)\n\ntotal_new_visits = train_df.groupby('totals.newVisits')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = False)\ntotal_new_visits.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = total_new_visits.index[:40]\ny1 = total_new_visits['total rows'][:40]\ny2 = total_new_visits['rows with revenue'][:40]\ny3 = total_new_visits['max'][:40]\ny4 = total_new_visits['min'][:40]\ny5 = total_new_visits['average revenue'][:40]\n\nprint('The effect of total new visits')\nplt.figure(figsize=(24,16))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ntotal_new_visits.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7e81001cbac1c75721ac33afda10f0828249f12c"},"cell_type":"code","source":"train_df['totals.pageviews'] = train_df['totals.pageviews'].fillna(0)\n\ntotal_pageviews = train_df.groupby('totals.pageviews')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = True)\ntotal_pageviews.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = total_pageviews.index[:50]\ny1 = total_pageviews['total rows'][:50]\ny2 = total_pageviews['rows with revenue'][:50]\ny3 = total_pageviews['max'][:50]\ny4 = total_pageviews['min'][:50]\ny5 = total_pageviews['average revenue'][:50]\n\nprint('The effect of total pageviews')\nplt.figure(figsize=(24,15))\n\nplt.subplot(3,2,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ntotal_pageviews.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e9c050f16fca8ddc3bff54cd573095ea691f8c74"},"cell_type":"markdown","source":"As we can see, total pageviews is a very good index for predicting revenue. The more pages a user visited, the higher chances he/she would buy more."},{"metadata":{"_uuid":"399511a0fd92fb580ed620e49efcac5bed869c6f"},"cell_type":"markdown","source":"As we finished checking total user behavior data, we also find that people who only hit once or only visit zero or one page don't buy anything at all either. We want't to find out the relationship between them and those whose bounces = 1."},{"metadata":{"trusted":true,"_uuid":"4191423768099979e6d5e46f5dc48372f5ace6b7"},"cell_type":"code","source":"bounces1 = train_df[train_df['totals.bounces']=='1']\nhitonce = train_df[train_df['totals.hits']=='1']\n\nfilter1 = train_df['totals.pageviews']== '1'\nfilter2 = train_df['totals.pageviews']== 0\nvisit0_1 = train_df[filter1|filter2]\n\nall_non_purchase = set(bounces1.index)&set(hitonce.index)&set(visit0_1.index)\nprint('There are',bounces1.shape[0],'people who bounced,',hitonce.shape[0],'people who only hit once,',visit0_1.shape[0],'people who only visited zero time or once, and',len(all_non_purchase),'people who did all of these.')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"45e475d2344353c939d26bdca46a930ac188670f"},"cell_type":"markdown","source":"Looks like these people are highly overlapping with each other. It's not difficult to know why since all the 3 features shows a user is not active on the site. In the real analysis, we will remove people with these features in the training set to train the model, and directly predict people with these features in the test set to have zero purchase."},{"metadata":{"_uuid":"5a8d418ab2888f444c47b70d6fb5ed5e3d035651"},"cell_type":"markdown","source":"## **2.4 Check Sources and Channels**#"},{"metadata":{"trusted":true,"_uuid":"fd1edf0c65d33f0328cec23b87674e44c1e2effe"},"cell_type":"code","source":"train_df['channelGrouping'] = train_df['channelGrouping'].fillna('NA')\nchannel = train_df.groupby('channelGrouping')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = False)\nchannel.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = channel.index[:40]\ny1 = channel['total rows'][:40]\ny2 = channel['rows with revenue'][:40]\ny3 = channel['max'][:40]\ny4 = channel['min'][:40]\ny5 = channel['average revenue'][:40]\n\nprint('The effect of channel')\nplt.figure(figsize=(24,16))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nchannel.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"11a53ec0f0cfbe99a342fb635784882a97d06760"},"cell_type":"markdown","source":"Looks like even tough people from organic search channel purchsed more times, but those who bought after seeing an display ad will spend more. <br>\nFeatures: 'Organic Search',  'Direct', 'Referral', 'Paid Search', 'Display', 'other'"},{"metadata":{"trusted":true,"_uuid":"c9dec9226ee81d25d3058a52c2966198f0ef7d30"},"cell_type":"code","source":"train_df['trafficSource.adContent'] = train_df['trafficSource.adContent'].fillna('NA')\nad_content = train_df.groupby('trafficSource.adContent')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = True)\nad_content.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = ad_content.index[:40]\ny1 = ad_content['total rows'][:40]\ny2 = ad_content['rows with revenue'][:40]\ny3 = ad_content['max'][:40]\ny4 = ad_content['min'][:40]\ny5 = ad_content['average revenue'][:40]\n\nprint('The effect of ad content')\nplt.figure(figsize=(24,15))\n\n#in the plot, the x can only be str, so we convert \"0/1\" to strings\nplt.subplot(3,2,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(3,2,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nad_content.head(50)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"74805b5d03a1612fecac475b2a63e83989bc43da"},"cell_type":"code","source":"aa = len(set(test_df['trafficSource.adContent']))\nbb = len(set(test_df['trafficSource.adContent']) & set(ad_content[ad_content['average revenue']>0].index))\nbb/aa","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b1d3ae35232855e8035edbc2aa1af3488bcea25f"},"cell_type":"code","source":"set(test_df['trafficSource.adContent']) & set(ad_content[ad_content['average revenue']>0].index)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8bb9e2c2b8eee6b352f7155b753b68a1386f1a69"},"cell_type":"markdown","source":"For the ads, different content will have different impacts on revenue. Pay attention that, even if there are 44 types of content of revenue, but only 12 of them will lead to a non zero revenue, and 8 of them are in the test set. But since most of the traffid doesn't have an ad, so wer are not going to use any features that is related to ads.<br>\nGenerally speaking, the traffic with non\nThen let's observe the clicks."},{"metadata":{"trusted":true,"_uuid":"3e8c7f1b5489a5157a842d4fe667a73017d905c3"},"cell_type":"code","source":"train_df['trafficSource.adwordsClickInfo.page'] = train_df['trafficSource.adwordsClickInfo.page'].fillna(0)\nad_click_page = train_df.groupby('trafficSource.adwordsClickInfo.page')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = False)\nad_click_page.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = ad_click_page.index[:40]\ny1 = ad_click_page['total rows'][:40]\ny2 = ad_click_page['rows with revenue'][:40]\ny3 = ad_click_page['max'][:40]\ny4 = ad_click_page['min'][:40]\ny5 = ad_click_page['average revenue'][:40]\n\nprint('The effect of ad click page')\nplt.figure(figsize=(24,16))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nad_click_page.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d71a7046e439b9bae7a12b2d89cb894564bf0a81"},"cell_type":"markdown","source":"For this column, we will have feature \"NA\",\"1\" and \"other\". It's just bizzare that some one will click more than once at an ad, and this is highly like a bot. Though we won't include this feature in the analysi, but the rows that 'trafficSource.adwordsClickInfo.page>1 must have a zero revenue."},{"metadata":{"trusted":true,"_uuid":"f94c1c3a11965ac6eee9c5d47397220a3396aa1f"},"cell_type":"code","source":"train_df['trafficSource.isTrueDirect'] = train_df['trafficSource.isTrueDirect'].fillna('False')\ntrue_direct = train_df.groupby('trafficSource.isTrueDirect')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = False)\ntrue_direct.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = true_direct.index[:40]\ny1 = true_direct['total rows'][:40]\ny2 = true_direct['rows with revenue'][:40]\ny3 = true_direct['max'][:40]\ny4 = true_direct['min'][:40]\ny5 = true_direct['average revenue'][:40]\n\nprint('The effect of true direct')\nplt.figure(figsize=(24,16))\n\n#in the plot, the x can only be str, so we convert \"0/1\" to strings\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ntrue_direct.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"768b17afb1afbd8aa1f008130fbfa407a723cc24"},"cell_type":"markdown","source":"As for this feature, we use 1 and 0 to express whether it is true direct or false."},{"metadata":{"trusted":true,"_uuid":"3c770000a7d0016290c58fb7f978690a66097e11"},"cell_type":"code","source":"train_df['trafficSource.keyword'] = train_df['trafficSource.keyword'].fillna('NA')\nkeywords = train_df.groupby('trafficSource.keyword')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = True)\nkeywords.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = keywords.index[:40]\ny1 = keywords['total rows'][:40]\ny2 = keywords['rows with revenue'][:40]\ny3 = keywords['max'][:40]\ny4 = keywords['min'][:40]\ny5 = keywords['average revenue'][:40]\n\nprint('The effect of keywords')\nplt.figure(figsize=(24,16))\n\n#in the plot, the x can only be str, so we convert \"0/1\" to strings\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nkeywords.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fdf2fe4b222b6b9da85648caad1fba7f86673902"},"cell_type":"code","source":"train_df['trafficSource.medium'] = train_df['trafficSource.medium'].fillna('NA')\nmedium = train_df.groupby('trafficSource.medium')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = False)\nmedium.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = medium.index[:40]\ny1 = medium['total rows'][:40]\ny2 = medium['rows with revenue'][:40]\ny3 = medium['max'][:40]\ny4 = medium['min'][:40]\ny5 = medium['average revenue'][:40]\n\nprint('The effect of medium')\nplt.figure(figsize=(24,16))\n\n#in the plot, the x can only be str, so we convert \"0/1\" to strings\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nmedium.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"14778abdbb08d363865540492a0bf310713410fa"},"cell_type":"markdown","source":"This is a good feature, we will use thse in the analysis."},{"metadata":{"trusted":true,"_uuid":"c296afb985e69cdd5a56a3cf6e69c1a7c03727b2"},"cell_type":"code","source":"train_df['trafficSource.referralPath'] = train_df['trafficSource.referralPath'].fillna('NA')\nreferral_path = train_df.groupby('trafficSource.referralPath')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='count',ascending = True)\nreferral_path.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = referral_path.index[:80]\ny1 = referral_path['total rows'][:80]\ny2 = referral_path['rows with revenue'][:80]\ny3 = referral_path['max'][:80]\ny4 = referral_path['min'][:80]\ny5 = referral_path['average revenue'][:80]\n\nprint('The effect of referral path')\nplt.figure(figsize=(24,30))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y2)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y4)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nreferral_path.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"533b64306ace7055ba09c2793e534dc20c847191"},"cell_type":"markdown","source":"Referral path is not a good feature, we are not going to use it in the analysis."},{"metadata":{"trusted":true,"_uuid":"b960892252a1cff2dd01ef867d4c5df45aea0919"},"cell_type":"code","source":"train_df['trafficSource.source'] = train_df['trafficSource.source'].fillna('NA')\nsource = train_df.groupby('trafficSource.source')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='mean',ascending = True)\nsource.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = source.index[:40]\ny1 = source['total rows'][:40]\ny2 = source['rows with revenue'][:40]\ny3 = source['max'][:40]\ny4 = source['min'][:40]\ny5 = source['average revenue'][:40]\n\nprint('The effect of source')\nplt.figure(figsize=(24,15))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y5)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nsource.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"63df00789eea0d807664b358d376a7559474c6d3"},"cell_type":"markdown","source":"This feature is weird. The source with the highst reveue only has 2 rows of data. The sources that have many data rows don't perform outstandingly in revenue. So we are not going to use it in the analysis."},{"metadata":{"trusted":true,"_uuid":"431494070c4d8db4b87a942039b487742d7da591"},"cell_type":"code","source":"train_df['trafficSource.adwordsClickInfo.adNetworkType'] = train_df['trafficSource.adwordsClickInfo.adNetworkType'].fillna('NA')\nadNetworkType = train_df.groupby('trafficSource.adwordsClickInfo.adNetworkType')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='mean',ascending = True)\nadNetworkType.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = adNetworkType.index[:40]\ny1 = adNetworkType['total rows'][:40]\ny2 = adNetworkType['rows with revenue'][:40]\ny3 = adNetworkType['max'][:40]\ny4 = adNetworkType['min'][:40]\ny5 = adNetworkType['average revenue'][:40]\n\nprint('The effect of adNetworkType')\nplt.figure(figsize=(24,15))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y5)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nadNetworkType.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7a97f41a88eaa47e05cd937880a4a1fc0ba1c19e"},"cell_type":"code","source":"train_df['trafficSource.adwordsClickInfo.gclId'] = train_df['trafficSource.adwordsClickInfo.gclId'].fillna('NA')\ngclId = train_df.groupby('trafficSource.adwordsClickInfo.gclId')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='mean',ascending = True)\ngclId.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = gclId.index[:40]\ny1 = gclId['total rows'][:40]\ny2 = gclId['rows with revenue'][:40]\ny3 = gclId['max'][:40]\ny4 = gclId['min'][:40]\ny5 = gclId['average revenue'][:40]\n\nprint('The effect of gclId')\nplt.figure(figsize=(24,15))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y5)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ngclId.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4471343f3613a5ca31aa318a240bbda447565b5e"},"cell_type":"code","source":"train_df['trafficSource.adwordsClickInfo.isVideoAd'] = train_df['trafficSource.adwordsClickInfo.isVideoAd'].fillna(True)\nisVideoAd = train_df.groupby('trafficSource.adwordsClickInfo.isVideoAd')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='mean',ascending = True)\nisVideoAd.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = isVideoAd.index[:40]\ny1 = isVideoAd['total rows'][:40]\ny2 = isVideoAd['rows with revenue'][:40]\ny3 = isVideoAd['max'][:40]\ny4 = isVideoAd['min'][:40]\ny5 = isVideoAd['average revenue'][:40]\n\nprint('The effect of isVideoAd')\nplt.figure(figsize=(24,15))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y5)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nisVideoAd.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4f96a10f43be9490f0a42ac8ab79f31ec04647a1"},"cell_type":"code","source":"train_df['trafficSource.adwordsClickInfo.slot'] = train_df['trafficSource.adwordsClickInfo.slot'].fillna('NA')\nslot = train_df.groupby('trafficSource.adwordsClickInfo.slot')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='mean',ascending = True)\nslot.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = slot.index[:40]\ny1 = slot['total rows'][:40]\ny2 = slot['rows with revenue'][:40]\ny3 = slot['max'][:40]\ny4 = slot['min'][:40]\ny5 = slot['average revenue'][:40]\n\nprint('The effect of slot')\nplt.figure(figsize=(24,15))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y5)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\nslot.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8f7dda8e5e25ec74c93ac94afcd2de08979909fd"},"cell_type":"code","source":"train_df['trafficSource.campaign'] = train_df['trafficSource.campaign'].fillna('NA')\ncampaign = train_df.groupby('trafficSource.campaign')['totals.transactionRevenue'].agg(['size','count','max','min','mean']).sort_values(by='mean',ascending = True)\ncampaign.columns = ['total rows','rows with revenue','max','min','average revenue']\n\nx = campaign.index[:40]\ny1 = campaign['total rows'][:40]\ny2 = campaign['rows with revenue'][:40]\ny3 = campaign['max'][:40]\ny4 = campaign['min'][:40]\ny5 = campaign['average revenue'][:40]\n\nprint('The effect of campaign')\nplt.figure(figsize=(24,15))\n\nplt.subplot(2,3,1)\nplt.bar(x.astype('str'),y1)\nplt.title('total rows')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,2)\nplt.bar(x.astype('str'),y2)\nplt.title('rows with revenue')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,3)\nplt.bar(x.astype('str'),y3)\nplt.title('max')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,4)\nplt.bar(x.astype('str'),y5)\nplt.title('min')\nplt.xticks(rotation=90)\n\nplt.subplot(2,3,5)\nplt.bar(x.astype('str'),y5)\nplt.title('average revenue')\nplt.xticks(rotation=90)\n\nplt.tight_layout()\nplt.show()\n\ncampaign.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"30211e7870b040b736f6f536594812d6bd906565"},"cell_type":"code","source":"train_df.columns","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"62fd47da92ef5d02f70778830cb259439ef99969"},"cell_type":"markdown","source":"Based on the data checking, we will finalize the appraoch the prepare the data in fitting and predicting."}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}