{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"scrolled":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 matplotlib.pyplot as plt\nimport seaborn as sns\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":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"data = pd.read_csv('../input/merchants.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"7920010ac880bf7134032936d1fd35fe59f30426"},"cell_type":"code","source":"print(data.shape)\nprint(data.dtypes)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f1c1e3c7195cb0a904e41d639553a671d5d84aa6"},"cell_type":"code","source":"data.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e7aa05b96553f549c0c17cb8a469cdd53c559ab9"},"cell_type":"code","source":"data['merchant_group_id'] = data['merchant_group_id'].astype(object)\ndata['merchant_category_id'] = data['merchant_category_id'].astype(object)\ndata['subsector_id'] = data['subsector_id'].astype(object)\ndata['city_id'] = data['city_id'].astype(object)\ndata['state_id'] = data['state_id'].astype(object)\ndata['category_2'] = data['category_2'].astype(object)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"f5f7515b1d37b10af52b9a901aa3315bd031659a"},"cell_type":"code","source":"print(data.dtypes)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ce9a5654351adc6bda069abdc63821f4203fab8d"},"cell_type":"code","source":"data.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3311bdc6e9a0b6b39f9ad4f4f93960361a352323"},"cell_type":"code","source":"print(data['numerical_1'].describe())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f1f4700474ba426630bb73a5ca10a5fec4f4daba"},"cell_type":"code","source":"print(data['numerical_2'].describe())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d0b5a8fe590bbf8a07fe3c6432c6c847716b9ae0"},"cell_type":"markdown","source":"사실상 1과 2는 비슷한 숫자들, 분포가 제정신이 아니다. 어떤 것을 의미하는거지.."},{"metadata":{"trusted":true,"_uuid":"86144410780b819cf74deeda0d48aa4ea33ed181"},"cell_type":"code","source":"f, ax = plt.subplots(figsize=(10, 8))\nsns.distplot(data['numerical_1'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7b9f45703f184efb5c370c0c5935cd25ed0a8131"},"cell_type":"code","source":"f, ax = plt.subplots(figsize=(8, 6))\nfig = sns.boxplot(data.numerical_1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"38bb836ca24c93788054d0d625a781bf128ee1dc"},"cell_type":"code","source":"f, ax = plt.subplots(figsize=(8, 6))\nfig = sns.boxplot(data.numerical_2)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8e90e05813fc81498407bdf7d047997758fdc490"},"cell_type":"markdown","source":"박스 플랏으로 봤을때에도 대부분의 값이-0.0574 에 고정되어있고 전체 관측치의 아주 적은 부분만 매우 높은 값을 가진다."},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"4d42b8ca9edcded2e23da9bdcb8fbc3302df2c03"},"cell_type":"code","source":"fig, ax = plt.subplots(1, 5, figsize = (16, 6));\ndata['category_1'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='Anonymized_Category_1')\ndata['most_recent_sales_range'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='gold', title='most_recent_sales_range')\ndata['most_recent_purchases_range'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='black', title='most_recent_purchases_range')\ndata['category_4'].value_counts().sort_index().plot(kind='bar', ax=ax[3], color='blue', title='Anonymized_Category_4')\ndata['category_2'].value_counts().sort_index().plot(kind='bar', ax=ax[4], color='red', title='Anonymized_Category_2')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8553ac962a430c7f6cff783bbbff420b2fc5ff44"},"cell_type":"markdown","source":"범주형 변수들 정리/\n1번 그래프의 경우 매우 편향된 분포, 4번 그래프와 5번그래프는 그보다는 분포가 고르나 역시뭔지는...\n2번의 경우는 최근 영업 달의 판매금액 3번은 판매회수(거래 수) 를 범주화한것.\nA ~ E로 갈수록 낮아지는것을 의미.  금액과 거래수이므로 비슷한 분포를 보이며, 하위 그룹이 훨씬 더 많은 피라미드형 분포를 가짐"},{"metadata":{"trusted":true,"_uuid":"03c9473a97061bdc0dc4a3ebde57f7e6c1ad5891"},"cell_type":"code","source":"print(data.dtypes)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b95799c482e1035d3cc5da02ffaf773e8f2b392f"},"cell_type":"code","source":"data.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2661cbe97b9a7310823b2e21cf7eb1d79ff941b3"},"cell_type":"code","source":"#### 3 month, 6 month, 12 month Analysis\n#1 Sales\nprint(data['avg_sales_lag3'].describe())\nprint(data['avg_sales_lag6'].describe())\nprint(data['avg_sales_lag12'].describe())\n#avg_sales_lag3\n#avg_sales_lag6\n#avg_sales_lag12\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"trusted":true,"_uuid":"27c00f9d9a7f4ce8fca17b11e614ffdece905531"},"cell_type":"code","source":"data[data.avg_sales_lag3.isnull()==1].head(15)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"41b2328c47b25c741aaf36d158abe48c1f4e84b9"},"cell_type":"code","source":"fig, ax = plt.subplots(1, 3, figsize = (16, 6));\ndata['avg_sales_lag3'].value_counts().sort_index().plot(kind='hist', ax=ax[0], color='teal', title='3_Month');\ndata['avg_sales_lag6'].value_counts().sort_index().plot(kind='hist', ax=ax[1], color='brown', title='6_Month');\ndata['avg_sales_lag12'].value_counts().sort_index().plot(kind='hist', ax=ax[2], color='blue', title='12_Month');\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ee7b988811f9ef17a484dcdceb31f53b4fefad1c"},"cell_type":"markdown","source":"지난 3, 6 12 개월간의 평균 수입을 가장 최근 달로 나눈 값이다.\n\n*이것은 즉 과거 n 개월 대비 최근의 성과가 어떠한지를 나타내는 변수라고 할 수 있다.\n양수이고, 값이 낮으면 낮을 수록 과거에 비해 최근 성과가 좋은 것을 의미한다.\n\n일반적으로는 지난 평균기간 동안의 수입들의 변화가 크지 않다면 1에 가까울 것이다. \n\n실제로 3, 6, 12 개월 모두 중위값이 1에 가까운 것을 위에서 확인할 수 있다. \n하지만 최대 8000에 해당하는 아웃라이어 종종 존재하는데 이러한 변수들에 대한 처리가 필요해 보인다."},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"917a80b28e457f178981a4c8917e7a7358a0f8ff"},"cell_type":"code","source":"print(data[data.avg_sales_lag3>10].shape[0])\nprint(data[data.avg_sales_lag6>10].shape[0])\nprint(data[data.avg_sales_lag12>10].shape[0])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"348cf8ad4527662caf7c013d922429593266efda"},"cell_type":"code","source":"# 최근\nprint((data[(data.avg_sales_lag3>10)&(data.most_recent_sales_range!=\"E\")].shape)[0])\nprint((data[(data.avg_sales_lag6>10)&(data.most_recent_sales_range!=\"E\")].shape)[0])\nprint((data[(data.avg_sales_lag12>10)&(data.most_recent_sales_range!=\"E\")].shape)[0])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d8faf3c27fa8cb0ca0c53265f0a2db1525774081"},"cell_type":"code","source":"f, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='most_recent_sales_range', y=\"avg_sales_lag3\", data=data)\nplt.xlabel(\"most_recent_sales_range\")\nplt.ylabel(\"avg_sales_lag3\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e7752e9b5efa73f33b7c68a5d77b1bb1dbb093d2"},"cell_type":"code","source":"f, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='most_recent_sales_range', y=\"avg_sales_lag6\", data=data)\nplt.xlabel(\"most_recent_sales_range\")\nplt.ylabel(\"avg_sales_lag3\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"650656588b6f2845f2e703e5afa8e02a7435511a"},"cell_type":"code","source":"f, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='most_recent_sales_range', y=\"avg_sales_lag12\", data=data)\nplt.xlabel(\"most_recent_sales_range\")\nplt.ylabel(\"avg_sales_lag3\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cdfcf67d559109622fdd750efe0333050d010e9e"},"cell_type":"markdown","source":"\n > 위의 그래프를 보면 알 수 있듯이 비정상적으로 높은 수치를 갖고 있는 \"avg_sales_lag\"변수들은 지난달의 판매량이 낮은 분위에 속해있는 경우에만 해당되는 것을 확인할 수 있다. 지난달 판매액이 A,B,C,D 분위에 해당하는 관측값의 경우에는 크게 오버슈팅하지 않는 것을 확인할 수 있다.\n\n*즉, 이 변수의 이상치들은 '과거에 비해 최근에 실적이 매우 부진해진 merchant'를 의미한다고 할 수 있다.\n\n**일정 분위수 이상의 값들에  winsorizing 처리를 하는 것이 효과적일 것으로 보인다."},{"metadata":{"trusted":true,"_uuid":"e059536079efe30fba4602f0a619b0509c908490"},"cell_type":"code","source":"\n#### 3 month, 6 month, 12 month Analysis\n#2 Purchases\nprint(data['avg_purchases_lag3'].describe())\nprint(data['avg_purchases_lag6'].describe())\nprint(data['avg_purchases_lag12'].describe())\n#avg_sales_lag3\n#avg_sales_lag6\n#avg_sales_lag12\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8f1cf92c599981c003bf6b5be92474de343e67c8"},"cell_type":"markdown","source":""},{"metadata":{"trusted":true,"_uuid":"1da617e586d7431566104b048e5d6e20e9b4f0f3"},"cell_type":"code","source":"data[data==np.inf].count()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0fe412f09cf6480abd0396e001cc916cb483c5b3"},"cell_type":"code","source":"data.loc[data['avg_purchases_lag3']==np.inf,].head(15)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5575a28bd620bd9d5238ce22042a8c0733a8c4c1"},"cell_type":"markdown","source":"purchases에서 inf 값은 분모(지난 영업달의 구매건수)가 0일 경우일 것이고 위에서 확인할 수 있듯이 3, 6, 12 개월 공통적으로 해당되는것으로 보아 가설이 맞다고 볼 수 있다. 따라서 inf 값을 기록한 3개의 관측치를 제외하고 \"avg_purchases_lag\" 변수 분석을 진행해야한다."},{"metadata":{"trusted":true,"_uuid":"6fc6e27a7c564dc054356cc5a0910efa9f48d0ff"},"cell_type":"code","source":"#data2 : not infinite variables in avg_purchases_lag\ndata2 = data.loc[data[\"avg_purchases_lag3\"]!=np.inf,]\nprint(data2['avg_purchases_lag3'].describe())\nprint(data2['avg_purchases_lag6'].describe())\nprint(data2['avg_purchases_lag12'].describe())        ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3804620032dc070ce4377f6ddec0a2b5df1c9095"},"cell_type":"code","source":"fig, ax = plt.subplots(1, 3, figsize = (16, 6));\ndata2['avg_purchases_lag3'].value_counts().sort_index().plot(kind='hist', ax=ax[0], color='teal', title='3_Month');\ndata2['avg_purchases_lag6'].value_counts().sort_index().plot(kind='hist', ax=ax[1], color='brown', title='6_Month');\ndata2['avg_purchases_lag12'].value_counts().sort_index().plot(kind='hist', ax=ax[2], color='blue', title='12_Month');\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"99b23eca981a631ecd758e1e6f1cda1beea6f6ed"},"cell_type":"code","source":"print(data[data.avg_purchases_lag3>10].shape[0])\nprint(data[data.avg_purchases_lag6>10].shape[0])\nprint(data[data.avg_purchases_lag12>10].shape[0])\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d62c53109f38cb675e7e50a0449100d81d687c83"},"cell_type":"markdown","source":"purchases 변수역시 sales와 비슷한 특성을 가지고 있는 것으로 보이나, 극단적인 값들이 더 적은것을 확인할 수 있다.\n큰값들의 분포"},{"metadata":{"trusted":true,"_uuid":"6721f1fe8e024b381b43a055070b813808eca7b5"},"cell_type":"code","source":"data3 = data2[data2.avg_purchases_lag12>10]\nfig, ax = plt.subplots(1, 3, figsize = (16, 6));\ndata3['avg_purchases_lag3'].value_counts().sort_index().plot(kind='hist', ax=ax[0], color='teal', title='3_Month');\ndata3['avg_purchases_lag6'].value_counts().sort_index().plot(kind='hist', ax=ax[1], color='brown', title='6_Month');\ndata3['avg_purchases_lag12'].value_counts().sort_index().plot(kind='hist', ax=ax[2], color='blue', title='12_Month');\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8252e749823b147822964e46435e598b5b9216ff"},"cell_type":"code","source":"\n#### 3 month, 6 month, 12 month Analysis\n#3 Purchases\nprint(data['active_months_lag3'].describe())\nprint(data['active_months_lag6'].describe())\nprint(data['active_months_lag12'].describe())\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1b338a8b8ba212438fbcd046e8d2928156c9f3a8"},"cell_type":"code","source":"fig, ax = plt.subplots(1, 3, figsize = (16, 6));\ndata['active_months_lag3'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='3_Month');\ndata['active_months_lag6'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='6_Month');\ndata['active_months_lag12'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='blue', title='12_Month');\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"80cbbe7ba0332528a4c4232307513fa232f804c0"},"cell_type":"markdown","source":"위의 두 변수들에 비해 특별한 이상치는 존재하지 않음.\n\n지난 n개월 동안 활성화 되어있었던 개월 수를 나타내는 것이므로 중요한 것은 3, 6, 12개월에 해당하지 않는 merchant들.\n\n이 변수를 이용하여 merchant의 성실성을 대용하는 변수를 생성할 수 있을 것으로 보인다.\nex) 3개월내에 1번이상, 12개월에 3개월 이상 영업을 쉬었다면(성실도가 낮다면, categorical 변수로 적용하는 것이 좋지 않을까?), 이는 해당 merchant와 주로 거래하는 고객의 로열티가 떨어질 수 있는 이유가 될 것이다."},{"metadata":{"trusted":true,"_uuid":"1be8c6b55f185edc638eeb6bd147d100b11bb87c"},"cell_type":"code","source":"x1 = data.loc[data['active_months_lag3']!=3]\nx2 = data.loc[data['active_months_lag6']!=6]\nx3 = data.loc[data['active_months_lag12']!=12]\n\nfig, ax = plt.subplots(1, 3, figsize = (16, 6));\nx1['active_months_lag6'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='3_Month');\nx2['active_months_lag6'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='6_Month');\nx3['active_months_lag12'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='blue', title='12_Month');\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e86f378947c204bc30e80eefcca3ff802af612e3"},"cell_type":"markdown","source":"N개월을 제외하고 나타낸 분포. 거의 선형적인 개형을보이고 있다. "},{"metadata":{"_uuid":"bf107a56e9ef3c1c89793900bc271e74278f0ce1"},"cell_type":"markdown","source":"Merchant 의 성실성(active months) / 최근 성장도(sales/purchases) / 변수들을 생성하여 고객이 주로 이용했던 merchant가 긍정적인지 부정적인지를 이용하면 고객 로열티 예측에 충분히 설명력을 가질 수 있을 것으로 보인다."},{"metadata":{"trusted":true,"_uuid":"40779da4b33ae94cf4f5f4762eaf6354cb5380a4"},"cell_type":"code","source":"data[\"consistency\"] = \"Y\"\ndata.loc[data[\"active_months_lag12\"]!=12,\"consistency\"]= \"N\"\n\ndata.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0e00a1b943773935354a29c7d6f8d4b96cd904f0"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8aeebd506c4f1059488a6d5b22eda0524ff97018"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e94757200c22d2a9c86f7aaffc180b694af44c45"},"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}