{"cells": [{"cell_type": "markdown", "source": ["# **1. Load Libraies and check input files**"], "metadata": {"_uuid": "4085d72f79e929955a8698e2b1d3d94a957d73c6", "_cell_guid": "9a3cafd5-77c5-48de-9019-15ac1ad14b14"}}, {"cell_type": "code", "source": ["import numpy as np # linear algebra\n", "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "import time\n", "from datetime import datetime\n", "from collections import Counter\n", "from subprocess import check_output\n", "print(check_output([\"ls\", \"../input\"]).decode(\"utf8\"))"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "fdd9c87cc238baca337ca4b492fed50e2c00609c", "_cell_guid": "fc8c9b24-901c-49bb-9c88-e447c7d91573"}}, {"cell_type": "markdown", "source": ["# **2. Load Data**\n", "\n", "*Please note to avoid memory problems due to large data set of user_logs I am just using 20 million rows for the purpose of data exploration.*"], "metadata": {"_uuid": "949094a2800324f6ba25b3dfa726379a580b9b02", "_cell_guid": "6ad807d0-097c-4848-baf0-c6dba37272d2"}}, {"cell_type": "code", "source": ["train = pd.read_csv('../input/train.csv')\n", "#sample_submission_zero= pd.read_csv('../input/sample_submission_zero.csv')\n", "members = pd.read_csv('../input/members.csv')\n", "transactions = pd.read_csv('../input/transactions.csv')\n", "#user_logs = pd.read_csv('../input/user_logs.csv',nrows = 2e7)\n", "\n"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "7411c1b94135148442008b4e9e6959b0b7c82a57", "_cell_guid": "a322e34a-9c6e-4f5b-98f3-43afe21fab80", "collapsed": true}}, {"cell_type": "markdown", "source": ["# **3. File Structure and data exploration**\n", "\n", "Let us first explore train data set"], "metadata": {"_uuid": "0d02672ec6b99285719620ed1f102e2c53324184", "_cell_guid": "cc7d3031-e517-43c8-9104-8691adf25825"}}, {"cell_type": "code", "source": ["train.head()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "710050fe6f96a5b319a17a126f60fe597bc2c209", "_cell_guid": "f9f5d725-5555-410d-87b7-5c723cd3f748"}}, {"cell_type": "code", "source": ["train.info()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "90260c9a619a0282032ae1c00bec7781707df251", "_cell_guid": "ac97cecf-4cc6-4021-b588-677e7a4a74c0"}}, {"cell_type": "code", "source": ["train.describe()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "30d67b31fa5e8209e7d48acffa913e5890052dcb", "_cell_guid": "15fa7f87-cd46-441a-84b4-30efdb2cdcd7"}}, {"cell_type": "markdown", "source": ["So train file contains 992931 user-ids(msno) with the binary classification as 1 (churn) and 0 ( no churn).  Also the in training set majority users are in no churn category (0) - about  94 % and about 6% are accounting to churn. \n", "\n", "So as per the training set majority user are going for renewal. This information can be good news or bad news( very biased training set), we have to se it later while predicting and submitting for the score. \n", "\n", "Next I will merge training set with members data set to explore more into training data sets."], "metadata": {"_uuid": "f9358ecf9374e6a239bcfd8e0f31673d3f76f468", "_cell_guid": "90890d11-e3a4-4329-a131-499ff8f1a637"}}, {"cell_type": "code", "source": ["training = pd.merge(left = train,right = members,how = 'left',on=['msno'])\n", "training.head()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "dea03cfe8d187e847ee719012fec474814d633c3", "_cell_guid": "285d4d1a-782f-471a-bc13-5481aacfb1bf"}}, {"cell_type": "code", "source": ["training.info()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "5bbf2159d79845238a5ab99d80c7ea2168eb61e0", "_cell_guid": "d035c040-cc29-4ddf-a045-79e5eb1260b2"}}, {"cell_type": "markdown", "source": ["We see that in the new merged data sets, the maximum non-null entries apart from is_churn is 876143 (City, bd, registered_via, registration_init_time, expiration_date). So there are 116788 entries which dosen't have any data ( about 12%). Also there is concern in gender columns as about 60% null entries."], "metadata": {"_uuid": "cc4a53b0c19c6fb10722deb4d1c30301499ecc0f", "_cell_guid": "cfa988ae-e6a9-4b09-a61f-fb02049b3a25"}}, {"cell_type": "markdown", "source": ["Changing the format of city and registered_via( except missing values) from float to int and changing blank values with NAN( for city, registered_via and gender)"], "metadata": {"_uuid": "9f3b0ea8c809d2f224838e7f708674070de28aa2", "_cell_guid": "cb5bebcf-f9b3-46e8-8023-3b21f0b8ddf8"}}, {"cell_type": "code", "source": ["training['city'] = training.city.apply(lambda x: int(x) if pd.notnull(x) else \"NAN\")\n", "training['registered_via'] = training.registered_via.apply(lambda x: int(x) if pd.notnull(x) else \"NAN\")\n", "training['gender']=training['gender'].fillna(\"NAN\")\n", "training.info()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "c02c442458c9c58cb63bb7f194b15d156bbc0ebf", "_cell_guid": "40b7a0ae-86b8-481b-a270-79c4cecdea6e"}}, {"cell_type": "markdown", "source": ["**Changing the format of dates in YYYY-MM-DD**"], "metadata": {}}, {"cell_type": "code", "source": ["training['registration_init_time'] = training.registration_init_time.apply(lambda x: datetime.strptime(str(int(x)), \"%Y%m%d\").date() if pd.notnull(x) else \"NAN\" )\n", "training['expiration_date'] = training.expiration_date.apply(lambda x: datetime.strptime(str(int(x)), \"%Y%m%d\").date() if pd.notnull(x) else \"NAN\")\n", "training.head()"], "outputs": [], "execution_count": null, "metadata": {}}, {"cell_type": "markdown", "source": ["**Data Exploration in Training data ( merged data set of train and members)**"], "metadata": {"_uuid": "a8c1af8f15ae72776326432566079456aa1a8164", "_cell_guid": "a9382c92-1fb4-4879-8989-867c94efffb5"}}, {"cell_type": "code", "source": ["# City count\n", "plt.figure(figsize=(12,12))\n", "plt.subplot(411)\n", "city_order = training['city'].unique()\n", "city_order=sorted(city_order, key=lambda x: float(x))\n", "sns.countplot(x=\"city\", data=training , order = city_order)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('City', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of City Count\", fontsize=12)\n", "plt.show()\n", "city_count = Counter(training['city']).most_common()\n", "print(\"City Count \" +str(city_count))\n", "\n", "#Registered Via Count\n", "plt.figure(figsize=(12,12))\n", "plt.subplot(412)\n", "R_V_order = training['registered_via'].unique()\n", "R_V_order = sorted(R_V_order, key=lambda x: str(x))\n", "R_V_order = sorted(R_V_order, key=lambda x: float(x))\n", "#above repetion of commands are very silly, but this was the only way I was able to diplay what I wanted\n", "sns.countplot(x=\"registered_via\", data=training,order = R_V_order)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('Registered Via', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of Registered Via Count\", fontsize=12)\n", "plt.show()\n", "RV_count = Counter(training['registered_via']).most_common()\n", "print(\"Registered Via Count \" +str(RV_count))\n", "\n", "#Gender count\n", "plt.figure(figsize=(12,12))\n", "plt.subplot(413)\n", "sns.countplot(x=\"gender\", data=training)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('Gender', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of Gender Count\", fontsize=12)\n", "plt.show()\n", "gender_count = Counter(training['gender']).most_common()\n", "print(\"Gender Count \" +str(gender_count))\n", "\n"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "a04f626e84622b72fde49377dc4200186b97c3aa", "_cell_guid": "ccd933a2-8ab5-4f4b-80b1-6541af60d879"}}, {"cell_type": "markdown", "source": ["** registration_init_time Trends exploration**"], "metadata": {}}, {"cell_type": "code", "source": ["#registration_init_time yearly trend\n", "training['registration_init_time_year'] = pd.DatetimeIndex(training['registration_init_time']).year\n", "training['registration_init_time_year'] = training.registration_init_time_year.apply(lambda x: int(x) if pd.notnull(x) else \"NAN\" )\n", "year_count=training['registration_init_time_year'].value_counts()\n", "#print(year_count)\n", "plt.figure(figsize=(12,12))\n", "plt.subplot(311)\n", "year_order = training['registration_init_time_year'].unique()\n", "year_order=sorted(year_order, key=lambda x: str(x))\n", "year_order = sorted(year_order, key=lambda x: float(x))\n", "sns.barplot(year_count.index, year_count.values,order=year_order)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('Year', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Yearly Trend of registration_init_time\", fontsize=12)\n", "plt.show()\n", "year_count_2 = Counter(training['registration_init_time_year']).most_common()\n", "print(\"Yearly Count \" +str(year_count_2))\n", "\n", "#registration_init_time monthly trend\n", "training['registration_init_time_month'] = pd.DatetimeIndex(training['registration_init_time']).month\n", "training['registration_init_time_month'] = training.registration_init_time_month.apply(lambda x: int(x) if pd.notnull(x) else \"NAN\" )\n", "month_count=training['registration_init_time_month'].value_counts()\n", "plt.figure(figsize=(12,12))\n", "plt.subplot(312)\n", "month_order = training['registration_init_time_month'].unique()\n", "month_order = sorted(month_order, key=lambda x: str(x))\n", "month_order = sorted(month_order, key=lambda x: float(x))\n", "sns.barplot(month_count.index, month_count.values,order=month_order)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('Month', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Monthly Trend of registration_init_time\", fontsize=12)\n", "plt.show()\n", "month_count_2 = Counter(training['registration_init_time_month']).most_common()\n", "print(\"Monthly Count \" +str(month_count_2))\n", "\n", "#registration_init_time day wise trend\n", "training['registration_init_time_weekday'] = pd.DatetimeIndex(training['registration_init_time']).weekday_name\n", "training['registration_init_time_weekday'] = training.registration_init_time_weekday.apply(lambda x: str(x) if pd.notnull(x) else \"NAN\" )\n", "day_count=training['registration_init_time_weekday'].value_counts()\n", "plt.figure(figsize=(12,12))\n", "plt.subplot(313)\n", "#day_order = training['registration_init_time_day'].unique()\n", "day_order = ['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday','NAN']\n", "sns.barplot(day_count.index, day_count.values,order=day_order)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('Day', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Day-wise Trend of registration_init_time\", fontsize=12)\n", "plt.show()\n", "day_count_2 = Counter(training['registration_init_time_weekday']).most_common()\n", "print(\"Day-wise Count \" +str(day_count_2))"], "outputs": [], "execution_count": null, "metadata": {}}, {"cell_type": "markdown", "source": ["**Observation:**\n", "* There are total of 21 Cities Encoded ( there is no City \"2\" in the data set). This can be one-hot encoded.\n", "* There are Class of \"3\", \"4\", \"7\", \"9\", \"13\" listed as registration method.  Kindly note that there is additional \"10\", and \"16\" class of cities listed in Member Data set but there are missing when we merged the data set **( see below)**. This can be one-hot encoded.\n", "* There are almost equal percentage of Male and Female, but about 60% of data is missing in gender field. We have see how to fill the missing entries or label them as third category. ( Not so sure about this, this can tuned while predicting and submission)\n", "* Registration trend has inncreased yearly, though there was a dip in 2014. Due to data upto few months in 2017, there is a dip.\n", "* Registration monthly trends are high in year end and year starting months. In between there is a smooth valley formation. \n", "* Registration daily trends are high on weekends. "], "metadata": {"_uuid": "1968f4af534df7564e58ebba89780d4477841d8b", "_cell_guid": "f82c82cb-9873-472f-a0b8-3a368c92701d"}}, {"cell_type": "markdown", "source": ["**Data Exploration in members data ( just for comparison with training merged dataset )**"], "metadata": {"_uuid": "0620ad4bb842fa51c896a1543e459aad59c33875", "_cell_guid": "4f743580-1acd-4914-ba5e-418313863307"}}, {"cell_type": "code", "source": ["members.info()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "af40d8720053dc0e3a27192a41dd65d8c1449e79", "_cell_guid": "b66a4278-c3e7-42ab-bab0-1242dd0ab000", "collapsed": true}}, {"cell_type": "code", "source": ["# City count in Members Data Set\n", "plt.figure(figsize=(12,12))\n", "plt.subplot(311)\n", "sns.countplot(x=\"city\", data=members)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('City', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of City Count in Members Data Set\", fontsize=12)\n", "plt.show()\n", "city_count = Counter(members['city']).most_common()\n", "print(\"City Count \" +str(city_count))\n", "\n", "#Registered Via Count in Members Data Set\n", "plt.figure(figsize=(12,12))\n", "plt.subplot(312)\n", "sns.countplot(x=\"registered_via\", data=members)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('Registered Via', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of Registered Via Count in Members Data Set\", fontsize=12)\n", "plt.show()\n", "RV_count = Counter(members['registered_via']).most_common()\n", "print(\"Registered Via Count \" +str(RV_count))\n", "\n", "\n", "#Gender count in Members Data Set\n", "plt.figure(figsize=(12,12))\n", "plt.subplot(313)\n", "sns.countplot(x=\"gender\", data=members)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('Gender', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of Gender Count in Members Data Set\", fontsize=12)\n", "plt.show()\n", "gender_count = Counter(members['gender']).most_common()\n", "print(\"Gender Count \" +str(gender_count))\n"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "06235b2805980090767cd180e9eacd505b3cd37a", "_cell_guid": "158a5753-3632-4f70-9463-9fc029621e31", "collapsed": true}}, {"cell_type": "markdown", "source": ["For birth date column there are many outliers present, as it is mentioned in the data section that \"column has outlier values ranging from -7000 to 2015, please use your judgement\". But I think age would be an important factor, have  clean the data to make sense of it.\n", "\n", "Let us check "], "metadata": {"_uuid": "5cd51dd4d1fcb426331ed6a91246273105404233", "_cell_guid": "f6da4540-2e49-4454-ac4b-a2be116ffdfa"}}, {"cell_type": "code", "source": ["tmp_1=training.bd.value_counts()\n", "tmp_1.head()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "5e819ba2ec1f32413ace955b55cc490cedfa3e86", "_cell_guid": "ebd4967d-befd-4ed6-b5ee-76db85c936a5", "collapsed": true}}, {"cell_type": "code", "source": ["training['bd'] = training.bd.apply(lambda x: int(x) if pd.notnull(x) else \"NAN\" )\n", "bd_count = Counter(training['bd']).most_common()\n", "print(\"BD Count \" +str(bd_count))"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "4a5740ca6bc79f7bc398cbea141366c0161967cc", "_cell_guid": "6a62a10f-a06f-4291-9879-2ce89429f874", "collapsed": true}}, {"cell_type": "markdown", "source": ["* First we can make all Birth date <= 1 to -99999( just a large -ve number) as I don't think it would make a difference\n", "* Next we can also ignore the Birth Date >= 100.\n"], "metadata": {"_uuid": "5c2c44ca3943295f8a35963d5d6fed22256f87aa", "_cell_guid": "828d1f15-892d-4318-a1d9-7aefff38ac21"}}, {"cell_type": "code", "source": ["#training.loc[(training['bd'] <= 1), 'bd'] = -99999\n", "#training.loc[(training['bd'] >= 100), 'bd'] = -99999\n", "training['bd'] = training.bd.apply(lambda x: -99999 if float(x)<=1 else x )\n", "training['bd'] = training.bd.apply(lambda x: -99999 if float(x)>=100 else x )"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "957231d676e7ec2570af99cdf793b0807b9f8609", "_cell_guid": "abe6067a-ac4f-40fc-a865-013d728b3787", "collapsed": true}}, {"cell_type": "code", "source": ["#Birth Date count in training Data Set\n", "plt.figure(figsize=(12,8))\n", "bd_order = training['bd'].unique()\n", "bd_order = sorted(bd_order, key=lambda x: str(x))\n", "bd_order = sorted(bd_order, key=lambda x: float(x))\n", "#above repetion of commands are very silly, but this was the only way I was able to diplay what I wanted\n", "sns.countplot(x=\"bd\", data=training , order = bd_order)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('BD', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of BD Count\", fontsize=12)\n", "plt.show()\n", "bd_count = Counter(training['bd']).most_common()\n", "print(\"BD Count \" +str(bd_count))"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "e515e81af2cf73491aae3e45ab885ba87646e6a0", "_cell_guid": "46d43f58-bd00-4655-bce2-48ef748b1471", "collapsed": true}}, {"cell_type": "markdown", "source": ["Birth Date Visualization without ouliers and NAN values"], "metadata": {"_uuid": "eab1a4393f59d80aa06ed7d2761085a27884239e", "_cell_guid": "8e653bd9-9db7-4acd-b5e2-a05aba47c537"}}, {"cell_type": "code", "source": ["tmp_bd = training[(training.bd != \"NAN\") & (training.bd != -99999)]\n", "print(\"Mean of Birth Date = \" +str(np.mean(tmp_bd['bd'])))\n", "print(\"Median of Birth Date = \" +str(np.median(tmp_bd['bd'])))\n", "#print(\"Mode of Birth Date = \" +str(np.mode(tmp_bd['bd'])))\n", "plt.figure(figsize=(12,8))\n", "plt.subplot(211)\n", "bd_order_2 = tmp_bd['bd'].unique()\n", "bd_order_2 = sorted(bd_order_2, key=lambda x: float(x))\n", "sns.countplot(x=\"bd\", data=tmp_bd , order = bd_order_2)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('BD', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of BD Count without ouliers and NAN values\", fontsize=12)\n", "plt.show()\n", "\n", "plt.figure(figsize=(4,12))\n", "plt.subplot(212)\n", "sns.boxplot(y=tmp_bd[\"bd\"],data=tmp_bd)\n", "plt.xlabel('BD', fontsize=12)\n", "plt.title(\"Box Plot of Birth Date without ouliers and NAN values\", fontsize=12)\n", "plt.show()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "a3341a84fb344bb07f5adee81bbe696d6e19cfd6", "_cell_guid": "14265a03-84ed-4ebc-9a04-dcfd44f2af3d", "collapsed": true}}, {"cell_type": "markdown", "source": ["So mostly we see that birth date is in-between 15-50 years, excluding the outliers( about 49% ) and NA's(12%).\n", "\n", "*Mean = 29.7742011546, Median = 28.0*"], "metadata": {"_uuid": "2f65155c64b219566b93a566e573fc20c8109ebf", "_cell_guid": "83f38f8b-da04-4136-a160-572bd44bdebb"}}, {"cell_type": "markdown", "source": ["**Relation of between train data set and members Data set**\n", "\n", "Let us try to understand if there is any relation between train data set and members data set"], "metadata": {"_uuid": "8fe1675262881d4960d6c8a932032cd62cf4a9ac", "_cell_guid": "96685f25-0751-43b1-8be2-e6af0d3dfa35"}}, {"cell_type": "code", "source": ["#Gender\n", "gender_crosstab=pd.crosstab(training['gender'],training['is_churn'])\n", "gender_crosstab.plot(kind='bar', stacked=True, grid=True)\n", "gender_crosstab[\"Ratio\"] =  gender_crosstab[1] / gender_crosstab[0]\n", "gender_crosstab"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "4f81ae30a3cf2264cbe039cf48276795c75a837b", "_cell_guid": "43e50c5b-c45e-426d-8a31-26dc0afde6eb", "collapsed": true}}, {"cell_type": "code", "source": ["#Registered Via\n", "registered_via_crosstab=pd.crosstab(training['registered_via'],training['is_churn'])\n", "registered_via_crosstab.plot(kind='bar', stacked=True, grid=True)\n", "registered_via_crosstab[\"Ratio\"] =  registered_via_crosstab[1] / registered_via_crosstab[0]\n", "registered_via_crosstab"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "49704a81fb46eb2abfaaaed05c066993f55129cb", "_cell_guid": "b63edb01-829e-4cc0-8131-d7c088076364", "collapsed": true}}, {"cell_type": "code", "source": ["#city\n", "city_crosstab=pd.crosstab(training['city'],training['is_churn'])\n", "city_crosstab.plot(kind='bar', stacked=True, grid=True)\n", "city_crosstab[\"Ratio\"] =  city_crosstab[1] / city_crosstab[0]\n", "city_crosstab"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "9c8f5d2dc49789aa5e6cda9568a4a6754ba89ed6", "_cell_guid": "997c74ac-54b9-47bf-91ac-0ea56e502c59", "collapsed": true}}, {"cell_type": "code", "source": ["#Birth Date\n", "sns.boxplot(x=tmp_bd[\"is_churn\"],y=tmp_bd[\"bd\"],data=tmp_bd);\n", "del tmp_bd # memory cleaning"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "3c0e7e0afe403992fb56f186e5dff6fe1fc5b585", "_cell_guid": "6b30e467-dfc6-4eac-ae60-c471a81602db", "collapsed": true}}, {"cell_type": "markdown", "source": ["Next, let us now explore **transactions data** set"], "metadata": {"_uuid": "68eb6dff4bdca2c55239faff15504f4628cafc5f", "_cell_guid": "c8e3b529-c487-4af7-89e3-cbe97a3f0bc2", "collapsed": true}}, {"cell_type": "code", "source": ["transactions.head()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "11a91190ae8a5158b9a0f72daaa97bb525df53f2", "_cell_guid": "de9d5854-5012-463f-9a11-0642e7b13975", "collapsed": true}}, {"cell_type": "code", "source": ["transactions.info()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "5b6aa767d7beff8162d372a6754eb694a0123d34", "_cell_guid": "967940e3-5c46-4849-a670-3377ab61a987", "collapsed": true}}, {"cell_type": "code", "source": ["transactions.describe()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "71cb08c4b0916435dd9f09690310d3c85338b9b8", "_cell_guid": "9483a285-c55d-42c0-bc93-eff68902df5d", "collapsed": true}}, {"cell_type": "markdown", "source": ["Lets us see the transaction data to check the range of values, later we can check the same after merging it with above training set"], "metadata": {"_uuid": "3a882bdb88fa96d04a588ef2d6e088ee29b5a242", "_cell_guid": "ec76e5dc-5a52-4b41-819d-108283f80e7c"}}, {"cell_type": "code", "source": ["# payment_method_id count in transactions Data Set\n", "plt.figure(figsize=(18,6))\n", "#plt.subplot(311)\n", "sns.countplot(x=\"payment_method_id\", data=transactions)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('payment_method_id', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of payment_method_id Count in transactions Data Set\", fontsize=12)\n", "plt.show()\n", "payment_method_id_count = Counter(transactions['payment_method_id']).most_common()\n", "print(\"payment_method_id Count \" +str(payment_method_id_count))\n"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "844029fc7973a05953133a460a2417bb1c71179d", "_cell_guid": "637fa635-5a65-488b-a54e-7973f77f9b2f", "collapsed": true}}, {"cell_type": "code", "source": ["# payment_plan_days count in transactions Data Set\n", "plt.figure(figsize=(18,6))\n", "sns.countplot(x=\"payment_plan_days\", data=transactions)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('payment_plan_days', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of payment_plan_days Count in transactions Data Set\", fontsize=12)\n", "plt.show()\n", "payment_plan_days_count = Counter(transactions['payment_plan_days']).most_common()\n", "print(\"payment_plan_days Count \" +str(payment_plan_days_count))\n"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "7341a3491254254a006ac46f0cc57ce5502729b8", "_cell_guid": "974d0b5f-a5ab-4788-a358-98efe84b834b", "collapsed": true}}, {"cell_type": "code", "source": ["# plan_list_price count in transactions Data Set\n", "plt.figure(figsize=(18,6))\n", "sns.countplot(x=\"plan_list_price\", data=transactions)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('plan_list_price', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of plan_list_price Count in transactions Data Set\", fontsize=12)\n", "plt.show()\n", "plan_list_price_count = Counter(transactions['plan_list_price']).most_common()\n", "print(\"plan_list_price Count \" +str(plan_list_price_count))\n"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "07a6d92fa7cdeffaf18f8d430747b199e367c4d6", "_cell_guid": "fc4bff2b-ca2b-4fda-9e10-536c5ff7f9b9", "collapsed": true}}, {"cell_type": "code", "source": ["# actual_amount_paid count in transactions Data Set\n", "plt.figure(figsize=(18,6))\n", "sns.countplot(x=\"actual_amount_paid\", data=transactions)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('actual_amount_paid', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of actual_amount_paid Count in transactions Data Set\", fontsize=12)\n", "plt.show()\n", "actual_amount_paid_count = Counter(transactions['actual_amount_paid']).most_common()\n", "print(\"actual_amount_paid Count \" +str(actual_amount_paid_count))"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "df111b66309ebe201fe98eed4d6e1c1e286a9a72", "_cell_guid": "8707503b-dd8d-447e-92ff-249454f2c8fb", "collapsed": true}}, {"cell_type": "code", "source": ["# is_auto_renew count in transactions Data Set\n", "plt.figure(figsize=(4,4))\n", "sns.countplot(x=\"is_auto_renew\", data=transactions)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('is_auto_renew', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of is_auto_renew Count in transactions Data Set\", fontsize=6)\n", "plt.show()\n", "is_auto_renew_count = Counter(transactions['is_auto_renew']).most_common()\n", "print(\"is_auto_renew Count \" +str(is_auto_renew_count))"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "ad2daa434b953514ba517a4155c1f71119de2e43", "_cell_guid": "87e78a3d-165a-48f3-a0e8-177b36326086", "collapsed": true}}, {"cell_type": "code", "source": ["# is_cancel count in transactions Data Set\n", "plt.figure(figsize=(4,4))\n", "sns.countplot(x=\"is_cancel\", data=transactions)\n", "plt.ylabel('Count', fontsize=12)\n", "plt.xlabel('is_cancel', fontsize=12)\n", "plt.xticks(rotation='vertical')\n", "plt.title(\"Frequency of is_cancel Count in transactions Data Set\", fontsize=6)\n", "plt.show()\n", "is_cancel_count = Counter(transactions['is_cancel']).most_common()\n", "print(\"is_cancel Count \" +str(is_cancel_count))"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "e3dda58649a9894bec526a7be76efee1fd06555e", "_cell_guid": "f8d79d8f-cfaf-402f-b78e-8c4249d61a77", "collapsed": true}}, {"cell_type": "markdown", "source": ["*I will be changing the dates format in transaction date after merging with training data set as it is taking too much time to convert the format.*"], "metadata": {"_uuid": "1ddc2a53a66f116ad39c650cc8bd4479669e120f", "_cell_guid": "a2619396-16bd-4cd8-bf5f-a307f11610f9"}}, {"cell_type": "code", "source": ["#Changing the format of dates in YYYY-MM-DD in transaction data set\n", "#transactions['transaction_date'] = transactions.transaction_date.apply(lambda x: datetime.strptime(str(int(x)), \"%Y%m%d\").date())\n", "#transactions['membership_expire_date'] = transactions.membership_expire_date.apply(lambda x: datetime.strptime(str(int(x)), \"%Y%m%d\").date())\n", "#transactions.head()"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "ea6e3ad6ac424cac1b6071e1cec75b0379f91de6", "_cell_guid": "4a812a68-1f0b-4c55-a85a-80bec26d2a5b", "collapsed": true}}, {"cell_type": "markdown", "source": ["**Observation**\n", "* So there are 21547746 entries in trasactions data set, as compared to 992931 entries in training set ( about 5%)\n", "* There are 40 payment methods( method class 9 is missing) and majority of users use payment method id 41\n", "* There are 37 payment plan days, out of which 30 day plan is very frequent. This is quite understandable as most people will take monthly subscription\n", "* There are 51 payment plan, out of which 149 one is most frequent.\n", "* Amount paid also have same 51 types and 149 is the most frequent one. Also there is 93% correlation in Payment plan and Actual Amount Paid, so almost same. (***see below for correlation)***\n", "* About 85% users have set their plan for Auto Renewal\n", "* About 4% users have canceled the subscription during the plan period"], "metadata": {"_uuid": "0d8851bb73e8de39a90622876e225a0d891e3ab8", "_cell_guid": "6a7a65b1-d43d-4b09-96db-09eef0ed4e79"}}, {"cell_type": "code", "source": ["#Correlation between plan_list_price and actual_amount_paid\n", "transactions['plan_list_price'].corr(transactions['actual_amount_paid'],method='pearson')                           "], "outputs": [], "execution_count": null, "metadata": {"_uuid": "af082329ed7e5b129e8776f5995f6cbd64c735c9", "scrolled": true, "_cell_guid": "9a653b1b-9365-4cd7-bd9b-d865e0f21b3d", "collapsed": true}}, {"cell_type": "markdown", "source": ["Let us see whether msno(users ids) are unique in transaction data set."], "metadata": {"_uuid": "79159d39467a9f345031499d6a9103f58b3c8882", "_cell_guid": "777909fb-2a52-4a65-84b9-ad04a7e5c3aa"}}, {"cell_type": "code", "source": ["#transactions['msno'].value_counts() \n", "(transactions['msno'].value_counts().reset_index())['msno'].value_counts()"], "outputs": [], "execution_count": null, "metadata": {}}, {"cell_type": "markdown", "source": ["So there are also more than one entries of many users, maybe having different payment plans and different transaction period. Lets us see data of one such user with 8 entries. "], "metadata": {"_uuid": "294962246e4951c83b8acc9b2a6476e88fcc58b8", "_cell_guid": "12ca95f3-7b0e-4579-9fdd-738128643cdb"}}, {"cell_type": "code", "source": ["#tmp1=transactions['msno'].value_counts() \n", "#tmp2=tmp1[tmp1==8]\n", "#print(tmp2.head(1))\n", "#del tmp1, tmp2"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "aaf44e18ed28c33d892a74592702f45b78c7a1a9", "_cell_guid": "fe9dc995-2193-49cd-a167-d782d5a2043a", "collapsed": true}}, {"cell_type": "markdown", "source": ["Transactions details of User with msno : \"**LNScSgIQZsX+hC3eVrwGFcdan0nftusOwk0jMAu7q9I= **\"   "], "metadata": {"_uuid": "4f2ad489413763aaceefcebcce47ae9d2d9130e8", "_cell_guid": "2d625eca-b60d-4df6-93f7-0aff487c39da"}}, {"cell_type": "code", "source": ["tmp1=transactions[transactions.msno==\"LNScSgIQZsX+hC3eVrwGFcdan0nftusOwk0jMAu7q9I=\"]\n", "tmp1=tmp1.sort_values('transaction_date')\n", "tmp1"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "7449c61a5a8372445851a5ebc70ee65fc7fe39db", "_cell_guid": "5bc3a8da-fb4b-4f3a-9fb3-e3ed37fd84d3", "collapsed": true}}, {"cell_type": "code", "source": ["del tmp1 # memory cleaning"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "cdde8f778dd77f3fd292e8106c62c3907fd6f85c", "_cell_guid": "6255323f-1055-46d7-8642-4c9a4b13181c", "collapsed": true}}, {"cell_type": "markdown", "source": ["So we have obeserved that same user can have multiple payment plan with different subcription time. So the above user was in 150 plan for three months and then moved to 180 plan."], "metadata": {"_uuid": "b292d6fcae5f356aa6144944b51757d9074745ff", "_cell_guid": "496ddaa2-e935-41f3-b2e9-867bfed5bbfd"}}, {"cell_type": "markdown", "source": ["So far so good, let us now merge the transaction data set with training data set.\n", "\n", "*Please note we are predicting churn or no churn for the month of April 2017 by training on the data of March 2017. By merging these two dataset we might see the same user with multiple subscription having is_churn field same for every subscription time. So we might have to filter merged training set. I will demonstrate this below.*"], "metadata": {"_uuid": "31eb53dcc64d2225f6cbd18c8608aed79507d2d0", "_cell_guid": "3b974748-d2da-41f4-84b5-eb30500dbca6"}}, {"cell_type": "code", "source": ["#merging the training and transaction data set\n", "training = pd.merge(left = training,right = transactions ,how = 'left',on=['msno'])\n", "\n", "#changing the format of the dates\n", "training['transaction_date'] = training.transaction_date.apply(lambda x: datetime.strptime(str(int(x)), \"%Y%m%d\").date() if pd.notnull(x) else \"NAN\" )\n", "training['membership_expire_date'] = training.membership_expire_date.apply(lambda x: datetime.strptime(str(int(x)), \"%Y%m%d\").date() if pd.notnull(x) else \"NAN\")\n", "training.head()\n"], "outputs": [], "execution_count": null, "metadata": {"_uuid": "ca98c9dcc5520f362ed24e7de01e533a733a194e", "_cell_guid": "4919722e-ebec-4b27-b74c-cb4ea3e99eed", "collapsed": true}}, {"cell_type": "markdown", "source": ["# Work in progress (more exploration to follow). \n", "\n", "**Please visit again and comment for any valuble inputs as it is my first EDA.**\n", "\n", "Upvote if you find this helpful."], "metadata": {"_uuid": "b85013eb8a3f243f50753617fb65fbf57e2fd82c", "_cell_guid": "876e781a-fd7f-4e6b-b87a-4608c2fcb740"}}], "nbformat": 4, "nbformat_minor": 1, "metadata": {"kernelspec": {"language": "python", "name": "python3", "display_name": "Python 3"}, "language_info": {"name": "python", "file_extension": ".py", "version": "3.6.1", "codemirror_mode": {"version": 3, "name": "ipython"}, "mimetype": "text/x-python", "pygments_lexer": "ipython3", "nbconvert_exporter": "python"}}}