{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import os\nimport json\nimport numpy as np\nimport pandas as pd\nfrom scipy.stats import norm\nfrom scipy import stats\nimport bq_helper\nimport matplotlib.pyplot as plt \nfrom plotly.offline import init_notebook_mode, iplot\nimport plotly.graph_objs as go\nfrom plotly import tools\ninit_notebook_mode(connected=True)\nimport warnings\nwarnings.filterwarnings('ignore')\n\nfrom pandas.io.json import json_normalize\nimport matplotlib.pyplot as plt\nimport seaborn as sns\ncolor = sns.color_palette()\n%matplotlib inline\nimport matplotlib\nmatplotlib.style.use('ggplot')\nsns.set_style('whitegrid')\n\nfrom plotly import tools\nimport plotly.offline as py\nfrom plotly.offline import init_notebook_mode, iplot\npy.init_notebook_mode(connected=True)\nimport plotly.graph_objs as go\n\nfrom sklearn import model_selection, preprocessing, metrics\nimport lightgbm as lgb\n\npd.options.mode.chained_assignment = None\npd.options.display.max_columns = 999\nimport os\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"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    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ac81d0c33635c053418aa9fde79e0042fec7bb69"},"cell_type":"code","source":"%%time\ntrain = load_df()\ntest = load_df(\"../input/test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ab550f63f30512ab2d82504a59ed8d5a3275056c"},"cell_type":"markdown","source":"# 1. Variables Identification"},{"metadata":{"trusted":true,"_uuid":"4491a76138e7f783f8a0e8f41a537380fa1dfb41"},"cell_type":"code","source":"#Size of datasets\nprint(\"Size of train data  (Rows, Columns): \",train.shape)\nprint(\"Size of test data  (Rows, Columns): \",test.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"15d4396d2e17e53bd44fa88821b22589551770d9"},"cell_type":"code","source":"#general information of data\nprint(\"Train data info: \",train.info())\nprint(\"----------------------------------------------------------------\")\nprint(\"Test data info: \",test.info())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"10912d83eb72898db62b602fa4491715ba1b82f3"},"cell_type":"code","source":"train.columns.values","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"74f933b08382232a3ae72dd1870da6e5a6064ee4"},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a7dab71b5bc27e9e047c853fa0a8e541010c8eda"},"cell_type":"code","source":"test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"34d99ab30a268b65a5bb52db667ca3b6ad3fe7e8"},"cell_type":"code","source":"print(\"Datatypes of Train dataset are:\")\nprint(train.dtypes.value_counts())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"399db316ebb5d0a7a026a57a689fb1574cbe0346"},"cell_type":"code","source":"#function for finding categorial variables\ndef cat_val(df):\n    categorical_variables = df.dtypes.loc[df.dtypes == 'object'].index\n    return categorical_variables\ncat_val(train)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"408aff3aa8bf41c486d1378c8beb2293e43cc366"},"cell_type":"markdown","source":"# 2. Missing Values"},{"metadata":{"trusted":true,"_uuid":"b89ac608862130b07de407aa7864302d2cf65478"},"cell_type":"code","source":"def missing_check(df):\n    total = df.isnull().sum().sort_values(ascending=False)\n    percent = (df.isnull().sum()/df.isnull().count()).sort_values(ascending=False)*100\n    missing_data = pd.concat([total, percent], axis=1, keys=['Total', 'Percent']) \n    #print(\"Missing check:\",missing_data )\n    return missing_data","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"06bf6ebd1ebcd5150b223ab17305c967ccb837aa"},"cell_type":"code","source":"missing_train=missing_check(train)\nmissing_train[missing_train['Total']>0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"79ba6b535260f147b9983eee6166dccedc10370c"},"cell_type":"code","source":"missing_test=missing_check(test)\nmissing_test[missing_test['Total']>0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"50969771583f31d5b73e5f6ccfbe187db194c95d"},"cell_type":"code","source":"def missing_value_exp(df):\n    missing_values = df.isnull().sum(axis=0).reset_index()\n    missing_values.columns = ['column_name', 'Total']\n    missing_values = missing_values.loc[missing_values['Total']>0]\n    missing_values = missing_values.sort_values(by='Total')\n\n    ind = np.arange(missing_values.shape[0])\n    width = 0.1\n    fig, ax = plt.subplots(figsize=(16,4))\n    rects = ax.barh(ind, missing_values.Total.values, color='r')\n    ax.set_yticks(ind)\n    ax.set_yticklabels(missing_values.column_name.values, rotation='horizontal')\n    ax.set_xlabel(\"Count of Missing Observations\")\n    ax.set_title(\"Missing Categorical Observations in Dataset\")\n    plt.show()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e76d32ca6ca3d296559eea06b70f413f7e535cd7"},"cell_type":"code","source":"missing_value_exp(train)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c85a224d345f1fe30513b7cee636d7519fb223d1"},"cell_type":"code","source":"missing_value_exp(test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e86cf777a7c81aefb7cc1834003c0e072ab6b1c6"},"cell_type":"markdown","source":"# 3. Univariate Analysis"},{"metadata":{"_uuid":"7a4545d8b92e18d87d3435d75bddb44a701f3b24"},"cell_type":"markdown","source":"**Target Variable Exploration**:"},{"metadata":{"trusted":true,"_uuid":"666982c9c0f3b6b424d68850adfd314d377662f8"},"cell_type":"code","source":"print(\"Basic Statistics of Target variable(totals.transactionRevenue): \")\ntrain[\"totals.transactionRevenue\"].astype('float').describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0fa9a7f9757f6f70544a65dcc722440e8d633818"},"cell_type":"code","source":"print(\"Skewness: %f\" % train[\"totals.transactionRevenue\"].astype('float').skew())\nprint(\"Kurtosis: %f\" % train[\"totals.transactionRevenue\"].astype('float').kurt())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"38b42e6718934e872b3168da9c37d5e744f3ad50"},"cell_type":"code","source":"#histogram and normal probability plot\nsns.distplot(train[\"totals.transactionRevenue\"].astype('float').dropna(), fit=norm);\nfig = plt.figure()\nres = stats.probplot(train[\"totals.transactionRevenue\"].astype('float').dropna(), plot=plt)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cd5da94fc2177490be8974d83a7c05e22852030d"},"cell_type":"code","source":"train[\"totals.transactionRevenue\"] = train[\"totals.transactionRevenue\"].astype('float')\nvisit = train.groupby(\"fullVisitorId\")[\"totals.transactionRevenue\"].sum().reset_index()\n\nplt.figure(figsize=(10,8))\nplt.scatter(range(visit.shape[0]), np.sort(np.log1p(visit[\"totals.transactionRevenue\"].values)))\nplt.xlabel('index', fontsize=12)\nplt.ylabel('TransactionRevenue', fontsize=10)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"045bda2a96701f21ae47eb1250cb23b952794278"},"cell_type":"code","source":"visit = train.groupby(\"fullVisitorId\")[\"totals.transactionRevenue\"].sum().reset_index()\nnzi = pd.notnull(train[\"totals.transactionRevenue\"]).sum()\nnzr = (visit[\"totals.transactionRevenue\"]>0).sum()\nprint(\"Number of instances in train set with non-zero revenue : \", nzi, \" and ratio is : \", nzi / train.shape[0])\nprint(\"Number of unique customers with non-zero revenue : \", nzr, \"and the ratio is : \", nzr / visit.shape[0])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d30164e9564a9e2bcd035d66b7226d62eadbca88"},"cell_type":"markdown","source":"**Number of visitors and common visitors:**"},{"metadata":{"trusted":true,"_uuid":"97cb0766829cf8c7cb6417a9bd8fe30a7dbc9155"},"cell_type":"code","source":"print(\"Number of unique visitors in train set : \",train.fullVisitorId.nunique(), \" out of rows : \",train.shape[0])\nprint(\"Number of unique visitors in test set : \",test.fullVisitorId.nunique(), \" out of rows : \",test.shape[0])\nprint(\"Number of common visitors in train and test set : \",len(set(train.fullVisitorId.unique()).intersection(set(test.fullVisitorId.unique())) ))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9efab8443b723da759be3f1ac16bbfbea3f49610"},"cell_type":"markdown","source":"**Columns with constant values:**"},{"metadata":{"trusted":true,"_uuid":"ca329809b4bf3177f2f33d8c228c3b5be7a3971f"},"cell_type":"code","source":"#Columns with constant values:\nconst_cols = [i for i in train.columns if train[i].nunique(dropna=False)==1 ]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6115b33c272ada8aba63a420bfa143325ba42570"},"cell_type":"code","source":"const_cols","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"236aeb644a27ce086d0dc8297c606a3086a4bc15"},"cell_type":"markdown","source":"** Device Attributes**"},{"metadata":{"trusted":true,"_uuid":"3f25a20525168749e67550c9ffd1b4bfabd7e2da"},"cell_type":"code","source":"device_cols = [\"device.browser\", \"device.deviceCategory\", \"device.operatingSystem\"]\n\ncolors = [\"#d6a5ff\", \"#fca6da\", \"#f4d39c\", \"#a9fcca\"]\ntraces = []\nfor i, col in enumerate(device_cols):\n    t = train[col].value_counts()\n    traces.append(go.Bar(marker=dict(color=colors[i]),orientation=\"h\", y = t.index[:15][::-1], x = t.values[:15][::-1]))\n\nfig = tools.make_subplots(rows=1, cols=3, subplot_titles=[\"Visits: Category\", \"Visits: Browser\",\"Visits: OS\"], print_grid=False)\nfig.append_trace(traces[1], 1, 1)\nfig.append_trace(traces[0], 1, 2)\nfig.append_trace(traces[2], 1, 3)\n\nfig['layout'].update(height=400, showlegend=False, title=\"Visits by Device Attributes\")\niplot(fig)\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1e49cdc2c1a7e0a88f4a28af7a9ff65131d38c6d"},"cell_type":"code","source":"## convert transaction revenue to float\ntrain[\"totals.transactionRevenue\"] = train[\"totals.transactionRevenue\"].astype('float')\n\ndevice_cols = [\"device.browser\", \"device.deviceCategory\", \"device.operatingSystem\"]\n\nfig = tools.make_subplots(rows=1, cols=3, subplot_titles=[\"Mean Revenue: Category\", \"Mean Revenue: Browser\",\"Mean Revenue: OS\"], print_grid=False)\n\ncolors = [\"red\", \"green\", \"purple\"]\ntrs = []\nfor i, col in enumerate(device_cols):\n    tmp = train.groupby(col).agg({\"totals.transactionRevenue\": \"mean\"}).reset_index().rename(columns={\"totals.transactionRevenue\" : \"Mean Revenue\"})\n    tmp = tmp.dropna().sort_values(\"Mean Revenue\", ascending = False)\n    tr = go.Bar(x = tmp[\"Mean Revenue\"][::-1], orientation=\"h\", marker=dict(opacity=0.5, color=colors[i]), y = tmp[col][::-1])\n    trs.append(tr)\n\nfig.append_trace(trs[1], 1, 1)\nfig.append_trace(trs[0], 1, 2)\nfig.append_trace(trs[2], 1, 3)\nfig['layout'].update(height=400, showlegend=False, title=\"Mean Revenue by Device Attributes\")\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b4c78d1437db5cc7696de1c9cab1bccdf23ac547"},"cell_type":"markdown","source":"**GeoNetwork Attributes**"},{"metadata":{"trusted":true,"_uuid":"327a931ef448e9bb2c500264372702edbd6fa8ae"},"cell_type":"code","source":"geo_cols = ['geoNetwork.city', 'geoNetwork.continent','geoNetwork.country',\n            'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region','geoNetwork.subContinent']\ngeo_cols = ['geoNetwork.continent','geoNetwork.subContinent']\n\ncolors = [\"#d6a5ff\", \"#fca6da\"]\nfig = tools.make_subplots(rows=1, cols=2, subplot_titles=[\"Visits : GeoNetwork Continent\", \"Visits : GeoNetwork subContinent\"], print_grid=False)\ntrs = []\nfor i,col in enumerate(geo_cols):\n    t = train[col].value_counts()\n    tr = go.Bar(x = t.index[:20], marker=dict(color=colors[i]), y = t.values[:20])\n    trs.append(tr)\n\nfig.append_trace(trs[0], 1, 1)\nfig.append_trace(trs[1], 1, 2)\nfig['layout'].update(height=400, margin=dict(b=150), showlegend=False)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5e928576603a05cab7f56f0f217a3c96113c0966"},"cell_type":"code","source":"geo_cols = ['geoNetwork.continent','geoNetwork.subContinent']\nfig = tools.make_subplots(rows=1, cols=2, subplot_titles=[\"Mean Revenue: Continent\", \"Mean Revenue: SubContinent\"], print_grid=False)\n\ncolors = [\"blue\", \"orange\"]\ntrs = []\nfor i, col in enumerate(geo_cols):\n    tmp = train.groupby(col).agg({\"totals.transactionRevenue\": \"mean\"}).reset_index().rename(columns={\"totals.transactionRevenue\" : \"Mean Revenue\"})\n    tmp = tmp.dropna().sort_values(\"Mean Revenue\", ascending = False)\n    tr = go.Bar(y = tmp[\"Mean Revenue\"], orientation=\"v\", marker=dict(opacity=0.5, color=colors[i]), x= tmp[col])\n    trs.append(tr)\n\nfig.append_trace(trs[0], 1, 1)\nfig.append_trace(trs[1], 1, 2)\nfig['layout'].update(height=450, margin=dict(b=200), showlegend=False)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b4023ee4118d1656c78651d81214a4f21ea542de"},"cell_type":"markdown","source":"**Traffic Attributes**"},{"metadata":{"trusted":true,"_uuid":"e815d2d49c49c55433fbf9ffd8004fb832075303"},"cell_type":"code","source":"fig = tools.make_subplots(rows=1, cols=2, subplot_titles=[\"TrafficSource Campaign (not-set removed)\", \"TrafficSource Medium\"], print_grid=False)\n\ncolors = [\"#d6a5ff\", \"#fca6da\", \"#f4d39c\", \"#a9fcca\"]\nt1 = train[\"trafficSource.campaign\"].value_counts()\nt2 = train[\"trafficSource.medium\"].value_counts()\ntr1 = go.Bar(x = t1.index, y = t1.values, marker=dict(color=colors[3]))\ntr2 = go.Bar(x = t2.index, y = t2.values, marker=dict(color=colors[2]))\ntr3 = go.Bar(x = t1.index[1:], y = t1.values[1:], marker=dict(color=colors[0]))\ntr4 = go.Bar(x = t2.index[1:], y = t2.values[1:])\n\nfig.append_trace(tr3, 1, 1)\nfig.append_trace(tr2, 1, 2)\nfig['layout'].update(height=400, margin=dict(b=100), showlegend=False)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bf208c06699859ebf5727f19dc18eeb285e7a615"},"cell_type":"markdown","source":"**Channel Grouping**"},{"metadata":{"trusted":true,"_uuid":"ee59fbb1fa0b8ed139c90f26d549a67172d55694"},"cell_type":"code","source":"tmp = train[\"channelGrouping\"].value_counts()\ncolors = [\"#8d44fc\", \"#ed95d5\", \"#caadf7\", \"#6161b7\", \"#7e7eba\", \"#babad1\"]\ntrace = go.Pie(labels=tmp.index, values=tmp.values, marker=dict(colors=colors))\nlayout = go.Layout(title=\"Channel Grouping\", height=400)\nfig = go.Figure(data = [trace], layout = layout)\niplot(fig, filename='basic_pie_chart')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"767b3253c372609e0549ec31d7cc1d280adff4e6"},"cell_type":"code","source":"\nimport datetime\n\ndef scatter_plot(cnt_srs, color):\n    trace = go.Scatter(\n        x=cnt_srs.index[::-1],\n        y=cnt_srs.values[::-1],\n        showlegend=False,\n        marker=dict(\n            color=color,\n        ),\n    )\n    return trace\n\ntrain['date'] = train['date'].apply(lambda x: datetime.date(int(str(x)[:4]), int(str(x)[4:6]), int(str(x)[6:])))\ncnt_srs = train.groupby('date')['totals.transactionRevenue'].agg(['size', 'count'])\ncnt_srs.columns = [\"count\", \"count of non-zero revenue\"]\ncnt_srs = cnt_srs.sort_index()\n#cnt_srs.index = cnt_srs.index.astype('str')\ntrace1 = scatter_plot(cnt_srs[\"count\"], 'red')\ntrace2 = scatter_plot(cnt_srs[\"count of non-zero revenue\"], 'blue')\n\nfig = tools.make_subplots(rows=2, cols=1, vertical_spacing=0.08,\n                          subplot_titles=[\"Date - Count\", \"Date - Non-zero Revenue count\"])\nfig.append_trace(trace1, 1, 1)\nfig.append_trace(trace2, 2, 1)\nfig['layout'].update(height=800, width=800, paper_bgcolor='rgb(233,233,233)', title=\"Date Plots\")\npy.iplot(fig, filename='date-plots')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"183df224f2adba2a4f17822d7e897d8fe1c2ec19"},"cell_type":"code","source":"def add_date_features(df):\n    df['date'] = df['date'].astype(str)\n    df[\"date\"] = df[\"date\"].apply(lambda x : x[:4] + \"-\" + x[4:6] + \"-\" + x[6:])\n    df[\"date\"] = pd.to_datetime(df[\"date\"])\n    \n    df[\"month\"]   = df['date'].dt.month\n    df[\"day\"]     = df['date'].dt.day\n    df[\"weekday\"] = df['date'].dt.weekday\n    return df \n\ntrain = add_date_features(train)\n\ntmp = train['date'].value_counts().to_frame().reset_index().sort_values('index')\ntmp = tmp.rename(columns = {\"index\" : \"dateX\", \"date\" : \"visits\"})\n\ntr = go.Scatter(mode=\"lines\", x = tmp[\"dateX\"].astype(str), y = tmp[\"visits\"])\nlayout = go.Layout(title=\"Visits by date\", height=400)\nfig = go.Figure(data = [tr], layout = layout)\niplot(fig)\n\n\ntmp = train.groupby(\"date\").agg({\"totals_transactionRevenue\" : \"mean\"}).reset_index()\ntmp = tmp.rename(columns = {\"date\" : \"dateX\", \"totals_transactionRevenue\" : \"mean_revenue\"})\ntr = go.Scatter(mode=\"lines\", x = tmp[\"dateX\"].astype(str), y = tmp[\"mean_revenue\"])\nlayout = go.Layout(title=\"MonthlyRevenue by date\", height=400)\nfig = go.Figure(data = [tr], layout = layout)\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6e537345a861cf665f1adea24f8c71ec4f1b3b59"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"13b790f3776a1eeec7532190d15b256b6d72c72e"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"81ee29256076fb6f85a5b54096b14e2701f4b899"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8e44f273843a3a7a83ccc7a0e1cb849572ed9250"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c80e4cdbe10f44bb0498de11b67cac538cbc5af7"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c1d162dd2f080c7a846445f33a1f99f303dadd23"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6c84e30bae667dfaa2d05a09ee5c594af2740adc"},"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}