{"cells":[{"metadata":{"_uuid":"7b603c3873a8e20f1987a4ca18887f30a0f714ad"},"cell_type":"markdown","source":"## General information\n\nThis kernel continues my [previous one](https://www.kaggle.com/artgor/fork-of-eda-on-basic-data-and-lgb-in-progress) - you can see EDA and other things there.\n\nThis kernel is dedicated to feature generation. I'll generate features step by step and try to increase CV."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport json\nimport bq_helper\nfrom pandas.io.json import json_normalize\nimport seaborn as sns \nimport matplotlib.pyplot as plt\n%matplotlib inline\nplt.style.use('ggplot')\nimport lightgbm as lgb\n\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.model_selection import TimeSeriesSplit, KFold\nfrom sklearn.metrics import mean_squared_error\nimport time","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields\n\ndef load_df(csv_path='../input/train.csv', JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']):\n\n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'})\n    \n    for column in JSON_COLUMNS:\n        column_as_df = json_normalize(df[column])\n        column_as_df.columns = [f\"{column}.{subcolumn}\" for subcolumn in column_as_df.columns]\n        df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)\n\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4432fc68b07ee992af552288974e52aa31c9fa41"},"cell_type":"code","source":"%%time\ntrain = load_df(\"../input/train.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9c8711b819c6f28a84b37ca0e5b1ceec84d65c74"},"cell_type":"code","source":"%%time\ntest = load_df(\"../input/test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f2dfe028ddcfccbfd0816802bf402d9274a61771"},"cell_type":"markdown","source":"## Data processing"},{"metadata":{"_uuid":"61737ac2fac6a48027869220e6bf343ba84a0921"},"cell_type":"markdown","source":"Some of columns aren't available in this dataset, let's drop them."},{"metadata":{"trusted":true,"_uuid":"68282c259933877bd186a04779cce6ab51db2109"},"cell_type":"code","source":"cols_to_drop = [col for col in train.columns if train[col].nunique(dropna=False) == 1]\ntrain.drop(cols_to_drop, axis=1, inplace=True)\ntest.drop([col for col in cols_to_drop if col in test.columns], axis=1, inplace=True)\n\n#only one not null value\ntrain.drop(['trafficSource.campaignCode'], axis=1, inplace=True)\n\nprint(f'Dropped {len(cols_to_drop)} columns.')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8d40a08f29cded4a4b9469b21d795e93edfab3fe"},"cell_type":"code","source":"train['totals.transactionRevenue'] = train['totals.transactionRevenue'].fillna(0).astype(int)\ntrain['totals.transactionRevenue'] = np.log1p(train['totals.transactionRevenue'])","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"24d3eb8db5803de24e79b12180b5fdb135211b8e"},"cell_type":"code","source":"def process_df(df):\n    \"\"\"Process df and create new features.\"\"\"\n    \n    for col in ['visitNumber', 'totals.hits', 'totals.pageviews']:\n        df[col] = df[col].astype(float)\n    df['trafficSource.adwordsClickInfo.isVideoAd'].fillna(True, inplace=True)\n    df['trafficSource.isTrueDirect'].fillna(False, inplace=True)\n    \n    df['date'] = pd.to_datetime(df['date'].apply(lambda x: str(x)[:4] + '-' + str(x)[4:6] + '-' + str(x)[6:]))\n    \n    df['month'] = df['date'].dt.month\n    df['day'] = df['date'].dt.day\n    df['weekday'] = df['date'].dt.weekday\n    df['weekofyear'] = df['date'].dt.weekofyear\n\n    df['month_unique_user_count'] = df.groupby('month')['fullVisitorId'].transform('nunique')\n    df['day_unique_user_count'] = df.groupby('day')['fullVisitorId'].transform('nunique')\n    df['weekday_unique_user_count'] = df.groupby('weekday')['fullVisitorId'].transform('nunique')\n    df['weekofyear_unique_user_count'] = df.groupby('weekofyear')['fullVisitorId'].transform('nunique')\n    \n    df['browser_category'] = df['device.browser'] + '_' + df['device.deviceCategory']\n    df['browser_operatingSystem'] = df['device.browser'] + '_' + df['device.operatingSystem']\n    df['source_country'] = df['trafficSource.source'] + '_' + df['geoNetwork.country']\n    \n    df['visitNumber'] = np.log1p(df['visitNumber'])\n    df['totals.hits'] = np.log1p(df['totals.hits'])\n    df['totals.pageviews'] = np.log1p(df['totals.pageviews'].fillna(0))\n\n    df['sum_pageviews_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('sum')\n    df['count_pageviews_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('count')\n    df['mean_pageviews_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('mean')\n    df['sum_hits_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.hits'].transform('sum')\n    df['count_hits_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.hits'].transform('count')\n    df['mean_hits_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.hits'].transform('mean')\n\n    df['mean_hits_per_day'] = df.groupby(['day'])['totals.hits'].transform('mean')\n    df['sum_hits_per_day'] = df.groupby(['day'])['totals.hits'].transform('sum')\n    \n    df['sum_pageviews_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('sum')\n    df['count_pageviews_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('count')\n    df['mean_pageviews_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.pageviews'].transform('mean')\n\n    df['sum_pageviews_per_region'] = df.groupby('geoNetwork.region')['totals.pageviews'].transform('sum')\n    df['count_pageviews_per_region'] = df.groupby('geoNetwork.region')['totals.pageviews'].transform('count')\n    df['mean_pageviews_per_region'] = df.groupby('geoNetwork.region')['totals.pageviews'].transform('mean')\n    \n    df['sum_hits_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.hits'].transform('sum')\n    df['count_hits_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.hits'].transform('count')\n    df['mean_hits_per_network_domain'] = df.groupby('geoNetwork.networkDomain')['totals.hits'].transform('mean')\n\n    df['sum_hits_per_region'] = df.groupby('geoNetwork.region')['totals.hits'].transform('sum')\n    df['count_hits_per_region'] = df.groupby('geoNetwork.region')['totals.hits'].transform('count')\n    df['mean_hits_per_region'] = df.groupby('geoNetwork.region')['totals.hits'].transform('mean')\n\n    df['sum_hits_per_country'] = df.groupby('geoNetwork.country')['totals.hits'].transform('sum')\n    df['count_hits_per_country'] = df.groupby('geoNetwork.country')['totals.hits'].transform('count')\n    df['mean_hits_per_country'] = df.groupby('geoNetwork.country')['totals.hits'].transform('mean')\n\n    df['user_pageviews_sum'] = df.groupby('fullVisitorId')['totals.pageviews'].transform('sum')\n    df['user_hits_sum'] = df.groupby('fullVisitorId')['totals.hits'].transform('sum')\n\n    df['user_pageviews_count'] = df.groupby('fullVisitorId')['totals.pageviews'].transform('count')\n    df['user_hits_count'] = df.groupby('fullVisitorId')['totals.hits'].transform('count')\n\n    df['user_pageviews_sum_to_mean'] = df['user_pageviews_sum'] / df['user_pageviews_sum'].mean()\n    df['user_hits_sum_to_mean'] = df['user_hits_sum'] / df['user_hits_sum'].mean()\n    df['user_pageviews_sum_to_mean'] = df['user_pageviews_sum'] / df['user_pageviews_sum'].mean()\n    df['user_hits_sum_to_mean'] = df['user_hits_sum'] / df['user_hits_sum'].mean()\n    \n    df['user_pageviews_to_region'] = df['user_pageviews_sum'] / df['mean_pageviews_per_region']\n    df['user_hits_to_region'] = df['user_hits_sum'] / df['mean_hits_per_region']\n    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1622817de65df457b8a12a09dcf407065fd6e605","scrolled":true},"cell_type":"code","source":"%%time\ntrain = process_df(train)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"74f5437958bc11dde434a513239b104d017b8088"},"cell_type":"code","source":"%%time\ntest = process_df(test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"54e7ddf1d0b43008d492d2ad1ab21b9b7fdee89a"},"cell_type":"code","source":"not_num_cols = ['visitId', 'totals.transactionRevenue', 'month', 'day', 'weekday', 'weekofyear']\nnum_cols = [col for col in train.columns if train[col].dtype in ['float64', 'int64'] and col not in not_num_cols]\n\nnot_cat_cols = ['fullVisitorId', 'sessionId', 'trafficSource.referralPath']\ncat_cols = [col for col in train.columns if train[col].dtype == 'object' and col not in not_cat_cols] + ['month', 'day', 'weekday', 'weekofyear']\n\nno_use = ['visitStartTime', \"date\", \"fullVisitorId\", \"sessionId\", \"visitId\", 'totals.transactionRevenue', 'trafficSource.referralPath']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ce6024727db6ce7b01d7634a389a80ab0cee4728"},"cell_type":"markdown","source":"### More features\n\nFor now only several features are calculated."},{"metadata":{"trusted":true,"_uuid":"02a8c4a25626f98493703074136104254d26fbfe"},"cell_type":"code","source":"def generate_more_features(df):\n    for col in num_cols:\n        df[col + '_root'] = df[col] ** 0.5\n        \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b213659917f6df83411b3dee25d2db97a126b973"},"cell_type":"code","source":"%%time\ntrain = generate_more_features(train)\ntest = generate_more_features(test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fc0956f9637cfb0e0c873014ec1e506fccb504f2"},"cell_type":"code","source":"def generate_more_features(df, features_slice=[]):\n    \"\"\"\n    Generate more features by multiplying all numerical columns by each other.\n    But can't do it for all columns due to memory limitations\n    \"\"\"\n    for col1 in num_cols[features_slice[0] : features_slice[1]]:\n        for col2 in num_cols:\n            if col1 != col2:\n                # print(col1, col2)\n                df[col1 + '_' + col2] = df[col1] * df[col2]\n        \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8d3eb1907497c42c08379ba82aa7f9ea50362a21"},"cell_type":"code","source":"%%time\ntrain = generate_more_features(train, [0, 3])\ntest = generate_more_features(test, [0, 3])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0e56c9299685638d951a957a898f0ebe06023d93"},"cell_type":"markdown","source":"### Feature processing"},{"metadata":{"trusted":true,"_uuid":"68f5dc8ea1ca30d36f37d43afa7ab0dccbd0ea70","scrolled":true},"cell_type":"code","source":"for col in cat_cols:\n    print(col)\n    lbl = LabelEncoder()\n    lbl.fit(list(train[col].values.astype('str')) + list(test[col].values.astype('str')))\n    train[col] = lbl.transform(list(train[col].values.astype('str')))\n    test[col] = lbl.transform(list(test[col].values.astype('str')))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3ffc62507a2adf81c8f2c1d4b9c65a7aac9dffe4"},"cell_type":"code","source":"train = train.sort_values('date')\nX = train.drop(no_use, axis=1)\ny = train['totals.transactionRevenue']\nX_test = test.drop([col for col in no_use if col in test.columns], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"baa3b9f864f6cb1f110f16890b94c8c398cdc051"},"cell_type":"markdown","source":"    In fact it seems that it will take some time to find a good validation - TimeSeriesSplit gives a high variance in scores, so I'll try kfold for now."},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"58e6ff71d4671b1285c10a3d3ecfad927a9a95c7"},"cell_type":"code","source":"params = {\"objective\" : \"regression\",\n          \"metric\" : \"rmse\", \n          #\"max_depth\": 6,\n          \"min_child_samples\": 20, \n          \"reg_alpha\": 0.033948965191129526, \n          \"reg_lambda\": 0.06490202783578762,\n          \"num_leaves\" : 34,\n          \"learning_rate\" : 0.019732018807662323,\n          \"subsample\" : 0.876,\n          \"colsample_bytree\" : 0.85,\n          \"subsample_freq \": 5,\n          #'min_split_gain': 0.024728814179385473,\n          #'min_child_weight': 39.40511524645848\n         }\n\nn_fold = 5\nfolds = KFold(n_splits=n_fold, random_state=42)\n# Cleaning and defining parameters for LGBM\nmodel = lgb.LGBMRegressor(**params, n_estimators = 20000, nthread = 4, n_jobs = -1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f54e71dc0a539034e45332efe9fbe3be8e3c367b","scrolled":true},"cell_type":"code","source":"oof = np.zeros(len(train))\noof_1 = np.zeros(len(train))\nprediction = np.zeros(len(test))\nscores = []\nscores_1 = []\nfeature_importance = pd.DataFrame()\nfor fold_n, (train_index, valid_index) in enumerate(folds.split(X)):\n    print('Fold', fold_n, 'started at', time.ctime())\n    X_train, X_valid = X.iloc[train_index], X.iloc[valid_index]\n    y_train, y_valid = y.iloc[train_index], y.iloc[valid_index]\n\n    model.fit(X_train, y_train, \n            eval_set=[(X_train, y_train), (X_valid, y_valid)], eval_metric='rmse',\n            verbose=500, early_stopping_rounds=100)\n    \n    y_pred_valid = model.predict(X_valid)\n    oof[valid_index] = y_pred_valid.reshape(-1,)\n    scores.append(mean_squared_error(y_valid, y_pred_valid) ** 0.5)\n    \n    y_val = train.iloc[valid_index][['fullVisitorId', 'totals.transactionRevenue']]\n    y_val[\"totals.transactionRevenue\"] = y_val[\"totals.transactionRevenue\"].apply(lambda x : 0.0 if x < 0 else x)\n    y_val[\"totals.transactionRevenue\"] = y_val[\"totals.transactionRevenue\"].fillna(0.0)\n    y_val['totals.transactionRevenue'] = np.expm1(y_val['totals.transactionRevenue'])\n    y_val_sum_true = y_val.groupby('fullVisitorId').sum().reset_index()['totals.transactionRevenue']\n    y_val_sum_true = np.log1p(y_val_sum_true)\n    \n    oof_1[valid_index] = np.expm1(oof[valid_index])\n    \n    val_df = train.iloc[valid_index][['fullVisitorId']]\n    val_df['totals.transactionRevenue'] = oof[valid_index]\n    val_df[\"totals.transactionRevenue\"] = val_df[\"totals.transactionRevenue\"].apply(lambda x : 0.0 if x < 0 else x)\n    val_df[\"totals.transactionRevenue\"] = val_df[\"totals.transactionRevenue\"].fillna(0.0)\n    y_val_sum_pred = val_df.groupby('fullVisitorId').sum().reset_index()['totals.transactionRevenue']\n    y_val_sum_pred = np.log1p(y_val_sum_pred)\n    scores_1.append(mean_squared_error(y_val_sum_true, y_val_sum_pred) ** 0.5)\n    \n       \n    y_pred = model.predict(X_test, num_iteration=model.best_iteration_)\n    prediction += y_pred\n    \n    # feature importance\n    fold_importance = pd.DataFrame()\n    fold_importance[\"feature\"] = X.columns\n    fold_importance[\"importance\"] = model.feature_importances_\n    fold_importance[\"fold\"] = fold_n + 1\n    feature_importance = pd.concat([feature_importance, fold_importance], axis=0)\n    \n    print('')\n    print(f'Fold {fold_n}. RMSE: {scores[-1]:.4f}.')\n    print('')\n\nprediction /= n_fold\nfeature_importance[\"importance\"] /= n_fold","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1f940bfef3fdfea92397dac75abe1ae7ea1c0549"},"cell_type":"code","source":"print('CV mean score: {0:.4f}, std: {1:.4f}.'.format(np.mean(scores), np.std(scores)))\nprint('CV new mean score: {0:.4f}, std: {1:.4f}.'.format(np.mean(scores_1), np.std(scores_1)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"691716d64502a3c43fd4fe826682ea59a7dc78dc"},"cell_type":"code","source":"#lgb.plot_importance(model, max_num_features=30);\n#feature_importance_lgb.sort_values('importance', ascending=False).set_index('features').plot('')\ncols = feature_importance[[\"feature\", \"importance\"]].groupby(\"feature\").mean().sort_values(\n    by=\"importance\", ascending=False)[:50].index\n\nbest_features = feature_importance.loc[feature_importance.feature.isin(cols)]\n\nplt.figure(figsize=(16, 12));\nsns.barplot(x=\"importance\", y=\"feature\", data=best_features.sort_values(by=\"importance\", ascending=False));\nplt.title('LGB Features (avg over folds)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"12ff6144f64ee25fc8bc0e0b65b6bd9574423811"},"cell_type":"code","source":"submission = test[['fullVisitorId']].copy()\nsubmission.loc[:, 'PredictedLogRevenue'] = prediction\nsubmission[\"PredictedLogRevenue\"] = submission[\"PredictedLogRevenue\"].apply(lambda x : 0.0 if x < 0 else x)\nsubmission[\"PredictedLogRevenue\"] = submission[\"PredictedLogRevenue\"].fillna(0.0)\ngrouped_test = submission[['fullVisitorId', 'PredictedLogRevenue']].groupby('fullVisitorId').sum().reset_index()\ngrouped_test.to_csv(f'lgb_cv{np.mean(scores):.4f}_std_{np.std(scores):.4f}_prediction_old.csv', index=False)\noof_df = pd.DataFrame({\"fullVisitorId\": train[\"fullVisitorId\"], \"PredictedLogRevenue\": oof})\noof_df.to_csv(f'lgb_cv{np.mean(scores):.4f}_std_{np.std(scores):.4f}_oof_old.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5ff5e65c34d80788b029350253aa7f00f707f333"},"cell_type":"code","source":"submission = test[['fullVisitorId']].copy()\nsubmission.loc[:, 'PredictedLogRevenue'] = np.expm1(prediction)\nsubmission[\"PredictedLogRevenue\"] = submission[\"PredictedLogRevenue\"].apply(lambda x : 0.0 if x < 0 else x)\nsubmission[\"PredictedLogRevenue\"] = submission[\"PredictedLogRevenue\"].fillna(0.0)\ngrouped_test = submission[['fullVisitorId', 'PredictedLogRevenue']].groupby('fullVisitorId').sum().reset_index()\ngrouped_test[\"PredictedLogRevenue\"] = np.log1p(grouped_test[\"PredictedLogRevenue\"])\ngrouped_test.to_csv(f'lgb_cv{np.mean(scores_1):.4f}_std_{np.std(scores_1):.4f}_prediction_new.csv', index=False)\noof_df = pd.DataFrame({\"fullVisitorId\": train[\"fullVisitorId\"], \"PredictedLogRevenue\": oof_1})\noof_df.to_csv(f'lgb_cv{np.mean(scores_1):.4f}_std_{np.std(scores_1):.4f}_oof_new.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"19540059f9eae13bef1285f5556f7d8a477b9038"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b2e3fe617aadf6002ba4f02a8bd335d4256da1dd"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8e3ab17f1abf9e5e9ed33abed41c57c87efff09c"},"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}