{"cells":[{"metadata":{"_uuid":"5c0d9e48dc6fd1ea7c0a3ef072525ca3eafd09ac"},"cell_type":"markdown","source":"**Objective of the notebook:**\n\nIn this notebook, We will analyze the data and try to perform simple data analysis around Missing values in the data set\n\n**Objective of the competition:**\n\nIn this competition, we a’re challenged to analyze a Google Merchandise Store (also known as GStore, where Google swag is sold) customer dataset to predict revenue per customer."},{"metadata":{"_uuid":"35dfe7ed52e520d178edb350512e8b4f3cc3d231","trusted":false},"cell_type":"code","source":"import warnings\nwarnings.filterwarnings('ignore')\n\nimport os\nimport json\nimport numpy as np\nimport pandas as pd\nfrom pandas.io.json import json_normalize\nimport matplotlib.pyplot as plt\n\nimport seaborn as sns\ncolor = sns.color_palette()\n\n%matplotlib inline\npd.options.mode.chained_assignment = None\npd.options.display.max_columns = 999\n\nfrom IPython.display import HTML, display\nimport tabulate\n\nimport plotly.offline as py\npy.init_notebook_mode(connected=True)\nfrom plotly.offline import init_notebook_mode, iplot\ninit_notebook_mode(connected=True)\nimport plotly.graph_objs as go\nimport plotly.offline as offline\noffline.init_notebook_mode()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c98bb488393c1810839705bf004f40df96977459"},"cell_type":"markdown","source":"**File Descriptions**\n\ntrain.csv - the training set - contains the same data as the BigQuery rstudio_train_set.\n\ntest.csv - the test set - contains the same data as the BigQuery rstudio_test_set.\n\n**Data Fields**\n\nfullVisitorId- A unique identifier for each user of the Google Merchandise Store.\n\nchannelGrouping - The channel via which the user came to the Store.\n\ndate - The date on which the user visited the Store.\n\ndevice - The specifications for the device used to access the Store.\n\ngeoNetwork - This section contains information about the geography of the user.\n\nsessionId - A unique identifier for this visit to the store.\n\nsocialEngagementType - Engagement type, either \"Socially Engaged\" or \"Not Socially Engaged\".\n\ntotals - This section contains aggregate values across the session.\n\ntrafficSource - This section contains information about the Traffic Source from which the session originated.\n\nvisitId - An identifier for this session. This is part of the value usually stored as the _utmb cookie. This is only unique to the user. For a completely unique ID, you should use a combination of fullVisitorId and visitId.\n\nvisitNumber - The session number for this user. If this is the first session, then this is set to 1.\n\nvisitStartTime - The timestamp (expressed as POSIX time).\n\n**Credit Note** : Using code from kernal [**Quick start: read csv and flatten json fields**](https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields) by [Julián Peller1 ](https://www.kaggle.com/julian3833)"},{"metadata":{"_uuid":"2d50751183fd6a6601d7a7e31aa727ecb8dae707","trusted":false},"cell_type":"code","source":"def load_df(csv_path='../input/train.csv', nrows=None):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    \n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'}, # Important!!\n                     nrows=nrows)\n    \n    for column in JSON_COLUMNS:\n        column_as_df = json_normalize(df[column])\n        column_as_df.columns = [f\"{column}.{subcolumn}\" for subcolumn in column_as_df.columns]\n        df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)\n    print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n    return df","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e3f9380aeebe3f1b0ca444cab7ba6580cabf888c","trusted":false},"cell_type":"code","source":"%%time\ntrain_df = load_df()\ntest_df = load_df(\"../input/test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ec49909cb03226298f2cbba636a6ed53d060fd6f","trusted":false},"cell_type":"code","source":"print('size of training data : ', train_df.shape)\nprint('size of testing data  : ', test_df.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a63e74ce8460267218413bbe0abbbe572191fb4f"},"cell_type":"markdown","source":"** Train Data snippets : **"},{"metadata":{"_uuid":"dc7b6ff9c23e59943fa52ddddfe7dd3149e73b46","trusted":false},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5e748106808a58bc0b54738f7237d9cd3a11f4ea"},"cell_type":"markdown","source":"** Column names for training data ** "},{"metadata":{"_uuid":"1df4cb17e13b3a25deb0436140e36ad092bb4c63","trusted":false},"cell_type":"code","source":"train_df.columns.values","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3e6efda5134aa7bb1e90545b3da52874d897a9dd"},"cell_type":"markdown","source":"** Train Data snippets : **"},{"metadata":{"_uuid":"c2f69387ca7c4ac6b1fd6fbe1c6a17e989d3c04d","trusted":false},"cell_type":"code","source":"test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f761a691b558bcb68737d7614a300f728bb60dbe"},"cell_type":"markdown","source":"**Column names for testing data**"},{"metadata":{"_uuid":"cb330a04ab2ec123daf2c7457294d713b64afdb5","trusted":false},"cell_type":"code","source":"test_df.columns.values","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ca61757846d1e21be1e5a68fe9e12bf384f2e159"},"cell_type":"markdown","source":"*** Missing values assesment ***\n\nIn this section \n\n    [a] We will display the counts and percentage of missing value.\n    \n    [b] We will explore the attribites with missing values \n    \n    [c] We will try to explore if we can provide a recomendation for imputation of missing values.\n"},{"metadata":{"_uuid":"fa5302d2c4e2b8fe00e3d871fe37e07857056141"},"cell_type":"markdown","source":"** Below statatistics shows that there are 8 columns with more than 97% missing values **\n\nNext task is to analyse the missing attribute and try to recommnd on imputing the missing values"},{"metadata":{"_uuid":"9ab95280dac4df2bf2f3a99b0acd70e9c00a51ca","trusted":false},"cell_type":"code","source":"total = train_df.isnull().sum().sort_values(ascending = False)\npercent = (train_df.isnull().sum() / train_df.isnull().count()*100).sort_values(ascending = False)\nmissing_application_train_data  = pd.concat([total, percent], axis=1, keys=['Total', 'Percent'])\nmissing_application_train_data.head(20)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0a2a6f17bd78f488cc6a378b1520c750ed325906"},"cell_type":"markdown","source":"<h3> <span style=\"color:,brown\">**Feature # 1 : trafficSource.campaignCode** </span></h3>\n\n*** Description : *** \n\nValue of the utm_id campaign tracking parameter, **used for manual campaign tracking.**\n\n*** Analysis : ***\n\nIn Train dataframe trafficSource.campaignCode has value in only one cell as a result we can drop this attribute. \n \nIn Test dataframe trafficSource.campaignCode is not present.\n\n**Recomendation :**   We can plan to remove this feature."},{"metadata":{"scrolled":true,"trusted":false,"_uuid":"85f013c6818740ddb555b4e3fe3e281cac8ce3bb"},"cell_type":"code","source":"temp1 = train_df['trafficSource.campaignCode'].value_counts()\n\ntrace1 = go.Bar(\n    x = temp1.index,\n    y = temp1 ,\n)\n\ndata = [trace1]\n\nlayout = go.Layout(\n    title = \"Campaign code for training data\",\n    xaxis=dict(\n        title='Campaign codes',\n        domain=[0, 0.5]\n    ),\n    \n    yaxis=dict(\n        title='Count of Campaign codes '\n        \n    )\n)\n\nfig = go.Figure(data=data, layout=layout)\npy.iplot(fig, filename='Campaign code')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"10e620c6a790ab1b909880a761ad7b18908f7b95"},"cell_type":"code","source":"train_df['trafficSource.campaignCode'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"29e81a75777fe315f5b5c8424b67e781bca1607a"},"cell_type":"markdown","source":"<h3> <span style=\"color:,brown\">**Feature # 2 : totals.transactionRevenue :** </span></h3>\n\n**Description :** \nTotal transaction revenue, expressed as <span style=\"color:,blue\">the value passed to Analytics multiplied by 10^6. *(e.g., 2.40 would be given as 2400000).</span>\n\n**Analysis : **\n\nNow This is the Target attribute . \n\n**Recomendation :** 5332 datapoints has valid numerical values and rest all datapoints can be populated with value 0_\n"},{"metadata":{"_uuid":"ef97d5e438f87869ccca43a09befc2a2f2a28409","trusted":false},"cell_type":"code","source":"transactionRevenue = train_df['totals.transactionRevenue'].value_counts()\nprint(transactionRevenue.head())\nlen(transactionRevenue)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dbfdfc705b7b96d04a2339eaa410c1f4c8ca4cf6"},"cell_type":"markdown","source":"<h3> <span style=\"color:,brown\">**Feature # 3 : trafficSource.adwordsClickInfo.page :** </span></h3>\n\n**Description : ** \n\nPage number in search results where the ad was shown.\n\n**Analysis :**\n\nIn 21362 cases add was shown on 1st page, In 73 cases add was shown on 2nd page and so on.\n\n**Recomendation : ** _With my experience I never go beyond 3 / 4  pages of search results so this feature is a good candidate for binning._\n"},{"metadata":{"trusted":false,"_uuid":"2f28ab19f972f13e88157e8d0ad407656c6844e0"},"cell_type":"code","source":"temp1 = train_df['trafficSource.adwordsClickInfo.page'].value_counts()\ntemp2 = test_df['trafficSource.adwordsClickInfo.page'].value_counts()\n\n\ntrace1 = go.Bar(\n    x = temp1.index,\n    y = temp1,\n   \n)\ntrace2 = go.Bar(\n    x = temp2.index,\n    y = temp2 \n    \n)\n\ndata = [trace1, trace2]\n\nlayout = go.Layout(\n    title = \"Page # where the ad was shown for training data\",\n    width = 900,\n    xaxis=dict(\n        title='Page #',\n        tickfont=dict(\n            size=14,\n            color='rgb(107, 107, 107)'\n        )\n    ),\n    \n    yaxis=dict(\n        title='# of instances',  \n        titlefont=dict(\n            size=16,\n            color='rgb(107, 107, 107)'\n        ),\n        tickfont=dict(\n            size=14,\n            color='rgb(107, 107, 107)'\n        )\n    )\n)\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8aa65546a1a96ab2c3a49721dd7e7628e104828b","trusted":false},"cell_type":"code","source":"print(train_df['trafficSource.adwordsClickInfo.page'].value_counts())\nprint(test_df['trafficSource.adwordsClickInfo.page'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"22623005fc08f9e9313b66f72076c561baf00b0e"},"cell_type":"markdown","source":"<h3> <span style=\"color:,brown\">**Feature # 4 : trafficSource.adwordsClickInfo.adNetworkType :** </span></h3>\n\n**Description :** \n\nNetwork Type. Takes one of the following values: {“Google Search\", \"Content\", \"Search partners\", \"Ad Exchange\", \"Yahoo Japan Search\", \"Yahoo Japan AFS\", “unknown”}\n\n**Analysis : **\n\nTrain dataframe contains *** Google Search and Search partners *** (Content class is missing in training data). \nTest dataframe contains *** Content, Google Search and Search partners. ***\n\n**Recomendation :**  _Not sure how to handle \"content\" ._ \n    We may need to bin the whole data into 2 bins \n    \n        1) Google Search and \n        \n        2) Others "},{"metadata":{"trusted":false,"_uuid":"35e5eabc4a359c982456c0f5032183be93d366cb"},"cell_type":"code","source":"temp1 = train_df['trafficSource.adwordsClickInfo.adNetworkType'].value_counts()\ntemp2 = test_df['trafficSource.adwordsClickInfo.adNetworkType'].value_counts()\n\n\ntrace1 = go.Bar(\n    x = temp1.index,\n    y = temp1,\n    name = 'train'\n   \n)\ntrace2 = go.Bar(\n    x = temp2.index,\n    y = temp2,\n    name = 'test'\n    \n)\n\ndata = [trace1, trace2]\n\nlayout = go.Layout(\n    title = \"Page # where the ad was shown for training data\",\n    width = 900,\n    xaxis=dict(\n        title='Page #',\n        tickfont=dict(\n            size=14,\n            color='rgb(107, 107, 107)'\n        )\n    ),\n    \n    yaxis=dict(\n        title='# of instances',  \n        titlefont=dict(\n            size=16,\n            color='rgb(107, 107, 107)'\n        ),\n        tickfont=dict(\n            size=14,\n            color='rgb(107, 107, 107)'\n        )\n    )\n)\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0f507e60c222dcf5b47ec14fcaa3b627960f1e1d","trusted":false},"cell_type":"code","source":"print(train_df['trafficSource.adwordsClickInfo.adNetworkType'].value_counts())\nprint(test_df['trafficSource.adwordsClickInfo.adNetworkType'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"257030bed6dc59b5d63747d0830ca809507b3ccd"},"cell_type":"markdown","source":"<h3> <span style=\"color:,brown\">**Feature # 5 : trafficSource.adwordsClickInfo.slot :** </span></h3>\n\n**Description :*** \n\nPosition of the Ad. Takes one of the following values:{“RHS\", \"Top\"}\n\n**Analysis : **\n\nAs per the document Top and RHS are the valid values but Test dataframe also contains Google Display Network, which may be an invalid entry. \n\n**Recomendation : **  We can replace all NaN's and Google Display Network with unique value class."},{"metadata":{"trusted":false,"_uuid":"01739fa1858796b80e011baa9e6df85b28dcf400"},"cell_type":"code","source":"temp1 = train_df['trafficSource.adwordsClickInfo.slot'].value_counts()\ntemp2 = test_df['trafficSource.adwordsClickInfo.slot'].value_counts()\n\n\ntrace1 = go.Bar(\n    x = temp1.index,\n    y = temp1,\n    name = 'train'\n   \n)\ntrace2 = go.Bar(\n    x = temp2.index,\n    y = temp2,\n    name = 'test'\n    \n)\n\ndata = [trace1, trace2]\n\nlayout = go.Layout(\n    title = \"Page # where the ad was shown for training data\",\n    width = 900,\n    xaxis=dict(\n        title='Page #',\n        tickfont=dict(\n            size=14,\n            color='rgb(107, 107, 107)'\n        )\n    ),\n    \n    yaxis=dict(\n        title='# of instances',  \n        titlefont=dict(\n            size=16,\n            color='rgb(107, 107, 107)'\n        ),\n        tickfont=dict(\n            size=14,\n            color='rgb(107, 107, 107)'\n        )\n    )\n)\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"311b9c06fcaf3cfe84f5273f92f52e8b2e3089a1","trusted":false},"cell_type":"code","source":"print(train_df['trafficSource.adwordsClickInfo.slot'].value_counts())\nprint(test_df['trafficSource.adwordsClickInfo.slot'].value_counts())\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b328c8257fdeb3ceae99edce90e90c02e64589de"},"cell_type":"markdown","source":"<h3> <span style=\"color:,brown\">**Feature # 6 : trafficSource.adwordsClickInfo.isVideoAd : ** </span></h3>\n\n**Description : **\n\nTrue if it is a Trueview video ad.\n \n**Analysis and Recomendation: **\n\nIn train and test Dataframe user can replace all NaN's with True."},{"metadata":{"_uuid":"40fc47011909e9739e9f1fca7a9b53fc1b9c6dc0","trusted":false},"cell_type":"code","source":"print(train_df['trafficSource.adwordsClickInfo.isVideoAd'].value_counts())\nprint(test_df['trafficSource.adwordsClickInfo.isVideoAd'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a291aee1479775e786777a79a5a989919f853af8"},"cell_type":"markdown","source":"<h3> <span style=\"color:,brown\">**Feature # 7 : trafficSource.isTrueDirect : ** </span></h3>\n\n**Description : **\n\nTrue if the source of the session was Direct (meaning the user typed the name of your website URL into the browser or came to your site via a bookmark), This field will also be true if 2 successive but distinct sessions have exactly the same campaign details. Otherwise NULL.\n\n**Analysis and Recomendation :**\n\nIn train and test Dataframe user can replace all NaN's with False.\n\n"},{"metadata":{"_uuid":"64335b3fd3fb52992a0781e340fb65bb9bd72fb7","trusted":false},"cell_type":"code","source":"print(train_df['trafficSource.isTrueDirect'].value_counts())\nprint(test_df['trafficSource.isTrueDirect'].value_counts())\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ab1bdaa397c36992057c29c1423fe67fb2b8563b"},"cell_type":"markdown","source":"<h3> <span style=\"color:,brown\">**Feature # 8 : trafficSource.referralPath : **</span></h3>\n\n**Description : **\n\nIf trafficSource.medium is \"referral\", then this is set to the path of the referrer"},{"metadata":{"_uuid":"1b21977bc66bd2ca3b39e245ef02d0e17a9220d8","trusted":false},"cell_type":"code","source":"#print(train_df['trafficSource.referralPath'].value_counts())\n#print(test_df['trafficSource.referralPath'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"73939fb880139d8a5d9430e587118ce71d79d075"},"cell_type":"markdown","source":"<h3> <span style=\"color:,brown\">**Feature # 9 : trafficSource.keyword : ** </span></h3>\n\n**Description : **\n\nThe keyword of the traffic source, usually set when the trafficSource.medium is \"organic\" or \"cpc\"."},{"metadata":{"_uuid":"fca620724ca5c95711c7b77fd783a688a25a43c6","trusted":false},"cell_type":"code","source":"# print(train_df['trafficSource.keyword'].value_counts())\n# print(test_df['trafficSource.keyword'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"91a75982dfd097dc22d2448a66ef198748ee3c7a"},"cell_type":"markdown","source":"<h3> <span style=\"color:,brown\">**Feature # 10 : totals.bounces : ** </span></h3>\n\n**Description : **\nTotal bounces (If the user leave the site rather than continuing on the site). For a bounced session, the value is 1, otherwise it is null.\n\n**Analysis ** \n\n**[Bounce rate](https://en.wikipedia.org/wiki/Bounce_rate)** is an Internet marketing term used in web traffic analysis. It represents the percentage of visitors who enter the site and then leave (\"bounce\") rather than continuing to view other pages within the same site. Bounce rate is basically calculated on how much time a person spends on your site.\n\n**Recomendation ** \n\nReplace all NaN's with _0_"},{"metadata":{"_uuid":"75d5ceccf972e3f1ba30263df47745b456ff3791","trusted":false},"cell_type":"code","source":"print(train_df['totals.bounces'].value_counts())\nprint(test_df['totals.bounces'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f88b7f5b93dc06cf349393ec4df248786f4dc26f"},"cell_type":"markdown","source":"<h3> <span style=\"color:,brown\">**Feature # 11 : totals.newVisits : ** </span></h3>\n\n**Description : **\n\nTotal number of new users in session (for convenience). If this is the first visit, this value is 1, otherwise it is null.\n\n**Recomendation :** \n\nreplace all NaN's with _0_"},{"metadata":{"_uuid":"adc45259fd3dc25f57562e9880e09b79e831694a","trusted":false},"cell_type":"code","source":"print(train_df['totals.newVisits'].value_counts())\nprint(test_df['totals.newVisits'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"00f91c41d5a42dec8ed73951e89906637b317db5"},"cell_type":"markdown","source":"\n"}],"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}