{"cells":[{"metadata":{"toc":true,"_uuid":"01dc0b11b3abf5f45fef57a51f3796c14d510105"},"cell_type":"markdown","source":"

Table of Contents

\n
"},{"metadata":{"_uuid":"f352592f756330e3443b835879873406ae17bdd3"},"cell_type":"markdown","source":"# Exploratory data analysis \n\n*By Jared Gong*\n\n探索性数据分析总是第一步。我首先需要读入数据，分析几个重要的变量，做一个简单的模型。"},{"metadata":{"_uuid":"2d3229dd07a8c9fc629fd8af0bd357753d60e68e"},"cell_type":"markdown","source":"## 比赛介绍\n\n>> Google Analytics Customer Revenue Prediction \n\n**Objective of the competition:**\n\n在本次竞赛中，我们面临的挑战是分析Google Merchandise Store（也称为GStore，销售谷歌周边）客户数据集，predicting the log of the total revenue per user, be aware that not all rows in `test.csv` will correspond to a row in the submission, but all unique `fullVisitorIds` will correspond to a row in the submission.\n\n\n有多个列包含不同深度的JSON blob。在其中一个JSON列（`total`）中，子列 `transactionRevenue` 包含我们尝试预测的收入信息。该子列仅存在于训练数据中。具体的来说，我们预测每个用户的所有交易总和的自然对数。对于测试集中的每个用户，目标是：\n\n$$\n\\text{target}^\\text{user} = \\ln \\sum_{i=1}^n \\text{transaction}_i^\\text{user}\n$$"},{"metadata":{"trusted":false,"_uuid":"ee389d648cc77ec7338a5584d7202d8dd652911a"},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"heading_collapsed":true,"_uuid":"70b518770183de5069a02eb500f4582a91b6493a"},"cell_type":"markdown","source":"## 相关有趣的知识点"},{"metadata":{"hidden":true,"_uuid":"9e904a577e26ccee29612c8f8868727cbbf2ba1b"},"cell_type":"markdown","source":"一种格式化输出"},{"metadata":{"hidden":true,"trusted":false,"_uuid":"4d52b1f4f8abd9abb762cbdd358ea1b46b3d6f05"},"cell_type":"code","source":"x = 2\nf'{x} is a number'","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"trusted":false,"_uuid":"e5dd9e664871bc6327f1ee875a1175ff2a5a394b"},"cell_type":"code","source":"x = 2\n'{} is a number'.format(x)","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"trusted":false,"_uuid":"4d44fecfc954a9be33bddd82a2936761b006c2a8"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"trusted":false,"_uuid":"4c6a3e66c5f7e7715eca291390195bacf0771ec2"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"heading_collapsed":true,"_uuid":"b87328919f28863cd323afa2a5b91e74af2bf011"},"cell_type":"markdown","source":"## 读入数据\n\n本数据有多个列包含不同深度的JSON blob。"},{"metadata":{"heading_collapsed":true,"hidden":true,"_uuid":"1fa761146a947b270f921d89df1db1fabf63b0f1"},"cell_type":"markdown","source":"### 直接 `pd.read_csv` 读入"},{"metadata":{"hidden":true,"scrolled":true,"trusted":false,"_uuid":"f8efd72b72dd39c3d4163c4620f872816e3f1302"},"cell_type":"code","source":"tmp = pd.read_csv('../input/train.csv', nrows=2)\nprint(tmp.shape)\ntmp","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"_uuid":"42f66336ba84a8ff6ba11bc13b103378ee7f0a6a"},"cell_type":"markdown","source":"总共12个变量：\n\n1. `channelGrouping` - 用户通过其进入商店的渠道。\n1. `date` - 用户访问Store的日期\n1. `device` - 用户访问 Store 的设备类型\n1. `fullVisitorId` - Google Merchandise Store的每个用户的唯一标识符。\n1. `geoNetwork` - 包含有关用户地理位置的信息。\n1. `sessionId` - 此次访问商店的唯一标识符。\n1. `socialEngagementType` - Engagement type, either \"Socially Engaged\" or \"Not Socially Engaged\".\n1. `totals` - This section contains aggregate values across the session.\n1. `trafficSource` - This section contains information about the Traffic Source from which the session originated.\n1. `visitId` - 此会话的标识符。这是通常存储为_utmb cookie的值的一部分。这仅对用户而言是唯一的。对于完全唯一的ID，您应该使用fullVisitorId和visitId的组合。\n1. `visitNumber` - 此用户的会话号。如果这是第一个会话，则将其设置为1。\n1. `visitStartTime` - 时间戳（表示为POSIX时间）。"},{"metadata":{"hidden":true,"trusted":false,"_uuid":"eb9f08fe872bae17fe2e4eb5c26c799e38e68c94"},"cell_type":"code","source":"tmp['device']","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"scrolled":true,"trusted":false,"_uuid":"681791b1685edfbe134a786adcf221bf303665f2"},"cell_type":"code","source":"# json_normalize(tmp['device']) # 此时会报错，因为读入的方式不对","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"_uuid":"701884510e3147b09402f86e07e0114465881b46"},"cell_type":"markdown","source":"下面讲解读入 `json` 文件！"},{"metadata":{"hidden":true,"scrolled":true,"trusted":false,"_uuid":"853107b9d2f4525c48d32b7ab97a116ac843b89a"},"cell_type":"code","source":"import json\nfrom pandas.io.json import json_normalize\n\ncsv_path = '../input/train.csv'\nJSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\nnrows = 2 \n\ndf = pd.read_csv(csv_path, \n converters={column: json.loads for column in JSON_COLUMNS}, \n dtype={'fullVisitorId': 'str'}, \n nrows=nrows)","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"scrolled":true,"trusted":false,"_uuid":"a777fa07b201796fe8197f609d13979fcaa20b09"},"cell_type":"code","source":"dc = json_normalize(df['device'])\ndc.columns = [f\"{column}.{subcolumn}\" for subcolumn in dc.columns]\ndc","execution_count":null,"outputs":[]},{"metadata":{"heading_collapsed":true,"hidden":true,"_uuid":"b7a72454c076c385eeb36900e3e0ed98d24c2f85"},"cell_type":"markdown","source":"### 读入成一个 dataframe"},{"metadata":{"hidden":true,"_uuid":"e2f51dbdb6e250e72d7741b8937a424806b13a96"},"cell_type":"markdown","source":"\n关于数据集：\n\n与大多数其他的kaggle比赛类似，我们给出了两个数据集\n\n train.csv\n test.csv\n\n数据集中的每一行都是对商店的一次访问。我们预测每个用户的所有交易总和的自然对数。\n\n需要注意的是，有多个列包含不同深度的JSON blob。在其中一个JSON列（`total`）中，子列 `transactionRevenue` 包含我们尝试预测的收入信息。"},{"metadata":{"code_folding":[4],"hidden":true,"trusted":false,"_uuid":"06beb709517520b5f942ca4ebaf6ec8cd20afa92"},"cell_type":"code","source":"import os\nimport json\nfrom pandas.io.json import json_normalize\n\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'}, \n nrows=nrows) # Important!!\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 print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n return df\n\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"scrolled":true,"trusted":false,"_uuid":"18e86763f95435908a0a9f94d1cb532ec636c098"},"cell_type":"code","source":"train = load_df(nrows=100000)\ntest =load_df('../input/test.csv', nrows=100000)\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2f31b82dbf175ceda96e7971e5e679c8413578ef"},"cell_type":"markdown","source":"## 数据基本处理"},{"metadata":{"_uuid":"b415411e95cfbf36aecfdd9a0c893396341a1f0c"},"cell_type":"markdown","source":"### 理解每个变量的含义\n\n总共12个变量：\n\n1. `channelGrouping` - 用户通过其进入商店的渠道。\n1. `date` - 用户访问Store的日期\n1. `device` - 用户访问 Store 的设备类型\n1. `fullVisitorId` - Google Merchandise Store的每个用户的唯一标识符。\n1. `geoNetwork` - 包含有关用户地理位置的信息。\n1. `sessionId` - 此次访问商店的唯一标识符。\n1. `socialEngagementType` - Engagement type, either \"Socially Engaged\" or \"Not Socially Engaged\".\n1. `totals` - This section contains aggregate values across the session.\n1. `trafficSource` - This section contains information about the Traffic Source from which the session originated.\n1. `visitId` - 此会话的标识符。这是通常存储为_utmb cookie的值的一部分。这仅对用户而言是唯一的。对于完全唯一的ID，您应该使用fullVisitorId和visitId的组合。\n1. `visitNumber` - 此用户的会话号。如果这是第一个会话，则将其设置为1。\n1. `visitStartTime` - 时间戳（表示为POSIX时间）。\n\n其中的四个变量 `device, geoNetwork, totals, trafficSource` 进一步处理成多个变量。"},{"metadata":{"scrolled":true,"trusted":false,"_uuid":"fda23f6b8cd26b5729afbe4fd75d9dd7cbd6fd49"},"cell_type":"code","source":"train.shape","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":false,"_uuid":"d824e41cd6e019d1b159909ed9142c1cdce853b0"},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"35808a4746903d9984983c587f7fd5fc5be4ab44"},"cell_type":"code","source":"train.tail()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"04366559ae58a96378a9442d7bf2b959d84abe81"},"cell_type":"markdown","source":"总共有54个特征, 包括 'channelGrouping', 'date', 'fullVisitorId', 'sessionId',\n 'socialEngagementType', 'visitId', 'visitNumber', 'visitStartTime',\n 'device.browser', 'device.browserSize', 'device.browserVersion',\n 'device.deviceCategory', 'device.flashVersion', 'device.isMobile',\n 'device.language', 'device.mobileDeviceBranding',\n 'device.mobileDeviceInfo', 'device.mobileDeviceMarketingName',\n 'device.mobileDeviceModel', 'device.mobileInputSelector',\n 'device.operatingSystem', 'device.operatingSystemVersion',\n 'device.screenColors', 'device.screenResolution', 'geoNetwork.city',\n 'geoNetwork.cityId', 'geoNetwork.continent', 'geoNetwork.country',\n 'geoNetwork.latitude', 'geoNetwork.longitude', 'geoNetwork.metro',\n 'geoNetwork.networkDomain', 'geoNetwork.networkLocation',\n 'geoNetwork.region', 'geoNetwork.subContinent', 'totals.bounces',\n 'totals.hits', 'totals.newVisits', 'totals.pageviews',\n 'totals.transactionRevenue', 'totals.visits', 'trafficSource.adContent',\n 'trafficSource.adwordsClickInfo.adNetworkType',\n 'trafficSource.adwordsClickInfo.criteriaParameters',\n 'trafficSource.adwordsClickInfo.gclId',\n 'trafficSource.adwordsClickInfo.isVideoAd',\n 'trafficSource.adwordsClickInfo.page',\n 'trafficSource.adwordsClickInfo.slot', 'trafficSource.campaign',\n 'trafficSource.isTrueDirect', 'trafficSource.keyword',\n 'trafficSource.medium', 'trafficSource.referralPath',\n 'trafficSource.source' "},{"metadata":{"_uuid":"eadff94231e1f62e0dd1459a1d3574391342bedc"},"cell_type":"markdown","source":"### Target variable"},{"metadata":{"_uuid":"b02c57eef72cf33c13adec4bba52540418808277"},"cell_type":"markdown","source":"\n我们预测每个用户的所有交易总和的自然对数。对于测试集中的每个用户，目标是：\n\n$$\n\\text{target}^\\text{user} = \\ln \\sum_{i=1}^n \\text{transaction}_i^\\text{user}\n$$\n\n看看进行散点图。"},{"metadata":{"trusted":false,"_uuid":"040a948ba761a7f45b5a1931c246b8797050e602"},"cell_type":"code","source":"np.log1p(0)","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":false,"_uuid":"75e1f2163c1c44584e6d8a09bfa6cc6e9dfd8eb8"},"cell_type":"code","source":"train[\"totals.transactionRevenue\"] = train[\"totals.transactionRevenue\"].astype('float')\ngdf = train.groupby(\"fullVisitorId\")[\"totals.transactionRevenue\"].sum().reset_index()\n\nplt.figure(figsize=(6,4))\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()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"3ec1932e61b44ddd2aa87e70988ae2a760319954"},"cell_type":"code","source":"gdf.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"46704b7f5d49de99c9a7fbf015009d70ab0055bd"},"cell_type":"markdown","source":"\n从中可以看出一个重要的点：\n\n*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":{"_uuid":"c02cecf6823ac18fa05444d5613cc680a1db09ea"},"cell_type":"markdown","source":"### full Vistior ld\n\n与客户 Id 相关的变量包括 `fullVisitorId, sessionId, visitId, visitNumber`，一个 `fullVisitorId` 包含多个 `sessionId`, 而一个 `sessionId` 唯一对应一个 `visitId`，个数和 `visitNumber` 相关。"},{"metadata":{"scrolled":true,"trusted":false,"_uuid":"00a70e164002e6c5fdd337e27c1f195eeddd22db"},"cell_type":"code","source":"ids = train[[\"fullVisitorId\", \"sessionId\", \"visitId\", \"visitNumber\"]]\nids.sort_values(by='sessionId')\nids.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":false,"_uuid":"9d39d368d9ee7c57cf3543a1f0e46c9133a1cc54"},"cell_type":"code","source":"ids['sessionId'].unique().shape, ids.shape, ids['fullVisitorId'].unique().shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"11780007a984d05dffa20493151688c82413d62e"},"cell_type":"code","source":"gong = ids.groupby(by='sessionId')['sessionId'].count()\ngong1 = gong.sort_values(ascending=False)[:7].index.values.tolist()\ngong2 = ids.loc[[i in gong1 for i in ids.sessionId], :].index\ngong3 = train.loc[gong2].sort_values(by='sessionId')\ngong3","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":false,"_uuid":"afa9dbfc8c43fd40a2e1c57a5140d3c978f3e2a9"},"cell_type":"code","source":"gong3.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"8983fe21354ffcfd76785a9a3ccf79841921be67"},"cell_type":"code","source":"gong3['date'].unique().shape[0] == 1","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":false,"_uuid":"956b73497ef0d9b1b5624e9c0a0a2b52ece87d90"},"cell_type":"code","source":"gong3[[c for c in gong3.columns if gong3[c].unique().shape[0] != 1]]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e2f5055da8f2361e9a25e9ec55c135fde9d51a89"},"cell_type":"markdown","source":"## 基本数据分析"},{"metadata":{"trusted":false,"_uuid":"7aaff3aa85cfea36e5bd6f480bac6c82a5ad729b"},"cell_type":"code","source":"nzi = pd.notnull(train[\"totals.transactionRevenue\"]).sum()\nnzr = (gdf[\"totals.transactionRevenue\"] > 0).sum()\nprint(\"Number of instances in train set with non-zero revenue : \", nzi, \" and ratio is : \", nzi / train.shape[0])\nprint(\"Number of unique customers with non-zero revenue : \", nzr, \"and the ratio is : \", nzr / gdf.shape[0])\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7d0d4e0d49f7363fc820ea499b02ac25d425f117"},"cell_type":"markdown","source":"访客人数和共同访客人数：\n\n现在让我们看一下训练和测试集中的不同访客数量，以及共同的访客的数量。"},{"metadata":{"trusted":false,"_uuid":"abcb93ae0796bf468acff42bcee6f06dd407a55a"},"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())) ))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"72573bb18f098be3af1d09dfd8d79fa065e28a9f"},"cell_type":"markdown","source":"查看具备有常数值的变量。"},{"metadata":{"scrolled":true,"trusted":false,"_uuid":"7221da1550cbf29bd03de1eda87f6454289f8df0"},"cell_type":"code","source":"[c for c in train.columns if train[c].nunique()==1]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f2302a858865d356fe40a4bfc53483561a804d90"},"cell_type":"markdown","source":"More to come ..."},{"metadata":{"trusted":false,"_uuid":"0e61d0568562fe748c356b6b51a6b6985569f9e3"},"cell_type":"code","source":" train.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"ad6f3a53f44a34475e5b224f535e496e605368a6"},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.6.5"},"toc":{"base_numbering":1,"nav_menu":{},"number_sections":true,"sideBar":true,"skip_h1_title":false,"title_cell":"Table of Contents","title_sidebar":"Contents","toc_cell":true,"toc_position":{"height":"calc(100% - 180px)","left":"10px","top":"150px","width":"305px"},"toc_section_display":true,"toc_window_display":true}},"nbformat":4,"nbformat_minor":1}