{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"@author: André Daniël VOLSCHENK  \n\nKaggle project {Google Analytics Customer Revenue Prediction}  \nkaggle.com/andredanielvolschenk  \n\n\n# Problem Statement\n\nDESCRIPTION  \nThe 80/20 rule has proven true for many businesses–only a small percentage of customers produce most of the revenue. As such, marketing teams are challenged to make appropriate investments in promotional strategies.  \nRStudio has partnered with Google Cloud and Kaggle to demonstrate the business impact that thorough data analysis can have.  \nIn this competition, you’re challenged to analyze a Google Merchandise Store (also known as GStore, where Google swag is sold) customer dataset to predict revenue per customer. Hopefully, the outcome will be more actionable operational changes and a better use of marketing budgets for those companies who choose to use data analysis on top of GA data.  \n\nEVALUATION  \nSubmissions are scored on the root mean squared error.  \n\nSubmission File  \nFor each fullVisitorId in the test set, you must predict the natural log of their total revenue in PredictedLogRevenue. The submission file should contain a header and have the following format:  \n\n| fullVisitorId                        | PredictedLogRevenue  \n|-----------------------------------------------------------\n| 0000000259678714014 | 0  \n| 0000049363351866189 | 0  \n| 0000053049821714864 | 0  \netc.  \n\nDATA  \n\nWhat files do I need?  \nYou will need to download train.csv and test.csv. These contain the data necessary to make predictions for each fullVisitorId listed in sample_submission.csv.  \n\nAll information below pertains to the data in both CSV and BigQuery format.  \n\nWhat should I expect the data format to be?  \nBoth train.csv and test.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.csv will correspond to a row in the submission, but all unique fullVisitorIds will correspond to a row in the submission.  \n\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!\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.  \n\nWhat am I predicting?  \nWe are predicting the natural log of the sum of all transactions per user.   \n\nFile Descriptions  \n\ntrain.csv - the training set - contains the same data as the BigQuery rstudio_train_set.  \ntest.csv - the test set - contains the same data as the BigQuery rstudio_test_set.  \nsampleSubmission.csv - a sample submission file in the correct format. Contains all fullVisitorIds in test.csv.  \n\nData Fields  \n\n| Data name                      | Description\n|--------------------------------------------------------------\n|  fullVisitorId                     | A unique identifier for each user of the Google Merchandise Store.\n| channelGrouping            | The channel via which the user came to the Store.\n| date                                   | The date on which the user visited the Store.\n| device                                | The specifications for the device used to access the Store.\n| geoNetwork                      | This section contains information about the geography of the user.\n| sessionId                           | A unique identifier for this visit to the store.\n| socialEngagementType   | Engagement type, either \"Socially Engaged\" or \"Not Socially Engaged\".\n| totals                                  | This section contains aggregate values across the session.\n| trafficSource                     | This section contains information about the Traffic Source from which the session originated.\n| visitId                                 | An identifier for this session. This is part of the value usually stored as the _utmb cookie. This is only unique to the user. For a completely unique ID, you should use a combination of fullVisitorId and visitId\n| visitNumber                      |  The session number for this user. If this is the first session, then this is set to 1\n| visitStartTime                  |  The timestamp (expressed as POSIX time)\n\nA more complete description of each column is given: https://support.google.com/analytics/answer/3437719?hl=en\n\nRemoved Data Fields  \nSome fields were censored to remove target leakage. The major censored fields are listed below.  \nhits - This row and nested fields are populated for any and all types of hits. Provides a record of all page visits.  \ncustomDimensions - This section contains any user-level or session-level custom dimensions that are set for a session. This is a repeated field and has an entry for each dimension that is set.  \ntotals - Multiple sub-columns were removed from the totals field.  \n\n# Import libraries\nLets import libraries and see what datafiles we have in our environment."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"2c78fdbecd0c9023349c195a3322961e210a1f53"},"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport json\nimport pandas.io.json as pdjson\n\nimport ast\n\nimport os\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"14dba5d86fda8aa7ef741775bfbe93187b74e749"},"cell_type":"markdown","source":"Note: train_v2.csv has 1'708'337 observations and test_v2.csv has 401'589 observations.  \nThat is a LOT of data!!!  \n\n# Load\n\nRecall that,  due to the formatting of fullVisitorId you must load the Id's as strings in order for all Id's to be properly unique!  \nLets just load 100 rows to take a quick look  \n\nSince `train_v2` and `test_v2`are so big, we will only load 1000 observations for now.  \n\nLets see what data1 looks like:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"1d4bc92773b46e12030985a4dfb3f2db7c85d108"},"cell_type":"code","source":"#path = 'C:\\\\Users\\\\Andre\\\\code\\\\Kaggle\\\\2. Google Analytics Customer Revenue Prediction\\\\train.csv'\npath = '../input/train_v2.csv'\ndata1 = pd.read_csv(path, sep=',', dtype={'fullVisitorId': 'str'}, nrows=100)\ndel(path)\n\n# load the competition test data\n#path = 'C:\\\\Users\\\\Andre\\\\code\\\\Kaggle\\\\2. Google Analytics Customer Revenue Prediction\\\\test.csv'\npath = '../input/test_v2.csv'\ndata2 = pd.read_csv(path, sep=',', dtype={'fullVisitorId': 'str'}, nrows=100)\ndel(path)\n\nprint('data1 shape:', data1.shape)\ndata1.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1a976a03453ef482fb2c7d837911d48124a3e873"},"cell_type":"markdown","source":"Lets see what `data2` looks like:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f4f295387bc1b1725696ff24e5b1c1fe04d81d97"},"cell_type":"code","source":"print('data2 shape:', data2.shape)\ndata2.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"96d2bbcc3f51f83af4f3bcbd5e6d866e168a78bb"},"cell_type":"markdown","source":"From both `data1` and `data2` we can see that there are multiple columns which contain JSON blobs of varying depth.  \n\nJSON is 'JavaScript Object Notation'  \nJSON is unstructured data, in-that each row in the table does not have the same number of column (when sub-columns are expanded).\n\nWe could write a function soon to just flatten all JSON columns that have embedded sub-columns...\n\nLets first make a list of the JSON columns:\n* `customDimensions`\n* `device`\n* `geoNetwork`\n* `hits`\n* `totals`\n* `trafficSource`\n\n\nWe have one problem to adress first: The `hits` and `customDimensions` columns encode its Jason blobs differently. It has the `[` and `]` characters around its blob. Furhermore, it uses single quotes `'` instead of double quotes `\"`.  They will have to receive special consideration.  \n\n# Explore JSON fields\nFirst we merge `data1` and `data2`..."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"24c5c1a294d5dc0325a2b0204ef771838d6aa5b6"},"cell_type":"code","source":"data = data1.append(data2, ignore_index=True)\ndel(data1, data2)\nprint(' data shape:', data.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"173c4211c9c7ceb1c57d0acea4ea95181acfc345"},"cell_type":"markdown","source":"Let us look at the JSON subcolumns in `data1`...  \nLets first look at the first observation in `customDimensions`:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"scrolled":true,"_uuid":"67f7175fa0ee87488d7039ab9a34b2fdd37cbdde"},"cell_type":"code","source":"data.customDimensions[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0a455fbc2220dadf3c14a7a57da82c6266b6c5b5"},"cell_type":"markdown","source":"This JSON blob has 2 subcolumns: index and value  \nThese can be kept in for now.  \n\nNow lets look at `device`:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"59eda25a24bed5f6b5c7bdb4ad6982098f2eeb7f"},"cell_type":"code","source":"data.device[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d89161c8c3e9b51f6ec47e7f8b99815741d85fe5"},"cell_type":"markdown","source":"Some columns here really wont add usefull information to our prediction, namely: 'browserVersion', 'browserSize', 'operatingSystemVersion', 'mobileInputSelector', 'flashVersion', 'screenColors', 'screenResolution'.  \n\nNow lets consider `geoNetwork`:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"cce6581e63b47f6d3515fb59b487df52a30a1fa4"},"cell_type":"code","source":"data.geoNetwork[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b9bff7dd771385aabcb5a9529ff8c67c03edc573"},"cell_type":"markdown","source":"Some useless columns:  \n'cityId', 'latitude', 'longitude', 'networkLocation'.  \n\nNext we consider `hits`:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"9d4a9ac7f4756b9ada6ea7c46b5a76aa528d6be1","scrolled":false},"cell_type":"code","source":"data.hits[0]","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_uuid":"a3c778af0a28f540b59083ac8107f587d7198b8a"},"cell_type":"markdown","source":"That is a lot of columns !  \nIn fact, these are subcolumns within subcolumns! These JSON blobs are deeply embedded!  \nFor now we will keep them all  \n\nNext we look at `totals`:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"42c1f94daa20116b930f4b100c570ee56859e55d"},"cell_type":"code","source":"data.totals[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cc5ab5825c309f6e7578d5aeefe07dedeba04486"},"cell_type":"markdown","source":"We will keep these for now, too.  \n\nFinally we can look at `trafficSource`:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"aaaf837dd3b9f118630db7943b04ecded3d0fb12"},"cell_type":"code","source":"data.trafficSource[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"32083d0eb00a38ef25d31d13bcd8ce0d387e8a45"},"cell_type":"markdown","source":"These are all important to keep, so we keep them.  \n\n# Parsing JSON\nNow we finally declare the function to parse the JSON blobs, keeping in mind the columns we can already delete, and keeing in mind that `customDimensions`, and `hits` are encoded differently:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"1a67ac22dd12ccd9949575f367caf35f7425b5bb"},"cell_type":"code","source":"# Any results you write to the current directory are saved as output.\n\ndef parse(csv_path, nrows=None):\n\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    device_list=df['device'].tolist()\n    \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    return df\nprint(\"The 'parse' function to flatten JSON columns have been created\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"251d6d7f884bae28b15ec4b7d70ab7e683b1a257"},"cell_type":"markdown","source":"Now lets read in `data1` and `data2` with 100'000 rows each, for a total of 200'000 observations.  \nWe then merge these to form `data`:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"b323a5f5010d0582e5bdf7b4d51d9ba412048f2e","scrolled":false},"cell_type":"code","source":"data1 = parse('../input/train_v2.csv', nrows=100000)\ndata2 = parse(\"../input/test_v2.csv\",nrows=100000)\n\nprint('data1 shape: ', data1.shape)\nprint('data2 shape: ', data2.shape)\n\ndata = data1.append(data2, sort=True)\ndel(data1, data2)\n\nprint('number of unique columns in data1 + data2:', data.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"afd819d5ca8d6a1b551473046677801235b9078f"},"cell_type":"markdown","source":"First lets see which columns are still JSON blobs in the form of lists:  "},{"metadata":{"_kg_hide-input":true,"trusted":true,"scrolled":false,"_uuid":"d867f2ab5a3531eab8bc9fc55ca003b5e185a851"},"cell_type":"code","source":"jsonlist=[]\nfor i in range(len(data.columns)):   # for each column\n    if (isinstance(data.iloc[1,i], list) ):  # see if some element 1 is a list\n        jsonlist.append( data.columns[i] )   # if yes, then save name to list\nprint(jsonlist)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5a4a545852dbb518fb39b0f4d16ec87c62a8efb0"},"cell_type":"markdown","source":"Before flattening these, we can immediately look into which columns we can delete.  \nLets look at the number of unique values per column. We want to see the number of unique values *including* nans, because nans *may* indicate something unique about the column.  \nWe also indicate which columns are a JSON blob in the form of a list:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"384916369d44d7411f31acfab10db750b53dd37e","scrolled":false},"cell_type":"code","source":"print(\"Printout for each column's number of unique values (incl. nans)\\n\")\nfor col in data.columns:\n    try:\n        print(col, ':', data[col].nunique(dropna=False))\n    except TypeError:\n        a=data[col].astype('str')\n        #print(a)\n        print( col, ':', a.nunique(dropna=False), ' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> LIST')\n# Clean workspace\ndel(col)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"acd55434954c376331d2f7f74a3384c3072b0135"},"cell_type":"markdown","source":"# Columns with constant values:\n\nLooks like there are quite a few features with only 1 value (including nans!) in the entire dataset.  \nThese columns will obviously not contribute to predictive power, so they may be removed.  \n\nLets remove useless columns and see the new shape of our data:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e6c45dd52b2bbbf8449ff62a37da8b50bd11563e","scrolled":false},"cell_type":"code","source":"print('Data shape before dropping constant columns:', data.shape)\n\nprint('\\nColumns being dropped:')\n\nfor col in data.columns:\n    try:\n        if (data[col].nunique(dropna=False) == 1):\n            del(data[col])\n            print(col)\n    except TypeError:\n        a=data[col].astype('str')\n        if (a.nunique(dropna=False) == 1):\n            del(data[col])\n            print(col)\ndel(col)\n\nprint('\\ndata shape is now:', data.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"937f45f5fffb1c9688b1531b7ff3892b09a20d48"},"cell_type":"markdown","source":"Lets look at what our data looks like now:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"scrolled":false,"_uuid":"adc8dee70d2ec5fe3d8761c6d22cc3660af3daeb"},"cell_type":"code","source":"data.head()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"4f1f6e86339e0e84415a3c6e0344af96b07cbee6"},"cell_type":"markdown","source":"Lets see which JSON columns remain:"},{"metadata":{"trusted":true,"_uuid":"c33b0078e85808ee82c4bf90150c7bd14d9aa414","scrolled":false,"_kg_hide-input":true},"cell_type":"code","source":"print(\"Printout for each column's number of unique values (incl. nans)\\n\")\nfor col in data.columns:\n    try:\n        print(col, ':', data[col].nunique(dropna=False))\n    except TypeError:\n        a=data[col].astype('str')\n        #print(a)\n        print( col, ':', a.nunique(dropna=False), ' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> LIST')\n# Clean workspace\ndel(col)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5c15d7efa85e240f9834cea8adb44b3bb34146e7"},"cell_type":"markdown","source":"There are many JSON columns with only 2 unique values (including nans!)  \nThese can be coded as categorical variables to save space! We will remember this for later.  \nWe only have 2 JSON lists that contain more than 2 unique entries: `hits_product` and `hits_promotion`.  \nLets consider each individually to see what subcolumns they have!  \n\nFor `hits_product`we consider observation 0:"},{"metadata":{"trusted":true,"_uuid":"0a0169e0c5e55f6af5d865020897e2196c78b2dc","_kg_hide-input":true},"cell_type":"code","source":"print('number of unique values in column:', \n      data['hits_product'].astype('str').nunique(dropna=False), '\\n' )\nprint( data['hits_product'].iloc[0] )","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"69d60893ed5c7fbbb00a848deddea5aee8465787"},"cell_type":"markdown","source":"These are a lot of additional columns !  \nBasically this column encodes the products shown in the GStore search result. We will leave this out, as this is a lot of information for what is likely minimal gain, if any.  "},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"d5f195a8766b27a40aa6b7451e4e0b9c038503af"},"cell_type":"code","source":"print('data shape:', data.shape)\ndata = data.drop(labels=['hits_product'], axis=1)\nprint('Removed hits_product')\nprint('data shape:', data.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f36e43ae06d6ccfd6a43003ce1c5b74813256173"},"cell_type":"markdown","source":"Next we look at `hits_promotion`. we consider observation 1:"},{"metadata":{"trusted":true,"_uuid":"264b3e360e93014c47e6786efd66022745f77e70","_kg_hide-input":true,"scrolled":false},"cell_type":"code","source":"print('number of unique values in column:', \n      data['hits_promotion'].astype('str').nunique(dropna=False), '\\n' )\nprint( data['hits_promotion'].iloc[1] )","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cd6bfc820f3ac587a3ae702dad519b1429b223fc"},"cell_type":"markdown","source":"This is again a lot of columns and data, but these are abstracted in some other existing columns that relate to promotions, namely:  \n`hits_promotionActionInfo.promoIsClick`, and `hits_promotionActionInfo.promoIsView`  \n\nLet us delete this column:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"01343322aa5f0669bb0c2383c704370b04f2f948"},"cell_type":"code","source":"print('data shape:', data.shape)\ndata = data.drop(labels=['hits_promotion'], axis=1)\nprint('Removed hits_promotion')\nprint('data shape:', data.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"175beab7571adc1e194e830c4e654a94022e91cd"},"cell_type":"markdown","source":"We now we have 106 features remaining.  \nIn the next notebook, we will continue reducing the number of columns under consideration, and we shall finally start using all the available observations.  \n\nLets printout our columns one last time so that we can re-use them for the next notebook:"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"3e9f0c5e8a08b4ed57b261ef524548b048637d65","scrolled":false},"cell_type":"code","source":"print(\"Printout for each column's number of unique values (incl. nans)\\n\")\nfor col in data.columns:\n    try:\n        print(col)\n    except TypeError:\n        a=data[col].astype('str')\n        #print(a)\n        print( col)\n# Clean workspace\ndel(col)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5dff6a2197a92e414d2ddd244541a813f4529317"},"cell_type":"markdown","source":"\n# Final comments\nWe have completed the data cleaning notebook. In the next notebook we shall reload observations, but immediately delete columns that do not feature in the list as given above, in order to save memory.  \n\n*Acknowledgements*  \nSpecial thank you to the following authors for their insightful kernels:  \nhttps://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields  \nhttps://www.kaggle.com/codlife/pre-processing-for-huge-train-data-with-chunksize  \nhttps://www.kaggle.com/usmanabbas/flatten-hits-and-customdimensions  \n\nI welcome comments and suggestions for improvement!  \n\nPart 2 shall be about Visualization, Exploratory Data Analysis (EDA), and Feature Engineering.  \nEach feature in the shall be explored and discussed.  \nPart 2 : https://www.kaggle.com/andredanielvolschenk/gstore-part-2-visuals-eda-feature-engineering"}],"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}