Part_1_Data Preparation and Exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8ed688e5",
   "metadata": {},
   "source": [
    "<h1 style=\"text-align:center\">Telecom Customer Churn</h1>\n",
    "\n",
    "## Part 1: Data Exploration and Preparation \n",
    "1. Exploring the datasets\n",
    "2. Creating a database tables form the datasets\n",
    "3. Joining the tables in database\n",
    "4. Preparing data for further analysis\n",
    "5. Writing the final dataframe to a .csv file"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40d3f029",
   "metadata": {},
   "source": [
    "---\n",
    "### 1. Reading CSV files with pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1fde737a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing required library\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "886f50bf",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>SeniorCitizen</th>\n",
       "      <th>Partner</th>\n",
       "      <th>Dependents</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3668-QPYBK</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9237-HQITU</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9305-CDSKC</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7892-POOKP</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0280-XJGEX</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID  Gender SeniorCitizen Partner Dependents\n",
       "0  3668-QPYBK    Male            No      No         No\n",
       "1  9237-HQITU  Female            No      No        Yes\n",
       "2  9305-CDSKC  Female            No      No        Yes\n",
       "3  7892-POOKP  Female            No     Yes        Yes\n",
       "4  0280-XJGEX    Male            No      No        Yes"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# importing customer.csv file\n",
    "customer = pd.read_csv(\"customer.csv\")\n",
    "customer.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "be2cd9c1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CustomerID       object\n",
      "Gender           object\n",
      "SeniorCitizen    object\n",
      "Partner          object\n",
      "Dependents       object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# validating the datatypes of customer dataframe\n",
    "print(customer.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cb8a94fe",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Cust_ID</th>\n",
       "      <th>State</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3668-QPYBK</td>\n",
       "      <td>California</td>\n",
       "      <td>33.964131</td>\n",
       "      <td>-118.272783</td>\n",
       "      <td>90003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9237-HQITU</td>\n",
       "      <td>California</td>\n",
       "      <td>34.059281</td>\n",
       "      <td>-118.307420</td>\n",
       "      <td>90005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9305-CDSKC</td>\n",
       "      <td>California</td>\n",
       "      <td>34.048013</td>\n",
       "      <td>-118.293953</td>\n",
       "      <td>90006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7892-POOKP</td>\n",
       "      <td>California</td>\n",
       "      <td>34.062125</td>\n",
       "      <td>-118.315709</td>\n",
       "      <td>90010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0280-XJGEX</td>\n",
       "      <td>California</td>\n",
       "      <td>34.039224</td>\n",
       "      <td>-118.266293</td>\n",
       "      <td>90015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Cust_ID       State   Latitude   Longitude  ZipCode\n",
       "0  3668-QPYBK  California  33.964131 -118.272783    90003\n",
       "1  9237-HQITU  California  34.059281 -118.307420    90005\n",
       "2  9305-CDSKC  California  34.048013 -118.293953    90006\n",
       "3  7892-POOKP  California  34.062125 -118.315709    90010\n",
       "4  0280-XJGEX  California  34.039224 -118.266293    90015"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# importing cust_loc.csv file\n",
    "cust_loc = pd.read_csv(\"cust_loc.csv\")\n",
    "cust_loc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3965f48b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cust_ID       object\n",
      "State         object\n",
      "Latitude     float64\n",
      "Longitude    float64\n",
      "ZipCode        int64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# validating the datatypes of cust_loc dataframe\n",
    "print(cust_loc.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d6686dfd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Cust_ID</th>\n",
       "      <th>PhoneService</th>\n",
       "      <th>MultipleLines</th>\n",
       "      <th>InternetService</th>\n",
       "      <th>OnlineSecurity</th>\n",
       "      <th>OnlineBackup</th>\n",
       "      <th>DeviceProtection</th>\n",
       "      <th>TechSupport</th>\n",
       "      <th>StreamingTV</th>\n",
       "      <th>StreamingMovies</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3668-QPYBK</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>DSL</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9237-HQITU</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>Fiber optic</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9305-CDSKC</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Fiber optic</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7892-POOKP</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Fiber optic</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0280-XJGEX</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Fiber optic</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Cust_ID PhoneService MultipleLines InternetService OnlineSecurity  \\\n",
       "0  3668-QPYBK          Yes            No             DSL            Yes   \n",
       "1  9237-HQITU          Yes            No     Fiber optic             No   \n",
       "2  9305-CDSKC          Yes           Yes     Fiber optic             No   \n",
       "3  7892-POOKP          Yes           Yes     Fiber optic             No   \n",
       "4  0280-XJGEX          Yes           Yes     Fiber optic             No   \n",
       "\n",
       "  OnlineBackup DeviceProtection TechSupport StreamingTV StreamingMovies  \n",
       "0          Yes               No          No          No              No  \n",
       "1           No               No          No          No              No  \n",
       "2           No              Yes          No         Yes             Yes  \n",
       "3           No              Yes         Yes         Yes             Yes  \n",
       "4          Yes              Yes          No         Yes             Yes  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# importing the cust_services.csv file\n",
    "cust_services = pd.read_csv(\"cust_services.csv\")\n",
    "cust_services.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "59beb527",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cust_ID             object\n",
      "PhoneService        object\n",
      "MultipleLines       object\n",
      "InternetService     object\n",
      "OnlineSecurity      object\n",
      "OnlineBackup        object\n",
      "DeviceProtection    object\n",
      "TechSupport         object\n",
      "StreamingTV         object\n",
      "StreamingMovies     object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# validating the datatypes of cust_services dataframe\n",
    "print(cust_services.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6439be3e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account_id</th>\n",
       "      <th>Tenure</th>\n",
       "      <th>Contract</th>\n",
       "      <th>PaymentMethod</th>\n",
       "      <th>PaperlessBilling</th>\n",
       "      <th>MonthlyCharges</th>\n",
       "      <th>TotalCharges</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3668-QPYBK</td>\n",
       "      <td>2</td>\n",
       "      <td>Month-to-month</td>\n",
       "      <td>Mailed0check</td>\n",
       "      <td>Yes</td>\n",
       "      <td>53.85</td>\n",
       "      <td>108.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9237-HQITU</td>\n",
       "      <td>2</td>\n",
       "      <td>Month-to-month</td>\n",
       "      <td>Electronic0check</td>\n",
       "      <td>Yes</td>\n",
       "      <td>70.70</td>\n",
       "      <td>151.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9305-CDSKC</td>\n",
       "      <td>8</td>\n",
       "      <td>Month-to-month</td>\n",
       "      <td>Electronic0check</td>\n",
       "      <td>Yes</td>\n",
       "      <td>99.65</td>\n",
       "      <td>820.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7892-POOKP</td>\n",
       "      <td>28</td>\n",
       "      <td>Month-to-month</td>\n",
       "      <td>Electronic0check</td>\n",
       "      <td>Yes</td>\n",
       "      <td>104.80</td>\n",
       "      <td>3046.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0280-XJGEX</td>\n",
       "      <td>49</td>\n",
       "      <td>Month-to-month</td>\n",
       "      <td>Bank0transfer0(automatic)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>103.70</td>\n",
       "      <td>5036.30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Account_id  Tenure        Contract              PaymentMethod  \\\n",
       "0  3668-QPYBK       2  Month-to-month               Mailed0check   \n",
       "1  9237-HQITU       2  Month-to-month           Electronic0check   \n",
       "2  9305-CDSKC       8  Month-to-month           Electronic0check   \n",
       "3  7892-POOKP      28  Month-to-month           Electronic0check   \n",
       "4  0280-XJGEX      49  Month-to-month  Bank0transfer0(automatic)   \n",
       "\n",
       "  PaperlessBilling  MonthlyCharges  TotalCharges  \n",
       "0              Yes           53.85        108.15  \n",
       "1              Yes           70.70        151.65  \n",
       "2              Yes           99.65        820.50  \n",
       "3              Yes          104.80       3046.05  \n",
       "4              Yes          103.70       5036.30  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# importing the cust_account.csv file\n",
    "cust_account = pd.read_csv(\"cust_account.csv\")\n",
    "cust_account.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3d78c520",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Account_id           object\n",
      "Tenure                int64\n",
      "Contract             object\n",
      "PaymentMethod        object\n",
      "PaperlessBilling     object\n",
      "MonthlyCharges      float64\n",
      "TotalCharges        float64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# validating the datatypes of cust_account dataframe\n",
    "print(cust_account.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ef145ef2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>Churn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3668-QPYBK</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9237-HQITU</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9305-CDSKC</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7892-POOKP</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0280-XJGEX</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Id Churn\n",
       "0  3668-QPYBK   Yes\n",
       "1  9237-HQITU   Yes\n",
       "2  9305-CDSKC   Yes\n",
       "3  7892-POOKP   Yes\n",
       "4  0280-XJGEX   Yes"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# importing the cust_churn.csv file\n",
    "cust_churn = pd.read_csv(\"cust_churn.csv\")\n",
    "cust_churn.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "77c2a5e4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Id       object\n",
      "Churn    object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# validating the datatypes of cust_churn dataframe\n",
    "print(cust_churn.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0bb2666",
   "metadata": {},
   "source": [
    "---\n",
    "### 2. Creating the SQLite database and tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "9a38f02e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# imporing required library\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "75d5553c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating a connection to create a new database\n",
    "conn = sqlite3.connect(\"customer_churn.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ef68a10c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating a cursor\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ba566156",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7043"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating the customer table in database\n",
    "customer.to_sql(\"customer\", conn, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e394f494",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7043"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating the customer location table in database\n",
    "cust_loc.to_sql(\"cust_loc\", conn, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "93e9bed5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7043"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating the customer services table in database\n",
    "cust_services.to_sql(\"cust_services\", conn, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "628868fb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7043"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating the customer account table in database\n",
    "cust_account.to_sql(\"cust_account\", conn, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "187d2015",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7043"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating the customer churn table in database\n",
    "cust_churn.to_sql(\"cust_churn\", conn, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "615ed9af",
   "metadata": {},
   "source": [
    "---\n",
    "### 3. Displaying table information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "fdf38ac1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# function for displaying the table names and column names\n",
    "def table_information(conn, cursor):\n",
    "    tables = cur.execute(\"SELECT name FROM sqlite_master WHERE type='table';\").fetchall()\n",
    "    for table_name in tables:\n",
    "        table_name = table_name[0]\n",
    "        table = pd.read_sql_query(\"SELECT * FROM {} LIMIT 0\".format(table_name), conn)\n",
    "        print(table_name + \": \")\n",
    "        for col in table.columns:\n",
    "            print(\"\\t\" + col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fc9b813d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cust_service: \n",
      "\tCustomerID\n",
      "\tPhoneService\n",
      "\tMultipleLines\n",
      "\tInternetService\n",
      "\tOnlineSecurity\n",
      "\tOnlineBackup\n",
      "\tDeviceProtection\n",
      "\tTechSupport\n",
      "\tStreamingTV\n",
      "\tStreamingMovies\n",
      "churn_all: \n",
      "\tCustomerID\n",
      "\tGender\n",
      "\tSeniorCitizen\n",
      "\tPartner\n",
      "\tDependents\n",
      "\tState\n",
      "\tLatitude\n",
      "\tLongitude\n",
      "\tZipCode\n",
      "\tPhoneService\n",
      "\tMultipleLines\n",
      "\tInternetService\n",
      "\tOnlineSecurity\n",
      "\tOnlineBackup\n",
      "\tDeviceProtection\n",
      "\tTechSupport\n",
      "\tStreamingTV\n",
      "\tStreamingMovies\n",
      "\tTenure\n",
      "\tContract\n",
      "\tPaymentMethod\n",
      "\tPaperlessBilling\n",
      "\tMonthlyCharges\n",
      "\tTotalCharges\n",
      "\tChurn\n",
      "customer: \n",
      "\tCustomerID\n",
      "\tGender\n",
      "\tSeniorCitizen\n",
      "\tPartner\n",
      "\tDependents\n",
      "cust_loc: \n",
      "\tCust_ID\n",
      "\tState\n",
      "\tLatitude\n",
      "\tLongitude\n",
      "\tZipCode\n",
      "cust_services: \n",
      "\tCust_ID\n",
      "\tPhoneService\n",
      "\tMultipleLines\n",
      "\tInternetService\n",
      "\tOnlineSecurity\n",
      "\tOnlineBackup\n",
      "\tDeviceProtection\n",
      "\tTechSupport\n",
      "\tStreamingTV\n",
      "\tStreamingMovies\n",
      "cust_account: \n",
      "\tAccount_id\n",
      "\tTenure\n",
      "\tContract\n",
      "\tPaymentMethod\n",
      "\tPaperlessBilling\n",
      "\tMonthlyCharges\n",
      "\tTotalCharges\n",
      "cust_churn: \n",
      "\tId\n",
      "\tChurn\n"
     ]
    }
   ],
   "source": [
    "# calling the function and passing argumens: conn and cur\n",
    "table_information(conn, cur)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43981ac9",
   "metadata": {},
   "source": [
    "---\n",
    "### 4. Joining all tables and writing dataframe to CSV file"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13fc346e",
   "metadata": {},
   "source": [
    "#### 4.1 Renaming table column directly in SQLite database\n",
    "In section 4.2, we can rename the tables directly in dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "0d3f8261",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1f8c2444040>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# renaming the Cust_ID column in cust_loc table to CustomerID\n",
    "cur.execute(\n",
    "\"\"\"\n",
    "alter table cust_loc\n",
    "rename column Cust_ID to CustomerID;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "1c3f7d61",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1f8c2444040>"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# renaming the Cust_ID column in cust_service table to CustomerID\n",
    "cur.execute(\n",
    "\"\"\"\n",
    "alter table cust_services\n",
    "rename column Cust_ID to CustomerID;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31c1c73f",
   "metadata": {},
   "source": [
    "#### 4.2 Renaming table columns in Pandas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "8eff33ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "# renaming Cust_ID column to CustomerID in cust_loc dataframe\n",
    "cust_loc = cust_loc.rename(columns={\"Cust_ID\": \"CustomerID\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "224598ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# renaming Cust_ID column to CustomerID in cust_service dataframe\n",
    "cust_services = cust_services.rename(columns={\"Cust_ID\": \"CustomerID\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "49e850a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# renaming Account_ID column to CustomerID in cust_account dataframe\n",
    "cust_account = cust_account.rename(columns={\"Account_id\": \"CustomerID\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "6ca439a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# renaming ID columns to CustomerID in cust_churn dataframe\n",
    "cust_churn = cust_churn.rename(columns={\"Id\": \"CustomerID\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5a9d3c1",
   "metadata": {},
   "source": [
    "#### 4.3 Joining dataframes in Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "82c012b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# list of dataframes to join\n",
    "dfs_to_join = [customer, cust_loc, cust_services, cust_account, cust_churn]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "7cbcafe6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# joining the dataframes\n",
    "churn_all = pd.concat(dfs_to_join, join=\"inner\", axis=1)\n",
    "\n",
    "# removing the duplicated (CustomerID) column\n",
    "churn_all = churn_all.loc[:, ~churn_all.columns.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "172dcfb8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>SeniorCitizen</th>\n",
       "      <th>Partner</th>\n",
       "      <th>Dependents</th>\n",
       "      <th>State</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>ZipCode</th>\n",
       "      <th>PhoneService</th>\n",
       "      <th>...</th>\n",
       "      <th>TechSupport</th>\n",
       "      <th>StreamingTV</th>\n",
       "      <th>StreamingMovies</th>\n",
       "      <th>Tenure</th>\n",
       "      <th>Contract</th>\n",
       "      <th>PaymentMethod</th>\n",
       "      <th>PaperlessBilling</th>\n",
       "      <th>MonthlyCharges</th>\n",
       "      <th>TotalCharges</th>\n",
       "      <th>Churn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3668-QPYBK</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>California</td>\n",
       "      <td>33.964131</td>\n",
       "      <td>-118.272783</td>\n",
       "      <td>90003</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>2</td>\n",
       "      <td>Month-to-month</td>\n",
       "      <td>Mailed0check</td>\n",
       "      <td>Yes</td>\n",
       "      <td>53.85</td>\n",
       "      <td>108.15</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9237-HQITU</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>California</td>\n",
       "      <td>34.059281</td>\n",
       "      <td>-118.307420</td>\n",
       "      <td>90005</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>2</td>\n",
       "      <td>Month-to-month</td>\n",
       "      <td>Electronic0check</td>\n",
       "      <td>Yes</td>\n",
       "      <td>70.70</td>\n",
       "      <td>151.65</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9305-CDSKC</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>California</td>\n",
       "      <td>34.048013</td>\n",
       "      <td>-118.293953</td>\n",
       "      <td>90006</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>8</td>\n",
       "      <td>Month-to-month</td>\n",
       "      <td>Electronic0check</td>\n",
       "      <td>Yes</td>\n",
       "      <td>99.65</td>\n",
       "      <td>820.50</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7892-POOKP</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>California</td>\n",
       "      <td>34.062125</td>\n",
       "      <td>-118.315709</td>\n",
       "      <td>90010</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>28</td>\n",
       "      <td>Month-to-month</td>\n",
       "      <td>Electronic0check</td>\n",
       "      <td>Yes</td>\n",
       "      <td>104.80</td>\n",
       "      <td>3046.05</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0280-XJGEX</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>California</td>\n",
       "      <td>34.039224</td>\n",
       "      <td>-118.266293</td>\n",
       "      <td>90015</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>49</td>\n",
       "      <td>Month-to-month</td>\n",
       "      <td>Bank0transfer0(automatic)</td>\n",
       "      <td>Yes</td>\n",
       "      <td>103.70</td>\n",
       "      <td>5036.30</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID  Gender SeniorCitizen Partner Dependents       State   Latitude  \\\n",
       "0  3668-QPYBK    Male            No      No         No  California  33.964131   \n",
       "1  9237-HQITU  Female            No      No        Yes  California  34.059281   \n",
       "2  9305-CDSKC  Female            No      No        Yes  California  34.048013   \n",
       "3  7892-POOKP  Female            No     Yes        Yes  California  34.062125   \n",
       "4  0280-XJGEX    Male            No      No        Yes  California  34.039224   \n",
       "\n",
       "    Longitude  ZipCode PhoneService  ... TechSupport StreamingTV  \\\n",
       "0 -118.272783    90003          Yes  ...          No          No   \n",
       "1 -118.307420    90005          Yes  ...          No          No   \n",
       "2 -118.293953    90006          Yes  ...          No         Yes   \n",
       "3 -118.315709    90010          Yes  ...         Yes         Yes   \n",
       "4 -118.266293    90015          Yes  ...          No         Yes   \n",
       "\n",
       "  StreamingMovies Tenure        Contract              PaymentMethod  \\\n",
       "0              No      2  Month-to-month               Mailed0check   \n",
       "1              No      2  Month-to-month           Electronic0check   \n",
       "2             Yes      8  Month-to-month           Electronic0check   \n",
       "3             Yes     28  Month-to-month           Electronic0check   \n",
       "4             Yes     49  Month-to-month  Bank0transfer0(automatic)   \n",
       "\n",
       "  PaperlessBilling MonthlyCharges  TotalCharges Churn  \n",
       "0              Yes          53.85        108.15   Yes  \n",
       "1              Yes          70.70        151.65   Yes  \n",
       "2              Yes          99.65        820.50   Yes  \n",
       "3              Yes         104.80       3046.05   Yes  \n",
       "4              Yes         103.70       5036.30   Yes  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# displaying the new joined dataframe\n",
    "churn_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21363e0b",
   "metadata": {},
   "source": [
    "#### 4.4 Writing the churn_all dataframe to a CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "8e6f5161",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['CustomerID', 'Gender', 'SeniorCitizen', 'Partner', 'Dependents',\n",
      "       'State', 'Latitude', 'Longitude', 'ZipCode', 'PhoneService',\n",
      "       'MultipleLines', 'InternetService', 'OnlineSecurity', 'OnlineBackup',\n",
      "       'DeviceProtection', 'TechSupport', 'StreamingTV', 'StreamingMovies',\n",
      "       'Tenure', 'Contract', 'PaymentMethod', 'PaperlessBilling',\n",
      "       'MonthlyCharges', 'TotalCharges', 'Churn'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# displaying all the columns\n",
    "print(churn_all.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "282404df",
   "metadata": {},
   "outputs": [],
   "source": [
    "# writing the new churn_all dataframe to a CSV file\n",
    "churn_all.to_csv('churn_all.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24ffd224",
   "metadata": {},
   "source": [
    "#### 4.5 Adding the churn_all table to the customer_churn.db database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "56334934",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cust_service: \n",
      "\tCustomerID\n",
      "\tPhoneService\n",
      "\tMultipleLines\n",
      "\tInternetService\n",
      "\tOnlineSecurity\n",
      "\tOnlineBackup\n",
      "\tDeviceProtection\n",
      "\tTechSupport\n",
      "\tStreamingTV\n",
      "\tStreamingMovies\n",
      "churn_all: \n",
      "\tCustomerID\n",
      "\tGender\n",
      "\tSeniorCitizen\n",
      "\tPartner\n",
      "\tDependents\n",
      "\tState\n",
      "\tLatitude\n",
      "\tLongitude\n",
      "\tZipCode\n",
      "\tPhoneService\n",
      "\tMultipleLines\n",
      "\tInternetService\n",
      "\tOnlineSecurity\n",
      "\tOnlineBackup\n",
      "\tDeviceProtection\n",
      "\tTechSupport\n",
      "\tStreamingTV\n",
      "\tStreamingMovies\n",
      "\tTenure\n",
      "\tContract\n",
      "\tPaymentMethod\n",
      "\tPaperlessBilling\n",
      "\tMonthlyCharges\n",
      "\tTotalCharges\n",
      "\tChurn\n",
      "customer: \n",
      "\tCustomerID\n",
      "\tGender\n",
      "\tSeniorCitizen\n",
      "\tPartner\n",
      "\tDependents\n",
      "cust_loc: \n",
      "\tCustomerID\n",
      "\tState\n",
      "\tLatitude\n",
      "\tLongitude\n",
      "\tZipCode\n",
      "cust_services: \n",
      "\tCustomerID\n",
      "\tPhoneService\n",
      "\tMultipleLines\n",
      "\tInternetService\n",
      "\tOnlineSecurity\n",
      "\tOnlineBackup\n",
      "\tDeviceProtection\n",
      "\tTechSupport\n",
      "\tStreamingTV\n",
      "\tStreamingMovies\n",
      "cust_account: \n",
      "\tAccount_id\n",
      "\tTenure\n",
      "\tContract\n",
      "\tPaymentMethod\n",
      "\tPaperlessBilling\n",
      "\tMonthlyCharges\n",
      "\tTotalCharges\n",
      "cust_churn: \n",
      "\tId\n",
      "\tChurn\n"
     ]
    }
   ],
   "source": [
    "# displaying all the tables and their columns\n",
    "table_information(conn, cur)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "1225fe6e",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CustomerID           object\n",
      "Gender               object\n",
      "SeniorCitizen        object\n",
      "Partner              object\n",
      "Dependents           object\n",
      "State                object\n",
      "Latitude            float64\n",
      "Longitude           float64\n",
      "ZipCode               int64\n",
      "PhoneService         object\n",
      "MultipleLines        object\n",
      "InternetService      object\n",
      "OnlineSecurity       object\n",
      "OnlineBackup         object\n",
      "DeviceProtection     object\n",
      "TechSupport          object\n",
      "StreamingTV          object\n",
      "StreamingMovies      object\n",
      "Tenure                int64\n",
      "Contract             object\n",
      "PaymentMethod        object\n",
      "PaperlessBilling     object\n",
      "MonthlyCharges      float64\n",
      "TotalCharges        float64\n",
      "Churn                object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# validating the datatypes of all the columns in churn_all table\n",
    "print(churn_all.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "38b455b1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7043"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating the churn_all table in database\n",
    "churn_all.to_sql(\"churn_all\", conn, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "523927a8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}