{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport seaborn as sns\nfrom matplotlib.pyplot import xticks, figure\nimport os\nprint(os.listdir(\"../input\"))\n\nfrom subprocess import check_output\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":"print(check_output(['ls', '../input']).decode('utf8'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"transaction_df = pd.read_csv('../input/transactions_v2.csv')\ntransaction_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"transaction_df.duplicated(subset='msno').sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"transaction_df.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"figure(num=None, figsize=(16,8), dpi=60)\nsns.countplot(x='payment_method_id', data=transaction_df)\nxticks(rotation=90)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"transaction_df.payment_method_id = transaction_df.payment_method_id.replace([2,3,5,6,8,10,11,12,14,16,17,18,19,21,23,24,25,26], 1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"transaction_df.payment_method_id = pd.factorize(transaction_df.payment_method_id)[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"figure(num=None, figsize=(16,8), dpi=60)\nsns.countplot(x='payment_method_id', data=transaction_df)\nxticks(rotation=90)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"transaction_df.payment_plan_days.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.boxplot(x='payment_plan_days', data=transaction_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"transaction_df.payment_plan_days.quantile([0.05,0.98])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"transaction_df.plan_list_price.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.boxplot(x='plan_list_price', data=transaction_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"transaction_df.plan_list_price.quantile([0.05,0.98])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.countplot(x='is_auto_renew', data=transaction_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"dublicate_msno_df = transaction_df[transaction_df.duplicated(subset='msno')]\ndublicated_msno = dublicate_msno_df.msno.unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"len(dublicated_msno)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"transaction_group = transaction_df.groupby('msno')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"new_transaction_df = pd.DataFrame(data=None, columns=['msno','payment_mode','total_plan_days','total_plan_price','total_ammount_paid','last_plan_days','last_plan_price',\n                                                     'is_auto_renewal','first_transaction_date','last_transaction_date','membership_expire_date','is_cancel','no_of_record'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for msno in dublicated_msno:\n    group_df = transaction_group.get_group(msno)\n    payment_mode = group_df.payment_method_id.mode()[0]\n    total_plan_days = group_df.payment_plan_days.sum()\n    total_plan_price = group_df.plan_list_price.sum()\n    total_ammount_paid = group_df.actual_amount_paid.sum()\n    is_auto_renewal = group_df.is_auto_renew.mode()[0]\n    first_transaction_date = group_df.transaction_date.min()\n    last_transaction_date = group_df.transaction_date.max()\n    membership_expire_date = group_df.membership_expire_date.max()\n    last_date = group_df[group_df.transaction_date==last_transaction_date]\n    last_plan_days = 0\n    last_plan_price = 0\n    if len(last_date) > 0:\n        last_plan_days = last_date['payment_plan_days'].values.max()\n        last_plan_price = last_date['plan_list_price'].values.max()\n    \n    cancled = group_df.is_cancel.values\n    is_cancle = 1 if 1 in cancled else 0\n    no_of_record = len(group_df)\n    pointer = len(new_transaction_df)\n    new_transaction_df.loc[pointer] = [msno, payment_mode, total_plan_days, total_plan_price, total_ammount_paid, last_plan_days, last_plan_price,\n                                       is_auto_renewal, first_transaction_date, last_transaction_date, membership_expire_date,\n                                       is_cancle, no_of_record]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"new_transaction_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(check_output(['ls', '.']).decode('utf8'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import os\nos.mkdir('data')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"new_transaction_df.to_csv('data/new_transaction_v2.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a href=\"data/new_transaction_v2.csv\">new_transaction_v2.csv</a>"},{"metadata":{"trusted":true},"cell_type":"code","source":"unique_msno_df = transaction_df[~transaction_df.msno.isin(dublicated_msno)]\nnew_transaction_df_1 = new_transaction_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"unique_msno_df.duplicated(subset='msno').sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"len(unique_msno_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"rename_columns = {'payment_method_id': 'payment_mode', 'payment_plan_days': 'total_plan_days', 'plan_list_price': 'total_plan_price',\n          'actual_amount_paid': 'total_ammount_paid', 'transaction_date': 'first_transaction_date','is_auto_renew': 'is_auto_renewal'}\nunique_msno_df['last_transaction_date'] = unique_msno_df.transaction_date\nunique_msno_df['last_plan_days'] = unique_msno_df['payment_plan_days']\nunique_msno_df['last_plan_price'] = unique_msno_df['plan_list_price']\nunique_msno_df['no_of_record'] = 1\nunique_msno_df = unique_msno_df.rename(columns=rename_columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"unique_msno_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"new_transaction_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"unique_msno_df.to_csv('data/unique_mnso.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a href=\"data/unique_mnso.csv\">unique_mnso.csv</a>"},{"metadata":{"trusted":true},"cell_type":"code","source":"new_transaction_df_1 = new_transaction_df_1.append(unique_msno_df, ignore_index=True, sort=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"new_transaction_df_1.to_csv('data/new_transaction_df_1.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a href=\"data/new_transaction_df_1.csv\">new_transaction_df_1.csv</a>"},{"metadata":{"trusted":true},"cell_type":"code","source":"del(new_transaction_df)\ndel(unique_msno_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_df = pd.read_csv('../input/members_v3.csv')\nmember_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_df.duplicated(subset='msno').sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_df.isna().sum() / len(member_df) * 100","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_df.gender.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.countplot(x='gender', data=member_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df = new_transaction_df_1.merge(member_df, how='left', on='msno')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.first_transaction_date = pd.to_datetime(member_details_df.first_transaction_date, format='%Y%m%d') \nmember_details_df.last_transaction_date = pd.to_datetime(member_details_df.last_transaction_date, format='%Y%m%d')\nmember_details_df.membership_expire_date = pd.to_datetime(member_details_df.membership_expire_date, format='%Y%m%d')\nmember_details_df.registration_init_time = pd.to_datetime(member_details_df.registration_init_time, format='%Y%m%d.0')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del(new_transaction_df_1)\ndel(member_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.isna().sum() / len(member_details_df) * 100","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.gender.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df = member_details_df.drop(columns='gender')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.city.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"figure(figsize=(10,6))\nsns.countplot('city', data=member_details_df)\nxticks(rotation=90)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.city = member_details_df.city.fillna(1.)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"figure(figsize=(10,6))\nsns.countplot('city', data=member_details_df)\nxticks(rotation=90)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.bd.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.boxplot(x='bd', data=member_details_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"percentiles = member_details_df.bd.quantile([0.51,0.999]).values\nmember_details_df.loc[member_details_df.bd < percentiles[0], 'bd'] = percentiles[0]\nmember_details_df.loc[member_details_df.bd > percentiles[1], 'bd'] = percentiles[1]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"percentiles","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.boxplot(x='bd', data=member_details_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.bd.fillna(member_details_df.bd.mean(), inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.registered_via.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.countplot(x='registered_via', data=member_details_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.registered_via.fillna(7., inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.countplot(x='registered_via', data=member_details_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.registration_init_time.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(member_details_df.registration_init_time.min())\nprint(member_details_df.registration_init_time.max())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"date_ftd = member_details_df.first_transaction_date\ndate_reginit = member_details_df.registration_init_time\n\ntotal_years = ((date_ftd - date_reginit) / np.timedelta64(1,'Y')).dropna().values\nprint(sum(total_years) / len(total_years))\ndel(date_ftd)\ndel(date_reginit)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from dateutil.relativedelta import relativedelta","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"temp_init_time = member_details_df.first_transaction_date - pd.Timedelta(days=365*3.44)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.registration_init_time = member_details_df.registration_init_time.fillna(temp_init_time).dt.date\n# del(temp_init_time)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.registration_init_time = pd.to_datetime(member_details_df.registration_init_time)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.isna().sum() / len(member_details_df) * 100","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.to_csv('data/member_details_df_clean.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a href='data/member_details_df_clean.csv'>member_details_df.csv</a>"},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# membership_expire_in = (member_details_df.membership_expire_date - member_details_df.last_transaction_date) / np.timedelta64(1, 'M')\n# membership_expire_in = membership_expire_in.round().astype(int)\n# member_details_df['membership_expire_in'] = membership_expire_in\n# del(membership_expire_in)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"diff_init_first_trans =  (member_details_df.first_transaction_date - member_details_df.registration_init_time) / np.timedelta64(1, 'Y')\ndiff_init_first_trans = diff_init_first_trans.round().astype(int)\nmember_details_df['diff_init_first_trans'] = diff_init_first_trans\ndel(diff_init_first_trans)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"average_plan_days = member_details_df.total_plan_days / member_details_df.no_of_record\naverage_plan_days = average_plan_days.astype(float).round(0).astype(int)\nmember_details_df['average_plan_days'] = average_plan_days","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df['average_plan_price'] = member_details_df.total_plan_price / member_details_df.no_of_record\nmember_details_df['average_amount_paid'] = member_details_df.total_ammount_paid / member_details_df.no_of_record","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df['ammount_due'] = member_details_df.total_plan_price - member_details_df.total_ammount_paid","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"drop_columns = ['total_plan_days', 'total_plan_price', 'total_ammount_paid', 'first_transaction_date', 'last_transaction_date',\n               'membership_expire_date', 'registration_init_time']\nmember_details_df = member_details_df.drop(columns=drop_columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df_final = member_details_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df = pd.read_csv('../input/train_v2.csv')\ntrain_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"len(train_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df = pd.read_csv('../input/sample_submission_v2.csv')\ntest_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"len(test_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df_final = member_details_df_final.merge(train_df, how='left', on='msno')\nmember_details_df_final = member_details_df_final.merge(test_df, how='left', on='msno')\nmember_details_df_final.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"collapsed":true},"cell_type":"code","source":"member_details_df_final.is_churn_x.fillna(member_details_df_final.is_churn_y, inplace=True)\nmember_details_df_final.rename(columns={'is_churn_x': 'is_churn'}, inplace=True)\nmember_details_df_final.drop(columns=['is_churn_y'], inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df_final.dropna(inplace=True)\nmember_details_df_final.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"member_details_df_final.to_csv('data/member_details_df_final.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a href='data/member_details_df_final.csv'>member_details_df_final.csv</a>"},{"metadata":{"trusted":true},"cell_type":"code","source":"del(member_details_df_final)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df = train_df.merge(member_details_df, how='left', on='msno')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.dropna(inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df = test_df.merge(member_details_df, how='left', on='msno')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df.dropna(inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_X = train_df.iloc[:,2:]\ntrain_y = train_df['is_churn']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_X = test_df.iloc[:,2:]\ntest_y = test_df['is_churn']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from sklearn.ensemble import RandomForestClassifier","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"rfc = RandomForestClassifier(max_depth=50, n_estimators=100, random_state=0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"rfc.fit(train_X, train_y)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"predict_y = rfc.predict(test_X)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_y = test_y.values","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"correct = 0\nfor i, val in enumerate(predict_y):\n    if val == test_y[i]:\n#         print('actual : {}, predicted : {}'.format(test_y[i], val))\n        correct += 1\nprint(correct)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"correct / len(test_y) * 100","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}