{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\nimport os\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.\nprint(os.listdir(\"../input\"))\n\nimport json\nfrom pandas.io.json import json_normalize\n\nimport matplotlib.pyplot as plt\nfrom sklearn import preprocessing\nfrom sklearn.model_selection import cross_val_score\nfrom sklearn.metrics import mean_absolute_error\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.model_selection import cross_val_score\nfrom sklearn.metrics import mean_squared_error\n\nimport lightgbm as lgb\nimport xgboost as xgb\nfrom catboost import CatBoostRegressor","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5e23cb36f38939474524651088a6de828bced060"},"cell_type":"markdown","source":"# 1. Import and Preprocess the Data#"},{"metadata":{"trusted":true,"_uuid":"f4e3a98a69f61cdba797f53bfbe5610a25096258"},"cell_type":"code","source":"def rmse(y_true, y_pred):\n    return round(np.sqrt(mean_squared_error(y_true, y_pred)), 5)\n\ndef load_df(path, nrows=None):\n    json_columns = ['device','geoNetwork','totals','trafficSource']\n    \n    df = pd.read_csv(path,\n                     #make sure that the json in csv will be converted as dict, otherwise, it will be string\n                     converters = {json_column: json.loads for json_column in json_columns},\n\n                     #make sure 'fullVisitorId' is string\n                     dtype = {'fullVisitorId':'str'},\n                     nrows=nrows)\n\n    for json_column in json_columns:\n        #conver the dict as a dataframe\n        converted_df = json_normalize(df[json_column])\n\n        #format the name, f'{json_column}.{subcolumn}' = '{}.{}'.format(json_column,subcolumn)\n        converted_df.columns = [f'{json_column}.{subcolumn}' for subcolumn in converted_df.columns]\n\n        #remove the origin columns that are dict, and add the dataframe made of the new columns, keep both the ids\n        df = df.drop(json_column, axis = 1).merge(converted_df, right_index=True, left_index=True)\n        \n    print(f'file:{path},shape:{df.shape}')\n    #output the loaded and processed df\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"79da91aec8f698bfc3d2cf0690860c98ecb07aaa"},"cell_type":"code","source":"%%time\ntrain_df = load_df('../input/train.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a6f4119d270549285cedb864eb32ad99d34f1969"},"cell_type":"code","source":"%%time\ntest_df = load_df('../input/test.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"50e3de6bcf4857f490891d51d069e16ada07bf66"},"cell_type":"code","source":"print('There are 2 columns that are in training set but not in the test set. They are',set(train_df.columns).difference(set(test_df.columns)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cdf3c0e9fa5423f6c278175dc2cb0b3e84af3a40"},"cell_type":"code","source":"#nunique() will return the unique numbers of a column, by default, dropna = False, but here, we make it True, because we don't want to ignore any null values\nunhelpful_columns = []\n\nfor column in train_df.columns:\n    if train_df[column].nunique(dropna = False) == 1:\n        unhelpful_columns.append(column)\n\nprint('There are',train_df.shape[1],'columns.',len(unhelpful_columns),'of them have identical values for all rows. And they are as follows:')\nprint(unhelpful_columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"62ca2d9fcf08b748a86e90919122489c471635c7"},"cell_type":"code","source":"#drop the unhelpful columns\nunhelpful_columns.append('trafficSource.campaignCode')\nfor column in unhelpful_columns:\n    if column in train_df.columns:\n        train_df = train_df.drop(column, axis = 1)\n    if column in test_df.columns:\n        test_df = test_df.drop(column, axis = 1)\nprint(train_df.shape,test_df.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1b3cb194a60705e21d3b776f768d59f8d609c979"},"cell_type":"code","source":"#prepare the y_train\ny_train = train_df[['totals.transactionRevenue']].fillna(0.0)\ny_train['totals.transactionRevenue'] = np.log1p(y_train['totals.transactionRevenue'].astype('float'))\ntrain_df = train_df.drop('totals.transactionRevenue',axis = 1)\nprint(type(y_train),train_df.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"54a12a451e3d32957f5035f252e9f2c55aee4579"},"cell_type":"code","source":"test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bd5881bf7cb5398a3db00e47fab9799fe39d5de2"},"cell_type":"code","source":"#process the date and time\ntrain_df.visitStartTime = pd.to_datetime(train_df.visitStartTime, unit='s')\ntest_df.visitStartTime = pd.to_datetime(test_df.visitStartTime, unit='s')\ntrain_df[\"date\"] = train_df.visitStartTime\ntest_df[\"date\"] = test_df.visitStartTime","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a162fc1336c3e47b598f6a6efb247a7ddcd9523b"},"cell_type":"code","source":"#add weekday, time and day info\nfor df in [train_df, test_df]:\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['hour'] = df['date'].dt.hour\n    df['month'] = df['date'].dt.month   # it must not be included in features during learning!\n    df['day'] = df['date'].dt.date  ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d6661cebe4020f60545d09c1b326c54f5448f0a6"},"cell_type":"code","source":"#add some combined feature\nfor df in [train_df, test_df]:\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":{"trusted":true,"_uuid":"01d8dd6a676e3a2b444b50475fe815260c812a35"},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"10465d2c0b576066e1c938d132f304ea1b362e26"},"cell_type":"code","source":"for df in [train_df, test_df]:\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":{"trusted":true,"_uuid":"33207e4aeb7ec12db35a9f91e7a2145dfb3e166a"},"cell_type":"code","source":"test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"209a10537c95712e8a0bc7fac099878fa549ad7e"},"cell_type":"code","source":"#convert numeric data to floats or boolean\ndef convert_data_format(df):\n    #numeric values\n    df['totals.hits'] = df['totals.hits'].astype('float').fillna(0.0)\n    df['totals.pageviews'] = df['totals.pageviews'].astype('float').fillna(0.0)\n    df['visitNumber'] = df['visitNumber'].astype('float')\n    df['trafficSource.adwordsClickInfo.page'] = df['trafficSource.adwordsClickInfo.page'].astype('float').fillna(0.0)\n    #boolean values\n    df['totals.bounces'] = df['totals.bounces'].fillna(0.0).astype('bool')\n    df['totals.newVisits'] = df['totals.newVisits'].fillna(0.0).astype('bool')\n    df['trafficSource.isTrueDirect'] = df['trafficSource.isTrueDirect'].fillna('False')\n    df['trafficSource.adwordsClickInfo.isVideoAd'] = df['trafficSource.adwordsClickInfo.isVideoAd'].fillna(True)\n    return df\ntrain_df = convert_data_format(train_df)\ntest_df = convert_data_format(test_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"909fb00c31e60f8f48c82fce3b88e44038c77f32"},"cell_type":"code","source":"#get the mean of hits and pageviews,and the max visit number then add these features to each user\nfor feature in [\"totals.hits\", \"totals.pageviews\"]:\n    alldata = pd.concat([train_df, test_df], sort=False)\n    alldata_id = alldata.groupby(\"fullVisitorId\")[feature].mean()\n    train_df[\"usermean_\" + feature] = train_df.fullVisitorId.map(alldata_id)\n    train_df[\"normal\" + feature] = (train_df[feature] - min(alldata[feature]))/(max(alldata[feature]) - min(alldata[feature]))\n    \n    test_df[\"usermean_\" + feature] = test_df.fullVisitorId.map(alldata_id)\n    test_df[\"normal\" + feature] = (test_df[feature] - min(alldata[feature]))/(max(alldata[feature]) - min(alldata[feature]))\n    \nfor feature in [\"visitNumber\"]:\n    info = pd.concat([train_df, test_df], sort=False).groupby(\"fullVisitorId\")[feature].max()\n    train_df[\"usermax_\" + feature] = train_df.fullVisitorId.map(info)\n    test_df[\"usermax_\" + feature] = test_df.fullVisitorId.map(info)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4392940564abec6fb06e5d06d40f92dc791b6769"},"cell_type":"code","source":"train_df.tail(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b6ce3f32c37e642fe38c32aaad06b86782becc59"},"cell_type":"code","source":"train_df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6f1dd2bfd9f76afe8a746f62cc0e89a4dd3ff72d"},"cell_type":"code","source":"#based on the training set analyasis, we trimmed out the rows in test_df that is confirmed zero revenue\nbounce_buy_yes = test_df['totals.bounces']!=1.0\nbounce_buy_no = test_df['totals.bounces']==1.0\n\nhit_buy_yes = test_df['totals.hits']!=1.0\nhit_buy_no = test_df['totals.hits']==1.0\n\npageview_buy_yes = test_df['totals.pageviews']>1.0\npageview_buy_no = test_df['totals.pageviews']<2.0\n\n# useful_broswer = ['Firefox','Chrome','Edge','Internet Explorer','Safari','Amazon Silk','Opera','Safari (in-app)','Android Webview']\n# useful_browser_yes = test_df['device.browser'].isin(useful_broswer)\n# useful_browser_no = ~test_df['device.browser'].isin(useful_broswer)\n\n# newly added filters\n# useful_OS = ['Chrome OS','Macintosh','Windows','Linux','Android','Windows Phone','iOS']\n# useful_OS_yes = test_df['device.operatingSystem'].isin(useful_OS)\n# useful_OS_no = ~test_df['device.operatingSystem'].isin(useful_OS)\n\n#nousedful_subContinent = ['Polynesia','Middle Africa','Micronesian Region','Melanesia']\n#useful_subContinent_yes = ~test_df['geoNetwork.subContinent'].isin(nousedful_subContinent)\n#useful_subContinent_no = test_df['geoNetwork.subContinent'].isin(nousedful_subContinent)\n\n#adwordsClickInfo_page_yes = test_df['trafficSource.adwordsClickInfo.page']<2.0\n#adwordsClickInfo_page_no = test_df['trafficSource.adwordsClickInfo.page']>1.0\n\n# adNetworkType_yes = test_df['trafficSource.adwordsClickInfo.adNetworkType'] != 'Search partners'\n# adNetworkType_no = test_df['trafficSource.adwordsClickInfo.adNetworkType'] == 'Search partners'\n\n# nouseful_campaign = ['AW - Electronics','All Products','Data Share']\n# useful_campaign_yes = ~test_df['trafficSource.campaign'].isin(nouseful_campaign)\n# useful_campaign_no = test_df['trafficSource.campaign'].isin(nouseful_campaign)\n\ntest_df_trimmed = test_df[bounce_buy_yes & hit_buy_yes & pageview_buy_yes] # & adwordsClickInfo_page_yes & useful_browser_yes & useful_subContinent_yes& useful_OS_yes &\n                         # & adNetworkType_yes & useful_campaign_yes]\ntest_df_dropped = test_df[bounce_buy_no | hit_buy_no | pageview_buy_no] # | adwordsClickInfo_page_no| useful_browser_no | useful_subContinent_no| useful_OS_no |\n                         # | adNetworkType_no | useful_campaign_no]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"564bdc8e758a9252a57940d71960827fb108d68f"},"cell_type":"code","source":"print(test_df.shape[0],test_df_trimmed.shape[0]+ test_df_dropped.shape[0], test_df_trimmed.shape[0], test_df_dropped.shape[0])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"822027a04acdf9184be29cb4c5e6373dd84b552e"},"cell_type":"code","source":"test_df.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0ef8be59959b4258b202fed0beeb8be239ddea95"},"cell_type":"code","source":"#process the catgory values\ncat_cols = [\"channelGrouping\", \"device.browser\", \n            \"device.deviceCategory\", \"device.operatingSystem\", \n            \"geoNetwork.city\", \"geoNetwork.continent\", \n            \"geoNetwork.country\", \"geoNetwork.metro\",\n            \"geoNetwork.networkDomain\", \"geoNetwork.region\", \n            \"geoNetwork.subContinent\", \"trafficSource.adContent\", \n            \"trafficSource.adwordsClickInfo.adNetworkType\", \n            \"trafficSource.adwordsClickInfo.gclId\", \n            \"trafficSource.adwordsClickInfo.page\", \n            \"trafficSource.adwordsClickInfo.slot\", \"trafficSource.campaign\",\n            \"trafficSource.keyword\", \"trafficSource.medium\", \n            \"trafficSource.referralPath\", \"trafficSource.source\",\n            \"trafficSource.adwordsClickInfo.isVideoAd\", \"trafficSource.isTrueDirect\",#below is the newly generated features,\n            'weekday','hour','month',\n            \"source.country\",\"campaign.medium\", \"browser.category\", \"browser.os\",\n            'content.source','medium.source','device_deviceCategory_channelGrouping', 'channelGrouping_browser','channelGrouping_OS',\n            'geoNetwork.subContinent_device.deviceCategory',\n            'geoNetwork.subContinent_device.operatingSystem',\n            'geoNetwork.subContinent_trafficSource.source']\n#             'geoNetwork.city_device.browser','geoNetwork.city_device.deviceCategory','geoNetwork.city_device.operatingSystem','geoNetwork.city_trafficSource.source',\n#             'geoNetwork.country_device.browser','geoNetwork.country_device.deviceCategory','geoNetwork.country_device.operatingSystem','geoNetwork.country_trafficSource.source'\n#             'geoNetwork.country_trafficSource.source',\n#             'device_deviceCategory_channelGrouping', 'channelGrouping_browser',\n#             'channelGrouping_OS', 'geoNetwork.city_device.browser',\n#             'geoNetwork.city_device.deviceCategory',\n#             'geoNetwork.city_device.operatingSystem',\n#             'geoNetwork.city_trafficSource.source',\n#             'geoNetwork.continent_device.browser',\n#             'geoNetwork.continent_device.deviceCategory',\n#             'geoNetwork.continent_device.operatingSystem',\n#             'geoNetwork.continent_trafficSource.source',\n#             'geoNetwork.country_device.browser',\n#             'geoNetwork.country_device.deviceCategory',\n#             'geoNetwork.country_device.operatingSystem',\n#             'geoNetwork.country_trafficSource.source',\n#             'geoNetwork.metro_device.browser',\n#             'geoNetwork.metro_device.deviceCategory',\n#             'geoNetwork.metro_device.operatingSystem',\n#             'geoNetwork.metro_trafficSource.source',\n#             'geoNetwork.networkDomain_device.browser',\n#             'geoNetwork.networkDomain_device.deviceCategory',\n#             'geoNetwork.networkDomain_device.operatingSystem',\n#             'geoNetwork.networkDomain_trafficSource.source',\n#             'geoNetwork.region_device.browser',\n#             'geoNetwork.region_device.deviceCategory',\n#             'geoNetwork.region_device.operatingSystem',\n#             'geoNetwork.region_trafficSource.source',\n#             'geoNetwork.subContinent_device.browser',\n#             'geoNetwork.subContinent_device.deviceCategory',\n#             'geoNetwork.subContinent_device.operatingSystem',\n#             'geoNetwork.subContinent_trafficSource.source', 'content.source',\n#             'medium.source'] \n\n#category values\nfor col in cat_cols:\n    lbl = preprocessing.LabelEncoder()\n    lbl.fit(list(train_df[col].values.astype('str')) + list(test_df_trimmed[col].values.astype('str')) + list(test_df_dropped[col].values.astype('str')))\n    train_df[col] = lbl.transform(list(train_df[col].values.astype('str')))\n    test_df_trimmed[col] = lbl.transform(list(test_df_trimmed[col].values.astype('str')))\n    test_df_dropped[col] = lbl.transform(list(test_df_dropped[col].values.astype('str')))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bb87492e8798edf7afb8c6c8e6165f5a82a6212a"},"cell_type":"code","source":"print('Now, the training set has',train_df.shape[0],'rows','the total test set has',test_df.shape[0],'or',test_df_trimmed.shape[0]+ test_df_dropped.shape[0],'rows.',test_df_trimmed.shape[0],'rows are left after trimming; while', test_df_dropped.shape[0],'are dropped. There are',test_df.shape[1],'columns in each dataset.')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ef01c6aa5a6aa2045a29140dea8d7da5ee0ead21"},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a4fbc0dc035409538d1ab28d16a03af930c2101c"},"cell_type":"markdown","source":"Output the processed data."},{"metadata":{"trusted":true,"_uuid":"ba264fed2cf1fb8b887ea70829e417998b1caf35"},"cell_type":"code","source":"train_df.to_csv('train_df.csv')\ntest_df_trimmed.to_csv('test_df_trimmed.csv')\ntest_df_dropped.to_csv('test_df_dropped.csv')\ny_train.to_csv('y_train.csv')","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}