{"cells":[{"metadata":{"_uuid":"5c9da4c0bb64f9a8ec0042e7d6794216d00b4135"},"cell_type":"markdown","source":"@author: André Daniël VOLSCHENK  \n\nKaggle project {Google Analytics Customer Revenue Prediction}  \nkaggle.com/andredanielvolschenk  \n\n# Preface\nThe *Google Analytics Customer Revenue Prediction* challenge had as its original aim to predict the revenue for a given visit to the store. This was changed, as the aim is now to predict the revenue from some customers in December 2018 and January 2019 (unseen data).  \n\nMy previous kernels,  \nPart 1 : (https://www.kaggle.com/andredanielvolschenk/gstore-part-1-data-cleansing )  \nand Part 2 (https://www.kaggle.com/andredanielvolschenk/gstore-part-2-visuals-eda-feature-engineering)  \nwere written during the original competition. When v2 was announced, and the data was changed substantially, I redid Parts 1 and 2.  \n\nThis is the first kernel I start since v2.\n\n# Contents\n* [Setup](#Setup)\n* [Load data](#Load-data)\n* [Formulating the target for version 2](#Formulating-the-target-for-version-2)\n* [Load data as in Part 2](#Load-data-as-in-Part-2)\n* [Data preparation](#Data-preparation)\n* [LGBM for feature importance](#LGBM-for-feature-importance)\n\n\n\n# Setup\n\n## Problem Statement\nImportant Note\n\nWe have now updated the data to work with the new forward-looking problem formulation. Note that in this competition you will be predicting the target for ALL users in the posted test set: test_v2.csv, for their transactions in the future time period of December 1st 2018 through January 31st 2019.\nWhat files do I need?  \n\nYou will need to download train_v2.csv and test_v2.csv. These contain the data necessary to make predictions for each fullVisitorId listed in sample_submission_v2.csv.  \n\nUnfortunately, due to time constraints, the BigQuery version of this data will not be made available immediately.\nWhat should I expect the data format to be?  \n\nBoth train_v2.csv and test_v2.csv contain the columns listed under Data Fields. Each row in the dataset is one visit to the store. Because we are predicting the log of the total revenue per user, be aware that not all rows in test_v2.csv will correspond to a row in the submission, but all unique fullVisitorIds will correspond to a row in the submission.\nIMPORTANT: Due to the formatting of fullVisitorId you must load the Id's as strings in order for all Id's to be properly unique!  \n\nThere are multiple columns which contain JSON blobs of varying depth. In one of those JSON columns, totals, the sub-column transactionRevenue contains the revenue information we are trying to predict. This sub-column exists only for the training data.\nWhat am I predicting?  \n\nWe are predicting the natural log of the sum of all transactions per user. Once the data is updated, as noted above, this will be for all users in test_v2.csv for December 1st, 2018 to January 31st, 2019.  \n\nNote that the dataset does NOT contain data for December 1st 2018 to January 31st 2019. You must identify the unique fullVisitorIds in the provided test_v2.csv and make predictions for them for those unseen months.\n\n## Import libraries\nLets import libraries and see what datafiles we have in our environment."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":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)\nimport statistics as ss\n\nimport json\nimport pandas.io.json as pdjson\nimport ast # Abstract Syntax Trees : The ast module helps Python applications to process trees of the Python abstract syntax grammar.\nimport datetime as dt\n\nimport gc   # Garbage Collector : gc exposes the underlying memory management mechanism of Python\ngc.enable()\n\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\nimport lightgbm as lgb\nimport sklearn.metrics as sklm\n\nimport os\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"64d0e62b696bce1e8007c9f723b3f84587f39344"},"cell_type":"markdown","source":"# Load data\nFor now lets load in `train_v2` and `test_v2`, but only the columns `fullVisitorId`, `date`, `visitStartTime`, `totals`.  \nFrom these we will generate and save only `fullVisitorId`, `month`, `week`, `weekday`, `hour`, `year`, `day`, `totals_transactionRevenue`."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"1d9b87663684d64792c4339b54335a6d87e5fb79","scrolled":false},"cell_type":"code","source":"json_vars = ['device', 'geoNetwork', 'totals', 'trafficSource', 'hits', 'customDimensions']\n\n# final_vars taken directly from the end of Part 2\nfinal_vars = ['fullVisitorId', 'month','week','weekday','hour', 'year', 'day','totals_transactionRevenue']\nprint('created json_var and final_var')\n\n\ndef extraction(df): # here we declare a function to do all feature extraction as per Part 2.\n    \n    df['date'] = df['date'].apply(lambda x: dt.date(int(str(x)[:4]), int(str(x)[4:6]), int(str(x)[6:])))\n    #% feature representation\n    df.date = pd.to_datetime(df.date, errors='coerce')\n    #% feature extraction - time and date features\n    # Get the month value from date\n    df['month'] = df['date'].dt.month\n    # Get the week value from date\n    df['week'] = df['date'].dt.week\n    # Get the weekday value from date\n    df['weekday'] = df['date'].dt.weekday\n    # Get the year\n    df['year'] = df['date'].dt.year\n    # Get the day of the month\n    df['day'] = df['date'].dt.day\n    df = df.drop(labels=['date'], axis=1)\n    \n    df['visitStartTime'] = pd.to_datetime(df['visitStartTime'], unit='s')\n    df['hour'] = df['visitStartTime'].dt.hour\n    df = df.drop(labels=['visitStartTime'], axis=1)\n    \n    return df\n\n\ndef load_df(csv_path, usecols=None):\n    JSON_COLUMNS = ['totals']#['device', 'geoNetwork', 'totals', 'trafficSource']\n    ans = pd.DataFrame()\n    \n    dfs = pd.read_csv(csv_path, sep=',',\n                      converters={column: json.loads for column in JSON_COLUMNS},\n                      dtype={'fullVisitorId': 'str'}, # Important!!\n                      chunksize = 100000, # 100 000\n                      #nrows=2000,  # TODO: remove this !\n                      usecols=['fullVisitorId', 'date','visitStartTime', 'totals']\n                     )\n                        # if memory runs out, try decrease chunksize\n    \n    for df in dfs:\n        df.reset_index(drop = True,inplace = True)\n        \n        for column in JSON_COLUMNS:\n            column_as_df = pdjson.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        print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n        \n        \n        df = extraction(df) # FEATURE EXTRACTION\n        \n        \n        # we wont see each and every column in each chunk that we load, so we need to find where our master list intersects with the actual data\n        usecols = set(usecols).intersection(df.columns)\n        usecols = list(usecols)\n        use_df = df[usecols]\n        del df\n        \n        #use_df = cardinality_redux(use_df) # DEAL WITH HIGH CARDINALITY\n        \n        gc.collect()\n        ans = pd.concat([ans, use_df], axis = 0).reset_index(drop = True)\n        print('Stored shape:', ans.shape)\n        \n    return ans\n\ndata1 = load_df('../input/train_v2.csv', usecols=final_vars)\nprint('data1 shape: ', data1.shape)\nprint(\"data1 loaded\")\n\ndata1.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"577fa4432dbcd43d03d24042d5523979439c74b2"},"cell_type":"markdown","source":"Just like in Part 2, we now set the NaNs in the target variable (`totals_transactionRevenue`) to zero for `data1`.   \nAs per the problem statement, we need to predict the natural log of the sum of all transactions per user. So lets log transform the target variable.  \nWe can then import and do the same for `data2`"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5d8746f0870e82b814018e01bae668a29ab2baf3","scrolled":false},"cell_type":"code","source":"data1['totals_transactionRevenue'].fillna(0, inplace=True)\ndata1['totals_transactionRevenue'] = np.log1p(data1['totals_transactionRevenue'].astype(float))\n\ndata2 = load_df('../input/test_v2.csv', usecols=final_vars)\nprint('data2 shape: ', data2.shape)\nprint(\"data2 loaded\")\ndata2['totals_transactionRevenue'].fillna(0, inplace=True)\ndata2['totals_transactionRevenue'] = np.log1p(data2['totals_transactionRevenue'].astype(float))\n\ndata2.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ad6fd5b76c5d0497c92b69c5995cf8eb771d03af"},"cell_type":"markdown","source":"# Formulating the target for version 2\nThe competition aim has recently changed. The aim is now to predict which users will return in a given time period, and how much they will spend in that time period. This is significantly more complex.  \n\n**How to approach this**  \n`train_v2` has dates : Aug 1st 2016 - Apr 30th 2018  \n`test_v2` has dates : May 1st 2018 to October 15th 2018  \nwhat we want to predict : December 1st 2018 - January 31st 2019  \n\nWe will be predicting the log of the sum of user revenue for this future time period for all users represented in the posted test set: test_v2.csv, which includes all transactions from May 1st 2018 to October 15th 2018.   \nWe have already taken the log of `train_v2` and `test_v2` in this notebook.  \n\nThe ultimate goal is then to use data and users from `test_v2` [May 1st 2018 to October 15th 2018] to predict the revenue for those users between [December 1st 2018 - January 31st 2019].  \n\nSo we use (5 and a half months) data to predict (2 months) data starting (1 and a half months) after the train set.  \n\nIf we count each half-month as a single \"period\", then the division becomes:  \nWe use 11 periods data to predict 4 periods data starting 3 periods after the train set.  \n\nFirst and foremost we need to create a new feature by combining `year` and `week`. We will start a week count where week 1 starts in the earliest week in 2016 in `train_v2`.  \n\n**Steps we will take**\n1. Use `train_v2` for all training and validation steps. We will do the final test on `test_v2`.\n2. After the model is created, we will make predictions using `test_v2` and submit those predictions. This seperation is strictly enforced to prevent information leakage. This in turn helps us create a more robust model.  \n\nLets start by making the period counter feature, called `count`:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"7882bfa3185517ae99b59c13d4829cbef51b6dcf"},"cell_type":"code","source":"# we take 1 through 15th of each month to be 1 period\n# the 16th till end is period 2\n\ndata1['count'] = ((data1['year'] - 2016) * 24) + (data1['month']-1)*2 + round( data1['day']/30 ) - 13\n\ndata1[['year', 'month', 'day', 'count']].sort_values('count').head()\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dfe2ddcf95e0c81b646b68f5e0ad2f46aaf7b0b9"},"cell_type":"markdown","source":"And now the same for `data2`:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"839d74e5c1c1358f2526e847f6eafe49b300d7e2"},"cell_type":"code","source":"# we take 1 through 15th of each month to be 1 period\n# the 16th till end is period 2\n\ndata2['count'] = ((data2['year'] - 2016) * 24) + (data2['month']-1)*2 + round( data2['day']/30 ) - 13\n\ndata2[['year', 'month', 'day', 'count']].sort_values('count').head()\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"317c7531b16a9ea10860ed7ca7944ab8e85e3c3c"},"cell_type":"markdown","source":"Now lets see the range of periods in the train and test sets:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"7be87610df8464b8788c25f2bd1aef6c7403794d"},"cell_type":"code","source":"print('Range of periods in train set:\\n',\n      data1['count'].min(), 'min\\n',\n      data1['count'].max(), 'max\\n',\n     )\n\nprint('Range of periods in test set:\\n',\n      data2['count'].min(), 'min\\n',\n      data2['count'].max(), 'max\\n',\n     )","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"060f40a16c0c117753804e5310d9b251bed2189a"},"cell_type":"markdown","source":"train: 1 to 42 = 42 periods  \ntest: 43 to 53 = 11 periods  \n\nThe time that we want to predict will be : 57 to 60 = 4 periods  \n\nFirst we will use the train set only. Since it has 42 periods, we can make some training sets by remembering the rule:  \n*We use 11 periods data to predict 4 periods data starting 3 periods after the train set.*  \n\nset 1 : get periods 1 through 11 revenue from periods 15 through 18.  \nset 2 : get periods 2 through 12 revenue from periods 16 through 19.  \n...  \nset 24 : get periods 24 through 34 revenue from periods 38 through 41.  \nset 25 : get periods 25 through 35 revenue from periods 39 through 42.     (last train epoch)  \n...  \nset 36 : get periods 36 through 46 revenue from periods 50 through 53.  \n\nLets write a function `makeSet` that takes as input a start period and returns the data with the revenue for each cutomer in the target period.  "},{"metadata":{"_uuid":"5660d5bcb121ec67f28238d911e1400d6fe4d6af"},"cell_type":"markdown","source":"![](https://i.imgur.com/Rn8J22m.png)"},{"metadata":{"_uuid":"bd111f0c1ad8cc86d9deb9a806b8380f3dfceb99"},"cell_type":"markdown","source":"For example: for the epoch starting at period 6 as shown above, we would type  \n`makeSet(6, data)`."},{"metadata":{"_kg_hide-input":true,"trusted":true,"scrolled":false,"_uuid":"0bd226090f9ec32289ff66b87a7065b5d819f6e5"},"cell_type":"code","source":"def makeSet(count, data, verbose=1):\n    \n    # PART 1 ----- Get targets --------\n    \n    targets = data[ (data['count']>=count+14) & (data['count']<=count+17)][['fullVisitorId', 'totals_transactionRevenue']]\n    targets['revenue'] = targets['totals_transactionRevenue']\n    targets = targets.drop(labels=['totals_transactionRevenue'], axis=1)\n    \n    targets['fullVisitorId'] = targets.fullVisitorId.astype('str')\n    targets = targets.groupby('fullVisitorId').sum()\n    \n    targets['fullVisitorId'] = targets.index\n    targets.reset_index(drop=True, inplace=True)\n    \n    # PART 2 ----- Fill in train set --------\n    \n    train = data[ (data['count']>=count) & (data['count']<=count+10)]\n    train=train.copy()\n    train['revenue'] = 0 # set all to 0 for now\n    \n    loyals = targets[targets.revenue>0]['fullVisitorId']\n    \n    if verbose:\n        print(loyals.shape[0], 'buyers in blue-box')\n        print( len( list(set(train.fullVisitorId.unique()) & set(targets.fullVisitorId.unique())) ) ,'customers in BOTH green and blue boxes')\n        print( len( set(train.fullVisitorId.unique()) & set( loyals ) ) ,'customers return to make a purchase')\n    \n    loyal_purchaser = set(train.fullVisitorId.unique()) & set( loyals )\n    for loyal in loyal_purchaser:\n        train.loc[train.fullVisitorId==loyal,'revenue'] = targets[targets.fullVisitorId==loyal]['revenue'].values[0]\n    \n    revenue=train['revenue']\n    train.drop(labels=['revenue'], axis=1, inplace=True)\n    return train, revenue\n\nprint('done')\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0ffe3ed2771ac9edf8e33a53f6aa3e80e827eb94"},"cell_type":"markdown","source":"We have successfully created a function to form training sets for us.  \n\nLets now actually see how many customers in each training epoch returned during its respective testing epoch.  \nTo do this we will cycle through each train epoch ('green box') to see whether customers returned during its resective test epoch ('blue box').  \n\nLets add `data2` to `data1` to form `data`. We then cycle through all epochs from 1 through "},{"metadata":{"_kg_hide-input":true,"trusted":true,"scrolled":false,"_uuid":"ff44e5c9463c62fc163a08e5ca7e1c5c05825133"},"cell_type":"code","source":"data = data1.append(data2)\ndel(data1, data2)\n\nfor count in range(1,37):   # epoch 1 through 36\n    print('for epoch', count, '----------------------------------------')\n    train,revenue = makeSet(count,data)\n    print( 'number of visits overwritten with a future revenue:', (revenue>0).sum() )\n    del(train)\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b115317218a9795bb0dcac14556ac9382c32b911"},"cell_type":"markdown","source":"Great!  \nWe can see that for each iteration:  \nthere are buyers in each blue-box,  \nthere are customers that were in both the green and blue boxes (overlap customers),  \nthere were overlap customers who made a purchase in the blue box <-- this is what we want to predict.  \nthe overlap customers (each duplicate) in the green box have their revenues overwritten with the sum of revenue from the blue box.  \n\nSo our function works. Now it is time to start finding which features are valuable to predict the target.\n\n# Load data as in Part 2\nLets load the data in the same way that we did in Part 2. We also perform feature extraction as per Part 2 immediately. Finally, high cardinality features are transformed to numeric  aggregates as per Part 2.  \nIn addition, we need the year from date too, as explained in section [Formulating the target for version 2](#Formulating-the-target-for-version-2)"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"22aa2d0ca3ec92242f01cd68a2aef7aa5cadb1f3","scrolled":false},"cell_type":"code","source":"json_vars = ['device', 'geoNetwork', 'totals', 'trafficSource', 'hits', 'customDimensions']\n\n# final_vars taken directly from the end of Part 2\nfinal_vars = ['channelGrouping','customDimensions_index','customDimensions_value','device_browser',\n'device_deviceCategory','device_operatingSystem','fullVisitorId','geoNetwork_city',\n'geoNetwork_continent','geoNetwork_country','geoNetwork_metro','geoNetwork_networkDomain',\n'geoNetwork_region','geoNetwork_subContinent','hits_appInfo.exitScreenName',\n'hits_appInfo.landingScreenName','hits_appInfo.screenName','hits_contentGroup.contentGroup2',\n'hits_contentGroup.contentGroupUniqueViews2','hits_dataSource','hits_eCommerceAction.option',\n'hits_eventInfo.eventCategory','hits_eventInfo.eventLabel','hits_hitNumber','hits_hour',\n'hits_isEntrance','hits_isExit','hits_item.currencyCode','hits_latencyTracking.domContentLoadedTime',\n'hits_latencyTracking.domInteractiveTime','hits_latencyTracking.domLatencyMetricsSample',\n'hits_latencyTracking.domainLookupTime','hits_latencyTracking.pageDownloadTime',\n'hits_latencyTracking.pageLoadSample','hits_latencyTracking.pageLoadTime',\n'hits_latencyTracking.redirectionTime','hits_latencyTracking.serverConnectionTime',\n'hits_latencyTracking.serverResponseTime','hits_latencyTracking.speedMetricsSample',\n'hits_page.hostname','hits_page.pagePath','hits_page.pagePathLevel1','hits_page.pagePathLevel2',\n'hits_page.pagePathLevel3','hits_page.pagePathLevel4','hits_page.pageTitle',\n'hits_promotionActionInfo.promoIsView','hits_referer','hits_social.hasSocialSourceReferral',\n'hits_social.socialNetwork','hits_transaction.currencyCode','hits_type','totals_bounces',\n'totals_hits','totals_newVisits','totals_pageviews','totals_sessionQualityDim','totals_timeOnSite',\n'totals_transactionRevenue','totals_transactions','trafficSource_adContent',\n'trafficSource_adwordsClickInfo.adNetworkType','trafficSource_adwordsClickInfo.gclId',\n'trafficSource_adwordsClickInfo.isVideoAd','trafficSource_adwordsClickInfo.slot',\n'trafficSource_isTrueDirect','trafficSource_medium','trafficSource_referralPath',\n'trafficSource_source','visitId','visitNumber','month','week','weekday','geoNetwork_city_count',\n'geoNetwork_city_hitssum','geoNetwork_city_viewssum','hour', 'year', 'day']\n# added year to this list\n# added day to this list\n\nprint('created json_var and final_var')\n\n\ndef extraction(df): # here we declare a function to do all feature extraction as per Part 2.\n    \n    df['date'] = df['date'].apply(lambda x: dt.date(int(str(x)[:4]), int(str(x)[4:6]), int(str(x)[6:])))\n    #% feature representation\n    df.date = pd.to_datetime(df.date, errors='coerce')\n    #% feature extraction - time and date features\n    # Get the month value from date\n    df['month'] = df['date'].dt.month\n    # Get the week value from date\n    df['week'] = df['date'].dt.week\n    # Get the weekday value from date\n    df['weekday'] = df['date'].dt.weekday\n    # Get the year\n    df['year'] = df['date'].dt.year\n    # Get the day of the month\n    df['day'] = df['date'].dt.day\n    df = df.drop(labels=['date'], axis=1)\n    \n    df['visitStartTime'] = pd.to_datetime(df['visitStartTime'], unit='s')\n    df['hour'] = df['visitStartTime'].dt.hour\n    df = df.drop(labels=['visitStartTime'], axis=1)\n    \n    return df\n\ndef make_countsum(df, dfstr):\n    df[dfstr] = df[dfstr].astype('str')\n    \n    df['totals_hits']=df['totals_hits'].fillna(0).astype('int')\n    df['totals_pageviews']=df['totals_pageviews'].fillna(0).astype('int')\n    \n    df[str(dfstr+'_count')] = df[dfstr]\n    df[str(dfstr+'_count')]=df.groupby(dfstr).transform('count')\n    \n    df[str(dfstr+'_hitssum')] = df.groupby(dfstr)['totals_hits'].transform('sum')\n    df[str(dfstr+'_viewssum')] = df.groupby(dfstr)['totals_pageviews'].transform('sum')\n    del(df[dfstr])\n    return df\n\n\ndef cardinality_redux(df): # this function covnerts high cardinality categorical features to numeric aggregates\n    lst = ['geoNetwork_city', 'geoNetwork_metro', 'geoNetwork_region', 'geoNetwork_country', \n           'geoNetwork_networkDomain', 'hits_appInfo.exitScreenName', \n           'hits_appInfo.landingScreenName', 'hits_appInfo.screenName', 'hits_eventInfo.eventLabel', \n           'hits_page.pagePath', 'hits_page.pagePathLevel1', 'hits_page.pagePathLevel2', \n           'hits_page.pagePathLevel3', 'hits_page.pagePathLevel4', 'hits_page.pageTitle', \n           'hits_referer', 'trafficSource_adContent', 'trafficSource_adwordsClickInfo.gclId']\n    for dfstr in lst:\n        df = make_countsum(df, dfstr)\n    return df\n\n# lets append json_vars with final_vars, because we still need to import the json vars before expanding them\nall_vars  = json_vars + final_vars # the master list of columns to import\n\ndef load_df(csv_path, usecols=None):\n    JSON_COLUMNS = ['totals','device', 'geoNetwork', 'totals', 'trafficSource']\n    ans = pd.DataFrame()\n    \n    dfs = pd.read_csv(csv_path, sep=',',\n                      converters={column: json.loads for column in JSON_COLUMNS},\n                      dtype={'fullVisitorId': 'str'}, # Important!!\n                      chunksize = 50000, # 100 000\n                      nrows=1000000  # TODO: remove this !\n                     )\n                        # if memory runs out, try decrease chunksize\n    \n    for df in dfs:\n        df.reset_index(drop = True,inplace = True)\n        \n        device_list=df['device'].tolist()\n        #deleting unwanted columns before normalizing\n        for device in device_list:\n            del device['browserVersion'],device['browserSize'],device['flashVersion'],device['mobileInputSelector'],device['operatingSystemVersion'],device['screenResolution'],device['screenColors']\n        df['device']=pd.Series(device_list)\n        \n        geoNetwork_list=df['geoNetwork'].tolist()\n        for network in geoNetwork_list:\n            del network['latitude'],network['longitude'],network['networkLocation'],network['cityId']\n        df['geoNetwork']=pd.Series(geoNetwork_list)\n        \n        df['hits']=df['hits'].apply(ast.literal_eval)\n        df['hits']=df['hits'].str[0]\n        df['hits']=df['hits'].apply(lambda x: {'index':np.NaN,'value':np.NaN} if pd.isnull(x) else x)\n    \n        df['customDimensions']=df['customDimensions'].apply(ast.literal_eval)\n        df['customDimensions']=df['customDimensions'].str[0]\n        df['customDimensions']=df['customDimensions'].apply(lambda x: {'index':np.NaN,'value':np.NaN} if pd.isnull(x) else x)\n    \n        JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource','hits','customDimensions']\n\n        for column in JSON_COLUMNS:\n            column_as_df = pdjson.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        print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n        \n        \n        df = extraction(df) # FEATURE EXTRACTION\n        \n        \n        # we wont see each and every column in each chunk that we load, so we need to find where our master list intersects with the actual data\n        usecols = set(usecols).intersection(df.columns)\n        usecols = list(usecols)\n        use_df = df[usecols]\n        del df\n        \n        use_df = cardinality_redux(use_df) # DEAL WITH HIGH CARDINALITY\n        \n        gc.collect()\n        ans = pd.concat([ans, use_df], axis = 0).reset_index(drop = True)\n        print('Stored shape:', ans.shape)\n        \n    return ans\n\ndata1 = load_df('../input/train_v2.csv', usecols=final_vars)\nprint('data1 shape: ', data1.shape)\nprint(\"data1 loaded\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8f3a9e6f38737c5c2e9373c227212a2d330c57c6"},"cell_type":"markdown","source":"Just like in Part 2, we now set the NaNs in the target variable (`totals_transactionRevenue`) to zero for `data1`.   \nAs per the problem statement, we need to predict the natural log of the sum of all transactions per user. So lets log transform the target variable.  "},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e6fe2f7a7f38613a4048fd2ad8383014cd5d7bec"},"cell_type":"code","source":"data1['totals_transactionRevenue'].fillna(0, inplace=True)\ndata1['totals_transactionRevenue'] = np.log1p(data1['totals_transactionRevenue'].astype(float))\n\n#data2 = load_df('../input/test_v2.csv', usecols=final_vars)\n#data2['totals_transactionRevenue'].fillna(0, inplace=True)\n#data2['totals_transactionRevenue'] = np.log1p(data2['totals_transactionRevenue'].astype(float))\n#print('data2 shape: ', data2.shape)\n#print(\"data2 loaded\")\n\n#test_target = data2['totals_transactionRevenue']\n#data2.drop(labels=['totals_transactionRevenue'], axis=1, inplace=True) # remove targets\n\n#data1 = data1.append(data2)  # Append rows of data2 to data1\n#del(data2)\ndata=data1\ndel(data1)\nprint('shape of data:', data.shape)\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"05f8fd4ec4d0bce2f5ad60ee370a0fbea8c22a4c"},"cell_type":"markdown","source":"# Data preparation\nNow that `data` has been created, we can perform simultaneous operations on it.  \nLets first take a peek at our data as it is now:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"9542041efd91d0e9b9760bba3b6b0bf1c9fea750"},"cell_type":"code","source":"data.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"30636f46d5c242a3a6385e98d4fbef81fab3614e"},"cell_type":"markdown","source":"Next we can aggregate some other high cardinality features that can be reduced to very few categories, as per Part 2:  \n(in addition, we could also aggregate `hits_contentGroup.contentGroup2`)"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"831d3b962dcdbc2da7cce301fa1300da250e3be4"},"cell_type":"code","source":"def aggregate(df, col, leave): # fn to aggregate all categories in df[col] except for cols in leave\n    df[col] = df[col].astype('str')\n    include = df[col].unique()  # array of all unique categories\n    include = list(include)\n    include = set(include).difference(set(leave))  # set: take out 'leave' from include\n    include = list(include)\n    df.loc[df[col].isin(include), col] = \"grouped\"  # rename all cols in 'include' to 'grouped'\n    return df\n\n\ndata = aggregate(data, 'customDimensions_value', leave=['North America'])\ndata = aggregate(data, 'device_browser', leave=['Chrome', 'Safari', 'Firefox', 'Internet Explorer'])\ndata = aggregate(data, 'device_operatingSystem', leave=['Windows', 'Macintosh', 'Android', 'iOS', 'Linux', 'Chrome OS'])\ndata = aggregate(data, 'geoNetwork_continent', leave=['Americas'])\ndata = aggregate(data, 'geoNetwork_subContinent', leave=['Northern America'])\ndata = aggregate(data, 'hits_contentGroup.contentGroup2', leave=['(not set)', 'Brands', 'Apparel'])\ndata = aggregate(data, 'hits_social.socialNetwork', leave=['(not set)', 'YouTube'])\ndata = aggregate(data, 'totals_transactions', leave=['nan'])\ndata = aggregate(data, 'trafficSource_referralPath', leave=['/'])\ndata = aggregate(data, 'trafficSource_source', leave=['(direct)','google','youtube.com'])\n\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b482200ff7df2090c14dcd8cd59255e4fc39f4fc"},"cell_type":"markdown","source":"Lets view the categorical features and how many categories remain for each:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"2b39237013a03d8f51c1deb65e0db740604f7497"},"cell_type":"code","source":"for col in data.columns:\n    x=data[col]\n    if (x.apply(np.isreal).all(axis=0)) & ((str(x.dtypes) != 'category')): # if numeric, but not category\n        #print(col, 'is numeric')\n        1+1\n    else:\n        print(col, 'has', data[col].nunique(dropna=False), 'categories')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7c43212b103eaf1c9b6db0f5e86f52264371b3ea"},"cell_type":"markdown","source":"`hits_hitNumber`, `hits_hour`, `totals_sessionQualityDim`, and `totals_timeOnSite` should all be encoded as numeric features:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"62208292c4fd2600f864fdcd33da79d91737d2a9"},"cell_type":"code","source":"data['hits_hitNumber'] = pd.to_numeric(data['hits_hitNumber'], errors='coerce', downcast='unsigned')\ndata['hits_hour'] = pd.to_numeric(data['hits_hour'], errors='coerce', downcast='unsigned')\ndata['totals_sessionQualityDim'] = pd.to_numeric(data['totals_sessionQualityDim'], errors='coerce', downcast='unsigned')\ndata['totals_timeOnSite'] = pd.to_numeric(data['totals_timeOnSite'], errors='coerce', downcast='unsigned')\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"72264cf7d38fc1cef94bc76d97b6285579d8587a"},"cell_type":"markdown","source":"Next lets see if we have any nans left:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"bfac9a6a6bc36d86d764a9fc583af977b49ef136"},"cell_type":"code","source":"print('Showing numeric features with nans only:')\nfor col in data.columns:\n    if (data[col].isnull().any()) & (col != 'fullVisitorId') & (col != 'visitId'): # keep IDs as string\n        if (data[col].apply(np.isreal).all(axis=0)) & ((str(data[col].dtypes) != 'category')):\n            print(col, 'has', data[col].isnull().sum(), 'nans' ) # check each column.\nprint('------------------------------------------------------------------------------')\nprint('Showing categorical features with nans only:')\nfor col in data.columns:\n    if (data[col].isnull().any()):\n        if (data[col].apply(np.isreal).all(axis=0))==0 & ((str(data[col].dtypes) != 'category'))==0:\n            print(col, 'has', data[col].isnull().sum(), 'nans' ) # check each column.\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"564cc4c2264acfd0e112cbd87f7e7197cc74053a"},"cell_type":"markdown","source":"`hits_isEntrance`, `hits_isExit`, `hits_promotionActionInfo.promoIsView`, `trafficSource_adwordsClickInfo.isVideoAd`, and `trafficSource_isTrueDirect` should be categorical.  \n\n`totals_sessionQualityDim`, `totals_timeOnSite` should encode nans as 0.  \n\nFor `hits_hitNumber`, `hits_hour` we will replace nan with the mode.  \n\n`totals_transactionRevenue` will have nans for the test set.\n\nThe nans in the categorical features will become categories unto themselves."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5430ad643b5af0388a51e0582c7f636f79bfd6d5"},"cell_type":"code","source":"# Convert to object:\ndata['hits_isEntrance'] = data['hits_isEntrance'].astype('str')\ndata['hits_isExit'] = data['hits_isExit'].astype('str')\ndata['hits_promotionActionInfo.promoIsView'] = data['hits_promotionActionInfo.promoIsView'].astype('str')\ndata['trafficSource_adwordsClickInfo.isVideoAd'] = data['trafficSource_adwordsClickInfo.isVideoAd'].astype('str')\ndata['trafficSource_isTrueDirect'] = data['trafficSource_isTrueDirect'].astype('str')\n\n# Encode nans as 0:\ndata['totals_sessionQualityDim'].fillna(0, inplace=True)\ndata['totals_timeOnSite'].fillna(0, inplace=True)\n\n# replace nans with mode:\ndata['hits_hitNumber'].fillna(ss.mode(data['hits_hitNumber']), inplace=True)\ndata['hits_hour'].fillna(ss.mode(data['hits_hour']), inplace=True)\n\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bd89415f1b9ae6c0513bd95ac4cfc7ebf8ac11d2"},"cell_type":"markdown","source":"Lets see the nans in numeric features again and number of categories in categorical features again.  \nwe will highlight any categorical features with 1 category only:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"a59d98033fb0d7f54b384891f64fe950070a2c01"},"cell_type":"code","source":"print('Showing numeric features with nans only:')\nfor col in data.columns:\n    if (data[col].isnull().any()):\n        if (data[col].apply(np.isreal).all(axis=0)) & ((str(data[col].dtypes) != 'category')):\n            print(col, 'has', data[col].isnull().sum(), 'nans' ) # check each column.\nprint('------------------------------------------------------------------------------')\nprint('Showing categorical features only:')\nfor col in data.columns:\n    x=data[col]\n    if (x.apply(np.isreal).all(axis=0)) & ((str(x.dtypes) != 'category')): # if numeric, but not category\n        #print(col, 'is numeric')\n        1+1\n    elif data[col].nunique(dropna=False) > 2:\n        print(col, 'has', data[col].nunique(dropna=False), 'categories')\n    elif data[col].nunique(dropna=False) == 1:\n        print(col, 'has', data[col].nunique(dropna=False), 'categories <-----------------')\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"50c4dca4622b741bbc152ca1f8ad369ed1e0f55d"},"cell_type":"markdown","source":"No categorical features have only 1 category.  \n\nNow we can represent categorical data as orderred categories. This is a quick approach to prevent feature explosion and get a feel for which features are powerful. We can one-hot-encode powerful features later in Part 4."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"192206585fe9191b8655e21dc39c15a192c09bbf"},"cell_type":"code","source":"for col in data.columns:\n    x=data[col]\n    if (x.apply(np.isreal).all(axis=0)) & ((str(x.dtypes) != 'category')): # if numeric, but not category\n        #print(col, 'is numeric')\n        1+1\n    else:\n        data[col] = data[col].astype('category',copy=False)\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2437e0474c92eecdb5fbd89b2b39c48b4c928786"},"cell_type":"markdown","source":"All our features should now be numeric or categorical!  \nBut since we one-hot-encoded our categorical features, we should have only floats, int, and boolean ... i.e. all numeric features!  \nLets see if any features are not formatted:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"dcc812836e7197aae4371cd96d39a8451e80ffaa"},"cell_type":"code","source":"data.dtypes","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cc102c6ab3462216198e1c6fa6b52135d4a6d52c"},"cell_type":"markdown","source":"Seems that all features are the correct types!\nAll are numeric or categorical.  \n\nLets make the period counter feature, called `count`:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"b2d22d4602a464d9fc6d7b976e80b0f04e032d48"},"cell_type":"code","source":"# we take the 1st through 15th of each month to be 1 period\n# the 16th till end is period 2\n\ndata['count'] = ((data['year'] - 2016) * 24) + (data['month']-1)*2 + round( data['day']/30 ) - 13\n\ndata[['year', 'month', 'day', 'count']].sort_values('count').head()\n\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"35c82b5d98e4ea87f6a029a4ed67deffde9dfbc0"},"cell_type":"markdown","source":"Lets see what our data looks like one last time:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"73cfb97bf53e92f47616c6ebe7237c0838d02776"},"cell_type":"code","source":"data.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c79b2ba4ff3d24378f05129c211ecaeb2d6c7292"},"cell_type":"markdown","source":"Some last columns we can safely delete are `visitId`,  `year`."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"28c28a367da708199fae685365b2dfc11e264e3d"},"cell_type":"code","source":"data.drop(labels=['visitId','year'], axis=1, inplace=True)\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"88cf323eaab5f7d70eb1ef9c141c049f55a52a76"},"cell_type":"markdown","source":"# LGBM for feature importance\nIn order to eliminate features before our serious Machine Learning takes place, we will assign feature importance using a preliminary Machine Learning step.  \nLets apply the LGBM (Light Gradient Boosting Machine) to assign feature importances.  \nThe LGBM is super fast and should give us a decent indication for which features have no relevance.  \n\nOur performance metric is RMSE (Root-mean-square-error).  \n\nWe use training sets 1 through 25 as explained earlier.  \n\nBecause of the massive amount of data, we shall use 3 appended train sets and 1 validation set per run.  \nFor example: sets 1,2,3 for training and set 4 for validation.  \nNext iteration: sets 2,3,4 for training and set 5 for validation.  \n*etc*\n\nThis is a rough way to do this, but it should give us an idea of our feature importances.  \n\nFor now lets only use a third of our data. We will use sets 1 through 9."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"7eacba2ddb1bad295405553a4696833d9a07344c","scrolled":false},"cell_type":"code","source":"def rmse(y_true, y_pred):\n    return np.sqrt(sklm.mean_squared_error(y_true, y_pred))\n\n\ndef plot_importances(imps):\n    mean_gain = np.log1p(imps[['gain', 'feature']].groupby('feature').mean())\n    imps['mean_gain'] = imps['feature'].map(mean_gain['gain'])\n    plt.figure(figsize=(10, 16))\n    sns.barplot(x='gain', y='feature', data=imps.sort_values('mean_gain', ascending=False))\n    plt.tight_layout()\n    #plt.savefig('importances.png')\n\n\n\n\ndef main():\n    importances = pd.DataFrame()\n    feature_name = data.columns\n    params = { 'metric': 'rmse' }\n    est_lgbm = lgb.LGBMRegressor(boosting_type='gbdt', num_leaves=32, max_depth=5,\n                                  learning_rate=0.01, n_estimators=10000, subsample=0.8, \n                                  subsample_freq=1, colsample_bytree=0.8,\n                                  reg_alpha=0.05, reg_lambda=0.05, random_state=1, **params)\n    \n    \n    for count in range(1,11): # (1,11) will range from 1 to 10\n        trn_x, trn_y = makeSet(count, data, 0)\n        x, y = makeSet(count+1, data, 0)\n        trn_x=trn_x.append(x)\n        trn_y=trn_y.append(y)\n        trn_x, trn_y = makeSet(count+2, data, 0)\n        trn_x=trn_x.append(x)\n        trn_y=trn_y.append(y)\n        del(x,y)\n        val_x, val_y = makeSet(count+3, data, 0) # give each set a chance to be the validation set\n        \n        # Train estimator.\n        est_lgbm.fit(trn_x, trn_y, \n                     eval_set=[(val_x, val_y)],\n                     eval_metric='rmse',\n                     early_stopping_rounds=50, \n                     verbose=False)\n        # Prediction and evaluation on validation data set.\n        val_pred = est_lgbm.predict(val_x)\n        rmse_valid = rmse(val_y, np.maximum(0, val_pred))\n        #mean_rmse += rmse_valid\n        #print(\"%d RMSE: %.5f\" % (fold_idx + 1, rmse_valid))\n        # Prediction of testing data set.\n        #y_test_vec += np.expm1(np.maximum(0, est_lgbm.predict(test_df)))\n        # Set feature importances.\n        imp_df = pd.DataFrame()\n        imp_df['feature'] = feature_name\n        imp_df['gain'] = est_lgbm.feature_importances_\n        #imp_df['fold'] = fold_idx + 1\n        importances = pd.concat([importances, imp_df], axis=0, sort=False)\n        \n        #print(\"Mean RMSE: %.5f\" % (mean_rmse / N_SPLITS))\n        \n        #del train_df, test_df, train_tvals\n        gc.collect()\n        \n        #imps = imps.\n    \n    # Plot feature importances\n    print('Plot feature importances')\n    #print(importances)\n    plot_importances(importances)\n    \n    \nif __name__ == '__main__':\n    main()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"358e9139ce2978161755da526374096c1ed4f341"},"cell_type":"markdown","source":"Wonderful!  \nWe can see that some features are far more important to the LGBM than others. Based on this visual, we can choose which features to load for Part 4, where the serious Machine Learning will take place.  \n\n*Acknowledgements*  \nSpecial thank you to the following authors for their insightful kernels:  \nhttps://www.kaggle.com/yoshoku/gacrp-v2-starter-kit/code  \n"}],"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}