{"cells":[{"metadata":{"_uuid":"4b39aac951daffb5d4da6ef258b43fd91d50f1e5"},"cell_type":"markdown","source":"참고\n* https://www.kaggle.com/kailex/r-eda-for-elo-ensemble-learning\n* https://www.kaggle.com/artgor/elo-eda-and-models"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\nimport os\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2153d00f5347c0ffdf0591c03d14ca931586fed3"},"cell_type":"markdown","source":"**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')\nmerchants = pd.read_csv('../input/merchants.csv')\nnew_merchant_t = pd.read_csv('../input/new_merchant_transactions.csv')\nhis_trans = pd.read_csv('../input/historical_transactions.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2f85e07b7dc4ae82e858be7c8fa5b0fef29fa8b9"},"cell_type":"code","source":"print(train.shape)\nprint(test.shape)\nprint(merchants.shape)\nprint(new_merchant_t.shape)\nprint(his_trans.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5bb1ae07d78bda49073b20979a3c6d76c4ce817a"},"cell_type":"code","source":"train.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"957cb11d97f607f96965e0cbbc421c30f9c74a78"},"cell_type":"code","source":"train['first_active_month'] = pd.to_datetime(train['first_active_month']).apply(lambda x: x.strftime('%Y-%m'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8d0b2beb88d06870c930ce8816bb7af7827369e9"},"cell_type":"code","source":"print(new_merchant_t.dtypes)\nprint(his_trans.dtypes)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"836e43708e231649b06a68ca914806b7039b35e4"},"cell_type":"code","source":"new_merchant_t['city_id'] = new_merchant_t['city_id'].astype(object)\nnew_merchant_t['merchant_category_id'] = new_merchant_t['merchant_category_id'].astype(object)\nnew_merchant_t['category_2'] = new_merchant_t['category_2'].astype(object)\nnew_merchant_t['state_id'] = new_merchant_t['state_id'].astype(object)\nnew_merchant_t['subsector_id'] = new_merchant_t['subsector_id'].astype(object)\nnew_merchant_t['purchase_date'] = pd.to_datetime(new_merchant_t['purchase_date'])\n\nhis_trans['city_id'] = his_trans['city_id'].astype(object)\nhis_trans['merchant_category_id'] = his_trans['merchant_category_id'].astype(object)\nhis_trans['category_2'] = his_trans['category_2'].astype(object)\nhis_trans['state_id'] = his_trans['state_id'].astype(object)\nhis_trans['subsector_id'] = his_trans['subsector_id'].astype(object)\nhis_trans['purchase_date'] = pd.to_datetime(his_trans['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c07e35d0d80d5b2a3a837ef0c92497edceafa60d"},"cell_type":"code","source":"print(merchants.dtypes)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"04d47f61c586ce90e6cceb5a6c743de3d3c98915"},"cell_type":"code","source":"merchants['merchant_group_id'] = merchants['merchant_group_id'].astype(object)\nmerchants['merchant_category_id'] = merchants['merchant_category_id'].astype(object)\nmerchants['subsector_id'] = merchants['subsector_id'].astype(object)\nmerchants['city_id'] = merchants['city_id'].astype(object)\nmerchants['state_id'] = merchants['state_id'].astype(object)\nmerchants['category_2'] = merchants['category_2'].astype(object)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ffe383f54a099b6a089ba80cc0afd0c426000a8d"},"cell_type":"markdown","source":"train.csv\n* card_id :\tUnique card identifier\n* first_active_month :\t'YYYY-MM', month of first purchase\n* feature_1 :\tAnonymized card categorical feature\n* feature_2 :\tAnonymized card categorical feature\n* feature_3 :\tAnonymized card categorical feature\n* target :\tLoyalty numerical score calculated 2 months after historical and evaluation period\n"},{"metadata":{"_uuid":"c9572d0b590693b61a3bc80305d302781a9c6579"},"cell_type":"markdown","source":"historical_transactions.csv , new_merchant_period.csv\n* card_id :\tCard identifier\n* month_lag :\tmonth lag to reference date\n* purchase_date :\tPurchase date\n* authorized_flag :\tY' if approved, 'N' if denied\n* category_3 :\tanonymized category\n* installments :\tnumber of installments of purchase\n* category_1 :\tanonymized category\n* merchant_category_id :\tMerchant category identifier (anonymized )\n* subsector_id :\tMerchant category group identifier (anonymized )\n* merchant_id :\tMerchant identifier (anonymized)\n* purchase_amount :\tNormalized purchase amount\n* city_id :\tCity identifier (anonymized )\n* state_id :\tState identifier (anonymized )\n* category_2 :\tanonymized category\n"},{"metadata":{"_uuid":"5093f262f6408d3dff587318172d9b740436aefb"},"cell_type":"markdown","source":"merchant.csv\n* merchant_id :\tUnique merchant identifier\n* merchant_group_id :\tMerchant group (anonymized )\n* merchant_category_id :\tUnique identifier for merchant category (anonymized )\n* subsector_id :\tMerchant category group (anonymized )\n* numerical_1 :\tanonymized measure\n* numerical_2 :\tanonymized measure\n* category_1 :\tanonymized category\n* most_recent_sales_range :\tRange of revenue (monetary units) in last active month --> A > B > C > D > E\n* most_recent_purchases_range :\tRange of quantity of transactions in last active month --> A > B > C > D > E\n* avg_sales_lag3 :\tMonthly average of revenue in last 3 months divided by revenue in last active month\n* avg_purchases_lag3 :\tMonthly average of transactions in last 3 months divided by transactions in last active month\n* active_months_lag3 :\tQuantity of active months within last 3 months\n* avg_sales_lag6 :\tMonthly average of revenue in last 6 months divided by revenue in last active month\n* avg_purchases_lag6 :\tMonthly average of transactions in last 6 months divided by transactions in last active month\n* active_months_lag6 :\tQuantity of active months within last 6 months\n* avg_sales_lag12 :\tMonthly average of revenue in last 12 months divided by revenue in last active month\n* avg_purchases_lag12 :\tMonthly average of transactions in last 12 months divided by transactions in last active month\n* active_months_lag12 :\tQuantity of active months within last 12 months\n* category_4 :\tanonymized category\n* city_id :\tCity identifier (anonymized )\n* state_id :\tState identifier (anonymized )\n* category_2 :\tanonymized category"},{"metadata":{"trusted":true,"_uuid":"867ccdf7ac90872412764dfdb32a3429289e4590"},"cell_type":"code","source":"train.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"71d2ee91a5a0769e0b07c207e90ff39924a80beb"},"cell_type":"markdown","source":"Predicting a loyalty score for each card_id represented in test.csv and sample_submission.csv. -> 'target'"},{"metadata":{"_uuid":"40be7b7cb7861cb16e36debc55d0dc27f63a1e0e"},"cell_type":"markdown","source":"**1. train**"},{"metadata":{"trusted":true,"_uuid":"21f03d361cb91b9ca4532c98eec39a99c2aa950d"},"cell_type":"code","source":"train['target'].describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f9b86d0e2d3764b7f359936d235774fc6a6f638f"},"cell_type":"markdown","source":"평균이 0보다 작고, 다른 값들에 비해 극단적인 값을 갖는 일부 값들이 존재"},{"metadata":{"_uuid":"16a5c4c9814e8ece3130f362d3592d23ae0d6a36"},"cell_type":"markdown","source":"The average is less than zero. Some values are extreme( relative to others ). "},{"metadata":{"trusted":true,"_uuid":"adc227a5ec366b7b6bde9808b7758c947babbc19"},"cell_type":"code","source":"f, ax = plt.subplots(figsize=(8, 6))\nfig = sns.boxplot(train.target)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"062258cdb916a5c1038c75a07d9144144e43344b"},"cell_type":"code","source":"f, ax = plt.subplots(figsize=(10, 8))\nsns.distplot(train['target'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9806bf2f94bad1118b65e12348cb6c65e57b88e0"},"cell_type":"markdown","source":"데이터를 살펴보면, target은 0 근처에 모여있다. -30보다 작은 데이터는 이상치가 아닐까 생각했다."},{"metadata":{"trusted":true,"_uuid":"99ae507f4f9ec0df43e2a613190ed57e6c4e0ee1"},"cell_type":"code","source":"# calculate the correlation matrix\ncorr = train.corr()\n\n# plot the heatmap\nsns.heatmap(corr, \n        xticklabels=corr.columns,\n        yticklabels=corr.columns, annot=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7f1d811f79452f7e64bf1ad0c0eb709e16e2f208"},"cell_type":"code","source":"col_list = train.columns.tolist()\ncol_list = col_list[2:]\nf = pd.melt(train, value_vars=col_list)\ng = sns.FacetGrid(f, col=\"variable\",  col_wrap=2, sharex=False, sharey=False)\ng = g.map(sns.distplot, \"value\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a11f33916b8b08286f359a352b8c9c63592805b7"},"cell_type":"code","source":"col_list = test.columns.tolist()\ncol_list = col_list[2:]\nf = pd.melt(test, value_vars=col_list)\ng = sns.FacetGrid(f, col=\"variable\",  col_wrap=2, sharex=False, sharey=False)\ng = g.map(sns.distplot, \"value\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"38b969045817d37a1feffb6113cf514d117fb5ab"},"cell_type":"markdown","source":"각각의 feature에 대해 train, test이 비슷한 분포"},{"metadata":{"_uuid":"70c865e309bbc6edd578f435065665fde24504f9"},"cell_type":"markdown","source":"**2. transaction data - historical, new**"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"c2baaa69c922e4c0c6d4e7b45934284e7fe84afe"},"cell_type":"code","source":"his_trans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"67649dd60e4b175166f85999dfe84570316e63e6"},"cell_type":"code","source":"his_trans.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a4d625fb08066d2afc325173da20564c86c09037"},"cell_type":"code","source":"new_merchant_t.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a50aa76a42011b4934378f42d94c34ac7cfddfbd"},"cell_type":"markdown","source":"(purchase_amount가 음수인건 무슨 의미..?? -> 뭔가 처리된 값이라서)"},{"metadata":{"trusted":true,"_uuid":"57d92a01e46edb5d29a297b70bf9147779c81b22","scrolled":false},"cell_type":"code","source":"fig, ax = plt.subplots(1, 4, figsize = (16, 6));\nhis_trans['authorized_flag'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='authorized_flag');\nhis_trans['category_1'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='category_1');\nhis_trans['category_2'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='gold', title='category_2');\nhis_trans['category_3'].value_counts().sort_index().plot(kind='bar', ax=ax[3], color='purple', title='category_3');\nplt.suptitle('Counts of categiories historical transaction');\nfig, ax = plt.subplots(1, 4, figsize = (16, 6));\nnew_merchant_t['authorized_flag'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='authorized_flag');\nnew_merchant_t['category_1'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='category_1');\nnew_merchant_t['category_2'].value_counts().sort_index().plot(kind='bar', ax=ax[2], color='gold', title='category_2');\nnew_merchant_t['category_3'].value_counts().sort_index().plot(kind='bar', ax=ax[3], color='purple', title='category_3');\nplt.suptitle('Counts of categiories new merchant transaction');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"747aecf27c7980d517d1ed85527f9ee30b7217ce"},"cell_type":"markdown","source":"거래 데이터의 일부 카테고리 변수를 살펴보았다. historical data와 new merchant data가 유사한 패턴을 보이는 것을 확인. 단, authorized_flag 변수만 다름. new merchant data에서는 N이 존재하지 않음. category_1은 historical data에서 Y값이 조금 더 많고, category_3에서 B가 historical data에서 조금 더 적은 비중."},{"metadata":{"trusted":true,"_uuid":"6f4d44a7f146c9e2515102796d60b5cc61949882"},"cell_type":"code","source":"his_trans[his_trans['authorized_flag']=='N'].head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"87be6c6f6eaf4b58f2b4991bd67876fd2eebeba9"},"cell_type":"code","source":"fig, ax = plt.subplots(1, 2, figsize = (16, 6));\nhis_trans['installments'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='installments');\nhis_trans['month_lag'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='month_lag');\nplt.suptitle('Counts of categiories historical transaction');\n\nfig, ax = plt.subplots(1, 2, figsize = (16, 6));\nnew_merchant_t['installments'].value_counts().sort_index().plot(kind='bar', ax=ax[0], color='teal', title='installments');\nnew_merchant_t['month_lag'].value_counts().sort_index().plot(kind='bar', ax=ax[1], color='brown', title='month_lag');\nplt.suptitle('Counts of categiories new merchant transaction');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"14496a8eb905edcc931bb433043637460707ffaa"},"cell_type":"markdown","source":"(installments 999?? -> outlier..??)"},{"metadata":{"trusted":true,"_uuid":"67b87a48a0e678d4429c97a3cac068365e7fa1a4"},"cell_type":"code","source":"his_trans[his_trans['installments']==999].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"14ddba681df6a2e3ea60381f3d1eb6fac4d7f9c9"},"cell_type":"code","source":"his_trans[(his_trans['installments']==999) & (his_trans.authorized_flag=='Y')]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"498a5dcd7011067037caa9d13c1254ba730fc319"},"cell_type":"markdown","source":"some transactions -> Y authorized_flag"},{"metadata":{"trusted":true,"_uuid":"75cae8b397e2e8bb7f2700cf17ecd663ca15c24c","scrolled":true},"cell_type":"code","source":"his_trans[his_trans['installments']==-1].head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ae283b1619557f92f6603160439b07476b738a61"},"cell_type":"markdown","source":"installments에 -1,999-> outlier? missing value? -> How can handle this? -> 999( if this means over 12 installments...maybe....I can change 999->(ex)13  )"},{"metadata":{"_uuid":"27321933c8394fd49ff458bbf9667c32c933b7c3"},"cell_type":"markdown","source":"-1 in id type variable (city_id, merchant_category_id, state_id, subsector_id)"},{"metadata":{"trusted":true,"_uuid":"dcdd23ae8e68a97b22198a0f77c2cb7bfd2f735c"},"cell_type":"code","source":"his_trans[his_trans['city_id']==-1]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"206e197d9be86ef665666912da6f441ed194e54b"},"cell_type":"code","source":"his_trans[(his_trans['city_id']==-1)&(his_trans['state_id']!=-1)]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f38b708a0b5d400656733658f5583fb5fda06c69"},"cell_type":"markdown","source":"If city_id is -1, state_id is -1, too."},{"metadata":{"trusted":true,"_uuid":"b724cadccb6aca8fbb6bb7ed4f193268064bec73"},"cell_type":"code","source":"print(len(his_trans[(his_trans['city_id']!=-1)&(his_trans['state_id']==-1)]))\nhis_trans[(his_trans['city_id']!=-1)&(his_trans['state_id']==-1)].head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"311eb14df21cff305c1909e255ddca108d3c80fc"},"cell_type":"markdown","source":"But the opposite does not. There are som cases city_id is not -1, state_id is -1."},{"metadata":{"_uuid":"82272afd2f8509abbfe70b99a8bec996e1da6f14"},"cell_type":"markdown","source":"-1 in id type variables -> missing value? outlier? -> how............?"},{"metadata":{"_uuid":"a0ed4c0d032f7b08a6b36547e13bd3eb47a16566"},"cell_type":"markdown","source":"**Data Preprocess**"},{"metadata":{"_uuid":"e788f536146b656da49a7c42e36a30a554f6ccaf"},"cell_type":"markdown","source":"결측치 확인"},{"metadata":{"trusted":true,"_uuid":"94671235320927d07bd7c465b326f8d2f172e8d2"},"cell_type":"code","source":"merchants.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"13740145a817854d63ab72017ffe30a3a6140f52"},"cell_type":"code","source":"merchants[merchants.avg_sales_lag3.isnull()==True]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ef6e29d4cbd79f8fca80c7c7666874f848bbce78"},"cell_type":"markdown","source":"avg_sales_lag의 결측치는 모두 동일한 데이터에서 발생."},{"metadata":{"trusted":true,"_uuid":"e19d9df287bbd3ad52a332de21e1461bea88634e"},"cell_type":"code","source":"new_merchant_t.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4b47f51457b25bb01011e5a5840cbf6d4d40706f"},"cell_type":"code","source":"his_trans.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e002167f30e01085b714feaf732a049cebce0707"},"cell_type":"markdown","source":"**train + historical**"},{"metadata":{"_uuid":"a74b0b35a9ba7bf757f76311396e7ade745f84f3"},"cell_type":"markdown","source":"먼저, train data에서 target이 -30보다 작았던 것들 제거"},{"metadata":{"trusted":true,"_uuid":"46a264ddc58e2c4f9997f04cc4cab2c23eb4e47a"},"cell_type":"code","source":"train = train[train.target>-30]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"33fdb2148c89a060347a6c17ed13ffe3adfe2e70"},"cell_type":"markdown","source":"1. purchase_amount (Normalized purchase amount)"},{"metadata":{"_uuid":"1beb948f2256050f22794874e0d9c57220713655"},"cell_type":"markdown","source":"구매금액으로 이해했는데 구매수인가..?"},{"metadata":{"trusted":true,"_uuid":"4e51f79cbb7f43abc1948e4b8250443ee805d02d"},"cell_type":"code","source":"# purchase_amount size per card_id (구매건수)\nc_his = his_trans.groupby(\"card_id\")\nc_his = c_his[\"purchase_amount\"].size().reset_index()\nc_his.columns = [\"card_id\",\"purchase_amount_size\"]\ntrain = pd.merge(train, c_his, on=\"card_id\", how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f0aac53ea0d27fa87c96616b206852c6ea5262fe"},"cell_type":"code","source":"data = pd.concat([train['target'], train['purchase_amount_size']], axis=1)\nf, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='purchase_amount_size', y=\"target\", data=data)\nplt.xlabel(\"purchase_amount_size\")\nplt.ylabel(\"target\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5067777ff80c05b2548faefe7b0eeb308825ac74"},"cell_type":"code","source":"# purchase_amount mean per card_id\nc_his = his_trans.groupby(\"card_id\")\nc_his = c_his[\"purchase_amount\"].mean().reset_index()\nc_his.columns = [\"card_id\",\"purchase_amount_mean\"]\ntrain = pd.merge(train, c_his, on=\"card_id\", how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"89aaf9ad7055105de23b65e63b2ddb66bc84ee45"},"cell_type":"code","source":"data = pd.concat([train['target'], train['purchase_amount_mean']], axis=1)\nf, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='purchase_amount_mean', y=\"target\", data=data)\nplt.xlabel(\"purchase_amount_mean\")\nplt.ylabel(\"target\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9b028d66b750884398d35c664fe4dc60c40dc2a5"},"cell_type":"markdown","source":"맨 오른쪽에 있는 점 확인"},{"metadata":{"trusted":true,"_uuid":"9333e5122454825c91d5c5834b12d0ca4885671a"},"cell_type":"code","source":"train[train['purchase_amount_mean']>400000]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"461a29bfc96d81a332538696f5db64ed7d789225"},"cell_type":"markdown","source":"혼자 튀는 이유가 his_trans의 describe를 보았을때 max값이 엄청 컸던 것 때문으로 추정되어 확인 -> 이 purchase amount가 이상치인가...?"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"56f6233bd72df6c995875b4fb6ce98fa550e5198"},"cell_type":"code","source":"his_trans[his_trans['purchase_amount']>400000]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c13c704ade1c143386a3eb0c6d80f1c7b51fb378"},"cell_type":"code","source":"# if remove that point,\ndata = data[data.purchase_amount_mean < 400000]\nf, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='purchase_amount_mean', y=\"target\", data=data)\nplt.xlabel(\"purchase_amount_mean\")\nplt.ylabel(\"target\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b4ebd7be1cad4960716e507c4366ef5e8de5edf6"},"cell_type":"markdown","source":"2. installments(number of installments of purchase)"},{"metadata":{"_uuid":"346ab82f577475a3f83d2070146309c1e71656b6"},"cell_type":"markdown","source":"할부 개월로 이해"},{"metadata":{"trusted":true,"_uuid":"c3cd3aa1d5ad48646370ec8dc1198ac37d224561"},"cell_type":"code","source":"c_his = his_trans.groupby(\"card_id\")\nc_his = c_his[\"installments\"].mean().reset_index()\nc_his.columns = [\"card_id\",\"installments_mean\"]\ntrain = pd.merge(train, c_his, on=\"card_id\", how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f79d8db4141ed8b648a36270b0834c2229d9d2cf"},"cell_type":"code","source":"data = pd.concat([train['target'], train['installments_mean']], axis=1)\nf, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='installments_mean', y=\"target\", data=data)\nplt.xlabel(\"installments_mean\")\nplt.ylabel(\"target\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"17d41858281b4f1c8136180831040ed8263ba378"},"cell_type":"markdown","source":"installments_mean (include -1, 999installments) -> How can handle this...? hange 999 -> 13.....or,,,,,,,,remove....?,,,,,,,,or other.....?"},{"metadata":{"_uuid":"a61c2726aa9f9bbd411e8002ce4e7df327e25db5"},"cell_type":"markdown","source":"할부....저렇게 많이도 가능한건....가요....? -> 999의 영향...999를 어떻게 처리해주면 좋을까요..? 만약 12개월 이상이 999로 되는거라면, 999를 13으로 대체...? 제거? 다른 값으로?"},{"metadata":{"_uuid":"444de3cb1d8570fe609920da5f80d507cf0ae62f"},"cell_type":"markdown","source":"3. month_lag(month lag to reference date)"},{"metadata":{"trusted":true,"_uuid":"b6f437b6c70aa10235b7e173b5cf95a78b7e14c5"},"cell_type":"code","source":"c_his = his_trans.groupby(\"card_id\")\nc_his = c_his[\"month_lag\"].max().reset_index()\nc_his.columns = [\"card_id\",\"month_lag_recent\"]\ntrain = pd.merge(train, c_his, on=\"card_id\", how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8b3eca9e8a002781ec6ea113b43fdfd4eb61fc51"},"cell_type":"code","source":"data = pd.concat([train['target'], train['month_lag_recent']], axis=1)\nf, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='month_lag_recent', y=\"target\", data=data)\nplt.xlabel(\"month_lag_recent\")\nplt.ylabel(\"target\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"aba94a795c312c9bf96a32b99e1fdf38457791a8"},"cell_type":"markdown","source":"최근에 구매한 경우가 많음"},{"metadata":{"_uuid":"a06cfaaf39a62a53de2005e6a01c3588042f2cf7"},"cell_type":"markdown","source":"first_active에서 최근 구매까지 기간을 살펴보면,"},{"metadata":{"trusted":true,"_uuid":"4c2d6f9b730a11cc7549efef3773f0cf19271176"},"cell_type":"code","source":"his_trans['purchase_date_month'] = his_trans['purchase_date'].apply(lambda x: x.strftime('%Y-%m'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4354af34d3f3800534ea7ff7a5e5cf2f56e471ce"},"cell_type":"code","source":"c_his = his_trans.groupby(\"card_id\")\nc_his = c_his[\"purchase_date_month\"].max().reset_index()\nc_his.columns = [\"card_id\",\"purchase_date_month_recent\"]\ntrain = pd.merge(train, c_his, on=\"card_id\", how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e8741c8e5f21d65c4d8070221b3fd282cf37c90d"},"cell_type":"code","source":"train['purchase_period'] = pd.to_datetime(train['purchase_date_month_recent'])-pd.to_datetime(train['first_active_month'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1f0738a0c0e93b9acd9c1a64dd34336785bf3ee9"},"cell_type":"code","source":"for i in range(len(train)):\n    train.purchase_period[i] = train.purchase_period[i].days//30","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e49d0405db2ca02700257cf2855affe9e666a563"},"cell_type":"code","source":"# calculate the correlation matrix\ncorr = train.corr()\n\n# plot the heatmap\nsns.heatmap(corr, \n        xticklabels=corr.columns,\n        yticklabels=corr.columns, annot=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"03b5f20814eab548a0bf0cdc0390429c66c8ddcb"},"cell_type":"markdown","source":"train에 거래 데이터를 추가해서 correlation을 다시 확인. 여전히.....ㅎ...."},{"metadata":{"_uuid":"9b55968a91d276d7ce7a29879fa0454242fa1d2f"},"cell_type":"markdown","source":"**train + new_merchant_transactions**"},{"metadata":{"trusted":true,"_uuid":"ecb24ddaaf0524ce134131081304f2e04972d1b4"},"cell_type":"code","source":"new_merchant_t.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a764450f1d3719d87578e0a8cb7cae5c9b0c580f"},"cell_type":"markdown","source":"1. purchase_amount (Normalized purchase amount)"},{"metadata":{"trusted":true,"_uuid":"7f933eb7a182e473fd10b3292199b33656f7c540"},"cell_type":"code","source":"# purchase_amount size per card_id (구매건수)\nc_his = new_merchant_t.groupby(\"card_id\")\nc_his = c_his[\"purchase_amount\"].size().reset_index()\nc_his.columns = [\"card_id\",\"purchase_amount_size_new\"]\ntrain = pd.merge(train, c_his, on=\"card_id\", how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5a21c6379f5305b9abc3488161fce3da8ecbccb0"},"cell_type":"code","source":"data = pd.concat([train['target'], train['purchase_amount_size_new']], axis=1)\nf, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='purchase_amount_size_new', y=\"target\", data=data)\nplt.xlabel(\"purchase_amount_size_new\")\nplt.ylabel(\"target\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"192bc75780d758917c7f7da6c62d7ee81d1a865b"},"cell_type":"code","source":"# purchase_amount mean per card_id\nc_his = new_merchant_t.groupby(\"card_id\")\nc_his = c_his[\"purchase_amount\"].mean().reset_index()\nc_his.columns = [\"card_id\",\"purchase_amount_mean_new\"]\ntrain = pd.merge(train, c_his, on=\"card_id\", how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2a4b37dbce8163568115cea39b752a0670b8bd9f"},"cell_type":"code","source":"data = pd.concat([train['target'], train['purchase_amount_mean_new']], axis=1)\nf, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='purchase_amount_mean_new', y=\"target\", data=data)\nplt.xlabel(\"purchase_amount_mean_new\")\nplt.ylabel(\"target\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e859bc6efbd4d6d980d94b1f76e8da826847e26b"},"cell_type":"markdown","source":"purchase_amount_mean과 target의 relation을 살펴봤을때, historical에서는 거의 flat한 형태에 가까웠는데 new에서는 조금 더 정규분포처럼 생긴 모양."},{"metadata":{"_uuid":"89c733cf9b9e0621eb44fa5d6fb64ab35ac0fe37"},"cell_type":"markdown","source":"2. installments(number of installments of purchase)"},{"metadata":{"trusted":true,"_uuid":"01fd92d3a1ae8cf8ca6e0015b3ca413949cbac80"},"cell_type":"code","source":"c_his = new_merchant_t.groupby(\"card_id\")\nc_his = c_his[\"installments\"].mean().reset_index()\nc_his.columns = [\"card_id\",\"installments_mean_new\"]\ntrain = pd.merge(train, c_his, on=\"card_id\", how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"29bd5dde812a2553d632231e073975d762128074"},"cell_type":"code","source":"data = pd.concat([train['target'], train['installments_mean_new']], axis=1)\nf, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='installments_mean_new', y=\"target\", data=data)\nplt.xlabel(\"installments_mean_new\")\nplt.ylabel(\"target\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"89bc481dea863e1f3a6a26020f8aa541b5889dce"},"cell_type":"markdown","source":"살짝 동떨어진 값이 존재..."},{"metadata":{"trusted":true,"_uuid":"81c1ad7efc48751a3e7855fe43b6af48b260d8cb"},"cell_type":"code","source":"train[train.installments_mean_new>30]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"84c61e4f04b32da99dc803518ccda46483f74db7"},"cell_type":"markdown","source":"혹시 installments가 999여서?"},{"metadata":{"trusted":true,"_uuid":"deebc9b0d90d6e0e629acede78b0a5dab10cd9b7"},"cell_type":"code","source":"new_merchant_t[new_merchant_t.installments==999]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e9ec0d80fd25183137b9ab297a2b9fc83a200029"},"cell_type":"markdown","source":"new_merchant_trasaction data에 999 installments인 경우가 2가지 존재."},{"metadata":{"trusted":true,"_uuid":"62f45260ebc1285428c34a77e3fef46cc2d9a34d"},"cell_type":"code","source":"# if remove that point,\ndata = data[data.installments_mean_new<30]\nf, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='installments_mean_new', y=\"target\", data=data)\nplt.xlabel(\"installments_mean_new\")\nplt.ylabel(\"target\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f37734e32736d96686bd7fd743eee314bd1dacb4"},"cell_type":"markdown","source":"그래프가 조금 신기...? 퍼져있기도 하고, 줄서있기도 하고..?"},{"metadata":{"_uuid":"d229d3e07666ff98502d6dde4b483676f1f316aa"},"cell_type":"markdown","source":"3. month_lag(month lag to reference date)"},{"metadata":{"trusted":true,"_uuid":"de1d41ba16c5d90c559d5bebf21a2eae7a3712da"},"cell_type":"code","source":"c_his = new_merchant_t.groupby(\"card_id\")\nc_his = c_his[\"month_lag\"].max().reset_index()\nc_his.columns = [\"card_id\",\"month_lag_recent_new\"]\ntrain = pd.merge(train, c_his, on=\"card_id\", how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e53e6eef3accb2afbed270e35a66c9f35b94f785"},"cell_type":"code","source":"data = pd.concat([train['target'], train['month_lag_recent_new']], axis=1)\nf, ax = plt.subplots(figsize=(8, 6))\nplt.scatter(x='month_lag_recent_new', y=\"target\", data=data)\nplt.xlabel(\"month_lag_recent_new\")\nplt.ylabel(\"target\")\nplt.show","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"408c9a0e30157880a8cc879fc6c867e8a16fb429"},"cell_type":"markdown","source":"first_active에서 최근 구매까지 기간을 살펴보면,"},{"metadata":{"trusted":true,"_uuid":"77e905e5692be2726cbde85dca071c649d484b53"},"cell_type":"code","source":"new_merchant_t['purchase_date_month'] = new_merchant_t['purchase_date'].apply(lambda x: x.strftime('%Y-%m'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e2658b97397f89d3e3c6553d717fbd69bfd92aa4"},"cell_type":"code","source":"c_his = new_merchant_t.groupby(\"card_id\")\nc_his = c_his[\"purchase_date_month\"].max().reset_index()\nc_his.columns = [\"card_id\",\"purchase_date_month_recent_new\"]\ntrain = pd.merge(train, c_his, on=\"card_id\", how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"15b26510c86c4cad61dbe0e3b8d4ad914b349234"},"cell_type":"code","source":"train['purchase_period_new'] = pd.to_datetime(train['purchase_date_month_recent_new'])-pd.to_datetime(train['first_active_month'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0f1138c24befc1d4029a10c0b1feb68214af5ad4"},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fe805cad7dddb2e863b7c1e5737bf6106846fe85"},"cell_type":"markdown","source":"근데 궁금한게..고객맞춤형 가게 추천같은건데...뭔가 지금 한건 그냥 고객별로만 본거같은데...흠....?\n변수 더 생성해보기 -> 평균 구매간격, 카드별 가장 빈도수 높은 city, state, merchant_id 등등의 id변수 활용..?여기서 나온 merchant_id를 기준으로 merchant data 이용...?흠...(merchant data 어떻게 활용하면 좋을까요..)\n"},{"metadata":{"_uuid":"74266c0e914d8a19e1756d27e2c824fa07cc69d0"},"cell_type":"markdown","source":"most frequent value in card_id (citiy_id, state_id,merchant_id, merchant_category_id, category1, category2, category3,subsector_id)"},{"metadata":{"trusted":true,"_uuid":"f676f6332b8de1474f96de6891a4a892d31fc6a8"},"cell_type":"code","source":"# Fill null by most frequent data\n#df_trans['category_2'].fillna(1.0,inplace=True)\n#df_trans['category_3'].fillna('A',inplace=True)\n#df_trans['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4c0b82df46ddfd939b3d9ccde411bc29d71530fd"},"cell_type":"code","source":"train.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"49aa810e8c5379a24b31f4d4d0008abcb13a1685"},"cell_type":"code","source":"train.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7fdd050acf240b179987fdee490fae3a5e003aca"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d7ebf72038f7a01523e88b0bab4254675c2da06f"},"cell_type":"markdown","source":"변수 다 만든 후 next week는 predict model 만들어서 비교해보기! "}],"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}