{"metadata": {"kernelspec": {"display_name": "Python 3", "language": "python", "name": "python3"}, "language_info": {"version": "3.6.3", "nbconvert_exporter": "python", "mimetype": "text/x-python", "pygments_lexer": "ipython3", "file_extension": ".py", "codemirror_mode": {"version": 3, "name": "ipython"}, "name": "python"}}, "nbformat_minor": 1, "cells": [{"metadata": {"_cell_guid": "8523af0b-a161-4a7d-be7b-a352cba00b2b", "_uuid": "7e5964b21cf7da170aba4d6f3853bcab23631c48"}, "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", "\n", "import numpy as np # linear algebra\n", "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\\\n", "import seaborn as sns\n", "import matplotlib.pyplot as plt\n", "\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", "\n", "from subprocess import check_output\n", "print(check_output([\"ls\", \"../input\"]).decode(\"utf8\"))\n", "\n", "# Any results you write to the current directory are saved as output."], "cell_type": "code", "outputs": [], "execution_count": 1}, {"metadata": {"_cell_guid": "0f771d16-ac2b-43e8-ab1c-8c60803ef9dc", "_uuid": "340c297aa490d2ae91ce56414bed45f3a5aa987d", "collapsed": true}, "source": ["train = pd.read_csv('../input/train_v2.csv')\n", "transactions = pd.read_csv('../input/transactions_v2.csv')\n", "members = pd.read_csv('../input/members_v3.csv')"], "cell_type": "code", "outputs": [], "execution_count": 2}, {"metadata": {"_cell_guid": "5b6c71d0-47e7-498f-980b-b84d88fa5ce3", "_uuid": "1288e8f737c7f6f619f3088b2613db1865ed5154"}, "source": ["#user_logs = pd.read_csv('../input/user_logs.csv')\n", "transactions.head(1)"], "cell_type": "code", "outputs": [], "execution_count": 3}, {"metadata": {"_cell_guid": "53624985-e219-4516-b0f3-3d5022cebc61", "_uuid": "0e6599bc3d5ae21adcdcd22b4c5ae1b14106fd64"}, "source": ["print(transactions.msno.nunique())\n", "print(train.msno.nunique())"], "cell_type": "code", "outputs": [], "execution_count": 4}, {"metadata": {"_cell_guid": "9e0151f6-0fcc-46fc-9f5f-f410a7ab3e34", "_uuid": "79968158096e7309ca2054a31585d9b85758c378"}, "source": ["train_transactions = pd.merge(train, transactions, on='msno', how='left')\n", "train_transactions.head(1)"], "cell_type": "code", "outputs": [], "execution_count": 5}, {"metadata": {"_cell_guid": "2f34bc1b-a47d-46fb-9ae4-a39fbe3591b3", "_uuid": "816019f1f939d3b8b1256ef0498606221b92cc48"}, "source": ["composite = pd.merge(train_transactions, members, on='msno', how='left')\n", "composite.head(1)"], "cell_type": "code", "outputs": [], "execution_count": 39}, {"metadata": {"_cell_guid": "626ad80a-a984-4e07-badb-7a5abdb9e850", "_uuid": "3af3fea944b67c199109e05d99c00cd31642480a"}, "source": ["print(composite.msno.nunique())\n", "composite.shape"], "cell_type": "code", "outputs": [], "execution_count": 7}, {"metadata": {"_cell_guid": "91748f6e-7d37-4fe8-9ec4-d3d5b75f462f", "_uuid": "e0e56ab0c790f92980421deb87be9ad0ccc3f228", "collapsed": true}, "source": ["composite['membership_expire_date'] = pd.to_datetime(composite['membership_expire_date'], format='%Y%m%d')\n", "composite['transaction_date'] = pd.to_datetime(composite['transaction_date'], format='%Y%m%d')"], "cell_type": "code", "outputs": [], "execution_count": 40}, {"metadata": {"_cell_guid": "95497acc-d228-4f15-91e6-97e291223d53", "_uuid": "ccc103425cfb12f0021743a96abeab5f120a74aa", "collapsed": true}, "source": ["mask = composite['registration_init_time'].notnull()\n", "composite.loc[composite[mask].index, 'registration_init_time'\n", "             ] = composite.loc[composite[mask].index, 'registration_init_time'].astype('int')"], "cell_type": "code", "outputs": [], "execution_count": 41}, {"metadata": {"_cell_guid": "b4e849c7-5957-4609-b663-603d61504535", "_uuid": "dea4cacf0cf51f977011e02421fe411cefc3100a"}, "source": ["composite.head(1)"], "cell_type": "code", "outputs": [], "execution_count": 10}, {"metadata": {}, "source": ["composite['registered_via'].value_counts()"], "cell_type": "code", "outputs": [], "execution_count": 16}, {"metadata": {"collapsed": true}, "source": ["def churn_rate(df, col):\n", "    col_rate = df.groupby(col)[['is_churn']].mean().reset_index().sort_values(\n", "        'is_churn', ascending=False)\n", "    sns.barplot(x=col, y='is_churn', data=col_rate)\n", "    print(plt.show())\n", "    return col_rate"], "cell_type": "code", "outputs": [], "execution_count": 42}, {"metadata": {}, "source": ["rv = churn_rate(composite,'registered_via')"], "cell_type": "code", "outputs": [], "execution_count": 43}, {"metadata": {}, "source": ["rv"], "cell_type": "code", "outputs": [], "execution_count": 31}, {"metadata": {}, "source": ["composite.is_churn.mean()"], "cell_type": "code", "outputs": [], "execution_count": 30}, {"metadata": {}, "source": ["cancel = churn_rate(composite,'is_cancel')"], "cell_type": "code", "outputs": [], "execution_count": 44}, {"metadata": {}, "source": ["cancel"], "cell_type": "code", "outputs": [], "execution_count": 26}, {"metadata": {"collapsed": true}, "source": ["composite['registration_init_time'] = pd.to_datetime(\n", "    composite['registration_init_time'], format='%Y%m%d')"], "cell_type": "code", "outputs": [], "execution_count": 45}, {"metadata": {}, "source": ["composite.head(1)"], "cell_type": "code", "outputs": [], "execution_count": 46}, {"metadata": {}, "source": ["most_recent = composite['registration_init_time'].max()"], "cell_type": "code", "outputs": [], "execution_count": 49}, {"metadata": {}, "source": ["composite['registration_init_weeks'] = (\n", "    most_recent - composite.registration_init_time)/ np.timedelta64(1, 'W')\n", "\n", "\n", "composite.head(1)"], "cell_type": "code", "outputs": [], "execution_count": 50}, {"metadata": {"collapsed": true}, "source": ["composite['registration_init_weeks'] = composite['registration_init_weeks'].round(decimals=0)"], "cell_type": "code", "outputs": [], "execution_count": 54}, {"metadata": {}, "source": ["weeks = composite.groupby('registration_init_weeks')[\n", "    ['is_churn']].mean().reset_index().sort_values('is_churn',ascending=False)\n", "weeks \n", "weeks.head(10)"], "cell_type": "code", "outputs": [], "execution_count": 55}, {"metadata": {}, "source": ["sns.barplot(x='registration_init_weeks', y='is_churn', data=weeks.head(10))\n", "plt.show()"], "cell_type": "code", "outputs": [], "execution_count": 61}, {"metadata": {}, "source": ["mask = (composite['registration_init_weeks']>=0)&(composite['registration_init_weeks']<=12)\n", "composite[mask]['registration_init_weeks'].value_counts()"], "cell_type": "code", "outputs": [], "execution_count": 63}, {"metadata": {}, "source": ["composite['registration_init_weeks'].value_counts(ascending=False).head(10)"], "cell_type": "code", "outputs": [], "execution_count": 58}, {"metadata": {"collapsed": true}, "source": ["fig, ax = plt.subplots(figsize=(10,6))\n", "\n", "ax.plot_date(composite['registration_init_time'], composite['is_churn', color=\"blue\", linestyle=\"-\")\n", "\n", "ax.set(xlabel='date', ylabel='churn',title='Click Rate Over Time')\n", "ax.legend()\n", "\n", "plt.show()"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {}, "source": ["#composite['registration_init_year'] = composite.registration_init_time.dt.year\n", "#composite['registration_init_month'] = composite.registration_init_time.dt.month\n", "#composite['registration_init_day'] = composite.registration_init_time.dt.day\n", "#composite = composite.drop('registration_init_time', axis=1)"], "cell_type": "code", "outputs": [], "execution_count": 29}, {"metadata": {}, "source": ["#year = churn_rate(composite, 'registration_init_year')"], "cell_type": "code", "outputs": [], "execution_count": 32}, {"metadata": {}, "source": ["#year"], "cell_type": "code", "outputs": [], "execution_count": 33}, {"metadata": {}, "source": ["#composite.registration_init_year.value_counts()"], "cell_type": "code", "outputs": [], "execution_count": 35}, {"metadata": {}, "source": ["#month = churn_rate(composite, 'registration_init_month')"], "cell_type": "code", "outputs": [], "execution_count": 36}, {"metadata": {}, "source": ["#month"], "cell_type": "code", "outputs": [], "execution_count": 37}, {"metadata": {}, "source": ["#day = churn_rate(composite, 'registration_init_day')"], "cell_type": "code", "outputs": [], "execution_count": 38}, {"metadata": {"_cell_guid": "611b3d2a-c034-40f8-b5bc-e8f7d1d239e5", "_uuid": "084fac54f6822fb8d7338c3fb901f9722d2a5e18"}, "source": ["#composite.isnull().sum()"], "cell_type": "code", "outputs": [], "execution_count": 11}, {"metadata": {"_cell_guid": "e3606711-1e0c-457c-bb47-6c49702ed76e", "_uuid": "880540e554184c7fb0477718f29ebb45cac05442"}, "source": ["composite.shape"], "cell_type": "code", "outputs": [], "execution_count": 12}, {"metadata": {"_cell_guid": "aacb3453-4657-4dfd-9226-d33cc714a214", "_uuid": "8cb2e323a6e63f9da3a7f9edc5069405c2ec29bf"}, "source": ["for col in transactions.columns:\n", "    print(col + ' has ' + str(transactions[col].nunique()) + ' unique_values.')"], "cell_type": "code", "outputs": [], "execution_count": 13}, {"metadata": {"_cell_guid": "0fcb1808-6ca7-4e09-94d8-9443f83f6140", "_uuid": "423f68d1d43b50e363b20ebae1437f8e96eb9b70", "collapsed": true}, "source": ["#transactions = transactions.drop_duplicates()\n", "#transactions['payment_method_id', 'payment_plan_days']"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "c827dc69-6e0e-4237-a70b-9e3551944e50", "_uuid": "e0de93e8724f49e8b61ec9b98475f0899402714b", "collapsed": true}, "source": ["#def churn_rate(df, col):\n", "#    col_rate = df.groupby(col)[['is_churn']].mean().reset_index().sort_values(\n", "#        'is_churn', ascending=False)\n", "#    sns.barplot(x=col, y='is_churn', data=col_rate)\n", "#    print(plt.show())\n", "#    return col_rate"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "d4323b25-3388-455d-bd97-4b9ca9562f57", "_uuid": "322fbdf52b46decad8b8bdab848b644fb53c7a42", "collapsed": true}, "source": [], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "b550a80b-7c7d-46ee-9010-8575d33e3386", "_uuid": "aad102275716db9f7ac6a68d0e76cab750612a56", "collapsed": true}, "source": ["#pm_id = churn_rate(transactions, 'payment_method_id')"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "71596d97-31ed-4df5-8fd3-cc9e302ccf22", "_uuid": "a87c02e145620a8f25cb39c75048d79fdf8bd74c", "collapsed": true}, "source": ["#pm_id.dtypes"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "62ebde98-a998-4605-83d6-1f9ee60ae582", "_uuid": "8a4016a17193b028e85f68a35dbe3d9362518234", "collapsed": true}, "source": ["#members = pd.merge(members, train, on='msno', how='left')\n"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "1a91c48f-c366-499d-bb3f-aa2a19d7d301", "_uuid": "90a0e84d377224c389e1d2c9292cca90b9399a65"}, "source": ["transactions.msno.nunique()"], "cell_type": "raw"}, {"metadata": {"_cell_guid": "5045e9c1-3d33-483d-b793-ee23fbab99d4", "_uuid": "cdc1f1ab49b1072775e4de42e690023d94830f82", "collapsed": true}, "source": ["#members.shape"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "86b05b27-3cc6-4431-be72-54e447b87150", "_uuid": "58009be4101b5cb99a4cd1845f282734666ae23d", "collapsed": true}, "source": [], "cell_type": "code", "outputs": [], "execution_count": null}], "nbformat": 4}