{"cells":[{"metadata":{"_uuid":"7702f43cab559779023be5ca9ce2c7e74c2eb78e"},"cell_type":"markdown","source":"<a id='top'></a>\n# Google Analytics Customer Revenue Prediction basic EDA and baseline\nThis kernel uses the new dataset **[GA data with json columns](https://www.kaggle.com/colinpearse/ga-analytics-with-json-columns)** which was converted using **[Google Analytics Data Without Pesky JSON](https://www.kaggle.com/colinpearse/google-analytics-data-without-pesky-json)**. This means newly initialised runs don't have to wait the five minutes or so for converting the JSON fields in train and test.\n\nThis Kernel has the following sections.\n\n#### [Initialisation](#initialisation) Importing, listing directories, reading the data and defining generic functions.\n\n#### [Simplify the data](#simplify) Dropping, creating, converting columns and printing a summary.\n\n#### [EDA: pie and scatter](#eda) Pie charts and scatter graphs a plenty.\n\n#### [Prepare the data](#prepare) Choosing the most useful columns, splitting the data into train and validation parts, encoding it and printing a summary.\n\n#### [The Neural Net](#neuralnet) Putting the data through LightGBM for a baseline score.\n\n<br>*NaN values: The pie and scatter chart functions use fillna(-1) because it yields useful information. However, filling NaN with -1 did not improve the baseline score. Q. would any \"NaN filler\" improve the machine learning score? \"0\" is not represented in the data but it seems intuitvely confusing to use this as a replacement for NaN.*"},{"metadata":{"_uuid":"384621649ee90c491fddc93e1373f5c5e1773eee"},"cell_type":"markdown","source":"##### Useful websites\nhttps://pandas.pydata.org/pandas-docs/stable/api.html#flat-file"},{"metadata":{"_uuid":"cc750a762c835225a1a9c3948d2a50b0ce534026"},"cell_type":"markdown","source":"<a id='initialisation'></a>\n# Initialisation\nImport, set up globals, list the contents of directories, read in the files and define some generic functions.\n<br>*NOTE: `fullVisitorId`s identify as numeric and non-numeric so force this to be a string*\n\nThe pie charts and scatter graphs may be cumbersome once viewed so use `do_for_loop_pies = False` and `do_for_loop_scatters = False` to skip these.\n\n[Next section - Simplify the data](#simplify) &nbsp;&nbsp;&nbsp; [Top of page](#top)"},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"0469c40b54f34ef8763a0a5a1a5711e2ecd19036"},"cell_type":"code","source":"import os\nimport sys\nimport warnings\nimport numpy as np\nimport pandas as pd \nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport lightgbm as lgb\nimport json\nimport re\nimport time\nfrom math import ceil\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.model_selection import train_test_split\nfrom sklearn import metrics\n\nwarnings.filterwarnings('ignore')\norig_max_rows = pd.options.display.max_rows\n#pd.set_option('display.max_rows', 500)\n#pd.set_option('display.max_colwidth', 90)\n\ndo_for_loop_pies = True\ndo_for_loop_scatters = True\n\nUNDERLINE = '\\033[4m'\nBOLD = '\\033[1m'\nEND = '\\033[0m'\n\n#newdata_dir = \"../input\"\nnewdata_dir = \"../input/ga-analytics-with-json-columns\"\ndata_dir = '../input/ga-customer-revenue-prediction'\nworking_dir = \".\"\n#working_dir = newdata_dir\nnewtrain_path = newdata_dir+\"/newtrain.csv\"\nnewtest_path  = newdata_dir+\"/newtest.csv\"\nsubmission_path  = working_dir+\"/test_results_lgb.csv\"\n!ls -ld $newdata_dir/*.csv","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"8b85ea32ff4b7959f3d316fa226ffc8ca783c741"},"cell_type":"code","source":"#%%javascript\n#IPython.OutputArea.prototype._should_scroll = function(lines) {\n#    return false;\n#}\n#// Kaggle fails on the above lines","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"4d1fb12880596c7079ccf3d49ee8cedce80d3aa8"},"cell_type":"code","source":"newtrain = pd.read_csv(newtrain_path, dtype={'fullVisitorId': 'str', 'visitId': 'str'})\nnewtest = pd.read_csv(newtest_path, dtype={'fullVisitorId': 'str', 'visitId': 'str'})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e3ffeb97c34c4237f4424b5e207d2c9d4acf9217"},"cell_type":"markdown","source":"### Generic data functions\n* summary: info=\"describe\" and default (\"basic\") are be self-explanatory; info=\"revenue\" has special columns:\n<br>'nNull' are non-Null entries for quick checks\n<br>'nRevs' are non-Null **totals.transactionRevenue** entries only for the non-Null entries of the column\n<br>'nNull%' is the % of nNull to overall rows\n<br>'nRevs%' is the % of nRevs to overall non-Null revenue entries\n\n*NOTE: `display.max_rows` is set to the number of columns, if the original max_rows is less*\n\n*NaN WARNING: since many functions use count(), dropna(), isna(), dropna() beware reusing them after manipulation with fillna()*"},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"fcf7e918dc3b5bc2444a0d18884b247c233373f0"},"cell_type":"code","source":"def summary(df, info=\"describe\", cols=None):\n    if info == \"describe\":\n        headings=['Null','dType','Type','MinMax','Mean','Std','Skew','Unique','Examples']\n    elif info == \"revenue\":\n        headings=['Null','nNull','nRevs','nNull%','nRevs%','dType','Type','Unique','Examples']    \n        nrevs = df['totals.transactionRevenue'].count()\n    else:\n        headings=['Null','dType','Type','Unique','Examples']\n\n    if cols == None:\n        cols = df.columns\n\n    nrows = df.shape[0]\n    if (nrows > orig_max_rows):\n        pd.set_option('display.max_rows', nrows)\n\n    print('DataFrame shape',df.shape)\n    sdf = pd.DataFrame(index=cols, columns=headings)\n    for col in cols:\n        sys.stdout.write('.')\n        sys.stdout.flush()\n        sdf['Null'][col]     = df[col].isna().sum()\n        sdf['Unique'][col]   = len(list(df[col].unique()))\n        sdf['dType'][col]    = df[col].dtypes\n        sdf['Type'][col]     = \"-\" if df[col].notna().sum() == 0 else type(df[col].dropna().iloc[0])\n        sdf['Examples'][col] = \"-\" if df[col].notna().sum() == 0 else list(df[col].unique())\n        if info == \"describe\":\n            if 'float' in str(df[col].dtype) or 'int' in str(df[col].dtype):\n                sdf['MinMax'][col] = str(round(df[col].min(),2))+'/'+str(round(df[col].max(),2))\n                sdf['Mean'][col]   = df[col].mean()\n                sdf['Std'][col]    = df[col].std()\n                sdf['Skew'][col]   = df[col].skew()\n        elif info == \"revenue\":\n            sdf['nNull'][col] = df[col].count()\n            sdf['nRevs'][col] = df.groupby(col)['totals.transactionRevenue'].count().sum()\n            sdf['nNull%'][col] = round(sdf['nNull'][col] / (nrows/100), 1)\n            sdf['nRevs%'][col] = round(sdf['nRevs'][col] / (nrevs/100), 1)\n    return sdf.fillna('-')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4130ffc1427e68d8347bb88ea9f7360209fa972d"},"cell_type":"markdown","source":"<a id='simplify'></a>\n# Simplify the data\nAltering the data so as to increase information and structure it in such a way as to aid the machine learning algorithm. The following are done:\n\n* Drop columns with no data in train or test (ie. if empty in either column is dropped in both).\n* `trafficSource.campaignCode` one value everywhere so ignore\n* `totals.visits` one value everywhere so ignore\n* `visitStartTime` is used to create `visitStartTimeHH` *Q: are minutes or even seconds useful?*\n* `date` is used to create `dateMm`\n* `trafficSource.adwordsClickInfo.gclId` *not used or changed yet*\n* `trafficSource.adContent` *not used or changed yet*\n* `trafficSource.adwordsClickInfo.adNetworkType` *not used or changed yet*\n* `trafficSource.adwordsClickInfo.isVideoAd` *not used or changed yet*\n* `trafficSource.adwordsClickInfo.page` *not used or changed yet*\n* `trafficSource.adwordsClickInfo.slot` *not used or changed yet*\n* `trafficSource.campaign` *not used or changed yet*\n\n*Q. What can I do with NaNs? (setting to -1 was useful for graphs but it didn't improve the score)\n<br>Q. the `trafficSource...` values are 2% of overall data but 4% of revenue&gt;0. How can I use this?*\n\n[Next section - EDA: pie and scatter](#eda) &nbsp;&nbsp;&nbsp; [Top of page](#top)"},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"80d172f840f5ff0672b6b4e8420bd8664052c83b"},"cell_type":"code","source":"def get_unused(df):\n    rows = df.shape[0]\n    ddf = df.isna().sum()\n    return list(ddf[ddf >= rows].index)\n\ndroplist = get_unused(newtrain)\nfor dropcol in get_unused(newtest):\n    if dropcol not in droplist:\n        droplist.append(dropcol)\n\nprint (\"dropping these columns from both newtrain and newtest:\")\nprint (droplist)\nnewtrain.drop(columns=droplist, inplace=True)\nnewtest.drop(columns=droplist, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"bf0c612f5a997bb06ed046110a2dd8ec44a9889e"},"cell_type":"code","source":"# Don't bother shuffling new column around\n#def move_last_col(df, col, insert_after):\n#    ordercols = list(df.columns[:-1].insert(insert_after, col))\n#    return df[ordercols]\n\ndef add_visitStartTimeHH(df):\n    df['visitStartTimeHH'] = pd.to_datetime(df['visitStartTime'], unit='s').apply(\n        lambda x: re.sub(r'^[^ \\s]+ (\\d\\d):\\d\\d:\\d\\d',r'\\1', str(x))).astype(int)\n    return df\n\ndef add_dateMm(df):\n    df['dateMm'] = df['date'].apply(lambda x: re.sub(r'^\\d\\d\\d\\d(\\d\\d)\\d\\d',r'\\1', str(x))).astype(int)\n    return df\n\n#def str2strlen(df, col):\n#    df['tmp.'+col] = df[col].str.count(\".\")\n#    df.drop(columns=[col], inplace=True)\n#    df.rename(columns={'tmp.'+col: col}, inplace=True)\n#    return df","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":true,"_uuid":"41b952681a1a889d8471f9ea49ff4ffbacfd7b26"},"cell_type":"code","source":"print (\"train/test: add visitStartTimeHH\")\nnewtrain = add_visitStartTimeHH(newtrain)\nnewtest  = add_visitStartTimeHH(newtest)\nprint (\"train/test: add dateMm\")\nnewtrain = add_dateMm(newtrain)\nnewtest  = add_dateMm(newtest)\n#print (\"train/test: change unique strings to len(strings) in ...gclId\")\n#newtrain = str2strlen(newtrain, 'trafficSource.adwordsClickInfo.gclId')\n#newtest  = str2strlen(newtest, 'trafficSource.adwordsClickInfo.gclId')","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":true,"_uuid":"51b786e5e0247cf0f52f479f54949b26d258f45e"},"cell_type":"code","source":"summary(newtrain, info=\"revenue\")","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"54b6bd101e0c846de6d53995e05896f0b39c0737"},"cell_type":"code","source":"summary(newtest, info=\"basic\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ebe0290c5dee389c31480a6bee4fec61e323d725"},"cell_type":"markdown","source":"<a id='eda'></a>\n# EDA: pie and scatter\nFirsty, a bar chart to show the imbalance of the revenue column.\n\nThen, pie charts and scatter graphs are used to assess unique entries in the columns and how they correlate. Pies are used when a column's unique entries &lt;= 1000 and scatter graphs are used when unique entries &gt;= 100, so there's plenty of overlap.\n\nThree charts/graphs are produced for each column:\n<br>(a) general prevalence of an entry, eg. November has the most entries overall;\n<br>(b) prevalence when revenue &gt; 0, eg. December has the most entries when it comes to spending money;\n<br>(c) prevalence for revenue quantity, eg. August has the biggest slice when it comes to how much money is spent.\n\n*Q. Is there a function to pull out the hundreds/thousands of unique column entries and produce two lists of correlation values which would contain all the information of (a), (b) and (c)?*\n\nThe last part of this sections deals with `fullVisitorId`. I want to know how much overlap there is between the unique entries of this column in the train and test data.\n\n[Next section - Prepare the data](#prepare) &nbsp;&nbsp;&nbsp; [Top of page](#top)"},{"metadata":{"_uuid":"e37a77498e0d67d28dd5c817656c4102abbdb2b2"},"cell_type":"markdown","source":"### Bar graph to state the obvious\nRows where revenue&gt;0 is 1.27% of the data.\n*<br>Q. Does duplicating rows where revenue&gt;0 improve the score?\n<br>Q. Does creating \"similar\" rows (to prevent over-fitting) improve the score?*"},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"14728e90c4a3262c2ab8d3a20d8ebb83a9babd1e"},"cell_type":"code","source":"def mybar_rows(data, labels=[], title=\"title\"):\n    drange = list(range(1,len(data)+1))\n    plt.bar(drange, data)\n    plt.ylabel('Rows', fontsize=15)\n    plt.xticks(drange, labels, fontsize=15, rotation=0)\n    plt.title(title, fontsize=15);\n\nnrows = newtrain['totals.transactionRevenue'].size\nnrevs = newtrain['totals.transactionRevenue'].dropna().size\nzrevs = nrows - nrevs\n\nprint (\"nrows = {:d}; NaN = {:d}; >0 = {:d}; {:1.2f}%\".format(nrows,zrevs,nrevs,nrevs/(nrows/100)))\nmybar_rows([nrows, zrevs, nrevs], labels=[\"nrows\", \"NaN\", \">0\"], title=\"totals.transactionRevenue\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5b6592e7e7ae6040d45bc227ce4e48014e42472c"},"cell_type":"markdown","source":"### Pie chart functions\n* ifsort: function to sort depending on sorty\n* col_by_size: overall prevalence of values in **col&#42;**; ignore if count <= threshold\n* col_by_col_count: count col2 instances for each unique value of **col1&#42;**; but ignore if count <= threshold\n* col_by_col_sum: sum col2 instances for each unique value of **col1&#42;**; but ignore if sum <=threshold\n* myautopct: pie is messy with 0.0 and 0.1 percent markings, so set a threshold\n* get_colors: to assign the same colour for index labels\n* get_labels: to assign a name or no name based on the value (to avoid clutter)\n* mypie: common options\n* make_revenue_pie: show 3 pies showing column prevalence (a) overall (b) by revenue instance and (c) by revenue sum\n\n** *&#42;col (or col1) is filled with -1 instead of NaN. -1 has no meaning in the entire data set (neither does 0 but I thought -1 more obvious so). Doing this allows the pie/scatter graphs to highlight usefulness of columns like ..adwords.., which are typically >90% NaNs* **"},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"61717fad3f3d8924068a78d4d908ae7d4b8e899e"},"cell_type":"code","source":"def ifsort(df, sorty=True):\n    return df.sort_values(ascending=True) if sorty is True else df\n\ndef col_by_size(df, col, threshold=0, sorty=True):\n    return ifsort(df.fillna({col: -1}).groupby(col).size().apply(lambda x: (x if x>threshold else np.nan)).dropna(), sorty=sorty)\n\ndef col_by_col_count(df, col1, col2, threshold=0, sorty=True):\n    return ifsort(df.fillna({col1: -1}).groupby(col1)[col2].count().apply(lambda x: (x if x>threshold else np.nan)).dropna(), sorty=sorty)\n\ndef col_by_col_sum(df, col1, col2, threshold=0, sorty=True):\n    return ifsort(df.fillna({col1: -1}).groupby(col1)[col2].sum(numeric_only=True).apply(lambda x: (x if x>threshold else np.nan)).dropna(), sorty=sorty)\n\ndef myautopct(pct):\n    return ('%.2f' % pct) if pct > 2 else ''\n\ndef get_colors(df):\n    colors = []\n    for i in df.index:\n        s = str(abs(hash(str(i))))\n        colors.append( ( float('0.'+s[0]), float('0.'+s[1]), float('0.'+s[2]) ) )\n    return colors\n\ndef get_labels(df):\n    labels = []\n    total = df.values.sum()\n    for i,v in dict(df).items():\n        pct = v / (total/100)\n        labels.append( ( i if pct > 1 else '' ) )\n    return labels\n\ndef mypie(df, title, angle=0, autocol=True, autolab=True):\n    colors = get_colors(df) if autocol is True else None\n    labels = get_labels(df) if autolab is True else None\n    # autopct='%1.1f%%'\n    # textprops={'size': 'small'}  (Kaggle python (3.6.6) + libs didn't recognise this)\n    df.plot(kind='pie', radius=1.2, startangle=angle, autopct=myautopct, pctdistance=0.8,\n        figsize=(5, 5), rotatelabels=False, legend=True, colors=colors, labels=labels, explode=[0.02]*df.size);\n    plt.title(title, weight='bold', size=14, x=2.0, y=-0.01);\n    plt.xlabel('')\n    plt.ylabel('')\n    plt.legend(bbox_to_anchor=(2.5, 1.0), ncol=2, fontsize=10, fancybox=True, shadow=True);\n    \ndef make_revenue_pie(df, col, unique=500, minperc=0, threshold=0, angle=0, sorty=True):\n    print ('\\n\\n'+BOLD+UNDERLINE+'Making a pie for',col,\"...\"+END)\n    uentries = df[col].astype(str).unique().size\n    percvalid = df[col].notna().sum() / (df[col].size / 100)\n    if percvalid < minperc:\n        print (\"NO PIE because {:s} has only {:1.1f}% non-Null entries (min={:d}%)\".format(col, percvalid, minperc))\n    elif uentries > unique:\n        print (\"NO PIE because there are over\",unique,\"unique\",col,\"entries\")\n    elif uentries <= 1:\n        print (\"NO PIE because all entries are identical\")\n    else:\n        dfrev = col_by_col_count(df, col, 'totals.transactionRevenue', threshold=threshold, sorty=sorty)\n        if not dfrev.empty:\n            dfsum = col_by_col_sum(df, col, 'totals.transactionRevenue', threshold=threshold, sorty=sorty)\n            dfgen = col_by_size(df, col, threshold=threshold, sorty=sorty)\n            mypie(dfgen, col+' prevalence',           angle=angle, autocol=True); plt.show()\n            mypie(dfrev, col+' by revenue instances', angle=angle, autocol=True); plt.show()\n            mypie(dfsum, col+' by revenue sum',       angle=angle, autocol=True); plt.show()\n        else:\n            print (\"NO PIE because\",col,\" does not have a unique entry with more than\",threshold,\"revenue instances\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1aaea2ade37dc740a6cff7dae09862f0fc842f98"},"cell_type":"markdown","source":"### Pie example deductions using example dateMm\nAs mentioned above, the albeit subtle differences can be seen for November, December and August in the three respective pies."},{"metadata":{"scrolled":false,"trusted":true,"_uuid":"b8e08ac5415632817055a8200414be16dcba9a52"},"cell_type":"code","source":"print (BOLD+'EXAMPLE:'+END)\nmake_revenue_pie(newtrain, 'dateMm', unique=1000, threshold=100, angle=0)\n#make_revenue_pie(newtrain, 'device.operatingSystem', unique=1000, threshold=100, angle=0, sorty=False)\n#make_revenue_pie(newtrain, 'geoNetwork.subContinent', unique=1000, threshold=100, angle=0)\n#make_revenue_pie(newtrain, 'geoNetwork.region', unique=1000, threshold=100, angle=0)\n#make_revenue_pie(newtrain, 'socialEngagementType', unique=1000, threshold=100, angle=0)\n#make_revenue_pie(newtrain, 'visitStartTimeHH', unique=1000, threshold=100, angle=0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"99d7612263b6b2b615ac238feed100b6ae0b7e32"},"cell_type":"markdown","source":"### Pie deductions for all columns with 1000 or fewer unique entries\nFor all \"pie-able\" columns, ie. those with fewer than 1000 unique entries show:\n<br> &nbsp;&nbsp; a) column prevalence for each unique entry; value is number of rows per unique entry\n<br> &nbsp;&nbsp; b) column by revenue instance; value is number of \"revenue>0\" rows per unique entry\n<br> &nbsp;&nbsp; c) column by revenue sum; value is sum of those same revenue rows per unique entry\n\nA rough summary of the +ve / -ve influence is listed below.\n\n#### useful\n* channelGrouping: +ve Referral; -ve Organic Search\n* date: split into month only\n* dateMm: +ve Dec,Apr,Aug -ve other months\n* visitStartTimeHH: +ve 6pm-midnight -ve other times\n* visitNumber: +ve 2-10 (possibly more); -ve 1\n* device.browser: +ve Chrome; -ve Safari\n* device.deviceCategory: +ve desktop; -ve mobile\n* device.isMobile: +ve False; -ve True (corroborates the above data)\n* device.operatingSystem: +ve Mac,Linux,Chrome; -ve Windows,Android,iOS\n* geoNetwork.city: +ve many US cities; -ve non-US cities\n* geoNetwork.continent: +ve Americas; -ve other continents\n* geoNetwork.country: +ve USA; -ve non-USA\n* geoNetwork.metro; something going on but a function could calculate +ve -ve better\n* geoNetwork.region; something going on but a function could calculate +ve -ve better\n* geoNetwork.subContinent; +ve North America; -ve all other sub-continents\n* totals.bounces: +ve NaN; -ve 1.0\n* totals.hits: +ve >10; -ve <10\n* totals.pageviews: +ve >10; -ve <10\n* totals.newVisits: +ve NaN; -ve 1.0\n* trafficSource.isTrueDirect: +ve True; -ve NaN\n* trafficSource.medium: +ve referral,cpc; -ve: organic search,affiliate\n* trafficSource.source: +ve mail.googleplex.com,(direct); -ve google,youtube.com\n\n#### don't know\n* trafficSource.adwordsClickInfo.gclId \n* trafficSource.adContent \n* trafficSource.adwordsClickInfo.adNetworkType\n* trafficSource.adwordsClickInfo.isVideoAd\n* trafficSource.adwordsClickInfo.page\n* trafficSource.adwordsClickInfo.slot\n* trafficSource.campaign\n\n#### not useful\n* visitStartTime: seconds since 1970, we use visitStartTimeHH instead (see above)\n* socialEngagementType: all rows have the same value\n* totals.visits: all rows have the same value\n\n*NOTE: the remaining columns will have had a disqualifying reason (see messages) or more than 1000 unique entries. For the latter, scatter graphs in the following section will take care of those columns.*"},{"metadata":{"scrolled":false,"trusted":true,"_uuid":"023a3df859479eaafc70fd6b8024e30a3bcea2fe"},"cell_type":"code","source":"if do_for_loop_pies:\n    for col in newtrain.columns:\n        make_revenue_pie(newtrain, col, unique=1000, threshold=100, angle=0)\nelse:\n    print (\"skipping pie chart for loop\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c89b8d8c1ffc3523bb2d071eb7a29fe59188efeb"},"cell_type":"markdown","source":"### Scatter graph functions\n* truncate: truncate strings within an array\n* get_xticks: make sure the xticks are readable and that the last (highest) is always shown\n* myscat: good options for a scatter graph\n* myscat_size: plot non-zero values only and max 'maxtick' labels for col prevalence\n* myscat_countrev: plot non-zero values only and max 'maxtick' labels for col by revenue instances\n* myscat_sumrev: plot non-zero values only and max 'maxtick' labels for col by revenue sum\n* scatter_revenue: plot only for those > than unique\n<br>*NOTE: natural log is used to compress the results when `log1p=True`*\n\nNext, an examples of three scatter graphs for `totals.hits`. After that a for loop does the same for all columns where unique entries >= 100."},{"metadata":{"scrolled":false,"trusted":true,"_uuid":"74f23884fa33db4c3cd58c20e931958b6bbe78d0"},"cell_type":"code","source":"def truncate(avals, tlen):\n    tstrs = []\n    tstrs[:] = (re.sub(r'(.{'+str(tlen)+'})(.*)', r'\\1......', str(val)) for val in avals)\n    return tstrs\n\ndef get_xticks(df, maxtick=39):\n    rows = df.shape[0]\n    xti = list(range(0, rows, ceil(rows/maxtick)))\n    #xtl = list(df.index)[0::ceil(rows/maxtick)]\n    xtl = truncate(list(df.index)[0::ceil(rows/maxtick)], 30)\n    if xtl[-1] != df.index[-1]:\n        xti.append(rows)\n        xtl.append(df.index[-1])\n    return xti, xtl\n\ndef myscat(df, col1, col2, log1p=True, sorty=True, threshold=0):\n    ylabel = \"ln(\"+col2+\")\"      if log1p is True else col2\n    ytmp   = np.log1p(df.values) if log1p is True else df.values\n    y      = np.sort(ytmp)       if sorty is True else ytmp\n    x      = range(0, len(y))\n    #print (\"NOTE:\",len(y),\"unique entries found where each value >\",threshold,\"(threshold) ; meaning\",len(y),\"data points for both x and y\")\n    plt.figure(figsize=(15,5))\n    plt.scatter(x, y)\n    plt.xlabel(col1, fontsize=12)\n    plt.ylabel(ylabel+\" > \"+str(threshold), fontsize=12)\n    xti, xtl = get_xticks(df)\n    plt.xticks(xti, xtl, rotation=90)\n    plt.show()\n\ndef myscat_size(df, col, log1p=True, threshold=0, sorty=True):\n    dfsize = col_by_size(newtrain, col, threshold=threshold, sorty=sorty)\n    myscat(dfsize, col, 'number of rows', log1p=log1p, sorty=sorty, threshold=threshold)\n\ndef myscat_countrev(df, col, log1p=True, threshold=0, sorty=True):\n    dfcount = col_by_col_count(newtrain, col, 'totals.transactionRevenue', threshold=threshold, sorty=sorty)\n    myscat(dfcount, col, 'totals.transactionRevenue', log1p=log1p, sorty=sorty, threshold=threshold)\n\ndef myscat_sumrev(df, col, log1p=True, threshold=0, sorty=True):\n    dfsum = col_by_col_sum(newtrain, col, 'totals.transactionRevenue', threshold=threshold, sorty=sorty)\n    myscat(dfsum, col, 'totals.transactionRevenue', log1p=log1p, sorty=sorty, threshold=threshold)\n\ndef scatter_revenue(df, col, unique=100, threshold=0, sorty=True):\n    print ('\\n\\n'+BOLD+UNDERLINE+'Scatter graphs for',col,\"(a) prevalence (b) by revenue instances (c) by revenue sum (\"+('sorted' if sorty is True else 'unsorted')+\")\"+END)\n    uentries = df[col].dropna().astype(str).unique().size\n    if uentries < unique:\n        print (\"NO SCATTER because there are under\",unique,\"unique\",col,\"entries\")\n    else:\n        myscat_size    (newtrain, col, log1p=True, threshold=threshold, sorty=sorty)\n        myscat_countrev(newtrain, col, log1p=True, threshold=threshold, sorty=sorty)\n        myscat_sumrev  (newtrain, col, log1p=True, threshold=threshold, sorty=sorty)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"212128db5fead4527da2c6f788d7cdaf11b6eb46"},"cell_type":"markdown","source":"### Scatter example deductions using example totals.hits\nGraph (a) shows a steady overall decline in quantity of hits from 1-500, but graphs (b) and (c) show a revenue sweetspot between 20-30 hits."},{"metadata":{"scrolled":false,"trusted":true,"_uuid":"d262a13a93c2eb134ec1678982cdf447f3534542"},"cell_type":"code","source":"print (BOLD+'EXAMPLE:'+END)\nscatter_revenue(newtrain, 'totals.hits', unique=0, threshold=0, sorty=False)\n#scatter_revenue(newtrain, 'fullVisitorId', unique=0, threshold=0, sorty=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d43ff7e212df58ae99176e3fbfedd53b130abadb"},"cell_type":"markdown","source":"### Scatter graph for all columns with 100 or more unique entries\nThe 3 graphs show the following for each column:\n<br>&nbsp;&nbsp;a) column prevalence for each unique entry; value is number of rows per unique entry\n<br>&nbsp;&nbsp;b) column by revenue instance; value is number of \"revenue>0\" rows per unique entry\n<br>&nbsp;&nbsp;c) column by revenue sum; value is sum of those same revenue rows per unique entry\n\nNOTE:\n* due to the large proportion of revenue NaN rows, graph (a) is expected to be more or less the same as a graph showing column by \"revenue==0\" instances\n* sort y (revenue) when x is type(object) but for other types (int/float) leave it sorted on x\n* NaNs are filled using fillna(-1) temporarily before showing the graphs\n\n\nA summary of the scatter graph observations are listed below along with columns judged already useful/not sure/not useful from previous sections. The columns listed below should be the same columns in our initial `summary(newtrain, info=\"revenue\")` line, ie. after the drop of NaN-only columns at the beginning.\n\n#### useful\n* visitNumber: revenue goes up with visits\n* geoNetwork.networkDomain: few domains give high revenue\n* totals.hits: interesting, unsorted yet graph (b) and (c) shows a definite pattern which is different to (a)\n* totals.pageviews: interesting for the same reason as totals.hits above\n* trafficSource.keyword: some random words, some real words, like for revenue for Google Merchandise\n* trafficSource.referralPath: strings random and real, maybe a conversion is needed to something more generic\n* trafficSource.source: real strings that show pattern\n\n#### check (see section after graphs)\n* fullVisitorId: check will show overlap (of unique values) between train and test data is not large enough\n\n#### already decided was useful but not mentioned above\n* dateMm: see pie section\n* visitStartTimeHH: see pie section\n* totals.bounces: see pie section\n* totals.newVisits: see pie section\n* channelGrouping: see pie section\n* device.browser: see pie section\n* device.deviceCategory: see pie section\n* device.isMobile: see pie section\n* device.operatingSystem: see pie section\n* geoNetwork.city: see pie section\n* geoNetwork.continent: see pie section\n* geoNetwork.country: see pie section\n* geoNetwork.metro; see pie section\n* geoNetwork.region; see pie section\n* geoNetwork.subContinent; see pie section\n* trafficSource.medium: see pie section\n* trafficSource.isTrueDirect: see pie section\n\n#### still not sure (play around with these later...)\n* trafficSource.adwordsClickInfo.gclId\n* trafficSource.adContent\n* trafficSource.adwordsClickInfo.adNetworkType\n* trafficSource.adwordsClickInfo.isVideoAd\n* trafficSource.adwordsClickInfo.page\n* trafficSource.adwordsClickInfo.slot\n* trafficSource.campaign\n\n#### not useful\n* visitId: unique numbers that won't be found in test data\n* sessionId: unique numbers that won't be found in test data\n* socialEngagementType: see pie section\n* totals.visits: see pie section\n* trafficSource.campaignCode: one value NaN11251kjhkvahf, and the rest are NaNs\n* totals.transactionRevenue: value we're trying to predict"},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"cd9cc595383b2e3a53329c25b456863684eb7b7d"},"cell_type":"code","source":"if do_for_loop_scatters:\n    for col in newtrain.columns:\n        sorty = True if newtrain[col].dtypes == 'object' else False\n        scatter_revenue(newtrain, col, unique=100, threshold=0, sorty=sorty)\nelse:\n    print (\"skipping scatter graph for loop\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"039221e75d2ce6412e5b01a5403ea0e70481ef0a"},"cell_type":"markdown","source":"### Check fullVisitorId unique entries in train and test\nAs the test below shows the unique entries from this column is less than 1% of the data sample. In baseline tests with LightGBM, adding/removing `fullVisitorId` didn't make much difference.\n\n*Q. How do I improve score by using those small number of unique values that appear in both newtrain and newtest?*"},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"06a9711bb3a6bd889d933af3f03fe83a22e227b1"},"cell_type":"code","source":"def check_unique_values(df1, df2, col):\n    l1 = list(df1[col].dropna().astype(str).unique()) \n    l2 = list(df2[col].dropna().astype(str).unique()) \n    return list(set(l1).intersection(l2))\n\ndef check_unique_values_with_revenue(df1, df2, col):\n    l1 = col_by_col_count(df1, col, 'totals.transactionRevenue', threshold=0, sorty=False).index\n    l2 = list(df2[col].dropna().astype(str).unique()) \n    return list(set(l1).intersection(l2))\n\ntrows = newtest.shape[0]\nprint (\"rows in newtest:\", trows)\nfor col in ['fullVisitorId']:\n    ul1 = check_unique_values(newtrain, newtest, col)\n    ul2 = check_unique_values_with_revenue(newtrain, newtest, col)\n    print (\"{:4d} common entries ({:1.2f}% of newtest), {:4d} rev>0 ({:1.2f}% of 1st value) for {:s}\".format(\n        len(ul1), len(ul1)/(trows/100), len(ul2), len(ul2)/(len(ul1)/100), col))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d4d00ae335b71e93ed451a675661d0937bed9d07"},"cell_type":"markdown","source":"<a id='prepare'></a>\n# Prepare the data\nHere we define the useful columns, split the data into training and validation, do some sanity checks and encode the data.\n\n[Next section - The Neural Net](#neuralnet) &nbsp;&nbsp;&nbsp; [Top of page](#top)"},{"metadata":{"_uuid":"cd42fa22c65c458c7cd1c303631201adcdb9d892"},"cell_type":"markdown","source":"### Define useful columns\nThe columns for training data should be the same as test except for the extra `totals.transactionRevenue` column, which will be removed later. Also, the columns `date` and `fullVisitorId` are used in checks too, so these need to be included now too. If they are not needed they should be removed later."},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"c928ca3538a33e943ae2b44aef559b88f5c58149"},"cell_type":"code","source":"useful_test_cols = [\n    'fullVisitorId',\n    'date',\n    'dateMm',\n    'visitStartTimeHH',\n    'channelGrouping',\n    'visitNumber',\n    'device.browser',\n    'device.deviceCategory',\n    'device.isMobile',\n    'device.operatingSystem',\n    'geoNetwork.city',\n    'geoNetwork.continent',\n    'geoNetwork.country',\n    'geoNetwork.networkDomain',\n    'geoNetwork.region',\n    'geoNetwork.subContinent',\n    'geoNetwork.metro',\n    'totals.hits',\n    'totals.pageviews',\n    'totals.bounces',\n    'totals.newVisits',\n    'trafficSource.keyword',\n    'trafficSource.referralPath',\n    'trafficSource.medium',\n    'trafficSource.source',\n    'trafficSource.isTrueDirect',\n    ]\n\n# totals.transactionRevenue will be removed later from train and validation data\nuseful_train_cols = list(set(useful_test_cols + ['totals.transactionRevenue']))\n    \n# still not sure about these, play around with them later on...\n# 'trafficSource.adwordsClickInfo.gclId',\n# 'trafficSource.adContent',\n# 'trafficSource.adwordsClickInfo.adNetworkType',\n# 'trafficSource.adwordsClickInfo.isVideoAd',\n# 'trafficSource.adwordsClickInfo.page',\n# 'trafficSource.adwordsClickInfo.slot',\n# 'trafficSource.campaign',","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fc0528d58decec961e9679f95fb4b0ba5ef84418"},"cell_type":"markdown","source":"### Splitting the data\nWe split the training data into trainx and validx, the latter validation data being 10% of the initial training data. The following checks are then done:\n* a bar graph showing % of revenue&gt;0 in trainx and validx - the values must be roughly the same\n* a check using a known unique value to ensure rows match between trainx and trainy."},{"metadata":{"scrolled":false,"trusted":true,"_uuid":"a127b0ecdba01aeac15d3207ba8050ad4e5c5955"},"cell_type":"code","source":"def split_data(df, cutoff=None, test_size=0.1, shuffle=True):\n    if cutoff is None:\n        tx, vx, ty, vy = train_test_split(df[useful_train_cols],\n                                          df['totals.transactionRevenue'],\n                                          test_size=test_size, shuffle=shuffle)\n        ty = ty.fillna(0).values\n        vy = vy.fillna(0).values\n    else:\n        tx = df[useful_train_cols].loc[(newtrain['date'] <  cutoff)]\n        vx = df[useful_train_cols].loc[(newtrain['date'] >= cutoff)]\n        ty = tx['totals.transactionRevenue'].fillna(0).values\n        vy = vx['totals.transactionRevenue'].fillna(0).values\n    return tx, vx, ty, vy\n\n#trainx, validx, trainy, validy = split_data(newtrain, cutoff=20170601)\ntrainx, validx, trainy, validy = split_data(newtrain, cutoff=None, test_size=0.1, shuffle=False)\n\ntestx     = newtest[useful_test_cols]\nvalidvids = validx['fullVisitorId'].values\ntestvids  = newtest['fullVisitorId'].values","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"2d59dcd55fdcd908cb2f486d0f4a3a346b6ec36c"},"cell_type":"code","source":"tnrows = trainx['totals.transactionRevenue'].size\nvnrows = validx['totals.transactionRevenue'].size\ntnrevs = trainx['totals.transactionRevenue'].dropna().size\nvnrevs = validx['totals.transactionRevenue'].dropna().size\ntzrevs = tnrows - tnrevs\nvzrevs = vnrows - vnrevs\n\nprint (\"trainx: nrows = {:6d}; NaN = {:4d}; >0 = {:4d}; {:1.2f}%\".format(tnrows,tzrevs,tnrevs,tnrevs/(tnrows/100)))\nprint (\"validx: nrows = {:6d}; NaN = {:4d}; >0 = {:4d}; {:1.2f}%\".format(vnrows,vzrevs,vnrevs,vnrevs/(vnrows/100)))\nmybar_rows([tnrows, tzrevs, tnrevs, vnrows, vzrevs, vnrevs], labels=[\"tnrows\", \"tNaN\", \"t>0\", \"vnrows\", \"vNaN\", \"v>0\"], title=\"REVENUE: train vs validation\")","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"e2af31614496c8aa0d90ff5f1e2ef790780a14f4"},"cell_type":"code","source":"# not managing to get the row number using panda functions; I need this if I use shuffle=True above\ndef get_row_number(arr, value):\n    row = 0\n    for v in arr:\n        if v == value:\n            return row\n        else:\n            row = row + 1\n\ndef get_testrow(df, dfx, arry, col, value):\n    dfnames = sys._getframe(1).f_code.co_names[1:4]  # [0] is the function name\n    i = df.index[(df[col] == value)].tolist()[0]\n    r = get_row_number(dfx[col], value)\n    print (\"using revenue value = {:d} to check trainx/trainy\".format(value))\n    print (\"{:8s}: value found at index {:d}, which is actually row {:d}\".format(dfnames[0], i, r))\n    print (\"{:8s}: value found at index {:6d} is: {:1.0f}\".format(dfnames[1], i, dfx.loc[i][col]))\n    print (\"{:8s}: value found at row   {:6d} is: {:1.0f}\".format(dfnames[1], r, dfx.iloc[r][col]))\n    print (\"{:8s}: value found at row   {:6d} is: {:1.0f}\".format(dfnames[2], r, arry[r]))\n    assert dfx.loc[i][col] == arry[r]\n    assert dfx.iloc[r][col] == arry[r]\n\nget_testrow(newtrain, trainx, trainy, 'totals.transactionRevenue', 306670000)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fe4749e988359194f2502abef1331c38ea0a0785"},"cell_type":"markdown","source":"### Drop totals.transactionRevenue\nAfter checks, drop `totals.transactionRevenue`. Also, if fields `date` and `fullVisitorId` are not required for machine learning then they can be dropped now too (they were used for checks in previous cells). A summary is shown of trainx for visual confirmation of changes."},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"3873e4de7bc6344046cbd89280fe7924248ed489"},"cell_type":"code","source":"trainx.drop(columns=['totals.transactionRevenue'], inplace=True)\nvalidx.drop(columns=['totals.transactionRevenue'], inplace=True)\nassert set(trainx.columns) == set(validx.columns)\nassert set(trainx.columns) == set(testx.columns)\nsummary(trainx, info=\"basic\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"810e4f67a8f85ae37f588ff829455ef69584846e"},"cell_type":"markdown","source":"### Functions for encoding\n* get_unique_categories: for all columns - we'll decide later which ones we'll encode\n* encode_columns: objects are always encoded (one-hot or label depending on qty of unique entries - see argument `onehot`); everything else is converted to float.\n\nAfter the three data frames are encoded (train, validation and test) the three are aligned using train+valid, then train+test and lastly train+valid again just in case the previous align removed columns from train. A summary is shown of the encoded training data trainxe for visual confirmation of the changes."},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"efb0e309e8fa4687a3d8e619cd6c634cd3078604"},"cell_type":"code","source":"def get_unique_categories(df, dfx, dft):\n    aunique = {}\n    for col in df.columns:\n        aunique[col] = list(set(list(df[col].astype(str).unique()) +\n                                list(dfx[col].astype(str).unique()) +\n                                list(dft[col].astype(str).unique())))\n        aunique[col].sort()\n    return aunique\n\ndef encode_columns(df, ucats, onehot=50):\n    sys.stdout.write(\"encoding \"+sys._getframe(1).f_code.co_names[1])  # [0] is function name\n    le = LabelEncoder()\n    for col,coltype in df.dtypes.items():\n        sys.stdout.write('.')\n        sys.stdout.flush()\n        if coltype == 'object':\n            if (len(ucats[col]) > onehot):\n                le.fit(ucats[col])\n                df[col] = le.transform(df[col].astype('str'))\n    df = pd.get_dummies(df)\n\n    for col,coltype in df.dtypes.items():\n        if coltype != 'float':\n            df[col] = df[col].astype(float)\n\n    print (\"returned new shape\",df.shape)\n    return df","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"38e5d4010883be8d6cdc7909543f5387f65cca2f"},"cell_type":"code","source":"unique_categories = get_unique_categories(trainx, validx, testx)\n\ntrainxe = encode_columns(trainx.copy(), unique_categories, onehot=0)\nvalidxe = encode_columns(validx.copy(), unique_categories, onehot=0)\ntestxe  = encode_columns(testx.copy(), unique_categories, onehot=0)\n\ntrainxe,validxe = trainxe.align(validxe, join='inner', axis=1)\ntrainxe,testxe  = trainxe.align(testxe, join='inner', axis=1)\ntrainxe,validxe = trainxe.align(validxe, join='inner', axis=1)\nprint (\"trainxe shape after align\", trainxe.shape)\nprint (\"validxe shape after align\", validxe.shape)\nprint (\"testxe shape after align\", testxe.shape)\nassert set(trainxe.columns) == set(validxe.columns)\nassert set(trainxe.columns) == set(testxe.columns)\nassert set(trainxe.dtypes) == set(validxe.dtypes)\nassert set(trainxe.dtypes) == set(testxe.dtypes)\nsummary(trainxe, info=\"basic\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ad95847cfb0a1a025bcf3e8f74630315ce6f3940"},"cell_type":"markdown","source":"<a id='neuralnet'></a>\n# The Neural Net\nLightLBM is used to create a baseline score using the following options:\n* objective: regression: typically used when predicting quantities\n* metric: rmse (root square loss): *try with np.expm1(y): l1 (absolute loss) l2 (square loss)*\n* boosting: gbdt (Gradient Boosting Decision Tree), *try: rf (random_forest), dart (Dropouts meet Multiple Additive Regression Trees), goss (Gradient-based One-Side Sampling)*\n* bagging: using independent methods and aggregating them afterwards\n\nSuggestions for higher accurancy (with usual overfitting caveat):\n* num_leaves: 30, try increasing\n* max_bin: 255, try increasing\n* learning_rate 0.1: try decreasing\n* num_iterations: try increasing\n\n[Top of page](#top)"},{"metadata":{"_uuid":"d3be0c4f9800393ff2aec87aef3ef971761cc463"},"cell_type":"markdown","source":"#### Tweaks and previous scores\nScores for LightGBM with the following tweaks.\n<br>*NOTE: splitting train/validation data was originally done by date - see `split_data` function above*\n* `1.91` when using only `totals.hits`\n* `1.73` when using only `totals.hits` `totals.pageviews` `date` `geoNetwork.city` `device.operatingSystem` `geoNetwork.country` `geoNetwork.subContinent`\n* `1.71` when using useful_cols, but removed `dateMm` and `date`\n* `1.71` when using useful_cols, but using `dateMm` instead of `date`\n* `1.69` when using useful_cols plus `trafficSource.adwordsClickInfo.gclId`\n* `1.68` when using useful_cols, but removed `fullVisitorId`\n* `1.68` when using useful_cols, but change all columns to int()\n* `1.68` when using useful_cols, but one_hot_encoding for <0 <50 <150 (<500 took hours to encode)\n* `1.68` when using useful_cols, but changed LightGBM `num_leaves` to 60 (was 30)\n* `1.68` when using useful_cols, but changed LightGBM `learning_rate` to 0.01 (was 0.1) - took 2000 iterations to get there\n* `1.68` when using useful_cols\n* `1.67` when using useful_cols and used only 1 month for validation data\n* `1.60` when using useful_cols and train_test_split(..., test_data=0.2, shuffle=False)\n* `1.60` when using useful_cols and train_test_split(..., test_data=0.1, shuffle=True)\n* `1.59` when using useful_cols and train_test_split(..., test_data=0.1, shuffle=False) *NOTE: sometimes I get `1.60`*"},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"6e4bdd23e4b04073857e4472107b2037ec82e632"},"cell_type":"code","source":"def train_nn(trainx, trainy, validx, validy, testx):\n    params = {\n        \"objective\"         : \"regression\",\n        \"metric\"            : \"rmse\", \n        \"num_leaves\"        : 30,\n        \"min_child_samples\" : 100,\n        \"learning_rate\"     : 0.1,\n        \"bagging_fraction\"  : 0.7,\n        \"feature_fraction\"  : 0.5,\n        \"bagging_frequency\" : 5,\n        \"bagging_seed\"      : 2018,\n        \"verbosity\"         : -1\n    }\n    \n    trainlgb = lgb.Dataset(trainxe, label=trainy)\n    validlgb = lgb.Dataset(validxe, label=validy)\n    return lgb.train(params, trainlgb, 5000, valid_sets=[validlgb], early_stopping_rounds=100, verbose_eval=100)\n\nnn = train_nn(trainxe, np.log1p(trainy), validxe, np.log1p(validy), testxe)\npredy = nn.predict(testxe, num_iteration=nn.best_iteration)\npredvy = nn.predict(validxe, num_iteration=nn.best_iteration)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"15f286dff450617f0a66b7408639d49e3e671cbe"},"cell_type":"markdown","source":"### Results\nFor both validation and test submission, in the next two respective cells, we have to:\n<br> &nbsp;&nbsp; (a) set to 0 any value &lt; 0;\n<br> &nbsp;&nbsp; (b) sum the revenue per visitor (`np.expm1` is the inverse of `log1p`);\n<br> &nbsp;&nbsp; (c) take `log1p` of the new values.\n\nFor the validation, the array `allZeros` is representative of the worst score attainable by a non-learning algorithm. It's as good and sometimes slightly better than random values. *NOTE: This value is 1.97 because validy is 10% of the training data, but expect it to increase/decrease as validy's % of training data increases/decreases.*"},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"f8c6293e8e14fc869ae1b71569d065c24faa0f76"},"cell_type":"code","source":"def rmse_log1p(df, col1, col2):\n    return np.sqrt(metrics.mean_squared_error(np.log1p(df[col1].values), np.log1p(df[col2].values)))\n\npredvy[predvy<0] = 0\nvalidation = pd.DataFrame({'fullVisitorId':             validvids,\n                           'totals.transactionRevenue': validy,\n                           'totals.predictedRevenue':   np.expm1(predvy),\n                           'totals.allZeros':           [0] * len(validy) })\n                           #'totals.allZeros':       np.random.randint(0, 2, (1, len(validy)))[0] })\n                           #'totals.allZeros':       np.random.random((1, len(validy)))[0] })\n\nprint(\"RMSE score where prediction always 0: \", rmse_log1p(validation, 'totals.transactionRevenue', 'totals.allZeros'))\nprint(\"RMSE score for validation (ungrouped):\", rmse_log1p(validation, 'totals.transactionRevenue', 'totals.predictedRevenue'))\nvalidation = validation.groupby('fullVisitorId')['totals.transactionRevenue','totals.predictedRevenue'].sum().reset_index()\nprint(\"RMSE score for validation (grouped):  \", rmse_log1p(validation, 'totals.transactionRevenue', 'totals.predictedRevenue'))","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true,"_uuid":"fba3a4986881c2fa7ed2b1009151a5d94b004908"},"cell_type":"code","source":"predy[predy<0] = 0\nsubmission = pd.DataFrame({'fullVisitorId':       testvids,\n                           'PredictedLogRevenue': np.expm1(predy)})\nsubmission = submission.groupby('fullVisitorId')['PredictedLogRevenue'].sum().reset_index()\nassert 617242 == submission['fullVisitorId'].nunique()  # Kaggle informs us that there can only be 617242 lines\n\nsubmission[\"PredictedLogRevenue\"] = np.log1p(submission[\"PredictedLogRevenue\"])\nsubmission.to_csv(submission_path, index=False)","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"_uuid":"7e4d502e74ec986183f5955f6797280f4e06d0b0"},"cell_type":"markdown","source":"[Top of page](#top)"},{"metadata":{"trusted":true,"_uuid":"d41566160ff6a749a4af9b2ac2e2743870cc9cd6"},"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}