{"cells":[{"metadata":{"trusted":true,"_uuid":"59bca02f231e846686e776ad9b99db39cba004ee"},"cell_type":"code","source":"import pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n","execution_count":1,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8202ae1594957110445204e967ed85092c7edfd1"},"cell_type":"code","source":"plt.figure(figsize=(20,20))","execution_count":2,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2909394b4f4e8a25bc823711596725303846107b"},"cell_type":"code","source":"trainsample=pd.read_csv('../input/train_sample.csv')","execution_count":3,"outputs":[]},{"metadata":{"_uuid":"94973f881a0ff7626240b3f30f783959f2e2f008"},"cell_type":"markdown","source":"### Train Sample Shape "},{"metadata":{"trusted":false,"_uuid":"1d24157fa648259567b32f1c38141c204e03274c"},"cell_type":"code","source":"trainsample.shape","execution_count":4,"outputs":[]},{"metadata":{"_uuid":"e292ddbb7f4393acf8303e82b53f300c0b1ba967"},"cell_type":"markdown","source":"### Train Sample Feature and Types\nis_attributed is the target variable"},{"metadata":{"trusted":false,"_uuid":"9e29fc9072e0091f76154e44276e214e1b0b8d0e"},"cell_type":"code","source":"trainsample.dtypes","execution_count":5,"outputs":[]},{"metadata":{"_uuid":"fc8b1add0a79433d840489fcb4a3b5c87e5b9e19"},"cell_type":"markdown","source":"### Preprocessing changing data types"},{"metadata":{"trusted":false,"_uuid":"1da5a50a91c20513947e8a083c8265509b061ef8"},"cell_type":"code","source":"trainsample.is_attributed=trainsample.is_attributed.astype('object')\ntrainsample.app=trainsample.app.astype('object')\ntrainsample.os=trainsample.os.astype('object')\ntrainsample.device=trainsample.device.astype('object')\ntrainsample.channel=trainsample.channel.astype('object')\n\ntrainsample.click_time=pd.to_datetime(trainsample['click_time'],format='%Y-%m-%d %H:%M:%S')\ntrainsample.attributed_time=pd.to_datetime(trainsample['attributed_time'],format='%Y-%m-%d %H:%M:%S')\n\n","execution_count":6,"outputs":[]},{"metadata":{"_uuid":"1fc83e563a89fadb601fec89ac62d1f3dda4ec9c"},"cell_type":"markdown","source":"### Missing Values  \nAttributed_Time seems to have a lot of missing values"},{"metadata":{"trusted":false,"_uuid":"2ea5dbb61c4a3ca9f188332517b428f1c0c1113c"},"cell_type":"code","source":"trainsample.isnull().sum()","execution_count":7,"outputs":[]},{"metadata":{"_uuid":"283606ea22a46cab7d222fa161cdd97f481eba24"},"cell_type":"markdown","source":"### Target Variable Count's\nTotally imbalanced dataset"},{"metadata":{"trusted":false,"_uuid":"53393fd5b93a386effa9680a61ad70fed67bd1c9"},"cell_type":"code","source":"trainsample.is_attributed.value_counts()","execution_count":8,"outputs":[]},{"metadata":{"_uuid":"713afc4b00f87d2c2a51480fd586e9cd1f6f75c8"},"cell_type":"markdown","source":"### Grouping and Counts of independent variables"},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"49d71711742885e7b55d28494853e43b5858a823"},"cell_type":"code","source":"def GroupByColumns(columns):\n    groupCol = trainsample.groupby(columns)\\\n                    .size()\\\n                    .sort_values(ascending=False)\\\n                    .reset_index()\n    return groupCol","execution_count":9,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"b0e225e4f5ae7a88aa5274e42077ab8a84432305"},"cell_type":"code","source":"columns=['app','device','os']","execution_count":10,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"4d1f465e7c3cd69f163db94d8c151a31b55e34bb"},"cell_type":"code","source":"GroupByColumns(columns)","execution_count":11,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"a410fef93b012b1d0023cb9fd956aaf77eb9a348"},"cell_type":"code","source":"columns=['app','device','os','is_attributed']","execution_count":12,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"3ed44ea6a991b0b87c65e04bd77a9c23059d16a3"},"cell_type":"code","source":"GroupByColumns(columns)","execution_count":13,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"df88de8822e052806460b3dcb8e632b9fc2a66e7"},"cell_type":"code","source":"columns=['app','device','os','is_attributed']","execution_count":14,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"991de5118f9769c575abae3571e0be63ea4051f3"},"cell_type":"code","source":"groupCol = trainsample.groupby(columns)\\\n                    .size()\\\n                    .sort_values(ascending=False)\\\n                    .reset_index()","execution_count":15,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"2e11dffc630e23390f7e8666413be021d895ff0e"},"cell_type":"code","source":"groupCol","execution_count":16,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"5f446b1741862a2c8b69d062b4a62c8b038dbac8"},"cell_type":"code","source":"AttributedDF= trainsample[trainsample.is_attributed==1]","execution_count":17,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"e6831c20c257ef636005774d4beba873e832f1c9"},"cell_type":"code","source":"timeDiffList= list()\ni= 0;\ndef timeBtwDwnld(df):\n    for i in range(0, len(df)):\n        timeDiffList.append((df.iloc[i]['attributed_time']-df.iloc[i]['click_time']).seconds)\n    return timeDiffList\n    ","execution_count":18,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"a9e0e483999035e9691b012cefc11bd360fc5c2b"},"cell_type":"code","source":"AttributedDF['timediff']=timeBtwDwnld(AttributedDF)","execution_count":19,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"c49942f87795bf6092234192c83988c40fe1ad08"},"cell_type":"code","source":"plt.hist(AttributedDF['timediff'],bins=30)\nplt.show()","execution_count":20,"outputs":[]},{"metadata":{"_uuid":"38980e7aa69e1e021d4f0a998670a8743ac80f57"},"cell_type":"markdown","source":"### Some clicks have turned into downloads after a day also"},{"metadata":{"trusted":false,"_uuid":"d9d67e2aea82518c86cd4bb19c82da4785496e9e"},"cell_type":"code","source":"AttributedDF[AttributedDF.timediff>3600].shape","execution_count":21,"outputs":[]},{"metadata":{"_uuid":"74c9e2c9b94285aa07928654689bd9a9dc39ad60"},"cell_type":"markdown","source":"### Plot's "},{"metadata":{"trusted":false,"_uuid":"32b1a83fc60f07d71836d7881e855b047c4c74fa"},"cell_type":"code","source":"appseries=trainsample.app.value_counts().nlargest(20)\nplt.figure(figsize=(20,20))","execution_count":22,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"dc75cd8421e6cacc4b0a4afb6650c936e5d218f6"},"cell_type":"code","source":"appseries.plot.bar()\nplt.show()","execution_count":23,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"a5818fe41a7f69d45e7a32c544fdca95a59bf60c"},"cell_type":"code","source":"osseries=trainsample.os.value_counts().nlargest(20)\nplt.figure(figsize=(20,20))","execution_count":24,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"06dce3b8b81d522baa2d9bbb45079807eb84d076"},"cell_type":"code","source":"osseries.plot.bar()\nplt.show()","execution_count":25,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"eb9f07bf96ac0781a170e63e1ce07ea312b4d123"},"cell_type":"code","source":"deviceseries=trainsample.device.value_counts().nlargest(20)\nplt.figure(figsize=(20,20))","execution_count":26,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"1bb6a1021fa35c273d4c9b8ab783ecaf2677b955"},"cell_type":"code","source":"deviceseries.plot.bar()\nplt.show()","execution_count":27,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"8b6b93a89687b363297917f299debba7590b6cd8"},"cell_type":"code","source":"channelseries=trainsample.channel.value_counts().nlargest(20)\nplt.figure(figsize=(20,20))","execution_count":28,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"7e31ce13feb29b9a3dce468e9dccaf962446c386"},"cell_type":"code","source":"channelseries.plot.bar()\nplt.show()","execution_count":29,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"4441579b40553bee6d28bcb0224c9d2c1cc1a597"},"cell_type":"code","source":"times = pd.DatetimeIndex(trainsample.click_time)\nplt.figure(figsize=(20,20))","execution_count":30,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"7a764cb907e2be81d8a8edea74341fb8cb582a95"},"cell_type":"code","source":"grouped = trainsample.groupby([times.day])['ip'].count()","execution_count":31,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"51019a2b38f38c7260e4b3bfa794ff7eb7086f1d"},"cell_type":"code","source":"grouped.plot.bar()\nplt.show()","execution_count":32,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"076a0fd524bbe7fa92ec2f6685dbae2428ea67b6"},"cell_type":"code","source":"grouped = trainsample.groupby([times.hour])['ip'].count()\nplt.figure(figsize=(20,20))","execution_count":33,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"324b0e6bd800109ce5fcf1407066314c11d052ca"},"cell_type":"code","source":"grouped.plot.bar()\nplt.show()","execution_count":34,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"5d4e628d8261e6bd8ed6414902b02c748eb1b613"},"cell_type":"code","source":"times = pd.DatetimeIndex(trainsample.attributed_time)","execution_count":35,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"5da63b9347e0d5f0fb3779138ab2f1864e4e38c2"},"cell_type":"code","source":"grouped = trainsample.groupby([times.day])['ip'].count()\nplt.figure(figsize=(20,20))","execution_count":36,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"12fba4eebacbe39427508ef01253aac0b127bde8"},"cell_type":"code","source":"grouped.plot.bar()\nplt.show()","execution_count":37,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"0339b402d10c9a84657984e795872c90ca555cb2"},"cell_type":"code","source":"grouped = trainsample.groupby([times.hour])['ip'].count()\nplt.figure(figsize=(20,20))","execution_count":38,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"581d42e34b15b4ea9b1f57c365dfeef849c536b0"},"cell_type":"code","source":"grouped.plot.bar()\nplt.show()","execution_count":39,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"4830048991335b3008b5f8c33bcb7a0829226726"},"cell_type":"code","source":"grouped = trainsample.groupby([times.minute])['ip'].count()","execution_count":40,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"568fbd333ba029b947171324920c64402e397881"},"cell_type":"code","source":"plt.figure(figsize=(20,20))","execution_count":41,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"8e837e5dad6a2bce057cd3f3efce228131922bd2"},"cell_type":"code","source":"grouped.plot.bar()\nplt.show()","execution_count":42,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"e3b646a271104e5f79f7c9bbfc487b5e6a323a54"},"cell_type":"code","source":"from imblearn.over_sampling import RandomOverSampler\n\nros = RandomOverSampler()\n\norig_x_data=trainsample[['ip','os','device','app','channel']]\norig_y_data=trainsample[['is_attributed']]\n\nx_oversampled, y_oversampled = ros.fit_sample(orig_x_data, orig_y_data)","execution_count":43,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"fa839bfde3429c007d7875cf4a571ff04b4c6fd4"},"cell_type":"code","source":"from collections import Counter\nprint(sorted(Counter(y_oversampled).items()))\n","execution_count":44,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"aea542abdf736c998e9afa830d0c40b2e1a2bc69"},"cell_type":"code","source":"trainsample['clicktimemins'] =trainsample.click_time.map(lambda t: t.strftime('%Y-%m-%d %H:%M'))","execution_count":45,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"47e7b0b5ddf609d0a133c0bd145b0c20e2b65757"},"cell_type":"code","source":"clickcountbymin=trainsample.groupby(['clicktimemins'])['ip'].count()\nplt.figure(figsize=(15,15))","execution_count":46,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"9d0db7ae2bb3e129719fb1ae450c3decf4b1f4da"},"cell_type":"code","source":"clickcountbymin.plot()\nplt.show()","execution_count":47,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"b3f92bd8f4c0ecccf7599fb410bc081c01b9ae97"},"cell_type":"code","source":"from pandas import Series\nfrom pandas import DataFrame\nfrom pandas import Grouper\nimport numpy as np","execution_count":48,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"fd7a9ce9e1f7ddf0b0806599a7c2e0ca56dcd10e"},"cell_type":"code","source":"series=Series(clickcountbymin)\nn = 480","execution_count":49,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"45e4b20f7ff3983bafa72e9f8a8f6240ae6f2e99"},"cell_type":"code","source":"newdf=trainsample.set_index(['click_time'])","execution_count":50,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"daa1550eb39c309e7585c69f473a4cbef59bb2e5"},"cell_type":"code","source":"dtresampler=newdf['ip'].resample('60s')","execution_count":51,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"f7ce36cf77494857892588c8d282879e57942b47"},"cell_type":"code","source":"trainsample['clickdate'] =trainsample.click_time.map(lambda t: t.strftime('%Y-%m-%d'))","execution_count":52,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"9b955b33e91aec0bfc1074b4e8bc3fad0ddddc40"},"cell_type":"code","source":"trainsample['timeofclick'] =trainsample.click_time.map(lambda t: t.strftime('%H:%M:%S'))","execution_count":53,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"a1b3895fe73ed75ffbdf760ba7c8f3e47c7ac0eb"},"cell_type":"code","source":"uniqdates=trainsample['clickdate'].unique()","execution_count":54,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"5c7e7430b7e3311106a4d4005299fad1d0affd29"},"cell_type":"code","source":"df6th=trainsample[trainsample.clickdate=='2017-11-06']\ndf6th=df6th.set_index(['click_time'])","execution_count":55,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"db2e2c927d9e0fc98e50479ab84327dc3b55c9a8"},"cell_type":"code","source":"df7th=trainsample[trainsample.clickdate=='2017-11-07']\ndf7th=df7th.set_index(['click_time'])","execution_count":56,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"2d1de05d05760a635d38b1be53f0c99ef77e44b9"},"cell_type":"code","source":"df8th=trainsample[trainsample.clickdate=='2017-11-08']\ndf8th=df8th.set_index(['click_time'])","execution_count":57,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"256aff7674029c9e44e8dcf6e105522aa096d9d0"},"cell_type":"code","source":"df9th=trainsample[trainsample.clickdate=='2017-11-09']\ndf9th=df9th.set_index(['click_time'])","execution_count":58,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"742701ff141d41cb0582c8f1ccecadbbc07a46c0"},"cell_type":"code","source":"\nfig,ax=plt.subplots(4,1,sharex='row',sharey=False,squeeze=False)\nplt.figure(figsize=(20,20))","execution_count":59,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"053f043df96d4fe0271a9b5339768345fb292da7"},"cell_type":"code","source":"ax[0][0]","execution_count":60,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"c60f0519756fbd06ec55fd5a7a05deb0a30e973e"},"cell_type":"code","source":"\nax[0][0].plot(df6th['ip'].resample('60s').count())","execution_count":61,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"59658ee9a765dd29fc24e62c4712eec9a6808024"},"cell_type":"code","source":"\nax[1][0].plot(df7th['ip'].resample('60s').count())","execution_count":62,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"e33fbfed976c3ea0e2ba627c4fc518e9d5df407c"},"cell_type":"code","source":"\nax[2][0].plot(df8th['ip'].resample('60s').count())","execution_count":63,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"0ad14e6d7f4269192b1ac25294d02bcd10af88c1"},"cell_type":"code","source":"\nax[3][0].plot(df9th['ip'].resample('60s').count())","execution_count":64,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"c1e5caaad71ab049b0e769485f92f004718abd6a"},"cell_type":"code","source":"plt.show()","execution_count":65,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"df1a456908ab5ee49710bd6097fa209a9a23df18"},"cell_type":"code","source":"columns=['ip','app','device','os']","execution_count":66,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"ce3c0bc9f42ea6ea98d5eca1dd8b8dd2405744fc"},"cell_type":"code","source":"GroupByColumns(columns)","execution_count":67,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"24863624d6ba463a9cdd43facc5b48addb7811f2"},"cell_type":"code","source":"#extract hour as a feature\ntrainsample['click_hour']=trainsample['click_time'].dt.hour\ntrainsample.is_attributed=trainsample.is_attributed.astype('int')","execution_count":68,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"112e2b434f69fadb36a539b25b02551bd8a1cce5"},"cell_type":"code","source":"#thanks to yuliagm for the conversation rate  idea \nproportion = trainsample[['ip', 'is_attributed']].groupby('ip', as_index=False).mean().sort_values('is_attributed', ascending=False)\ncounts = trainsample[['ip', 'is_attributed']].groupby('ip', as_index=False).count().sort_values('is_attributed', ascending=False)\nmerge = counts.merge(proportion, on='ip', how='left')\nmerge.columns = ['ip', 'click_count', 'prop_downloaded']\n\nax = merge[:300].plot(secondary_y='prop_downloaded')\nplt.title('Conversion Rates  300 Most Popular IPs')\nax.set(ylabel='Click Count')\nplt.ylabel('Proportion Downloaded')\nplt.show()\n\nprint('Counversion Rates over Counts of Most Popular IPs')\nprint(merge[:30])","execution_count":69,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"410933b8defc9cac17fda8daa9ef6423e8445338"},"cell_type":"code","source":"trainsample['click_hour']=trainsample['click_time'].dt.hour\nsns.barplot('click_hour', 'is_attributed', data=trainsample)\nplt.title('HOURLY CONVERSION RATIO');\nplt.ylabel('Converted Ratio');","execution_count":70,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"4f8f16cc68f9736a14c1d17d4f18e9eed5cb8d1a"},"cell_type":"code","source":"plt.show()","execution_count":71,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"8c6f99936177f42abbabde78e12130f7d519a6e4"},"cell_type":"code","source":"proportion = trainsample[['app', 'is_attributed']].groupby('app', as_index=False).mean().sort_values('is_attributed', ascending=False)\ncounts = trainsample[['app', 'is_attributed']].groupby('app', as_index=False).count().sort_values('is_attributed', ascending=False)\nmerge = counts.merge(proportion, on='app', how='left')\nmerge.columns = ['app', 'click_count', 'prop_downloaded']\n\nax = merge[:100].plot(secondary_y='prop_downloaded')\nplt.title('Conversion Rates over Counts of 100 Most Popular Apps')\nax.set(ylabel='Click Count')\nplt.ylabel('Proportion Downloaded')\nplt.show()\n\nprint('Counversion Rates over Counts of Most Popular Apps')\nprint(merge[:20])","execution_count":72,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"47b9b4aafe48ae91b20937fb01e82b5495c9fa49"},"cell_type":"code","source":"proportion = trainsample[['os', 'is_attributed']].groupby('os', as_index=False).mean().sort_values('is_attributed', ascending=False)\ncounts = trainsample[['os', 'is_attributed']].groupby('os', as_index=False).count().sort_values('is_attributed', ascending=False)\nmerge = counts.merge(proportion, on='os', how='left')\nmerge.columns = ['os', 'click_count', 'prop_downloaded']\n\nax = merge[:100].plot(secondary_y='prop_downloaded')\nplt.title('Conversion Rates ')\nax.set(ylabel='Click Count')\nplt.ylabel('Proportion Downloaded')\nplt.show()\n\nprint('Counversion Rates over Counts of Most Popular os')\nprint(merge[:20])","execution_count":73,"outputs":[]},{"metadata":{"collapsed":true,"trusted":false,"_uuid":"7686548f56d6e6f2aafc3a6297e1485cf003bca8"},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.5.2"}},"nbformat":4,"nbformat_minor":1}