{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport pandas as pd\nimport matplotlib.pyplot as plt\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nimport os\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cc43090ae50c34abd593b5ed7e6a4718e78567b6"},"cell_type":"markdown","source":"# Loading Training & Historical Data"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"train = pd.read_csv(\"../input/train.csv\")\ntest = pd.read_csv(\"../input/test.csv\")\nhistorical= pd.read_csv(\"../input/historical_transactions.csv\")\nmerchant =  pd.read_csv(\"../input/merchants.csv\")\nnew_merchant =  pd.read_csv(\"../input/new_merchant_transactions.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cab1501578aa259da54241fa4838530b010a4529"},"cell_type":"code","source":"print(historical.merchant_id.value_counts()[:10])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"aefabda260c27ce85185f1f07c75fb3e2245a9a1"},"cell_type":"markdown","source":"# Check first merchant who has the most client"},{"metadata":{"trusted":true,"_uuid":"9038d47cbae48351637162da2ac93a184c426a42"},"cell_type":"code","source":"#best merchant_id\ntmp = historical[historical.merchant_id == 'M_ID_00a6ca8a8a']\n#card_id from best merchandisor\ntmp.card_id.value_counts()\ntmp11 = tmp[tmp.card_id == 'C_ID_5846bc1b5b']\ntrain.card_id \nhistorical.head()\n#train.card_id.str.slice()\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cfc900408dd8197f4ffcbd03de0182f37e76848d"},"cell_type":"markdown","source":"## feature1  <> category 2\n## feature2 <> category 3\n## feature3 <> category 1"},{"metadata":{"trusted":true,"_uuid":"3372bbddc604c0417c03df3b00422677605b2f1f"},"cell_type":"code","source":"print(train.feature_1.value_counts())\nprint(train.feature_2.value_counts())\nprint(train.feature_3.value_counts())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"5f985e7399f3f6ff8dbbab17e6a7927f17947e88"},"cell_type":"code","source":"print(pd.crosstab(historical.category_1,historical.category_3).apply(lambda r: r/r.sum(), axis=1))\nprint(pd.crosstab(historical.category_2,historical.category_3).apply(lambda r: r/r.sum(), axis=1))\nprint(pd.crosstab(historical.category_1,historical.category_2).apply(lambda r: r/r.sum(), axis=1))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f8dc32add670a28477c8af3830a76074b90d0ef9"},"cell_type":"code","source":"#best count buyers\n\nfor i,idx in enumerate(historical.card_id.value_counts()[:10].index):\n    if i == 0 :\n        most10 = historical[historical.card_id ==idx]\n    else:\n        most10 = most10.append(historical[historical.card_id ==idx])\n#check if there is specific city who has power buyers        \nmost10.city_id.value_counts()[:10]        \n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"db81ba9c7f5e197594bf17473336f89f303a8cc0"},"cell_type":"code","source":"historical.city_id.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"24241c40d46c89d2a43476e6fdf697b8d5e987a7"},"cell_type":"code","source":"#fisrt of all lets check first power buyer\nmost1 = historical[historical.card_id =='C_ID_3d3dfdc692']\nprint(most1.city_id.value_counts()) \nprint(most1.columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ef7ac2702234bd64106fdda6b1e50d588394a8ce"},"cell_type":"code","source":"import datetime\nmost1.index = most1.purchase_date\nmost1.index = pd.to_datetime(most1.index, format='%Y-%m-%d %H:%M:%S.%f')\nmost1 = most1.sort_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6bfc0f38e9d2eedc5543d027f20501daec140ce3"},"cell_type":"code","source":"most1['month_lag'].tail()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"27c1b4c213e2ded7b357c39fc367ee6a0e997ab7"},"cell_type":"code","source":"most1[['month_lag','purchase_amount']].groupby(by='month_lag').mean().plot()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bf487451b4e86fc9e2f9ae65417476b739b606f7"},"cell_type":"code","source":"most1[['month_lag','purchase_amount']].groupby(by='month_lag').count().plot()\n    \n\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"32aa8d67437a904694197f03f67c370035e533cc"},"cell_type":"code","source":"#d = pd.date_range(start=most1.index.min().date(), end=most1.index.max().date(), freq='M').tolist()   \nd = most1.index.strftime('%Y-%m').unique()\nfor month in d:\n    plt.scatter(x=most1[month].month_lag,y=most1[month].merchant_category_id)\n    #most1[month]['merchant_category_id'].plot()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7701f1ae4f8d94b91dd7193f717245b651c1901a"},"cell_type":"code","source":"\n\n#d = pd.date_range(start=most1.index.min().date(), end=most1.index.max().date(), freq='M').tolist()   \nd = most1.index.strftime('%Y-%m').unique()\nfor month in d:\n    plt.scatter(x=most1[month].month_lag,y=most1[month].installments)\n    #most1[month]['merchant_category_id'].plot()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1c5f52661ad2259c0d5633a3779fd128f02f085a"},"cell_type":"code","source":"print(len(historical.card_id.unique()))\nprint(len(train.card_id.unique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"876d176173bf57923acd99505eefaaf8fe4b0fef"},"cell_type":"code","source":"#card_id that can be used for modeling\ntrain_card_id = set(train.card_id.unique()).intersection(set(historical.card_id.unique())) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ac188c04f0bf21254b4d638f8dd5b3e0b9e629b0"},"cell_type":"code","source":"test_card_id = set(historical.card_id.unique()).intersection(set(test.card_id.unique())) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d93a370f712af11f6f620b02bdec264e43498b83"},"cell_type":"code","source":"train_historical = historical[historical.card_id == train_card_id]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6b3743b1adc3ca013bcb23978bd1f2eaff120f6e"},"cell_type":"code","source":"train_history = historical[historical.card_id.isin(list(train.index))]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d604416ae433ca3b08e76953a0811343d977b1d0"},"cell_type":"code","source":"a = historical[historical.card_id =='C_ID_0ed4255e62' ]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3048ac74399056b429bb45e6b80f30aecbdb453e"},"cell_type":"code","source":"print(a.category_1.value_counts())\nprint(a.category_2.value_counts())\nprint(a.category_3.value_counts())\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"349de95b1c85dba182a98521411958dbe48af058"},"cell_type":"code","source":"train[train.card_id == 'C_ID_0ed4255e62']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5bfdc0fa10ad81344014953d959586301c0ededa"},"cell_type":"code","source":"train_merged = train.merge(train_history,on=\"card_id\",how = \"outer\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"317e2e9e49f22b5843f9c3749cb5d151fb917dd3"},"cell_type":"code","source":"pd.crosstab(train_merged.feature_3,train_merged.category_1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dee9270db1a077f66ccf2cd8b20d8d4c62ea8b80"},"cell_type":"code","source":"pd.crosstab(train_merged.feature_2,train_merged.category_3)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"623c18b64c52c524bb4490170576847dd2c43a69"},"cell_type":"code","source":"merchant.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"857db81dc9767a15b90f5aa7a60ac98fbb6c6b18"},"cell_type":"code","source":"merchant.loc[(merchant.merchant_id.value_counts() == 1).values]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0c29c42679ee2116d91d272c5071be25b2d4f088"},"cell_type":"code","source":"train_merchant_id =  set(train_merged.merchant_id.unique()).intersection(set(merchants.merchant_id.unique())) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"deab407a577d7304987862fda0088c0f79c45959"},"cell_type":"code","source":"historical[historical.card_id == 'C_ID_a4e600deef']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a752b781ddc10ae55783a3c9a5010098fe7358a6"},"cell_type":"code","source":"new_merchant.head(30)\nnew_merchant.month_lag.value_counts()\nhistorical.merchant_id.value_counts()\nhistorical.merchant_id.value_counts()[:10].index\nfor i,idx in enumerate(historical.merchant_id.value_counts()[:10].index):\n    if i == 0 :\n        most_merchant10 = historical[historical.merchant_id ==idx]\n    else:\n        most_merchant10 = most_merchant10.append(historical[historical.merchant_id ==idx])\npd.crosstab(most_merchant10.merchant_id,most_merchant10.month_lag)\npd.crosstab(most1.merchant_id,most1.month_lag)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"752fbc154249e66deaeb81fcedbaf95f9397ecbe"},"cell_type":"code","source":"historical.\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}