{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# Import libraries \nimport pandas as pd \nimport numpy as np\nimport os\nimport time\nfrom dask import dataframe as dd\nimport json\nfrom pandas import json_normalize\nfrom datetime import datetime\nfrom datetime import timedelta\nimport csv\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.preprocessing import OrdinalEncoder, StandardScaler, MinMaxScaler, OneHotEncoder\nfrom sklearn.compose import ColumnTransformer\nfrom sklearn.metrics import mean_squared_error, mean_absolute_error, r2_score, explained_variance_score \nimport lightgbm as lgb\n\npd.set_option('display.max_columns', 500)\npd.set_option('display.max_rows', 100)","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2021-08-16T08:14:43.157935Z","iopub.execute_input":"2021-08-16T08:14:43.158373Z","iopub.status.idle":"2021-08-16T08:14:45.338131Z","shell.execute_reply.started":"2021-08-16T08:14:43.158282Z","shell.execute_reply":"2021-08-16T08:14:45.337003Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Brief task description and approach of it’s solving\n- The dataset is too big to read it in one piece with Pandas, so Dask was used to read the initial data.\n- The goal is to predict the sum of transactions per user during two months period. Initial data is in the context of user’s visits, so we need to do grouping per user.\n- In the train set we need to recreate the same conditions as for the test set: data available for 168 days, 46 days of gap and the target is the sum of transactions per user (for users in the data for 168 days) during the 62 days after gap period. \n- To construct the training data following steps were performed:\n    - concatenate training and test sets (since testing data contains all necessary columns, this way we can increase volume of training data);\n    - find first date in the combined dataset and select 4 non-overlapping periods of 168 days each;\n    - calculate target for each of four 168 days periods – find unique Visitor IDs of users in the period, select data for 62 days after 46 days gap for these users and sum up transactions’ revenue per user for 62 days. For users who are not found in 62 days period set target as 0. \n- The trick that was used in winner’s solution is using model stacking by predicting probability that user returns during 62 days period. In regression model training we use data only for returned users. Final prediction is made by multiplying probability that user returns to the revenue prediction made by regression model. ","metadata":{}},{"cell_type":"markdown","source":"# 1. Read the data","metadata":{}},{"cell_type":"markdown","source":"In the hidden cells we define a function to read the data and parse json columns. You can view it by pressing \"Show hidden code\".","metadata":{}},{"cell_type":"code","source":"def parse_json(file_path, file_name):\n    start = time.time()\n    custom_date_parser = lambda x: datetime.strptime(x, \"%Y%m%d\")\n\n    with open(file_path, 'r') as f:\n        header = next(csv.reader(f))\n        \n    df = dd.read_csv(file_path, \n                  parse_dates=['date'],\n                  date_parser=custom_date_parser,\n                  usecols = list(set(header) - {'hits'}),\n                  dtype={'fullVisitorId':'str'})\n    \n    df['Region'] = df.customDimensions.map(lambda x: x.split(\"'value': '\")[-1][0:-3])\n    df = df.drop('customDimensions', axis=1)\n    \n    print('Starting parsing JSON columns')\n    # Parse columns with JSON values and add them as separate column to the main table\n    JSON_columns = ['device', 'geoNetwork', 'totals', 'trafficSource']\n\n    for col in JSON_columns:\n        df[col] = df[col].map(lambda x: json.loads(x), meta=('','object'))\n        new_df = json_normalize(df[col])\n        new_df.columns = [f\"{col}.{subcolumn}\" for subcolumn in new_df.columns]\n        df = df.merge(new_df, right_index=True, left_index=True)\n        df = df.drop(col, axis=1)\n    \n    print('Finished parsing JSON columns')\n    # These columns have the same values, so it's decided to drop them\n    df = df.drop(['device.browserVersion', 'device.browserSize', 'device.operatingSystemVersion', \n         'device.mobileDeviceBranding', 'device.mobileDeviceModel', 'device.mobileInputSelector',\n        'device.mobileDeviceInfo', 'device.mobileDeviceMarketingName', 'device.flashVersion',\n        'device.language', 'device.screenColors', 'device.screenResolution', 'geoNetwork.cityId', \n         'geoNetwork.networkLocation', 'trafficSource.adwordsClickInfo.criteriaParameters', 'geoNetwork.latitude', \n        'geoNetwork.longitude', 'socialEngagementType'], axis=1)\n    \n    df=df.compute()\n    df.to_csv('{}_before_aggregating.csv'.format(file_name), index=False)\n    end = time.time()\n    print('Time spent on processing {} mins'.format((end - start)/60))\n    return df","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2021-08-16T08:14:45.339443Z","iopub.execute_input":"2021-08-16T08:14:45.339729Z","iopub.status.idle":"2021-08-16T08:14:45.35302Z","shell.execute_reply.started":"2021-08-16T08:14:45.339702Z","shell.execute_reply":"2021-08-16T08:14:45.351815Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# These cells are commented since reading the files and parsing JSON columns takes a lot of time\n# In the cell below we read the result of function run from .csv file\n\n#test_raw = parse_json('/kaggle/input/ga-customer-revenue-prediction/test_v2.csv', file_name='test_raw')\n#train_raw = parse_json('/kaggle/input/ga-customer-revenue-prediction/train_v2.csv', file_name='train_raw')","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Read the result of running the function \"parse_json\"\ncustom_date_parser = lambda x: datetime.strptime(x, \"%Y-%m-%d\")\n\ntest_raw = pd.read_csv('/kaggle/input/ga-competition-processed-data/test_raw_before_aggregating.csv', \n                  parse_dates=['date'],\n                  date_parser=custom_date_parser,                 \n                  dtype={'fullVisitorId':'str'})\n\ntrain_raw = pd.read_csv('/kaggle/input/ga-competition-processed-data/train_raw_before_aggregating.csv', \n                  parse_dates=['date'],\n                  date_parser=custom_date_parser,                 \n                  dtype={'fullVisitorId':'str'})","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:14:45.35471Z","iopub.execute_input":"2021-08-16T08:14:45.355053Z","iopub.status.idle":"2021-08-16T08:15:29.890305Z","shell.execute_reply.started":"2021-08-16T08:14:45.354987Z","shell.execute_reply":"2021-08-16T08:15:29.889482Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2. Brief EDA","metadata":{}},{"cell_type":"code","source":"# Combine datsets received on the previous step\ndf = pd.concat([train_raw, test_raw])","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:15:29.891729Z","iopub.execute_input":"2021-08-16T08:15:29.89214Z","iopub.status.idle":"2021-08-16T08:15:31.816177Z","shell.execute_reply.started":"2021-08-16T08:15:29.892111Z","shell.execute_reply":"2021-08-16T08:15:31.815313Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Set necessary values as null values\nNulls = ['(not set)', 'not available in demo dataset', '(not provided)', \n         'unknown.unknown', '/', 'Not Socially Engaged', 'not set', '(none)']\nfor null in Nulls:    \n    df.replace(null, np.nan, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:15:31.817241Z","iopub.execute_input":"2021-08-16T08:15:31.817658Z","iopub.status.idle":"2021-08-16T08:16:06.231587Z","shell.execute_reply.started":"2021-08-16T08:15:31.817627Z","shell.execute_reply":"2021-08-16T08:16:06.230532Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('Check proportion of null values')\nprint(round(df.isna().sum()/len(df)*100),0)","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:31:13.111794Z","iopub.execute_input":"2021-08-16T08:31:13.112174Z","iopub.status.idle":"2021-08-16T08:31:16.599522Z","shell.execute_reply.started":"2021-08-16T08:31:13.112143Z","shell.execute_reply":"2021-08-16T08:31:16.598376Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# We have rows with not null transactions and null revenue\n# We will assume that this could happen in reality, for example, because of promo campaigns.\ndf[(df['totals.transactionRevenue'].isna())&(df['totals.transactions'].notnull())][['totals.transactionRevenue', 'totals.transactions']].head(10)","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:16:11.409914Z","iopub.execute_input":"2021-08-16T08:16:11.410231Z","iopub.status.idle":"2021-08-16T08:16:11.440251Z","shell.execute_reply.started":"2021-08-16T08:16:11.4102Z","shell.execute_reply":"2021-08-16T08:16:11.439231Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check values count for columns where number of unique values <10\nfor i in df.columns:\n    if df[i].nunique()<10:\n        print ('\\033[1m'+i+':'+'\\033[0m')\n        print(df[i].value_counts())","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:34:46.42372Z","iopub.execute_input":"2021-08-16T08:34:46.424089Z","iopub.status.idle":"2021-08-16T08:34:57.122912Z","shell.execute_reply.started":"2021-08-16T08:34:46.424057Z","shell.execute_reply":"2021-08-16T08:34:57.12191Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Convert necessary columns to numerical to apply aggregation functions during grouping on the next step\n\ndef convert_to_num(df):\n    # Fill missing values where necessary\n    df['totals.bounces'] = df['totals.bounces'].fillna(0).astype('int')\n    df['totals.newVisits'] = df['totals.newVisits'].fillna(0).astype('int')\n    df['trafficSource.isTrueDirect'] = df['trafficSource.isTrueDirect'].fillna(False).astype('bool')\n    df['trafficSource.adwordsClickInfo.isVideoAd'] = df['trafficSource.adwordsClickInfo.isVideoAd'].astype('bool')\n    # Convert to numeric\n    col_to_numeric = ['totals.timeOnSite', 'totals.hits', 'totals.pageviews', 'totals.sessionQualityDim', 'totals.transactions', 'totals.transactionRevenue', \n                    'totals.totalTransactionRevenue', 'totals.visits']\n    for col in col_to_numeric:\n        df[col] = df[col].astype('float')\n    return (df)","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:16:22.663589Z","iopub.execute_input":"2021-08-16T08:16:22.663873Z","iopub.status.idle":"2021-08-16T08:16:22.670884Z","shell.execute_reply.started":"2021-08-16T08:16:22.663846Z","shell.execute_reply":"2021-08-16T08:16:22.669907Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Convert columns to numeric\ndf = convert_to_num(df)\ntest_raw = convert_to_num(test_raw)","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:16:22.672051Z","iopub.execute_input":"2021-08-16T08:16:22.672374Z","iopub.status.idle":"2021-08-16T08:16:24.92239Z","shell.execute_reply.started":"2021-08-16T08:16:22.672331Z","shell.execute_reply":"2021-08-16T08:16:24.92142Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3. Construct train set by analogy of test set and group by VisitorID","metadata":{}},{"cell_type":"markdown","source":"In the hidden cells we create a dictionary of columns to group with aggregation functions and define a function to construct training set from grouped data for necessary periods.","metadata":{}},{"cell_type":"code","source":"group_dict = {\n            'geoNetwork.networkDomain': [('networkDomain', lambda x: x.dropna().max())],\n            'geoNetwork.city': [('city', lambda x: x.dropna().max())],\n            'geoNetwork.metro': [('metro', lambda x: x.dropna().max())],\n            'geoNetwork.region': [('geo_region', lambda x: x.dropna().max())],\n            'geoNetwork.country': [('country', lambda x: x.dropna().max())],\n            'geoNetwork.continent': [('continent', lambda x: x.dropna().max())],\n            'geoNetwork.subContinent': [('subContinent', lambda x: x.dropna().max())],\n\n            'device.operatingSystem': [('operatingSystem', lambda x: x.dropna().max())],\n            'device.isMobile': [('isMobile', lambda x: x.dropna().mean())],\n            'device.deviceCategory': [('deviceCategory', lambda x: x.dropna().max())],\n            'device.browser': [('browser', lambda x: x.dropna().max())],\n        \n            'trafficSource.source': [('source', lambda x: x.dropna().max())],\n            'trafficSource.medium': [('medium', lambda x: x.dropna().max())],\n            'trafficSource.isTrueDirect': [('isTrueDirect', lambda x: x.dropna().mean())],\n            'trafficSource.adwordsClickInfo.isVideoAd': [('isVideoAd', lambda x: x.dropna().mean())],\n    \n            'channelGrouping': [('channelGrouping', lambda x: x.dropna().max())],\n            'date': [('first_ses_from_the_period_start', lambda x: x.dropna().min() - df_mindate),\n                     ('last_ses_from_the_period_end', lambda x: df_maxdate - x.dropna().max()),\n                     ('interval_dates', lambda x: x.dropna().max() - x.dropna().min()),\n                     ('unqiue_date_num', lambda x: len(set(x.dropna())) )], \n            'visitNumber': [('visitNumber', lambda x: x.dropna().max())],\n            'visitStartTime': [('visitStartTime', lambda x: x.dropna().count())],\n            'Region': [('Region', lambda x: x.dropna().max())],\n        \n            'totals.bounces': [('bounces', lambda x: x.dropna().sum())],\n            'totals.timeOnSite': [('timeOnSite_sum', lambda x: x.dropna().sum()),\n                                  ('timeOnSite_min', lambda x: x.dropna().min()), \n                                  ('timeOnSite_max', lambda x: x.dropna().max()),\n                                  ('timeOnSite_mean', lambda x: x.dropna().mean()),\n                                 ('timeOnSite_median', lambda x: x.dropna().median()),\n                                 ('timeOnSite_std', lambda x: np.std(x.dropna()))],\n            'totals.pageviews': [('pageviews_sum', lambda x: x.dropna().sum()),\n                                 ('pageviews_min', lambda x: x.dropna().min()), \n                                 ('pageviews_max', lambda x: x.dropna().max()),\n                                 ('pageviews_mean', lambda x: x.dropna().mean()),\n                                ('pageviews_median', lambda x: x.dropna().median()),\n                                ('pageviews_std', lambda x: np.std(x.dropna()))],\n            'totals.hits': [('hits_sum', lambda x: x.dropna().sum()), \n                            ('hits_min', lambda x: x.dropna().min()), \n                            ('hits_max', lambda x: x.dropna().max()), \n                            ('hits_mean', lambda x: x.dropna().mean()),\n                           ('hits_median', lambda x: x.dropna().median()),\n                           ('hits_std', lambda x: np.std(x.dropna()))],\n            'totals.sessionQualityDim': [('sessionQualityDim_sum', lambda x: x.dropna().sum()),\n                                        ('sessionQualityDim_mean', lambda x: x.dropna().mean()),\n                                        ('sessionQualityDim_median', lambda x: x.dropna().median()),\n                                        ('sessionQualityDim_min', lambda x: x.dropna().min()),\n                                        ('sessionQualityDim_max', lambda x: x.dropna().max()),\n                                        ('sessionQualityDim_std', lambda x: np.std(x.dropna()))],\n            'totals.newVisits': [('newVisits', lambda x: x.dropna().max())],\n            'totals.transactionRevenue':  [('transactionRevenue_sum', lambda x:x.dropna().sum()),\n                                          ('transactionRevenue_mean', lambda x:x.dropna().mean()),\n                                          ('transactionRevenue_median', lambda x:x.dropna().median()),\n                                          ('transactionRevenue_min', lambda x:x.dropna().min()),\n                                          ('transactionRevenue_max', lambda x:x.dropna().max()),\n                                          ('transactionRevenue_std', lambda x: np.std(x.dropna()))],\n            'totals.totalTransactionRevenue':  [('totalTransactionRevenue_max', lambda x:x.dropna().max())],\n            'totals.transactions' : [('transactions_sum', lambda x:x.dropna().sum()),\n                                    ('transactions_mean', lambda x:x.dropna().mean()),\n                                    ('transactions_median', lambda x:x.dropna().median()),\n                                    ('transactions_min', lambda x:x.dropna().min()),\n                                    ('transactions_max', lambda x:x.dropna().max()),\n                                    ('transactions_std', lambda x:np.std(x.dropna()))]    \n}","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2021-08-16T08:16:24.923582Z","iopub.execute_input":"2021-08-16T08:16:24.923859Z","iopub.status.idle":"2021-08-16T08:16:24.954047Z","shell.execute_reply.started":"2021-08-16T08:16:24.923832Z","shell.execute_reply":"2021-08-16T08:16:24.952869Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def getTimeFramewithFeatures(tr, k=1):\n    start = time.time()\n    tf = tr.loc[(tr['date'] >= min(tr['date']) + timedelta(days=168*(k-1))) \n              & (tr['date'] < min(tr['date']) + timedelta(days=168*k))]\n\n    tf_fvid = set(tr.loc[(tr['date'] >= min(tr['date']) + timedelta(days=168*k + 46 )) \n                       & (tr['date'] < min(tr['date']) + timedelta(days=168*k + 46 + 62))]['fullVisitorId'])\n\n    tf_returned = tf[tf['fullVisitorId'].isin(tf_fvid)]\n    \n    tf_tst = tr[tr['fullVisitorId'].isin(set(tf_returned['fullVisitorId']))\n             & (tr['date'] >= min(tr['date']) + timedelta(days=168*k + 46))\n             & (tr['date'] < min(tr['date']) + timedelta(days=168*k + 46 + 62))]\n\n    tf_target = tf_tst.groupby('fullVisitorId').agg({\n                                    'totals.transactionRevenue': [('sum_revenue_target',  'sum')] })\n    tf_target.columns = tf_target.columns.droplevel()\n    tf_target.reset_index(inplace=True)\n    tf_target['ret'] = 1\n    \n    tf_nonret = pd.DataFrame()\n    tf_nonret['fullVisitorId'] = list(set(tf['fullVisitorId']) - tf_fvid)    \n    tf_nonret['sum_revenue_target'] = 0\n    tf_nonret['ret'] = 0\n    \n    tf_target = pd.concat([tf_target, tf_nonret], axis=0).reset_index(drop=True)\n    # len(set(tf['fullVisitorId'])), len(set(tf_target['fullVisitorId']))\n    global df_maxdate, df_mindate\n    df_maxdate = max(tf['date'])\n    df_mindate = min(tf['date'])\n\n    tf = tf.groupby('fullVisitorId').agg(group_dict)\n                                                      \n    tf.columns = tf.columns.droplevel()\n\n    tf = pd.merge(tf, tf_target, left_on='fullVisitorId', right_on='fullVisitorId')\n    end = time.time()\n    print('Time spent on processing {} mins'.format((end - start)/60))\n\n    return tf","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2021-08-16T08:16:24.955682Z","iopub.execute_input":"2021-08-16T08:16:24.9561Z","iopub.status.idle":"2021-08-16T08:16:24.973125Z","shell.execute_reply.started":"2021-08-16T08:16:24.956056Z","shell.execute_reply":"2021-08-16T08:16:24.971997Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# In this cell we get train data by parts. Cell is commented since execution takes approximately 70 mins for one part. \n# We will read the results of this function run from .csv file later\n\n#train_1 = getTimeFramewithFeatures(df, k=1)\n#train_1.to_csv('train_pivot_1.csv')\n\n#train_2 = getTimeFramewithFeatures(df, k=2)\n#train_2.to_csv('train_pivot_2.csv')\n\n#train_3 = getTimeFramewithFeatures(df, k=3)\n#train_3.to_csv('train_pivot_3.csv')\n\n#train_4 = getTimeFramewithFeatures(df, k=4)\n#train_4.to_csv('train_pivot_4.csv')","metadata":{"execution":{"iopub.status.busy":"2021-08-12T05:00:13.686971Z","iopub.execute_input":"2021-08-12T05:00:13.687481Z","iopub.status.idle":"2021-08-12T06:02:27.330707Z","shell.execute_reply.started":"2021-08-12T05:00:13.687442Z","shell.execute_reply":"2021-08-12T06:02:27.329357Z"},"_kg_hide-input":true,"_kg_hide-output":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 4. Construct test set and group by VisitorID","metadata":{}},{"cell_type":"code","source":"# Cell is commented since execution takes approximately 70 mins\n# We will read the results of this cell run from .csv file later\n\n# df_maxdate = max(df['date'])\n# df_mindate = min(df['date'])\n\n# start = time.time()\n\n# test = test_raw.groupby('fullVisitorId').agg(group_dict)\n# test.columns = test.columns.droplevel()\n# test['sum_revenue_target'] = np.nan\n# test['ret'] = np.nan\n# test = test.reset_index()\n\n# end = time.time()\n# print('Time spent on processing {} mins'.format((end - start)/60))\n# test.to_csv('test_5.csv')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 6. Combine all data","metadata":{}},{"cell_type":"code","source":"# Cell is commented since grouping of datasets for concatenation takes a lot of time.  \n# We will read the results from previously prepared .csv file.\n\n# train_all = pd.concat([train_1, train_2, train_3, train_4, test], axis=0, sort=False).reset_index(drop=True)\n\n# train_all['interval_dates'] = train_all['interval_dates'].dt.days\n# train_all['first_ses_from_the_period_start'] = train_all['first_ses_from_the_period_start'].dt.days\n# train_all['last_ses_from_the_period_end'] = train_all['last_ses_from_the_period_end'].dt.days\n# train_all['sum_revenue_target'] = np.log1p(train_all['sum_revenue_target'])\n\n# train_all.to_csv('train_and_test_clean.csv')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Read the dataset from previously generated file\ntrain_all = pd.read_csv('/kaggle/input/ga-competition-processed-and-grouped-data/train_and_test.csv', dtype={'fullVisitorId':'str'})\ntrain_all = train_all.drop(columns=['Unnamed: 0'])","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:16:24.974239Z","iopub.execute_input":"2021-08-16T08:16:24.974721Z","iopub.status.idle":"2021-08-16T08:16:44.88508Z","shell.execute_reply.started":"2021-08-16T08:16:24.974639Z","shell.execute_reply":"2021-08-16T08:16:44.88395Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Fill null categorical variables with value ‘999’ \ncat_cols_to_fill = train_all.loc[:, train_all.isna().sum()>0].select_dtypes(include='object').columns\ntrain_all[cat_cols_to_fill] = train_all[cat_cols_to_fill].fillna('999')\ntrain_all['networkDomain'] = train_all['networkDomain'].replace('(not set)', '999') # on the previous step we missed this value, that also can be considered as null\n\n# Fill null numerical variables with value 0\nnum_cols_to_fill = [i for i in train_all.loc[:, train_all.isna().sum()>0].select_dtypes(include=['int', 'float']).columns if i not in ['sum_revenue_target', 'ret']]\ntrain_all[num_cols_to_fill] = train_all[num_cols_to_fill].fillna(0)","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:16:45.527614Z","iopub.execute_input":"2021-08-16T08:16:45.527917Z","iopub.status.idle":"2021-08-16T08:16:57.720892Z","shell.execute_reply.started":"2021-08-16T08:16:45.527887Z","shell.execute_reply":"2021-08-16T08:16:57.71922Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Replace values with count <30 of 'networkDomain' column with 'other' value\nvalues_to_replace = train_all['networkDomain'].value_counts()[train_all['networkDomain'].value_counts()<30].index\n\nfor i in values_to_replace:\n    train_all['networkDomain'] = train_all['networkDomain'].replace(i, 'other')","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:16:57.723344Z","iopub.execute_input":"2021-08-16T08:16:57.723705Z","iopub.status.idle":"2021-08-16T08:17:24.283913Z","shell.execute_reply.started":"2021-08-16T08:16:57.72367Z","shell.execute_reply":"2021-08-16T08:17:24.282911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 7. Model building","metadata":{}},{"cell_type":"code","source":"target_cols = ['sum_revenue_target', 'ret', 'fullVisitorId']\ndata = train_all.drop(columns=target_cols)","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:17:24.285147Z","iopub.execute_input":"2021-08-16T08:17:24.285446Z","iopub.status.idle":"2021-08-16T08:17:24.289956Z","shell.execute_reply.started":"2021-08-16T08:17:24.285411Z","shell.execute_reply":"2021-08-16T08:17:24.288803Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Initialize transformer \ncat_cols = data.select_dtypes(include=[\"object\", \"category\"]).columns\nnum_cols = data.select_dtypes(include=[\"number\", \"bool\"]).columns\ntransformer = ColumnTransformer([(\"cat\", OrdinalEncoder(), cat_cols),\n                                 ('num', StandardScaler(), num_cols)])\n# Transform the data\ntransformed =  transformer.fit_transform(data)","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:18:45.087564Z","iopub.execute_input":"2021-08-16T08:18:45.088125Z","iopub.status.idle":"2021-08-16T08:19:02.909188Z","shell.execute_reply.started":"2021-08-16T08:18:45.08808Z","shell.execute_reply":"2021-08-16T08:19:02.907934Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Combine transformed data with target variables\ndata_transformed =  pd.concat((train_all[['sum_revenue_target', 'ret']], pd.DataFrame(transformed, columns = data.columns)), axis=1)","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:19:02.91106Z","iopub.execute_input":"2021-08-16T08:19:02.91152Z","iopub.status.idle":"2021-08-16T08:19:05.292686Z","shell.execute_reply.started":"2021-08-16T08:19:02.911457Z","shell.execute_reply":"2021-08-16T08:19:05.291594Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Separate train and test set\ntrain = data_transformed[data_transformed['sum_revenue_target'].notnull()]\ntest = data_transformed[data_transformed['sum_revenue_target'].isnull()]","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:19:05.29437Z","iopub.execute_input":"2021-08-16T08:19:05.294713Z","iopub.status.idle":"2021-08-16T08:19:07.460105Z","shell.execute_reply.started":"2021-08-16T08:19:05.294682Z","shell.execute_reply":"2021-08-16T08:19:07.459161Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create LGB dataset\ndtrain_all = lgb.Dataset(train.drop(['sum_revenue_target', 'ret'], axis=1), label=train['ret'], params={'verbose': -1})\ndtrain_ret = lgb.Dataset(train.drop(['sum_revenue_target', 'ret'], axis=1)[train['ret']==1], label=train['sum_revenue_target'][train['ret']==1],\n                        params={'verbose': -1})","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:19:07.461363Z","iopub.execute_input":"2021-08-16T08:19:07.461836Z","iopub.status.idle":"2021-08-16T08:19:08.226063Z","shell.execute_reply.started":"2021-08-16T08:19:07.4618Z","shell.execute_reply":"2021-08-16T08:19:08.225004Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Set parameters for two models \nparams_lgb1 = {\n        \"objective\" : \"binary\",\n        \"metric\" : \"binary_logloss\",\n        \"max_bin\": 256,\n        \"num_leaves\" : 15,\n        \"min_child_samples\" : 1,\n        \"learning_rate\" : 0.005,\n        \"bagging_fraction\" : 0.9,\n        \"feature_fraction\" : 0.8,\n        \"bagging_freq\" : 1,\n        \"verbose\": -1}\nparams_lgb2 = {\n        \"objective\" : \"regression\",\n        \"metric\" : \"rmse\", \n        \"max_bin\": 256,\n        \"num_leaves\" : 9,\n        \"min_child_samples\" : 1,\n        \"learning_rate\" : 0.005,\n        \"bagging_fraction\" : 0.9,\n        \"feature_fraction\" : 0.8,\n        \"bagging_freq\" : 1,\n        \"verbose\": -1}","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:19:10.60341Z","iopub.execute_input":"2021-08-16T08:19:10.603814Z","iopub.status.idle":"2021-08-16T08:19:10.610785Z","shell.execute_reply.started":"2021-08-16T08:19:10.603782Z","shell.execute_reply":"2021-08-16T08:19:10.609621Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Initialize array for results storing\npred_lgb_sum  = np.zeros(test.shape[0],)\n\nfor i in range(10):\n    print('Interation number', i, 'completed.')\n    params_lgb1['feature_fraction_seed'] = 0+i\n    params_lgb1['bagging_seed'] = 1+i\n    lgb_model1 = lgb.train(params_lgb1, dtrain_all, num_boost_round=1200)\n    pr_lgb = lgb_model1.predict(test.drop(['sum_revenue_target', 'ret'], axis=1), num_iteration=lgb_model1.best_iteration)\n    params_lgb2['feature_fraction_seed'] = 0+i\n    params_lgb2['bagging_seed'] = 1+i\n    lgb_model2 = lgb.train(params_lgb2, dtrain_ret, num_boost_round=368)\n    pr_lgb_ret = lgb_model2.predict(test.drop(['sum_revenue_target', 'ret'], axis=1), num_iteration=lgb_model2.best_iteration)\n    \n    pred_lgb_sum = pred_lgb_sum + pr_lgb*pr_lgb_ret\n\npred_final = pred_lgb_sum/10","metadata":{"execution":{"iopub.status.busy":"2021-08-16T08:19:13.760625Z","iopub.execute_input":"2021-08-16T08:19:13.760982Z","iopub.status.idle":"2021-08-16T08:21:47.510316Z","shell.execute_reply.started":"2021-08-16T08:19:13.760953Z","shell.execute_reply":"2021-08-16T08:21:47.509316Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check that we don’t get negative predictions   \nprint(pred_final.min())","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 8. Prepare submission file ","metadata":{}},{"cell_type":"code","source":"submission = pd.DataFrame()\nsubmission['fullVisitorId'] = train_all[train_all['sum_revenue_target'].isnull()]['fullVisitorId']\nsubmission['PredictedLogRevenue'] = pred_final","metadata":{"execution":{"iopub.status.busy":"2021-08-13T20:41:06.944312Z","iopub.execute_input":"2021-08-13T20:41:06.944764Z","iopub.status.idle":"2021-08-13T20:41:07.093719Z","shell.execute_reply.started":"2021-08-13T20:41:06.944729Z","shell.execute_reply":"2021-08-13T20:41:07.092509Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission.to_csv('submission.csv', index=False)","metadata":{"execution":{"iopub.status.busy":"2021-08-13T20:41:10.92104Z","iopub.execute_input":"2021-08-13T20:41:10.921423Z","iopub.status.idle":"2021-08-13T20:41:12.260406Z","shell.execute_reply.started":"2021-08-13T20:41:10.921392Z","shell.execute_reply":"2021-08-13T20:41:12.259169Z"},"trusted":true},"execution_count":null,"outputs":[]}]}