Lab_CA_2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = \"/home/kotarum/Documentos/Cosas/Labs/lab-customer-analysis-round-2/files_for_lab/csv_files/marketing_customer_analysis.csv\"\n",
    "df = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataframe shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The shape of the dataframe is: (10910, 26)\n"
     ]
    }
   ],
   "source": [
    "print(\"The shape of the dataframe is:\", df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['Unnamed: 0', 'Customer', 'State', 'Customer Lifetime Value',\n",
      "       'Response', 'Coverage', 'Education', 'Effective To Date',\n",
      "       'EmploymentStatus', 'Gender', 'Income', 'Location Code',\n",
      "       'Marital Status', 'Monthly Premium Auto', 'Months Since Last Claim',\n",
      "       'Months Since Policy Inception', 'Number of Open Complaints',\n",
      "       'Number of Policies', 'Policy Type', 'Policy', 'Renew Offer Type',\n",
      "       'Sales Channel', 'Total Claim Amount', 'Vehicle Class', 'Vehicle Size',\n",
      "       'Vehicle Type'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['unnamed: 0', 'customer', 'state', 'customer lifetime value',\n",
      "       'response', 'coverage', 'education', 'effective to date',\n",
      "       'employmentstatus', 'gender', 'income', 'location code',\n",
      "       'marital status', 'monthly premium auto', 'months since last claim',\n",
      "       'months since policy inception', 'number of open complaints',\n",
      "       'number of policies', 'policy type', 'policy', 'renew offer type',\n",
      "       'sales channel', 'total claim amount', 'vehicle class', 'vehicle size',\n",
      "       'vehicle type'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "df.columns = df.columns.str.lower()\n",
    "print(df.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Numerical and categorical columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "unnamed: 0\n",
      "Numerical\n",
      "\n",
      "customer\n",
      "Categorical\n",
      "\n",
      "state\n",
      "Categorical\n",
      "\n",
      "customer lifetime value\n",
      "Numerical\n",
      "\n",
      "response\n",
      "Categorical\n",
      "\n",
      "coverage\n",
      "Categorical\n",
      "\n",
      "education\n",
      "Categorical\n",
      "\n",
      "effective to date\n",
      "Categorical\n",
      "\n",
      "employmentstatus\n",
      "Categorical\n",
      "\n",
      "gender\n",
      "Categorical\n",
      "\n",
      "income\n",
      "Numerical\n",
      "\n",
      "location code\n",
      "Categorical\n",
      "\n",
      "marital status\n",
      "Categorical\n",
      "\n",
      "monthly premium auto\n",
      "Numerical\n",
      "\n",
      "months since last claim\n",
      "Numerical\n",
      "\n",
      "months since policy inception\n",
      "Numerical\n",
      "\n",
      "number of open complaints\n",
      "Numerical\n",
      "\n",
      "number of policies\n",
      "Numerical\n",
      "\n",
      "policy type\n",
      "Categorical\n",
      "\n",
      "policy\n",
      "Categorical\n",
      "\n",
      "renew offer type\n",
      "Categorical\n",
      "\n",
      "sales channel\n",
      "Categorical\n",
      "\n",
      "total claim amount\n",
      "Numerical\n",
      "\n",
      "vehicle class\n",
      "Categorical\n",
      "\n",
      "vehicle size\n",
      "Categorical\n",
      "\n",
      "vehicle type\n",
      "Categorical\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for col in df.columns:\n",
    "    if df[col].dtype == object:\n",
    "        print(col)\n",
    "        print(\"Categorical\")\n",
    "        print()\n",
    "    else:\n",
    "        print(col)\n",
    "        print(\"Numerical\")\n",
    "        print()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Work with the NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10910 entries, 0 to 10909\n",
      "Data columns (total 26 columns):\n",
      " #   Column                         Non-Null Count  Dtype  \n",
      "---  ------                         --------------  -----  \n",
      " 0   unnamed: 0                     10910 non-null  int64  \n",
      " 1   customer                       10910 non-null  object \n",
      " 2   state                          10279 non-null  object \n",
      " 3   customer lifetime value        10910 non-null  float64\n",
      " 4   response                       10279 non-null  object \n",
      " 5   coverage                       10910 non-null  object \n",
      " 6   education                      10910 non-null  object \n",
      " 7   effective to date              10910 non-null  object \n",
      " 8   employmentstatus               10910 non-null  object \n",
      " 9   gender                         10910 non-null  object \n",
      " 10  income                         10910 non-null  int64  \n",
      " 11  location code                  10910 non-null  object \n",
      " 12  marital status                 10910 non-null  object \n",
      " 13  monthly premium auto           10910 non-null  int64  \n",
      " 14  months since last claim        10277 non-null  float64\n",
      " 15  months since policy inception  10910 non-null  int64  \n",
      " 16  number of open complaints      10277 non-null  float64\n",
      " 17  number of policies             10910 non-null  int64  \n",
      " 18  policy type                    10910 non-null  object \n",
      " 19  policy                         10910 non-null  object \n",
      " 20  renew offer type               10910 non-null  object \n",
      " 21  sales channel                  10910 non-null  object \n",
      " 22  total claim amount             10910 non-null  float64\n",
      " 23  vehicle class                  10288 non-null  object \n",
      " 24  vehicle size                   10288 non-null  object \n",
      " 25  vehicle type                   5428 non-null   object \n",
      "dtypes: float64(4), int64(5), object(17)\n",
      "memory usage: 2.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "unnamed: 0                          0\n",
       "customer                            0\n",
       "state                             631\n",
       "customer lifetime value             0\n",
       "response                          631\n",
       "coverage                            0\n",
       "education                           0\n",
       "effective to date                   0\n",
       "employmentstatus                    0\n",
       "gender                              0\n",
       "income                              0\n",
       "location code                       0\n",
       "marital status                      0\n",
       "monthly premium auto                0\n",
       "months since last claim           633\n",
       "months since policy inception       0\n",
       "number of open complaints         633\n",
       "number of policies                  0\n",
       "policy type                         0\n",
       "policy                              0\n",
       "renew offer type                    0\n",
       "sales channel                       0\n",
       "total claim amount                  0\n",
       "vehicle class                     622\n",
       "vehicle size                      622\n",
       "vehicle type                     5482\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "unnamed: 0                          0\n",
       "customer                            0\n",
       "state                             631\n",
       "customer lifetime value             0\n",
       "response                          631\n",
       "coverage                            0\n",
       "education                           0\n",
       "effective to date                   0\n",
       "employmentstatus                    0\n",
       "gender                              0\n",
       "income                              0\n",
       "location code                       0\n",
       "marital status                      0\n",
       "monthly premium auto                0\n",
       "months since last claim             0\n",
       "months since policy inception       0\n",
       "number of open complaints           0\n",
       "number of policies                  0\n",
       "policy type                         0\n",
       "policy                              0\n",
       "renew offer type                    0\n",
       "sales channel                       0\n",
       "total claim amount                  0\n",
       "vehicle class                     622\n",
       "vehicle size                      622\n",
       "vehicle type                     5482\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col_median = df[\"months since last claim\"].dropna().median()\n",
    "df[\"months since last claim\"] = df[\"months since last claim\"].fillna(col_median)\n",
    "\n",
    "col_median2 = df[\"number of open complaints\"].dropna().median()\n",
    "df[\"number of open complaints\"] = df[\"number of open complaints\"].fillna(col_median2)\n",
    "\n",
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "unnamed: 0                       0\n",
       "customer                         0\n",
       "state                            0\n",
       "customer lifetime value          0\n",
       "response                         0\n",
       "coverage                         0\n",
       "education                        0\n",
       "effective to date                0\n",
       "employmentstatus                 0\n",
       "gender                           0\n",
       "income                           0\n",
       "location code                    0\n",
       "marital status                   0\n",
       "monthly premium auto             0\n",
       "months since last claim          0\n",
       "months since policy inception    0\n",
       "number of open complaints        0\n",
       "number of policies               0\n",
       "policy type                      0\n",
       "policy                           0\n",
       "renew offer type                 0\n",
       "sales channel                    0\n",
       "total claim amount               0\n",
       "vehicle class                    0\n",
       "vehicle size                     0\n",
       "vehicle type                     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.dropna(subset=[\"state\", \"response\", \"vehicle class\", \"vehicle size\", \"vehicle type\"])\n",
    "\n",
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract the months from the dataset and store in a separate column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    unnamed: 0 customer       state  customer lifetime value response  \\\n",
      "2            2  LZ68649  Washington             14947.917300       No   \n",
      "3            3  XL78013      Oregon             22332.439460      Yes   \n",
      "10          10  HG93801     Arizona              5154.764074       No   \n",
      "13          13  KR82385  California              5454.587929       No   \n",
      "16          16  FH51383  California              5326.677654       No   \n",
      "\n",
      "    coverage             education effective to date employmentstatus gender  \\\n",
      "2      Basic              Bachelor        2011-02-10         Employed      M   \n",
      "3   Extended               College        2011-01-11         Employed      M   \n",
      "10  Extended  High School or Below        2011-01-02         Employed      M   \n",
      "13     Basic                Master        2011-01-26         Employed      M   \n",
      "16     Basic  High School or Below        2011-02-07         Employed      F   \n",
      "\n",
      "    ...  number of policies     policy type        policy  renew offer type  \\\n",
      "2   ...                   2   Personal Auto   Personal L3            Offer3   \n",
      "3   ...                   2  Corporate Auto  Corporate L3            Offer2   \n",
      "10  ...                   1  Corporate Auto  Corporate L3            Offer2   \n",
      "13  ...                   4   Personal Auto   Personal L3            Offer4   \n",
      "16  ...                   6   Personal Auto   Personal L3            Offer4   \n",
      "\n",
      "    sales channel  total claim amount  vehicle class  vehicle size  \\\n",
      "2     Call Center          480.000000            SUV       Medsize   \n",
      "3          Branch          484.013411  Four-Door Car       Medsize   \n",
      "10         Branch          442.521087            SUV         Large   \n",
      "13    Call Center          331.200000   Two-Door Car       Medsize   \n",
      "16    Call Center          300.528579   Two-Door Car         Large   \n",
      "\n",
      "   vehicle type month  \n",
      "2             A     2  \n",
      "3             A     1  \n",
      "10            A     1  \n",
      "13            A     1  \n",
      "16            A     2  \n",
      "\n",
      "[5 rows x 27 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_2164866/1435503029.py:1: UserWarning: Could not infer format, so each element will be parsed individually, falling back to `dateutil`. To ensure parsing is consistent and as-expected, please specify a format.\n",
      "  df['effective to date'] = pd.to_datetime(df['effective to date'])\n"
     ]
    }
   ],
   "source": [
    "df['effective to date'] = pd.to_datetime(df['effective to date'])\n",
    "\n",
    "df['month'] = df['effective to date'].dt.month\n",
    "print(df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_quarter_data = df[df['month'].isin([1, 2, 3])]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ih_venv",
   "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}