{"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", "collapsed": true}, "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": null}, {"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": null}, {"metadata": {"_cell_guid": "5b6c71d0-47e7-498f-980b-b84d88fa5ce3", "_uuid": "1288e8f737c7f6f619f3088b2613db1865ed5154", "collapsed": true}, "source": ["#user_logs = pd.read_csv('../input/user_logs.csv')\n", "transactions.head(1)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "53624985-e219-4516-b0f3-3d5022cebc61", "_uuid": "0e6599bc3d5ae21adcdcd22b4c5ae1b14106fd64", "collapsed": true}, "source": ["print(transactions.msno.nunique())\n", "print(train.msno.nunique())"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "9e0151f6-0fcc-46fc-9f5f-f410a7ab3e34", "_uuid": "79968158096e7309ca2054a31585d9b85758c378", "collapsed": true}, "source": ["train_transactions = pd.merge(train, transactions, on='msno', how='left')\n", "train_transactions.head(1)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "2f34bc1b-a47d-46fb-9ae4-a39fbe3591b3", "_uuid": "816019f1f939d3b8b1256ef0498606221b92cc48", "collapsed": true}, "source": ["composite = pd.merge(train_transactions, members, on='msno', how='left')\n", "composite.head(1)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "626ad80a-a984-4e07-badb-7a5abdb9e850", "_uuid": "3af3fea944b67c199109e05d99c00cd31642480a", "collapsed": true}, "source": ["print(composite.msno.nunique())\n", "composite.shape"], "cell_type": "code", "outputs": [], "execution_count": null}, {"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": null}, {"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": null}, {"metadata": {"_cell_guid": "b4e849c7-5957-4609-b663-603d61504535", "_uuid": "dea4cacf0cf51f977011e02421fe411cefc3100a", "collapsed": true}, "source": ["composite.head(1)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "611b3d2a-c034-40f8-b5bc-e8f7d1d239e5", "_uuid": "084fac54f6822fb8d7338c3fb901f9722d2a5e18", "collapsed": true}, "source": ["composite.isnull().sum()"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "e3606711-1e0c-457c-bb47-6c49702ed76e", "_uuid": "880540e554184c7fb0477718f29ebb45cac05442", "collapsed": true}, "source": ["composite.shape"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "aacb3453-4657-4dfd-9226-d33cc714a214", "_uuid": "8cb2e323a6e63f9da3a7f9edc5069405c2ec29bf", "collapsed": true}, "source": ["for col in transactions.columns:\n", "    print(col + ' has ' + str(transactions[col].nunique()) + ' unique_values.')"], "cell_type": "code", "outputs": [], "execution_count": null}, {"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": "a4289612-31a6-49fc-9402-c105e82a5fd4", "_uuid": "1b1826f8e9ac3dab152730302fd2a3adf856bfb5", "collapsed": true}, "source": ["sns.kdeplot(transactions.payment_plan_days)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "d4323b25-3388-455d-bd97-4b9ca9562f57", "_uuid": "322fbdf52b46decad8b8bdab848b644fb53c7a42", "collapsed": true}, "source": ["transactions['payment_plan_days'].hist(bins=20)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "68951548-a4b5-4c33-bf99-e11b6ccfb34c", "_uuid": "aabfa377ca09ac4c2cee6cb74de3df28f2f7396d", "collapsed": true}, "source": ["sns.distplot(transactions.payment_plan_days)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "d1d91231-5354-406a-8ca2-1cfa2dbd0cac", "_uuid": "f8479b8bb141f5c7a9045f64b8f958682f606167", "collapsed": true}, "source": ["sns.distplot(transactions.plan_list_price)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "e57b55b6-e381-424f-93ca-9ac7fcc31a75", "_uuid": "7a3ed078c315cb0efb2051f32ccd3cdec5d6986d", "collapsed": true}, "source": ["sns.distplot(transactions.actual_amount_paid)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "fa005ad9-1608-4155-b48b-56add34648e8", "_uuid": "d6ab1aaea13094b193089e63ed5355f5bc6b6841", "collapsed": true}, "source": ["composite.payment_plan_days.value_counts().head(10)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "cf9622fa-0e24-406c-8274-18599d95f293", "_uuid": "d08ff6e1f9d8173cbf6989ae2270330cf91373eb", "collapsed": true}, "source": ["composite.plan_list_price.value_counts().head(10)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "a49ecd5c-b049-44dd-8a13-df61b75366d1", "_uuid": "77543fff8d646f6cb3758288e867b9b94544fa70", "collapsed": true}, "source": ["composite.actual_amount_paid.value_counts().head(10)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "41fc47ba-c239-42e1-9adf-26017149651f", "_uuid": "1f0d38af93a2ad8f52bfbd0a140ade1d229b0d2f", "collapsed": true}, "source": ["composite['discount'] = (composite.plan_list_price - composite.actual_amount_paid)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "a41d7bd9-d2f7-4644-94fd-1b83585c8308", "_uuid": "de6f97cc10353c21a9a7d3db282f75225cc5ab93", "collapsed": true}, "source": ["composite.discount.value_counts()"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "7236e5ee-ecaa-47f9-8e67-d8a5b5f20992", "_uuid": "9fcdd158a56146e5f827395808c2138d03b9cf60", "collapsed": true}, "source": ["my_list = ['plan_list_price', 'actual_amount_paid', 'payment_plan_days','is_churn']"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "0da24153-9b68-42e7-9edd-7c1f1aeb91e7", "_uuid": "4d64a00f691e9f8edab1a9e35f66c8c8f3ee84a4", "collapsed": true}, "source": ["composite[my_list]"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "a22974da-6f27-4d79-9734-026049363e41", "_uuid": "95b205b91f32fa3f9ea41566374059ba2223afb3", "collapsed": true}, "source": ["a = composite['actual_amount_paid']\n", "b = composite['plan_list_price']\n", "c = composite['payment_plan_days']\n", "d = composite['is_churn']"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "0301881e-722c-4ac5-a20c-dca71808f313", "_uuid": "08e95b3a930b695dfe1ad6bd06bcf75ba7da2fa4", "collapsed": true}, "source": ["plt.scatter(a,d)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "ac806905-1c55-4b41-9dd9-1e4cc659c196", "_uuid": "637f41289c6ae1fa9ae6a65ee49868de4a7cf60a", "collapsed": true}, "source": ["plt.scatter(b,d)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "a6cf8de5-4bfc-4925-9698-3c55e2670ab8", "_uuid": "b6a5cb9e784ee359d3e4c3ee0da9ab1f50ce3b46", "collapsed": true}, "source": ["plt.scatter(c,d)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "b43a8f4b-b48c-4ac8-b616-b623d3749891", "_uuid": "164a41d671e37fef62b58d99d4a81a0d28f6dbee", "collapsed": true}, "source": ["plt.scatter(a,b)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "82fd6982-eb2d-4a32-a944-fe4f123684d3", "_uuid": "01c597e958c20c4445dc2d21d1a6559e3ce281c6", "collapsed": true}, "source": ["plt.scatter(a,c)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "a3dbfd32-c9a4-43f0-8ca3-349cf29d721c", "_uuid": "0d460e21e6594c6cbd7fbcbd6ab81660080caa46", "collapsed": true}, "source": ["plt.scatter(b,c)"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "38c7a492-83b3-4301-b24b-d58080f8c792", "_uuid": "13c1345873ec90034af2d2066b0669cd0a2a80bc", "collapsed": true}, "source": ["payment_plan_days = composite[my_list].groupby('payment_plan_days').agg(['sum','count'])"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "321a7470-5326-4bae-84c6-ddd5ea33ef1f", "_uuid": "c99b6475f58443c0560745e083676bef89f4fa17", "collapsed": true}, "source": ["payment_plan_days"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "85b04e39-c078-4e5a-8ced-97793c448b7e", "_uuid": "88f100a4624f72ac87080e8a276f289cbafaed4c", "collapsed": true}, "source": ["payment_plan_days.groupby(axis=1, level=0).apply(lambda x: x.sum()/ x.count())"], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "2853bf0c-837e-437b-8cb1-2df0936e6bd3", "_uuid": "6afb08dae7f876d2109feb2d72874d5ea02e7c6e", "collapsed": true}, "source": [], "cell_type": "code", "outputs": [], "execution_count": null}, {"metadata": {"_cell_guid": "8a97a8c1-e558-4d86-aa15-66408b34ff09", "_uuid": "2febafe329d9ac66b91e233d696706c7118faedb", "collapsed": true}, "source": ["transactions"], "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}