{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"\nimport os\nimport pandas as pd\nimport numpy as np\nfrom pandas.io.json import json_normalize\nimport json\nimport time\nimport warnings\n\n#from pycountry_convert import ( map_countries, country_name_to_country_alpha3,)\nimport pytz as pytz\nimport datetime\n\n#Plotting\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nplt.style.use('ggplot')\n\n#Sklearn\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.model_selection import KFold\n\n#lgm and graph viz\nimport graphviz \nimport lightgbm as lgb\n\nwarnings.filterwarnings('ignore')\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e9fceeef9e657c72a7dd152f0a084829d7a642b8"},"cell_type":"code","source":"def load_df(csv_path='../input/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', 'visitId':'str', 'visitStartTime':'str', 'date':'str'}, \n                     nrows=nrows)\n\n    #Normalize JSON colunmns and drop\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    return df\n\n\ndef drop_constant_cols(df):\n    ## Drop constant columns\n    const_cols = [c for c in df.columns if df[c].nunique(dropna=False) == 1]\n    df.drop(const_cols, axis=1, inplace=True)\n    \n    #this columnm is only in train data\n    try:\n        df.drop('trafficSource.campaignCode', axis=1, inplace=True)   \n    except:\n        None   \n    \n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"13e5a6bf02268a370e6493ac28e52a349ebe0c69"},"cell_type":"code","source":"os.listdir('../input')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"014943e71faed7e9fe2ee1b3b6a9d3578c0b912f"},"cell_type":"code","source":"%%time\n#Load\ntrain_df = load_df(csv_path='../input/ga-customer-revenue-prediction/train.csv', nrows = None)\n#train_df.to_pickle('train_flat_no_drop.pkl')\ndrop_constant_cols(train_df)\n\ntest_df = load_df(csv_path='../input/ga-customer-revenue-prediction/test.csv', nrows = None)\n#train_df.to_pickle('test_flat_no_drop.pkl')\ndrop_constant_cols(test_df)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"812505f0f2eb398ab2afa150e29ac2e1924c4e18"},"cell_type":"code","source":"# Extract target values and Ids\ncat_cols = ['channelGrouping','device.browser',\n       'device.deviceCategory', 'device.isMobile', 'device.operatingSystem',\n       'geoNetwork.city', 'geoNetwork.continent', 'geoNetwork.country',\n       'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region',\n       'geoNetwork.subContinent','trafficSource.adContent',\n       'trafficSource.adwordsClickInfo.adNetworkType',\n       'trafficSource.adwordsClickInfo.gclId',\n       'trafficSource.adwordsClickInfo.isVideoAd',\n       'trafficSource.adwordsClickInfo.page',\n       'trafficSource.adwordsClickInfo.slot', 'trafficSource.campaign',\n       'trafficSource.isTrueDirect', 'trafficSource.keyword',\n       'trafficSource.medium', 'trafficSource.referralPath',\n       'trafficSource.source'  ]\n\n\nnum_cols = ['visitNumber', 'totals.bounces', 'totals.hits',\n            'totals.newVisits', 'totals.pageviews', \n            '_local_hourofday'  ]\n\ninteraction_cols = ['totals.hits / totals.pageviews', 'totals.hits * totals.pageviews',\n       'totals.hits - totals.pageviews']\n\nvisitStartTime = ['visitStartTime']\n\nID_cols = ['date', 'fullVisitorId', 'sessionId', 'visitId']\n\ntarget_col = ['totals.transactionRevenue']\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2897e2379eea48e2364f522c3b06cd3ae58c11fe"},"cell_type":"code","source":"os.listdir('../input/geocodes-timezones')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"546bc830b066e9e2f4d92eb020a1ced7e21cd8d7"},"cell_type":"code","source":"#Load\ngeocode_df= pd.read_pickle('../input/geocodes-timezones/geocodes_timezones.pkl')\n\ndef time_zone_converter(x):\n    \n    try:\n        return pytz.country_timezones(x)[0]\n    except AttributeError:\n        return np.nan\n   \n\ndef time_localizer(s):\n    #format of series [time,zone]\n    try:\n        tz =pytz.timezone(s[1])\n        return pytz.utc.localize(s[0], is_dst=None).astimezone(tz)\n    except:\n        return np.nan\n    \ndef remove_missing_vals(x):\n    remove_list = ['(not set)', 'not available in demo dataset','unknown.unknown']\n    if x in remove_list:\n        return ''\n    else:\n        return x \n    \ndef map_timezone(x):   \n    try:\n        return timezone_dict[x]\n    except KeyError:\n        return 'UTC'\n\n","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"%%time\ntrain_df['visitStartTime'] = pd.to_datetime(train_df['visitStartTime'], unit = 's')\ntest_df['visitStartTime'] = pd.to_datetime(test_df['visitStartTime'], unit = 's')\n\n#Generate foreign key '_search_term' by concatenating city, region, country\ntrain_df['_search_term'] = train_df['geoNetwork.city'].map(remove_missing_vals) + ' ' + train_df['geoNetwork.region'].map(remove_missing_vals) + ' ' + train_df['geoNetwork.country'].map(remove_missing_vals)\ntest_df['_search_term'] = test_df['geoNetwork.city'].map(remove_missing_vals) + ' ' + test_df['geoNetwork.region'].map(remove_missing_vals) + ' ' + test_df['geoNetwork.country'].map(remove_missing_vals)\n\n#Set global variable, needed for map_timezone function\nglobal timezone_dict\ntimezone_dict = dict(zip(geocode_df['search_term'], geocode_df['timeZoneId']))\n\n#Map timezones\ntrain_df['_timeZoneId'] = train_df['_search_term'].map(map_timezone)\ntest_df['_timeZoneId'] = test_df['_search_term'].map(map_timezone)\n  \n#Create time zone aware column\ntrain_df['_local_time'] = train_df[['visitStartTime', '_timeZoneId']].apply(time_localizer, axis = 1).astype(str)\ntest_df['_local_time'] = test_df[['visitStartTime', '_timeZoneId']].apply(time_localizer, axis = 1).astype(str)  \n\n#Localize hour time\ntrain_df['_local_hourofday'] = train_df['_local_time'].str[11:13]\ntest_df['_local_hourofday'] = test_df['_local_time'].str[11:13]\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"71f493e7c8bac0ee6b027da699e8788a63bca12b"},"cell_type":"code","source":"%%time\ndef map_longitude(x):   \n    try:\n        return longitude_dict[x]\n    except KeyError:\n        return np.nan\n    \ndef map_latitude(x):   \n    try:\n        return latitude_dict[x]\n    except KeyError:\n        return np.nan\n    \nglobal longitude_dict\nlongitude_dict = dict(zip(geocode_df['search_term'], geocode_df['geometry.location.lng']))\n\nglobal latitude_dict\nlatitude_dict = dict(zip(geocode_df['search_term'], geocode_df['geometry.location.lat']))\n\n\n#Map latitude\ntrain_df['_latitude'] = train_df['_search_term'].map(map_latitude)\ntest_df['_latitude'] = test_df['_search_term'].map(map_latitude)\n\n#Map longitude\ntrain_df['_longitude'] = train_df['_search_term'].map(map_longitude)\ntest_df['_longitude'] = test_df['_search_term'].map(map_longitude)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bdd2458cf8656f32e2dba37f91466cc44d77711f"},"cell_type":"markdown","source":"# Time since last visit "},{"metadata":{"trusted":true,"_uuid":"81decb31ba798fdb596d8ec7eb3adb2531df3d06"},"cell_type":"code","source":"%%time\ntrain_ts = train_df[['fullVisitorId', 'sessionId', 'visitId', 'visitNumber', 'visitStartTime']].copy()\ntest_ts = test_df[['fullVisitorId', 'sessionId', 'visitId', 'visitNumber', 'visitStartTime']].copy()\n\n\ntrain_df['_time_since_last_visit'] = train_ts.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime'].diff()\ntrain_df['_time_since_last_visit_2'] = train_ts.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime'].diff(2)\ntest_df['_time_since_last_visit'] = test_ts.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime'].diff()\ntest_df['_time_since_last_visit_2'] = test_ts.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime'].diff(2)\n\ntrain_df['_time_to_next_visit'] = train_ts.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime'].diff(-1)\ntrain_df['_time_to_next_visit_2'] = train_ts.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime'].diff(-2)\ntest_df['_time_to_next_visit'] = test_ts.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime'].diff(-1)\ntest_df['_time_to_next_visit_2'] = test_ts.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime'].diff(-2)\n\n#del train_ts\n#del test_ts","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5190672e5f7a749e3dcd4d6a6ed415b2f86337e9"},"cell_type":"code","source":"%%time\nfor col in ['totals.bounces', 'totals.hits','totals.pageviews',  '_local_hourofday']:\n    train_df['_prev_{}_1'.format(col)] = train_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')[col].shift(1)\n    test_df['_prev_{}_1'.format(col)] = test_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')[col].shift(1)\n    train_df['_prev_{}_2'.format(col)] = train_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')[col].shift(2)\n    test_df['_prev_{}_2'.format(col)] = test_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')[col].shift(2)\n    \n    train_df['_next_{}_1'.format(col)] = train_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')[col].shift(-1)\n    test_df['_next_{}_1'.format(col)] = test_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')[col].shift(-1)\n    train_df['_next_{}_2'.format(col)] = train_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')[col].shift(-2)\n    test_df['_next_{}_2'.format(col)] = test_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')[col].shift(-2)\n    \n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dd0224a0a006e24ccfebb72e4ee7f26eff248ea9"},"cell_type":"markdown","source":"## Previous numerical values "},{"metadata":{"_uuid":"14e8aea99fee98bf4f4037b6fa76595dcebef091"},"cell_type":"markdown","source":"test_df['_previous_'] = test_ts.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime'].diff()\n\nnum_cols = ['visitNumber', 'totals.bounces', 'totals.hits',\n            'totals.newVisits', 'totals.pageviews', \n            '_local_hourofday'  ]"},{"metadata":{"trusted":true,"_uuid":"d299d516e8fa434584602383bda23681377f7f99"},"cell_type":"code","source":"%%time\ntrain_df['_time_first_visit'] = train_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime']\\\n.transform('first')\ntrain_df['_time_last_visit'] = train_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime']\\\n.transform('last')\ntrain_df['_difference_first_last'] = train_df['_time_last_visit'] - train_df['_time_first_visit']\ntrain_df['_time_since_first_visit'] = train_df['visitStartTime'] - train_df['_time_first_visit']\ntrain_df.drop(['_time_first_visit', '_time_last_visit'], axis = 1,inplace = True)\n\n\ntest_df['_time_first_visit'] = test_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime']\\\n.transform('first')\ntest_df['_time_last_visit'] = test_df.sort_values(['fullVisitorId', 'visitStartTime']).groupby('fullVisitorId')['visitStartTime']\\\n.transform('last')\ntest_df['_difference_first_last'] = test_df['_time_last_visit'] - test_df['_time_first_visit']\ntest_df['_difference_first_last'] = test_df['_time_last_visit'] - test_df['_time_first_visit']\ntest_df['_time_since_first_visit'] = test_df['visitStartTime'] - test_df['_time_first_visit']\ntest_df.drop(['_time_first_visit', '_time_last_visit'], axis = 1,inplace = True)\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"db16dab4405a2ca5b81ca03aa8fe3ed1aa0905bc"},"cell_type":"code","source":"train_df.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cb989c825dd9d2e7d3cb1d132ccb7c43e9df98a9"},"cell_type":"code","source":"%%time\n#train_df['_time_since_last_visit'] = pd.to_numeric(train_df['_time_since_last_visit'])\n#test_df['_time_since_last_visit'] = pd.to_numeric(test_df['_time_since_last_visit'])\n\n#Preparation\nwip_cols = ['fullVisitorId', 'sessionId', 'visitId',\n       'visitNumber', 'visitStartTime','totals.bounces', 'totals.hits',\n       'totals.newVisits', 'totals.pageviews', '_time_since_last_visit']\n\ntrain_ts = train_df.sort_values(['fullVisitorId', 'visitStartTime']).reset_index()\ntrain_ts['index'] = train_ts['index'].astype('str')\ntrain_ts_grouped = train_ts.groupby('fullVisitorId')\n\n#Calculating rolling frequency\ntemp_roll = train_ts_grouped.rolling('12H', on ='visitStartTime')['visitNumber'].count().reset_index().add_suffix('_12H') \ntrain_ts = pd.concat([train_ts, temp_roll['visitNumber_12H']], axis = 1)\n\ntemp_roll = train_ts_grouped.rolling('7D', on ='visitStartTime')['visitNumber'].count().reset_index().add_suffix('_7D') \ntrain_ts = pd.concat([train_ts, temp_roll['visitNumber_7D']], axis = 1)\n\ntemp_roll = train_ts_grouped.rolling('30D', on ='visitStartTime')['visitNumber'].count().reset_index().add_suffix('_30D') \ntrain_ts = pd.concat([train_ts, temp_roll['visitNumber_30D']], axis = 1)\n\ntrain_ts['index'] = train_ts['index'].astype('int')\ntrain_ts.set_index('index', inplace = True)\ntrain_ts.sort_index(inplace = True)\ntrain_df = train_ts.copy()\ndel train_ts\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9065763646b1f76774c7659f128e25f8bc9181c6"},"cell_type":"code","source":"train_df.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b44d131fbc763feba1c71e13f14172438e731a7b"},"cell_type":"code","source":"%%time\n\ntest_ts = test_df.sort_values(['fullVisitorId', 'visitStartTime']).reset_index()\ntest_ts['index'] = test_ts['index'].astype('str')\ntest_ts_grouped = test_ts.groupby('fullVisitorId')\n\n#Calculating rolling frequency\ntemp_roll = test_ts_grouped.rolling('12H', on ='visitStartTime')['visitNumber'].count().reset_index().add_suffix('_12H') \ntest_ts = pd.concat([test_ts, temp_roll['visitNumber_12H']], axis = 1)\n\ntemp_roll = test_ts_grouped.rolling('7D', on ='visitStartTime')['visitNumber'].count().reset_index().add_suffix('_7D') \ntest_ts = pd.concat([test_ts, temp_roll['visitNumber_7D']], axis = 1)\n\ntemp_roll = test_ts_grouped.rolling('30D', on ='visitStartTime')['visitNumber'].count().reset_index().add_suffix('_30D')\ntest_ts = pd.concat([test_ts, temp_roll['visitNumber_30D']], axis = 1)\n\ntest_ts['index'] = test_ts['index'].astype('int')\ntest_ts.set_index('index', inplace = True)\ntest_ts.sort_index(inplace = True)\ntest_df = test_ts.copy()\ndel test_ts\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0567ad89095a0c1cea6479982017b1834c4e9b0e"},"cell_type":"code","source":"test_df.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"83a0e62b8ec186431d5eba17e9f7065f18e992ba"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c68c159be35ae6efacf603e2342e1f035462e8ae"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c5ef3941b6c320562128fb5c4978c6962b7f0d58"},"cell_type":"code","source":"train_df.to_pickle('train_flat_FE.pkl')\ntest_df.to_pickle('test_flat_FE.pkl')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c77bc200b9640b24bc514148854ad3b1c81fcc3b"},"cell_type":"markdown","source":"# Categoricals processing "},{"metadata":{"trusted":true,"_uuid":"b551a41c89a17721a4c1a99e152270ffc5c83a38"},"cell_type":"code","source":"%%time\n#Categorical encoding\nfor c in cat_cols:\n    #Convert NAs to unknown\n    train_df[c] = train_df[c].fillna('unknown')\n    test_df[c] = test_df[c].fillna('unknown')\n\n\n#Rename \"Other\" those with less than 10\nfor col in cat_cols:\n    #For train data\n    series1 = pd.value_counts(train_df[col])\n    mask1 = series1 < 10\n    train_df[col] = np.where(train_df[col].isin(series1[mask1].index),'Other_{}'.format(col), train_df[col])\n    \n    #For test data\n    series2 = pd.value_counts(test_df[col])\n    mask2 = series2 < 10\n    test_df[col] = np.where(test_df[col].isin(series2[mask2].index),'Other_{}'.format(col), test_df[col])\n    ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"45fa96ce3e0ddf81f7a19302126b705f045a16fe"},"cell_type":"code","source":"%%time\ninteract_cats = ['channelGrouping', 'device.operatingSystem',\n                'geoNetwork.city', 'geoNetwork.country', 'geoNetwork.networkDomain',\n                 'trafficSource.medium', \n                'trafficSource.referralPath', 'trafficSource.source']\n\n#2-way interactions\nfrom itertools import combinations\n\ndef categorical_interaction_terms_2(df, columns):\n    for c in combinations(columns,2):\n        df['{}+{}'.format(c[0], c[1]) ] = df[c[0]] + '_' + df[c[1]]\n    return df\n\ndef categorical_interaction_terms_3(df, columns):\n    for c in combinations(columns,3):\n        df['{}+{}+{}'.format(c[0], c[1], c[2]) ] = df[c[0]] + '_' + df[c[1]] + '_' + df[c[2]]\n    return df\n\ntrain_df = categorical_interaction_terms_2(train_df,interact_cats )\n#train_df = categorical_interaction_terms_3(train_df,interact_cats )\n\ntest_df = categorical_interaction_terms_2(test_df,interact_cats )\n#test_df = categorical_interaction_terms_3(test_df,interact_cats )\n\ninteract_cats_to_keep = [ 'geoNetwork.city+geoNetwork.networkDomain',\n  'device.operatingSystem+geoNetwork.networkDomain',\n  'device.operatingSystem+geoNetwork.city', \n  'channelGrouping+geoNetwork.networkDomain',\n  'geoNetwork.city+trafficSource.source',\n 'geoNetwork.networkDomain+trafficSource.source',\n 'geoNetwork.networkDomain+trafficSource.referralPath',\n 'geoNetwork.networkDomain+trafficSource.medium',\n 'geoNetwork.city+trafficSource.medium',\n 'geoNetwork.city+geoNetwork.country']\n\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"57416a80a1e7d73fbba8b53755277fbb240332e2"},"cell_type":"markdown","source":"# Label encoding "},{"metadata":{"trusted":true,"_uuid":"497c0ddbcdc5cc7f61819e2843cca6cd6c156712"},"cell_type":"code","source":"%%time\n\n#Factorize cats\nfor f in (cat_cols + interact_cats_to_keep ):\n    train_df[f], indexer = pd.factorize(train_df[f])\n    test_df[f] = indexer.get_indexer(test_df[f])\n\ndel indexer","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8c839665ea31cdf14443b387eb5f52eb0832f768"},"cell_type":"code","source":"train_df.to_pickle('train_flat_FE_CAT_LE.pkl')\ntest_df.to_pickle('test_flat_FE_CAT_LE.pkl')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"495e6d18764ab36a465d0d511337a2a8bf9f55fa"},"cell_type":"code","source":"train_df.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"04bd5e681683d0d9de3af19be9d13b1f73b69c24"},"cell_type":"code","source":"test_df.info()","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}