lab-dw-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "25d7736c-ba17-4aff-b6bb-66eba20fbf4e",
   "metadata": {},
   "source": [
    "# Lab | Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1973e9e-8be6-4039-b70e-d73ee0d94c99",
   "metadata": {},
   "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",
    "\n",
    "The data includes information such as customer ID, state, gender, education, income, and other variables that can be used to perform various analyses.\n",
    "\n",
    "Throughout the lab, we will be using the pandas library in Python to manipulate and analyze the data. Pandas is a powerful library that provides various data manipulation and analysis tools, including the ability to load and manipulate data from a variety of sources, including CSV files."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8045146f-f4f7-44d9-8cd9-130d6400c73a",
   "metadata": {},
   "source": [
    "### Data Description\n",
    "\n",
    "- Customer - Customer ID\n",
    "\n",
    "- ST - State where customers live\n",
    "\n",
    "- Gender - Gender of the customer\n",
    "\n",
    "- Education - Background education of customers \n",
    "\n",
    "- Customer Lifetime Value - Customer lifetime value(CLV) is the total revenue the client will derive from their entire relationship with a customer. In other words, is the predicted or calculated value of a customer over their entire duration as a policyholder with the insurance company. It is an estimation of the net profit that the insurance company expects to generate from a customer throughout their relationship with the company. Customer Lifetime Value takes into account factors such as the duration of the customer's policy, premium payments, claim history, renewal likelihood, and potential additional services or products the customer may purchase. It helps insurers assess the long-term profitability and value associated with retaining a particular customer.\n",
    "\n",
    "- Income - Customers income\n",
    "\n",
    "- Monthly Premium Auto - Amount of money the customer pays on a monthly basis as a premium for their auto insurance coverage. It represents the recurring cost that the insured person must pay to maintain their insurance policy and receive coverage for potential damages, accidents, or other covered events related to their vehicle.\n",
    "\n",
    "- Number of Open Complaints - Number of complaints the customer opened\n",
    "\n",
    "- Policy Type - There are three type of policies in car insurance (Corporate Auto, Personal Auto, and Special Auto)\n",
    "\n",
    "- Vehicle Class - Type of vehicle classes that customers have Two-Door Car, Four-Door Car SUV, Luxury SUV, Sports Car, and Luxury Car\n",
    "\n",
    "- Total Claim Amount - the sum of all claims made by the customer. It represents the total monetary value of all approved claims for incidents such as accidents, theft, vandalism, or other covered events.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a72419b-20fc-4905-817a-8c83abc59de6",
   "metadata": {},
   "source": [
    "External Resources: https://towardsdatascience.com/filtering-data-frames-in-pandas-b570b1f834b9"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f8ece17-e919-4e23-96c0-c7c59778436a",
   "metadata": {},
   "source": [
    "## Challenge 1: Understanding the data\n",
    "\n",
    "In this challenge, you will use pandas to explore a given dataset. Your task is to gain a deep understanding of the data by analyzing its characteristics, dimensions, and statistical properties."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91437bd5-59a6-49c0-8150-ef0e6e6eb253",
   "metadata": {},
   "source": [
    "- Identify the dimensions of the dataset by determining the number of rows and columns it contains.\n",
    "- Determine the data types of each column and evaluate whether they are appropriate for the nature of the variable. You should also provide suggestions for fixing any incorrect data types.\n",
    "- Identify the number of unique values for each column and determine which columns appear to be categorical. You should also describe the unique values of each categorical column and the range of values for numerical columns, and give your insights.\n",
    "- Compute summary statistics such as mean, median, mode, standard deviation, and quartiles to understand the central tendency and distribution of the data for numerical columns. You should also provide your conclusions based on these summary statistics.\n",
    "- Compute summary statistics for categorical columns and providing your conclusions based on these statistics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "dd4e8cd8-a6f6-486c-a5c4-1745b0c035f4",
   "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>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",
       "  </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              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": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Your code here\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "file1 = pd.read_csv('https://raw.githubusercontent.com/data-bootcamp-v4/data/main/file1.csv')\n",
    "file1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "16419893",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4008, 11)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "fc997971",
   "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>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]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Determine the data types of each column and evaluate whether they are appropriate for the nature of the variable. \n",
    "#You should also provide suggestions for fixing any incorrect data types.\n",
    "file1.info\n",
    "file1col = file1.columns\n",
    "file1['Vehicle Class'].unique()\n",
    "\n",
    "display(file1)\n",
    "\n",
    "# Customer lifetime value should be float64\n",
    "# Number of Open Complaints value should be float64"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "83a99013",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ST column has 8  unique values\n",
      "GENDER column has 5  unique values\n",
      "Education column has 6  unique values\n",
      "Customer Lifetime Value column has 1027  unique values\n",
      "Income column has 774  unique values\n",
      "Monthly Premium Auto column has 132  unique values\n",
      "Number of Open Complaints column has 6  unique values\n",
      "Policy Type column has 3  unique values\n",
      "Vehicle Class column has 6  unique values\n",
      "Total Claim Amount column has 761  unique values\n"
     ]
    }
   ],
   "source": [
    "# Identify the number of unique values for each column and determine which columns appear to be categorical. \n",
    "# You should also describe the unique values of each categorical column and the range of values for numerical columns, and give your insights.\n",
    "\n",
    "#file1[file1col].value_counts()\n",
    "#file1['ST'].value_counts()\n",
    "\n",
    "file1['ST'].nunique()\n",
    "\n",
    "print(\"ST column has\", file1['ST'].nunique(),\" unique values\")\n",
    "print(\"GENDER column has\", file1['GENDER'].nunique(),\" unique values\")\n",
    "print(\"Education column has\", file1['Education'].nunique(),\" unique values\")\n",
    "print(\"Customer Lifetime Value column has\", file1['Customer Lifetime Value'].nunique(),\" unique values\")\n",
    "print(\"Income column has\", file1['Income'].nunique(),\" unique values\")\n",
    "print(\"Monthly Premium Auto column has\", file1['Monthly Premium Auto'].nunique(),\" unique values\")\n",
    "print(\"Number of Open Complaints column has\", file1['Number of Open Complaints'].nunique(),\" unique values\")\n",
    "print(\"Policy Type column has\", file1['Policy Type'].nunique(),\" unique values\")\n",
    "print(\"Vehicle Class column has\", file1['Vehicle Class'].nunique(),\" unique values\")\n",
    "print(\"Total Claim Amount column has\", file1['Total Claim Amount'].nunique(),\" unique values\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "16892337",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Customer column has  1071  unique values\n",
      "ST column has  8  unique values\n",
      "GENDER column has  5  unique values\n",
      "Education column has  6  unique values\n",
      "Customer Lifetime Value column has  1027  unique values\n",
      "Income column has  774  unique values\n",
      "Monthly Premium Auto column has  132  unique values\n",
      "Number of Open Complaints column has  6  unique values\n",
      "Policy Type column has  3  unique values\n",
      "Vehicle Class column has  6  unique values\n",
      "Total Claim Amount column has  761  unique values\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "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')"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "file1col = file1.columns\n",
    "for col in file1.columns:\n",
    "    print(col,\"column has \", file1[col].nunique(),\" unique values\")\n",
    "file1.head()\n",
    "\n",
    "cat = ['ST', 'GENDER', 'Education', 'Policy Type', 'Vehicle Class']\n",
    "num = [4,5,6,7,10] \n",
    "\n",
    "display(file1col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "bbaccde0",
   "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>Customer</th>\n",
       "      <th>ST</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>Education</th>\n",
       "      <th>Customer Lifetime Value</th>\n",
       "      <th>Number of Open Complaints</th>\n",
       "      <th>Policy Type</th>\n",
       "      <th>Vehicle Class</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>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</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>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</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>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Two-Door Car</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>1/0/00</td>\n",
       "      <td>Corporate Auto</td>\n",
       "      <td>SUV</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>1/0/00</td>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>Four-Door Car</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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4008 rows × 8 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 Number of Open Complaints     Policy Type  \\\n",
       "0                        NaN                    1/0/00   Personal Auto   \n",
       "1                 697953.59%                    1/0/00   Personal Auto   \n",
       "2                1288743.17%                    1/0/00   Personal Auto   \n",
       "3                 764586.18%                    1/0/00  Corporate Auto   \n",
       "4                 536307.65%                    1/0/00   Personal Auto   \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",
       "      Vehicle Class  \n",
       "0     Four-Door Car  \n",
       "1     Four-Door Car  \n",
       "2      Two-Door Car  \n",
       "3               SUV  \n",
       "4     Four-Door Car  \n",
       "...             ...  \n",
       "4003            NaN  \n",
       "4004            NaN  \n",
       "4005            NaN  \n",
       "4006            NaN  \n",
       "4007            NaN  \n",
       "\n",
       "[4008 rows x 8 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file1.select_dtypes('object')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "95598a79",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Customer\n",
       "RB50392    1\n",
       "HJ15383    1\n",
       "AO74776    1\n",
       "HQ82233    1\n",
       "OL72737    1\n",
       "          ..\n",
       "RO26085    1\n",
       "ES57969    1\n",
       "JK55587    1\n",
       "RN97635    1\n",
       "MY31220    1\n",
       "Name: count, Length: 1071, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "ST\n",
       "Oregon        320\n",
       "California    211\n",
       "Arizona       186\n",
       "Cali          120\n",
       "Nevada         98\n",
       "Washington     81\n",
       "WA             30\n",
       "AZ             25\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "GENDER\n",
       "F         457\n",
       "M         413\n",
       "Male       39\n",
       "female     28\n",
       "Femal      17\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Education\n",
       "Bachelor                324\n",
       "College                 313\n",
       "High School or Below    296\n",
       "Master                   94\n",
       "Doctor                   37\n",
       "Bachelors                 7\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Customer Lifetime Value\n",
       "445811.34%     4\n",
       "251459.20%     4\n",
       "272535.64%     3\n",
       "578018.22%     3\n",
       "684615.03%     3\n",
       "              ..\n",
       "245357.08%     1\n",
       "507566.27%     1\n",
       "321497.94%     1\n",
       "1227534.31%    1\n",
       "899704.02%     1\n",
       "Name: count, Length: 1027, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Number of Open Complaints\n",
       "1/0/00    830\n",
       "1/1/00    138\n",
       "1/2/00     50\n",
       "1/3/00     34\n",
       "1/4/00     13\n",
       "1/5/00      6\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Policy Type\n",
       "Personal Auto     780\n",
       "Corporate Auto    234\n",
       "Special Auto       57\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Vehicle Class\n",
       "Four-Door Car    576\n",
       "Two-Door Car     205\n",
       "SUV              199\n",
       "Sports Car        57\n",
       "Luxury SUV        20\n",
       "Luxury Car        14\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cat1 = file1.select_dtypes('object')\n",
    "cat1col = cat1.columns\n",
    "\n",
    "\n",
    "for col in cat1col:\n",
    "    display(file1[col].value_counts())\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b88031a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Customer, Customer LifetimeValue, Number of Complaints are not categorical\n",
    "#ST needs to be cleaned and Gender as well"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "a2948fb5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Income\n",
       "0.0        252\n",
       "43860.0      4\n",
       "17622.0      4\n",
       "36650.0      3\n",
       "14072.0      3\n",
       "          ... \n",
       "29486.0      1\n",
       "58557.0      1\n",
       "25632.0      1\n",
       "18768.0      1\n",
       "54230.0      1\n",
       "Name: count, Length: 774, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Monthly Premium Auto\n",
       "65.0     47\n",
       "63.0     39\n",
       "71.0     37\n",
       "67.0     37\n",
       "73.0     37\n",
       "         ..\n",
       "177.0     1\n",
       "181.0     1\n",
       "276.0     1\n",
       "253.0     1\n",
       "199.0     1\n",
       "Name: count, Length: 132, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Total Claim Amount\n",
       "321.600000    19\n",
       "331.200000    15\n",
       "312.000000    14\n",
       "350.400000    14\n",
       "316.800000    12\n",
       "              ..\n",
       "421.484456     1\n",
       "430.505942     1\n",
       "7.646763       1\n",
       "55.510526      1\n",
       "207.320041     1\n",
       "Name: count, Length: 761, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "num1 = file1.select_dtypes('number')\n",
    "num1col = num1.columns\n",
    "\n",
    "\n",
    "for col in num1col:\n",
    "    display(file1[col].value_counts())\n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a703890-63db-4944-b7ab-95a4f8185120",
   "metadata": {},
   "source": [
    "## Challenge 2: analyzing the data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0776a403-c56a-452f-ac33-5fd4fdb06fc7",
   "metadata": {},
   "source": [
    "### Exercise 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eedbc484-da4d-4f9c-9343-e1d44311a87e",
   "metadata": {},
   "source": [
    "The marketing team wants to know the top 5 less common customer locations. Create a pandas Series object that contains the customer locations and their frequencies, and then retrieve the top 5 less common locations in ascending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "59fdacb0",
   "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>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",
       "  </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              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": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "2dca5073-4520-4f42-9390-4b92733284ed",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ST        \n",
       "Oregon        320\n",
       "California    211\n",
       "Arizona       186\n",
       "Cali          120\n",
       "Nevada         98\n",
       "Washington     81\n",
       "WA             30\n",
       "AZ             25\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "ST        \n",
       "AZ             25\n",
       "WA             30\n",
       "Washington     81\n",
       "Nevada         98\n",
       "Cali          120\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Your code here\n",
    "file1[['ST']]\n",
    "lessloc = file1[['ST']].value_counts()\n",
    "display(lessloc)\n",
    "\n",
    "lessloc.tail(5).sort_values( )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ce80f43-4afa-43c7-a78a-c917444da4e0",
   "metadata": {},
   "source": [
    "### Exercise 2\n",
    "\n",
    "The sales team wants to know the total number of policies sold for each type of policy. Create a pandas Series object that contains the policy types and their total number of policies sold, and then retrieve the policy type with the highest number of policies sold."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9f13997-1555-4f98-aca6-970fda1d2c3f",
   "metadata": {},
   "source": [
    "*Hint:*\n",
    "- *Using value_counts() method simplifies this analysis.*\n",
    "- *Futhermore, there is a method that returns the index of the maximum value in a column or row.*\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "5afe498b",
   "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>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",
       "  </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              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": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "bcfad6c1-9af2-4b0b-9aa9-0dc5c17473c0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Policy Type  \n",
       "Personal Auto    780\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Your code here\n",
    "file1[['Policy Type']]\n",
    "policy = file1[['Policy Type']].value_counts()\n",
    "\n",
    "policy.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b863fd3-bf91-4d5d-86eb-be29ed9f5b70",
   "metadata": {},
   "source": [
    "### Exercise 3\n",
    "\n",
    "The sales team wants to know if customers with Personal Auto have a lower income than those with Corporate Auto. How does the average income compare between the two policy types?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1386d75-2810-4aa1-93e0-9485aa12d552",
   "metadata": {},
   "source": [
    "- Use *loc* to create two dataframes: one containing only Personal Auto policies and one containing only Corporate Auto policies.\n",
    "- Calculate the average income for each policy.\n",
    "- Print the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "8510976f",
   "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>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",
       "  </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              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": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "0c0563cf-6f8b-463d-a321-651a972f82e5",
   "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>Policy Type</th>\n",
       "      <th>Income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>48767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>36357.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>62902.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1065</th>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>69417.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1066</th>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>38644.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1067</th>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>63209.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1068</th>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>16061.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1070</th>\n",
       "      <td>Personal Auto</td>\n",
       "      <td>54230.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>780 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Policy Type   Income\n",
       "0     Personal Auto      0.0\n",
       "1     Personal Auto      0.0\n",
       "2     Personal Auto  48767.0\n",
       "4     Personal Auto  36357.0\n",
       "5     Personal Auto  62902.0\n",
       "...             ...      ...\n",
       "1065  Personal Auto  69417.0\n",
       "1066  Personal Auto  38644.0\n",
       "1067  Personal Auto  63209.0\n",
       "1068  Personal Auto  16061.0\n",
       "1070  Personal Auto  54230.0\n",
       "\n",
       "[780 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "El promedio Income para Personal es  38180.69871794872\n",
      "El promedio Income para Corporate es  41390.31196581197\n"
     ]
    }
   ],
   "source": [
    "# Your code here\n",
    "#file1[['Policy Type']]\n",
    "info = file1.loc[:,['Policy Type','Income']]\n",
    "personel = info[info['Policy Type'] == 'Personal Auto']\n",
    "corporate = info[info['Policy Type'] == 'Corporate Auto']\n",
    "#display(info)\n",
    "display(personel)\n",
    "#display(corporate)\n",
    "# Average Personal\n",
    "persavg = personel.iloc[:,1].mean()\n",
    "print(\"El promedio Income para Personal es \", persavg)\n",
    "# Average Corporate\n",
    "corpavg = corporate.iloc[:,1].mean()\n",
    "print(\"El promedio Income para Corporate es \", corpavg)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80b16c27-f4a5-4727-a229-1f88671cf4e2",
   "metadata": {},
   "source": [
    "### Bonus: Exercise 4\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac584986-299b-475f-ac2e-928c16c3f512",
   "metadata": {},
   "source": [
    "Your goal is to identify customers with a high policy claim amount.\n",
    "\n",
    "Instructions:\n",
    "\n",
    "- Review again the statistics for total claim amount 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. Consider customers with a high policy claim amount to be those in the top 25% of the total claim amount. Create a pandas DataFrame object that contains information about customers with a policy claim amount greater than the 75th percentile.\n",
    "- Use DataFrame methods to calculate summary statistics about the high policy claim amount data. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e3af5f1-6023-4b05-9c01-d05392daa650",
   "metadata": {},
   "source": [
    "*Note: When analyzing data, we often want to focus on certain groups of values to gain insights. Percentiles are a useful tool to help us define these groups. A percentile is a measure that tells us what percentage of values in a dataset are below a certain value. For example, the 75th percentile represents the value below which 75% of the data falls. Similarly, the 25th percentile represents the value below which 25% of the data falls. When we talk about the top 25%, we are referring to the values that fall above the 75th percentile, which represent the top quarter of the data. On the other hand, when we talk about the bottom 25%, we are referring to the values that fall below the 25th percentile, which represent the bottom quarter of the data. By focusing on these groups, we can identify patterns and trends that may be useful for making decisions and taking action.*\n",
    "\n",
    "*Hint: look for a method that gives you the percentile or quantile 0.75 and 0.25 for a Pandas Series.*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d234634-50bd-41e0-88f7-d5ba684455d1",
   "metadata": {},
   "source": [
    "*Hint 2: check `Boolean selection according to the values of a single column` in https://towardsdatascience.com/filtering-data-frames-in-pandas-b570b1f834b9*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b731bca6-a760-4860-a27b-a33efa712ce0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}