{"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\n\nimport os\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e688678dc2a15c21b4ad76461cdbbe2983cdb54c"},"cell_type":"code","source":"gc.enable()\n\nfeatures = ['channelGrouping', 'date', 'fullVisitorId', 'visitId',\\\n       'visitNumber', 'visitStartTime', 'device.browser',\\\n       'device.deviceCategory', 'device.isMobile', 'device.operatingSystem',\\\n       'geoNetwork.city', 'geoNetwork.continent', 'geoNetwork.country',\\\n       'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region',\\\n       'geoNetwork.subContinent', 'totals.bounces', 'totals.hits',\\\n       'totals.newVisits', 'totals.pageviews', 'totals.transactionRevenue',\\\n       'trafficSource.adContent', 'trafficSource.campaign',\\\n       'trafficSource.isTrueDirect', 'trafficSource.keyword',\\\n       'trafficSource.medium', 'trafficSource.referralPath',\\\n       'trafficSource.source']\n\ndef load_df(csv_path):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    ans = pd.DataFrame()\n    dfs = pd.read_csv(csv_path, sep=',',\n            converters={column: json.loads for column in JSON_COLUMNS}, \n            dtype={'fullVisitorId': 'str'}, # Important!!\n            chunksize=200000)\n    for df in dfs:\n        df.reset_index(drop=True, inplace=True)\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\n        #print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n        use_df = df[features]\n        del df\n        gc.collect()\n        ans = pd.concat([ans, use_df], axis=0).reset_index(drop=True)\n        #print(ans.shape)\n    return ans","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data = [{'id': 1, 'name': {'first': 'Coleen', 'last': 'Volk'}},\n        {'name': {'given': 'Mose', 'family': 'Regner'}},\n        {'id': 2, 'name': 'Faye Raker'}]\n\njson_normalize(data)","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')\n# test = load_df('../input/test.csv')\n\nprint('train date:', min(train['date']), 'to', max(train['date']))\n# print('test date:', min(test['date']), 'to', max(test['date']))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train['totals.transactionRevenue'].isnull().value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"838393362ad3cf67afe056b0a27cb11926077220"},"cell_type":"code","source":"# Thanks and credited to https://www.kaggle.com/gemartin\ndef reduce_mem_usage(df):\n    \"\"\" iterate through all the columns of a dataframe and modify the data type\n        to reduce memory usage.        \n    \"\"\"\n    start_mem = df.memory_usage().sum() \n    print('Memory usage of dataframe is {:.2f} MB'.format(start_mem))\n    \n    for col in df.columns:\n        col_type = df[col].dtype\n        \n        if col_type != object:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)\n        #else:\n            #df[col] = df[col].astype('category')\n\n    end_mem = df.memory_usage().sum() \n    print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))\n    print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n    return df\n\ntrain = reduce_mem_usage(train)\n# test = reduce_mem_usage(test)","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},"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":{"trusted":true,"_uuid":"ce7728eb1e5e50bd38f6db35feeb133b471d3721"},"cell_type":"code","source":"test['totals.transactionRevenue'] = np.nan","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)\nall_data = train.reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"21e9028c7ddbec508a817e3253b3dcfccf0a6f8b"},"cell_type":"code","source":"print(all_data.info())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"69f008c8b1d10e4b28945b97be085b43c290cc27","scrolled":false},"cell_type":"code","source":"null_cnt = train.isnull().sum().sort_values()\nprint(null_cnt[null_cnt > 0])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train['totals.newVisits'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Документация https://support.google.com/analytics/answer/3437719?hl=ru"},{"metadata":{"trusted":true,"_uuid":"f3134948730860dbe84e9a3672b5398c437b791f"},"cell_type":"code","source":"# fillna object feature\nfor col in ['trafficSource.keyword',\n            'trafficSource.referralPath',\n            'trafficSource.adContent']:\n    all_data[col].fillna('unknown', inplace=True)\n\n# fillna numeric feature\n# заполняем в соответствии с продуктовой логикой\nall_data['totals.pageviews'].fillna(1, inplace=True) # хотя бы 1 просмотр одной страницы должен быть\nall_data['totals.newVisits'].fillna(0, inplace=True) # если NaN - то это не новый пользователь\nall_data['totals.bounces'].fillna(0, inplace=True) # если NaN - то это не отскок\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) # прямой заход на сайт через url","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e93201ab3ceec116e1e66c64e8cb8b13019d96f6"},"cell_type":"code","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)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e607e6782bbbaa79a7cfbaa79364223a899fe94c"},"cell_type":"code","source":"# pickup any visitor\nall_data[all_data['fullVisitorId'] == '7813149961404844386'].sort_values(by='visitNumber')[\n    ['date','visitId','visitNumber','totals.hits','totals.pageviews']].head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data[all_data['fullVisitorId'] == '7813149961404844386'].sort_values(by='visitNumber').head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(\"min_date:\", all_data['date'].min(), \"max_date:\", all_data['date'].max())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a8f4e841406d2babd6e8c3429dc0f9221a2f58ad"},"cell_type":"code","source":"train_rev = train[train['totals.transactionRevenue'] > 0].copy()\nprint(len(train_rev))\ntrain_rev.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a4558c13d3270bd5e12a8abae94dc144194d4cd0"},"cell_type":"code","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(colormap='jet')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"76b8e5cdbcd0f77f0af9a38fc23d7d28de7ac6b3"},"cell_type":"markdown","source":"## customDimensions"},{"metadata":{"trusted":true,"_uuid":"bf4afafc3b1a3af5fc244c663f64dacea55f7428"},"cell_type":"code","source":"print('unique customDimensions count:', train['customDimensions'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'customDimensions')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"df1597ce637e40c82ddbce0567f1aca6d44010d5"},"cell_type":"markdown","source":"## date"},{"metadata":{"trusted":true,"_uuid":"7f62796c68e95615311e28ec061fc76c0877e967"},"cell_type":"code","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)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data.head()","execution_count":null,"outputs":[]},{"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":"code","source":"plotCategoryRateBar(all_data, train_rev, 'channelGrouping')","execution_count":null,"outputs":[]},{"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":"code","source":"print('train all:', len(train))\nprint('train unique fullVisitorId:', train['fullVisitorId'].nunique())\nprint('train unique visitId:', train['visitId'].nunique())\nprint('-' * 30)\n# print('test all:', len(test))\n# print('test unique fullVisitorId:', test['fullVisitorId'].nunique())\n# print('test unique visitId:', test['visitId'].nunique())\n\n#print('common fullVisitorId:', len(pd.merge(train, test, how='inner', on='fullVisitorId'))) # 183434","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"47d962d3eb9f8674f7dc2a457877faab6cbaa916"},"cell_type":"code","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())","execution_count":null,"outputs":[]},{"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":"code","source":"all_data['_visitStartHour'] = all_data['visitStartTime'].apply(\n    lambda x: str(datetime.fromtimestamp(x).hour))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a6b79c64d4c75031a9cd6d04abeff9eaa9648b25"},"cell_type":"markdown","source":"## device"},{"metadata":{"trusted":true,"_uuid":"94893bde8a5f5f2cdd5fe7757eab49f252753adb"},"cell_type":"code","source":"print('unique browser count:', train['device.browser'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'device.browser', 10)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Проверяем, есть ли некорректные данные в isMobile"},{"metadata":{"trusted":true},"cell_type":"code","source":"pd.crosstab(all_data['device.deviceCategory'], all_data['device.isMobile'], margins=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Корректируем"},{"metadata":{"trusted":true,"_uuid":"6103fcf3bcc3f70eec8a93d91551ec134b122342"},"cell_type":"code","source":"all_data['isMobile'] = True\nall_data.loc[all_data['device.deviceCategory'] == 'desktop', 'isMobile'] = False","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d8df08c75a5c8ac8f4771805d09d96b83362f3ae"},"cell_type":"code","source":"print('unique operatingSystem count:', train['device.operatingSystem'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'device.operatingSystem', 10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d33b8252e73c702753086bb7c0a889f1a7922a9e"},"cell_type":"markdown","source":"## geoNetwork"},{"metadata":{"trusted":true,"_uuid":"3b1eaa2069013d4a1c19b80516e159a536dc6d8d"},"cell_type":"code","source":"print('unique geoNetwork.city count:', train['geoNetwork.city'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'geoNetwork.city', 10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4f44c8d97db856c51d763f5212aa3c98698130e3"},"cell_type":"code","source":"print('unique geoNetwork.region count:', train['geoNetwork.region'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'geoNetwork.region', 10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data['geoNetwork.region'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"95c2c106c2f546e66c3d55c8cb791fffdbc10107"},"cell_type":"code","source":"print('unique geoNetwork.subContinent count:', train['geoNetwork.subContinent'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'geoNetwork.subContinent', 10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"0464279e4253c4b89ba7bfb085f7049ed85bc128"},"cell_type":"code","source":"print('unique geoNetwork.continent count:', train['geoNetwork.continent'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'geoNetwork.continent')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"084e17c130b2a0db036b9024d16440202b391bf6"},"cell_type":"code","source":"print('unique geoNetwork.metro count:', train['geoNetwork.metro'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'geoNetwork.metro', 10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2c5ae32c4cd8953a6f722ff66a72e6266a057d17","scrolled":false},"cell_type":"code","source":"print('unique geoNetwork.networkDomain count:', train['geoNetwork.networkDomain'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'geoNetwork.networkDomain', 10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"88469fecbc877ce38ea430e1cfd5e5ab998033e5"},"cell_type":"markdown","source":"## totals"},{"metadata":{"trusted":true,"_uuid":"82570f747376d5688ce66ff788d6687b5d92447b","scrolled":true},"cell_type":"code","source":"print(all_data['totals.hits'].value_counts()[:10])\n\nall_data['totals.hits'] = all_data['totals.hits'].astype(int)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d4b608e99f2ed2e8c2732ec7aabf3b24335e5ad8"},"cell_type":"code","source":"print(all_data['totals.pageviews'].value_counts()[:10])\n\nall_data['totals.pageviews'] = all_data['totals.pageviews'].astype(int)","execution_count":null,"outputs":[]},{"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":"code","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)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"216ff4ed12ecb7543b1cb0a71582f580cfc1becc","scrolled":false},"cell_type":"code","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)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8ddc105a27a0647205e91113d1e6744856756848"},"cell_type":"code","source":"print(all_data['trafficSource.isTrueDirect'].value_counts())\nplotCategoryRateBar(all_data, train_rev, 'trafficSource.isTrueDirect')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7492ccdd02d468d1e7d9df57dfd3d09e5f4c2ed5"},"cell_type":"code","source":"print('unique trafficSource.keyword count:', train['trafficSource.keyword'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'trafficSource.keyword', 10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8cc6336da483573190d7bbfc039d0aae3afc26a7"},"cell_type":"code","source":"print('unique trafficSource.medium count:', train['trafficSource.medium'].nunique())\nplotCategoryRateBar(all_data, train_rev, 'trafficSource.medium')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"68bbc2cf706edc7878ec79ef18a50ce39d5f6985","scrolled":true},"cell_type":"code","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)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"921c55633ac9c113521b33611564762bc8090919"},"cell_type":"code","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)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7a0eef047ac62a8ab52abb174de2e84dd6150ed9"},"cell_type":"markdown","source":"## Aggregate"},{"metadata":{"trusted":true,"_uuid":"2dc1702ba3ecfc3c1e9b439fc9ba5f4a53b12ecb"},"cell_type":"code","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)\n\ndel info","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data = all_data.drop(['_meanHitsPerDay', '_meanHitsPerWeekday', '_meanHitsPerMonth', '_sumHitsPerDay', '_sumHitsPerWeekday', \n               '_sumHitsPerMonth', '_usermean_totals.hits', '_usermean_totals.pageviews', '_usermax_visitNumber'], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fa595eea18a34885ba7fecbbd4bf63406d09460c"},"cell_type":"code","source":"all_data['_source.country'] = all_data['trafficSource.source'] + '_' + all_data['geoNetwork.country']\nall_data['_campaign.medium'] = all_data['trafficSource.campaign'] + '_' + all_data['trafficSource.medium']\nall_data['_browser.category'] = all_data['device.browser'] + '_' + all_data['device.deviceCategory']\nall_data['_browser.os'] = all_data['device.browser'] + '_' + all_data['device.operatingSystem']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data = all_data.drop(['_source.country', '_campaign.medium', '_browser.category', '_browser.os'], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(all_data['device.deviceCategory'].value_counts())\nprint(all_data['device.operatingSystem'].value_counts())\nprint(all_data['trafficSource.adContent'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"os_set = set(['Windows', 'Macintosh', 'Android', 'iOS', 'Linux', 'Chrome OS'])\nall_data['device.operatingSystem.cut'] = all_data['device.operatingSystem'].apply(lambda x: x if x in os_set else 'other')\nad_cnt_set = set(['unknown', 'Google Merchandise Collection', 'Google Online Store'])\nall_data['trafficSource.adContent.cut'] = all_data['trafficSource.adContent'].apply(lambda x: x if x in ad_cnt_set else 'other')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data = all_data.drop(['device.operatingSystem', 'trafficSource.adContent'], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(all_data['geoNetwork.city'].value_counts()[:30])\nprint(all_data['geoNetwork.continent'].value_counts())\nprint(all_data['geoNetwork.country'].value_counts()[:30])\nprint(all_data['geoNetwork.metro'].value_counts()[:30])\nprint(all_data['geoNetwork.networkDomain'].value_counts()[:30])\nprint(all_data['geoNetwork.region'].value_counts()[:30])\nprint(all_data['geoNetwork.subContinent'].value_counts()[:30])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"city_set = set(all_data['geoNetwork.city'].value_counts()[:30].index)\ncountry_set = set(all_data['geoNetwork.country'].value_counts()[:30].index)\nmetro_set = set(all_data['geoNetwork.metro'].value_counts()[:30].index)\nnet_dom_set = set(all_data['geoNetwork.networkDomain'].value_counts()[:30].index)\nregion_set = set(all_data['geoNetwork.region'].value_counts()[:30].index)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data['geoNetwork.city.cut'] = all_data['geoNetwork.city'].apply(lambda x: x if x in city_set else 'other')\nall_data['geoNetwork.country.cut'] = all_data['geoNetwork.country'].apply(lambda x: x if x in country_set else 'other')\nall_data['geoNetwork.metro.cut'] = all_data['geoNetwork.metro'].apply(lambda x: x if x in metro_set else 'other')\nall_data['geoNetwork.networkDomain.cut'] = all_data['geoNetwork.networkDomain'].apply(lambda x: x if x in net_dom_set else 'other')\nall_data['geoNetwork.region.cut'] = all_data['geoNetwork.region'].apply(lambda x: x if x in region_set else 'other')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data = all_data.drop(['geoNetwork.city', 'geoNetwork.country', 'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region'], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(all_data['trafficSource.medium'].value_counts())\nprint(all_data['trafficSource.source'].value_counts()[:10])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"tr_source_set = set(all_data['trafficSource.source'].value_counts()[:10].index)\n\nall_data['trafficSource.source.cut'] = all_data['trafficSource.source'].apply(lambda x: x if x in tr_source_set else 'other')\n\nall_data = all_data.drop(['trafficSource.source'], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data = all_data.drop(['trafficSource.keyword', 'trafficSource.campaign', 'trafficSource.referralPath'], axis=1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Агрегации"},{"metadata":{"trusted":true},"cell_type":"code","source":"train1 = all_data[(all_data._year == 2016) | ((all_data._year == 2017) & (all_data._month <= 2))]\ntest1 = all_data[(all_data._year == 2017) & (all_data._month >= 3) & (all_data._month <= 4)]\n\nprint(train1.shape)\nprint(test1.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train2 = all_data[((all_data._year == 2016) & (all_data._month >= 11)) | ((all_data._year == 2017) & (all_data._month <= 4))]\ntest2 = all_data[(all_data._year == 2017) & (all_data._month >= 5) & (all_data._month <= 6)]\n\nprint(train2.shape)\nprint(test2.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1['sinceLastTime'] = train1['visitStartTime'].max() - train1['visitStartTime']\ntrain2['sinceLastTime'] = train2['visitStartTime'].max() - train2['visitStartTime']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1['totals.transactionRevenue'] = np.expm1(train1['totals.transactionRevenue'])\ntest1['totals.transactionRevenue'] = np.expm1(test1['totals.transactionRevenue'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train2['totals.transactionRevenue'] = np.expm1(train2['totals.transactionRevenue'])\ntest2['totals.transactionRevenue'] = np.expm1(test2['totals.transactionRevenue'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"num_columns = ['device.isMobile', 'totals.bounces', 'totals.hits', 'totals.newVisits', 'totals.pageviews',\n              'totals.transactionRevenue', 'trafficSource.isTrueDirect', '_visitStartHour', 'isMobile',\n              '_adContentGMC', '_withCampaign', '_referralRoot', '_sourceGpmall']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"categ_columns = ['channelGrouping', 'device.browser', 'device.deviceCategory', 'geoNetwork.continent',\n                                 'geoNetwork.subContinent', 'trafficSource.medium', 'device.operatingSystem.cut',\n                                 'trafficSource.adContent.cut', 'geoNetwork.city.cut', 'geoNetwork.country.cut',\n                                 'geoNetwork.metro.cut', 'geoNetwork.networkDomain.cut', 'geoNetwork.region.cut',\n                                 'trafficSource.source.cut']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"time_columns = ['sinceLastTime']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1_group = train1.groupby(['fullVisitorId'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train2_group = train2.groupby(['fullVisitorId'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1_vis_categ = train1_group[categ_columns].first().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train2_vis_categ = train2_group[categ_columns].first().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1_vis_num = train1_group[num_columns].agg(['mean', 'max', 'sum'])\ntrain1_vis_num.columns = ['_'.join(col).strip() for col in train1_vis_num.columns.values]\ntrain1_vis_num = train1_vis_num.reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train2_vis_num = train2_group[num_columns].agg(['mean', 'max', 'sum'])\ntrain2_vis_num.columns = ['_'.join(col).strip() for col in train2_vis_num.columns.values]\ntrain2_vis_num = train2_vis_num.reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1_vis_time = train1_group[time_columns].min().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train2_vis_time = train2_group[time_columns].min().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1_vis = train1_vis_categ.merge(train1_vis_num, on='fullVisitorId').merge(train1_vis_time, on='fullVisitorId')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train2_vis = train2_vis_categ.merge(train2_vis_num, on='fullVisitorId').merge(train2_vis_time, on='fullVisitorId')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1[train1.fullVisitorId == '0002871498069867123']","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## transform revenue factors"},{"metadata":{"trusted":true},"cell_type":"code","source":"for fac in ['totals.transactionRevenue_mean', 'totals.transactionRevenue_max', 'totals.transactionRevenue_sum']:\n    train1_vis[fac] = np.log1p(train1_vis[fac])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for fac in ['totals.transactionRevenue_mean', 'totals.transactionRevenue_max', 'totals.transactionRevenue_sum']:\n    train2_vis[fac] = np.log1p(train2_vis[fac])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1_vis[train1_vis['totals.transactionRevenue_sum'] > 0]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Constructing target from test data"},{"metadata":{"trusted":true},"cell_type":"code","source":"test1_y = test1.groupby(['fullVisitorId'])['totals.transactionRevenue'].sum().reset_index()\ntest2_y = test2.groupby(['fullVisitorId'])['totals.transactionRevenue'].sum().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test1_y['totals.transactionRevenue'] = np.log1p(test1_y['totals.transactionRevenue'])\ntest2_y['totals.transactionRevenue'] = np.log1p(test2_y['totals.transactionRevenue'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(test1_y[test1_y['totals.transactionRevenue'] > 0].shape)\nprint(test2_y[test2_y['totals.transactionRevenue'] > 0].shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1_vis = train1_vis.merge(test1_y, on='fullVisitorId', how='left')\ntrain1_vis['target'] = train1_vis['totals.transactionRevenue'].fillna(0)\ntrain1_vis = train1_vis.drop(['totals.transactionRevenue'], axis=1)\n\ntrain1_vis","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train2_vis = train2_vis.merge(test2_y, on='fullVisitorId', how='left')\ntrain2_vis['target'] = train2_vis['totals.transactionRevenue'].fillna(0)\ntrain2_vis = train2_vis.drop(['totals.transactionRevenue'], axis=1)\n\ntrain2_vis","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1_vis[train1_vis.target > 0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1_vis_dumm = pd.get_dummies(train1_vis, columns=['channelGrouping', 'device.browser', 'device.deviceCategory', 'geoNetwork.continent',\n                                 'geoNetwork.subContinent', 'trafficSource.medium', 'device.operatingSystem.cut',\n                                 'trafficSource.adContent.cut', 'geoNetwork.city.cut', 'geoNetwork.country.cut',\n                                 'geoNetwork.metro.cut', 'geoNetwork.networkDomain.cut', 'geoNetwork.region.cut',\n                                 'trafficSource.source.cut'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train2_vis_dumm = pd.get_dummies(train2_vis, columns=['channelGrouping', 'device.browser', 'device.deviceCategory', 'geoNetwork.continent',\n                                 'geoNetwork.subContinent', 'trafficSource.medium', 'device.operatingSystem.cut',\n                                 'trafficSource.adContent.cut', 'geoNetwork.city.cut', 'geoNetwork.country.cut',\n                                 'geoNetwork.metro.cut', 'geoNetwork.networkDomain.cut', 'geoNetwork.region.cut',\n                                 'trafficSource.source.cut'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cols_to_drop = set(train2_vis_dumm.columns) - set(train1_vis_dumm.columns)\n\ntrain2_vis_dumm = train2_vis_dumm.drop(list(cols_to_drop), axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cols_to_add = set(train1_vis_dumm.columns) - set(train2_vis_dumm.columns)\n\nfor col in cols_to_add:\n    train2_vis_dumm[col] = 0","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Model fitting"},{"metadata":{"trusted":true},"cell_type":"code","source":"from sklearn import metrics\nimport lightgbm as lgb","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"params={'learning_rate': 0.01,\n        'objective':'regression',\n        'metric':'rmse',\n        'num_leaves': 31,\n        'verbose': 1,\n        'random_state':42,\n        'bagging_fraction': 0.6,\n        'feature_fraction': 0.6\n       }\n\nreg = lgb.LGBMRegressor(**params, n_estimators=500)\n\nreg.fit(train1_vis_dumm.drop(['fullVisitorId', 'target'], axis=1), train1_vis_dumm['target'],\n        eval_set=[(train2_vis_dumm.drop(['fullVisitorId', 'target'], axis=1), train2_vis_dumm['target'])],\n        early_stopping_rounds=50,\n        verbose=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"predicts_train = reg.predict(train1_vis_dumm.drop(['fullVisitorId', 'target'], axis=1))\npredicts_valid = reg.predict(train2_vis_dumm.drop(['fullVisitorId', 'target'], axis=1))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(\"rmse train:\", np.sqrt(metrics.mean_squared_error(predicts_train, train1_vis_dumm['target'])))\nprint(\"rmse valid:\", np.sqrt(metrics.mean_squared_error(predicts_valid, train2_vis_dumm['target'])))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train1_vis_dumm['predicts'] = predicts_train\ntrain2_vis_dumm['predicts'] = predicts_valid","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"predicts_train.max()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train2_vis_dumm[train2_vis_dumm.target > 15][['target', 'predicts']]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## feature importances"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Plot feature importance\nfeature_importance = reg.feature_importances_\nfeature_importance = 100.0 * (feature_importance / feature_importance.max())\nsorted_idx = np.argsort(feature_importance)\nsorted_idx = sorted_idx[len(feature_importance) - 30:]\npos = np.arange(sorted_idx.shape[0]) + .5\n\nplt.figure(figsize=(12,8))\nplt.barh(pos, feature_importance[sorted_idx], align='center')\nplt.yticks(pos, train1_vis_dumm.drop(['fullVisitorId', 'target'], axis=1).columns[sorted_idx])\nplt.xlabel('Relative Importance')\nplt.title('Variable Importance')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Домашнее задание\n\n1. Обучаем модель на отток клиентов (классификация - будет ли вообще данный клиент проявлять активность на протяжении следующих 2 месяцев)\n2. Корректируем предсказания с учетом этих показаний (если клиент не будет проявлять активность, то есть оттечет, значит и logRevenue от него должен быть равен строго 0)"},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}