{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "35b7f880",
   "metadata": {
    "scrolled": true
   },
   "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>Age</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Region</th>\n",
       "      <th>Marital_status</th>\n",
       "      <th>Number Of Dependants</th>\n",
       "      <th>BMI_Category</th>\n",
       "      <th>Smoking_Status</th>\n",
       "      <th>Employment_Status</th>\n",
       "      <th>Income_Level</th>\n",
       "      <th>Income_Lakhs</th>\n",
       "      <th>Medical History</th>\n",
       "      <th>Insurance_Plan</th>\n",
       "      <th>Annual_Premium_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>26</td>\n",
       "      <td>Male</td>\n",
       "      <td>Northwest</td>\n",
       "      <td>Unmarried</td>\n",
       "      <td>0</td>\n",
       "      <td>Normal</td>\n",
       "      <td>No Smoking</td>\n",
       "      <td>Salaried</td>\n",
       "      <td>&lt;10L</td>\n",
       "      <td>6</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Bronze</td>\n",
       "      <td>9053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29</td>\n",
       "      <td>Female</td>\n",
       "      <td>Southeast</td>\n",
       "      <td>Married</td>\n",
       "      <td>2</td>\n",
       "      <td>Obesity</td>\n",
       "      <td>Regular</td>\n",
       "      <td>Salaried</td>\n",
       "      <td>&lt;10L</td>\n",
       "      <td>6</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Bronze</td>\n",
       "      <td>16339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>49</td>\n",
       "      <td>Female</td>\n",
       "      <td>Northeast</td>\n",
       "      <td>Married</td>\n",
       "      <td>2</td>\n",
       "      <td>Normal</td>\n",
       "      <td>No Smoking</td>\n",
       "      <td>Self-Employed</td>\n",
       "      <td>10L - 25L</td>\n",
       "      <td>20</td>\n",
       "      <td>High blood pressure</td>\n",
       "      <td>Silver</td>\n",
       "      <td>18164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>30</td>\n",
       "      <td>Female</td>\n",
       "      <td>Southeast</td>\n",
       "      <td>Married</td>\n",
       "      <td>3</td>\n",
       "      <td>Normal</td>\n",
       "      <td>No Smoking</td>\n",
       "      <td>Salaried</td>\n",
       "      <td>&gt; 40L</td>\n",
       "      <td>77</td>\n",
       "      <td>No Disease</td>\n",
       "      <td>Gold</td>\n",
       "      <td>20303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18</td>\n",
       "      <td>Male</td>\n",
       "      <td>Northeast</td>\n",
       "      <td>Unmarried</td>\n",
       "      <td>0</td>\n",
       "      <td>Overweight</td>\n",
       "      <td>Regular</td>\n",
       "      <td>Self-Employed</td>\n",
       "      <td>&gt; 40L</td>\n",
       "      <td>99</td>\n",
       "      <td>High blood pressure</td>\n",
       "      <td>Silver</td>\n",
       "      <td>13365</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Age  Gender     Region Marital_status  Number Of Dependants BMI_Category  \\\n",
       "0   26    Male  Northwest      Unmarried                     0       Normal   \n",
       "1   29  Female  Southeast        Married                     2      Obesity   \n",
       "2   49  Female  Northeast        Married                     2       Normal   \n",
       "3   30  Female  Southeast        Married                     3       Normal   \n",
       "4   18    Male  Northeast      Unmarried                     0   Overweight   \n",
       "\n",
       "  Smoking_Status Employment_Status Income_Level  Income_Lakhs  \\\n",
       "0     No Smoking          Salaried         <10L             6   \n",
       "1        Regular          Salaried         <10L             6   \n",
       "2     No Smoking     Self-Employed    10L - 25L            20   \n",
       "3     No Smoking          Salaried        > 40L            77   \n",
       "4        Regular     Self-Employed        > 40L            99   \n",
       "\n",
       "       Medical History Insurance_Plan  Annual_Premium_Amount  \n",
       "0             Diabetes         Bronze                   9053  \n",
       "1             Diabetes         Bronze                  16339  \n",
       "2  High blood pressure         Silver                  18164  \n",
       "3           No Disease           Gold                  20303  \n",
       "4  High blood pressure         Silver                  13365  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(\"premiums.xlsx\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2b820232",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50000, 13)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "206a3cdb",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    50000.000000\n",
       "mean        34.593480\n",
       "std         15.000437\n",
       "min         18.000000\n",
       "25%         22.000000\n",
       "50%         31.000000\n",
       "75%         45.000000\n",
       "max        356.000000\n",
       "Name: Age, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Age.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "8b905a77",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_young = df[df.Age<=25]\n",
    "df_rest = df[df.Age>25]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f1d671ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((20096, 13), (29904, 13))"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_young.shape, df_rest.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f566ae1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_young.to_excel(\"premiums_young.xlsx\", index=False)\n",
    "df_rest.to_excel(\"premiums_rest.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebcc0f68",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "469c45f4",
   "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>Age</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Region</th>\n",
       "      <th>Marital_status</th>\n",
       "      <th>Number Of Dependants</th>\n",
       "      <th>BMI_Category</th>\n",
       "      <th>Smoking_Status</th>\n",
       "      <th>Employment_Status</th>\n",
       "      <th>Income_Level</th>\n",
       "      <th>Income_Lakhs</th>\n",
       "      <th>Medical History</th>\n",
       "      <th>Insurance_Plan</th>\n",
       "      <th>Annual_Premium_Amount</th>\n",
       "      <th>Genetical_Risk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>26</td>\n",
       "      <td>Male</td>\n",
       "      <td>Northwest</td>\n",
       "      <td>Unmarried</td>\n",
       "      <td>0</td>\n",
       "      <td>Normal</td>\n",
       "      <td>No Smoking</td>\n",
       "      <td>Salaried</td>\n",
       "      <td>&lt;10L</td>\n",
       "      <td>6</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Bronze</td>\n",
       "      <td>9053</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29</td>\n",
       "      <td>Female</td>\n",
       "      <td>Southeast</td>\n",
       "      <td>Married</td>\n",
       "      <td>2</td>\n",
       "      <td>Obesity</td>\n",
       "      <td>Regular</td>\n",
       "      <td>Salaried</td>\n",
       "      <td>&lt;10L</td>\n",
       "      <td>6</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Bronze</td>\n",
       "      <td>16339</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Age  Gender     Region Marital_status  Number Of Dependants BMI_Category  \\\n",
       "0   26    Male  Northwest      Unmarried                     0       Normal   \n",
       "1   29  Female  Southeast        Married                     2      Obesity   \n",
       "\n",
       "  Smoking_Status Employment_Status Income_Level  Income_Lakhs Medical History  \\\n",
       "0     No Smoking          Salaried         <10L             6        Diabetes   \n",
       "1        Regular          Salaried         <10L             6        Diabetes   \n",
       "\n",
       "  Insurance_Plan  Annual_Premium_Amount  Genetical_Risk  \n",
       "0         Bronze                   9053               5  \n",
       "1         Bronze                  16339               0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(\"premiums_with_gr.xlsx\")\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "48104400",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((20096, 14), (29904, 14))"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_young = df[df.Age<=25]\n",
    "df_rest = df[df.Age>25]\n",
    "\n",
    "df_young.shape, df_rest.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "18a2d9ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_young.to_excel(\"premiums_young_with_gr.xlsx\", index=False)\n",
    "df_rest.to_excel(\"premiums_rest_with_gr.xlsx\", index=False)"
   ]
  }
 ],
 "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}