{"cells":[{"metadata":{"_uuid":"7b603c3873a8e20f1987a4ca18887f30a0f714ad"},"cell_type":"markdown","source":"## General information\nThis kernel is dedicated to EDA of Google Analytics Customer Revenue Prediction  competition as well as feature engineering. For now basic data is used and not data from BigQuery.\n\nIn this dataset we can see customers which went to Google Merchandise Store, info about them and their transactions. We need to predict the natural log of the sum of all transactions per user.\n\nA continuation of this kernel can be found in [this one](https://www.kaggle.com/artgor/lgb-and-feature-generation) it has more features, CV calculation and other things."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport json\nimport bq_helper\nfrom pandas.io.json import json_normalize\nimport seaborn as sns \nimport matplotlib.pyplot as plt\n%matplotlib inline\nplt.style.use('ggplot')\nimport lightgbm as lgb\n\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.model_selection import TimeSeriesSplit, KFold\nfrom sklearn.metrics import mean_squared_error","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"# https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields\n\ndef load_df(csv_path='../input/train.csv', JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource'], load_all=False):\n    if not load_all:\n        df = pd.read_csv(csv_path, \n                         converters={column: json.loads for column in JSON_COLUMNS}, \n                         dtype={'fullVisitorId': 'str'}, nrows=250000)\n    else:\n        df = pd.read_csv(csv_path, \n                         converters={column: json.loads for column in JSON_COLUMNS}, \n                         dtype={'fullVisitorId': 'str'})\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\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4432fc68b07ee992af552288974e52aa31c9fa41"},"cell_type":"code","source":"%%time\ntrain = load_df(\"../input/train_v2.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9c8711b819c6f28a84b37ca0e5b1ceec84d65c74"},"cell_type":"code","source":"%%time\ntest = load_df(\"../input/test_v2.csv\", load_all=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0529ae895cca83127dbd0a5056452031b291aad0"},"cell_type":"code","source":"# some data processing\ntrain['trafficSource.adwordsClickInfo.isVideoAd'].fillna(True, inplace=True)\ntest['trafficSource.adwordsClickInfo.isVideoAd'].fillna(True, inplace=True)\ntrain['trafficSource.isTrueDirect'].fillna(False, inplace=True)\ntest['trafficSource.isTrueDirect'].fillna(False, inplace=True)\n\ntrain['date'] = pd.to_datetime(train['date'].apply(lambda x: str(x)[:4] + '-' + str(x)[4:6] + '-' + str(x)[6:]))\ntest['date'] = pd.to_datetime(test['date'].apply(lambda x: str(x)[:4] + '-' + str(x)[4:6] + '-' + str(x)[6:]))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f2dfe028ddcfccbfd0816802bf402d9274a61771"},"cell_type":"markdown","source":"## Data exploration"},{"metadata":{"_uuid":"61737ac2fac6a48027869220e6bf343ba84a0921"},"cell_type":"markdown","source":"Some of columns aren't available in this dataset, let's drop them."},{"metadata":{"trusted":true,"_uuid":"68282c259933877bd186a04779cce6ab51db2109"},"cell_type":"code","source":"cols_to_drop = [col for col in train.columns if train[col].nunique(dropna=False) == 1]\ntrain.drop(cols_to_drop, axis=1, inplace=True)\ntest.drop([col for col in cols_to_drop if col in test.columns], axis=1, inplace=True)\n\n#only one not null value\n# train.drop(['trafficSource.campaignCode'], axis=1, inplace=True)\n# test.drop(['trafficSource.campaignCode'], axis=1, inplace=True)\n\nprint(f'Dropped {len(cols_to_drop)} columns.')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f25961d247d9faabda46857c29cab597207f1419"},"cell_type":"code","source":"train[['visitNumber', 'totals.hits', 'totals.pageviews', 'totals.transactionRevenue']].info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"72709608bb0a2076b355a2d8324e513336815431"},"cell_type":"code","source":"# converting columns into more reasonable format\nfor col in ['visitNumber', 'totals.hits', 'totals.pageviews', 'totals.transactionRevenue']:\n    train[col] = train[col].astype(float)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dcd58186cc286835003e508c19cf299ad68b6c78"},"cell_type":"code","source":"train.drop(['customDimensions', 'hits', 'trafficSource.referralPath', 'trafficSource.source', 'totals.totalTransactionRevenue'], axis=1, inplace=True)\ntest.drop(['customDimensions', 'hits', 'trafficSource.referralPath', 'trafficSource.source', 'totals.totalTransactionRevenue'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ea6ecadd5f5ba04eac3fe4e048aa8fcddc066a38"},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a4e9f44ad434a9fa5a85f06ee8c3184d14db2cda"},"cell_type":"code","source":"for col in train.columns:\n    if train[col].isnull().sum() > 0:\n        rate = train[col].isnull().sum() * 100 / train.shape[0]\n        print(f'Column {col} has {rate:.4f}% missing values.')\n    if train[col].dtype == 'object':\n        if (train[col] == 'not available in demo dataset').sum() > 0:\n            rate = (train[col] == 'not available in demo dataset').sum() * 100 / train.shape[0]\n            print(f'Column {col} has {rate:.4f}% values not available in dataset.')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c1451a31fa28b2b2a2497fb8cfcba018a7afa2fe"},"cell_type":"markdown","source":"We can see several thing from this overview:\n- less than 2% of all transactions bring revenue. This percentage is more or less reasonable;\n- some columns have full representation only in BigQuery, so it is necessary to use it;\n- traffic sourse in some cases is unknown, we'll need to find a way to fill these values;\n- there several groups of features: visitor activity, geodata and device info, source of traffic;"},{"metadata":{"_uuid":"c25aae75645a80f9d1c517ab95f51094e838f117"},"cell_type":"markdown","source":"## Feature analysis"},{"metadata":{"_uuid":"8930a92f179ae66a72cf702a8d0ea059299ab785"},"cell_type":"markdown","source":"### Revenue"},{"metadata":{"trusted":true,"_uuid":"134174a89178ac3738ca2f82bb5eefc2cf3377e3"},"cell_type":"code","source":"plt.hist(np.log1p(train.loc[train['totals.transactionRevenue'].isna() == False, 'totals.transactionRevenue']));\nplt.title('Distribution of revenue');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"87556cb48ff9878101d975840f3a7e3e9edba34c"},"cell_type":"markdown","source":"Here we can see the distribution of transaction revenue. But it would be more useful to see a distribution of total revenue per user."},{"metadata":{"trusted":true,"_uuid":"c4bb8b91293b5b23f2a913e013cc003049287bdc"},"cell_type":"code","source":"grouped = train.groupby('fullVisitorId')['totals.transactionRevenue'].sum().reset_index()\ngrouped = grouped.loc[grouped['totals.transactionRevenue'].isna() == False]\nplt.hist(np.log(grouped.loc[grouped['totals.transactionRevenue'] > 0, 'totals.transactionRevenue']));\nplt.title('Distribution of total revenue per user');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"64e0f05abdd49ce970219caf9e845fc447e86a0a"},"cell_type":"markdown","source":"The plots are quite similar."},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"585c83fa39d173fb073e65fca2214c36216f7487"},"cell_type":"code","source":"counts = train.loc[train['totals.transactionRevenue'] > 0, 'fullVisitorId'].value_counts()\nprint('There are {0} paying users ({1} total) in train data.'.format(len(counts), train['fullVisitorId'].nunique()))\nprint('{0} users ({1:.4f}% of paying) have 1 paid transaction.'.format(np.sum(counts == 1), 100 * np.sum(counts == 1) / len(counts)))\nprint('{0} users ({1:.4f}% of paying) have 2 paid transaction.'.format(np.sum(counts == 2), 100 * np.sum(counts == 2) / len(counts)))\nprint('')\nprint('Count of non-zero transactions per user:')\ncounts.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"344f339f03ba35367ccbd0aadf1efd4bb1a0b631"},"cell_type":"markdown","source":"Most paying users made only 1 transaction, but there are several users, who had a lot of transactions. Regular customers?"},{"metadata":{"trusted":true,"_uuid":"389b30983da6a2ddf980f8812249a48589291104","scrolled":false},"cell_type":"code","source":"train['totals.transactionRevenue'] = train['totals.transactionRevenue'].fillna(0)\ntarget = train['totals.transactionRevenue']\ntrain['totals.transactionRevenue'] = np.log1p(train['totals.transactionRevenue'])\nsns.set(rc={'figure.figsize':(20, 16)})\ntrain_ = train.loc[train['totals.transactionRevenue'] > 0.0]\nsns.boxplot(x=\"device.deviceCategory\", y=\"totals.transactionRevenue\", hue=\"channelGrouping\",  data=train_)\nplt.title(\"Total revenue by device category and channel.\");\nplt.xticks(rotation='vertical')\nplt.legend(bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9165446b3d1c6f1d629df912249c5a7540a6b30f"},"cell_type":"markdown","source":"We can see that revenue comes mostly from desktops. Social, Affiliates and others aren't as profitable as other channels."},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"1f4bb2c008a3c0fd4365ee3f9cde8a425f596696","_kg_hide-input":true},"cell_type":"code","source":"fig, ax1 = plt.subplots(figsize=(16, 8))\nplt.title(\"Trends of transactions number by paying and non-paying users\");\ntrain.groupby(['date'])['totals.transactionRevenue'].count().plot(color='brown')\nax1.set_ylabel('Transaction count', color='b')\nplt.legend(['Non-paying users'])\nax2 = ax1.twinx()\ntrain_.groupby(['date'])['totals.transactionRevenue'].count().plot(color='gold')\nax2.set_ylabel('Transaction count', color='g')\nplt.legend(['Paying users'], loc=(0.875, 0.9))\nplt.grid(False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e73bce4c6ee854e20aff6cd7324c49a1a0d17b89"},"cell_type":"code","source":"fig, ax1 = plt.subplots(figsize=(16, 8))\nplt.title(\"Trends transaction count and total value by paying users\")\ntrain_.groupby(['date'])['totals.transactionRevenue'].count().plot(color='purple')\nax1.set_ylabel('Transaction count', color='b')\nplt.legend(['Transaction count'])\nax2 = ax1.twinx()\ntrain_.groupby(['date'])['totals.transactionRevenue'].sum().plot(color='gold')\nax2.set_ylabel('Natural logarithm of sum of transactions', color='g')\nplt.legend(['Transaction sum'], loc=(0.875, 0.9))\nplt.grid(False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7b57d6463f180acbe70ae729d6e8afb43fd5301e"},"cell_type":"code","source":"print(f'First date in train set is {train[\"date\"].min()}. Last date in train set is {train[\"date\"].max()}.')\nprint(f'First date in test set is {test[\"date\"].min()}. Last date in test set is {test[\"date\"].max()}.')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a2b5d5fdd5aae2c6b51e91d9c06d874f683cf644"},"cell_type":"markdown","source":"\nIt isn't surprising that trends of sum and count of paid transactions are almost similar. It is worth noticing that there are several periods when the number of non-paying users was significantly higher that the number of paying users, but it didin't influence total sums.\n\nTrain and test period don't intersect."},{"metadata":{"_uuid":"8e388d8356375b20e2f67b57e10a5403e38df522"},"cell_type":"markdown","source":"### Devices\n\nLet's see which devices bring most revenue!"},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"96c86609f9cb289ac639ba4a043b8a77b73d44dc","_kg_hide-input":true},"cell_type":"code","source":"fig, ax = plt.subplots(2, 2, figsize = (16, 12))\nprint('Mean revenue per transaction')\nsns.pointplot(x=\"device.browser\", y=\"totals.transactionRevenue\", hue=\"device.isMobile\", data=train_, ax = ax[0, 0]);\nsns.pointplot(x=\"device.deviceCategory\", y=\"totals.transactionRevenue\", hue=\"device.isMobile\", data=train_, ax = ax[0, 1]);\nsns.pointplot(x=\"device.operatingSystem\", y=\"totals.transactionRevenue\", hue=\"device.isMobile\", data=train_, ax = ax[1, 0]);\nsns.pointplot(x=\"device.isMobile\", y=\"totals.transactionRevenue\", data=train_, ax = ax[1, 1]);\nax[0, 0].xaxis.set_tick_params(rotation=30);\nax[0, 1].xaxis.set_tick_params(rotation=30);\nax[1, 0].xaxis.set_tick_params(rotation=30);\nax[1, 1].xaxis.set_tick_params(rotation=30);","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ccb4decd8cfea52e2db75695e99215b69330c307"},"cell_type":"markdown","source":"It seems that devices on Chrome OS and Macs bring most profit.\n\nThere is an interesting fact - there are desktop devices which are considered to be mobile devices. Is it an error?"},{"metadata":{"_uuid":"e14aee044f918c6adfc99d2232a5cc2049fa00f9"},"cell_type":"markdown","source":"### geoNetwork"},{"metadata":{"trusted":true,"_uuid":"8b5b58601aa7cee99f2c205a18ee481ed75c6fab"},"cell_type":"code","source":"def show_count_sum(df, col):\n    return df.groupby(col).agg({'totals.transactionRevenue': ['count', 'mean']}).sort_values(('totals.transactionRevenue', 'count'), ascending=False).head()\nshow_count_sum(train_, 'geoNetwork.subContinent')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b24b90b3422804043ca60589f3741c1bd1b4a35e"},"cell_type":"markdown","source":"Obviously most transactions come from America"},{"metadata":{"trusted":true,"_uuid":"0bb902651660a8f3871aa2203df2c5ffa8bff017"},"cell_type":"code","source":"show_count_sum(train_.loc[train_['geoNetwork.subContinent'] == 'Northern America'], 'geoNetwork.metro')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0d5b2bf5f082bc971672e71e345d1f49390fe0aa"},"cell_type":"code","source":"show_count_sum(train_.loc[train_['geoNetwork.subContinent'] == 'Northern America'], 'geoNetwork.networkDomain')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dce134066ac48f70da68bb5f9440aff1e58141ca"},"cell_type":"code","source":"show_count_sum(train_.loc[train_['geoNetwork.subContinent'] == 'Northern America'], 'geoNetwork.region')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"844482311810416eba65bf53cc9f5b8fc3726510"},"cell_type":"markdown","source":"Most transactions are from California or New York."},{"metadata":{"_uuid":"c4c3245dd4eb59dbd4b70fda9832b5a97669b7c6"},"cell_type":"markdown","source":"### Traffic source"},{"metadata":{"trusted":true,"_uuid":"5842dfbb6b947fa1157a74049dda33ba0046d67a"},"cell_type":"code","source":"show_count_sum(train_, 'trafficSource.medium')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"62f439d18b0d0d7f714c2c9e3b093ae20425aba1"},"cell_type":"markdown","source":"Let's see how video ads influence revenue. There are for graphs, two of them show count of all users or only paying users, another two show mean and total revenue for transactions based on wheather there were video ads."},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"8d024cc77b20abb04c4d360c6da103c813ba6d7e"},"cell_type":"code","source":"fig, ax = plt.subplots(2, 2, figsize = (12, 12));\nsns.countplot('trafficSource.adwordsClickInfo.isVideoAd', data=train, ax = ax[0, 0]);\nax[0, 0].set_title('All users count');\nsns.countplot('trafficSource.adwordsClickInfo.isVideoAd', data=train_, ax = ax[0, 1]);\nax[0, 1].set_title('Paying users count');\ntrain.groupby('trafficSource.adwordsClickInfo.isVideoAd')['totals.transactionRevenue'].mean().plot('bar', ax = ax[1, 0]);\nax[1, 0].set_title('Mean revenue');\ntrain.groupby('trafficSource.adwordsClickInfo.isVideoAd')['totals.transactionRevenue'].sum().plot('bar', ax = ax[1, 1]);\nax[1, 1].set_title('Total revenue');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8753dee63f716569de984d89005d5825a5cca18d"},"cell_type":"markdown","source":"We can see that the ratio of users arriving from video ads is almost the same for paid and non-paid transactions.\n\nAnd total revenue from transactions from video ads is much higher."},{"metadata":{"_uuid":"5f7b7dd8e67cee67f07ef706bb861d602e24fbeb"},"cell_type":"markdown","source":"Let's see the same graphs for trafficSource.adwordsClickInfo.isTrueDirect feature"},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"8e6aa08299fabdf7bf49143e6ea11e3376755e26"},"cell_type":"code","source":"fig, ax = plt.subplots(2, 2, figsize = (12, 12));\nsns.countplot('trafficSource.isTrueDirect', data=train, ax = ax[0, 0]);\nax[0, 0].set_title('All users count');\nsns.countplot('trafficSource.isTrueDirect', data=train_, ax = ax[0, 1]);\nax[0, 1].set_title('Paying users count');\ntrain.groupby('trafficSource.isTrueDirect')['totals.transactionRevenue'].mean().plot('bar', ax = ax[1, 0]);\nax[1, 0].set_title('Mean revenue');\ntrain.groupby('trafficSource.isTrueDirect')['totals.transactionRevenue'].sum().plot('bar', ax = ax[1, 1]);\nax[1, 1].set_title('Total revenue');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"98c18fa9855be81cd8bee527cf4129657cb3375d"},"cell_type":"markdown","source":"True directing seems to be better in all regards."},{"metadata":{"_uuid":"08f11d72239653309f435c3afa39d128356869de"},"cell_type":"markdown","source":"### Totals"},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"83e7e4d75b71a38c9ea7814cc21d69bea5dc59d6"},"cell_type":"code","source":"plt.figure(figsize=(12, 8))\nplt.title('Distribution of pageviews');\nplt.hist(np.log1p(train['totals.pageviews'].fillna(0)));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"107c96a35b4d728de8ea6c5136c1aaa6a27c6634"},"cell_type":"code","source":"plt.figure(figsize=(12, 8))\nplt.title('Distribution of hits');\nplt.hist(np.log1p(train['totals.hits'].fillna(0)));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d79e7af9f6fa8beca1f4e7607d48ae2ea291b6a3"},"cell_type":"code","source":"plt.figure(figsize=(12, 8))\nplt.title('pageviews vs revenue');\nplt.scatter(train_['totals.pageviews'], train_['totals.transactionRevenue']);\nplt.xlabel('Pageviews');\nplt.ylabel('Revenue');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cacf0d069fa9bf61bf4e65e3a3cc66ae4d247d38"},"cell_type":"markdown","source":"## Feature engineering"},{"metadata":{"_uuid":"32899747432430098439444725fa058732e093f5"},"cell_type":"markdown","source":"At first let's create some features"},{"metadata":{"trusted":true,"_uuid":"a88ec51e08b5370ca22a97c438ec7af1350adc5e"},"cell_type":"code","source":"del grouped, counts, train_","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6df4dfaf9d16fffc65ea102e21b16f0c22f64d5f"},"cell_type":"code","source":"# time based\ntrain['month'] = train['date'].dt.month\ntrain['day'] = train['date'].dt.day\ntrain['weekday'] = train['date'].dt.weekday\ntrain['weekofyear'] = train['date'].dt.weekofyear\n\ntrain['month_unique_user_count'] = train.groupby('month')['fullVisitorId'].transform('nunique')\ntrain['day_unique_user_count'] = train.groupby('day')['fullVisitorId'].transform('nunique')\ntrain['weekday_unique_user_count'] = train.groupby('weekday')['fullVisitorId'].transform('nunique')\ntrain['weekofyear_unique_user_count'] = train.groupby('weekofyear')['fullVisitorId'].transform('nunique')\n\ntest['month'] = test['date'].dt.month\ntest['day'] = test['date'].dt.day\ntest['weekday'] = test['date'].dt.weekday\ntest['weekofyear'] = test['date'].dt.weekofyear\n\ntest['month_unique_user_count'] = test.groupby('month')['fullVisitorId'].transform('nunique')\ntest['day_unique_user_count'] = test.groupby('day')['fullVisitorId'].transform('nunique')\ntest['weekday_unique_user_count'] = test.groupby('weekday')['fullVisitorId'].transform('nunique')\ntest['weekofyear_unique_user_count'] = test.groupby('weekofyear')['fullVisitorId'].transform('nunique')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9ee5c046e72b2064f55523fd081e3b66174422ab"},"cell_type":"code","source":"# device based\n\ntrain['browser_category'] = train['device.browser'] + '_' + train['device.deviceCategory']\ntrain['browser_operatingSystem'] = train['device.browser'] + '_' + train['device.operatingSystem']\n\ntest['browser_category'] = test['device.browser'] + '_' + test['device.deviceCategory']\ntest['browser_operatingSystem'] = test['device.browser'] + '_' + test['device.operatingSystem']\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"4a0f302e3f94514a0fd3f810f32ce1176fbb3aa4"},"cell_type":"code","source":"train['visitNumber'] = np.log1p(train['visitNumber'])\ntest['visitNumber'] = np.log1p(test['visitNumber'])\n\ntrain['totals.hits'] = np.log1p(train['totals.hits'])\ntest['totals.hits'] = np.log1p(test['totals.hits'].astype(int))\n\ntrain['totals.pageviews'] = np.log1p(train['totals.pageviews'].fillna(0))\ntest['totals.pageviews'] = np.log1p(test['totals.pageviews'].astype(float).fillna(0))\n\ntrain['sum_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('sum')\ntrain['count_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('count')\ntrain['mean_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('mean')\ntrain['sum_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('sum')\ntrain['count_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('count')\ntrain['mean_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('mean')\n\ntest['sum_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('sum')\ntest['count_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('count')\ntest['mean_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('mean')\ntest['sum_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('sum')\ntest['count_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('count')\ntest['mean_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('mean')\n\ntrain['mean_hits_per_day'] = train.groupby(['day'])['totals.hits'].transform('mean')\ntrain['sum_hits_per_day'] = train.groupby(['day'])['totals.hits'].transform('sum')\ntest['mean_hits_per_day'] = test.groupby(['day'])['totals.hits'].transform('mean')\ntest['sum_hits_per_day'] = test.groupby(['day'])['totals.hits'].transform('sum')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d2079b2b6ac0bb6ba70d8777585972950ffc0a4c"},"cell_type":"code","source":"train['sum_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('sum')\ntrain['count_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('count')\ntrain['mean_pageviews_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('mean')\n\ntrain['sum_pageviews_per_region'] = train.groupby('geoNetwork.region')['totals.pageviews'].transform('sum')\ntrain['count_pageviews_per_region'] = train.groupby('geoNetwork.region')['totals.pageviews'].transform('count')\ntrain['mean_pageviews_per_region'] = train.groupby('geoNetwork.region')['totals.pageviews'].transform('mean')\n\ntest['sum_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('sum')\ntest['count_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('count')\ntest['mean_pageviews_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('mean')\n\ntest['sum_pageviews_per_region'] = test.groupby('geoNetwork.region')['totals.pageviews'].transform('sum')\ntest['count_pageviews_per_region'] = test.groupby('geoNetwork.region')['totals.pageviews'].transform('count')\ntest['mean_pageviews_per_region'] = test.groupby('geoNetwork.region')['totals.pageviews'].transform('mean')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1622817de65df457b8a12a09dcf407065fd6e605"},"cell_type":"code","source":"train['sum_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('sum')\ntrain['count_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('count')\ntrain['mean_hits_per_network_domain'] = train.groupby('geoNetwork.networkDomain')['totals.hits'].transform('mean')\n\ntrain['sum_hits_per_region'] = train.groupby('geoNetwork.region')['totals.hits'].transform('sum')\ntrain['count_hits_per_region'] = train.groupby('geoNetwork.region')['totals.hits'].transform('count')\ntrain['mean_hits_per_region'] = train.groupby('geoNetwork.region')['totals.hits'].transform('mean')\n\ntrain['sum_hits_per_country'] = train.groupby('geoNetwork.country')['totals.hits'].transform('sum')\ntrain['count_hits_per_country'] = train.groupby('geoNetwork.country')['totals.hits'].transform('count')\ntrain['mean_hits_per_country'] = train.groupby('geoNetwork.country')['totals.hits'].transform('mean')\n\ntest['sum_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('sum')\ntest['count_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('count')\ntest['mean_hits_per_network_domain'] = test.groupby('geoNetwork.networkDomain')['totals.hits'].transform('mean')\n\ntest['sum_hits_per_region'] = test.groupby('geoNetwork.region')['totals.hits'].transform('sum')\ntest['count_hits_per_region'] = test.groupby('geoNetwork.region')['totals.hits'].transform('count')\ntest['mean_hits_per_region'] = test.groupby('geoNetwork.region')['totals.hits'].transform('mean')\n\ntest['sum_hits_per_country'] = test.groupby('geoNetwork.country')['totals.hits'].transform('sum')\ntest['count_hits_per_country'] = test.groupby('geoNetwork.country')['totals.hits'].transform('count')\ntest['mean_hits_per_country'] = test.groupby('geoNetwork.country')['totals.hits'].transform('mean')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"74f5437958bc11dde434a513239b104d017b8088"},"cell_type":"code","source":"train['user_pageviews_sum'] = train.groupby('fullVisitorId')['totals.pageviews'].transform('sum')\ntrain['user_hits_sum'] = train.groupby('fullVisitorId')['totals.hits'].transform('sum')\ntest['user_pageviews_sum'] = test.groupby('fullVisitorId')['totals.pageviews'].transform('sum')\ntest['user_hits_sum'] = test.groupby('fullVisitorId')['totals.hits'].transform('sum')\n\ntrain['user_pageviews_count'] = train.groupby('fullVisitorId')['totals.pageviews'].transform('count')\ntrain['user_hits_count'] = train.groupby('fullVisitorId')['totals.hits'].transform('count')\ntest['user_pageviews_count'] = test.groupby('fullVisitorId')['totals.pageviews'].transform('count')\ntest['user_hits_count'] = test.groupby('fullVisitorId')['totals.hits'].transform('count')\n\ntrain['user_pageviews_sum_to_mean'] = train['user_pageviews_sum'] / train['user_pageviews_sum'].mean()\ntrain['user_hits_sum_to_mean'] = train['user_hits_sum'] / train['user_hits_sum'].mean()\ntest['user_pageviews_sum_to_mean'] = test['user_pageviews_sum'] / test['user_pageviews_sum'].mean()\ntest['user_hits_sum_to_mean'] = test['user_hits_sum'] / test['user_hits_sum'].mean()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c0c1bdeee1e30b1eb766d785f5adce19498d0c42"},"cell_type":"code","source":"train['user_pageviews_to_region'] = train['user_pageviews_sum'] / train['mean_pageviews_per_region']\ntrain['user_hits_to_region'] = train['user_hits_sum'] / train['mean_hits_per_region']\n\ntest['user_pageviews_to_region'] = test['user_pageviews_sum'] / test['mean_pageviews_per_region']\ntest['user_hits_to_region'] = test['user_hits_sum'] / test['mean_hits_per_region']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0e56c9299685638d951a957a898f0ebe06023d93"},"cell_type":"markdown","source":"### Feature processing"},{"metadata":{"trusted":true,"_uuid":"32a91ae853c9487776f7d2b48821334a2e70035f"},"cell_type":"code","source":"num_cols = ['visitNumber', 'totals.hits', 'totals.pageviews', 'month_unique_user_count', 'day_unique_user_count', 'mean_hits_per_day'\n           'sum_pageviews_per_network_domain', 'sum_hits_per_network_domain', 'count_hits_per_network_domain', 'sum_hits_per_region',\n           'sum_hits_per_day', 'count_pageviews_per_network_domain', 'mean_pageviews_per_network_domain', 'weekday_unique_user_count',\n           'sum_pageviews_per_region', 'count_pageviews_per_region', 'mean_pageviews_per_region', 'user_pageviews_count', 'user_hits_count',\n           'count_hits_per_region', 'mean_hits_per_region', 'user_pageviews_sum', 'user_hits_sum', 'user_pageviews_sum_to_mean',\n            'user_hits_sum_to_mean', 'user_pageviews_to_region', 'user_hits_to_region', 'mean_pageviews_per_network_domain',\n           'mean_hits_per_network_domain']\n\nno_use = [\"visitNumber\", \"date\", \"fullVisitorId\", \"sessionId\", \"visitId\", \"visitStartTime\", 'totals.transactionRevenue', 'trafficSource.referralPath']\ncat_cols = [col for col in train.columns if col not in num_cols and col not in no_use]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"68f5dc8ea1ca30d36f37d43afa7ab0dccbd0ea70"},"cell_type":"code","source":"for col in cat_cols:\n    if col != 'trafficSource.campaignCode':\n        print(col)\n        lbl = LabelEncoder()\n        lbl.fit(list(train[col].values.astype('str')) + list(test[col].values.astype('str')))\n        train[col] = lbl.transform(list(train[col].values.astype('str')))\n        test[col] = lbl.transform(list(test[col].values.astype('str')))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3ffc62507a2adf81c8f2c1d4b9c65a7aac9dffe4"},"cell_type":"code","source":"no_use.append('trafficSource.campaignCode')\ntrain = train.sort_values('date')\nX = train.drop([col for col in no_use if col in train.columns], axis=1)\ny = train['totals.transactionRevenue']\nX_test = test.drop([col for col in no_use if col in test.columns], axis=1)\n# I use TimeSeriesSplit as we have time series\ntscv = TimeSeriesSplit(n_splits=5)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"baa3b9f864f6cb1f110f16890b94c8c398cdc051"},"cell_type":"markdown","source":"In fact it seems that it will take some time to find a good validation - TimeSeriesSplit gives a high variance in scores, so I'll try kfold for now."},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"58e6ff71d4671b1285c10a3d3ecfad927a9a95c7"},"cell_type":"code","source":"params = {\"objective\" : \"regression\", \"metric\" : \"rmse\", \"max_depth\": 12, \"min_child_samples\": 20, \"reg_alpha\": 0.1, \"reg_lambda\": 0.1,\n        \"num_leaves\" : 1024, \"learning_rate\" : 0.01, \"subsample\" : 0.9, \"colsample_bytree\" : 0.9, \"subsample_freq \": 10}\nn_fold = 10\nfolds = KFold(n_splits=n_fold, shuffle=False, random_state=42)\n# Cleaning and defining parameters for LGBM\nmodel = lgb.LGBMRegressor(**params, n_estimators = 20000, nthread = 4, n_jobs = -1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f54e71dc0a539034e45332efe9fbe3be8e3c367b"},"cell_type":"code","source":"prediction = np.zeros(test.shape[0])\n\nfor fold_n, (train_index, test_index) in enumerate(folds.split(X)):\n    print('Fold:', fold_n)\n    #print(f'Train samples: {len(train_index)}. Valid samples: {len(test_index)}')\n    X_train, X_valid = X.iloc[train_index], X.iloc[test_index]\n    y_train, y_valid = y.iloc[train_index], y.iloc[test_index]\n    \n\n    model.fit(X_train, y_train, \n            eval_set=[(X_train, y_train), (X_valid, y_valid)], eval_metric='rmse',\n            verbose=500, early_stopping_rounds=100)\n    \n    y_pred = model.predict(X_test, num_iteration=model.best_iteration_)\n    prediction += y_pred\nprediction /= n_fold","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"691716d64502a3c43fd4fe826682ea59a7dc78dc"},"cell_type":"code","source":"lgb.plot_importance(model, max_num_features=30);","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"32273ab8daa2740e385fb356d447c88a20ffe0a3"},"cell_type":"markdown","source":"Previously I made a submission following the officially suggested logic: calculating logarithm of sum of predicted revenues. But some people say that it is better to simply submit the sum of predicted values (which are already logarithms). Let's try!"},{"metadata":{"trusted":true,"_uuid":"12ff6144f64ee25fc8bc0e0b65b6bd9574423811"},"cell_type":"code","source":"test[\"PredictedLogRevenue\"] = prediction\nsub = test.groupby(\"fullVisitorId\").agg({\"PredictedLogRevenue\" : \"sum\"}).reset_index()\nsub[\"PredictedLogRevenue\"] = np.log1p(sub[\"PredictedLogRevenue\"])\nsub[\"PredictedLogRevenue\"] = sub[\"PredictedLogRevenue\"].apply(lambda x : 0.0 if x < 0 else x)\nsub[\"PredictedLogRevenue\"] = sub[\"PredictedLogRevenue\"].fillna(0.0)\nsub.to_csv(\"lgb.csv\", index=False)\n\n\n# from this kernel: https://www.kaggle.com/fabiendaniel/lgbm-rf-starter-lb-1-70\n#submission = test[['fullVisitorId']].copy()\n#submission.loc[:, 'PredictedLogRevenue'] = np.expm1(predictions)\n#submission.loc[:, 'PredictedLogRevenue'] = prediction\n#submission[\"PredictedLogRevenue\"] = submission[\"PredictedLogRevenue\"].apply(lambda x : 0.0 if x < 0 else x)\n#submission[\"PredictedLogRevenue\"] = submission[\"PredictedLogRevenue\"].fillna(0.0)\n#grouped_test = submission[['fullVisitorId', 'PredictedLogRevenue']].groupby('fullVisitorId').sum().reset_index()\n#grouped_test[\"PredictedLogRevenue\"] = np.log1p(grouped_test[\"PredictedLogRevenue\"])\n#grouped_test.to_csv('lgb.csv',index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5a3f58295a8b1110c2d0d3cdca84dc21d9b0abb5"},"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}