{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"#Action\n\n#Import Libraries\n\nimport datetime\nimport math\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\nimport os\nprint(os.listdir(\"../input\"))\n\nimport json # to convert json in df\nfrom pandas.io.json import json_normalize # to normalize the json file","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"# peak at the dataset\ntrain_head = pd.read_csv(\"../input/train.csv\",nrows=5)\n#show train data \ntrain_head","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"574fa4ba2d9cbd22812948f527f598857a6e0ea1"},"cell_type":"code","source":"#peak at the dataset\ntest_head = pd.read_csv(\"../input/test.csv\",nrows=5)\n#show train data \ntest_head\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7d9483fc9a5799857fe5e777ef01e018ad4393ee"},"cell_type":"markdown","source":"**4 fields in nest Jason format**\n*  device\n* geoNetwork\n* totals\n* trafficSource\n"},{"metadata":{"trusted":true,"_uuid":"1546d45f224252f3340b2cee90803462e9894f2e"},"cell_type":"code","source":"#load train dataset\ntrain = pd.read_csv(\"../input/train.csv\", low_memory=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"7f4c2137fc42eab52118a847410517abe9774620"},"cell_type":"code","source":"#shape and column names train\nprint (train.shape)\nprint (train.columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4f29eca441de5214d45386eb9acb0dbf9e9ccd3a"},"cell_type":"code","source":"# load test dataset\ntest = pd.read_csv(\"../input/test.csv\", low_memory=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"127bbb397e76d336131dd8002729aa919b358c67"},"cell_type":"code","source":"#shape and column names of test\nprint (test.shape)\nprint (test.columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9f5099b085331a8fc752056068e522777bbcf064"},"cell_type":"code","source":"sampleSubmission = pd.read_csv(\"../input/sample_submission.csv\")\n#shape and column names of submission file\nprint (sampleSubmission.shape)\nprint (sampleSubmission.columns)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6e3e94391c9386423100d4be5f78b691fb383fcb"},"cell_type":"code","source":"#Train:\n# sessionId = fullVisitorId + visitId\nprint(len(train))\nprint(train.sessionId.nunique())\nprint(train.fullVisitorId.nunique())\nprint(train.visitId.nunique())\n\n# sessionid is not unique for somereason, duplicates do exist.\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9a925f2b593821df9a29affe8bd123bfda64789c"},"cell_type":"code","source":"#test:\nprint(len(test))\nprint(test.sessionId.nunique())\nprint(test.fullVisitorId.nunique())\nprint(test.visitId.nunique())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6e52dbb65da31aeb17ddaf49659175a80db900b2"},"cell_type":"markdown","source":"**fullVisitorId 617242 matches between test and submission.**"},{"metadata":{"trusted":true,"_uuid":"98f7c9c1cc47d627493710c3e5aba3f1eac7a662"},"cell_type":"code","source":"#Understand the data types \ntrain.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7dd6d477e40b1451bac32f34c42a024b9bf1ad38"},"cell_type":"code","source":"#Action\n#borrowed code to parse JSON objects, big query another alternative, need to review this code\n# removed sampling and added check, takes time to run\n\ncolumns = ['device', 'geoNetwork', 'totals', 'trafficSource'] # Columns that have json format\n\ndir_path = \"../input/\" # you can change to your local \n\n# p is a fractional number to skiprows and read just a random sample of the our dataset. \n#p = 0.07 # *** In this case we will use 50% of data set *** #\n\n#Code to transform the json format columns in table\ndef json_read(df):\n    #joining the [ path + df received]\n    data_frame = dir_path + df\n    \n    #Importing the dataset\n    df = pd.read_csv(data_frame, \n                     converters={column: json.loads for column in columns}, # loading the json columns properly\n                     dtype={'fullVisitorId': 'str'}) # transforming this column to string\n        \n    for column in columns: #loop to finally transform the columns in data frame\n        #It will normalize and set the json to a table\n        column_as_df = json_normalize(df[column]) \n        # here will be set the name using the category and subcategory of json columns\n        column_as_df.columns = [f\"{column}.{subcolumn}\" for subcolumn in column_as_df.columns] \n        # after extracting the values, let drop the original columns\n        df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)\n        print(\"check\")\n \n    # Printing the shape of dataframes that was imported     \n    print(f\"Loaded {os.path.basename(data_frame)}. Shape: {df.shape}\")\n    return df # returning the df after importing and transforming\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7e07b379f906282f89ef2f85e110c776c10fe358"},"cell_type":"code","source":"%%time\n#Action\n\n#Loading the train again with new function\ndf_train = json_read(\"train.csv\")\nprint (\"executed time:\",datetime.datetime.now())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a77c109227874e6763a7c9103f39ebda40591652"},"cell_type":"code","source":"#peak the dataset\ndf_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3268ea57b3cab15a826ef94c02ae20b8eefd7234"},"cell_type":"code","source":"#Garbage collection, will error if no train and test\nimport gc\ndel [[train,test]]\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0d666dd9e97c57ac2e68d5810a82b3d8cb9941c9"},"cell_type":"code","source":" %who\n    # what variables exist in the program, to ensure it is deleted","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f60b91abedf0dc57dd69a60402ca586b43db08cd"},"cell_type":"markdown","source":"**Action: unwrap the test data once the analysis is over in the train**"},{"metadata":{"_uuid":"7629030f30ab8fa24af0688d45db51c4b8a133da"},"cell_type":"markdown","source":"totals.transactionRevenue\tfield has the revenue for each session."},{"metadata":{"trusted":true,"_uuid":"d915980d5859443583e0516aaa5cfa8b663384a6"},"cell_type":"code","source":"#what datatypes ?\ndf_train.dtypes\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"619c28e78f86f4ff75a31a5017c1b60da57f3bda"},"cell_type":"markdown","source":"**Missing value analysis and any field transformation**\n1. find null values, analyze them\n2. way to replace missing values\n3. Date Transformation\n4. Many values are not numberical should be converted ?\n5. Not available in demo dataset  - is not NAN or NULL counted, only NAN** - can be removed"},{"metadata":{"trusted":true,"_uuid":"0249e01c5477a532cedd057ba978016b59835a56"},"cell_type":"code","source":"# find the null values \ntotal=df_train.isnull().sum()\ntotal \n#df_train[\"channelGrouping\"].value_counts()\n#Describe the Data\n#df_train.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1e1df3e64ece165a1f6d8499acbbb8a8175a7f86"},"cell_type":"code","source":"# what numeric variables \nnumeric_features = df_train.select_dtypes(include=[np.number])\nnumeric_features.columns\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"490055804bfe3108a01c7948f497629676cea291"},"cell_type":"code","source":"# what non numeric variables \nnumeric_features = df_train.select_dtypes(include=[np.object])\nnumeric_features.columns","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6fa0c05c89486b751e695ef3dd1ccb19bc7c918b","trusted":true},"cell_type":"code","source":"#Action\n#let fill the missing values\n# my priority is address few fields i like, not to replace all the missing fields\n\n\ndef FillingNaValues(df):    # fillna numeric feature\n    df['totals.pageviews'].fillna(1, inplace=True) #filling NA's with 1\n    df['totals.newVisits'].fillna(0, inplace=True) #filling NA's with 0\n    df['totals.bounces'].fillna(0, inplace=True)   #filling NA's with 0\n    df[\"totals.transactionRevenue\"] = df[\"totals.transactionRevenue\"].fillna(0.0) #filling NA with zero\n    \n    return df #return the transformed dataframe","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d86a9bff31c67a1742405399bf59079adea96276"},"cell_type":"code","source":"#Action\n# replace missing values using the above fucntion\ndf_train = FillingNaValues(df_train)\nprint (\"executed time:\",datetime.datetime.now())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2ac92e158541ec741e23701128f5d754d4b0d4ab"},"cell_type":"code","source":"#Action\n#change date formating using function (borrowed,quote source)\n\nfrom datetime import datetime\ndef date_process(df):\n    df[\"date\"] = pd.to_datetime(df[\"date\"], format=\"%Y%m%d\") # seting the column as pandas datetime\n    df[\"_weekday\"] = df['date'].dt.weekday #extracting week day\n    df[\"_day\"] = df['date'].dt.day # extracting day\n    df[\"_month\"] = df['date'].dt.month # extracting day\n    df[\"_year\"] = df['date'].dt.year # extracting day\n    df['_visitHour'] = (df['visitStartTime'].apply(lambda x: str(datetime.fromtimestamp(x).hour))).astype(int)\n    \n    return df #returning the df after the transformations","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ae99ab5f5665484e354cb914e7734d68a1f55bd5"},"cell_type":"code","source":"#Action\n#Call the function for date formating\ndf_train = date_process(df_train) #calling the function that we created above\n\ndf_train.head(2) #printing the first 2 rows of our dataset","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c744c4486dcf3dfa698aacb0ae448d4d93ce35b0"},"cell_type":"code","source":"#Action\n# find the constant columns and remove them \nconstant_columns = []\nfor col in df_train.columns:\n    if len(df_train[col].value_counts()) == 1:\n        constant_columns.append(col)\n\n#print column names \nconstant_columns        ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c5b7fc7b64da9b9c9462aaa48b4dd356cb3c8e7c"},"cell_type":"code","source":"#Action\n#delete the columns which has empty values \nfor x in constant_columns:\n    df_train.drop(x,axis=1, inplace=True)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ab55bed6aed426d8fdf742a7194befd21c579d17"},"cell_type":"code","source":"df_train.shape\ndf_train.columns","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"826c5503af70a31d762e482a9fd4c30595e08f3f"},"cell_type":"markdown","source":"Convert these Object datatype to Numeric to perform aggregation\n* totals.bounces \n* totals.hits\n* totals.newVisits\n* totals.pageviews\n* totals.transactionRevenue\n"},{"metadata":{"trusted":true,"_uuid":"9a642ba140f6d71fe23a0297040dd1acb03bcc14"},"cell_type":"code","source":"#Action\n# Data type conversion object to Int\ndf_train['totals.bounces'] = df_train['totals.bounces'].astype(str).astype(int)\ndf_train['totals.hits'] = df_train['totals.hits'].astype(str).astype(int)\ndf_train['totals.newVisits'] = df_train['totals.newVisits'].astype(str).astype(int)\ndf_train['totals.pageviews'] = df_train['totals.pageviews'].astype(str).astype(float)\ndf_train['totals.transactionRevenue'] = df_train['totals.transactionRevenue'].astype(str).astype(float)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8dbc7595c6aff738f1c4d1e198c93230ef40a75d"},"cell_type":"code","source":"df_train.groupby('channelGrouping')['totals.transactionRevenue'].agg('sum')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"43ae4cc33bb8dc4b8d15e57ce9fa1b41c243809e"},"cell_type":"markdown","source":"**EDA and Plots**\n1. graphs, histograms, tables"},{"metadata":{"trusted":true,"_uuid":"5536cadd0eba28c3a52b6f5299fd5e229a05abd8"},"cell_type":"code","source":"# Natural log issue pad by 1 \n%matplotlib inline\nplt.subplot(211)\ndf_train[\"totals.transactionRevenue\"].hist(bins =5)\nplt.ylabel('transactionRevenu')\nplt.title('transactionRevenu histogram')\n\nplt.subplot(212)\nLogRevenue = np.log(df_train[\"totals.transactionRevenue\"]+1)\nLogRevenue.hist(bins =5)\nplt.ylabel('log(transactionRevenu)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b7814089937c2cb2b2e68b99bc33ff84214e889b"},"cell_type":"code","source":"# distribution of numercial values \n%matplotlib inline\nimport matplotlib.pyplot as plt\nattributes = [\"totals.bounces\", \"totals.hits\",\"totals.newVisits\",\n              \"totals.pageviews\",\"visitNumber\",\"_visitHour\"]\n\nloc[:,attributes].hist(bins =20,figsize =(20,15))\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e5c0426594a86ba513362187e8d74902d8d7df7f"},"cell_type":"code","source":"%matplotlib inline\nchannel_df = df_train['channelGrouping'].value_counts()\nchannel_df.index.name = 'channelGrouping'\nchannel_df.sort_index(inplace=True)\nchannel_df.plot(kind='bar',rot=20, title= 'Channel Distribution -count not revenue',figsize=(14,5))\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"55491e5b8ed920aa3c11d42e9f88e57463184e3e"},"cell_type":"code","source":"# sample visitor\nsample_visitor = df_train[df_train['fullVisitorId'] == '7813149961404844386'].sort_values(by='visitNumber')\n\n#sample_visitor[['channelGrouping','date','visitId','visitNumber','totals.hits','totals.pageviews','totals.transactionRevenue']].head(30)\nsample_visitor","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5f34443338210774b0428aadccf353418f1bcdd8"},"cell_type":"code","source":"#Action\ndf_train['target'] = np.log(df_train[\"totals.transactionRevenue\"]+1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"07619536554b881b83a93aba05a46efda405e154"},"cell_type":"code","source":"#verify if the transformation happended\ndf_train[df_train['fullVisitorId'] == '7813149961404844386'].sort_values(by='visitNumber')['target'].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"356e2fe7535b6c81c7b8b095180a6707e9626dfe"},"cell_type":"code","source":"#cross tab on device\npd.crosstab(df_train['device.deviceCategory'], df_train['device.isMobile'], margins=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1254bbecf0b8563fdbf6762c490dc1c0bb87e68e"},"cell_type":"code","source":"#revenue by mobile device \ng1 = df_train.groupby('device.isMobile')['target'].sum()\ng1.plot.bar()\nplt.show()\n\n# seems like non mobile device generate more revenue","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8610dee7f8d9a557692ce3844c892cb15db2cb1e"},"cell_type":"code","source":"#revenue by mobile device \ng1 = df_train.groupby('device.deviceCategory')['target'].sum()\ng1.plot.bar()\nplt.show()\n\n# Again Desktop makes more revenue","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"df06d1701202e03e2f28fb41bb20476bba90faa1"},"cell_type":"code","source":"#revenue by browser \ng1 = df_train.groupby('device.browser')['target'].sum().sort_values()\ndf =pd.DataFrame(g1)\ndf =  df[df['target']>0]\ndf.plot.barh()\nplt.show()\n\n# too many browsers, \n# chrome leads the way","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"16866b732061e53a7963521d9f1952f6b96b481f"},"cell_type":"code","source":"#revenue by Operating System \ng1 = df_train.groupby('device.operatingSystem')['target'].sum().sort_values()\ndf =pd.DataFrame(g1)\ndf =  df[df['target']>0]\ndf.plot.barh()\nplt.show()\ndf.plot(kind='bar', stacked=True)\nchannel_df.plot(kind='bar',rot=20, title= 'Channel Distribution -count not revenue',figsize=(14,5))\n\n\n# too many browsers, \n# chrome leads the way","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fd1a1ebe6c8b88c404ca45cebea275e005084e12"},"cell_type":"code","source":"df.plot(kind='bar',rot=20, title= 'revenue by OS',figsize=(14,5))\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cc5fcb26ae587212304a39c253e43684da6ca8c4"},"cell_type":"code","source":"#revenue by date \ng1 = df_train.groupby('date')['target'].sum().sort_values()\ndf =pd.DataFrame(g1)\ndf.plot(figsize=(40,5))\nplt.show()\n# definitely some seaonality going on","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6ae4724447b5482bcf06f6686051ee0d59efa230"},"cell_type":"code","source":"#revenue by year \ng1 = df_train.groupby('_year')['target'].sum().sort_values()\ndf =pd.DataFrame(g1)\ndf.plot(kind='bar',rot=20, title= 'revenue by year')\nplt.show()\n# renevue increasing each year","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d8a1bab9bd543a78f92262bd4eaf0c3a4ae68db8"},"cell_type":"code","source":"#revenue by year \ng1 = df_train.groupby(['_month'])['target'].sum()\ndf =pd.DataFrame(g1)\ndf.plot()\nplt.show()\n# Dec month has higher revenue","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7a3e518bfc5bdcd6d68cfbb55352ea70c31f8349"},"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}