{"cells":[{"metadata":{"_uuid":"330f40f4dcef2f0a2f27b67e5e5bf700f18b6dad"},"cell_type":"markdown","source":"The purpose of this kernel is to visualize the sample data to get hopefully get some inspiration for feature engineering.\n\nSummary:\nThough the sample is relatively smaller than the entire train dataset, but it can be observed that the click count and average attributed rate for other click count for each variable matters.\nSpecifically, the attribution rate is positively correlated to the average attributed rate for other click count for that variable value, while negatively correlated to the click count for that variable value\nFor click time, the day of the week, day of the year and the hour matt"},{"metadata":{"_uuid":"c6c0def069833af3bd249aa3647c8eda542af45e"},"cell_type":"markdown","source":"## 1. Load"},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"bb212cb92ccefba3243015f3f26d9e6a54436553"},"cell_type":"code","source":"# 1.1 Load Library--------------------------------------------------------------\n# data analysis and wrangling\nimport pandas as pd\nimport numpy as np\nfrom tqdm import tqdm\nimport gc\n\n#ignore warnings\nimport warnings\nwarnings.filterwarnings('ignore')\n\npd.options.mode.chained_assignment = None\npd.options.display.max_columns = 999","execution_count":2,"outputs":[]},{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","trusted":false},"cell_type":"code","source":"# 1.2 Load data--------------------------------------------------------------\ndata_dir = '../input/'\ndtypes = {\n    'click_id'      : 'uint32',\n    'ip'            : 'uint32',\n    'app'           : 'uint16',\n    'device'        : 'uint16',\n    'os'            : 'uint16',\n    'channel'       : 'uint16',\n    'is_attributed' : 'uint8'}\ntrain_df = pd.read_csv(data_dir + 'train.csv', skiprows = range(500000, 184403890), dtype=dtypes) # 184,903,891 in total\nprint('train_df shape: ' + str( train_df.shape))","execution_count":3,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","collapsed":true},"cell_type":"markdown","source":"## 2. Data exploration and visualization"},{"metadata":{"trusted":false,"_uuid":"804662596b4a8ef8f8a8b4a6f5fd1f965cee2440"},"cell_type":"code","source":"#Visualization\nimport matplotlib as mpl\nimport matplotlib.pyplot as plt\nimport matplotlib.pylab as pylab\nimport matplotlib.patches as mpatches\nimport seaborn as sns\n\nimport plotly.offline as py\nimport cufflinks as cf\ncf.set_config_file(offline=True, world_readable=True, theme='ggplot')\n\n#Configure Visualization Defaults\n%matplotlib inline\nmpl.style.use('ggplot')\nsns.set_style('white')\n\ndef get_barplot(df, factor):\n    rate_df = df.groupby(factor)['is_attributed'].mean()\n    print(rate_df.iplot(kind='bar', xTitle=factor, title='attributed rate'))\n    count_df = df.groupby(factor)['is_attributed'].count()\n    print(count_df.iplot(kind='bar', xTitle=factor, title='frequency count'))","execution_count":4,"outputs":[]},{"metadata":{"_uuid":"d81a6480122da9da5739d2619f8937dedaaf7442"},"cell_type":"markdown","source":"### 2a. ip (click count and attributed rate)"},{"metadata":{"trusted":false,"_uuid":"119c5a9112013a11b4d680cc45ea0d83f5170f72"},"cell_type":"code","source":"agg = train_df.groupby('ip').agg(dict(is_attributed = 'sum', app = 'count')).reset_index()\nagg = agg.rename(columns = dict(is_attributed = 'ip_attribute_sum', app = 'ip_click_count'))\ntrain_df = train_df.merge(agg, on = 'ip')\ntrain_df['ip_click_count'] / 50\ntrain_df['ip_click_count_fix'] = (round(train_df['ip_click_count'] / 1)).clip(0,30).astype(int)\nget_barplot(train_df, 'ip_click_count_fix')\ndel agg\ngc.collect()","execution_count":5,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"2d4bfffeb63309c450a621649ba84be49d1635df"},"cell_type":"code","source":"# attribute rate for the ip for other clicks\ntrain_df['ip_other_click_attribute_rate'] = (train_df['ip_attribute_sum'] - train_df['is_attributed']) / (train_df['ip_click_count'] - 1)\nattribute_rate_mean = train_df['ip_other_click_attribute_rate'].mean()\ntrain_df['ip_other_click_attribute_rate'] = train_df.apply(lambda r: \\\n    r['ip_other_click_attribute_rate'] if (r['ip_click_count']> 10) \\\n    else attribute_rate_mean, axis = 1)\ntrain_df['ip_other_click_attribute_rate'] = train_df['ip_other_click_attribute_rate'].fillna(attribute_rate_mean)\n\ntrain_df['ip_other_click_attribute_rate_fix'] = round(train_df['ip_other_click_attribute_rate'], 2).clip(0,0.1)\nget_barplot(train_df, 'ip_other_click_attribute_rate_fix')","execution_count":6,"outputs":[]},{"metadata":{"_uuid":"9f887e689df450b736acc9a09467bf03027c35af"},"cell_type":"markdown","source":"### 2a. app (click count and attributed rate)"},{"metadata":{"trusted":false,"_uuid":"c84928749dde6f0da185ca5be58b4c2d1f875121"},"cell_type":"code","source":"agg = train_df.groupby('app').agg(dict(is_attributed = 'sum', ip = 'count')).reset_index()\nagg = agg.rename(columns = dict(is_attributed = 'app_attribute_sum', ip = 'app_click_count'))\ntrain_df = train_df.merge(agg, on = 'app')\ntrain_df['app_click_count_fix'] = (round(train_df['app_click_count'] / 2000)).clip(0,30).astype(int)\nget_barplot(train_df, 'app_click_count_fix')\n\ndel agg\ngc.collect()","execution_count":11,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"9f5d4a2a7d37eda19ad2db11fe02eb65039f41ba"},"cell_type":"code","source":"# attribute rate for the app for other clicks\ntrain_df['app_other_click_attribute_rate'] = (train_df['app_attribute_sum'] - train_df['is_attributed']) / (train_df['app_click_count'] - 1)\nattribute_rate_mean = train_df['app_other_click_attribute_rate'].mean()\ntrain_df['app_other_click_attribute_rate'] = train_df.apply(lambda r: \\\n    r['app_other_click_attribute_rate'] if (r['app_click_count']> 1000) \\\n    else attribute_rate_mean, axis = 1)\ntrain_df['app_other_click_attribute_rate'] = train_df['app_other_click_attribute_rate'].fillna(attribute_rate_mean)\ntrain_df['app_other_click_attribute_rate_fix'] = round(train_df['app_other_click_attribute_rate'], 4).clip(0,0.0005)\nget_barplot(train_df, 'app_other_click_attribute_rate_fix')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fa3e23128ac823ea76fdd50df38661e08050be8b"},"cell_type":"markdown","source":"### 2c. device (click count and attributed rate)"},{"metadata":{"trusted":false,"_uuid":"440dca9b78e3bdbc3ff358b29375ed2d5c0ee78a"},"cell_type":"code","source":"agg = train_df.groupby('device').agg(dict(is_attributed = 'sum', ip = 'count')).reset_index()\nagg = agg.rename(columns = dict(is_attributed = 'device_attribute_sum', ip = 'device_click_count'))\ntrain_df = train_df.merge(agg, on = 'device')\ntrain_df['device_click_count_fix'] = (round(train_df['device_click_count'] / 5000)).clip(0,10).astype(int)\nget_barplot(train_df, 'device_click_count_fix')\n\ndel agg\ngc.collect()","execution_count":12,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"b9c31c680285da36db8c5d80ef8eeaa4e1c7860b"},"cell_type":"code","source":"train_df['device_other_click_attribute_rate'] = (train_df['device_attribute_sum'] - train_df['is_attributed']) / (train_df['device_click_count'] - 1)\nattribute_rate_mean = train_df['device_other_click_attribute_rate'].mean()\ntrain_df['device_other_click_attribute_rate'] = train_df.apply(lambda r: \\\n    r['device_other_click_attribute_rate'] if (r['device_click_count']> 1000) \\\n    else attribute_rate_mean, axis = 1)\ntrain_df['device_other_click_attribute_rate'] = train_df['device_other_click_attribute_rate'].fillna(attribute_rate_mean)\ntrain_df['device_other_click_attribute_rate_fix'] = round(train_df['device_other_click_attribute_rate'], 4).clip(0,0.001)\nget_barplot(train_df, 'device_other_click_attribute_rate_fix')","execution_count":16,"outputs":[]},{"metadata":{"_uuid":"61dd6689ff2298b0d45e1bb166b17bbcbf33db52"},"cell_type":"markdown","source":"### 2d. os (click count and attributed rate)"},{"metadata":{"trusted":false,"_uuid":"3aa4b842641e5342b592faf01e0074f8c2b0a9f1"},"cell_type":"code","source":"agg = train_df.groupby('os').agg(dict(is_attributed = 'sum', ip = 'count')).reset_index()\nagg = agg.rename(columns = dict(is_attributed = 'os_attribute_sum', ip = 'os_click_count'))\ntrain_df = train_df.merge(agg, on = 'os')\ntrain_df['os_click_count_fix'] = (round(train_df['os_click_count'] / 1000)).clip(0,10).astype(int)\nget_barplot(train_df, 'os_click_count_fix')\n\ndel agg\ngc.collect()","execution_count":17,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"dd3bb3a1e3995d07d6f60364f4d3d4708a22e7f3"},"cell_type":"code","source":"# attribute rate for the os for other clicks\ntrain_df['os_other_click_attribute_rate'] = (train_df['os_attribute_sum'] - train_df['is_attributed']) / (train_df['os_click_count'] - 1)\nattribute_rate_mean = train_df['os_other_click_attribute_rate'].mean()\ntrain_df['os_other_click_attribute_rate'] = train_df.apply(lambda r: \\\n    r['os_other_click_attribute_rate'] if (r['os_click_count']> 1000) \\\n    else attribute_rate_mean, axis = 1)\ntrain_df['os_other_click_attribute_rate'] = train_df['os_other_click_attribute_rate'].fillna(attribute_rate_mean)\ntrain_df['os_other_click_attribute_rate_fix'] = round(train_df['os_other_click_attribute_rate'], 4).clip(0,0.001)\nget_barplot(train_df, 'os_other_click_attribute_rate_fix')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c152240887cb3517cad358479e443a5a5ea7dfed"},"cell_type":"markdown","source":"### 2e. channel (click count and attributed rate)"},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"4449ea6bc2581666dd9df81862faf64c29f3ae7d"},"cell_type":"code","source":"agg = train_df.groupby('channel').agg(dict(is_attributed = 'sum', ip = 'count')).reset_index()\nagg = agg.rename(columns = dict(is_attributed = 'channel_attribute_sum', ip = 'channel_click_count'))\ntrain_df = train_df.merge(agg, on = 'channel')\ntrain_df['channel_click_count_fix'] = (round(train_df['channel_click_count'] / 1000)).clip(0,10).astype(int)\nget_barplot(train_df, 'channel_click_count_fix')\n\ndel agg\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"c6e229bc81f246113ca7381ac4c2c2b2a3b5dec0"},"cell_type":"code","source":"# attribute rate for the channel for other clicks\ntrain_df['channel_other_click_attribute_rate'] = (train_df['channel_attribute_sum'] - train_df['is_attributed']) / (train_df['channel_click_count'] - 1)\nattribute_rate_mean = train_df['channel_other_click_attribute_rate'].mean()\ntrain_df['channel_other_click_attribute_rate'] = train_df.apply(lambda r: \\\n    r['channel_other_click_attribute_rate'] if (r['channel_click_count']> 1000) \\\n    else attribute_rate_mean, axis = 1)\ntrain_df['channel_other_click_attribute_rate'] = train_df['channel_other_click_attribute_rate'].fillna(attribute_rate_mean)\ntrain_df['channel_other_click_attribute_rate_fix'] = round(train_df['channel_other_click_attribute_rate'], 4).clip(0,0.001)\nget_barplot(train_df, 'channel_other_click_attribute_rate_fix')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cdf9c70c95c5404f5d1eb599fa7350b3c182a6fd"},"cell_type":"markdown","source":"### 2f. click_time"},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"9b7b395b66547a5743ae97cf467b389311d348dd"},"cell_type":"code","source":"train_df['attributed_time_fix']= pd.to_datetime(train_df['attributed_time'])\ntrain_df['click_time_fix'] = pd.to_datetime(train_df['click_time'])","execution_count":18,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"6a835d51b2bb6c55ac4297a8d34022e92bde3a49"},"cell_type":"code","source":"train_df['click_time_dayofweek'] = train_df['click_time_fix'].map(lambda x: x.dayofweek).astype(str)\ntrain_df['click_time_dayofyear'] = train_df['click_time_fix'].map(lambda x: x.dayofyear).astype(int)\ntrain_df['click_time_hour'] = train_df['click_time_fix'].map(lambda x: x.hour).astype(int)","execution_count":19,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"b52046a9c63b1ed3792a7af1c70693be7c90f53e"},"cell_type":"code","source":"get_barplot(train_df,'click_time_dayofweek')","execution_count":20,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"79bdc985bd73294dd5a96b3777c235e2ca6ba7a8"},"cell_type":"code","source":"get_barplot(train_df,'click_time_dayofyear')","execution_count":21,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"90b6c9aa75aaac091c3ff9802e124464f0fb6803"},"cell_type":"code","source":"get_barplot(train_df,'click_time_hour')","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.4","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}