Joshua_Kibuye_Week_3_Individual_Project.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Joshua_Kibuye_Week_3_Individual_Project.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "GU9rgTjCjySW",
        "W8rnZnISjrJL"
      ],
      "authorship_tag": "ABX9TyNhjJ7sKrKn6XrD2595Nkml",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Kibuye24/Week_3_IP/blob/main/Joshua_Kibuye_Week_3_Individual_Project.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GU9rgTjCjySW"
      },
      "source": [
        "# DESCRIPTION OF THE DATA SCIENCE PROBLEM\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ecZNO58qj6YZ"
      },
      "source": [
        "Currently MTN Cote d'Ivoire would like to upgrade its technology infrastructure for its mobile users in Ivory Coast. Studying the given dataset, how does MTN Cote d'Ivoire go about the upgrade of its infrastructure strategy within the given cities?\n",
        "The telecom data provided for this project is only a sample ( i.e. for only three days).  The data files that you will need for this project will be as follows:\n",
        "\n",
        "1. cells_geo_description.xlsx [Link] (Links to an external site.)\n",
        "2. cells_geo.csv [Link] (Links to an external site.)\n",
        "3. CDR_description.xlsx [Link] (Links to an external site.)\n",
        "4. CDR 20120507 [http://bit.ly/TelecomDataset1] (Links to an external site.)\n",
        "5. CDR 20120508 [http://bit.ly/TelecomDataset2] (Links to an external site.)\n",
        "5. CDR 20120509 [http://bit.ly/TelecomDataset3]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "W8rnZnISjrJL"
      },
      "source": [
        "# IMPORTING OUR LIBRARIES"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fBDfnCghw8pZ"
      },
      "source": [
        "#we will first import our library. We use pandas due to the datasets given. Numpy will be used for arithmetic functions\n",
        "import pandas as pd\n",
        "import numpy as np"
      ],
      "execution_count": 122,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mVVLKuOiX3yj"
      },
      "source": [
        "# DATA CLEANING AND ANALYSIS OF THE CELLS GEO DATASET"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 390
        },
        "id": "gD5YAVc7Bo-Z",
        "outputId": "44686be5-4a5b-4808-d510-423be3eda60c"
      },
      "source": [
        "##Here, we are gettinf the description of the cells_geo dataset.\n",
        "data_xls = pd.read_excel('/content/cells_geo_description.xlsx', dtype=str, index_col=None)\n",
        "data_xls.to_csv('geoDescription.csv', encoding='utf-8', index=False)\n",
        "df = pd.read_csv('geoDescription.csv')\n",
        "df"
      ],
      "execution_count": 123,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Column name</th>\n",
              "      <th>Description</th>\n",
              "      <th>Format</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>VILLES</td>\n",
              "      <td>City</td>\n",
              "      <td>String</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>STATUS</td>\n",
              "      <td>In Service or not</td>\n",
              "      <td>String</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>LOCALISATION</td>\n",
              "      <td>in ABIDJAN or not</td>\n",
              "      <td>String</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>DECOUPZONE</td>\n",
              "      <td>Geographical Zone</td>\n",
              "      <td>String</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>ZONENAME</td>\n",
              "      <td>Name of Zone</td>\n",
              "      <td>String</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>LONGITUDE</td>\n",
              "      <td>Longitude</td>\n",
              "      <td>Float</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>LATITUDE</td>\n",
              "      <td>Latitude</td>\n",
              "      <td>Float</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>REGION</td>\n",
              "      <td>Region</td>\n",
              "      <td>String</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>AREA</td>\n",
              "      <td>Area</td>\n",
              "      <td>String</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>CELL_ID</td>\n",
              "      <td>ID of the cell</td>\n",
              "      <td>String</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>SITE_CODE</td>\n",
              "      <td>Site (there are several cells per site, severa...</td>\n",
              "      <td>String</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     Column name                                        Description  Format\n",
              "0         VILLES                                               City  String\n",
              "1         STATUS                                  In Service or not  String\n",
              "2   LOCALISATION                                  in ABIDJAN or not  String\n",
              "3     DECOUPZONE                                  Geographical Zone  String\n",
              "4       ZONENAME                                       Name of Zone  String\n",
              "5      LONGITUDE                                          Longitude   Float\n",
              "6       LATITUDE                                           Latitude   Float\n",
              "7         REGION                                             Region  String\n",
              "8           AREA                                               Area  String\n",
              "9        CELL_ID                                     ID of the cell  String\n",
              "10     SITE_CODE  Site (there are several cells per site, severa...  String"
            ]
          },
          "metadata": {},
          "execution_count": 123
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "id": "mXN9MRci1LI5",
        "outputId": "c7e82b2b-e8e6-4632-ada4-eb259958715b"
      },
      "source": [
        "##Viewing our cells geo dataset\n",
        "url = '/content/cells_geo.csv'\n",
        "df = pd.read_csv(url, delimiter=';')\n",
        "df\n"
      ],
      "execution_count": 124,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Unnamed: 0</th>\n",
              "      <th>VILLES</th>\n",
              "      <th>STATUS</th>\n",
              "      <th>LOCALISATION</th>\n",
              "      <th>DECOUPZONE</th>\n",
              "      <th>ZONENAME</th>\n",
              "      <th>LONGITUDE</th>\n",
              "      <th>LATITUDE</th>\n",
              "      <th>REGION</th>\n",
              "      <th>AREA</th>\n",
              "      <th>CELL_ID</th>\n",
              "      <th>SITE_CODE</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>ADJAME</td>\n",
              "      <td>In Service</td>\n",
              "      <td>ABIDJAN</td>\n",
              "      <td>\"Abidjan_EST</td>\n",
              "      <td>AGBANNEW</td>\n",
              "      <td>-4.000667</td>\n",
              "      <td>5.368194</td>\n",
              "      <td>LAGUNE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>fbb2e75af3</td>\n",
              "      <td>0bb492ef17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>ADJAME</td>\n",
              "      <td>In Service</td>\n",
              "      <td>ABIDJAN</td>\n",
              "      <td>\"Abidjan_EST</td>\n",
              "      <td>AGBANNEW</td>\n",
              "      <td>-4.000667</td>\n",
              "      <td>5.368194</td>\n",
              "      <td>LAGUNE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1502501d78</td>\n",
              "      <td>0bb492ef17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>ADJAME</td>\n",
              "      <td>In Service</td>\n",
              "      <td>ABIDJAN</td>\n",
              "      <td>\"Abidjan_EST</td>\n",
              "      <td>AGBANNEW</td>\n",
              "      <td>-4.000667</td>\n",
              "      <td>5.368194</td>\n",
              "      <td>LAGUNE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0c2e182a0c</td>\n",
              "      <td>0bb492ef17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>ASSINIE</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-EST</td>\n",
              "      <td>ASSINIE\"</td>\n",
              "      <td>-3.488467</td>\n",
              "      <td>5.158761</td>\n",
              "      <td>SUD COMOE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>69bdfc69fd</td>\n",
              "      <td>8a1e6ce31f</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>ASSINIE</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-EST</td>\n",
              "      <td>ASSINIE\"</td>\n",
              "      <td>-3.488467</td>\n",
              "      <td>5.158761</td>\n",
              "      <td>SUD COMOE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7a1029d2ab</td>\n",
              "      <td>8a1e6ce31f</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3969</th>\n",
              "      <td>3969</td>\n",
              "      <td>ZUENOULA</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-NORD</td>\n",
              "      <td>ZONE A - Grand-NORD</td>\n",
              "      <td>-6.039694</td>\n",
              "      <td>7.428611</td>\n",
              "      <td>MARAHOUE</td>\n",
              "      <td>CENTRE NORTH</td>\n",
              "      <td>1cfc2d7ab1</td>\n",
              "      <td>f91ed8d2e9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3970</th>\n",
              "      <td>3970</td>\n",
              "      <td>ZUENOULA</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-NORD</td>\n",
              "      <td>ZONE A - Grand-NORD</td>\n",
              "      <td>-6.039694</td>\n",
              "      <td>7.428611</td>\n",
              "      <td>MARAHOUE</td>\n",
              "      <td>CENTRE NORTH</td>\n",
              "      <td>3ae1708153</td>\n",
              "      <td>f91ed8d2e9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3971</th>\n",
              "      <td>3971</td>\n",
              "      <td>ZUENOULA</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-NORD</td>\n",
              "      <td>ZONE A - Grand-NORD</td>\n",
              "      <td>-6.065450</td>\n",
              "      <td>7.444592</td>\n",
              "      <td>MARAHOUE</td>\n",
              "      <td>CENTRE NORTH</td>\n",
              "      <td>cb71f8a29c</td>\n",
              "      <td>61bee9c70c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3972</th>\n",
              "      <td>3972</td>\n",
              "      <td>ZUENOULA</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-NORD</td>\n",
              "      <td>ZONE A - Grand-NORD</td>\n",
              "      <td>-6.065450</td>\n",
              "      <td>7.444592</td>\n",
              "      <td>MARAHOUE</td>\n",
              "      <td>CENTRE NORTH</td>\n",
              "      <td>d4a6a0b645</td>\n",
              "      <td>61bee9c70c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3973</th>\n",
              "      <td>3973</td>\n",
              "      <td>ZUENOULA</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-NORD</td>\n",
              "      <td>ZONE A - Grand-NORD</td>\n",
              "      <td>-6.065450</td>\n",
              "      <td>7.444592</td>\n",
              "      <td>MARAHOUE</td>\n",
              "      <td>CENTRE NORTH</td>\n",
              "      <td>f9f3e42589</td>\n",
              "      <td>61bee9c70c</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>3974 rows × 12 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "      Unnamed: 0    VILLES      STATUS  ...          AREA     CELL_ID   SITE_CODE\n",
              "0              0    ADJAME  In Service  ...           NaN  fbb2e75af3  0bb492ef17\n",
              "1              1    ADJAME  In Service  ...           NaN  1502501d78  0bb492ef17\n",
              "2              2    ADJAME  In Service  ...           NaN  0c2e182a0c  0bb492ef17\n",
              "3              3   ASSINIE  In Service  ...           NaN  69bdfc69fd  8a1e6ce31f\n",
              "4              4   ASSINIE  In Service  ...           NaN  7a1029d2ab  8a1e6ce31f\n",
              "...          ...       ...         ...  ...           ...         ...         ...\n",
              "3969        3969  ZUENOULA  In Service  ...  CENTRE NORTH  1cfc2d7ab1  f91ed8d2e9\n",
              "3970        3970  ZUENOULA  In Service  ...  CENTRE NORTH  3ae1708153  f91ed8d2e9\n",
              "3971        3971  ZUENOULA  In Service  ...  CENTRE NORTH  cb71f8a29c  61bee9c70c\n",
              "3972        3972  ZUENOULA  In Service  ...  CENTRE NORTH  d4a6a0b645  61bee9c70c\n",
              "3973        3973  ZUENOULA  In Service  ...  CENTRE NORTH  f9f3e42589  61bee9c70c\n",
              "\n",
              "[3974 rows x 12 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 124
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "id": "KJP11yOxaoNP",
        "outputId": "80670175-cc6f-41e9-fe67-d070e4e2bee4"
      },
      "source": [
        "#We now proceed to cleaning the data we have on the cells geo dataset. We start with finding if there are duplicates and dropping them\n",
        "df.duplicated()\n",
        "df.drop_duplicates()\n",
        "##We also need to check and  drop rows that only have null values\n",
        "df.dropna(axis=1, how='all')\n",
        "##we then proceed to drop rows where the status is not in service as the company needs data from those who are in service\n",
        "df.dropna(subset=['STATUS'])\n",
        "##Our data has been cleaned and it is ready for analysis"
      ],
      "execution_count": 125,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Unnamed: 0</th>\n",
              "      <th>VILLES</th>\n",
              "      <th>STATUS</th>\n",
              "      <th>LOCALISATION</th>\n",
              "      <th>DECOUPZONE</th>\n",
              "      <th>ZONENAME</th>\n",
              "      <th>LONGITUDE</th>\n",
              "      <th>LATITUDE</th>\n",
              "      <th>REGION</th>\n",
              "      <th>AREA</th>\n",
              "      <th>CELL_ID</th>\n",
              "      <th>SITE_CODE</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>ADJAME</td>\n",
              "      <td>In Service</td>\n",
              "      <td>ABIDJAN</td>\n",
              "      <td>\"Abidjan_EST</td>\n",
              "      <td>AGBANNEW</td>\n",
              "      <td>-4.000667</td>\n",
              "      <td>5.368194</td>\n",
              "      <td>LAGUNE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>fbb2e75af3</td>\n",
              "      <td>0bb492ef17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>ADJAME</td>\n",
              "      <td>In Service</td>\n",
              "      <td>ABIDJAN</td>\n",
              "      <td>\"Abidjan_EST</td>\n",
              "      <td>AGBANNEW</td>\n",
              "      <td>-4.000667</td>\n",
              "      <td>5.368194</td>\n",
              "      <td>LAGUNE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1502501d78</td>\n",
              "      <td>0bb492ef17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>ADJAME</td>\n",
              "      <td>In Service</td>\n",
              "      <td>ABIDJAN</td>\n",
              "      <td>\"Abidjan_EST</td>\n",
              "      <td>AGBANNEW</td>\n",
              "      <td>-4.000667</td>\n",
              "      <td>5.368194</td>\n",
              "      <td>LAGUNE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0c2e182a0c</td>\n",
              "      <td>0bb492ef17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>ASSINIE</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-EST</td>\n",
              "      <td>ASSINIE\"</td>\n",
              "      <td>-3.488467</td>\n",
              "      <td>5.158761</td>\n",
              "      <td>SUD COMOE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>69bdfc69fd</td>\n",
              "      <td>8a1e6ce31f</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>ASSINIE</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-EST</td>\n",
              "      <td>ASSINIE\"</td>\n",
              "      <td>-3.488467</td>\n",
              "      <td>5.158761</td>\n",
              "      <td>SUD COMOE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7a1029d2ab</td>\n",
              "      <td>8a1e6ce31f</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3969</th>\n",
              "      <td>3969</td>\n",
              "      <td>ZUENOULA</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-NORD</td>\n",
              "      <td>ZONE A - Grand-NORD</td>\n",
              "      <td>-6.039694</td>\n",
              "      <td>7.428611</td>\n",
              "      <td>MARAHOUE</td>\n",
              "      <td>CENTRE NORTH</td>\n",
              "      <td>1cfc2d7ab1</td>\n",
              "      <td>f91ed8d2e9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3970</th>\n",
              "      <td>3970</td>\n",
              "      <td>ZUENOULA</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-NORD</td>\n",
              "      <td>ZONE A - Grand-NORD</td>\n",
              "      <td>-6.039694</td>\n",
              "      <td>7.428611</td>\n",
              "      <td>MARAHOUE</td>\n",
              "      <td>CENTRE NORTH</td>\n",
              "      <td>3ae1708153</td>\n",
              "      <td>f91ed8d2e9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3971</th>\n",
              "      <td>3971</td>\n",
              "      <td>ZUENOULA</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-NORD</td>\n",
              "      <td>ZONE A - Grand-NORD</td>\n",
              "      <td>-6.065450</td>\n",
              "      <td>7.444592</td>\n",
              "      <td>MARAHOUE</td>\n",
              "      <td>CENTRE NORTH</td>\n",
              "      <td>cb71f8a29c</td>\n",
              "      <td>61bee9c70c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3972</th>\n",
              "      <td>3972</td>\n",
              "      <td>ZUENOULA</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-NORD</td>\n",
              "      <td>ZONE A - Grand-NORD</td>\n",
              "      <td>-6.065450</td>\n",
              "      <td>7.444592</td>\n",
              "      <td>MARAHOUE</td>\n",
              "      <td>CENTRE NORTH</td>\n",
              "      <td>d4a6a0b645</td>\n",
              "      <td>61bee9c70c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3973</th>\n",
              "      <td>3973</td>\n",
              "      <td>ZUENOULA</td>\n",
              "      <td>In Service</td>\n",
              "      <td>INTERIEUR</td>\n",
              "      <td>Grand-NORD</td>\n",
              "      <td>ZONE A - Grand-NORD</td>\n",
              "      <td>-6.065450</td>\n",
              "      <td>7.444592</td>\n",
              "      <td>MARAHOUE</td>\n",
              "      <td>CENTRE NORTH</td>\n",
              "      <td>f9f3e42589</td>\n",
              "      <td>61bee9c70c</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>3907 rows × 12 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "      Unnamed: 0    VILLES      STATUS  ...          AREA     CELL_ID   SITE_CODE\n",
              "0              0    ADJAME  In Service  ...           NaN  fbb2e75af3  0bb492ef17\n",
              "1              1    ADJAME  In Service  ...           NaN  1502501d78  0bb492ef17\n",
              "2              2    ADJAME  In Service  ...           NaN  0c2e182a0c  0bb492ef17\n",
              "3              3   ASSINIE  In Service  ...           NaN  69bdfc69fd  8a1e6ce31f\n",
              "4              4   ASSINIE  In Service  ...           NaN  7a1029d2ab  8a1e6ce31f\n",
              "...          ...       ...         ...  ...           ...         ...         ...\n",
              "3969        3969  ZUENOULA  In Service  ...  CENTRE NORTH  1cfc2d7ab1  f91ed8d2e9\n",
              "3970        3970  ZUENOULA  In Service  ...  CENTRE NORTH  3ae1708153  f91ed8d2e9\n",
              "3971        3971  ZUENOULA  In Service  ...  CENTRE NORTH  cb71f8a29c  61bee9c70c\n",
              "3972        3972  ZUENOULA  In Service  ...  CENTRE NORTH  d4a6a0b645  61bee9c70c\n",
              "3973        3973  ZUENOULA  In Service  ...  CENTRE NORTH  f9f3e42589  61bee9c70c\n",
              "\n",
              "[3907 rows x 12 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 125
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bCNdKEli0Xt6",
        "outputId": "25ec7eee-4926-4cd5-ddb6-659e0f960e78"
      },
      "source": [
        "#We first find the cities that have the most frequencies\n",
        "df['VILLES'].value_counts()[:5].sort_values(ascending=False)"
      ],
      "execution_count": 146,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "COCODY         354\n",
              "YOPOUGON       295\n",
              "ABOBO          255\n",
              "MARCORY        141\n",
              "TREICHVILLE    132\n",
              "Name: VILLES, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 146
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 669
        },
        "id": "dKnlJAIdcDE5",
        "outputId": "84096823-0ce2-462c-8919-fe9001b84e07"
      },
      "source": [
        "##We will first find the city that has the most usage in the different Regions. This will help us find cities with most usage\n",
        "df['VILLES'].groupby(df['REGION']).describe()"
      ],
      "execution_count": 126,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>count</th>\n",
              "      <th>unique</th>\n",
              "      <th>top</th>\n",
              "      <th>freq</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>REGION</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>18 MONTAGNES</th>\n",
              "      <td>102</td>\n",
              "      <td>24</td>\n",
              "      <td>MAN</td>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>AGNEBY</th>\n",
              "      <td>103</td>\n",
              "      <td>24</td>\n",
              "      <td>ADZOPE</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>BAFING</th>\n",
              "      <td>42</td>\n",
              "      <td>14</td>\n",
              "      <td>BOROTOUKRO</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>BAS SASSANDRA</th>\n",
              "      <td>248</td>\n",
              "      <td>46</td>\n",
              "      <td>SAN-PEDRO</td>\n",
              "      <td>55</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>DENGUELE</th>\n",
              "      <td>68</td>\n",
              "      <td>20</td>\n",
              "      <td>ODIENNE</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>FROMAGER</th>\n",
              "      <td>110</td>\n",
              "      <td>17</td>\n",
              "      <td>GAGNOA</td>\n",
              "      <td>55</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>HAUT SASSANDRA</th>\n",
              "      <td>180</td>\n",
              "      <td>32</td>\n",
              "      <td>DALOA</td>\n",
              "      <td>59</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>LACS</th>\n",
              "      <td>160</td>\n",
              "      <td>16</td>\n",
              "      <td>YAMOUSSOUKRO</td>\n",
              "      <td>104</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>LAGUNE</th>\n",
              "      <td>1850</td>\n",
              "      <td>56</td>\n",
              "      <td>COCODY</td>\n",
              "      <td>354</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MARAHOUE</th>\n",
              "      <td>78</td>\n",
              "      <td>18</td>\n",
              "      <td>BOUAFLE</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MOYEN CAVALLY</th>\n",
              "      <td>102</td>\n",
              "      <td>19</td>\n",
              "      <td>GUIGLO</td>\n",
              "      <td>27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MOYEN COMOE</th>\n",
              "      <td>84</td>\n",
              "      <td>21</td>\n",
              "      <td>ABENGOUROU</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>NZI COMOE</th>\n",
              "      <td>112</td>\n",
              "      <td>29</td>\n",
              "      <td>DIMBOKRO</td>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>SAVANE</th>\n",
              "      <td>154</td>\n",
              "      <td>38</td>\n",
              "      <td>KORHOGO</td>\n",
              "      <td>36</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>SUD BANDAMA</th>\n",
              "      <td>128</td>\n",
              "      <td>25</td>\n",
              "      <td>DIVO</td>\n",
              "      <td>39</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>SUD COMOE</th>\n",
              "      <td>88</td>\n",
              "      <td>20</td>\n",
              "      <td>ABOISSO</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>VALLEE DU BANDAMA</th>\n",
              "      <td>158</td>\n",
              "      <td>32</td>\n",
              "      <td>BOUAKE</td>\n",
              "      <td>65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>WORODOUGOU</th>\n",
              "      <td>99</td>\n",
              "      <td>29</td>\n",
              "      <td>SEGUELA</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>ZANZAN</th>\n",
              "      <td>108</td>\n",
              "      <td>28</td>\n",
              "      <td>BONDOUKOU</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                  count unique           top freq\n",
              "REGION                                           \n",
              "18 MONTAGNES        102     24           MAN   24\n",
              "AGNEBY              103     24        ADZOPE   21\n",
              "BAFING               42     14    BOROTOUKRO    3\n",
              "BAS SASSANDRA       248     46     SAN-PEDRO   55\n",
              "DENGUELE             68     20       ODIENNE   12\n",
              "FROMAGER            110     17        GAGNOA   55\n",
              "HAUT SASSANDRA      180     32         DALOA   59\n",
              "LACS                160     16  YAMOUSSOUKRO  104\n",
              "LAGUNE             1850     56        COCODY  354\n",
              "MARAHOUE             78     18       BOUAFLE   15\n",
              "MOYEN CAVALLY       102     19        GUIGLO   27\n",
              "MOYEN COMOE          84     21    ABENGOUROU   21\n",
              "NZI COMOE           112     29      DIMBOKRO   16\n",
              "SAVANE              154     38       KORHOGO   36\n",
              "SUD BANDAMA         128     25          DIVO   39\n",
              "SUD COMOE            88     20       ABOISSO   18\n",
              "VALLEE DU BANDAMA   158     32        BOUAKE   65\n",
              "WORODOUGOU           99     29       SEGUELA   12\n",
              "ZANZAN              108     28     BONDOUKOU   21"
            ]
          },
          "metadata": {},
          "execution_count": 126
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "id": "Lp-BVeHWjcY8",
        "outputId": "5256425f-0daf-4702-a99e-d07534305fa7"
      },
      "source": [
        "##We will also look to find the most usage of cities by localization to know what localisation needs more focus\n",
        "df['VILLES'].groupby(df['LOCALISATION']).describe()"
      ],
      "execution_count": 127,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>count</th>\n",
              "      <th>unique</th>\n",
              "      <th>top</th>\n",
              "      <th>freq</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>LOCALISATION</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>ABIDJAN</th>\n",
              "      <td>1669</td>\n",
              "      <td>15</td>\n",
              "      <td>COCODY</td>\n",
              "      <td>354</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>INTERIEUR</th>\n",
              "      <td>2305</td>\n",
              "      <td>490</td>\n",
              "      <td>YAMOUSSOUKRO</td>\n",
              "      <td>104</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "             count unique           top freq\n",
              "LOCALISATION                                \n",
              "ABIDJAN       1669     15        COCODY  354\n",
              "INTERIEUR     2305    490  YAMOUSSOUKRO  104"
            ]
          },
          "metadata": {},
          "execution_count": 127
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "id": "JlQQnPZbkjpL",
        "outputId": "ff240c15-96cf-406e-ad60-6496715f0297"
      },
      "source": [
        "#We also find the regions based on the localization to know the top regions to focus on per localisation\n",
        "df['REGION'].groupby(df['LOCALISATION']).describe()"
      ],
      "execution_count": 128,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>count</th>\n",
              "      <th>unique</th>\n",
              "      <th>top</th>\n",
              "      <th>freq</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>LOCALISATION</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>ABIDJAN</th>\n",
              "      <td>1669</td>\n",
              "      <td>1</td>\n",
              "      <td>LAGUNE</td>\n",
              "      <td>1669</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>INTERIEUR</th>\n",
              "      <td>2305</td>\n",
              "      <td>19</td>\n",
              "      <td>BAS SASSANDRA</td>\n",
              "      <td>248</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "             count unique            top  freq\n",
              "LOCALISATION                                  \n",
              "ABIDJAN       1669      1         LAGUNE  1669\n",
              "INTERIEUR     2305     19  BAS SASSANDRA   248"
            ]
          },
          "metadata": {},
          "execution_count": 128
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eqb71lC6XZlj"
      },
      "source": [
        "#DATA CLEANING AND ANALYSIS OF THE TELCOM DATASETS"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "NmbT-biNwzcB",
        "outputId": "fdf2653a-4929-4e75-d589-a716e13f136c"
      },
      "source": [
        "##View our first telecom dataset\n",
        "url = \"http://bit.ly/Telcom_dataset1\"\n",
        "df_1 = pd.read_csv(url)\n",
        "df_1.head()\n",
        "##The three tables will need to be merged. For that, the columns should be same and DATETIME will be changed to DATE_TIME like other datasets\n",
        "df_1 = df_1.rename(columns = {'DATETIME':'DATE_TIME', 'PRODUTC':'PRODUCT'})\n",
        "df_1.head()"
      ],
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>PRODUCT</th>\n",
              "      <th>VALUE</th>\n",
              "      <th>DATE_TIME</th>\n",
              "      <th>CELL_ON_SITE</th>\n",
              "      <th>DW_A_NUMBER_INT</th>\n",
              "      <th>DW_B_NUMBER_INT</th>\n",
              "      <th>COUNTRY_A</th>\n",
              "      <th>COUNTRY_B</th>\n",
              "      <th>CELL_ID</th>\n",
              "      <th>SITE_ID</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Voice</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-06 23:04:37.0</td>\n",
              "      <td>1</td>\n",
              "      <td>49fae048b547780</td>\n",
              "      <td>1a49f8b0ded9de6</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>/TJNe+Mmtu</td>\n",
              "      <td>/TJNe+Mmtu</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>sms</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-06 23:05:25.0</td>\n",
              "      <td>0</td>\n",
              "      <td>7a54a6eaee19e65</td>\n",
              "      <td>93343ba6e41af95</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>/+cKZKIp41</td>\n",
              "      <td>/+cKZKIp41</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>sms</td>\n",
              "      <td>25</td>\n",
              "      <td>2012-05-06 23:05:41.0</td>\n",
              "      <td>0</td>\n",
              "      <td>6008887b0207d82</td>\n",
              "      <td>7aacc9709ce0a20</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>/u0FSD+ahi</td>\n",
              "      <td>/u0FSD+ahi</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Voice</td>\n",
              "      <td>56</td>\n",
              "      <td>2012-05-06 23:05:42.0</td>\n",
              "      <td>1</td>\n",
              "      <td>7370032e20f4e96</td>\n",
              "      <td>6af4a60e74c91d3</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>+cNeJzsTp3</td>\n",
              "      <td>+cNeJzsTp3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Voice</td>\n",
              "      <td>14</td>\n",
              "      <td>2012-05-06 23:06:29.0</td>\n",
              "      <td>1</td>\n",
              "      <td>966ba4656e38e62</td>\n",
              "      <td>02aa736ee555d5c</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>051KhYDCpv</td>\n",
              "      <td>051KhYDCpv</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  PRODUCT  VALUE              DATE_TIME  ... COUNTRY_B     CELL_ID     SITE_ID\n",
              "0   Voice      0  2012-05-06 23:04:37.0  ...     19e2e  /TJNe+Mmtu  /TJNe+Mmtu\n",
              "1     sms      0  2012-05-06 23:05:25.0  ...     19e2e  /+cKZKIp41  /+cKZKIp41\n",
              "2     sms     25  2012-05-06 23:05:41.0  ...     19e2e  /u0FSD+ahi  /u0FSD+ahi\n",
              "3   Voice     56  2012-05-06 23:05:42.0  ...     19e2e  +cNeJzsTp3  +cNeJzsTp3\n",
              "4   Voice     14  2012-05-06 23:06:29.0  ...     19e2e  051KhYDCpv  051KhYDCpv\n",
              "\n",
              "[5 rows x 10 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "qjRg_ETvxlrT",
        "outputId": "adddc309-5b50-4840-ec3b-51d8a613c773"
      },
      "source": [
        "##viewing our second telecom dataset\n",
        "url = \"http://bit.ly/Telcom_dataset2\"\n",
        "df_2 = pd.read_csv(url)\n",
        "df_2.head()\n",
        "\n",
        "##The three tables will need to be merged. For that, the columns should be same and DW_A_NUMBER will be changed to DW_A_NUMBER_INT like other datasets\n",
        "df_2 = df_2.rename(columns = {'DW_A_NUMBER':'DW_A_NUMBER_INT', 'DW_B_NUMBER':'DW_B_NUMBER_INT'})\n",
        "df_2.head()"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>PRODUCT</th>\n",
              "      <th>VALUE</th>\n",
              "      <th>DATE_TIME</th>\n",
              "      <th>CELL_ON_SITE</th>\n",
              "      <th>DW_A_NUMBER_INT</th>\n",
              "      <th>DW_B_NUMBER_INT</th>\n",
              "      <th>COUNTRY_A</th>\n",
              "      <th>COUNTRY_B</th>\n",
              "      <th>CELL_ID</th>\n",
              "      <th>SITE_ID</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>sms</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-07 23:02:06.0</td>\n",
              "      <td>2</td>\n",
              "      <td>32255675c3b6fe3</td>\n",
              "      <td>1aa7ae01386e9fb</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>+854AcBQT2</td>\n",
              "      <td>+854AcBQT2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Voice</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-07 23:03:44.0</td>\n",
              "      <td>1</td>\n",
              "      <td>ff16d6ac74c1c64</td>\n",
              "      <td>87d87309c15a8c9</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>+854AcBQT2</td>\n",
              "      <td>+854AcBQT2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>sms</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-07 23:04:06.0</td>\n",
              "      <td>0</td>\n",
              "      <td>98d9f2fb5ff624b</td>\n",
              "      <td>93343ba6e41af95</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>+laSrk7g6q</td>\n",
              "      <td>+laSrk7g6q</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Voice</td>\n",
              "      <td>37</td>\n",
              "      <td>2012-05-07 23:04:48.0</td>\n",
              "      <td>1</td>\n",
              "      <td>3f9c8a33ec7be1f</td>\n",
              "      <td>7d05a38f89c49d8</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>/xvRrCVKoQ</td>\n",
              "      <td>/xvRrCVKoQ</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>sms</td>\n",
              "      <td>25</td>\n",
              "      <td>2012-05-07 23:04:58.0</td>\n",
              "      <td>1</td>\n",
              "      <td>7bcccc03adf3864</td>\n",
              "      <td>56a5d5430d2fa86</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>+bO+qkSonO</td>\n",
              "      <td>+bO+qkSonO</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  PRODUCT  VALUE              DATE_TIME  ... COUNTRY_B     CELL_ID     SITE_ID\n",
              "0     sms      0  2012-05-07 23:02:06.0  ...     19e2e  +854AcBQT2  +854AcBQT2\n",
              "1   Voice      0  2012-05-07 23:03:44.0  ...     19e2e  +854AcBQT2  +854AcBQT2\n",
              "2     sms      0  2012-05-07 23:04:06.0  ...     19e2e  +laSrk7g6q  +laSrk7g6q\n",
              "3   Voice     37  2012-05-07 23:04:48.0  ...     19e2e  /xvRrCVKoQ  /xvRrCVKoQ\n",
              "4     sms     25  2012-05-07 23:04:58.0  ...     19e2e  +bO+qkSonO  +bO+qkSonO\n",
              "\n",
              "[5 rows x 10 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "BTqCX_s68Ooo",
        "outputId": "0ee39a34-dea8-4aae-bcc6-9abb708e3efa"
      },
      "source": [
        "##Viewing our third telecom dataset\n",
        "url = \"http://bit.ly/Telcom_dataset3\"\n",
        "df_3 = pd.read_csv(url)\n",
        "df_3.head()\n",
        "##Renaming of the columns siet_ID and CELLID is done to make them in line with the description\n",
        "df_3 = df_3.rename(columns = {'CELLID':'CELL_ID', 'SIET_ID':'SITE_ID'})\n",
        "df_3.head()"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>PRODUCT</th>\n",
              "      <th>VALUE</th>\n",
              "      <th>DATE_TIME</th>\n",
              "      <th>CELL_ON_SITE</th>\n",
              "      <th>DW_A_NUMBER_INT</th>\n",
              "      <th>DW_B_NUMBER_INT</th>\n",
              "      <th>COUNTRY_A</th>\n",
              "      <th>COUNTRY_B</th>\n",
              "      <th>CELL_ID</th>\n",
              "      <th>SITE_ID</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Voice</td>\n",
              "      <td>61</td>\n",
              "      <td>2012-05-08 23:01:28.0</td>\n",
              "      <td>2</td>\n",
              "      <td>966f68353622feb</td>\n",
              "      <td>ffd53a3bb2db6f1</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>+38u2u/rfx</td>\n",
              "      <td>+38u2u/rfx</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>sms</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-08 23:01:31.0</td>\n",
              "      <td>1</td>\n",
              "      <td>a0a597384c612d7</td>\n",
              "      <td>cf3b218f1dde792</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>+Fs4mTvdKx</td>\n",
              "      <td>+Fs4mTvdKx</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Voice</td>\n",
              "      <td>10</td>\n",
              "      <td>2012-05-08 23:02:59.0</td>\n",
              "      <td>1</td>\n",
              "      <td>5441a9a58c97132</td>\n",
              "      <td>52f8b299515b9b7</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>+ZaaZozy/+</td>\n",
              "      <td>+ZaaZozy/+</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>sms</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-08 23:03:04.0</td>\n",
              "      <td>0</td>\n",
              "      <td>8ab8b9ed6989f61</td>\n",
              "      <td>e09e736e46c5c3d</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>+cNeJzsTp3</td>\n",
              "      <td>+cNeJzsTp3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Voice</td>\n",
              "      <td>22</td>\n",
              "      <td>2012-05-08 23:03:16.0</td>\n",
              "      <td>4</td>\n",
              "      <td>52662a62b199334</td>\n",
              "      <td>2186273faab4602</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>/eeMklgyJA</td>\n",
              "      <td>/eeMklgyJA</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  PRODUCT  VALUE              DATE_TIME  ... COUNTRY_B     CELL_ID     SITE_ID\n",
              "0   Voice     61  2012-05-08 23:01:28.0  ...     19e2e  +38u2u/rfx  +38u2u/rfx\n",
              "1     sms      0  2012-05-08 23:01:31.0  ...     19e2e  +Fs4mTvdKx  +Fs4mTvdKx\n",
              "2   Voice     10  2012-05-08 23:02:59.0  ...     19e2e  +ZaaZozy/+  +ZaaZozy/+\n",
              "3     sms      0  2012-05-08 23:03:04.0  ...     19e2e  +cNeJzsTp3  +cNeJzsTp3\n",
              "4   Voice     22  2012-05-08 23:03:16.0  ...     19e2e  /eeMklgyJA  /eeMklgyJA\n",
              "\n",
              "[5 rows x 10 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "id": "BjIlBdGvHUzW",
        "outputId": "bf72c541-a79e-4a1c-fbd5-1132f8c2dad0"
      },
      "source": [
        "##Now we can merge the three telecom datasets to do a uniform data analysis and cleaning\n",
        "df_telcom = pd.concat([df_1, df_2, df_3], axis=0)\n",
        "df_telcom"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>PRODUCT</th>\n",
              "      <th>VALUE</th>\n",
              "      <th>DATE_TIME</th>\n",
              "      <th>CELL_ON_SITE</th>\n",
              "      <th>DW_A_NUMBER_INT</th>\n",
              "      <th>DW_B_NUMBER_INT</th>\n",
              "      <th>COUNTRY_A</th>\n",
              "      <th>COUNTRY_B</th>\n",
              "      <th>CELL_ID</th>\n",
              "      <th>SITE_ID</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Voice</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-06 23:04:37.0</td>\n",
              "      <td>1</td>\n",
              "      <td>49fae048b547780</td>\n",
              "      <td>1a49f8b0ded9de6</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>/TJNe+Mmtu</td>\n",
              "      <td>/TJNe+Mmtu</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>sms</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-06 23:05:25.0</td>\n",
              "      <td>0</td>\n",
              "      <td>7a54a6eaee19e65</td>\n",
              "      <td>93343ba6e41af95</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>/+cKZKIp41</td>\n",
              "      <td>/+cKZKIp41</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>sms</td>\n",
              "      <td>25</td>\n",
              "      <td>2012-05-06 23:05:41.0</td>\n",
              "      <td>0</td>\n",
              "      <td>6008887b0207d82</td>\n",
              "      <td>7aacc9709ce0a20</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>/u0FSD+ahi</td>\n",
              "      <td>/u0FSD+ahi</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Voice</td>\n",
              "      <td>56</td>\n",
              "      <td>2012-05-06 23:05:42.0</td>\n",
              "      <td>1</td>\n",
              "      <td>7370032e20f4e96</td>\n",
              "      <td>6af4a60e74c91d3</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>+cNeJzsTp3</td>\n",
              "      <td>+cNeJzsTp3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Voice</td>\n",
              "      <td>14</td>\n",
              "      <td>2012-05-06 23:06:29.0</td>\n",
              "      <td>1</td>\n",
              "      <td>966ba4656e38e62</td>\n",
              "      <td>02aa736ee555d5c</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>051KhYDCpv</td>\n",
              "      <td>051KhYDCpv</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4996</th>\n",
              "      <td>Voice</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-09 00:01:43.0</td>\n",
              "      <td>3</td>\n",
              "      <td>467333ade2b4b9c</td>\n",
              "      <td>0ab2acd804d01fe</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>2aa04dcab7</td>\n",
              "      <td>f7d63a29e5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4997</th>\n",
              "      <td>Voice</td>\n",
              "      <td>62</td>\n",
              "      <td>2012-05-09 00:01:43.0</td>\n",
              "      <td>1</td>\n",
              "      <td>e077a6b74c5053c</td>\n",
              "      <td>4c8098eb6ea1a41</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>7f1f2f889f</td>\n",
              "      <td>b4ddb9e93c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4998</th>\n",
              "      <td>Voice</td>\n",
              "      <td>32</td>\n",
              "      <td>2012-05-09 00:01:43.0</td>\n",
              "      <td>1</td>\n",
              "      <td>77f53c39d27c050</td>\n",
              "      <td>9d064f013e2cab2</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>a4731e780e</td>\n",
              "      <td>d68260ef50</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4999</th>\n",
              "      <td>Voice</td>\n",
              "      <td>30</td>\n",
              "      <td>2012-05-09 00:01:43.0</td>\n",
              "      <td>2</td>\n",
              "      <td>672ce74c418aa7a</td>\n",
              "      <td>f9ff5af59cf0d68</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>98ed726da2</td>\n",
              "      <td>b4130b54ab</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5000</th>\n",
              "      <td>Voice</td>\n",
              "      <td>60</td>\n",
              "      <td>2012-05-09 00:01:43.0</td>\n",
              "      <td>2</td>\n",
              "      <td>6edeb4c55714650</td>\n",
              "      <td>7e60838216482a7</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>1c11a2fcbb</td>\n",
              "      <td>6d70dae094</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>15003 rows × 10 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "     PRODUCT  VALUE              DATE_TIME  ... COUNTRY_B     CELL_ID     SITE_ID\n",
              "0      Voice      0  2012-05-06 23:04:37.0  ...     19e2e  /TJNe+Mmtu  /TJNe+Mmtu\n",
              "1        sms      0  2012-05-06 23:05:25.0  ...     19e2e  /+cKZKIp41  /+cKZKIp41\n",
              "2        sms     25  2012-05-06 23:05:41.0  ...     19e2e  /u0FSD+ahi  /u0FSD+ahi\n",
              "3      Voice     56  2012-05-06 23:05:42.0  ...     19e2e  +cNeJzsTp3  +cNeJzsTp3\n",
              "4      Voice     14  2012-05-06 23:06:29.0  ...     19e2e  051KhYDCpv  051KhYDCpv\n",
              "...      ...    ...                    ...  ...       ...         ...         ...\n",
              "4996   Voice      0  2012-05-09 00:01:43.0  ...     19e2e  2aa04dcab7  f7d63a29e5\n",
              "4997   Voice     62  2012-05-09 00:01:43.0  ...     19e2e  7f1f2f889f  b4ddb9e93c\n",
              "4998   Voice     32  2012-05-09 00:01:43.0  ...     19e2e  a4731e780e  d68260ef50\n",
              "4999   Voice     30  2012-05-09 00:01:43.0  ...     19e2e  98ed726da2  b4130b54ab\n",
              "5000   Voice     60  2012-05-09 00:01:43.0  ...     19e2e  1c11a2fcbb  6d70dae094\n",
              "\n",
              "[15003 rows x 10 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "id": "mR8ImJn8Jetn",
        "outputId": "e022d85d-f72a-4dad-9c1b-b912ca59dae4"
      },
      "source": [
        "##We will then drop the unecessary columns as per the description. Which are the country columns and cell on site.\n",
        "df_telcom.drop(['DW_A_NUMBER_INT','DW_B_NUMBER_INT','CELL_ON_SITE'], axis = 1, inplace = True) \n",
        "##Once the tables have merged and unused columns dropped, we can check for duplicated items and drop the duplicates\n",
        "df_telcom.duplicated()\n",
        "##There are some duplicates and we will now drop them\n",
        "df_telcom = df_telcom.drop_duplicates()\n",
        "df_telcom\n",
        "##We also need to check and  drop rows that only have null values\n",
        "df_telcom.dropna(axis=1, how='all')\n",
        "##With that, we can conclude that the telcom dataset has been cleaned and is ready for analysis"
      ],
      "execution_count": 153,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>PRODUCT</th>\n",
              "      <th>VALUE</th>\n",
              "      <th>DATE_TIME</th>\n",
              "      <th>COUNTRY_A</th>\n",
              "      <th>COUNTRY_B</th>\n",
              "      <th>CELL_ID</th>\n",
              "      <th>SITE_ID</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Voice</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-06 23:04:37.0</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>/TJNe+Mmtu</td>\n",
              "      <td>/TJNe+Mmtu</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>sms</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-06 23:05:25.0</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>/+cKZKIp41</td>\n",
              "      <td>/+cKZKIp41</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>sms</td>\n",
              "      <td>25</td>\n",
              "      <td>2012-05-06 23:05:41.0</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>/u0FSD+ahi</td>\n",
              "      <td>/u0FSD+ahi</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Voice</td>\n",
              "      <td>56</td>\n",
              "      <td>2012-05-06 23:05:42.0</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>+cNeJzsTp3</td>\n",
              "      <td>+cNeJzsTp3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Voice</td>\n",
              "      <td>14</td>\n",
              "      <td>2012-05-06 23:06:29.0</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>051KhYDCpv</td>\n",
              "      <td>051KhYDCpv</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4995</th>\n",
              "      <td>Voice</td>\n",
              "      <td>10</td>\n",
              "      <td>2012-05-09 00:01:43.0</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>OTHER</td>\n",
              "      <td>6467daa1db</td>\n",
              "      <td>8a96bae9c1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4996</th>\n",
              "      <td>Voice</td>\n",
              "      <td>0</td>\n",
              "      <td>2012-05-09 00:01:43.0</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>2aa04dcab7</td>\n",
              "      <td>f7d63a29e5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4997</th>\n",
              "      <td>Voice</td>\n",
              "      <td>62</td>\n",
              "      <td>2012-05-09 00:01:43.0</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>7f1f2f889f</td>\n",
              "      <td>b4ddb9e93c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4999</th>\n",
              "      <td>Voice</td>\n",
              "      <td>30</td>\n",
              "      <td>2012-05-09 00:01:43.0</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>98ed726da2</td>\n",
              "      <td>b4130b54ab</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5000</th>\n",
              "      <td>Voice</td>\n",
              "      <td>60</td>\n",
              "      <td>2012-05-09 00:01:43.0</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>1c11a2fcbb</td>\n",
              "      <td>6d70dae094</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>13272 rows × 7 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "     PRODUCT  VALUE              DATE_TIME  ... COUNTRY_B     CELL_ID     SITE_ID\n",
              "0      Voice      0  2012-05-06 23:04:37.0  ...     19e2e  /TJNe+Mmtu  /TJNe+Mmtu\n",
              "1        sms      0  2012-05-06 23:05:25.0  ...     19e2e  /+cKZKIp41  /+cKZKIp41\n",
              "2        sms     25  2012-05-06 23:05:41.0  ...     19e2e  /u0FSD+ahi  /u0FSD+ahi\n",
              "3      Voice     56  2012-05-06 23:05:42.0  ...     19e2e  +cNeJzsTp3  +cNeJzsTp3\n",
              "4      Voice     14  2012-05-06 23:06:29.0  ...     19e2e  051KhYDCpv  051KhYDCpv\n",
              "...      ...    ...                    ...  ...       ...         ...         ...\n",
              "4995   Voice     10  2012-05-09 00:01:43.0  ...     OTHER  6467daa1db  8a96bae9c1\n",
              "4996   Voice      0  2012-05-09 00:01:43.0  ...     19e2e  2aa04dcab7  f7d63a29e5\n",
              "4997   Voice     62  2012-05-09 00:01:43.0  ...     19e2e  7f1f2f889f  b4ddb9e93c\n",
              "4999   Voice     30  2012-05-09 00:01:43.0  ...     19e2e  98ed726da2  b4130b54ab\n",
              "5000   Voice     60  2012-05-09 00:01:43.0  ...     19e2e  1c11a2fcbb  6d70dae094\n",
              "\n",
              "[13272 rows x 7 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 153
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "id": "pGuKl778QyoK",
        "outputId": "f2730455-e4ee-42f1-d8be-0e774af8d202"
      },
      "source": [
        "##Here, we want to find the value of the three different products to establish what product the company should focus on\n",
        "df_telcom['VALUE'].groupby(df_telcom['PRODUCT']).describe()"
      ],
      "execution_count": 150,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>count</th>\n",
              "      <th>mean</th>\n",
              "      <th>std</th>\n",
              "      <th>min</th>\n",
              "      <th>25%</th>\n",
              "      <th>50%</th>\n",
              "      <th>75%</th>\n",
              "      <th>max</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>PRODUCT</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Voice</th>\n",
              "      <td>5568.0</td>\n",
              "      <td>55.085489</td>\n",
              "      <td>237.347901</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>20.0</td>\n",
              "      <td>52.25</td>\n",
              "      <td>12900.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>data</th>\n",
              "      <td>786.0</td>\n",
              "      <td>21.987277</td>\n",
              "      <td>177.296103</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1.00</td>\n",
              "      <td>4440.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>sms</th>\n",
              "      <td>6918.0</td>\n",
              "      <td>7.549436</td>\n",
              "      <td>14.710186</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>25.00</td>\n",
              "      <td>100.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "          count       mean         std  min  25%   50%    75%      max\n",
              "PRODUCT                                                               \n",
              "Voice    5568.0  55.085489  237.347901  0.0  1.0  20.0  52.25  12900.0\n",
              "data      786.0  21.987277  177.296103  0.0  0.0   0.0   1.00   4440.0\n",
              "sms      6918.0   7.549436   14.710186  0.0  0.0   0.0  25.00    100.0"
            ]
          },
          "metadata": {},
          "execution_count": 150
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "id": "M_NRxKYbT7GU",
        "outputId": "85cdda83-0e38-4c46-9b6e-6f4d91a09141"
      },
      "source": [
        "##We will also analyze the time when the various products are most used to determine what time of the day peaks are.\n",
        "df_telcom['DATE_TIME'].groupby(df_telcom['PRODUCT']).describe()"
      ],
      "execution_count": 151,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>count</th>\n",
              "      <th>unique</th>\n",
              "      <th>top</th>\n",
              "      <th>freq</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>PRODUCT</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Voice</th>\n",
              "      <td>5568</td>\n",
              "      <td>1102</td>\n",
              "      <td>2012-05-07 00:01:32.0</td>\n",
              "      <td>31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>data</th>\n",
              "      <td>786</td>\n",
              "      <td>585</td>\n",
              "      <td>2012-05-08 00:01:44.0</td>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>sms</th>\n",
              "      <td>6918</td>\n",
              "      <td>1246</td>\n",
              "      <td>2012-05-09 00:00:05.0</td>\n",
              "      <td>47</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        count unique                    top freq\n",
              "PRODUCT                                         \n",
              "Voice    5568   1102  2012-05-07 00:01:32.0   31\n",
              "data      786    585  2012-05-08 00:01:44.0    6\n",
              "sms      6918   1246  2012-05-09 00:00:05.0   47"
            ]
          },
          "metadata": {},
          "execution_count": 151
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "06QWeUd8nNmJ",
        "outputId": "6524bab1-1244-4a86-91e1-6005bdd5f8ef"
      },
      "source": [
        "##We will also look at the description of time when people use the products in light of the best time for infrastracture changes to be done\n",
        "df_telcom['DATE_TIME'].describe()"
      ],
      "execution_count": 110,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count                     13272\n",
              "unique                     2143\n",
              "top       2012-05-09 00:01:14.0\n",
              "freq                         60\n",
              "Name: DATE_TIME, dtype: object"
            ]
          },
          "metadata": {},
          "execution_count": 110
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "id": "v5GBd7rSU-89",
        "outputId": "83adc8ea-efe8-4cdf-c9de-fd176e0e1a52"
      },
      "source": [
        "##Country A only had 19e2e as the unique country. All the products are based on that country and focus can be done there. However, Country_B there were  two unique\n",
        "##for that, we will find the variations in the products and frequencies to see which product to focus on which country\n",
        "df_telcom['COUNTRY_B'].groupby(df_telcom['PRODUCT']).describe()"
      ],
      "execution_count": 113,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>count</th>\n",
              "      <th>unique</th>\n",
              "      <th>top</th>\n",
              "      <th>freq</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>PRODUCT</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Voice</th>\n",
              "      <td>5568</td>\n",
              "      <td>2</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>5403</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>data</th>\n",
              "      <td>786</td>\n",
              "      <td>1</td>\n",
              "      <td>OTHER</td>\n",
              "      <td>786</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>sms</th>\n",
              "      <td>6918</td>\n",
              "      <td>2</td>\n",
              "      <td>19e2e</td>\n",
              "      <td>6654</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        count unique    top  freq\n",
              "PRODUCT                          \n",
              "Voice    5568      2  19e2e  5403\n",
              "data      786      1  OTHER   786\n",
              "sms      6918      2  19e2e  6654"
            ]
          },
          "metadata": {},
          "execution_count": 113
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "U-9Ds4CC9IMb"
      },
      "source": [
        "# RECCOMENDATIONS"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QbcewPV29OBP"
      },
      "source": [
        "From the Cells geo dataset, we learn the cities and regions that infrastructure development should be prioritized. COCODY, YOPOPOGUN, and ABOBO should get the changes first. LAGUNE , BAS SASSANDRA, and HAUT SASSANDRA are the regions that should get priority. ABIDJAN and INTIRIEUR can get equal priority.\n",
        "From the Telcom Dataset, we learn the best times for infrastructure change to be during office hours. We also learn that data infrastructure in country OTHER should be a priority over the voice and SMS. SMS infrastructure should be given top priority over voice and data as it has the most usage.\n"
      ]
    }
  ]
}