{"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 pandas.io.json import json_normalize\nimport seaborn as sns\nimport matplotlib.pyplot as plt","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"**Introduction**   \n\nIn the dataset we have  two files **train.csv** and **test.csv** which are having 903653 and 804684 rows respectively.  The amount of test data is almost equal to the train data which mean that while testing the model it'll test wider range of scenarios and it'll check the accuracy of the model throughly.\n\nBefore getting started with the code it's good to get familiar with the data that we have here. Dataset contains these 12 columns which give information about the visitors , their mode of access, number of visits and more statistical detail about their location and visits.\n\nEach field of dataset is described below :\n\n* fullVisitorId- A unique id assigend to each customer.\n* channelGrouping - Channel through which the user accessed store.\n* date - The date on which the user visited the store.\n* device - Device information which was used to access store.\n* geoNetwork - It has information about the location of the visitor.\n* sessionId - An id assigned to the session created for user visit to the store. It is actually a combination of        fullVisitorId and visitId.\n* socialEngagementType - Tells the type of social enagement by the customer.\n* totals - This section contains aggregate values across the session.\n* trafficSource - Detail abou the source from where the traffic is coming.\n* visitId - An id for a specific visit each time customer is visiting the store.\n* visitNumber - Number of visit customer is having to the store.\n* visitStartTime - Timestamp for the visiting time.\n\nFrom the fields mentioned above  'device' , 'geoNetwork' , 'totals'  and trafficSource have data in the json format which we can take and add to the data frame as separate columns. Next we'll see how we can flatten these fields to get meaningful data from them in the form of columns."},{"metadata":{"_uuid":"65dfd87af1df1cca8d524655ecb6c58fdc27c321","trusted":true},"cell_type":"code","source":"def load_df(csv_path):\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'}\n                     )\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":"35034d938ee3e0707c7e0c5505fce2639f9dbcd2"},"cell_type":"markdown","source":"In the **load_df()** i'll explain the steps one by one so you can understand what is happening inside.\n\nWe've four fields as mentioned earlier which are having data in the form of json. So we've defined those fields which we'll handle one by one inside the loop.\n\nFirst of all we'll load the csv file into the data frame. While reading the csv we're using converters field to pass through the column value to **json.loads()** which will generate json object for each value in that column.We're doing it for all the columns having json data. Another parameter is **dtype** which is used to define the data type for the columns if we want to change any but we're specifying it because the overview of the data specifies that **fullVisitorId** should be string.\n\nAfter the csv is loaded, we'll fetch the values of each column and create a data frame from those json values with the help fo **json_normalize** which gives us a dataframe for the values in that column. Then we're defining the column names for the new data frame that we got from the json values. Next step is to drop the colum in existing data frame for which we're creating new columns and merge both the data frame .**right_index** and **left_index** is true to merge it based on the indexes."},{"metadata":{"trusted":true,"_uuid":"75aa730fd256e5b618f0ade8fa5c8c1bada3d208"},"cell_type":"code","source":"train_df = load_df('../input/train.csv')\ntest_df = load_df(\"../input/test.csv\")\n\ntrain_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"eedbd84ff749ab36c46ed4119d0ec0a0823accf1"},"cell_type":"markdown","source":"Now you can see after we've flattened the json fields available in our dataset and there are new columns which we don't know about yet so let's take a look at those columns. \n\nThe newly available information in our dataset are\n\n* device        \n     * device.browser\n     * device.browserSize\n     * device.broserVersion\n     * device.deviceCategory\n     * device.flashVersion\n     * device.isMobile\n     * device.language\n     * device.mobileDeviceBranding\n     * device.mobileDeviceInfo\n     * device.mobileDeviceMarketingName\n     * device.mobileDeviceModel\n     * device.mobileDeviceInputSelector\n     * device.operatingSystem\n     * device.operatingSystemVersion\n     * device.screenColor\n     * deivce.screenResolution\n* geoNetwork     \n     * geoNetwork.city\n     * geoNetwork.cityId\n     * geoNetwork.continent\n     * geoNetwork.country\n     * geoNetwork.longitude\n     * geoNetwork.latitude\n     * geoNetwork.metro\n     * geoNetwork.networkDomain\n     * geoNetwork.networkLocation\n     * geoNetwork.region\n     * geoNetwork.subContinent\n* totals    \n     * totals.bounces\n     * totals.hits\n     * totals.newVisits\n     * totals.pageviews\n     * totals.transactionRevenue\n     * totals.visits\n* trafficSource    \n     * trafficSource.adContent\n     * trafficSource.adwordsClickInfo.adNetworkType\n     * trafficSource.adwordsClickInfo.criteriaParameters\n     * trafficSource.adwordsClickInfo.gclId\n     * trafficSource.adwordsClickInfo.isVideoAd\n     * trafficSource.adwordsClickInfo.page\n     * trafficSource.adwordsClickInfo.slot\n     * trafficSource.adwordsClickInfo.campaign\n     * trafficSource.adwordsClickInfo.campaignCode\n     * trafficSource.adwordsClickInfo.isTrueDirect\n     * trafficSource.adwordsClickInfo.keyword\n     * trafficSource.adwordsClickInfo.medium\n     * trafficSource.adwordsClickInfo.referralPath\n     * trafficSource.adwordsClickInfo.source\n        \n        \n   If we'll look at the above outut then we'll notice that a lot of the columns which are added newly in the dataset are        having null values. Not specifically 'null' but values which doesn't contain any useful data so it'll be better to have a      look at such values before going further."},{"metadata":{"trusted":true,"_uuid":"4ce00eede19b835dcb0540decfbf4cdda5fa1c96","scrolled":false},"cell_type":"code","source":"def check_null_values(data_frame,dataset_name):\n    null_count = ((data_frame[data_frame.columns] == 'not available in demo dataset') | (data_frame[data_frame.columns].isna())).sum()\n    \n    #print(\"\\n--------------------{} Dataset Null Count-----------------------\".format(dataset_name))\n    #print(null_count)\n\n    keys = null_count.keys()\n    df_size = len(data_frame) \n    null_col = [col for col in keys if null_count[str(col)] > 0 if null_count[str(col)] == df_size]\n    print(\"No. of columns with null values in {} dataset : {}\".format(dataset_name,len(null_col)))\n    return null_col,null_count\n    \ntrain_null_col,train_null_count = check_null_values(train_df,\"Training\")\ntest_null_col,test_null_count = check_null_values(test_df,\"Testing\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ec6db0d5000ab64fbd77279529d65752cf03a538"},"cell_type":"markdown","source":"After that when i checked the values of all the columns whether those have data or not then i found that few columns are not giving any information. So i checked the count of such columns above and i found out that few columns are not giving any information and are of no use. Simillarly few columns are not having information for some of the rows but for other rows they have information. We've checked for both values which are not useful as well as values which are na(not available).\n\nWe got count of columns which are not having any useful information, count is 17. Since these columns are not useful for getting the desired results so it'll not be a wise decision to keep  it in the dataset as it'll only increase the processing time. In the next step we'll remove these columns and proceed towards processing the data. \n    "},{"metadata":{"trusted":true,"_uuid":"ee25a2e84b9627be6b06da32e7a4a45db69ecd1e","_kg_hide-input":false,"_kg_hide-output":false,"scrolled":false},"cell_type":"code","source":"col_labels = ['Column Name','Null Count']\nnull_count_df = pd.DataFrame(columns=col_labels,data={col_labels[0] : list(train_null_count.keys()),\n                                                     col_labels[1] : list(train_null_count.values)})\nfig,ax = plt.subplots(figsize=(20,8))\nnull_graph = sns.barplot(x=col_labels[0],y=col_labels[1],data=null_count_df,palette=sns.color_palette(\"Paired\",len(null_count_df)),ax=ax,capsize=0.5)\nnull_graph.set_xticklabels(labels=[label.split(\".\")[-1] for label in null_count_df['Column Name']],rotation=60)\n\nprint(\"\\nColumns having unnecessary information from training set \\n\" ,train_null_col)\nprint(\"\\nColumns having unnecessary information from testing set \\n\" ,test_null_col)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dc709b5d1a464914cd5e0845cfd94cec99aa86bd"},"cell_type":"markdown","source":"Actually earlier we were getting count of null values as a list but that didn't seem interesting and it was dull so i thought to bring it in a more pleasant manner so that we can have a better understanding of these values. These are the columns which we're going to  drop from the dataset. In the bar chart above you'll see a lot of bars which are shooting up high and all those are the columns which have no information that we can use for our analysis. From the chart it'll appear that more than half of the columns are having null value but it's not so. Actually the number of columns are too much to adjust within the screen so it got little bit messy. Sorry for that! I've put only last pat of the column names which are derived from json so that you can clearly understand them. We already have these column names in separate lists which we'll use to remove from the dataset."},{"metadata":{"trusted":true,"_uuid":"f5938a667616b806345935bea6ef9e1b7c1403b6","scrolled":true},"cell_type":"code","source":"train_df.drop(columns=train_null_col,inplace=True)\ntest_df.drop(columns=test_null_col,inplace=True)\ntrain_df.drop(columns=['trafficSource.campaignCode','visitStartTime'],inplace=True)\ntest_df.drop(columns=['visitStartTime'],inplace=True)\n\nprint(\"Training Set Shape after dropping columns\",train_df.shape)\nprint(\"Testing Set Shape after dropping columns\",test_df.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"04b40ef5f69be3f91ea892ab1625e94b0cd003c7"},"cell_type":"markdown","source":"We dropped the columns from the dataset and along with that I've removed  **trafficSource.campaignCode** . The reason for dropping campaignCode you'll see by looking at the count of null values in this column for training dataset then it's 903652 which is almost the size of dataset.\n\nNow you'll see that number of rows for both training and testing set is almost equal which means that both set doesn't have any extra columns which can affect the process. Even though we're done removing extra unnecessary data from the dataset but we still have some null values which has to be handled because all the machine learning algorithms doesn't work on null values. We'll look on the columns which are still having some values null but not all.\n\nSo before this we had handled the columns which were not at all useful for us. But even now we're not done with cleaning the data. There are still some columns which are having nulll values and we'll start managing them now..\n"},{"metadata":{"trusted":true,"_uuid":"f965780749ba317e93d5bbdaf6ba1848a7cfc487","scrolled":false},"cell_type":"code","source":"def handle_null(df,name) :\n    df['totals.newVisits'].fillna(0,inplace=True)\n    df['totals.pageviews'].fillna(0,inplace=True)\n    \n    null_col,null_count = check_null_values(df,name)\n    df_len = len(df)\n    \n    null_percent = [round((null_count_val/df_len)* 100,2) for null_count_val in null_count.values]\n    null_percent = pd.DataFrame(columns=['Column Name','Null Percent'],data={ 'Column Name' : df.keys(),'Null Percent' : null_percent})\n    null_percent.set_index('Column Name',inplace=True)\n    return null_percent\n\ntrain_df['totals.transactionRevenue'].fillna(0,inplace=True)\ntrain_col_null_percent = handle_null(train_df,\"Training\")\ntest_col_null_percent = handle_null(test_df,\"Testing\")\n\ndisplay(train_col_null_percent)\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c9a506f50b7af76287393d24e5785d51af34ed58"},"cell_type":"markdown","source":"First of all it'll be better to handle **totals.transactionRevenue** column because that's the column which we'll use to predict in our analysis. If that column will have any null values then training won't be accurate in predicting results. So for that i've replaced all the null values in the column to 0 because those visits by the customer didn't resulted in any purchase by them which means that total revenue from the transaction is 0.\n\nNow we'll move towards handling other columns which were having some null values and some information. When we start to look at the null value count which we calculated earlier then we'll see that many columns are still having a lot of null values or values which won't be useful for us so for those columns we'll put a threshold and discard them. I could see only 2 columns which we can use by filling some values in place of NaN. These two columns are **totals.newVisits** and **totals.pageviews**.\n\n**totals.newVisits** column specify the new visits if any by the customer and it had around **77.8%** values available under the column and only **22.2%** were missing which we thought to replace  with 0 . It has only value '1' so we thought the other value 0 to be replaced wherever we're having NaN. Simillarly for **totals.pageViews** which was missing only 100 values which also we replaced with 0 as it's almost negligible value which won't have much affect on our analysis. These two columns we've handled now. \n\nBasically while discarding columns which we should consider a threshold percentage. If any column has percentage of null values more than the threshold then we should discard that column . I've represented the number of null values in all the columns in a separate dataframe so that you can get a better understanding of them. As you can see the column are having null percentage of more than 50%. "},{"metadata":{"trusted":true,"_uuid":"4bc7d350d23a9669a86e86a4611f7912314425e3"},"cell_type":"code","source":"train_cols_to_discard = [column for column in train_col_null_percent.index.values if int(train_col_null_percent.at[column,'Null Percent']) > 22]\ntrain_cols_to_discard.extend(['fullVisitorId','totals.visits','socialEngagementType','sessionId','visitId','geoNetwork.networkDomain','trafficSource.campaign'])\n#print(train_cols_to_discard)\ntrain_df.drop(train_cols_to_discard,axis=1,inplace=True)\n\ntest_cols_to_discard = [column for column in test_col_null_percent.index.values if int(test_col_null_percent.at[column,'Null Percent']) > 22]\ntest_cols_to_discard.extend(['socialEngagementType','totals.visits','sessionId','visitId','geoNetwork.networkDomain','trafficSource.campaign'])\ntest_df.drop(test_cols_to_discard,axis=1,inplace=True)\n\ntrain_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"78ec2283ebcc8da7a88d41ec5c4172adce4b062d"},"cell_type":"markdown","source":"Generally this threshold is conisdered to be 20% but we're considering it to be **22%** in our case. Since we've one columns i.e. totals.newVisits which is having nearly 22% null values. Here we'll drop the columns which we're going more than our threshold level. \n\nWhile i was observing the data frame, it just came to my mind that we still have 2 columns which are having unique values and their presence won't have much help in analyzing the customer behavior because they're not involved in defining it. These are just unique numbers for identifying session and enagement so i dropped them as well. The question that might be coming to your mind will be that we still have **fullVisitorId** which is another unique id. The reason why i kept it in the dataset is because it's not unique for each row and number of times a visitor comes again which can somewhat tell about customers behavior.\n\nTo get rid of unnecessary data we created a list of columns which are having percentage of null values more than our threshold value and we've used this list to drop columns from training as well as test dataset. So now after all the cleaning of data we're getting 22 columns which are not having columns with substantial null values in it. Now we're in such a stage that we can start proceeding towards analyzing  data because we've data fields which are having useful data.\n\n**I'll update my analysis here very soon with some graphs and more insight into the data. Thankyou for going through my analysis and i hope that you would've got something useful out of analysis. Please suggest any changes or give feedback if you have any!! Thanks [Aaron](https://www.kaggle.com/stajh05) for your valuable feedback.**"},{"metadata":{"trusted":true,"_uuid":"b53513e061f15b76a7cdd8ebaef5a4c8c5817d05","scrolled":false},"cell_type":"code","source":"train_quarter = [int((((date%10000)/100)/4)+1) for date in train_df['date']]\ntest_quarter = [int((((date%10000)/100)/4)+1) for date in test_df['date']]\n\ndef getDay(date):\n    return date%100\n\ntrain_df = train_df.assign(quarterOfYear=train_quarter)\ntest_df = test_df.assign(quarterOfYear=test_quarter)\n\ntrain_df['date'] = train_df['date'].apply(getDay)\ntest_df['date'] = test_df['date'].apply(getDay)\n\nfrom sklearn.linear_model import LinearRegression\nfrom sklearn.preprocessing import LabelEncoder\ncol_to_encode = ['channelGrouping','device.browser','device.deviceCategory','device.isMobile','device.operatingSystem','geoNetwork.continent','geoNetwork.country','geoNetwork.subContinent', 'trafficSource.medium', 'trafficSource.source']\nle = LabelEncoder()\n\ndef encode_cols(data_frame,columns):\n    for column in col_to_encode :\n        data_frame[column] = le.fit(data_frame[column]).transform(data_frame[column]) \n\nencode_cols(train_df,col_to_encode)\nencode_cols(test_df,col_to_encode)\n        \ntraining_col = list(train_df.keys())\ntraining_col.remove('totals.transactionRevenue')\nfull_visitor_ids = test_df['fullVisitorId'].values\n\ntest_col = list(test_df.keys())\ntest_col.remove('fullVisitorId')\n\n\nX_train = train_df[training_col] \ny_train = train_df['totals.transactionRevenue']\n\n\n#print(type(train_df['device.browser'][0]))\n#print([col_val for col_val in train_df.columns.values if isinstance(train_df[col_val][0],str)])\n\n\n#train_df.apply(le.fit_transform)\n    \n#print(le.fit(train_df['device.deviceCategory']).transform(train_df[['device.deviceCategory','device.browser']]))\ntrain_df.head()\ntest_df.head()\n\n#X_train.head()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f88dec35675178f91b2f2f43013785c6751b7703","scrolled":true},"cell_type":"code","source":"from sklearn.ensemble import RandomForestRegressor\n\n#lr_model = LinearRegression()\nrf = RandomForestRegressor(n_estimators = 500, random_state = 42)\n#model = lr_model.fit(X_train,y_train)\nmodel = rf.fit(X_train,y_train)\n#print(model.coef_)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"83c1418b6bd8ac592e7c807111bec0fcc301f519"},"cell_type":"code","source":"prediction = rf.predict(test_df[test_col])\nprint(len(prediction))\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d70c730cac27f02f3ece01cc61341caa09ce1489"},"cell_type":"code","source":"import math\n\ntest_df = test_df.assign(predictedRevenue=prediction)\ngroupby_visitorId = test_df.groupby('fullVisitorId').sum()\n\ndef calculate_log(revenue):\n    return math.log10(abs(revenue) + 1)\n\npredicted_df = groupby_visitorId['predictedRevenue'].apply(calculate_log)\npredicted_df_new = pd.DataFrame({'fullVisitorId' : list(predicted_df.keys()),'PredictedLogRevenue' : predicted_df.values})\npredicted_df_new.to_csv('output_pred.csv',index=False)\n","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}