{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nimport os\nprint(os.listdir(\"../input/1-step-by-step-format-data-to-columnar-format/\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5daccb8db16193ce00b61645134a8b6a268523b8"},"cell_type":"code","source":"import pandas as pd\nidir = \"../input/1-step-by-step-format-data-to-columnar-format/\"\ntrain_df = pd.read_csv(idir + \"train_flat.csv\", \n    dtype={'fullVisitorId': str, 'date': str, 'visitId':str, 'visitNumber':str, 'visitStartTime':str, 'sessionId': str  },\n    nrows=1000000, \n    low_memory=False)\n\ntest_df = pd.read_csv(idir + \"test_flat.csv\", \n    dtype={'fullVisitorId': str, 'date': str, 'visitId':str, 'visitNumber':str, 'visitStartTime':str, 'sessionId': str  },\n    nrows=1000000, \n    low_memory=False)\n\ntrain_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"31cadc68282fea870fde665dd9b17651e5bb6e98"},"cell_type":"code","source":"%%time\ncdf = pd.concat([train_df.count(), train_df.nunique(), train_df.isna().sum(), train_df.dtypes], axis = 1)\ncdf = cdf.reset_index()\ncdf.columns = [\"Column_Name\", \"Total_Records\", \"Unique_Values\", \"Null Values\", \"data_types\"]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"fd1ca906f69fd6456a8d76cddf977deb70281f14"},"cell_type":"code","source":"cdf[cdf.Total_Records > 0].reset_index()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d6f93e5124d397938214790bbe99a647458afb43"},"cell_type":"markdown","source":"## Drop columns in train not in test \nExcept for total revenue"},{"metadata":{"trusted":true,"_uuid":"794a482d30834bcbf819f644af882cb0994f95ad"},"cell_type":"code","source":"print(\"Variables not in test but in train : \", set(train_df.columns).difference(set(test_df.columns)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4088f838a4a8f0016f210b1e790778c75a72aa44"},"cell_type":"code","source":"train_df.drop(columns = ['trafficSource_campaignCode'], inplace = True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"31da6557e886bdd19708b958e25e488a270f1320"},"cell_type":"markdown","source":"### Drop columns that has 0 values"},{"metadata":{"trusted":true,"_uuid":"0cfd798e005a1f29d033b5bfaa87f624f3b8f003"},"cell_type":"code","source":"%%time\nfor df in [train_df, test_df]: \n    df.drop(columns = cdf[cdf.Total_Records == 0][\"Column_Name\"], inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b009092b7aced0d44ceacb13377e7885971400d1","scrolled":true},"cell_type":"code","source":"train_df.select_dtypes('object').describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5874ba0285f2152d948b3168b140b3bf54d11226"},"cell_type":"code","source":"train_df.select_dtypes(exclude = 'object').describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b191a4301b5dbd25050bee4d275a003fb0f5b772"},"cell_type":"markdown","source":"## Fill in 0's for null numeric values"},{"metadata":{"trusted":true,"_uuid":"30b8d6fb39f3a3d9fd352f62fc8a48a03e58bdf4"},"cell_type":"code","source":"for df in [train_df, test_df]: \n    df['totals_transactionRevenue'] = train_df['totals_transactionRevenue'].fillna(0)\n    df['totals_newVisits'] = train_df['totals_newVisits'].fillna(0)\n    df['totals_bounces'] = train_df['totals_newVisits'].fillna(0)\n    df['trafficSource_adwordsClickInfo.page'] = train_df['trafficSource_adwordsClickInfo.page'].fillna(0)\n\ntrain_df.select_dtypes(exclude = 'object').describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2b156497fbaeb2b84bbdd2aa117523279f38daac"},"cell_type":"markdown","source":"## Handle the Text Boolean columns"},{"metadata":{"trusted":true,"_uuid":"459c41bd5c2434e67189cc13711cba812ee3121a"},"cell_type":"code","source":"for df in ([train_df, test_df]): \n    df['trafficSource_adwordsClickInfo.isVideoAd'] = df['trafficSource_adwordsClickInfo.isVideoAd'].fillna(True)\n    df['trafficSource_isTrueDirect'] = df['trafficSource_isTrueDirect'].fillna(False)\n    df['trafficSource_isVideoAd'] = df['trafficSource_adwordsClickInfo.isVideoAd'].map({True: 1, False: 0})\n    df['trafficSource_isDirect'] = df['trafficSource_isTrueDirect'].map({True: 1, False: 0})\n    df.drop(columns = ['trafficSource_adwordsClickInfo.isVideoAd', 'trafficSource_isTrueDirect'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"510b599ab1e6d1c3439c8442e4b3e56fa833c89f"},"cell_type":"markdown","source":"## Handle the null character columns\n\n### TODO: Hierarchy to be  be check  if data low level data is avialable\n-> If country is available, then we should populate continent and sub-continent  \n-> If Device is available, OS should be guessed"},{"metadata":{"trusted":true,"_uuid":"7a39fdc269c9ce9289ab7b56909243100f66c854"},"cell_type":"code","source":"%%time\nfor df in [train_df, test_df]: \n    for col in df.select_dtypes('object').columns: \n        df[col] = df[col].fillna('DEF')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a98ec0fc5f2da3385579a86a6502c995ccc8b5d5"},"cell_type":"code","source":"train_df.select_dtypes('object').describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"92cb694c95a7bef8ef5c8d937609c45900eb9fb5"},"cell_type":"code","source":"train_df.select_dtypes(exclude = 'object').describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fadce5e2d1e9041b6164951ce9867493f9f7531a"},"cell_type":"markdown","source":"## Group values with few rows and 0 revenue into a single value\n\nSome columns has huge list of values and we have to split them into batches of 1000's"},{"metadata":{"trusted":true,"_uuid":"22090659cc2f7c9a15ec537ca27d92b94437057e"},"cell_type":"code","source":"%%time\n# attributes = ['device_browser']\nattributes = ['device_browser', 'device_deviceCategory', 'device_operatingSystem', 'geoNetwork_city', 'geoNetwork_metro', 'geoNetwork_region', 'geoNetwork_country', 'geoNetwork_subContinent', 'geoNetwork_continent', 'geoNetwork_networkDomain', 'trafficSource_adContent', 'trafficSource_adwordsClickInfo.adNetworkType', 'trafficSource_adwordsClickInfo.page', 'trafficSource_adwordsClickInfo.slot', 'trafficSource_campaign', 'trafficSource_keyword', 'trafficSource_medium', 'trafficSource_referralPath', 'trafficSource_source', 'trafficSource_adwordsClickInfo.gclId']\nfor att in attributes: \n    print(att)\n    c = train_df[att].value_counts()\n    s = train_df.groupby(att)['totals_transactionRevenue'].sum()\n    df = pd.concat([c, s], axis = 1, sort=True)\n    df.columns = ['recs', 'tr']\n    df = df.reset_index()\n    df.columns = ['idx', 'recs', 'tr']\n    val_to_keep = list(df[ (df.recs > 10000) | (df.tr > 0 ) ].idx)\n    val_to_keep.append('DEF')\n    d = dict()\n    counter = 0\n    for i in c.index:\n        if i not in val_to_keep: \n            d[i] = \"DEF\"\n            counter = counter + 1\n            if ( counter % 2000 == 0 ): \n                print(\"Iter : \" + str(round(counter/2000)))\n                train_df[att].replace(d, inplace=True)\n                test_df[att].replace(d, inplace=True)\n                d = dict()\n    train_df[att].replace(d, inplace=True)\n    test_df[att].replace(d, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"06d40d050f4b5dd21333c2755fee598a9821f715"},"cell_type":"code","source":"f = './train_flat_cln1.csv'\ntrain_df.to_csv(f, index = False)\ns = os.stat(f)\nnum_lines = sum(1 for line in open(f))\nprint(f + \":\" + str(round(s.st_size / (1024 * 1024)) ) + \" MB : \" + str(num_lines) + \" lines\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d4ccb6fdc5a96adc63742d42dc32c43127d25eac"},"cell_type":"code","source":"f = './test_flat_cln1.csv'\ntest_df.to_csv(f, index = False)\ns = os.stat(f)\nnum_lines = sum(1 for line in open(f))\nprint(f + \":\" + str(round(s.st_size / (1024 * 1024)) ) + \" MB : \" + str(num_lines) + \" lines\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f109867e62859f8120ade5ede6ed025bf1bd8c07"},"cell_type":"markdown","source":"## Correlation analysis"},{"metadata":{"trusted":true,"_uuid":"50b1f4b492bd2a6f949b43d73aa3ee49f59713c3","scrolled":true},"cell_type":"code","source":"c = train_df.select_dtypes(exclude = 'object').drop(columns = ['totals_visits', 'trafficSource_isVideoAd', 'trafficSource_isDirect']).corr()\nc","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"97da70908c18175795876524f90b2d3a365ace07"},"cell_type":"code","source":"from plotly.offline import init_notebook_mode, iplot\nimport plotly.graph_objs as go\nfrom plotly import tools","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8b73d261a826466f154eb387102b400b101d39ef","_kg_hide-input":true},"cell_type":"code","source":"init_notebook_mode(connected=True)\ntrace = go.Heatmap(z = c.values.tolist(), x = c.columns, y = c.columns, colorscale='RdBu')\ndata=[trace]\nlayout = go.Layout(\n    autosize=False,\n    width=600,\n    height=600,\n)\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='basic-heatmap')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5c46f45b5dbbe5551cbef1e01603dc72969c2246"},"cell_type":"markdown","source":"From the above figure we see revenue is correlated with total_hits and page_views"},{"metadata":{"_uuid":"171150e1cf435841bf95cb740460c744666577d2"},"cell_type":"markdown","source":"## Understanding the variables\nWe have 4 JSON objects in the source - Device, Location, Traffic and Totals source.  Lets analyze these 4 in detail."},{"metadata":{"_uuid":"4d72d8d8dcffa3253bbe1c75ebd89c774c3454e0"},"cell_type":"markdown","source":"## Program to create "},{"metadata":{"_kg_hide-input":true,"trusted":true,"scrolled":false,"_uuid":"001e5afb9e1071f463b3ca998572fa64399b458d"},"cell_type":"code","source":"def create_count_rev_plot(bar_series, scatter_series): \n    trace1 = go.Bar(\n        x = bar_series.index, \n        y = bar_series.values, \n        name = 'No of Records'\n    )\n    trace2 = go.Scatter(\n        x = scatter_series.index, \n        y = scatter_series['totals_transactionRevenue'],\n        yaxis = 'y2', \n        mode='markers+text', \n        name = 'TR'\n    )\n    trace3 = go.Scatter(\n        x = scatter_series.index, \n        y = scatter_series['totals_transactionRevenue'] / scatter_series['totals_pageviews'],\n        yaxis = 'y3', \n        mode='markers+text', \n        name = 'TR / PV'\n    )\n    trace4 = go.Scatter(\n        x = scatter_series.index, \n        y = scatter_series['totals_transactionRevenue'] / scatter_series['totals_hits'],\n        yaxis = 'y4', \n        mode='markers+text', \n        name = 'TR / Hit'\n    )\n    layout = go.Layout(\n        title=sc.name + ' & ' + rs.name,\n        yaxis=dict(\n            title='No. of Records', \n            titlefont=dict(\n                color='#1f77b4'\n            ),\n            tickfont=dict(\n                color='#1f77b4'\n            )\n        ),\n        yaxis2=dict(\n            title='Total Revenue',\n            titlefont=dict(\n                color='#ff7f0e'\n            ),\n            tickfont=dict(\n                color='#ff7f0e'\n            ),\n            anchor='free',\n            overlaying='y',\n            side='left',\n            position=0.02\n        ),\n        yaxis3=dict(\n            title='Total Revenue Per PageView',\n            titlefont=dict(\n                color='#d62728'\n            ),\n            tickfont=dict(\n                color='#d62728'\n            ),\n            anchor='x',\n            overlaying='y',\n            side='right'        \n        ),\n        yaxis4=dict(\n            title='Total Revenue Per Hit',\n            titlefont=dict(\n                color='#9467bd'\n            ),\n            tickfont=dict(\n                color='#9467bd'\n            ),\n            anchor='free',\n            overlaying='y',\n            side='right',\n            position=0.98        \n        )\n    )\n    data = [trace1, trace2, trace3, trace4]\n    fig = go.Figure(data=data, layout=layout)\n    fig['layout'].update(showlegend = False, height = 600, width = 800)\n    return iplot(fig, filename='multiple-axes-multiple')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"4737ae5f8c8b94e6b1b28f90755f93d53d0a8582"},"cell_type":"code","source":"for att in [\"device_browser\", 'device_deviceCategory', 'device_operatingSystem']:\n    sc = train_df[att].value_counts()\n    sc.name = att\n    rs = train_df.groupby(att)['totals_transactionRevenue', 'totals_hits', 'totals_pageviews'].sum()\n    rs.name = \"Revenues\"\n    create_count_rev_plot(sc,rs)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a4c84997189eca344b8352c3d1e0988ce57cb0de","scrolled":false},"cell_type":"code","source":"# Skip: 'geoNetwork_networkDomain'\nfor att in [\"geoNetwork_city\", 'geoNetwork_metro', 'geoNetwork_region', 'geoNetwork_country', 'geoNetwork_subContinent', 'geoNetwork_continent', 'geoNetwork_networkDomain']:\n    sc = train_df[att].value_counts()\n    sc.name = att\n    rs = train_df.groupby(att)['totals_transactionRevenue', 'totals_hits', 'totals_pageviews'].sum()\n    rs.name = \"Revenues\"\n    create_count_rev_plot(sc,rs)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"fc06fe28872bf7f768281364ead1c749008a7014"},"cell_type":"code","source":"# Skip: 'trafficSource_adwordsClickInfo.gclId'\nfor att in [\"trafficSource_adContent\", 'trafficSource_adwordsClickInfo.adNetworkType', 'trafficSource_adwordsClickInfo.isVideoAd', 'trafficSource_adwordsClickInfo.page', 'trafficSource_adwordsClickInfo.slot', 'trafficSource_campaign', 'trafficSource_isTrueDirect', 'trafficSource_keyword', 'trafficSource_medium', 'trafficSource_referralPath', 'trafficSource_source', 'trafficSource_adwordsClickInfo.gclId']:\n    sc = train_df[att].value_counts()\n    sc.name = att\n    rs = train_df.groupby(att)['totals_transactionRevenue', 'totals_hits', 'totals_pageviews'].sum()\n    rs.name = \"Revenues\"\n    create_count_rev_plot(sc,rs)","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}