{"cells":[{"metadata":{"_uuid":"a4176bdb948cbe9f4ac9375d537bcb5bd1d9e543"},"cell_type":"markdown","source":"# Used in this kernel:\n* preprocessed dataset by olivier https://www.kaggle.com/ogrellier/create-extracted-json-fields-dataset\n* exported google analytics data https://www.kaggle.com/satian/exported-google-analytics-data\n\n\n* story of a leak v.01 https://www.kaggle.com/igauty/story-of-a-leak-v01\n* future is here https://www.kaggle.com/ashishpatel26/future-is-here\n* teach-lightgbm-to-sum-predictions https://www.kaggle.com/satian/story-of-a-leak/notebook\n* Thanks for karkun : https://www.kaggle.com/karkun/sergey-ivanov-msu-mmp/\n\n\nThe code below is full of bugs, which I didn't remove as they seem to improve LB."},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:20.374807Z","start_time":"2018-10-14T10:14:18.330773Z"},"trusted":true,"_uuid":"b016d39d81044ee10dc2db1e96effe796ab436c7"},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\n\n# DRAGONS\nimport xgboost as xgb\nimport lightgbm as lgb\nimport catboost as cat\n\n# plots\nimport seaborn as sns\nimport matplotlib.pyplot as plt\n%matplotlib inline\n\n# pandas / plt options\npd.options.display.max_columns = 999\nplt.rcParams['figure.figsize'] = (14, 7)\nfont = {'family' : 'verdana',\n        'weight' : 'bold',\n        'size'   : 14}\nplt.rc('font', **font)\n\n# remove warnings\nimport warnings\nwarnings.simplefilter(\"ignore\")\n\n# garbage collector\nimport gc\ngc.enable()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dbc90dc52990c378a98005c2e07b8acc9a48e820"},"cell_type":"markdown","source":"# Loading data"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:30.266681Z","start_time":"2018-10-14T10:14:20.378792Z"},"trusted":true,"_uuid":"7f6b083fcf773198924ff26d53f2d39ed1ec2dd5"},"cell_type":"code","source":"train = pd.read_csv('../input/create-extracted-json-fields-dataset/extracted_fields_train.gz', dtype={'date': str, 'fullVisitorId': str, 'sessionId':str, 'visitId': np.int64})\ntest = pd.read_csv('../input/create-extracted-json-fields-dataset/extracted_fields_test.gz', dtype={'date': str, 'fullVisitorId': str, 'sessionId':str, 'visitId': np.int64})\ntrain.shape, test.shape","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:30.297664Z","start_time":"2018-10-14T10:14:30.267681Z"},"trusted":true,"_uuid":"12834063b05cfd19329161414b0ebdd071213c7e"},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:30.396606Z","start_time":"2018-10-14T10:14:30.299663Z"},"trusted":true,"_uuid":"7001881e9cdfc9638e1e168ce1a97ffa1a6ee437"},"cell_type":"code","source":"train.columns","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:30.550531Z","start_time":"2018-10-14T10:14:30.397605Z"},"trusted":true,"_uuid":"f1d568be7bbf52ac066399d17c17cc44f0dd69ea"},"cell_type":"code","source":"# Getting data from leak\ntrain_store_1 = pd.read_csv('../input/exported-google-analytics-data/Train_external_data.csv', low_memory=False, skiprows=6, dtype={\"Client Id\":'str'})\ntrain_store_2 = pd.read_csv('../input/exported-google-analytics-data/Train_external_data_2.csv', low_memory=False, skiprows=6, dtype={\"Client Id\":'str'})\ntest_store_1 = pd.read_csv('../input/exported-google-analytics-data/Test_external_data.csv', low_memory=False, skiprows=6, dtype={\"Client Id\":'str'})\ntest_store_2 = pd.read_csv('../input/exported-google-analytics-data/Test_external_data_2.csv', low_memory=False, skiprows=6, dtype={\"Client Id\":'str'})","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:30.621476Z","start_time":"2018-10-14T10:14:30.551531Z"},"trusted":true,"_uuid":"46f2d38576a9bccb01f09bdd2a167c779bbaaeb9"},"cell_type":"code","source":"# Getting VisitId from Google Analytics...\nfor df in [train_store_1, train_store_2, test_store_1, test_store_2]:\n    df[\"visitId\"] = df[\"Client Id\"].apply(lambda x: x.split('.', 1)[1]).astype(np.int64)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:36.446327Z","start_time":"2018-10-14T10:14:30.623475Z"},"trusted":true,"_uuid":"0a2c78bc947a4442398175ca79258d506afb1856"},"cell_type":"code","source":"# Merge with train/test data\ntrain = train.merge(pd.concat([train_store_1, train_store_2], sort=False), how=\"left\", on=\"visitId\")\ntest = test.merge(pd.concat([test_store_1, test_store_2], sort=False), how=\"left\", on=\"visitId\")\n\n# Drop Client Id\nfor df in [train, test]:\n    df.drop(\"Client Id\", 1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:36.452308Z","start_time":"2018-10-14T10:14:36.447328Z"},"trusted":true,"_uuid":"84b797e0506d46d08c178c42495cae71a4dbdf1b"},"cell_type":"code","source":"train.columns","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:37.910365Z","start_time":"2018-10-14T10:14:36.608218Z"},"trusted":true,"_uuid":"acd4760138ba9a70f2341d5f51395afcce94fea0"},"cell_type":"code","source":"# Cleaning Revenue\nfor df in [train, test]:\n    df[\"Revenue\"].fillna('$', inplace=True)\n    df[\"Revenue\"] = df[\"Revenue\"].apply(lambda x: x.replace('$', '').replace(',', ''))\n    df[\"Revenue\"] = pd.to_numeric(df[\"Revenue\"], errors=\"coerce\")\n    df[\"Revenue\"].fillna(0.0, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:37.956337Z","start_time":"2018-10-14T10:14:37.911363Z"},"trusted":true,"_uuid":"41c897be1747a5e6e90b54ea1307993870f3aa3d"},"cell_type":"code","source":"for df in [train_store_1, train_store_2, test_store_1, test_store_2]:\n    del df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"heading_collapsed":true,"_uuid":"119d7b06bb71fb519a943d0c11e447a170d916bb"},"cell_type":"markdown","source":"# Looking around"},{"metadata":{"hidden":true,"_uuid":"21f6e7a41c2316f62138cf9349c341d6ca3ad8a3"},"cell_type":"markdown","source":"Some pictures to have in mind: target distribution"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:39.624803Z","start_time":"2018-10-14T10:14:37.957337Z"},"hidden":true,"trusted":true,"_uuid":"328715e736f64240b00cd7ee1a3fb69bb5cdcdc0"},"cell_type":"code","source":"target_sums = train.groupby(\"fullVisitorId\")[\"totals.transactionRevenue\"].sum().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:44.420456Z","start_time":"2018-10-14T10:14:39.625788Z"},"hidden":true,"trusted":true,"_uuid":"9a2625a3b7c28ac417ba1172f63d308e12dfd581"},"cell_type":"code","source":"plt.figure(figsize=(20,6))\nplt.scatter(range(target_sums.shape[0]), np.sort(np.log1p(target_sums[\"totals.transactionRevenue\"].values)), color = \"#ff4500\")\nplt.xlabel('index')\nplt.ylabel('TransactionRevenue')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-07T17:19:33.369729Z","start_time":"2018-10-07T17:19:33.366722Z"},"hidden":true,"_uuid":"2721d8feb6f34473500c413e68d450a26cf248ff"},"cell_type":"markdown","source":"Key problem:"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:46.039836Z","start_time":"2018-10-14T10:14:44.421442Z"},"hidden":true,"trusted":true,"_uuid":"df2413f1c01615949d9ca3e6be92c354a737975d"},"cell_type":"code","source":"train.date = pd.to_datetime(train.date, format=\"%Y%m%d\")\ntest.date = pd.to_datetime(test.date, format=\"%Y%m%d\")\nplt.figure(figsize=(20,6))\ntrain.date.value_counts().sort_index().plot(label=\"train\", color = \"#FFD700\")\ntest.date.value_counts().sort_index().plot(label=\"test\", color = \"#48D1CC\")\nplt.legend()","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"_uuid":"7bad2cb96ee8759029c2673274ebafea874ab019"},"cell_type":"markdown","source":"Comparing categories in train and test:"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:46.414251Z","start_time":"2018-10-14T10:14:46.040835Z"},"hidden":true,"trusted":true,"_uuid":"d01fd047ebf1846878180feed9ec7727d86eb5c9"},"cell_type":"code","source":"palette = sns.color_palette(\"bright\")\n\n# Use palplot and pass in the variable:\n# sns.palplot(palette)\n\ndef drawBars(columnname):\n    plt.figure(figsize=(20,6))\n    sns.barplot(x=\"count\", y=\"index\", hue=\"dataset\",data=pd.melt(pd.concat([train[columnname].value_counts().rename(\"train\"), \n                                                                            test[columnname].value_counts().rename(\"test\")], axis=1, sort=\"False\").reset_index(), \n                                                                 id_vars=\"index\", var_name=\"dataset\", value_name=\"count\"),palette=palette)\n\ndrawBars(\"channelGrouping\")","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:46.749189Z","start_time":"2018-10-14T10:14:46.415238Z"},"hidden":true,"trusted":true,"_uuid":"c74de2f8e3ec4d25deae73dd6717a821c0368bc9"},"cell_type":"code","source":"drawBars(\"geoNetwork.continent\")","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:47.335654Z","start_time":"2018-10-14T10:14:46.751174Z"},"hidden":true,"trusted":true,"_uuid":"11be97e1e52ade3a4c65cb0133d23f24e5fa0c7f"},"cell_type":"code","source":"ids_train = set(train.fullVisitorId.unique())\nids_test = set(test.fullVisitorId.unique())\nprint(\"Unique visitor ids in train:\", len(ids_train))\nprint(\"Unique visitor ids in test:\", len(ids_test))\nprint(\"Common visitors in train and test:\", len(ids_train & ids_test))","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"_uuid":"656995db4dd09f60e3dcd6f96bed21267c2ee200"},"cell_type":"markdown","source":"Weird \"double\" sessions:"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:48.731726Z","start_time":"2018-10-14T10:14:47.336636Z"},"hidden":true,"trusted":true,"_uuid":"c21eed82c1eb34e88d016ebf83611f9093458bfa"},"cell_type":"code","source":"problem = train[train.sessionId.map(train.sessionId.value_counts() == 2)].sort_values([\"sessionId\", 'visitStartTime'])\nproblem.head(10)","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"_uuid":"4e397738f34d9c4c05735755ed06900d943e2c83"},"cell_type":"markdown","source":"VisitStartTime seems to be same thing as visitId... yet not always!"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:48.753719Z","start_time":"2018-10-14T10:14:48.732712Z"},"hidden":true,"trusted":true,"_uuid":"d1da4c6151bb7383022c400ac4f4fd5b5a8d8be2"},"cell_type":"code","source":"plt.figure(figsize=(20,6))\n(train.visitStartTime == train.visitId).value_counts()\n(train.visitStartTime == train.visitId).value_counts().plot.barh(color = [\"orange\",\"green\"])","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"_uuid":"095aa23508ed949ebdd2116a290c960ee5c1d5c4"},"cell_type":"markdown","source":"Suspicious simultaneous visitors with same visitorId and same visitStartTime:"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:53.691564Z","start_time":"2018-10-14T10:14:48.754700Z"},"hidden":true,"trusted":true,"_uuid":"8c46911f4f61272464a1adfa00a29b4c54b2d472"},"cell_type":"code","source":"train.loc[pd.to_datetime(train.visitStartTime, unit='s') == \"2017-04-25 18:49:35\"].head(8)","execution_count":null,"outputs":[]},{"metadata":{"hidden":true,"_uuid":"62bd7555d3071836e871bfd3056916375d4a84ae"},"cell_type":"markdown","source":"Seems to be a serious problem:"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:54.071763Z","start_time":"2018-10-14T10:14:53.692566Z"},"hidden":true,"trusted":true,"_uuid":"5dc9f41d1d93edcee46d9d3cd73be75334c42c24"},"cell_type":"code","source":"print(\"Train: \", np.bincount(train.visitId.value_counts()))","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:14:54.199694Z","start_time":"2018-10-14T10:14:54.072751Z"},"hidden":true,"trusted":true,"_uuid":"8aca51ed171fffc4b5745422ebfbd934f1fcf2a0"},"cell_type":"code","source":"print(\"test: \", np.bincount(test.visitId.value_counts()))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"17aff2e8a7b671707d33e013bfed5978d3d689f1"},"cell_type":"markdown","source":"# Preprocessing"},{"metadata":{"_uuid":"943bf363df8a37af86aeb46eec0cd8a9db078bf7"},"cell_type":"markdown","source":"Setting time as index and saving time as feature (for FE purposes only)"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:15:03.164776Z","start_time":"2018-10-14T10:14:54.200678Z"},"trusted":true,"_uuid":"3ed4a7cd86cef5f945e2e57d1c29affd8aec46d6"},"cell_type":"code","source":"train.visitStartTime = pd.to_datetime(train.visitStartTime, unit='s')\ntest.visitStartTime = pd.to_datetime(test.visitStartTime, unit='s')\ntrain[\"date\"] = train.visitStartTime\ntest[\"date\"] = test.visitStartTime","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:15:04.170198Z","start_time":"2018-10-14T10:15:03.165734Z"},"trusted":true,"_uuid":"37086f27593fe6ffae04bba4016f1e2250e8d6bd"},"cell_type":"code","source":"train.set_index(\"visitStartTime\", inplace=True)\ntest.set_index(\"visitStartTime\", inplace=True)\ntrain.sort_index(inplace=True)\ntest.sort_index(inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fe8ceb2681656261dd7fcd114a8f102817cf3aaa"},"cell_type":"markdown","source":"Clearing rare categories and setting 0 to NaNs:"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:15:04.263164Z","start_time":"2018-10-14T10:15:04.171179Z"},"trusted":true,"_uuid":"9fb6867cb20933891f4f1d5fbfe210bedadea973"},"cell_type":"code","source":"def clearRare(columnname, limit = 1000):\n    # you may search for rare categories in train, train&test, or just test\n    #vc = pd.concat([train[columnname], test[columnname]], sort=False).value_counts()\n    vc = test[columnname].value_counts()\n    \n    common = vc > limit\n    common = set(common.index[common].values)\n    print(\"Set\", sum(vc <= limit), columnname, \"categories to 'other';\", end=\" \")\n    \n    train.loc[train[columnname].map(lambda x: x not in common), columnname] = 'other'\n    test.loc[test[columnname].map(lambda x: x not in common), columnname] = 'other'\n    print(\"now there are\", train[columnname].nunique(), \"categories in train\")","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:15:06.666037Z","start_time":"2018-10-14T10:15:04.264177Z"},"trusted":true,"_uuid":"f701fb7ac0896957145e44b1e827019d75b51ea9"},"cell_type":"code","source":"train.fillna(0, inplace=True)\ntest.fillna(0, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:15:14.334775Z","start_time":"2018-10-14T10:15:06.667037Z"},"trusted":true,"_uuid":"a695f7c5574559cde287a13717e45fb2d2cfbf3e"},"cell_type":"code","source":"clearRare(\"device.browser\")\nclearRare(\"device.operatingSystem\")\nclearRare(\"geoNetwork.country\")\nclearRare(\"geoNetwork.city\")\nclearRare(\"geoNetwork.metro\")\nclearRare(\"geoNetwork.networkDomain\")\nclearRare(\"geoNetwork.region\")\nclearRare(\"geoNetwork.subContinent\")\nclearRare(\"trafficSource.adContent\")\nclearRare(\"trafficSource.campaign\")\nclearRare(\"trafficSource.keyword\")\nclearRare(\"trafficSource.medium\")\nclearRare(\"trafficSource.referralPath\")\nclearRare(\"trafficSource.source\")","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:15:26.177042Z","start_time":"2018-10-14T10:15:19.256035Z"},"trusted":true,"_uuid":"8fe894ddc03426c5478af9e6d0434b4979dd9a26"},"cell_type":"code","source":"# Clearing leaked data:\nfor df in [train, test]:\n    df[\"Avg. Session Duration\"][df[\"Avg. Session Duration\"] == 0] = \"00:00:00\"\n    df[\"Avg. Session Duration\"] = df[\"Avg. Session Duration\"].str.split(':').apply(lambda x: int(x[0]) * 60 + int(x[1]))\n    df[\"Bounce Rate\"] = df[\"Bounce Rate\"].astype(str).apply(lambda x: x.replace('%', '')).astype(float)\n    df[\"Goal Conversion Rate\"] = df[\"Goal Conversion Rate\"].astype(str).apply(lambda x: x.replace('%', '')).astype(float)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2b7f3a3afa9484311f41ad94445c47f538d05159"},"cell_type":"markdown","source":"# Features"},{"metadata":{"_uuid":"68e22002e956f21dbcdd067827bbf52b108aac0b"},"cell_type":"markdown","source":"Based on strange things in dataset:"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:15:55.506837Z","start_time":"2018-10-14T10:15:52.159667Z"},"trusted":true,"_uuid":"3f1a255e21cbee7ae45c1fe652cf4ae23bb4f86a"},"cell_type":"code","source":"for df in [train, test]:\n    # remember these features were equal, but not always? May be it means something...\n    df[\"id_incoherence\"] = pd.to_datetime(df.visitId, unit='s') != df.date\n    # remember visitId dublicates?\n    df[\"visitId_dublicates\"] = df.visitId.map(df.visitId.value_counts())\n    # remember session dublicates?\n    df[\"session_dublicates\"] = df.sessionId.map(df.sessionId.value_counts())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"37f5047dce936f96a127e277d918e7ddfe615551"},"cell_type":"markdown","source":"Basic time features:"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:15:56.543232Z","start_time":"2018-10-14T10:15:55.507819Z"},"trusted":true,"_uuid":"073ccfadc886fdde9da3a780f61aff53bfc52f29"},"cell_type":"code","source":"for df in [train, test]:\n    df['weekday'] = df['date'].dt.dayofweek.astype(object)\n    df['time'] = df['date'].dt.second + df['date'].dt.minute*60 + df['date'].dt.hour*3600\n    #df['month'] = df['date'].dt.month   # it must not be included in features during learning!\n    df['day'] = df['date'].dt.date       # it must not be included in features during learning!","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b83208cb7e0f7c9b86c52bfd12c5148b5325d52f"},"cell_type":"markdown","source":"Looking to future features (from https://www.kaggle.com/ashishpatel26/future-is-here):"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:16:18.857190Z","start_time":"2018-10-14T10:15:59.656227Z"},"trusted":true,"_uuid":"68c9f7aa5d7138e5dde3facbb3b4e6799781a4bd"},"cell_type":"code","source":"df = pd.concat([train, test])\ndf.sort_values(['fullVisitorId', 'date'], ascending=True, inplace=True)\ndf['prev_session'] = (df['date'] - df[['fullVisitorId', 'date']].groupby('fullVisitorId')['date'].shift(1)).astype(np.int64) // 1e9 // 60 // 60\ndf['next_session'] = (df['date'] - df[['fullVisitorId', 'date']].groupby('fullVisitorId')['date'].shift(-1)).astype(np.int64) // 1e9 // 60 // 60\ndf.sort_index(inplace=True)\n\ntrain = df[:len(train)]\ntest = df[len(train):]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7e73108b18143f336249bfa7eccc0528efc9a9d5"},"cell_type":"markdown","source":"Paired categories from \"teach-lightgbm-to-sum-predictions\" kernel"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:16:41.590190Z","start_time":"2018-10-14T10:16:39.896553Z"},"trusted":true,"_uuid":"10d1600e3defb6ff55155f6f8e951f3dd028552b"},"cell_type":"code","source":"for df in [train, test]:\n    df['source.country'] = df['trafficSource.source'] + '_' + df['geoNetwork.country']\n    df['campaign.medium'] = df['trafficSource.campaign'] + '_' + df['trafficSource.medium']\n    df['browser.category'] = df['device.browser'] + '_' + df['device.deviceCategory']\n    df['browser.os'] = df['device.browser'] + '_' + df['device.operatingSystem']","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:16:52.339513Z","start_time":"2018-10-14T10:16:41.591753Z"},"trusted":true,"_uuid":"58bb8b85f89863602b6467195cc00aee0a0ab52d"},"cell_type":"code","source":"for df in [train, test]:\n    df['device_deviceCategory_channelGrouping'] = df['device.deviceCategory'] + \"_\" + df['channelGrouping']\n    df['channelGrouping_browser'] = df['device.browser'] + \"_\" + df['channelGrouping']\n    df['channelGrouping_OS'] = df['device.operatingSystem'] + \"_\" + df['channelGrouping']\n    \n    for i in ['geoNetwork.city', 'geoNetwork.continent', 'geoNetwork.country','geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region','geoNetwork.subContinent']:\n        for j in ['device.browser','device.deviceCategory', 'device.operatingSystem', 'trafficSource.source']:\n            df[i + \"_\" + j] = df[i] + \"_\" + df[j]\n    \n    df['content.source'] = df['trafficSource.adContent'].astype(str) + \"_\" + df['source.country']\n    df['medium.source'] = df['trafficSource.medium'] + \"_\" + df['source.country']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ae67bc3d2938e76ac7d7c9f11dd5bf70d0cdcdbd"},"cell_type":"markdown","source":"User-aggregating features:"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:17:35.164422Z","start_time":"2018-10-14T10:16:52.340484Z"},"scrolled":true,"trusted":true,"_uuid":"27514d21f0fc7a9dad33ec270d839477254d43f9"},"cell_type":"code","source":"for feature in [\"totals.hits\", \"totals.pageviews\"]:\n    info = pd.concat([train, test], sort=False).groupby(\"fullVisitorId\")[feature].mean()\n    train[\"usermean_\" + feature] = train.fullVisitorId.map(info)\n    test[\"usermean_\" + feature] = test.fullVisitorId.map(info)\n    \nfor feature in [\"visitNumber\"]:\n    info = pd.concat([train, test], sort=False).groupby(\"fullVisitorId\")[feature].max()\n    train[\"usermax_\" + feature] = train.fullVisitorId.map(info)\n    test[\"usermax_\" + feature] = test.fullVisitorId.map(info)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dbf5236e34eba96d3dc50b7a1a80d202a64f56bf"},"cell_type":"markdown","source":"# Encoding features"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:17:35.245363Z","start_time":"2018-10-14T10:17:35.166407Z"},"trusted":true,"_uuid":"78ae6da4f2949dc31b194ad718afc21ce7e589d0"},"cell_type":"code","source":"excluded = ['date', 'fullVisitorId', 'sessionId', 'totals.transactionRevenue', 'visitId', 'visitStartTime', \n            'month', 'day', 'help']\n\ncat_cols = [f for f in train.columns if (train[f].dtype == 'object' and f not in excluded)]\nreal_cols = [f for f in train.columns if (not f in cat_cols and f not in excluded)]","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:17:39.712707Z","start_time":"2018-10-14T10:17:35.246376Z"},"scrolled":true,"trusted":true,"_uuid":"3d86a3019a0ec353e3d0200d4c4ff7cc852b07f2"},"cell_type":"code","source":"train[cat_cols].nunique()","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:28:54.550611Z","start_time":"2018-10-14T10:22:17.008542Z"},"trusted":true,"_uuid":"076160aba3c0147983bbf2c7f67c59564cc4d5b7"},"cell_type":"code","source":"from sklearn.preprocessing import LabelEncoder\nfor col in cat_cols:\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":{"ExecuteTime":{"end_time":"2018-10-14T10:28:55.236215Z","start_time":"2018-10-14T10:28:54.551593Z"},"trusted":true,"_uuid":"38279bd60b601e107acb4e80135728475642349d"},"cell_type":"code","source":"for col in real_cols:\n    train[col] = train[col].astype(float)\n    test[col] = test[col].astype(float)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:28:56.395339Z","start_time":"2018-10-14T10:28:55.237198Z"},"trusted":true,"_uuid":"c4970a0e547b1a62a3d8ab1f00ff32fdf4680d22"},"cell_type":"code","source":"train[real_cols + cat_cols].head()","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:28:56.900050Z","start_time":"2018-10-14T10:28:56.396338Z"},"trusted":true,"_uuid":"13e4f6dde59ace24a87c92eacce5665732eddab6"},"cell_type":"code","source":"for to_del in [\"date\", \"sessionId\", \"visitId\", \"day\"]:\n    del train[to_del]\n    del test[to_del]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3c064d11d02eea63a2f8ecc52c5d61870e0ddc69"},"cell_type":"markdown","source":"# Preparing validation"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:31:00.099098Z","start_time":"2018-10-14T10:31:00.089103Z"},"trusted":true,"_uuid":"29d31db304283a23cb88b8618882b7fae081be6d"},"cell_type":"code","source":"excluded = ['date', 'fullVisitorId', 'sessionId', 'totals.transactionRevenue', 'visitId', 'visitStartTime', \"month\", \"help\"]\n\ncat_cols = [f for f in train.columns if (train[f].dtype == 'int64' and f not in excluded)]\nreal_cols = [f for f in train.columns if (not f in cat_cols and f not in excluded)]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fad0e919bf09d72e0b0064cd058bfe11bf7620a9"},"cell_type":"markdown","source":"Function to tell us the score using the metric we actually care about"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:31:00.209041Z","start_time":"2018-10-14T10:31:00.101097Z"},"trusted":true,"_uuid":"f9467d334a330a5a06debbe0636b190c7a36bf87"},"cell_type":"code","source":"from sklearn.metrics import mean_squared_error\ndef score(data, y):\n    validation_res = pd.DataFrame(\n    {\"fullVisitorId\": data[\"fullVisitorId\"].values,\n     \"transactionRevenue\": data[\"totals.transactionRevenue\"].values,\n     \"predictedRevenue\": np.expm1(y)})\n\n    validation_res = validation_res.groupby(\"fullVisitorId\")[\"transactionRevenue\", \"predictedRevenue\"].sum().reset_index()\n    return np.sqrt(mean_squared_error(np.log1p(validation_res[\"transactionRevenue\"].values), \n                                     np.log1p(validation_res[\"predictedRevenue\"].values)))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"36f6b217c775d8f4b49739ba578003d2b23cb6a4"},"cell_type":"markdown","source":"Cute function to validate and prepare stacking"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:31:00.326979Z","start_time":"2018-10-14T10:31:00.210038Z"},"trusted":true,"_uuid":"3e9b9136e4afdc96cfa7ed89ea2cd70d356334f8"},"cell_type":"code","source":"from sklearn.model_selection import GroupKFold\n\nclass KFoldValidation():\n    def __init__(self, data, n_splits=5):\n        unique_vis = np.array(sorted(data['fullVisitorId'].astype(str).unique()))\n        folds = GroupKFold(n_splits)\n        ids = np.arange(data.shape[0])\n        \n        self.fold_ids = []\n        for trn_vis, val_vis in folds.split(X=unique_vis, y=unique_vis, groups=unique_vis):\n            self.fold_ids.append([\n                    ids[data['fullVisitorId'].astype(str).isin(unique_vis[trn_vis])],\n                    ids[data['fullVisitorId'].astype(str).isin(unique_vis[val_vis])]\n                ])\n            \n    def validate(self, train, test, features, model, name=\"\", prepare_stacking=False, \n                 fit_params={\"early_stopping_rounds\": 50, \"verbose\": 100, \"eval_metric\": \"rmse\"}):\n        model.FI = pd.DataFrame(index=features)\n        full_score = 0\n        \n        if prepare_stacking:\n            test[name] = 0\n            train[name] = np.NaN\n        \n        for fold_id, (trn, val) in enumerate(self.fold_ids):\n            devel = train[features].iloc[trn]\n            y_devel = np.log1p(train[\"totals.transactionRevenue\"].iloc[trn])\n            valid = train[features].iloc[val]\n            y_valid = np.log1p(train[\"totals.transactionRevenue\"].iloc[val])\n                       \n            print(\"Fold \", fold_id, \":\")\n            model.fit(devel, y_devel, eval_set=[(valid, y_valid)], **fit_params)\n            \n            if len(model.feature_importances_) == len(features):  # some bugs in catboost?\n                model.FI['fold' + str(fold_id)] = model.feature_importances_ / model.feature_importances_.sum()\n\n            predictions = model.predict(valid)\n            predictions[predictions < 0] = 0\n            print(\"Fold \", fold_id, \" error: \", mean_squared_error(y_valid, predictions)**0.5)\n            \n            fold_score = score(train.iloc[val], predictions)\n            full_score += fold_score / len(self.fold_ids)\n            print(\"Fold \", fold_id, \" score: \", fold_score)\n            \n            if prepare_stacking:\n                train[name].iloc[val] = predictions\n                \n                test_predictions = model.predict(test[features])\n                test_predictions[test_predictions < 0] = 0\n                test[name] += test_predictions / len(self.fold_ids)\n                \n        print(\"Final score: \", full_score)\n        return full_score","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:31:06.926033Z","start_time":"2018-10-14T10:31:00.328983Z"},"trusted":true,"_uuid":"873e39a4fbb0b39c4ca817018f57f91799e8cfb4"},"cell_type":"code","source":"Kfolder = KFoldValidation(train)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:31:06.932030Z","start_time":"2018-10-14T10:31:06.928032Z"},"trusted":true,"_uuid":"627dfd643eeea3343a832bf49132775ad0cf8de6"},"cell_type":"code","source":"lgbmodel = lgb.LGBMRegressor(n_estimators=1500, objective=\"regression\", metric=\"rmse\", num_leaves=31, min_child_samples=100,\n                      learning_rate=0.03, bagging_fraction=0.75, feature_fraction=0.55, bagging_frequency=6, \n                      bagging_seed=2019, subsample=.9, colsample_bytree=.9, use_best_model=True)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:34:23.651287Z","start_time":"2018-10-14T10:31:06.934028Z"},"scrolled":false,"trusted":true,"_uuid":"866753ac2ec4083463f35dc10f1871298006bfb0"},"cell_type":"code","source":"Kfolder.validate(train, test, real_cols + cat_cols, lgbmodel, \"lgbpred\", prepare_stacking=True)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:34:31.126837Z","start_time":"2018-10-14T10:34:30.869974Z"},"trusted":true,"_uuid":"3d42fd6591f49a3bbffaf319513f3f954b77ea7d"},"cell_type":"code","source":"lgbmodel.FI.mean(axis=1).sort_values()[:30].plot(kind=\"barh\")","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-09T17:50:33.536413Z","start_time":"2018-10-09T17:50:33.533401Z"},"_uuid":"5f563afd19474f6085298bf1e6edd64cb69d0155"},"cell_type":"markdown","source":"# User-level"},{"metadata":{"_uuid":"cc16253b1fa723956648d89b19908a79e27a3102"},"cell_type":"markdown","source":"Make one user one object:\n* all features are averaged\n* we hope, that categorical features do not change for one user (that's not true :/ )\n* categoricals labels are averaged (!!!) and are treated as numerical features (o_O)\n* predictions are averaged in multiple ways..."},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:36:09.827135Z","start_time":"2018-10-14T10:36:09.821150Z"},"trusted":true,"_uuid":"9b9693648513555c5f0688af651581c7c192d581"},"cell_type":"code","source":"def create_user_df(df):\n    agg_data = df[real_cols + cat_cols + ['fullVisitorId']].groupby('fullVisitorId').mean()\n    \n    pred_list = df[['fullVisitorId', 'lgbpred']].groupby('fullVisitorId').apply(lambda visitor_df: list(visitor_df.lgbpred))\\\n        .apply(lambda x: {'pred_'+str(i): pred for i, pred in enumerate(x)})\n    all_predictions = pd.DataFrame(list(pred_list.values), index=agg_data.index)\n    feats = all_predictions.columns\n\n    all_predictions['t_mean'] = all_predictions.mean(axis=1)\n    all_predictions['t_median'] = all_predictions.median(axis=1)   # including t_mean as one of the elements? well, ok\n    all_predictions['t_sum_log'] = all_predictions.sum(axis=1)\n    all_predictions['t_sum_act'] = all_predictions.fillna(0).sum(axis=1)\n    all_predictions['t_nb_sess'] = all_predictions.isnull().sum(axis=1)\n\n    full_data = pd.concat([agg_data, all_predictions], axis=1).astype(float)\n    full_data['fullVisitorId'] = full_data.index\n    del agg_data, all_predictions\n    gc.collect()\n    return full_data","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:39:14.694931Z","start_time":"2018-10-14T10:36:11.373146Z"},"trusted":true,"_uuid":"c00b7107b4a9069f9be7d466c3c523cf880037ea"},"cell_type":"code","source":"user_train = create_user_df(train)\nuser_test = create_user_df(test)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:39:16.454615Z","start_time":"2018-10-14T10:39:14.696930Z"},"trusted":true,"_uuid":"3c2550e5680a06aa78525cc6fac57e70fb288f6b"},"cell_type":"code","source":"features = list(user_train.columns)[:-1]  # don't include \"fullVisitorId\"\nuser_train[\"totals.transactionRevenue\"] = train[['fullVisitorId', 'totals.transactionRevenue']].groupby('fullVisitorId').sum()","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:39:30.672677Z","start_time":"2018-10-14T10:39:30.651678Z"},"trusted":true,"_uuid":"2cb410a63ac86d08117421bdcee2c32ffce980a5"},"cell_type":"code","source":"for f in features:\n    if f not in user_test.columns:\n        user_test[f] = np.nan","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c1e15413cbb73c6e74948e91f173dfc8fb2db093"},"cell_type":"markdown","source":"# Meta-models"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:39:44.481353Z","start_time":"2018-10-14T10:39:38.085163Z"},"trusted":true,"_uuid":"3161efd586a3bc6421c92b16192e7d768105f008"},"cell_type":"code","source":"Kfolder = KFoldValidation(user_train)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:39:44.488349Z","start_time":"2018-10-14T10:39:44.482352Z"},"trusted":true,"_uuid":"31cac11992e6c8704766d6224c355d796dba4642"},"cell_type":"code","source":"lgbmodel = lgb.LGBMRegressor(n_estimators=1500, objective=\"regression\", metric=\"rmse\", num_leaves=31, min_child_samples=100,\n                      learning_rate=0.03, bagging_fraction=0.7, feature_fraction=0.55, bagging_frequency=6, \n                      bagging_seed=2019, subsample=.9, colsample_bytree=.9, use_best_model=True)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:42:33.819663Z","start_time":"2018-10-14T10:39:44.490358Z"},"scrolled":false,"trusted":true,"_uuid":"682665e7749f8378bd90ba78adf265fabde8a146"},"cell_type":"code","source":"Kfolder.validate(user_train, user_test, features, lgbmodel, name=\"lgbfinal\", prepare_stacking=True)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T10:42:33.825471Z","start_time":"2018-10-14T10:42:33.820663Z"},"trusted":true,"_uuid":"97c4de57ccfeea68d93ac407ea931d7ce422f894"},"cell_type":"code","source":"xgbmodel = xgb.XGBRegressor(max_depth=22, learning_rate=0.02, n_estimators=1000, \n                                         objective='reg:linear', gamma=1.45, seed=2019, silent=False,\n                                        subsample=0.67, colsample_bytree=0.054, colsample_bylevel=0.50)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T11:05:28.635869Z","start_time":"2018-10-14T10:42:33.827470Z"},"scrolled":true,"trusted":true,"_uuid":"26bfa011883360279934e61cad8d2f2a6ee1ccf9"},"cell_type":"code","source":"Kfolder.validate(user_train, user_test, features, xgbmodel, name=\"xgbfinal\", prepare_stacking=True)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T11:05:28.655836Z","start_time":"2018-10-14T11:05:28.638846Z"},"trusted":true,"_uuid":"a69f5b9029548458fc8e6378fc7fac1e2a46ac3e"},"cell_type":"code","source":"catmodel = cat.CatBoostRegressor(iterations=500, learning_rate=0.2, depth=5, random_seed=2019)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T11:10:36.794158Z","start_time":"2018-10-14T11:05:28.657835Z"},"scrolled":true,"trusted":true,"_uuid":"97fb40a1e6e6fb9d67d0202a9a5df781dcf591b5"},"cell_type":"code","source":"Kfolder.validate(user_train, user_test, features, catmodel, name=\"catfinal\", prepare_stacking=True,\n                fit_params={\"use_best_model\": True, \"verbose\": 100})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ca522c27232b2b6e77795a49847e98712b58ccbf"},"cell_type":"markdown","source":"# Ensembling dragons"},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T11:10:38.041351Z","start_time":"2018-10-14T11:10:36.796149Z"},"scrolled":true,"trusted":true,"_uuid":"63e9c6b72d17bc1a95cf0d43b1fddb3ba4a69da7"},"cell_type":"code","source":"user_train['PredictedLogRevenue'] = 0.5 * user_train[\"lgbfinal\"] + \\\n                                    0.1 * user_train[\"xgbfinal\"] + \\\n                                    0.4 * user_train[\"catfinal\"]\nscore(user_train, user_train.PredictedLogRevenue)","execution_count":null,"outputs":[]},{"metadata":{"ExecuteTime":{"end_time":"2018-10-14T11:10:42.362447Z","start_time":"2018-10-14T11:10:38.042333Z"},"trusted":true,"_uuid":"8458bf6e73d51551b3a9414583acf4d23138eb0b"},"cell_type":"code","source":"user_test['PredictedLogRevenue'] = 0.5 * user_test[\"lgbfinal\"] +  0.4 * user_test[\"catfinal\"] + 0.1 * user_test[\"xgbfinal\"]\nuser_test[['PredictedLogRevenue']].to_csv('leak_check_submission.csv', index=True)","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"},"latex_envs":{"LaTeX_envs_menu_present":true,"autoclose":false,"autocomplete":true,"bibliofile":"biblio.bib","cite_by":"apalike","current_citInitial":1,"eqLabelWithNumbers":true,"eqNumInitial":1,"hotkeys":{"equation":"Ctrl-E","itemize":"Ctrl-I"},"labels_anchors":false,"latex_user_defs":false,"report_style_numbering":false,"user_envs_cfg":false},"notify_time":"10"},"nbformat":4,"nbformat_minor":1}