{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport json\nimport gc\nimport sys\nimport math\n\nfrom pandas.io.json import json_normalize\nfrom datetime import datetime\nfrom sklearn import preprocessing\n\nimport os\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e688678dc2a15c21b4ad76461cdbbe2983cdb54c"},"cell_type":"code","source":"def load_df(csv_path, 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    \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","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"scrolled":true},"cell_type":"code","source":"%%time\ntrain = load_df('../input/train.csv')\ntest = load_df('../input/test.csv')\n\nprint('train date:', min(train['date']), 'to', max(train['date']))\nprint('test date:', min(test['date']), 'to', max(test['date']))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3a4cd6ceececc6736eed2ca310037ca59df30563"},"cell_type":"code","source":"# only train feature\nfor c in train.columns.values:\n    if c not in test.columns.values: print(c)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"92a8e510b31fc08078cadc85836b8807ea12ef51","scrolled":true},"cell_type":"code","source":"train['totals.transactionRevenue'].fillna(0, inplace=True)\ntrain['totals.transactionRevenue'] = np.log1p(train['totals.transactionRevenue'].astype(float))\nprint(train['totals.transactionRevenue'].describe())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"39e9e2840995b2a4d084b2a23644028622451e79"},"cell_type":"markdown","source":"# "},{"metadata":{"trusted":true,"_uuid":"02c53418bb112ed272f7282d3e1e4b3228ec3f39"},"cell_type":"code","source":"all_data = train.append(test, sort=False).reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"21e9028c7ddbec508a817e3253b3dcfccf0a6f8b"},"cell_type":"code","source":"print(all_data.info())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7af501629ee5749241ced88cfca154c28e6b93ef"},"cell_type":"code","source":"all_data.count()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"399fa62cd89b459ab4c700ae20c118e7816e6a34"},"cell_type":"code","source":"all_data.isnull().sum()   #null개수 확인","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1496133261c87e994d3c70302367423110b25195"},"cell_type":"code","source":"x='channelGrouping'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2d4f22ee3a64e269fabb80b255b103b617b21fee","_kg_hide-output":false,"_kg_hide-input":false},"cell_type":"code","source":"x='date'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"809981a661e8c9dea960f486ef35df2a10aa72be"},"cell_type":"code","source":"x='fullVisitorId'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c52f5a96836cd1399a44e4c5c8230790b9c28ae8"},"cell_type":"code","source":"x='sessionId'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"93dd9e464d8258353c39459df7b5814a71c97e09"},"cell_type":"code","source":"x='socialEngagementType'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"75aaf654cec6c17c87a6cb02b4538ca7d31ef2bd"},"cell_type":"code","source":"x='visitId'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1a9a837138eeb8f39e8d90011bf71ebd39f25d56"},"cell_type":"code","source":"x='visitNumber'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c5873db634a7ce28fd8dfa7dc1167ec2a809aef2"},"cell_type":"code","source":"x='visitStartTime'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1f8368cd9c4336ea9d39fca817d25c024b05f0ec"},"cell_type":"code","source":"x='device.browser'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"480380475d7918ce26988b0afc051d93df735f0f"},"cell_type":"code","source":"x='device.browserSize'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bdb5a1bc0af95a11e5839771313d3a18e11ce845"},"cell_type":"code","source":"x='device.browserVersion'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"71c65323dfc4db9c4896046f0e7908a33037e197"},"cell_type":"code","source":"x='device.deviceCategory'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"260a830645d2b9ead81d769d38201aa7d317f5bb"},"cell_type":"code","source":"x='device.flashVersion'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f128eb92b7760fc44444d7ac38835c45faf22660"},"cell_type":"code","source":"x='device.isMobile'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"28331f2d1f4122bf91eba60480c4c4af35b9f9ef"},"cell_type":"code","source":"x='device.language'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fdcde0daa32b5d53c3f6a20d34c424357b0f7e70"},"cell_type":"code","source":"x='device.mobileDeviceBranding'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dd07540498fa94d2c8e6104a9d69678faedc480a"},"cell_type":"code","source":"x='device.mobileDeviceInfo'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5a22c6508325ab52ed77add0556a8f8942ba870f"},"cell_type":"code","source":"x='device.mobileDeviceMarketingName'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f0dcfe14195ffc5cbd91ff99e480790ee2307518"},"cell_type":"code","source":"x='device.mobileDeviceModel'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"80d74f05c2a14d9da3a0659893ad9f36190b72a7"},"cell_type":"code","source":"x='device.mobileInputSelector'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dd34c81930e97ff3a9389dfa2c2190e4274bd928"},"cell_type":"code","source":"x='device.operatingSystem'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5ac8954f4ccfe14c9466bec5567300104a8c082b"},"cell_type":"code","source":"x='device.operatingSystemVersion'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7e5c714be80c6b3d94a56554110e7b12fd1e7019"},"cell_type":"code","source":"x='device.screenColors'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e231101eae4f856b08f76b8c1e403da8d1a4c20f"},"cell_type":"code","source":"x='device.screenResolution'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b3465b0ace7ede46d0365863af0f8d918cc23aa8"},"cell_type":"code","source":"x='geoNetwork.city'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"99da14a915295979fabcaea527f5d5c0d5bb5076"},"cell_type":"code","source":"x='geoNetwork.cityId'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f8887ccc1bef04a5168d56b755cd2701083fdada"},"cell_type":"code","source":"x='geoNetwork.continent'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"85426a229d720aa2245c66ed566921fdf479dee0"},"cell_type":"code","source":"x='geoNetwork.country'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3ccea68317a673723d54b23191caf8f51ffdebb0"},"cell_type":"code","source":"x='geoNetwork.latitude'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d3cb815de0f0e092ef20f816f78c335a074ab920"},"cell_type":"code","source":"x='geoNetwork.longitude'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a8aab51afcf24e4a94029c43e0a4c16d3d678405"},"cell_type":"code","source":"x='geoNetwork.metro'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"de6ff6c0ae27c893843c01b05f8f5f4ec6be23a6"},"cell_type":"code","source":"x='geoNetwork.networkDomain'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"272819954929c955e3978ea2ad9e6582bafb7509"},"cell_type":"code","source":"x='geoNetwork.networkLocation'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a94eb9c52eec669b5799eca90c03630ff8897122"},"cell_type":"code","source":"x='geoNetwork.region'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1cbb94036752ce93912dc244d1c5eb33d27bec6e"},"cell_type":"code","source":"x='geoNetwork.subContinent'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1aa8b3f237321c5f5fac952779325591f3232ca5"},"cell_type":"code","source":"x='totals.bounces'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"46e1ef067586b721388ea306af39d47545b9f681"},"cell_type":"code","source":"x='totals.hits'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b3afe82cc044a923e5be533e759aeda4ed452c2f"},"cell_type":"code","source":"x='totals.newVisits'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9264ac2d3cc7029f014d1fdfa5a0bf74c9f21c89"},"cell_type":"code","source":"x='totals.pageviews'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cfba2a74511dc6cab0c62e760c278cdb417aaf07"},"cell_type":"code","source":"x='totals.transactionRevenue'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0512be1e96d8a9a49372191876606b34340202a5"},"cell_type":"code","source":"x='totals.visits'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"68ed9dee90fb141fc35f1f549946d4d2d7c12554"},"cell_type":"code","source":"x='trafficSource.adContent'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"254880571812a77e6207ebf4f1ca59d1a19e0d32"},"cell_type":"code","source":"x='trafficSource.adwordsClickInfo.adNetworkType'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"048cb21ededd967b563faf4c1404e0e4b75d8589"},"cell_type":"code","source":"x='trafficSource.adwordsClickInfo.criteriaParameters'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"e8621606a17eadc3816c0b8e427658eed9d47e46"},"cell_type":"code","source":"x='trafficSource.adwordsClickInfo.gclId'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"23cd727102e11a19daa2779c513faf90ae90ad3a"},"cell_type":"code","source":"x='trafficSource.adwordsClickInfo.isVideoAd'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4acfe9a1a8129c94395be4cf471189869e29e6f9"},"cell_type":"code","source":"x='trafficSource.adwordsClickInfo.page'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1f28b3aa2e70f2e6b225159bee9e1251641bcf07"},"cell_type":"code","source":"x='trafficSource.adwordsClickInfo.slot'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"23e0d73521052bd15e22458b352536f1696916e3"},"cell_type":"code","source":"x='trafficSource.campaign'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"37cf80cc4db03a89fe3d7b867ebd2032c61c1837"},"cell_type":"code","source":"x='trafficSource.campaignCode'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"73edc2295fa27fc13174f1249410a30d2ca85b18"},"cell_type":"code","source":"x='trafficSource.isTrueDirect'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4a8b4f234de4a334f2bb80de0a3ac974fd3101d1"},"cell_type":"code","source":"x='trafficSource.keyword'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"650e3a6c924dca0c0276ddaf652d00777629a0a0"},"cell_type":"code","source":"x='trafficSource.medium'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8fe0850b3d89bc1c468cf11c34d076828bca0b1b"},"cell_type":"code","source":"x='trafficSource.referralPath'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bc99d40c7b4d2a34914ba11ab5a8e4b822fb3e94"},"cell_type":"code","source":"x='trafficSource.source'\nall_data[x].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"69f008c8b1d10e4b28945b97be085b43c290cc27"},"cell_type":"markdown","source":"null_cnt = train.isnull().sum().sort_values()\nprint(null_cnt[null_cnt > 0])"},{"metadata":{"trusted":true,"_uuid":"f3134948730860dbe84e9a3672b5398c437b791f"},"cell_type":"markdown","source":"# fillna object feature\nfor col in ['trafficSource.keyword',\n            'trafficSource.referralPath',\n            'trafficSource.adwordsClickInfo.gclId',\n            'trafficSource.adwordsClickInfo.adNetworkType',\n            'trafficSource.adwordsClickInfo.isVideoAd',\n            'trafficSource.adwordsClickInfo.page',\n            'trafficSource.adwordsClickInfo.slot',\n            'trafficSource.adContent']:\n    all_data[col].fillna('unknown', inplace=True)\n\n# fillna numeric feature\nall_data['totals.pageviews'].fillna(1, inplace=True)\nall_data['totals.newVisits'].fillna(0, inplace=True)\nall_data['totals.bounces'].fillna(0, inplace=True)\nall_data['totals.pageviews'] = all_data['totals.pageviews'].astype(int)\nall_data['totals.newVisits'] = all_data['totals.newVisits'].astype(int)\nall_data['totals.bounces'] = all_data['totals.bounces'].astype(int)\n\n# fillna boolean feature\nall_data['trafficSource.isTrueDirect'].fillna(False, inplace=True)"},{"metadata":{"_uuid":"2b36b530124dc3e4af615996f5870e71030ca7e3"},"cell_type":"markdown","source":"**DROP"},{"metadata":{"trusted":true,"_uuid":"e93201ab3ceec116e1e66c64e8cb8b13019d96f6"},"cell_type":"markdown","source":"# drop constant column\nconstant_column = [col for col in all_data.columns if all_data[col].nunique() == 1]\n#for c in constant_column:\n#    print(c + ':', train[c].unique())\n\nprint('drop columns:', constant_column)\nall_data.drop(constant_column, axis=1, inplace=True)"},{"metadata":{"trusted":true,"_uuid":"2670f11b9a006b470dce47ab5c724a295bdee544"},"cell_type":"markdown","source":"all_data.columns\n"},{"metadata":{"trusted":true,"_uuid":"e607e6782bbbaa79a7cfbaa79364223a899fe94c"},"cell_type":"markdown","source":"# pickup any visitor\nall_data[all_data['fullVisitorId'] == '7813149961404844386'].sort_values(by='visitNumber')[\n    ['date','visitId','visitNumber','totals.hits','totals.pageviews']].head(20)"},{"metadata":{"trusted":true,"_uuid":"a8f4e841406d2babd6e8c3429dc0f9221a2f58ad"},"cell_type":"markdown","source":"train_rev = all_data[all_data['totals.transactionRevenue'] > 0].copy()\nprint(len(train_rev))\ntrain_rev.head()"},{"metadata":{"trusted":true,"_uuid":"a4558c13d3270bd5e12a8abae94dc144194d4cd0"},"cell_type":"markdown","source":"def plotCategoryRateBar(a, b, colName, topN=np.nan):\n    if topN == topN: # isNotNan\n        vals = b[colName].value_counts()[:topN]\n        subA = a.loc[a[colName].isin(vals.index.values), colName]\n        df = pd.DataFrame({'All':subA.value_counts() / len(a), 'Revenue':vals / len(b)})\n    else:\n        df = pd.DataFrame({'All':a[colName].value_counts() / len(a), 'Revenue':b[colName].value_counts() / len(b)})\n    df.sort_values('Revenue').plot.barh()"},{"metadata":{"_uuid":"df1597ce637e40c82ddbce0567f1aca6d44010d5"},"cell_type":"markdown","source":"## date"},{"metadata":{"trusted":true,"_uuid":"7f62796c68e95615311e28ec061fc76c0877e967"},"cell_type":"markdown","source":"format_str = '%Y%m%d'\nall_data['formated_date'] = all_data['date'].apply(lambda x: datetime.strptime(str(x), format_str))\nall_data['_year'] = all_data['formated_date'].apply(lambda x:x.year)\nall_data['_month'] = all_data['formated_date'].apply(lambda x:x.month)\nall_data['_quarterMonth'] = all_data['formated_date'].apply(lambda x:x.day//8)\nall_data['_day'] = all_data['formated_date'].apply(lambda x:x.day)\nall_data['_weekday'] = all_data['formated_date'].apply(lambda x:x.weekday())\n\nall_data.drop(['date','formated_date'], axis=1, inplace=True)"},{"metadata":{"_uuid":"67f4ef3a6889ca30fdc15847fda4f21de1a9dc21"},"cell_type":"markdown","source":"## channelGrouping\n* The channel via which the user came to the Store."},{"metadata":{"trusted":true,"_uuid":"dcd7482789b1e78f42cc63a442fd9b165ca2c351"},"cell_type":"markdown","source":"plotCategoryRateBar(all_data, train_rev, 'channelGrouping')"},{"metadata":{"_uuid":"62571c594360b5ffdb8eed4fbea60d4307de9ae4"},"cell_type":"markdown","source":"## fullVisitorId\n* A unique identifier for each user of the Google Merchandise Store.\n\n## visitId\n* An identifier for this session. This is part of the value usually stored as the _utmb cookie. This is only unique to the user.   \nFor a completely unique ID, you should use a combination of fullVisitorId and visitId.\n\n## newVisits\n"},{"metadata":{"trusted":true,"_uuid":"e8799d0686a4caa28107145884221bdfaa1412ce"},"cell_type":"markdown","source":"print('train all:', len(train))\nprint('train unique fullVisitorId:', train['fullVisitorId'].nunique())\nprint('train unique visitId:', train['visitId'].nunique())\nprint('-' * 30)\nprint('test all:', len(test))\nprint('test unique fullVisitorId:', test['fullVisitorId'].nunique())\nprint('test unique visitId:', test['visitId'].nunique())\n\n#print('common fullVisitorId:', len(pd.merge(train, test, how='inner', on='fullVisitorId'))) # 183434"},{"metadata":{"trusted":true,"_uuid":"47d962d3eb9f8674f7dc2a457877faab6cbaa916"},"cell_type":"markdown","source":"print(all_data['visitNumber'].value_counts()[:5])\nprint('-' * 30)\nprint(all_data['totals.newVisits'].value_counts())\nprint('-' * 30)\nprint(all_data['totals.bounces'].value_counts())"},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"b8b2c483edf2c431d7d8c1615bd7f14b57e4e149"},"cell_type":"code","source":"#maxVisitNumber = max(all_data['visitNumber'])\n#fvid = all_data[all_data['visitNumber'] == maxVisitNumber]['fullVisitorId']\n#all_data[all_data['fullVisitorId'] == fvid.values[0]].sort_values(by='visitNumber')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d17c15ad90f60602af11d19a9cf1c8ca8c7abb98"},"cell_type":"markdown","source":"all_data['_visitStartHour'] = all_data['visitStartTime'].apply(\n    lambda x: str(datetime.fromtimestamp(x).hour))"},{"metadata":{"_uuid":"8be250dbce52564a55cda2f225e247039921fe27"},"cell_type":"markdown","source":"## sessionId\n*  A unique identifier for this visit to the store."},{"metadata":{"trusted":true,"_uuid":"eca53b9445ae8fc90f07f741a6b664e9d5101a10"},"cell_type":"markdown","source":"print('train all sessionId:', len(train['sessionId']))\nprint('train unique sessionId:', train['sessionId'].nunique())"},{"metadata":{"_uuid":"5fdfa1c4bc5ecbbdb841ab4dad42f1e8144f190e"},"cell_type":"markdown","source":"## socialEngagementType\n* Engagement type, either \"Socially Engaged\" or \"Not Socially Engaged\"."},{"metadata":{"trusted":true,"_uuid":"07a97995e15e5f3bd321a303d159ad3b941d1017"},"cell_type":"code","source":"#all_data['socialEngagementType'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a6b79c64d4c75031a9cd6d04abeff9eaa9648b25"},"cell_type":"markdown","source":"## device"},{"metadata":{"trusted":true,"_uuid":"94893bde8a5f5f2cdd5fe7757eab49f252753adb"},"cell_type":"markdown","source":"print('unique browser count:', train['device.browser'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'device.browser', 10)"},{"metadata":{"trusted":true,"_uuid":"6103fcf3bcc3f70eec8a93d91551ec134b122342"},"cell_type":"markdown","source":"pd.crosstab(all_data['device.deviceCategory'], all_data['device.isMobile'], margins=False)"},{"metadata":{"trusted":true,"_uuid":"7b86e92f7c79484da9fa08233f91e01e5d363fb0"},"cell_type":"markdown","source":"#pd.crosstab(train_rev['device.deviceCategory'], train_rev['device.isMobile'], margins=False)\n\nall_data['isMobile'] = True\nall_data.loc[all_data['device.deviceCategory'] == 'desktop', 'isMobile'] = False"},{"metadata":{"trusted":true,"_uuid":"d8df08c75a5c8ac8f4771805d09d96b83362f3ae"},"cell_type":"markdown","source":"print('unique operatingSystem count:', train['device.operatingSystem'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'device.operatingSystem', 10)"},{"metadata":{"_uuid":"d33b8252e73c702753086bb7c0a889f1a7922a9e"},"cell_type":"markdown","source":"## geoNetwork"},{"metadata":{"trusted":true,"_uuid":"3b1eaa2069013d4a1c19b80516e159a536dc6d8d"},"cell_type":"markdown","source":"print('unique geoNetwork.city count:', train['geoNetwork.city'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'geoNetwork.city', 10)"},{"metadata":{"trusted":true,"_uuid":"4f44c8d97db856c51d763f5212aa3c98698130e3"},"cell_type":"markdown","source":"print('unique geoNetwork.region count:', train['geoNetwork.region'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'geoNetwork.region', 10)"},{"metadata":{"trusted":true,"_uuid":"95c2c106c2f546e66c3d55c8cb791fffdbc10107"},"cell_type":"markdown","source":"print('unique geoNetwork.subContinent count:', train['geoNetwork.subContinent'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'geoNetwork.subContinent', 10)"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"0464279e4253c4b89ba7bfb085f7049ed85bc128"},"cell_type":"markdown","source":"plotCategoryRateBar(all_data, train_rev, 'geoNetwork.continent')"},{"metadata":{"trusted":true,"_uuid":"084e17c130b2a0db036b9024d16440202b391bf6"},"cell_type":"markdown","source":"print('unique geoNetwork.metro count:', train['geoNetwork.metro'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'geoNetwork.metro', 10)"},{"metadata":{"trusted":true,"_uuid":"2c5ae32c4cd8953a6f722ff66a72e6266a057d17","scrolled":false},"cell_type":"markdown","source":"print('unique geoNetwork.networkDomain count:', train['geoNetwork.networkDomain'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'geoNetwork.networkDomain', 10)"},{"metadata":{"_uuid":"88469fecbc877ce38ea430e1cfd5e5ab998033e5"},"cell_type":"markdown","source":"## totals"},{"metadata":{"trusted":true,"_uuid":"82570f747376d5688ce66ff788d6687b5d92447b","scrolled":true},"cell_type":"markdown","source":"print(all_data['totals.hits'].value_counts()[:10])\n\nall_data['totals.hits'] = all_data['totals.hits'].astype(int)"},{"metadata":{"trusted":true,"_uuid":"d4b608e99f2ed2e8c2732ec7aabf3b24335e5ad8"},"cell_type":"markdown","source":"print(all_data['totals.pageviews'].value_counts()[:10])\n\nall_data['totals.pageviews'] = all_data['totals.pageviews'].astype(int)"},{"metadata":{"trusted":true,"_uuid":"1ed36a972f984806f846c77a44cdc7aa5285ab5b"},"cell_type":"code","source":"#print(all_data['totals.visits'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6bff69f27cc25158cf9aa565173d8aec5a6290ee"},"cell_type":"markdown","source":"## trafficSource"},{"metadata":{"trusted":true,"_uuid":"27776645f0f26a7cdd4ff84334df20d7451c37ae"},"cell_type":"markdown","source":"print('unique trafficSource.adContent count:', train['trafficSource.adContent'].nunique())\n\nplotCategoryRateBar(all_data, train_rev, 'trafficSource.adContent', 10)\n\nall_data['_adContentGMC'] = (all_data['trafficSource.adContent'] == 'Google Merchandise Collection').astype(np.uint8)"},{"metadata":{"trusted":true,"_uuid":"216ff4ed12ecb7543b1cb0a71582f580cfc1becc","scrolled":false},"cell_type":"markdown","source":"print('unique trafficSource.campaign count:', train['trafficSource.campaign'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'trafficSource.campaign', 10)\n\nall_data['_withCampaign'] = (all_data['trafficSource.campaign'] != '(not set)').astype(np.uint8)"},{"metadata":{"trusted":true,"_uuid":"336f80e51b483f7fa8cf01b9598b4ad9c2cdaa01"},"cell_type":"code","source":"#print(all_data['campaignCode'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8ddc105a27a0647205e91113d1e6744856756848"},"cell_type":"markdown","source":"print(all_data['trafficSource.isTrueDirect'].value_counts())\nplotCategoryRateBar(all_data, train_rev, 'trafficSource.isTrueDirect')"},{"metadata":{"trusted":true,"_uuid":"7492ccdd02d468d1e7d9df57dfd3d09e5f4c2ed5"},"cell_type":"markdown","source":"print('unique trafficSource.keyword count:', train['trafficSource.keyword'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'trafficSource.keyword', 10)"},{"metadata":{"trusted":true,"_uuid":"8cc6336da483573190d7bbfc039d0aae3afc26a7"},"cell_type":"markdown","source":"print('unique trafficSource.medium count:', train['trafficSource.medium'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'trafficSource.medium')"},{"metadata":{"trusted":true,"_uuid":"68bbc2cf706edc7878ec79ef18a50ce39d5f6985","scrolled":true},"cell_type":"markdown","source":"print('unique trafficSource.referralPath count:', train['trafficSource.referralPath'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'trafficSource.referralPath', 10)\n\nall_data['_referralRoot'] = (all_data['trafficSource.referralPath'] == '/').astype(np.uint8)"},{"metadata":{"trusted":true,"_uuid":"921c55633ac9c113521b33611564762bc8090919"},"cell_type":"markdown","source":"print('unique trafficSource.source count:', train['trafficSource.source'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'trafficSource.source', 10)\n\nall_data['_sourceGpmall'] = (all_data['trafficSource.source'] == 'mall.googleplex.com').astype(np.uint8)"},{"metadata":{"_uuid":"7a0eef047ac62a8ab52abb174de2e84dd6150ed9"},"cell_type":"markdown","source":"## Aggregate"},{"metadata":{"trusted":true,"_uuid":"2dc1702ba3ecfc3c1e9b439fc9ba5f4a53b12ecb"},"cell_type":"markdown","source":"_='''\n'''\nall_data['_meanHitsPerDay'] = all_data.groupby(['_day'])['totals.hits'].transform('mean')\nall_data['_meanHitsPerWeekday'] = all_data.groupby(['_weekday'])['totals.hits'].transform('mean')\nall_data['_meanHitsPerMonth'] = all_data.groupby(['_month'])['totals.hits'].transform('mean')\nall_data['_sumHitsPerDay'] = all_data.groupby(['_day'])['totals.hits'].transform('sum')\nall_data['_sumHitsPerWeekday'] = all_data.groupby(['_weekday'])['totals.hits'].transform('sum')\nall_data['_sumHitsPerMonth'] = all_data.groupby(['_month'])['totals.hits'].transform('sum')\n\nfor feature in ['totals.hits', 'totals.pageviews']:\n    info = all_data.groupby('fullVisitorId')[feature].mean()\n    all_data['_usermean_' + feature] = all_data.fullVisitorId.map(info)\n    \nfor feature in [\"visitNumber\"]:\n    info = all_data.groupby('fullVisitorId')[feature].max()\n    all_data['_usermax_' + feature] = all_data.fullVisitorId.map(info)"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"4a0ab9f364cae6bb9f7db738fd2b4f320230db17"},"cell_type":"markdown","source":"_='''\ntrain_rev = train_rev.sort_values(['visitStartTime']).reset_index()\ntrain_rev['_buyCount'] = train_rev.groupby('fullVisitorId').cumcount() + 1\nall_data = pd.merge(all_data, train_rev[['_buyCount','fullVisitorId','visitId']], \n                    on=['fullVisitorId','visitId'], how='left')\nfor fvId in train_rev['fullVisitorId'].unique():\n    visitor_data = all_data[all_data['fullVisitorId'] == fvId].sort_values(['visitStartTime'])['_buyCount'].reset_index()\n    all_data.loc[all_data['fullVisitorId'] == fvId, '_buyCount'] = visitor_data['_buyCount'].fillna(method='ffill').values\nall_data['_buyCount'].fillna(0, inplace=True)\nall_data['_buyRate'] = all_data['_buyCount'] / all_data['visitNumber']\n'''"},{"metadata":{"_uuid":"7415c5915b8dd8fbe1c512f8f249ab548ce85c19"},"cell_type":"markdown","source":"## Select feature"},{"metadata":{"trusted":true,"_uuid":"19788577f9cf3b3bb98c7d229c82bd3099225187"},"cell_type":"markdown","source":"null_cnt = all_data.isnull().sum().sort_values()\nprint(null_cnt[null_cnt > 0])"},{"metadata":{"trusted":true,"_uuid":"221c4d5f9bd5349e70c6af79d830356f7ab79428"},"cell_type":"markdown","source":"all_data.drop([\n    'sessionId','visitId','visitStartTime',\n    'trafficSource.adwordsClickInfo.adNetworkType',\n    'trafficSource.adwordsClickInfo.gclId',\n    'trafficSource.adwordsClickInfo.page',\n    'trafficSource.adwordsClickInfo.slot'],axis=1,inplace=True)\n\nfor i, t in all_data.loc[:, all_data.columns != 'fullVisitorId'].dtypes.iteritems():\n    if t == object:\n        all_data[i].fillna('unknown', inplace=True)\n        all_data[i] = pd.factorize(all_data[i])[0]\n        #all_data[i] = all_data[i].astype('category')"},{"metadata":{"_uuid":"c74fb04f46aeff7873b9d99e78a8bc030cd25340"},"cell_type":"markdown","source":"# Prediction"},{"metadata":{"trusted":true,"_uuid":"b56f51768a55937e8a7c9580fe7f91b8c0cbd6ac"},"cell_type":"markdown","source":"all_data.info()"},{"metadata":{"trusted":true,"_uuid":"f6c50f06b9c73de1dc6dd7a41e5221f7130224ad"},"cell_type":"markdown","source":"train = all_data[all_data['totals.transactionRevenue'].notnull()]\ntest = all_data[all_data['totals.transactionRevenue'].isnull()].drop(['totals.transactionRevenue'], axis=1)"},{"metadata":{"trusted":true,"_uuid":"684fdd483abd0da7de1c74b504ddb32a25b5c39b"},"cell_type":"markdown","source":"train_id = train['fullVisitorId']\ntest_id = test['fullVisitorId']\n\nY_train_reg = train.pop('totals.transactionRevenue')\nY_train_cls = (Y_train_reg.fillna(0) > 0).astype(np.uint8)\nX_train = train.drop(['fullVisitorId'], axis=1)\nX_test  = test.drop(['fullVisitorId'], axis=1)\n\nprint(X_train.shape, X_test.shape)"},{"metadata":{"trusted":true,"_uuid":"e4cdae49bc3394c1766bb70dc182bc5140fe068c"},"cell_type":"markdown","source":"import sys\nimport gc\n\ndel all_data, train, test, train_rev\ngc.collect()\n\nprint(pd.DataFrame([[val for val in dir()], [sys.getsizeof(eval(val)) for val in dir()]],\n                   index=['name','size']).T.sort_values('size', ascending=False).reset_index(drop=True)[:10])"},{"metadata":{"trusted":true,"_uuid":"6357c37006d1433d710fd56aee8717fa1ae2efd3"},"cell_type":"markdown","source":"from sklearn.model_selection import StratifiedKFold\nfrom sklearn.metrics import roc_auc_score\nimport lightgbm as lgb"},{"metadata":{"trusted":true,"_uuid":"c5b68807cb7ca44624f5f1d30e6bd9fd7883de29"},"cell_type":"markdown","source":"%%time\n\nparams={'learning_rate': 0.01,\n        'objective':'regression',\n        'metric':'rmse',\n        'num_leaves': 64,\n        'bagging_freq': 3,\n        'bagging_fraction': 1,\n        'feature_fraction': 1,\n        'max_depth':-1,\n        'subsample_for_bin':50000,\n        'random_state':42\n       }\n'''\nreg = lgb.LGBMRegressor(**params, n_estimators=3000)\nreg.fit(X_train, Y_train_cls, verbose=100)\npred_reg = reg.predict(X_test, num_iteration=reg.best_iteration_)\n'''\nfolds = StratifiedKFold(n_splits=5, shuffle=True, random_state=42)\n\noof_preds = np.zeros(X_train.shape[0])\nsub_preds = np.zeros(X_test.shape[0])\nfor fold_, (trn_, val_) in enumerate(folds.split(X_train, Y_train_cls)):\n    trn_x, trn_y = X_train.iloc[trn_], Y_train_cls.iloc[trn_]\n    val_x, val_y = X_train.iloc[val_], Y_train_cls.iloc[val_]\n    \n    reg = lgb.LGBMRegressor(**params, n_estimators=3000)\n    reg.fit(trn_x, trn_y, eval_set=[(val_x, val_y)], early_stopping_rounds=50, verbose=500)\n    \n    oof_preds[val_] = reg.predict(val_x, num_iteration=reg.best_iteration_)\n    print(roc_auc_score(val_y, oof_preds[val_]))\n    sub_preds += reg.predict(X_test, num_iteration=reg.best_iteration_) / folds.n_splits\n\npred_reg = sub_preds\nprint(len(pred_reg), len(pred_reg[pred_reg > 0.1]))"},{"metadata":{"trusted":true,"_uuid":"ec7016a42f68fdae8db998d33e04c4913a08e6b1"},"cell_type":"markdown","source":"# Plot feature importance\nfeature_importance = reg.feature_importances_[:20]\nfeature_importance = 100.0 * (feature_importance / feature_importance.max())\nsorted_idx = np.argsort(feature_importance)\npos = np.arange(sorted_idx.shape[0]) + .5\n\nplt.figure(figsize=(12,6))\nplt.barh(pos, feature_importance[sorted_idx], align='center')\nplt.yticks(pos, X_train.columns[sorted_idx])\nplt.xlabel('Relative Importance')\nplt.title('Variable Importance')\nplt.show()"},{"metadata":{"trusted":true,"_uuid":"e544aab2bc7716ba32a23b2c3c0977c01844e2bd"},"cell_type":"markdown","source":"%%time\n\nparams={'learning_rate': 0.03,\n        'objective':'regression',\n        'metric':'rmse',\n        'num_leaves': 64,\n        #'bagging_freq': 3,\n        #'bagging_fraction': 0.6,\n        'feature_fraction': 1,\n        'max_depth':-1,\n        'random_state':42\n       }\nreg = lgb.LGBMRegressor(**params, n_estimators=1000)\nreg.fit(X_train[Y_train_reg > 0], Y_train_reg[Y_train_reg > 0])\n\npred = np.zeros(len(pred_reg))\nfor i in np.arange(len(pred_reg)):\n    pred[i] = reg.predict([X_test.iloc[i]])[0] * pred_reg[i]"},{"metadata":{"trusted":true,"_uuid":"ce46c06204ebe113492b0bb018ca5513a8982dec"},"cell_type":"markdown","source":"submission = pd.DataFrame({'fullVisitorId':test_id, 'PredictedLogRevenue':pred})\nsubmission[\"PredictedLogRevenue\"] = submission[\"PredictedLogRevenue\"].apply(lambda x : 0.0 if x < 0 else x)\nsubmission[\"PredictedLogRevenue\"] = submission[\"PredictedLogRevenue\"].fillna(0.0)\nsubmission_sum = submission[['fullVisitorId', 'PredictedLogRevenue']].groupby('fullVisitorId').sum().reset_index()\nsubmission_sum.to_csv(\"submission.csv\", index=False)\nsubmission_sum.head(20)"},{"metadata":{"trusted":true,"_uuid":"d4eca9c014bd3f1e0d02f9fbd39239f2be9c4628"},"cell_type":"markdown","source":"submission_sum['PredictedLogRevenue'].describe()"},{"metadata":{"trusted":true,"_uuid":"0f736b18d8d2fdfaa197c81b68f787e6cea573c6"},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"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}