{"cells":[{"metadata":{"_uuid":"0fa56c1d379080c444962eb520555598c02c7a4f"},"cell_type":"markdown","source":"*  we're predicting the natural log of the total revenue per unique user, which is, based on totals.transactionRevenue.  (Where a Nan is actually a 0).\n* We should log the sum total of *totals.transactionRevenue*\n     * https://www.kaggle.com/c/google-analytics-customer-revenue-prediction/discussion/65691#387112\n\n * https://www.kaggle.com/mlisovyi/flatten-json-fields-smart-dump-data\n * https://www.kaggle.com/jpmiller/showing-nan-in-its-various-forms"},{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","scrolled":true,"trusted":false},"cell_type":"code","source":"import matplotlib.pyplot as plt\nimport seaborn as sns\ncolor = sns.color_palette()\n%matplotlib inline\n\nimport numpy as np\nimport pandas as pd\nimport json\n# import missingno as msno\n# import hvplot.pandas\n\nPATH = './input/'","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b77246388f350545068849b690f55a1f099cfd57"},"cell_type":"markdown","source":"### Data Prep\n\n* Additional ideas for missing values and unary columns top drop:\n https://www.kaggle.com/mlisovyi/flatten-json-fields-smart-dump-data\n * https://www.kaggle.com/c/google-analytics-customer-revenue-prediction/discussion/65691#387112"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"code","source":"json_cols = ['device', 'geoNetwork', 'totals', 'trafficSource']\n\nnan_list = [\"not available in demo dataset\",\n            \"unknown.unknown\",\n            \"(not provided)\",\n            \"(not set)\"\n#             ,\"Not Socially Engaged\" # this last one is borderline \n           ]\nnan_dict = {nl:np.nan for nl in nan_list}\n\n# columns to drop : https://www.kaggle.com/c/google-analytics-customer-revenue-prediction/discussion/65691#387112\nlist_single_value = ['trafficSource.campaignCode', 'socialEngagementType', 'totals.visits']\n\ndef df_prep(file):\n    df = pd.read_csv(file, dtype={'fullVisitorId': str, 'date': str}, \n            parse_dates=['date'],infer_datetime_format=True, nrows=None)\n    \n    for jc in json_cols:  # parse json  # Would probably be better with json_normalize from pandas\n        flat_df = pd.DataFrame(df.pop(jc).apply(pd.io.json.loads).values.tolist())\n        flat_df.columns = ['{}.{}'.format(jc, c) for c in flat_df.columns]\n        df = df.join(flat_df)\n    ad_df = df.pop('trafficSource.adwordsClickInfo').apply(pd.Series) # handle dict column\n    ad_df.columns = ['adwords.{}'.format(c) for c in ad_df.columns]\n    df = df.join(ad_df)\n    df.replace(nan_dict, inplace=True) # handle disguised NaNs\n    \n    # Remove all-missing columns\n    df.dropna(how=\"all\",axis=1,inplace=True)\n    \n    df.drop([c for c in list_single_value if c in df.columns], axis=1, inplace=True)\n    \n# ### From : https://www.kaggle.com/mlisovyi/flatten-json-fields-smart-dump-data\n    df['trafficSource.isTrueDirect'] = (df['trafficSource.isTrueDirect'].fillna(False)).astype(bool)\n    df['totals.bounces'] = df['totals.bounces'].fillna(0).astype(np.uint8)\n    df['totals.newVisits'] = df['totals.newVisits'].fillna(0).astype(np.uint8) # has NaNs ?\n    df['totals.pageviews'] = df['totals.pageviews'].fillna(0).astype(np.uint16)\n    \n    # rename lat Long\n    df.rename(columns={'geoNetwork.latitude':'Latitude', 'geoNetwork.longitude':\"Longitude\"},inplace=True)\n\n    #parse unix epoch timestamp\n    df.visitStartTime = pd.to_datetime(df.visitStartTime,unit='s',infer_datetime_format=True)\n    \n#     df.set_index(['fullVisitorId', 'sessionId'], inplace=True) # disabled for now\n\n    df.drop([\"sessionId\"],axis=1,inplace=True)\n    return df","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1659a3a82dab1addd9922ea1fb4dae710f70de8b","scrolled":true,"trusted":false},"cell_type":"code","source":"%%time\n\ntrain = df_prep(PATH+'train.csv')\nprint(\"train Shape: \",train.shape)\ntest = df_prep(PATH+'test.csv')\nprint(\"test Shape: \",test.shape)\ndisplay(train.head(7))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"071b61a88082569f62760a5857a4c272344f2cf8","trusted":false},"cell_type":"code","source":"train.columns","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8cbbeb815018cb2f5de95f6ec28ca9808c77e9cc","trusted":false},"cell_type":"code","source":"train[['channelGrouping', 'date', 'fullVisitorId', 'visitId',\n       'visitNumber', 'visitStartTime', 'device.browser',\n       'device.deviceCategory', 'device.isMobile', 'device.operatingSystem',\n       'geoNetwork.city', 'geoNetwork.continent', 'geoNetwork.country',\n       'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region',\n       'geoNetwork.subContinent', 'totals.bounces', 'totals.hits',\n       'totals.newVisits', 'totals.pageviews', 'totals.transactionRevenue',\n        'trafficSource.adContent', 'trafficSource.campaign', 'trafficSource.isTrueDirect',\n       'trafficSource.keyword', 'trafficSource.medium',\n       'trafficSource.referralPath', 'trafficSource.source', 'adwords.page',\n       'adwords.slot', 'adwords.gclId', 'adwords.adNetworkType']].nunique()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9c8562b8359e2448a75024f229363ed67971913c","trusted":false},"cell_type":"code","source":"### Many variables only contain a single variable, remove them:\n### change code version ; errors due to unhashable dicts\n# columns = [col for col in train.columns if train[col].nunique() > 1] # can also be done with \".any() command\"\n# print(len(columns))\n# train = train[columns]\n# test = test[columns]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bf3b4761fb31845dacef6bb2fbcfa2610107cfa2","trusted":false},"cell_type":"code","source":"train.visitStartTime.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4ce3a5484f2fb70cc46e0922a44a5d859e64ad07"},"cell_type":"markdown","source":"## Target col: \n* We will want to sum then log at the end, (if we do it once per customer, VS predicting CLV at each point in time..?)\n* totals_transactionRevenue - nan is actually 0 \n* **Major, novel  feature: Transactions per session (mean and boolean)**\n    * Dan\n    \n* WE see most visitors never make any purchase"},{"metadata":{"_uuid":"8aef650221fbe8e17af372ce6212013b730a4fc1","trusted":false},"cell_type":"code","source":"#impute 0 for missing/NaNs of target column\ntrain['totals.transactionRevenue'] = pd.to_numeric(train['totals.transactionRevenue'].fillna(0)) #.astype(\"float\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4714b79c7d35a31047be86bef0b58d42a9714af3","trusted":false},"cell_type":"code","source":"train['totals.transactionRevenue'].dtype","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a69632bbc6a109760948ee2926a5edf579a47c99","trusted":false},"cell_type":"code","source":"train.loc[train['totals.transactionRevenue']>0]['totals.transactionRevenue'].describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a9070103528ab57eca6a9cfd069d8e021f777fe8","trusted":false},"cell_type":"code","source":"## https://www.kaggle.com/ashishpatel26/light-gbm-with-bayesian-style-parameter-tuning\n\ngdf = train.groupby(\"fullVisitorId\")[\"totals.transactionRevenue\"].sum().reset_index()\n\nplt.figure(figsize=(9,7))\nplt.scatter(range(gdf.shape[0]), np.sort(np.log1p(gdf[\"totals.transactionRevenue\"].values)))\nplt.xlabel('index', fontsize=12)\nplt.ylabel('TransactionRevenue', fontsize=12)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7399dc7ee162b8bcf2a0c90266fd81a484746fa6","scrolled":true,"trusted":false},"cell_type":"code","source":"print(\"Train set: shape {} with {} unique users\"\n      .format(train.shape,train['fullVisitorId'].nunique()))\nprint(\"Test set: shape {} with {} unique users\"\n      .format(test.shape,test['fullVisitorId'].nunique()))\nprint(\"Users in both train and test set:\",\n      len(set(train.fullVisitorId.unique()).intersection(set(test.fullVisitorId.unique()))))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dc4ac5443137331c6fd248a04080a469dcf05015"},"cell_type":"markdown","source":"#### Test data\n* we predict once per customer. (in test) , using the last entry\n* multiple rows present = we have more history for them -> concat with train data for history.\n* Note the lack of overlap with train -> we may want to entirely exclude the y/target from history, to avouid leaks! "},{"metadata":{"trusted":false,"_uuid":"2de0daefbc0c2d1b72dbef884f82fa424fd76e96"},"cell_type":"code","source":"test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"199a9cea58f8ec8c2879498818cc7ab52f358704"},"cell_type":"code","source":"print(\"orig test shape:\",test.shape)\ntest_pred = test.set_index(\"visitStartTime\",drop=False).groupby(\"fullVisitorId\").last().reset_index().drop_duplicates(\"fullVisitorId\")\nprint(\"pred ready test shape:\",test_pred.shape)\ntest_pred.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"afff1dcb1f33d325f33884abf7104111c2a17134"},"cell_type":"markdown","source":"### finals target data\n* start with data per user at their final time stamp. This isn't what we'd use for a final model but can give us great insights , and iscompatible with feature engineering for the historical data!\n* Get last timestamp for each fullVisitorId,  sum historical totals.transactionRevenue transactions, then log that sum.\n    * There's no history for ~80% of users (i.e most appear only once). \n    * Also, user history - note train/test disjoint and sparsity!\n    \n    \n    * featurize: https://stackoverflow.com/questions/45022226/find-days-since-last-event-pandas-dataframe"},{"metadata":{"_uuid":"53416cfe05d87fd0bfc028693bc0924484cac40a","scrolled":true,"trusted":false},"cell_type":"code","source":"# df2 = train.drop([#\"date\",\n#                   \"sessionId\"\n# #                   , \"visitId\" # ? \n#                  ],axis=1)\n\ndf2 = train.copy()\n\ndf2[\"sumLog_transactionRevenue\"] = df2[[\"fullVisitorId\",\"totals.transactionRevenue\"]].groupby(\"fullVisitorId\")[\"totals.transactionRevenue\"].transform(\"sum\")\n# log transform target (we don't do log1P on purpose)! \ndf2['sumLog_transactionRevenue'] = df2['sumLog_transactionRevenue'].apply(lambda x: np.log1p(x)) #.apply(lambda x: np.log(x) if x > 0 else x)\nprint(\"# unique visitor IDs : \", df2.fullVisitorId.nunique())\nprint(\"subset initial Data shape\", df2.shape)\ndf2 = df2.set_index(\"visitStartTime\",drop=False).groupby(\"fullVisitorId\").last().drop(\"totals.transactionRevenue\",axis=1).reset_index()\nprint(\"Data with target + only last entry in train per fullVisitorId:\", df2.shape)\ndf2.tail()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6c0d30b4f8cf0f5b13b21b5946abbc5bcf623c35"},"cell_type":"markdown","source":"## Concat context\n* train + test historical data\n*Could drop last entries in test for space saving.. But might be wanted for country level feature, cooccurrence etc'?\n"},{"metadata":{"trusted":false,"_uuid":"2c20af75bc50b2c0dbfa269e8610b045e6a69164"},"cell_type":"code","source":"df_context = pd.concat([train,test])\ndf_context.shape","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4e1f2a70e34a129efbd80e0a5fbf0a501837f91c"},"cell_type":"markdown","source":"## Save data\n* Could use Feather or binary format, but let's stay simple\n"},{"metadata":{"_uuid":"eb0e435edc4f94906e526900190f9236bd125025","trusted":false},"cell_type":"code","source":"# is enabling INDEXes, then keep index!\ndf2.to_csv(\"gstore_train_CLV_v1.csv.gz\",index=False,compression=\"gzip\")\n# train.to_csv(\"gstore_train_v1.csv.gz\",index=False,compression=\"gzip\")\n# test.to_csv(\"gstore_test_v1.csv.gz\",index=False,compression=\"gzip\")\n\ndf_context.to_csv(\"gstore_context_all_v1.csv.gz\",index=False,compression=\"gzip\")\ntest_pred.to_csv(\"gstore_test_Pred_v1.csv.gz\",index=False,compression=\"gzip\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a3ea6707515a48b1facf2787302d9ae5bd3bfda7"},"cell_type":"markdown","source":"## NaN EDA cont\n*Source:  https://www.kaggle.com/jsaguiar/complete-exploratory-analysis\n        * code requires changing (fullVisitorId in index in my version)\n*  'totals_transactionRevenue' column=  the transaction value for each visit. \n*Train set has 98.72% of missing values which we can consider as zero revenue (no purchase).\n* The black lines are the closest normal distribution that we can fit to each distribution."},{"metadata":{"_uuid":"a40086d5bd21b5916672d33ed6bbeb21313f8a20","trusted":false},"cell_type":"code","source":"non_missing = len(train[~train['totals.transactionRevenue'].isnull()])\nnum_visitors = train[~train['totals.transactionRevenue'].isnull()]['fullVisitorId'].nunique()\nprint(\"totals.transactionRevenue has {} non-missing values or {:.3f}% (train set)\"\n      .format(non_missing, 100*non_missing/len(train)))\nprint(\"Only {} unique users have transactions or {:.3f}% (train set)\"\n      .format(num_visitors, num_visitors/train['fullVisitorId'].nunique()))\n# Logn Distplot\nrevenue = train['totals.transactionRevenue'].dropna().astype('float64')\nplt.figure(figsize=(10,4))\nplt.title(\"Natural log Distribution - Transactions revenue\")\nax1 = sns.distplot(np.log(revenue), color=\"#006633\", fit=norm)\n# Log10 Distplot\nplt.figure(figsize=(10,4))\nplt.title(\"Log10 Distribution - Transactions revenue\")\nax1 = sns.distplot(np.log10(revenue), color=\"#006633\", fit=norm)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7357cd289be7b19f1bfbadc9db21be9cc2093b4e"},"cell_type":"markdown","source":"### Transaction Revenue\n\nOur target column, transactionRevenue, looks especially sparse. Let's look closer..."},{"metadata":{"_uuid":"bc859c3f409cb1c3eddf7ea43037942b2e2d3623","trusted":false},"cell_type":"code","source":"target_df = pd.read_csv('../input/train.csv', usecols=['totals'])\nflat_df = pd.io.json.json_normalize(target_df.totals.apply(json.loads))\nflat_df['transactionRevenue'] = flat_df.transactionRevenue.astype(np.float32)\nflat_df.transactionRevenue.isnull().sum()/flat_df.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"00fbd859bbb7c8b79b58328ed3e17118b2f2e608","trusted":false},"cell_type":"code","source":"flat_df.fillna(0, inplace=True)\nflat_dft.hist('transactionRevenue', bins=24) #.hvplo","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"217a4dc85ffb7fa09d2a8882740a2790a3bae8de"},"cell_type":"markdown","source":"Well, OK...there's quite a bit of 0s here.  Zooming in on the 1%  greater than 0 shows the difference between browsers and buyers."},{"metadata":{"_uuid":"9aea0c96a29ee483fed3ae09a085cdc6b3c38c5b","trusted":false},"cell_type":"code","source":"flat_df.replace(0, np.NaN, inplace=True)\nflat_df.hist('transactionRevenue', bins=25) #.hvplot","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.6.3"}},"nbformat":4,"nbformat_minor":1}