Data Cleaning of a customer call list.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "ce17fa66-29a4-42ba-af06-db9af85e1470",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "id": "6fae62c6-b269-4dd6-89ec-5b0210d9d1f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df =pd.read_excel(r\"C:\\Users\\BG COMPUTERS\\Videos\\Captures\\Pandas\\Reading files\\Customer Call List.xlsx\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "id": "de1a7793-211c-4b47-847c-7557fadff188",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "      <th>Not_Useful_Column</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123-545-5421</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123/643/9775</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>/White</td>\n",
       "      <td>7066950392</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123-543-2345</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876|678|3469</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123-545-5421   \n",
       "1        1002       Abed     Nadir  123/643/9775   \n",
       "2        1003     Walter    /White    7066950392   \n",
       "3        1004     Dwight   Schrute  123-543-2345   \n",
       "4        1005        Jon      Snow  876|678|3469   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \\\n",
       "0                  123 Shire Lane, Shire             Yes             No   \n",
       "1                    93 West Main Street              No            Yes   \n",
       "2                     298 Drugs Driveway               N            NaN   \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y   \n",
       "4                       123 Dragons Road               Y             No   \n",
       "\n",
       "   Not_Useful_Column  \n",
       "0               True  \n",
       "1              False  \n",
       "2               True  \n",
       "3               True  \n",
       "4               True  "
      ]
     },
     "execution_count": 241,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "id": "e75e0e13-f6e0-4753-bbc8-4f66a0164f4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# cleaning and standardizing data\n",
    "#1. drop duplicates( check the last two rows )\n",
    "df.drop_duplicates(inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "id": "36d2fd62-b51f-4ccc-9230-d8f1345885a3",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "      <th>Not_Useful_Column</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123-545-5421</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123/643/9775</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>/White</td>\n",
       "      <td>7066950392</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123-543-2345</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876|678|3469</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123-545-5421   \n",
       "1        1002       Abed     Nadir  123/643/9775   \n",
       "2        1003     Walter    /White    7066950392   \n",
       "3        1004     Dwight   Schrute  123-543-2345   \n",
       "4        1005        Jon      Snow  876|678|3469   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \\\n",
       "0                  123 Shire Lane, Shire             Yes             No   \n",
       "1                    93 West Main Street              No            Yes   \n",
       "2                     298 Drugs Driveway               N            NaN   \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y   \n",
       "4                       123 Dragons Road               Y             No   \n",
       "\n",
       "   Not_Useful_Column  \n",
       "0               True  \n",
       "1              False  \n",
       "2               True  \n",
       "3               True  \n",
       "4               True  "
      ]
     },
     "execution_count": 243,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "id": "1fbdbf97-a95f-4fe0-bf50-d0dbc41414b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# to remove irrelevant columns\n",
    "df= df.drop(columns ='Not_Useful_Column') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "id": "9908eeaf-d237-4712-b30b-57358bf669e1",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123-545-5421</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123/643/9775</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>/White</td>\n",
       "      <td>7066950392</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123-543-2345</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876|678|3469</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123-545-5421   \n",
       "1        1002       Abed     Nadir  123/643/9775   \n",
       "2        1003     Walter    /White    7066950392   \n",
       "3        1004     Dwight   Schrute  123-543-2345   \n",
       "4        1005        Jon      Snow  876|678|3469   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \n",
       "0                  123 Shire Lane, Shire             Yes             No  \n",
       "1                    93 West Main Street              No            Yes  \n",
       "2                     298 Drugs Driveway               N            NaN  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y  \n",
       "4                       123 Dragons Road               Y             No  "
      ]
     },
     "execution_count": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "id": "2972c8b9-1585-428d-b9bc-f63ce74d99fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "#lstrip for left, rstrip for right, and strip for both side\n",
    "df['Last_Name'] =df['Last_Name'].str.lstrip('...')\n",
    "df['Last_Name'] =df['Last_Name'].str.lstrip('/')\n",
    "df['Last_Name'] =df['Last_Name'].str.rstrip('_') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "id": "b7dde0a7-5c16-4bd6-af6a-e9ca1903db52",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123-545-5421</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123/643/9775</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>7066950392</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123-543-2345</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876|678|3469</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123-545-5421   \n",
       "1        1002       Abed     Nadir  123/643/9775   \n",
       "2        1003     Walter     White    7066950392   \n",
       "3        1004     Dwight   Schrute  123-543-2345   \n",
       "4        1005        Jon      Snow  876|678|3469   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \n",
       "0                  123 Shire Lane, Shire             Yes             No  \n",
       "1                    93 West Main Street              No            Yes  \n",
       "2                     298 Drugs Driveway               N            NaN  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y  \n",
       "4                       123 Dragons Road               Y             No  "
      ]
     },
     "execution_count": 247,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "id": "d275db76-3b9e-4bd5-a647-8c5762386033",
   "metadata": {},
   "outputs": [],
   "source": [
    "# to remove all the charcters at once\n",
    "df['Last_Name'] =df['Last_Name'].str.strip('123._/') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "id": "f1ab7cb3-868e-457e-b14a-c1e44fceddfb",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123-545-5421</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123/643/9775</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>7066950392</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123-543-2345</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876|678|3469</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123-545-5421   \n",
       "1        1002       Abed     Nadir  123/643/9775   \n",
       "2        1003     Walter     White    7066950392   \n",
       "3        1004     Dwight   Schrute  123-543-2345   \n",
       "4        1005        Jon      Snow  876|678|3469   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \n",
       "0                  123 Shire Lane, Shire             Yes             No  \n",
       "1                    93 West Main Street              No            Yes  \n",
       "2                     298 Drugs Driveway               N            NaN  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y  \n",
       "4                       123 Dragons Road               Y             No  "
      ]
     },
     "execution_count": 249,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "id": "994a0c72-315a-4ef9-9913-4188c51dcc5f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     123-545-5421\n",
       "1       1236439775\n",
       "2              NaN\n",
       "3     123-543-2345\n",
       "4     876|678|3469\n",
       "5     304-762-2467\n",
       "6              NaN\n",
       "7     876|678|3469\n",
       "8               Na\n",
       "9     123-545-5421\n",
       "10             NaN\n",
       "11             NaN\n",
       "12    123-543-2345\n",
       "13    876|678|3469\n",
       "14    304-762-2467\n",
       "15    123-545-5421\n",
       "16      1236439775\n",
       "17             NaN\n",
       "18              Na\n",
       "19    876|678|3469\n",
       "Name: Phone_Number, dtype: object"
      ]
     },
     "execution_count": 251,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df[\"Phone_Number\"].str.replace('[^a-zA-Z0-9]','').. this didnt work for me( will troubleshoot later)\n",
    "df[\"Phone_Number\"].str.replace('-','')\n",
    "df[\"Phone_Number\"].str.replace('|','')\n",
    "df[\"Phone_Number\"].str.replace('/','') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "id": "90c82a45-eb0b-4979-a255-a516b07ec05f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 20 entries, 0 to 19\n",
      "Data columns (total 7 columns):\n",
      " #   Column           Non-Null Count  Dtype \n",
      "---  ------           --------------  ----- \n",
      " 0   CustomerID       20 non-null     int64 \n",
      " 1   First_Name       20 non-null     object\n",
      " 2   Last_Name        19 non-null     object\n",
      " 3   Phone_Number     18 non-null     object\n",
      " 4   Address          20 non-null     object\n",
      " 5   Paying Customer  20 non-null     object\n",
      " 6   Do_Not_Contact   16 non-null     object\n",
      "dtypes: int64(1), object(6)\n",
      "memory usage: 1.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "id": "4ed5d55f-5d2e-4c2f-95c7-8cdb5765bc09",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     123-545-5421\n",
       "1     123/643/9775\n",
       "2              NaN\n",
       "3     123-543-2345\n",
       "4       8766783469\n",
       "5     304-762-2467\n",
       "6              NaN\n",
       "7       8766783469\n",
       "8              N/a\n",
       "9     123-545-5421\n",
       "10             NaN\n",
       "11             NaN\n",
       "12    123-543-2345\n",
       "13      8766783469\n",
       "14    304-762-2467\n",
       "15    123-545-5421\n",
       "16    123/643/9775\n",
       "17             NaN\n",
       "18             N/a\n",
       "19      8766783469\n",
       "Name: Phone_Number, dtype: object"
      ]
     },
     "execution_count": 253,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Phone_Number\"].str.replace('|','') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "id": "35e6a587-2a81-4b02-a094-782a5fe8a232",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       1235455421\n",
       "1     123/643/9775\n",
       "2              NaN\n",
       "3       1235432345\n",
       "4     876|678|3469\n",
       "5       3047622467\n",
       "6              NaN\n",
       "7     876|678|3469\n",
       "8              N/a\n",
       "9       1235455421\n",
       "10             NaN\n",
       "11             NaN\n",
       "12      1235432345\n",
       "13    876|678|3469\n",
       "14      3047622467\n",
       "15      1235455421\n",
       "16    123/643/9775\n",
       "17             NaN\n",
       "18             N/a\n",
       "19    876|678|3469\n",
       "Name: Phone_Number, dtype: object"
      ]
     },
     "execution_count": 255,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Phone_Number\"] = df[\"Phone_Number\"].str.replace('-','')\n",
    "df[\"Phone_Number\"] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "id": "9b135da6-ec3a-4d60-aa51-7cb7c93a7584",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Phone_Number\"] = df[\"Phone_Number\"].str.replace('/','') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "id": "11f8499e-cba4-4182-8d7c-1d2d552f4797",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       1235455421\n",
       "1       1236439775\n",
       "2              NaN\n",
       "3       1235432345\n",
       "4     876|678|3469\n",
       "5       3047622467\n",
       "6              NaN\n",
       "7     876|678|3469\n",
       "8               Na\n",
       "9       1235455421\n",
       "10             NaN\n",
       "11             NaN\n",
       "12      1235432345\n",
       "13    876|678|3469\n",
       "14      3047622467\n",
       "15      1235455421\n",
       "16      1236439775\n",
       "17             NaN\n",
       "18              Na\n",
       "19    876|678|3469\n",
       "Name: Phone_Number, dtype: object"
      ]
     },
     "execution_count": 257,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Phone_Number\"] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "id": "09c9272b-5d4a-4350-afd5-a1d0c1243eb2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     1235455421\n",
       "1     1236439775\n",
       "2            NaN\n",
       "3     1235432345\n",
       "4     8766783469\n",
       "5     3047622467\n",
       "6            NaN\n",
       "7     8766783469\n",
       "8             Na\n",
       "9     1235455421\n",
       "10           NaN\n",
       "11           NaN\n",
       "12    1235432345\n",
       "13    8766783469\n",
       "14    3047622467\n",
       "15    1235455421\n",
       "16    1236439775\n",
       "17           NaN\n",
       "18            Na\n",
       "19    8766783469\n",
       "Name: Phone_Number, dtype: object"
      ]
     },
     "execution_count": 258,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Phone_Number\"] = df[\"Phone_Number\"].str.replace('|','')\n",
    "df[\"Phone_Number\"] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "id": "eb0aa196-6538-4c42-9116-79730385d3b1",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>1235455421</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>1236439775</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>NaN</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>1235432345</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>8766783469</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name Phone_Number  \\\n",
       "0        1001      Frodo   Baggins   1235455421   \n",
       "1        1002       Abed     Nadir   1236439775   \n",
       "2        1003     Walter     White          NaN   \n",
       "3        1004     Dwight   Schrute   1235432345   \n",
       "4        1005        Jon      Snow   8766783469   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \n",
       "0                  123 Shire Lane, Shire             Yes             No  \n",
       "1                    93 West Main Street              No            Yes  \n",
       "2                     298 Drugs Driveway               N            NaN  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y  \n",
       "4                       123 Dragons Road               Y             No  "
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "id": "bc2c949f-a1f4-48b2-b47b-32886441cdf8",
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "'float' object is not subscriptable",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[260], line 2\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[38;5;66;03m#df[\"Phone_Number\"].apply(lambda x: x[0:3] + '-' + x[3:6] + '-' + x[6:10])\u001b[39;00m\n\u001b[1;32m----> 2\u001b[0m df[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mPhone_Number\u001b[39m\u001b[38;5;124m\"\u001b[39m]\u001b[38;5;241m.\u001b[39mapply(\u001b[38;5;28;01mlambda\u001b[39;00m x: x[\u001b[38;5;241m0\u001b[39m:\u001b[38;5;241m3\u001b[39m]\u001b[38;5;241m+\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m-\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;241m+\u001b[39m x[\u001b[38;5;241m3\u001b[39m:\u001b[38;5;241m6\u001b[39m]\u001b[38;5;241m+\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m_\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;241m+\u001b[39mx[\u001b[38;5;241m6\u001b[39m\u001b[38;5;241m-\u001b[39m\u001b[38;5;241m10\u001b[39m])\n",
      "File \u001b[1;32mC:\\Anaconda\\Lib\\site-packages\\pandas\\core\\series.py:4924\u001b[0m, in \u001b[0;36mSeries.apply\u001b[1;34m(self, func, convert_dtype, args, by_row, **kwargs)\u001b[0m\n\u001b[0;32m   4789\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mapply\u001b[39m(\n\u001b[0;32m   4790\u001b[0m     \u001b[38;5;28mself\u001b[39m,\n\u001b[0;32m   4791\u001b[0m     func: AggFuncType,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   4796\u001b[0m     \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs,\n\u001b[0;32m   4797\u001b[0m ) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m DataFrame \u001b[38;5;241m|\u001b[39m Series:\n\u001b[0;32m   4798\u001b[0m \u001b[38;5;250m    \u001b[39m\u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[0;32m   4799\u001b[0m \u001b[38;5;124;03m    Invoke function on values of Series.\u001b[39;00m\n\u001b[0;32m   4800\u001b[0m \n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   4915\u001b[0m \u001b[38;5;124;03m    dtype: float64\u001b[39;00m\n\u001b[0;32m   4916\u001b[0m \u001b[38;5;124;03m    \"\"\"\u001b[39;00m\n\u001b[0;32m   4917\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m SeriesApply(\n\u001b[0;32m   4918\u001b[0m         \u001b[38;5;28mself\u001b[39m,\n\u001b[0;32m   4919\u001b[0m         func,\n\u001b[0;32m   4920\u001b[0m         convert_dtype\u001b[38;5;241m=\u001b[39mconvert_dtype,\n\u001b[0;32m   4921\u001b[0m         by_row\u001b[38;5;241m=\u001b[39mby_row,\n\u001b[0;32m   4922\u001b[0m         args\u001b[38;5;241m=\u001b[39margs,\n\u001b[0;32m   4923\u001b[0m         kwargs\u001b[38;5;241m=\u001b[39mkwargs,\n\u001b[1;32m-> 4924\u001b[0m     )\u001b[38;5;241m.\u001b[39mapply()\n",
      "File \u001b[1;32mC:\\Anaconda\\Lib\\site-packages\\pandas\\core\\apply.py:1427\u001b[0m, in \u001b[0;36mSeriesApply.apply\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1424\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mapply_compat()\n\u001b[0;32m   1426\u001b[0m \u001b[38;5;66;03m# self.func is Callable\u001b[39;00m\n\u001b[1;32m-> 1427\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mapply_standard()\n",
      "File \u001b[1;32mC:\\Anaconda\\Lib\\site-packages\\pandas\\core\\apply.py:1507\u001b[0m, in \u001b[0;36mSeriesApply.apply_standard\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1501\u001b[0m \u001b[38;5;66;03m# row-wise access\u001b[39;00m\n\u001b[0;32m   1502\u001b[0m \u001b[38;5;66;03m# apply doesn't have a `na_action` keyword and for backward compat reasons\u001b[39;00m\n\u001b[0;32m   1503\u001b[0m \u001b[38;5;66;03m# we need to give `na_action=\"ignore\"` for categorical data.\u001b[39;00m\n\u001b[0;32m   1504\u001b[0m \u001b[38;5;66;03m# TODO: remove the `na_action=\"ignore\"` when that default has been changed in\u001b[39;00m\n\u001b[0;32m   1505\u001b[0m \u001b[38;5;66;03m#  Categorical (GH51645).\u001b[39;00m\n\u001b[0;32m   1506\u001b[0m action \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mignore\u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(obj\u001b[38;5;241m.\u001b[39mdtype, CategoricalDtype) \u001b[38;5;28;01melse\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[1;32m-> 1507\u001b[0m mapped \u001b[38;5;241m=\u001b[39m obj\u001b[38;5;241m.\u001b[39m_map_values(\n\u001b[0;32m   1508\u001b[0m     mapper\u001b[38;5;241m=\u001b[39mcurried, na_action\u001b[38;5;241m=\u001b[39maction, convert\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mconvert_dtype\n\u001b[0;32m   1509\u001b[0m )\n\u001b[0;32m   1511\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(mapped) \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(mapped[\u001b[38;5;241m0\u001b[39m], ABCSeries):\n\u001b[0;32m   1512\u001b[0m     \u001b[38;5;66;03m# GH#43986 Need to do list(mapped) in order to get treated as nested\u001b[39;00m\n\u001b[0;32m   1513\u001b[0m     \u001b[38;5;66;03m#  See also GH#25959 regarding EA support\u001b[39;00m\n\u001b[0;32m   1514\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m obj\u001b[38;5;241m.\u001b[39m_constructor_expanddim(\u001b[38;5;28mlist\u001b[39m(mapped), index\u001b[38;5;241m=\u001b[39mobj\u001b[38;5;241m.\u001b[39mindex)\n",
      "File \u001b[1;32mC:\\Anaconda\\Lib\\site-packages\\pandas\\core\\base.py:921\u001b[0m, in \u001b[0;36mIndexOpsMixin._map_values\u001b[1;34m(self, mapper, na_action, convert)\u001b[0m\n\u001b[0;32m    918\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(arr, ExtensionArray):\n\u001b[0;32m    919\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m arr\u001b[38;5;241m.\u001b[39mmap(mapper, na_action\u001b[38;5;241m=\u001b[39mna_action)\n\u001b[1;32m--> 921\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m algorithms\u001b[38;5;241m.\u001b[39mmap_array(arr, mapper, na_action\u001b[38;5;241m=\u001b[39mna_action, convert\u001b[38;5;241m=\u001b[39mconvert)\n",
      "File \u001b[1;32mC:\\Anaconda\\Lib\\site-packages\\pandas\\core\\algorithms.py:1743\u001b[0m, in \u001b[0;36mmap_array\u001b[1;34m(arr, mapper, na_action, convert)\u001b[0m\n\u001b[0;32m   1741\u001b[0m values \u001b[38;5;241m=\u001b[39m arr\u001b[38;5;241m.\u001b[39mastype(\u001b[38;5;28mobject\u001b[39m, copy\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m)\n\u001b[0;32m   1742\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m na_action \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m-> 1743\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m lib\u001b[38;5;241m.\u001b[39mmap_infer(values, mapper, convert\u001b[38;5;241m=\u001b[39mconvert)\n\u001b[0;32m   1744\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m   1745\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m lib\u001b[38;5;241m.\u001b[39mmap_infer_mask(\n\u001b[0;32m   1746\u001b[0m         values, mapper, mask\u001b[38;5;241m=\u001b[39misna(values)\u001b[38;5;241m.\u001b[39mview(np\u001b[38;5;241m.\u001b[39muint8), convert\u001b[38;5;241m=\u001b[39mconvert\n\u001b[0;32m   1747\u001b[0m     )\n",
      "File \u001b[1;32mlib.pyx:2972\u001b[0m, in \u001b[0;36mpandas._libs.lib.map_infer\u001b[1;34m()\u001b[0m\n",
      "Cell \u001b[1;32mIn[260], line 2\u001b[0m, in \u001b[0;36m<lambda>\u001b[1;34m(x)\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[38;5;66;03m#df[\"Phone_Number\"].apply(lambda x: x[0:3] + '-' + x[3:6] + '-' + x[6:10])\u001b[39;00m\n\u001b[1;32m----> 2\u001b[0m df[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mPhone_Number\u001b[39m\u001b[38;5;124m\"\u001b[39m]\u001b[38;5;241m.\u001b[39mapply(\u001b[38;5;28;01mlambda\u001b[39;00m x: x[\u001b[38;5;241m0\u001b[39m:\u001b[38;5;241m3\u001b[39m]\u001b[38;5;241m+\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m-\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;241m+\u001b[39m x[\u001b[38;5;241m3\u001b[39m:\u001b[38;5;241m6\u001b[39m]\u001b[38;5;241m+\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m_\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;241m+\u001b[39mx[\u001b[38;5;241m6\u001b[39m\u001b[38;5;241m-\u001b[39m\u001b[38;5;241m10\u001b[39m])\n",
      "\u001b[1;31mTypeError\u001b[0m: 'float' object is not subscriptable"
     ]
    }
   ],
   "source": [
    "#df[\"Phone_Number\"].apply(lambda x: x[0:3] + '-' + x[3:6] + '-' + x[6:10])\n",
    "df[\"Phone_Number\"].apply(lambda x: x[0:3]+'-'+ x[3:6]+'_'+x[6-10]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "id": "a5a820f1-5d00-4daf-bf4a-ca015fef60d0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "Index: 20 entries, 0 to 19\n",
      "Series name: Phone_Number\n",
      "Non-Null Count  Dtype \n",
      "--------------  ----- \n",
      "15 non-null     object\n",
      "dtypes: object(1)\n",
      "memory usage: 320.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "# the error indicate that the data type in the phonenumnber column is not a string\n",
    "df[\"Phone_Number\"].info() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "id": "ee905ef7-6c82-4588-adf0-19b323ce0bc8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'0     1235455421\\n1     1236439775\\n2            NaN\\n3     1235432345\\n4     8766783469\\n5     3047622467\\n6            NaN\\n7     8766783469\\n8             Na\\n9     1235455421\\n10           NaN\\n11           NaN\\n12    1235432345\\n13    8766783469\\n14    3047622467\\n15    1235455421\\n16    1236439775\\n17           NaN\\n18            Na\\n19    8766783469\\nName: Phone_Number, dtype: object'"
      ]
     },
     "execution_count": 262,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we need to convert all data in that collumn to string\n",
    "# we might want to change it like this, but doesnt look right\n",
    "str(df[\"Phone_Number\"]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "id": "afb41718-0f6e-488c-8a62-9260add7f60c",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>1235455421</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>1236439775</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>nan</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>1235432345</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>8766783469</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name Phone_Number  \\\n",
       "0        1001      Frodo   Baggins   1235455421   \n",
       "1        1002       Abed     Nadir   1236439775   \n",
       "2        1003     Walter     White          nan   \n",
       "3        1004     Dwight   Schrute   1235432345   \n",
       "4        1005        Jon      Snow   8766783469   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \n",
       "0                  123 Shire Lane, Shire             Yes             No  \n",
       "1                    93 West Main Street              No            Yes  \n",
       "2                     298 Drugs Driveway               N            NaN  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y  \n",
       "4                       123 Dragons Road               Y             No  "
      ]
     },
     "execution_count": 263,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# to enable us do it right, we either loop through each value or use lambda function. We use the latter\n",
    "df[\"Phone_Number\"]= df[\"Phone_Number\"].apply(lambda x: str(x))\n",
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "id": "dbca7090-ef79-410d-9264-a8edeedd8a75",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123--64-3-97</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>nan----</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123--54-3-23</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54   \n",
       "1        1002       Abed     Nadir  123--64-3-97   \n",
       "2        1003     Walter     White       nan----   \n",
       "3        1004     Dwight   Schrute  123--54-3-23   \n",
       "4        1005        Jon      Snow  876--67-8-34   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \n",
       "0                  123 Shire Lane, Shire             Yes             No  \n",
       "1                    93 West Main Street              No            Yes  \n",
       "2                     298 Drugs Driveway               N            NaN  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y  \n",
       "4                       123 Dragons Road               Y             No  "
      ]
     },
     "execution_count": 265,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Phone_Number\"]= df[\"Phone_Number\"].apply(lambda x: x[0:3] + '-' + x[3:6] + '-' + x[6:10])\n",
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "id": "6ecf3321-ec74-4b01-b627-1d9479108663",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123--64-3-97</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123--54-3-23</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54   \n",
       "1        1002       Abed     Nadir  123--64-3-97   \n",
       "2        1003     Walter     White            --   \n",
       "3        1004     Dwight   Schrute  123--54-3-23   \n",
       "4        1005        Jon      Snow  876--67-8-34   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \n",
       "0                  123 Shire Lane, Shire             Yes             No  \n",
       "1                    93 West Main Street              No            Yes  \n",
       "2                     298 Drugs Driveway               N            NaN  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y  \n",
       "4                       123 Dragons Road               Y             No  "
      ]
     },
     "execution_count": 266,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Phone_Number\"]= df[\"Phone_Number\"].str.replace('nan--','')\n",
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "id": "812f6fef-ad1d-4564-85e3-78bd0bb85790",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123--64-3-97</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123--54-3-23</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54   \n",
       "1        1002       Abed     Nadir  123--64-3-97   \n",
       "2        1003     Walter     White            --   \n",
       "3        1004     Dwight   Schrute  123--54-3-23   \n",
       "4        1005        Jon      Snow  876--67-8-34   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \n",
       "0                  123 Shire Lane, Shire             Yes             No  \n",
       "1                    93 West Main Street              No            Yes  \n",
       "2                     298 Drugs Driveway               N            NaN  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y  \n",
       "4                       123 Dragons Road               Y             No  "
      ]
     },
     "execution_count": 267,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Phone_Number\"]= df[\"Phone_Number\"].str.replace('Na--','')\n",
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "id": "e3d77a42-5538-40ef-9d43-105af2840606",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123--64-3-97</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123--54-3-23</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54   \n",
       "1        1002       Abed     Nadir  123--64-3-97   \n",
       "2        1003     Walter     White            --   \n",
       "3        1004     Dwight   Schrute  123--54-3-23   \n",
       "4        1005        Jon      Snow  876--67-8-34   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \n",
       "0                  123 Shire Lane, Shire             Yes             No  \n",
       "1                    93 West Main Street              No            Yes  \n",
       "2                     298 Drugs Driveway               N            NaN  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y  \n",
       "4                       123 Dragons Road               Y             No  "
      ]
     },
     "execution_count": 268,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "id": "89446866-e15f-4d55-8aeb-f5b9c8c6b9ad",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "      <th>Street Address</th>\n",
       "      <th>State</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>123 Shire Lane</td>\n",
       "      <td>Shire</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123--64-3-97</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123--54-3-23</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Y</td>\n",
       "      <td>980 Paper Avenue</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>18503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54   \n",
       "1        1002       Abed     Nadir  123--64-3-97   \n",
       "2        1003     Walter     White            --   \n",
       "3        1004     Dwight   Schrute  123--54-3-23   \n",
       "4        1005        Jon      Snow  876--67-8-34   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \\\n",
       "0                  123 Shire Lane, Shire             Yes             No   \n",
       "1                    93 West Main Street              No            Yes   \n",
       "2                     298 Drugs Driveway               N            NaN   \n",
       "3  980 Paper Avenue, Pennsylvania, 18503             Yes              Y   \n",
       "4                       123 Dragons Road               Y             No   \n",
       "\n",
       "        Street Address          State ZipCode  \n",
       "0       123 Shire Lane          Shire    None  \n",
       "1  93 West Main Street           None    None  \n",
       "2   298 Drugs Driveway           None    None  \n",
       "3     980 Paper Avenue   Pennsylvania   18503  \n",
       "4     123 Dragons Road           None    None  "
      ]
     },
     "execution_count": 269,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# to assign the address to new columns\n",
    "df[[\"Street Address\",\"State\",\"ZipCode\"]] = df[\"Address\"].str.split(',',expand=True)\n",
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 270,
   "id": "ae5a9728-3a37-4b23-b22d-acf9c5dc1c3c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     Yeses\n",
       "1        No\n",
       "2         N\n",
       "3     Yeses\n",
       "4       Yes\n",
       "5     Yeses\n",
       "6        No\n",
       "7         N\n",
       "8     Yeses\n",
       "9     Yeses\n",
       "10    Yeses\n",
       "11      Yes\n",
       "12    Yeses\n",
       "13    Yeses\n",
       "14        N\n",
       "15       No\n",
       "16    Yeses\n",
       "17      Yes\n",
       "18      N/a\n",
       "19    Yeses\n",
       "Name: Paying Customer, dtype: object"
      ]
     },
     "execution_count": 270,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# standardize the paying_customer column with Yes and No\n",
    "df[\"Paying Customer\"].str.replace('Y','Yes') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "id": "83898ac6-7685-40ab-ba6a-7e1f02ff9516",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       Y\n",
       "1      No\n",
       "2       N\n",
       "3       Y\n",
       "4       Y\n",
       "5       Y\n",
       "6      No\n",
       "7       N\n",
       "8       Y\n",
       "9       Y\n",
       "10      Y\n",
       "11      Y\n",
       "12      Y\n",
       "13      Y\n",
       "14      N\n",
       "15     No\n",
       "16      Y\n",
       "17      Y\n",
       "18    N/a\n",
       "19      Y\n",
       "Name: Paying Customer, dtype: object"
      ]
     },
     "execution_count": 271,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this doesnt look right as it modifies Yes to Y as well, so we do the reverse\n",
    "df[\"Paying Customer\"]= df[\"Paying Customer\"].str.replace('Yes','Y')\n",
    "df[\"Paying Customer\"] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "id": "8038beb6-3c88-4e6f-982d-9eaeead3cb86",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       Y\n",
       "1       N\n",
       "2       N\n",
       "3       Y\n",
       "4       Y\n",
       "5       Y\n",
       "6       N\n",
       "7       N\n",
       "8       Y\n",
       "9       Y\n",
       "10      Y\n",
       "11      Y\n",
       "12      Y\n",
       "13      Y\n",
       "14      N\n",
       "15      N\n",
       "16      Y\n",
       "17      Y\n",
       "18    N/a\n",
       "19      Y\n",
       "Name: Paying Customer, dtype: object"
      ]
     },
     "execution_count": 273,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Paying Customer\"]= df[\"Paying Customer\"].str.replace('No','N')\n",
    "df[\"Paying Customer\"] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "id": "d6501816-27fe-4984-afcd-0694120f61a3",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "      <th>Street Address</th>\n",
       "      <th>State</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "      <td>123 Shire Lane</td>\n",
       "      <td>Shire</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123--64-3-97</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123--54-3-23</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>980 Paper Avenue</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>18503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>No</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54   \n",
       "1        1002       Abed     Nadir  123--64-3-97   \n",
       "2        1003     Walter     White            --   \n",
       "3        1004     Dwight   Schrute  123--54-3-23   \n",
       "4        1005        Jon      Snow  876--67-8-34   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \\\n",
       "0                  123 Shire Lane, Shire               Y             No   \n",
       "1                    93 West Main Street               N            Yes   \n",
       "2                     298 Drugs Driveway               N            NaN   \n",
       "3  980 Paper Avenue, Pennsylvania, 18503               Y              Y   \n",
       "4                       123 Dragons Road               Y             No   \n",
       "\n",
       "        Street Address          State ZipCode  \n",
       "0       123 Shire Lane          Shire    None  \n",
       "1  93 West Main Street           None    None  \n",
       "2   298 Drugs Driveway           None    None  \n",
       "3     980 Paper Avenue   Pennsylvania   18503  \n",
       "4     123 Dragons Road           None    None  "
      ]
     },
     "execution_count": 274,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "id": "ad733e47-39a8-429b-94e5-f6ca3eaf81dc",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "      <th>Street Address</th>\n",
       "      <th>State</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Shire Lane</td>\n",
       "      <td>Shire</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123--64-3-97</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123--54-3-23</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>980 Paper Avenue</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>18503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54   \n",
       "1        1002       Abed     Nadir  123--64-3-97   \n",
       "2        1003     Walter     White            --   \n",
       "3        1004     Dwight   Schrute  123--54-3-23   \n",
       "4        1005        Jon      Snow  876--67-8-34   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \\\n",
       "0                  123 Shire Lane, Shire               Y              N   \n",
       "1                    93 West Main Street               N              Y   \n",
       "2                     298 Drugs Driveway               N            NaN   \n",
       "3  980 Paper Avenue, Pennsylvania, 18503               Y              Y   \n",
       "4                       123 Dragons Road               Y              N   \n",
       "\n",
       "        Street Address          State ZipCode  \n",
       "0       123 Shire Lane          Shire    None  \n",
       "1  93 West Main Street           None    None  \n",
       "2   298 Drugs Driveway           None    None  \n",
       "3     980 Paper Avenue   Pennsylvania   18503  \n",
       "4     123 Dragons Road           None    None  "
      ]
     },
     "execution_count": 275,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# doing the same for Do not contact column\n",
    "df[\"Do_Not_Contact\"]= df[\"Do_Not_Contact\"].str.replace('Yes','Y')\n",
    "df[\"Do_Not_Contact\"]= df[\"Do_Not_Contact\"].str.replace('No','N')\n",
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "id": "4c6e6921-704f-44e3-9aee-f23eb8240376",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "      <th>Street Address</th>\n",
       "      <th>State</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Shire Lane</td>\n",
       "      <td>Shire</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123--64-3-97</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123--54-3-23</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>980 Paper Avenue</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>18503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54   \n",
       "1        1002       Abed     Nadir  123--64-3-97   \n",
       "2        1003     Walter     White            --   \n",
       "3        1004     Dwight   Schrute  123--54-3-23   \n",
       "4        1005        Jon      Snow  876--67-8-34   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \\\n",
       "0                  123 Shire Lane, Shire               Y              N   \n",
       "1                    93 West Main Street               N              Y   \n",
       "2                     298 Drugs Driveway               N                  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503               Y              Y   \n",
       "4                       123 Dragons Road               Y              N   \n",
       "\n",
       "        Street Address          State ZipCode  \n",
       "0       123 Shire Lane          Shire          \n",
       "1  93 West Main Street                         \n",
       "2   298 Drugs Driveway                         \n",
       "3     980 Paper Avenue   Pennsylvania   18503  \n",
       "4     123 Dragons Road                         "
      ]
     },
     "execution_count": 278,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Usinf the fillna keyword to to remove na values, \n",
    "df=df.fillna('')\n",
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "id": "62ec8418-27ce-4de5-8c65-410ed4c8ce79",
   "metadata": {},
   "outputs": [],
   "source": [
    "# using r theeplace command to remove other na values that appear as string\n",
    "df[\"Address\"]=df[\"Address\"].str.replace('N/a','') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "id": "e9ea63cc-f65b-42b6-919f-669f4e5f6669",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "      <th>Street Address</th>\n",
       "      <th>State</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Shire Lane</td>\n",
       "      <td>Shire</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123--64-3-97</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123--54-3-23</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>980 Paper Avenue</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>18503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54   \n",
       "1        1002       Abed     Nadir  123--64-3-97   \n",
       "2        1003     Walter     White            --   \n",
       "3        1004     Dwight   Schrute  123--54-3-23   \n",
       "4        1005        Jon      Snow  876--67-8-34   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \\\n",
       "0                  123 Shire Lane, Shire               Y              N   \n",
       "1                    93 West Main Street               N              Y   \n",
       "2                     298 Drugs Driveway               N                  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503               Y              Y   \n",
       "4                       123 Dragons Road               Y              N   \n",
       "\n",
       "        Street Address          State ZipCode  \n",
       "0       123 Shire Lane          Shire          \n",
       "1  93 West Main Street                         \n",
       "2   298 Drugs Driveway                         \n",
       "3     980 Paper Avenue   Pennsylvania   18503  \n",
       "4     123 Dragons Road                         "
      ]
     },
     "execution_count": 280,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "id": "ceca7d1f-bcfa-4c80-9980-30e4224f19e0",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "      <th>Street Address</th>\n",
       "      <th>State</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Shire Lane</td>\n",
       "      <td>Shire</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Abed</td>\n",
       "      <td>Nadir</td>\n",
       "      <td>123--64-3-97</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>93 West Main Street</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Dwight</td>\n",
       "      <td>Schrute</td>\n",
       "      <td>123--54-3-23</td>\n",
       "      <td>980 Paper Avenue, Pennsylvania, 18503</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>980 Paper Avenue</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>18503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54   \n",
       "1        1002       Abed     Nadir  123--64-3-97   \n",
       "2        1003     Walter     White            --   \n",
       "3        1004     Dwight   Schrute  123--54-3-23   \n",
       "4        1005        Jon      Snow  876--67-8-34   \n",
       "\n",
       "                                 Address Paying Customer Do_Not_Contact  \\\n",
       "0                  123 Shire Lane, Shire               Y              N   \n",
       "1                    93 West Main Street               N              Y   \n",
       "2                     298 Drugs Driveway               N                  \n",
       "3  980 Paper Avenue, Pennsylvania, 18503               Y              Y   \n",
       "4                       123 Dragons Road               Y              N   \n",
       "\n",
       "        Street Address          State ZipCode  \n",
       "0       123 Shire Lane          Shire          \n",
       "1  93 West Main Street                         \n",
       "2   298 Drugs Driveway                         \n",
       "3     980 Paper Avenue   Pennsylvania   18503  \n",
       "4     123 Dragons Road                         "
      ]
     },
     "execution_count": 281,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Paying Customer\"]=df[\"Paying Customer\"].str.replace('N/a','')\n",
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "id": "5a267581-0892-4806-a142-7f297ac2d6be",
   "metadata": {},
   "outputs": [],
   "source": [
    "# to remove the row/data that is Yes to Do_not_contact\n",
    "for x in df.index:\n",
    "    if df.loc[x,\"Do_Not_Contact\"] == 'Y':\n",
    "        df.drop(x,inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "id": "9ae3a453-78fd-4a26-a602-9517d6e7a397",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "      <th>Street Address</th>\n",
       "      <th>State</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Shire Lane</td>\n",
       "      <td>Shire</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007</td>\n",
       "      <td>Jeff</td>\n",
       "      <td>Winger</td>\n",
       "      <td>--</td>\n",
       "      <td>1209 South Street</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>1209 South Street</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1008</td>\n",
       "      <td>Sherlock</td>\n",
       "      <td>Holmes</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>98 Clue Drive</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>98 Clue Drive</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number                Address  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54  123 Shire Lane, Shire   \n",
       "2        1003     Walter     White            --     298 Drugs Driveway   \n",
       "4        1005        Jon      Snow  876--67-8-34       123 Dragons Road   \n",
       "6        1007       Jeff    Winger            --      1209 South Street   \n",
       "7        1008   Sherlock    Holmes  876--67-8-34          98 Clue Drive   \n",
       "\n",
       "  Paying Customer Do_Not_Contact      Street Address   State ZipCode  \n",
       "0               Y              N      123 Shire Lane   Shire          \n",
       "2               N                 298 Drugs Driveway                  \n",
       "4               Y              N    123 Dragons Road                  \n",
       "6               N              N   1209 South Street                  \n",
       "7               N              N       98 Clue Drive                  "
      ]
     },
     "execution_count": 283,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "id": "ada4ea9d-af08-48a2-b949-5a9b36c8e72d",
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in df.index:\n",
    "    if df.loc[x,\"Phone_Number\"] == '':\n",
    "        df.drop(x,inplace=True)\n",
    "#or values that are blanks, we can also drop the rows with\n",
    "#df= df.dropna(subset= 'Phone_Number\"),inplace =True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "id": "a90c5df7-f264-43dd-b1f4-508732a769fd",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "      <th>Street Address</th>\n",
       "      <th>State</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Shire Lane</td>\n",
       "      <td>Shire</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007</td>\n",
       "      <td>Jeff</td>\n",
       "      <td>Winger</td>\n",
       "      <td>--</td>\n",
       "      <td>1209 South Street</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>1209 South Street</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1008</td>\n",
       "      <td>Sherlock</td>\n",
       "      <td>Holmes</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>98 Clue Drive</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>98 Clue Drive</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number                Address  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54  123 Shire Lane, Shire   \n",
       "2        1003     Walter     White            --     298 Drugs Driveway   \n",
       "4        1005        Jon      Snow  876--67-8-34       123 Dragons Road   \n",
       "6        1007       Jeff    Winger            --      1209 South Street   \n",
       "7        1008   Sherlock    Holmes  876--67-8-34          98 Clue Drive   \n",
       "\n",
       "  Paying Customer Do_Not_Contact      Street Address   State ZipCode  \n",
       "0               Y              N      123 Shire Lane   Shire          \n",
       "2               N                 298 Drugs Driveway                  \n",
       "4               Y              N    123 Dragons Road                  \n",
       "6               N              N   1209 South Street                  \n",
       "7               N              N       98 Clue Drive                  "
      ]
     },
     "execution_count": 285,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "id": "cbf60396-45bc-46a5-be49-91aee7a7586a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# to reset the index to make the number appear in increasing order\n",
    "df =df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "id": "3042e144-3566-4678-9287-3ebe9edffbf0",
   "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>First_Name</th>\n",
       "      <th>Last_Name</th>\n",
       "      <th>Phone_Number</th>\n",
       "      <th>Address</th>\n",
       "      <th>Paying Customer</th>\n",
       "      <th>Do_Not_Contact</th>\n",
       "      <th>Street Address</th>\n",
       "      <th>State</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Frodo</td>\n",
       "      <td>Baggins</td>\n",
       "      <td>123--54-5-54</td>\n",
       "      <td>123 Shire Lane, Shire</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Shire Lane</td>\n",
       "      <td>Shire</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1003</td>\n",
       "      <td>Walter</td>\n",
       "      <td>White</td>\n",
       "      <td>--</td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>298 Drugs Driveway</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Snow</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>123 Dragons Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1007</td>\n",
       "      <td>Jeff</td>\n",
       "      <td>Winger</td>\n",
       "      <td>--</td>\n",
       "      <td>1209 South Street</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>1209 South Street</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1008</td>\n",
       "      <td>Sherlock</td>\n",
       "      <td>Holmes</td>\n",
       "      <td>876--67-8-34</td>\n",
       "      <td>98 Clue Drive</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>98 Clue Drive</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID First_Name Last_Name  Phone_Number                Address  \\\n",
       "0        1001      Frodo   Baggins  123--54-5-54  123 Shire Lane, Shire   \n",
       "1        1003     Walter     White            --     298 Drugs Driveway   \n",
       "2        1005        Jon      Snow  876--67-8-34       123 Dragons Road   \n",
       "3        1007       Jeff    Winger            --      1209 South Street   \n",
       "4        1008   Sherlock    Holmes  876--67-8-34          98 Clue Drive   \n",
       "\n",
       "  Paying Customer Do_Not_Contact      Street Address   State ZipCode  \n",
       "0               Y              N      123 Shire Lane   Shire          \n",
       "1               N                 298 Drugs Driveway                  \n",
       "2               Y              N    123 Dragons Road                  \n",
       "3               N              N   1209 South Street                  \n",
       "4               N              N       98 Clue Drive                  "
      ]
     },
     "execution_count": 287,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "id": "32a02fa6-4a8e-4b36-8662-f1368d1a606e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# saving the clean data to a csv without the index column\n",
    "df.to_csv(r\"C:\\Users\\BG COMPUTERS\\Phython_Portfolio projects.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "306b216a-59d3-4f20-949d-7dca097fddd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#End"
   ]
  }
 ],
 "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}