lab-dw-data-cleaning-and-formatting.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "25d7736c-ba17-4aff-b6bb-66eba20fbf4e",
   "metadata": {
    "id": "25d7736c-ba17-4aff-b6bb-66eba20fbf4e"
   },
   "source": [
    "# Lab | Data Cleaning and Formatting"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1973e9e-8be6-4039-b70e-d73ee0d94c99",
   "metadata": {
    "id": "d1973e9e-8be6-4039-b70e-d73ee0d94c99"
   },
   "source": [
    "In this lab, we will be working with the customer data from an insurance company, which can be found in the CSV file located at the following link: https://raw.githubusercontent.com/data-bootcamp-v4/data/main/file1.csv\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31b8a9e7-7db9-4604-991b-ef6771603e57",
   "metadata": {
    "id": "31b8a9e7-7db9-4604-991b-ef6771603e57"
   },
   "source": [
    "# Challenge 1: Data Cleaning and Formatting"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81553f19-9f2c-484b-8940-520aff884022",
   "metadata": {
    "id": "81553f19-9f2c-484b-8940-520aff884022"
   },
   "source": [
    "## Exercise 1: Cleaning Column Names"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34a929f4-1be4-4fa8-adda-42ffd920be90",
   "metadata": {
    "id": "34a929f4-1be4-4fa8-adda-42ffd920be90"
   },
   "source": [
    "To ensure consistency and ease of use, standardize the column names of the dataframe. Start by taking a first look at the dataframe and identifying any column names that need to be modified. Use appropriate naming conventions and make sure that column names are descriptive and informative.\n",
    "\n",
    "*Hint*:\n",
    "- *Column names should be in lower case*\n",
    "- *White spaces in column names should be replaced by `_`*\n",
    "- *`st` could be replaced for `state`*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "5810735c-8056-4442-bbf2-dda38d3e284a",
   "metadata": {
    "id": "5810735c-8056-4442-bbf2-dda38d3e284a"
   },
   "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>Customer</th>\n",
       "      <th>ST</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>Education</th>\n",
       "      <th>Customer Lifetime Value</th>\n",
       "      <th>Income</th>\n",
       "      <th>Monthly Premium Auto</th>\n",
       "      <th>Number of Open Complaints</th>\n",
       "      <th>Policy Type</th>\n",
       "      <th>Vehicle Class</th>\n",
       "      <th>Total Claim Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RB50392</td>\n",
       "      <td>Washington</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Master</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</td>\n",
       "      <td>2.704934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>QZ44356</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>F</td>\n",
       "      <td>Bachelor</td>\n",
       "      <td>697953.59%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</td>\n",
       "      <td>1131.464935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AI49188</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>F</td>\n",
       "      <td>Bachelor</td>\n",
       "      <td>1288743.17%</td>\n",
       "      <td>48767.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Two-Door Car</td>\n",
       "      <td>566.472247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WW63253</td>\n",
       "      <td>California</td>\n",
       "      <td>M</td>\n",
       "      <td>Bachelor</td>\n",
       "      <td>764586.18%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>1/0/00</td>\n",
       "      <td>Corporate Auto</td>\n",
       "      <td>SUV</td>\n",
       "      <td>529.881344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GA49547</td>\n",
       "      <td>Washington</td>\n",
       "      <td>M</td>\n",
       "      <td>High School or Below</td>\n",
       "      <td>536307.65%</td>\n",
       "      <td>36357.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</td>\n",
       "      <td>17.269323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4003</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4004</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4005</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4006</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4007</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4008 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Customer          ST GENDER             Education  \\\n",
       "0     RB50392  Washington    NaN                Master   \n",
       "1     QZ44356     Arizona      F              Bachelor   \n",
       "2     AI49188      Nevada      F              Bachelor   \n",
       "3     WW63253  California      M              Bachelor   \n",
       "4     GA49547  Washington      M  High School or Below   \n",
       "...       ...         ...    ...                   ...   \n",
       "4003      NaN         NaN    NaN                   NaN   \n",
       "4004      NaN         NaN    NaN                   NaN   \n",
       "4005      NaN         NaN    NaN                   NaN   \n",
       "4006      NaN         NaN    NaN                   NaN   \n",
       "4007      NaN         NaN    NaN                   NaN   \n",
       "\n",
       "     Customer Lifetime Value   Income  Monthly Premium Auto  \\\n",
       "0                        NaN      0.0                1000.0   \n",
       "1                 697953.59%      0.0                  94.0   \n",
       "2                1288743.17%  48767.0                 108.0   \n",
       "3                 764586.18%      0.0                 106.0   \n",
       "4                 536307.65%  36357.0                  68.0   \n",
       "...                      ...      ...                   ...   \n",
       "4003                     NaN      NaN                   NaN   \n",
       "4004                     NaN      NaN                   NaN   \n",
       "4005                     NaN      NaN                   NaN   \n",
       "4006                     NaN      NaN                   NaN   \n",
       "4007                     NaN      NaN                   NaN   \n",
       "\n",
       "     Number of Open Complaints     Policy Type  Vehicle Class  \\\n",
       "0                       1/0/00   Personal Auto  Four-Door Car   \n",
       "1                       1/0/00   Personal Auto  Four-Door Car   \n",
       "2                       1/0/00   Personal Auto   Two-Door Car   \n",
       "3                       1/0/00  Corporate Auto            SUV   \n",
       "4                       1/0/00   Personal Auto  Four-Door Car   \n",
       "...                        ...             ...            ...   \n",
       "4003                       NaN             NaN            NaN   \n",
       "4004                       NaN             NaN            NaN   \n",
       "4005                       NaN             NaN            NaN   \n",
       "4006                       NaN             NaN            NaN   \n",
       "4007                       NaN             NaN            NaN   \n",
       "\n",
       "      Total Claim Amount  \n",
       "0               2.704934  \n",
       "1            1131.464935  \n",
       "2             566.472247  \n",
       "3             529.881344  \n",
       "4              17.269323  \n",
       "...                  ...  \n",
       "4003                 NaN  \n",
       "4004                 NaN  \n",
       "4005                 NaN  \n",
       "4006                 NaN  \n",
       "4007                 NaN  \n",
       "\n",
       "[4008 rows x 11 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Your code here\n",
    "import pandas as pd\n",
    "url = 'https://raw.githubusercontent.com/data-bootcamp-v4/data/main/file1.csv'\n",
    "ic = pd.read_csv(url)\n",
    "ic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "a5f3967d-8104-48d5-9022-696c1ae874ee",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Column Names: Index(['Customer', 'ST', 'GENDER', 'Education', 'Customer Lifetime Value',\n",
      "       'Income', 'Monthly Premium Auto', 'Number of Open Complaints',\n",
      "       'Policy Type', 'Vehicle Class', 'Total Claim Amount'],\n",
      "      dtype='object')\n",
      "Cleaned Column Names: Index(['customer', 'state', 'gender', 'education', 'customer_lifetime_value',\n",
      "       'income', 'monthly_premium_auto', 'number_of_open_complaints',\n",
      "       'policy_type', 'vehicle_class', 'total_claim_amount'],\n",
      "      dtype='object')\n"
     ]
    },
    {
     "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>customer</th>\n",
       "      <th>state</th>\n",
       "      <th>gender</th>\n",
       "      <th>education</th>\n",
       "      <th>customer_lifetime_value</th>\n",
       "      <th>income</th>\n",
       "      <th>monthly_premium_auto</th>\n",
       "      <th>number_of_open_complaints</th>\n",
       "      <th>policy_type</th>\n",
       "      <th>vehicle_class</th>\n",
       "      <th>total_claim_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RB50392</td>\n",
       "      <td>Washington</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Master</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</td>\n",
       "      <td>2.704934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>QZ44356</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>F</td>\n",
       "      <td>Bachelor</td>\n",
       "      <td>697953.59%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</td>\n",
       "      <td>1131.464935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AI49188</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>F</td>\n",
       "      <td>Bachelor</td>\n",
       "      <td>1288743.17%</td>\n",
       "      <td>48767.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Two-Door Car</td>\n",
       "      <td>566.472247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WW63253</td>\n",
       "      <td>California</td>\n",
       "      <td>M</td>\n",
       "      <td>Bachelor</td>\n",
       "      <td>764586.18%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>1/0/00</td>\n",
       "      <td>Corporate Auto</td>\n",
       "      <td>SUV</td>\n",
       "      <td>529.881344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GA49547</td>\n",
       "      <td>Washington</td>\n",
       "      <td>M</td>\n",
       "      <td>High School or Below</td>\n",
       "      <td>536307.65%</td>\n",
       "      <td>36357.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</td>\n",
       "      <td>17.269323</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  customer       state gender             education customer_lifetime_value  \\\n",
       "0  RB50392  Washington    NaN                Master                     NaN   \n",
       "1  QZ44356     Arizona      F              Bachelor              697953.59%   \n",
       "2  AI49188      Nevada      F              Bachelor             1288743.17%   \n",
       "3  WW63253  California      M              Bachelor              764586.18%   \n",
       "4  GA49547  Washington      M  High School or Below              536307.65%   \n",
       "\n",
       "    income  monthly_premium_auto number_of_open_complaints     policy_type  \\\n",
       "0      0.0                1000.0                    1/0/00   Personal Auto   \n",
       "1      0.0                  94.0                    1/0/00   Personal Auto   \n",
       "2  48767.0                 108.0                    1/0/00   Personal Auto   \n",
       "3      0.0                 106.0                    1/0/00  Corporate Auto   \n",
       "4  36357.0                  68.0                    1/0/00   Personal Auto   \n",
       "\n",
       "   vehicle_class  total_claim_amount  \n",
       "0  Four-Door Car            2.704934  \n",
       "1  Four-Door Car         1131.464935  \n",
       "2   Two-Door Car          566.472247  \n",
       "3            SUV          529.881344  \n",
       "4  Four-Door Car           17.269323  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "# Display the original column names\n",
    "print(\"Original Column Names:\", ic.columns)\n",
    "\n",
    "# Standardize column names\n",
    "ic.columns = [\n",
    "    'customer', \n",
    "    'state', \n",
    "    'gender', \n",
    "    'education', \n",
    "    'customer_lifetime_value', \n",
    "    'income', \n",
    "    'monthly_premium_auto', \n",
    "    'number_of_open_complaints', \n",
    "    'policy_type', \n",
    "    'vehicle_class', \n",
    "    'total_claim_amount'\n",
    "]\n",
    "\n",
    "# Display the cleaned column names\n",
    "print(\"Cleaned Column Names:\", ic.columns)\n",
    "\n",
    "# Verify the cleaned dataframe\n",
    "ic.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cb501ec-36ff-4589-b872-6252bb150316",
   "metadata": {
    "id": "9cb501ec-36ff-4589-b872-6252bb150316"
   },
   "source": [
    "## Exercise 2: Cleaning invalid Values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "771fdcf3-8e20-4b06-9c24-3a93ba2b0909",
   "metadata": {
    "id": "771fdcf3-8e20-4b06-9c24-3a93ba2b0909"
   },
   "source": [
    "The dataset contains columns with inconsistent and incorrect values that could affect the accuracy of our analysis. Therefore, we need to clean these columns to ensure that they only contain valid data.\n",
    "\n",
    "Note that this exercise will focus only on cleaning inconsistent values and will not involve handling null values (NaN or None).\n",
    "\n",
    "*Hint*:\n",
    "- *Gender column contains various inconsistent values such as \"F\", \"M\", \"Femal\", \"Male\", \"female\", which need to be standardized, for example, to \"M\" and \"F\".*\n",
    "- *State abbreviations be can replaced with its full name, for example \"AZ\": \"Arizona\", \"Cali\": \"California\", \"WA\": \"Washington\"*\n",
    "- *In education, \"Bachelors\" could be replaced by \"Bachelor\"*\n",
    "- *In Customer Lifetime Value, delete the `%` character*\n",
    "- *In vehicle class, \"Sports Car\", \"Luxury SUV\" and \"Luxury Car\" could be replaced by \"Luxury\"*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "3f8ee5cb-50ab-48af-8a9f-9a389804033c",
   "metadata": {
    "id": "3f8ee5cb-50ab-48af-8a9f-9a389804033c"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Customer          ST GENDER             Education  Customer Lifetime Value  \\\n",
      "0  RB50392  Washington    NaN                Master                      NaN   \n",
      "1  QZ44356     Arizona      F              Bachelor                697953.59   \n",
      "2  AI49188      Nevada      F              Bachelor               1288743.17   \n",
      "3  WW63253  California      M              Bachelor                764586.18   \n",
      "4  GA49547  Washington      M  High School or Below                536307.65   \n",
      "\n",
      "    Income  Monthly Premium Auto Number of Open Complaints     Policy Type  \\\n",
      "0      0.0                1000.0                    1/0/00   Personal Auto   \n",
      "1      0.0                  94.0                    1/0/00   Personal Auto   \n",
      "2  48767.0                 108.0                    1/0/00   Personal Auto   \n",
      "3      0.0                 106.0                    1/0/00  Corporate Auto   \n",
      "4  36357.0                  68.0                    1/0/00   Personal Auto   \n",
      "\n",
      "   Vehicle Class  Total Claim Amount  \n",
      "0  Four-Door Car            2.704934  \n",
      "1  Four-Door Car         1131.464935  \n",
      "2   Two-Door Car          566.472247  \n",
      "3            SUV          529.881344  \n",
      "4  Four-Door Car           17.269323  \n"
     ]
    }
   ],
   "source": [
    "# Your code here\n",
    "# Map for replacing inconsistent state abbreviations with full names\n",
    "state_map = {\n",
    "    \"AZ\": \"Arizona\",\n",
    "    \"Cali\": \"California\",\n",
    "    \"WA\": \"Washington\"\n",
    "}\n",
    "\n",
    "# Map for replacing inconsistent gender values\n",
    "gender_map = {\n",
    "    \"F\": \"F\",\n",
    "    \"M\": \"M\",\n",
    "    \"Femal\": \"F\",\n",
    "    \"female\": \"F\",\n",
    "    \"Male\": \"M\",\n",
    "    \"male\": \"M\"\n",
    "}\n",
    "\n",
    "# to apply transformations\n",
    "url = 'https://raw.githubusercontent.com/data-bootcamp-v4/data/main/file1.csv'\n",
    "ic = pd.read_csv(url)\n",
    "\n",
    "# Standardize Gender column\n",
    "ic['GENDER'] = ic['GENDER'].replace(gender_map)\n",
    "\n",
    "# Replace state abbreviations with full names\n",
    "ic['ST'] = ic['ST'].replace(state_map)\n",
    "\n",
    "# Standardize Education column\n",
    "ic['Education'] = ic['Education'].replace({\n",
    "    \"Bachelors\": \"Bachelor\"\n",
    "})\n",
    "\n",
    "# Remove '%' from Customer Lifetime Value column and convert to numeric\n",
    "ic['Customer Lifetime Value'] = ic['Customer Lifetime Value'].str.replace('%', '').astype(float)\n",
    "\n",
    "# Standardize Vehicle Class column\n",
    "ic['Vehicle Class'] = ic['Vehicle Class'].replace({\n",
    "    \"Sports Car\": \"Luxury\",\n",
    "    \"Luxury SUV\": \"Luxury\",\n",
    "    \"Luxury Car\": \"Luxury\"\n",
    "})\n",
    "\n",
    "# Verify cleaned dataframe\n",
    "print(ic.head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85ff78ce-0174-4890-9db3-8048b7d7d2d0",
   "metadata": {
    "id": "85ff78ce-0174-4890-9db3-8048b7d7d2d0"
   },
   "source": [
    "## Exercise 3: Formatting data types"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b91c2cf8-79a2-4baf-9f65-ff2fb22270bd",
   "metadata": {
    "id": "b91c2cf8-79a2-4baf-9f65-ff2fb22270bd"
   },
   "source": [
    "The data types of many columns in the dataset appear to be incorrect. This could impact the accuracy of our analysis. To ensure accurate analysis, we need to correct the data types of these columns. Please update the data types of the columns as appropriate."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43e5d853-ff9e-43b2-9d92-aef2f78764f3",
   "metadata": {
    "id": "43e5d853-ff9e-43b2-9d92-aef2f78764f3"
   },
   "source": [
    "It is important to note that this exercise does not involve handling null values (NaN or None)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "329ca691-9196-4419-8969-3596746237a1",
   "metadata": {
    "id": "329ca691-9196-4419-8969-3596746237a1"
   },
   "source": [
    "*Hint*:\n",
    "- *Customer lifetime value should be numeric*\n",
    "- *Number of open complaints has an incorrect format. Look at the different values it takes with `unique()` and take the middle value. As an example, 1/5/00 should be 5. Number of open complaints is a string - remember you can use `split()` to deal with it and take the number you need. Finally, since it should be numeric, cast the column to be in its proper type.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "eb8f5991-73e9-405f-bf1c-6b7c589379a9",
   "metadata": {
    "id": "eb8f5991-73e9-405f-bf1c-6b7c589379a9"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Customer                      object\n",
      "ST                            object\n",
      "GENDER                        object\n",
      "Education                     object\n",
      "Customer Lifetime Value       object\n",
      "Income                       float64\n",
      "Monthly Premium Auto         float64\n",
      "Number of Open Complaints     object\n",
      "Policy Type                   object\n",
      "Vehicle Class                 object\n",
      "Total Claim Amount           float64\n",
      "dtype: object\n",
      "Customer                      object\n",
      "ST                            object\n",
      "GENDER                        object\n",
      "Education                     object\n",
      "Customer Lifetime Value      float64\n",
      "Income                       float64\n",
      "Monthly Premium Auto         float64\n",
      "Number of Open Complaints    float64\n",
      "Policy Type                   object\n",
      "Vehicle Class                 object\n",
      "Total Claim Amount           float64\n",
      "dtype: object\n"
     ]
    },
    {
     "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>Customer</th>\n",
       "      <th>ST</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>Education</th>\n",
       "      <th>Customer Lifetime Value</th>\n",
       "      <th>Income</th>\n",
       "      <th>Monthly Premium Auto</th>\n",
       "      <th>Number of Open Complaints</th>\n",
       "      <th>Policy Type</th>\n",
       "      <th>Vehicle Class</th>\n",
       "      <th>Total Claim Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RB50392</td>\n",
       "      <td>Washington</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Master</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</td>\n",
       "      <td>2.704934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>QZ44356</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>F</td>\n",
       "      <td>Bachelor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</td>\n",
       "      <td>1131.464935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AI49188</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>F</td>\n",
       "      <td>Bachelor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48767.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Two-Door Car</td>\n",
       "      <td>566.472247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WW63253</td>\n",
       "      <td>California</td>\n",
       "      <td>M</td>\n",
       "      <td>Bachelor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Corporate Auto</td>\n",
       "      <td>SUV</td>\n",
       "      <td>529.881344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GA49547</td>\n",
       "      <td>Washington</td>\n",
       "      <td>M</td>\n",
       "      <td>High School or Below</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36357.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</td>\n",
       "      <td>17.269323</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Customer          ST GENDER             Education  Customer Lifetime Value  \\\n",
       "0  RB50392  Washington    NaN                Master                      NaN   \n",
       "1  QZ44356     Arizona      F              Bachelor                      NaN   \n",
       "2  AI49188      Nevada      F              Bachelor                      NaN   \n",
       "3  WW63253  California      M              Bachelor                      NaN   \n",
       "4  GA49547  Washington      M  High School or Below                      NaN   \n",
       "\n",
       "    Income  Monthly Premium Auto  Number of Open Complaints     Policy Type  \\\n",
       "0      0.0                1000.0                        0.0   Personal Auto   \n",
       "1      0.0                  94.0                        0.0   Personal Auto   \n",
       "2  48767.0                 108.0                        0.0   Personal Auto   \n",
       "3      0.0                 106.0                        0.0  Corporate Auto   \n",
       "4  36357.0                  68.0                        0.0   Personal Auto   \n",
       "\n",
       "   Vehicle Class  Total Claim Amount  \n",
       "0  Four-Door Car            2.704934  \n",
       "1  Four-Door Car         1131.464935  \n",
       "2   Two-Door Car          566.472247  \n",
       "3            SUV          529.881344  \n",
       "4  Four-Door Car           17.269323  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# View the data types and check the structure of the data\n",
    "print(ic.dtypes)\n",
    "\n",
    "# 1. Customer Lifetime Value: Convert it to numeric (float or int)\n",
    "ic['Customer Lifetime Value'] = pd.to_numeric(ic['Customer Lifetime Value'], errors='coerce')\n",
    "\n",
    "# 2. Number of Open Complaints: Process the values and extract the numeric part\n",
    "ic['Number of Open Complaints'] = ic['Number of Open Complaints'].apply(lambda x: int(x.split('/')[1]) if isinstance(x, str) else x)\n",
    "\n",
    "# Check the data types again after conversion\n",
    "print(ic.dtypes)\n",
    "\n",
    "# Display the updated dataframe (optional)\n",
    "ic.head()\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14c52e28-2d0c-4dd2-8bd5-3476e34fadc1",
   "metadata": {
    "id": "14c52e28-2d0c-4dd2-8bd5-3476e34fadc1"
   },
   "source": [
    "## Exercise 4: Dealing with Null values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34b9a20f-7d32-4417-975e-1b4dfb0e16cd",
   "metadata": {
    "id": "34b9a20f-7d32-4417-975e-1b4dfb0e16cd"
   },
   "source": [
    "Identify any columns with null or missing values. Identify how many null values each column has. You can use the `isnull()` function in pandas to find columns with null values.\n",
    "\n",
    "Decide on a strategy for handling the null values. There are several options, including:\n",
    "\n",
    "- Drop the rows or columns with null values\n",
    "- Fill the null values with a specific value (such as the column mean or median for numerical variables, and mode for categorical variables)\n",
    "- Fill the null values with the previous or next value in the column\n",
    "- Fill the null values based on a more complex algorithm or model (note: we haven't covered this yet)\n",
    "\n",
    "Implement your chosen strategy to handle the null values. You can use the `fillna()` function in pandas to fill null values or `dropna()` function to drop null values.\n",
    "\n",
    "Verify that your strategy has successfully handled the null values. You can use the `isnull()` function again to check if there are still null values in the dataset.\n",
    "\n",
    "Remember to document your process and explain your reasoning for choosing a particular strategy for handling null values.\n",
    "\n",
    "After formatting data types, as a last step, convert all the numeric variables to integers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "f184fc35-7831-4836-a0a5-e7f99e01b40e",
   "metadata": {
    "id": "f184fc35-7831-4836-a0a5-e7f99e01b40e"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Null Values Per Column:\n",
      "Customer                     2937\n",
      "ST                           2937\n",
      "GENDER                       3054\n",
      "Education                    2937\n",
      "Customer Lifetime Value      2940\n",
      "Income                       2937\n",
      "Monthly Premium Auto         2937\n",
      "Number of Open Complaints    2937\n",
      "Policy Type                  2937\n",
      "Vehicle Class                2937\n",
      "Total Claim Amount           2937\n",
      "dtype: int64\n",
      "Null Values After Handling:\n",
      "Customer                     0\n",
      "ST                           0\n",
      "GENDER                       0\n",
      "Education                    0\n",
      "Customer Lifetime Value      0\n",
      "Income                       0\n",
      "Monthly Premium Auto         0\n",
      "Number of Open Complaints    0\n",
      "Policy Type                  0\n",
      "Vehicle Class                0\n",
      "Total Claim Amount           0\n",
      "dtype: int64\n",
      "Updated Data Types After Conversion:\n",
      "Customer                     object\n",
      "ST                           object\n",
      "GENDER                       object\n",
      "Education                    object\n",
      "Customer Lifetime Value       int32\n",
      "Income                        int32\n",
      "Monthly Premium Auto          int32\n",
      "Number of Open Complaints    object\n",
      "Policy Type                  object\n",
      "Vehicle Class                object\n",
      "Total Claim Amount            int32\n",
      "dtype: object\n",
      "Cleaned DataFrame:\n",
      "  Customer          ST GENDER             Education  Customer Lifetime Value  \\\n",
      "0  RB50392  Washington      F                Master                   588174   \n",
      "1  QZ44356     Arizona      F              Bachelor                   697953   \n",
      "2  AI49188      Nevada      F              Bachelor                  1288743   \n",
      "3  WW63253  California      M              Bachelor                   764586   \n",
      "4  GA49547  Washington      M  High School or Below                   536307   \n",
      "\n",
      "   Income  Monthly Premium Auto Number of Open Complaints     Policy Type  \\\n",
      "0       0                  1000                    1/0/00   Personal Auto   \n",
      "1       0                    94                    1/0/00   Personal Auto   \n",
      "2   48767                   108                    1/0/00   Personal Auto   \n",
      "3       0                   106                    1/0/00  Corporate Auto   \n",
      "4   36357                    68                    1/0/00   Personal Auto   \n",
      "\n",
      "   Vehicle Class  Total Claim Amount  \n",
      "0  Four-Door Car                   2  \n",
      "1  Four-Door Car                1131  \n",
      "2   Two-Door Car                 566  \n",
      "3            SUV                 529  \n",
      "4  Four-Door Car                  17  \n"
     ]
    }
   ],
   "source": [
    "# Your code here\n",
    "\n",
    "# Display the number of null values in each column\n",
    "print(\"Null Values Per Column:\")\n",
    "print(ic.isnull().sum())\n",
    "\n",
    "# Strategy for handling null values:\n",
    "# For numerical variables: Fill nulls with the median.\n",
    "# For categorical variables: Fill nulls with the mode.\n",
    "\n",
    "# Define a function to handle missing values\n",
    "def handle_missing_values(df):\n",
    "    for column in df.columns:\n",
    "        if df[column].dtype == 'object':  # Categorical variable\n",
    "            mode = df[column].mode()[0]  # Get the mode of the column\n",
    "            df[column] = df[column].fillna(mode)  # Fill with mode\n",
    "        else:  # Numerical variable\n",
    "            median = df[column].median()  # Get the median of the column\n",
    "            df[column] = df[column].fillna(median)  # Fill with median\n",
    "    return df\n",
    "\n",
    "# Apply the function to handle missing values\n",
    "ic = handle_missing_values(ic)\n",
    "\n",
    "# Verify that null values have been handled\n",
    "print(\"Null Values After Handling:\")\n",
    "print(ic.isnull().sum())\n",
    "\n",
    "# Convert all numeric variables to integers\n",
    "numeric_columns = ic.select_dtypes(include=['float64', 'int64']).columns\n",
    "ic[numeric_columns] = ic[numeric_columns].astype(int)\n",
    "\n",
    "# Verify data types\n",
    "print(\"Updated Data Types After Conversion:\")\n",
    "print(ic.dtypes)\n",
    "\n",
    "# Display the first few rows of the cleaned dataframe\n",
    "print(\"Cleaned DataFrame:\")\n",
    "print(ic.head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98416351-e999-4156-9834-9b00a311adfa",
   "metadata": {
    "id": "98416351-e999-4156-9834-9b00a311adfa"
   },
   "source": [
    "## Exercise 5: Dealing with duplicates"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea0816a7-a18e-4d4c-b667-a8452a800bd1",
   "metadata": {
    "id": "ea0816a7-a18e-4d4c-b667-a8452a800bd1"
   },
   "source": [
    "Use the `.duplicated()` method to identify any duplicate rows in the dataframe.\n",
    "\n",
    "Decide on a strategy for handling the duplicates. Options include:\n",
    "- Dropping all duplicate rows\n",
    "- Keeping only the first occurrence of each duplicated row\n",
    "- Keeping only the last occurrence of each duplicated row\n",
    "- Dropping duplicates based on a subset of columns\n",
    "- Dropping duplicates based on a specific column\n",
    "\n",
    "Implement your chosen strategy using the `drop_duplicates()` function.\n",
    "\n",
    "Verify that your strategy has successfully handled the duplicates by checking for duplicates again using `.duplicated()`.\n",
    "\n",
    "Remember to document your process and explain your reasoning for choosing a particular strategy for handling duplicates.\n",
    "\n",
    "Save the cleaned dataset to a new CSV file.\n",
    "\n",
    "*Hint*: *after dropping duplicates, reset the index to ensure consistency*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "1929362c-47ed-47cb-baca-358b78d401a0",
   "metadata": {
    "id": "1929362c-47ed-47cb-baca-358b78d401a0"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of duplicate rows: 2936\n",
      "Number of duplicate rows after cleaning: 0\n"
     ]
    }
   ],
   "source": [
    "# Your code here\n",
    "# Check for duplicate rows\n",
    "duplicates = ic.duplicated().sum()\n",
    "print(f\"Number of duplicate rows: {duplicates}\")\n",
    "\n",
    "# Handle duplicates by keeping only the first occurrence\n",
    "ic_cleaned = ic.drop_duplicates(keep='first')\n",
    "\n",
    "# Verify that duplicates have been handled\n",
    "duplicates_after = ic_cleaned.duplicated().sum()\n",
    "print(f\"Number of duplicate rows after cleaning: {duplicates_after}\")\n",
    "\n",
    "# Reset the index after dropping duplicates\n",
    "ic_cleaned = ic_cleaned.reset_index(drop=True)\n",
    "\n",
    "# Save the cleaned dataset to a new CSV file\n",
    "ic_cleaned.to_csv('cleaned_file.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60840701-4783-40e2-b4d8-55303f9100c9",
   "metadata": {
    "id": "60840701-4783-40e2-b4d8-55303f9100c9"
   },
   "source": [
    "# Bonus: Challenge 2: creating functions on a separate `py` file"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d1adb3a-17cf-4899-8041-da21a4337fb4",
   "metadata": {
    "id": "9d1adb3a-17cf-4899-8041-da21a4337fb4"
   },
   "source": [
    "Put all the data cleaning and formatting steps into functions, and create a main function that performs all the cleaning and formatting.\n",
    "\n",
    "Write these functions in separate .py file(s). By putting these steps into functions, we can make the code more modular and easier to maintain."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e170dc2-b62c-417a-8248-e63ed18a70c4",
   "metadata": {
    "id": "0e170dc2-b62c-417a-8248-e63ed18a70c4"
   },
   "source": [
    "*Hint: autoreload module is a utility module in Python that allows you to automatically reload modules in the current session when changes are made to the source code. This can be useful in situations where you are actively developing code and want to see the effects of changes you make without having to constantly restart the Python interpreter or Jupyter Notebook kernel.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "a52c6dfc-cd11-4d01-bda4-f719fa33e9a4",
   "metadata": {
    "id": "a52c6dfc-cd11-4d01-bda4-f719fa33e9a4"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of duplicate rows: 2936\n",
      "Duplicates removed.\n",
      "Index reset.\n",
      "Number of duplicate rows after cleaning: 0\n",
      "Cleaned data saved to 'cleaned_file.csv'.\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# data_cleaning.py\n",
    "def check_duplicates(df):\n",
    "    return df.duplicated().sum()\n",
    "\n",
    "def remove_duplicates(df, keep='first'):\n",
    "    return df.drop_duplicates(keep=keep)\n",
    "\n",
    "def reset_index(df):\n",
    "    return df.reset_index(drop=True)\n",
    "\n",
    "def save_data(df, filename):\n",
    "    df.to_csv(filename, index=False)\n",
    "\n",
    "# Check for duplicates\n",
    "duplicates = check_duplicates(ic)\n",
    "print(f\"Number of duplicate rows: {duplicates}\")\n",
    "\n",
    "# Remove duplicates\n",
    "ic_cleaned = remove_duplicates(ic)\n",
    "print(\"Duplicates removed.\")\n",
    "# Reset the index\n",
    "ic_cleaned = reset_index(ic_cleaned)\n",
    "print(\"Index reset.\")\n",
    "\n",
    "# Verify duplicates after cleaning\n",
    "duplicates_after = check_duplicates(ic_cleaned)\n",
    "print(f\"Number of duplicate rows after cleaning: {duplicates_after}\")\n",
    "\n",
    "# Save the cleaned data\n",
    "save_data(ic_cleaned, 'cleaned_file.csv')\n",
    "print(\"Cleaned data saved to 'cleaned_file.csv'.\")\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80f846bb-3f5e-4ca2-96c0-900728daca5a",
   "metadata": {
    "id": "80f846bb-3f5e-4ca2-96c0-900728daca5a",
    "tags": []
   },
   "source": [
    "# Bonus: Challenge 3: Analyzing Clean and Formated Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9021630e-cc90-446c-b5bd-264d6c864207",
   "metadata": {
    "id": "9021630e-cc90-446c-b5bd-264d6c864207"
   },
   "source": [
    "You have been tasked with analyzing the data to identify potential areas for improving customer retention and profitability. Your goal is to identify customers with a high policy claim amount and a low customer lifetime value.\n",
    "\n",
    "In the Pandas Lab, we only looked at high policy claim amounts because we couldn't look into low customer lifetime values. If we had tried to work with that column, we wouldn't have been able to because customer lifetime value wasn't clean and in its proper format. So after cleaning and formatting the data, let's get some more interesting insights!\n",
    "\n",
    "Instructions:\n",
    "\n",
    "- Review the statistics again for total claim amount and customer lifetime value to gain an understanding of the data.\n",
    "- To identify potential areas for improving customer retention and profitability, we want to focus on customers with a high policy claim amount and a low customer lifetime value. Consider customers with a high policy claim amount to be those in the top 25% of the total claim amount, and clients with a low customer lifetime value to be those in the bottom 25% of the customer lifetime value. Create a pandas DataFrame object that contains information about customers with a policy claim amount greater than the 75th percentile and a customer lifetime value in the bottom 25th percentile.\n",
    "- Use DataFrame methods to calculate summary statistics about the high policy claim amount and low customer lifetime value data. To do so, select both columns of the dataframe simultaneously and pass it to the `.describe()` method. This will give you descriptive statistics, such as mean, median, standard deviation, minimum and maximum values for both columns at the same time, allowing you to compare and analyze their characteristics."
   ]
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}