{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"### I screwed up uploading the script as it is not visible to everyone, hence i am uploading it here and making the other one private.\n"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"# As discussed in the discussion section, we have leak in the competition if we use external data.\n# Discussed here: https://www.kaggle.com/c/ga-customer-revenue-prediction/discussion/68235#401950\n \n#  I have downloaded the data and used it to climb to the 2nd position. Before using it I was at the 600th position. \n#  Most of the code used it from olivier's (https://www.kaggle.com/ogrellier) notebooks in this competition and I haven't done any feature engineering with the external data yet.\n \n#  You can use the data that I downloaded as it would be in the data of this kernel. \n#  There are 4 files 2 for train and 2 for test.\n#Can't upload the notebook as kaggle kernels keep on crashing but this script will get you same score as mine.\n\nimport warnings\nwarnings.simplefilter(\"ignore\")\nimport os\nimport json\nimport pandas as pd\nimport numpy as np\nfrom pandas.io.json import json_normalize\nfrom IPython.display import display\nfrom sklearn.metrics import mean_squared_error\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport gc\nimport lightgbm as lgb\nimport xgboost as xgb\nfrom sklearn.model_selection import GroupKFold\npd.set_option(\"display.max_rows\", 1000, \"display.max_columns\", 1000)\npd.options.mode.chained_assignment = None\n\n#using https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields/notebook\ndef load_df(csv_path='../input/ga-customer-revenue-prediction/train.csv', nrows=None):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    \n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'}, # Important!!\n                     nrows=nrows)\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    print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n    return df\n    \ntrain = load_df()\ntest = load_df(\"../input/ga-customer-revenue-prediction/test.csv\")\n\n#Loading external data\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'})\n\n#Getting VisitId to Join with our train, test data\ndef get_visitid(id):\n    bef_, af_ = str(x).split('.')\n    return int(bef_), (int(af_)*10 if len(af_)==1 else int(af_))\n\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(str)\n\ntrain_exdata = pd.concat([train_store_1, train_store_2], sort=False)\ntest_exdata = pd.concat([test_store_1, test_store_2], sort=False)\n\nfor df in [train, test]:\n    df[\"visitId\"] = df[\"visitId\"].astype(str)\n\n# Merge with train/test data\ntrain_new = train.merge(train_exdata, how=\"left\", on=\"visitId\")\ntest_new = test.merge(test_exdata, how=\"left\", on=\"visitId\")\n\n# Drop Client Id\nfor df in [train_new, test_new]:\n    df.drop(\"Client Id\", 1, inplace=True)\n\n#Cleaning Revenue\nfor df in [train_new, test_new]:\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)\n\n#Imputing NaN\nfor df in [train_new, test_new]:\n    df[\"Sessions\"] = df[\"Sessions\"].fillna(0)\n    df[\"Avg. Session Duration\"] = df[\"Avg. Session Duration\"].fillna(0)\n    df[\"Bounce Rate\"] = df[\"Bounce Rate\"].fillna(0)\n    df[\"Revenue\"] = df[\"Revenue\"].fillna(0)\n    df[\"Transactions\"] = df[\"Transactions\"].fillna(0)\n    df[\"Goal Conversion Rate\"] = df[\"Goal Conversion Rate\"].fillna(0)\n    df['trafficSource.adContent'].fillna('N/A', inplace=True)\n    df['trafficSource.adwordsClickInfo.slot'].fillna('N/A', inplace=True)\n    df['trafficSource.adwordsClickInfo.page'].fillna(0.0, inplace=True)\n    df['trafficSource.adwordsClickInfo.isVideoAd'].fillna('N/A', inplace=True)\n    df['trafficSource.adwordsClickInfo.adNetworkType'].fillna('N/A', inplace=True)\n    df['trafficSource.adwordsClickInfo.gclId'].fillna('N/A', inplace=True)\n    df['trafficSource.isTrueDirect'].fillna('N/A', inplace=True)\n    df['trafficSource.referralPath'].fillna('N/A', inplace=True)\n    df['trafficSource.keyword'].fillna('N/A', inplace=True)\n    df['totals.bounces'].fillna(0.0, inplace=True)\n    df['totals.newVisits'].fillna(0.0, inplace=True)\n    df['totals.pageviews'].fillna(0.0, inplace=True)\n\ndel train\ndel test\ntrain = train_new\ntest = test_new\ndel train_new\ndel test_new\n\nfor df in [train, test]:\n    df['date_new'] = pd.to_datetime(df['visitStartTime'], unit='s')\n    df['date'] = df['date_new'].dt.date\n    df['sess_date_dow'] = df['date_new'].dt.dayofweek\n    df['sess_date_hours'] = df['date_new'].dt.hour\n    df['sess_month'] = df['date_new'].dt.month\n\n# Dropping Constant Columns\n\nconst_cols = [col for col in train.columns if len(train[col].unique())==1]\n\nfor df in [train, test]:\n    df.drop(const_cols, 1, inplace=True)\n\ntrain.drop('trafficSource.campaignCode', 1, inplace=True)\n\n# Modeling with Olivier's method\n#https://www.kaggle.com/ogrellier/teach-lightgbm-to-sum-predictions\n\ndef get_folds(df=None, n_splits=5):\n    \"\"\"Returns dataframe indices corresponding to Visitors Group KFold\"\"\"\n    # Get sorted unique visitors\n    unique_vis = np.array(sorted(df['fullVisitorId'].unique()))\n\n    # Get folds\n    folds = GroupKFold(n_splits=n_splits)\n    fold_ids = []\n    ids = np.arange(df.shape[0])\n    for trn_vis, val_vis in folds.split(X=unique_vis, y=unique_vis, groups=unique_vis):\n        fold_ids.append(\n            [\n                ids[df['fullVisitorId'].isin(unique_vis[trn_vis])],\n                ids[df['fullVisitorId'].isin(unique_vis[val_vis])]\n            ]\n        )\n\n    return fold_ids\n\ny_reg = train['totals.transactionRevenue'].fillna(0)\ndel train['totals.transactionRevenue']\n\nif 'totals.transactionRevenue' in test.columns:\n    del test['totals.transactionRevenue']\n\nexcluded_features = [\n    'date', 'date_new', 'fullVisitorId', 'sessionId', 'totals.transactionRevenue', \n    'visitId', 'visitStartTime'\n]\n\ncategorical_features = [\n    _f for _f in train.columns\n    if (_f not in excluded_features) & (train[_f].dtype == 'object')\n]\n\nfor f in categorical_features:\n    train[f], indexer = pd.factorize(train[f])\n    test[f] = indexer.get_indexer(test[f])\n\nfor df in [train, test]:\n    df['hits/pageviews'] = (df[\"totals.pageviews\"]/(df[\"totals.hits\"])).apply(lambda x: 0 if np.isinf(x) else x)\n    df['is_high_hits'] = np.logical_or(df[\"totals.hits\"]>4,df[\"totals.pageviews\"]>4).astype(np.int32)\n    df[\"Revenue\"] = np.log1p(df[\"Revenue\"])\n\nwarnings.simplefilter(action='ignore', category=FutureWarning)\nfolds = get_folds(df=train, n_splits=2) #changed from 5\ny_reg = y_reg.astype(float)\ntrain_features = [_f for _f in train.columns if _f not in excluded_features]\nprint(train_features)\n\nimportances = pd.DataFrame()\noof_reg_preds = np.zeros(train.shape[0])\nsub_reg_preds = np.zeros(test.shape[0])\nfor fold_, (trn_, val_) in enumerate(folds):\n    trn_x, trn_y = train[train_features].iloc[trn_], y_reg.iloc[trn_]\n    val_x, val_y = train[train_features].iloc[val_], y_reg.iloc[val_]\n    \n    reg = lgb.LGBMRegressor(\n        num_leaves=31,\n        learning_rate=0.03,\n        n_estimators=1000,\n        subsample=.9,\n        colsample_bytree=.9,\n        random_state=1\n    )\n    reg.fit(\n        trn_x, np.log1p(trn_y),\n        eval_set=[(val_x, np.log1p(val_y))],\n        early_stopping_rounds=100,\n        verbose=100,\n        eval_metric='rmse'\n    )\n    imp_df = pd.DataFrame()\n    imp_df['feature'] = train_features\n    imp_df['gain'] = reg.booster_.feature_importance(importance_type='gain')\n    \n    imp_df['fold'] = fold_ + 1\n    importances = pd.concat([importances, imp_df], axis=0, sort=False)\n    \n    oof_reg_preds[val_] = reg.predict(val_x, num_iteration=reg.best_iteration_)\n    oof_reg_preds[oof_reg_preds < 0] = 0\n    _preds = reg.predict(test[train_features], num_iteration=reg.best_iteration_)\n    _preds[_preds < 0] = 0\n    sub_reg_preds += np.expm1(_preds) / len(folds)\n    \nprint(mean_squared_error(np.log1p(y_reg), oof_reg_preds) ** .5)\n\n\n# importances['gain_log'] = np.log1p(importances['gain'])\n# mean_gain = importances[['gain', 'feature']].groupby('feature').mean()\n# importances['mean_gain'] = importances['feature'].map(mean_gain['gain'])\n\n# plt.figure(figsize=(8, 12))\n# plot = sns.barplot(x='gain_log', y='feature', data=importances.sort_values('mean_gain', ascending=False))\n# fig = plot.get_figure()\n# fig.savefig(\"feat_imp_1.png\")\n# plt.close()\n\n\ntrain['predictions'] = np.expm1(oof_reg_preds)\ntest['predictions'] = sub_reg_preds\n\n# Aggregate data at User level\ntrn_data = train[train_features + ['fullVisitorId']].groupby('fullVisitorId').mean()\n\n# Create a list of predictions for each Visitor\ntrn_pred_list = train[['fullVisitorId', 'predictions']].groupby('fullVisitorId')\\\n    .apply(lambda df: list(df.predictions))\\\n    .apply(lambda x: {'pred_'+str(i): pred for i, pred in enumerate(x)})\n\n# Create a DataFrame with VisitorId as index\n# trn_pred_list contains dict \n# so creating a dataframe from it will expand dict values into columns\ntrn_all_predictions = pd.DataFrame(list(trn_pred_list.values), index=trn_data.index)\ntrn_feats = trn_all_predictions.columns\n\nwith warnings.catch_warnings():\n    warnings.simplefilter(\"ignore\")\n    trn_all_predictions['t_mean'] = np.log1p(trn_all_predictions[trn_feats].mean(axis=1))\n    trn_all_predictions['t_median'] = np.log1p(trn_all_predictions[trn_feats].median(axis=1))\n    trn_all_predictions['t_sum_log'] = np.log1p(trn_all_predictions[trn_feats]).sum(axis=1)\n    trn_all_predictions['t_sum_act'] = np.log1p(trn_all_predictions[trn_feats].fillna(0).sum(axis=1))\n    trn_all_predictions['t_nb_sess'] = trn_all_predictions[trn_feats].isnull().sum(axis=1)\n    full_data = pd.concat([trn_data, trn_all_predictions], axis=1)\n    del trn_data, trn_all_predictions\n    gc.collect()\n    print(full_data.shape)\n\n\nsub_pred_list = test[['fullVisitorId', 'predictions']].groupby('fullVisitorId')\\\n    .apply(lambda df: list(df.predictions))\\\n    .apply(lambda x: {'pred_'+str(i): pred for i, pred in enumerate(x)})\n\nsub_data = test[train_features + ['fullVisitorId']].groupby('fullVisitorId').mean()\nsub_all_predictions = pd.DataFrame(list(sub_pred_list.values), index=sub_data.index)\n\n\nwith warnings.catch_warnings():\n    warnings.simplefilter(\"ignore\")\n    for f in trn_feats:\n        if f not in sub_all_predictions.columns:\n            sub_all_predictions[f] = np.nan\n    sub_all_predictions['t_mean'] = np.log1p(sub_all_predictions[trn_feats].mean(axis=1))\n    sub_all_predictions['t_median'] = np.log1p(sub_all_predictions[trn_feats].median(axis=1))\n    sub_all_predictions['t_sum_log'] = np.log1p(sub_all_predictions[trn_feats]).sum(axis=1)\n    sub_all_predictions['t_sum_act'] = np.log1p(sub_all_predictions[trn_feats].fillna(0).sum(axis=1))\n    sub_all_predictions['t_nb_sess'] = sub_all_predictions[trn_feats].isnull().sum(axis=1)\n    sub_full_data = pd.concat([sub_data, sub_all_predictions], axis=1)\n    del sub_data, sub_all_predictions\n    gc.collect()\n    print(sub_full_data.shape)\n\n# Create target at Visitor level\ntrain['target'] = y_reg\ntrn_user_target = train[['fullVisitorId', 'target']].groupby('fullVisitorId').sum()\n\n# Train a model at Visitor level\nwarnings.simplefilter(action='ignore', category=FutureWarning)\nfolds = get_folds(df=full_data[['totals.pageviews']].reset_index(), n_splits=2)#changed from 5\n\noof_preds = np.zeros(full_data.shape[0])\nsub_preds = np.zeros(sub_full_data.shape[0])\nvis_importances = pd.DataFrame()\n\nfor fold_, (trn_, val_) in enumerate(folds):\n    trn_x, trn_y = full_data.iloc[trn_], trn_user_target['target'].iloc[trn_]\n    val_x, val_y = full_data.iloc[val_], trn_user_target['target'].iloc[val_]\n    \n    reg = lgb.LGBMRegressor(\n        num_leaves=31,\n        learning_rate=0.03,\n        n_estimators=1000,\n        subsample=.9,\n        colsample_bytree=.9,\n        random_state=1\n    )\n    reg.fit(\n        trn_x, np.log1p(trn_y),\n        eval_set=[(trn_x, np.log1p(trn_y)), (val_x, np.log1p(val_y))],\n        eval_names=['TRAIN', 'VALID'],\n        early_stopping_rounds=100,\n        eval_metric='rmse',\n        verbose=100\n    )\n    \n    imp_df = pd.DataFrame()\n    imp_df['feature'] = trn_x.columns\n    imp_df['gain'] = reg.booster_.feature_importance(importance_type='gain')\n    \n    imp_df['fold'] = fold_ + 1\n    vis_importances = pd.concat([vis_importances, imp_df], axis=0, sort=False)\n    \n    oof_preds[val_] = reg.predict(val_x, num_iteration=reg.best_iteration_)\n    oof_preds[oof_preds < 0] = 0\n    \n    # Make sure features are in the same order\n    _preds = reg.predict(sub_full_data[full_data.columns], num_iteration=reg.best_iteration_)\n    _preds[_preds < 0] = 0\n    sub_preds += _preds / len(folds)\n    \nprint(mean_squared_error(np.log1p(trn_user_target['target']), oof_preds) ** .5)\n\n# # Display feature importances\n# vis_importances['gain_log'] = np.log1p(vis_importances['gain'])\n# mean_gain = vis_importances[['gain', 'feature']].groupby('feature').mean()\n# vis_importances['mean_gain'] = vis_importances['feature'].map(mean_gain['gain'])\n\n# plt.figure(figsize=(8, 25))\n# plot = sns.barplot(x='gain_log', y='feature', data=vis_importances.sort_values('mean_gain', ascending=False).iloc[:300])\n# fig = plot.get_figure()\n# fig.savefig(\"feat_imp_2.png\")\n# plt.close()\n\n# Save predictions\nsub_full_data['PredictedLogRevenue'] = sub_preds\nsub_full_data[['PredictedLogRevenue']].to_csv('submission_new.csv', index=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8da99616b553584b7f186e5dab1a9be10c24a09d"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"53d274a367ef376227d2afc224d4cb3e89c3804f"},"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}