DataCleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleaning the Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Prep "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from scipy import stats\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import warnings\n",
    "warnings.simplefilter(\"ignore\")\n",
    "from IPython.display import Image\n",
    "from ipywidgets import interact, fixed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('Data/2017.csv')\n",
    "#df.to_parquet('2017.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import pyarrow.parquet as pq\n",
    "#table = pq.read_table(\"2017.parquet\")\n",
    "# Optionally convert to Pandas DataFrame\n",
    "#df = table.to_pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manipulate Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Translate column names into english\n",
    "data = df.rename(columns={\n",
    "    \"hashed_ID_Único\":\"UniqueID\",\n",
    "    \"Data\":\"Date\", \n",
    "    \"Unidade\":\"Facility\",\n",
    "    \"Grupo EFR\":\"Payer\",\n",
    "    \"Grupo Rúbrica\":\"SpecificService\", \n",
    "    \"Tipo Rúbrica\":\"CategoryofService\",\n",
    "    \"Sexo\":\"Sex\",\n",
    "    \"Data Nascimento\":\"BirthYear\",\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Date</th>\n",
       "      <th>Facility</th>\n",
       "      <th>Payer</th>\n",
       "      <th>SpecificService</th>\n",
       "      <th>CategoryofService</th>\n",
       "      <th>BirthYear</th>\n",
       "      <th>Sex</th>\n",
       "      <th>UniqueID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14/09/2017</td>\n",
       "      <td>HCIS</td>\n",
       "      <td>ADSE</td>\n",
       "      <td>SERVIÇOS ESPECIAIS CARDIOLOGIA</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21/08/2017</td>\n",
       "      <td>CCSJM</td>\n",
       "      <td>PARTICULARES</td>\n",
       "      <td>SERVIÇOS E TÉCNICAS GERAIS</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21/08/2017</td>\n",
       "      <td>CCSJM</td>\n",
       "      <td>PARTICULARES</td>\n",
       "      <td>SERVIÇOS E TÉCNICAS GERAIS</td>\n",
       "      <td>SERVIÇOS E TÉCNICAS GERAIS</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18/12/2017</td>\n",
       "      <td>CCTV</td>\n",
       "      <td>ADSE</td>\n",
       "      <td>SERVIÇOS ESPECIAIS UROLOGIA</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24/04/2017</td>\n",
       "      <td>CCSJM</td>\n",
       "      <td>PARTICULARES</td>\n",
       "      <td>SERVIÇOS ESPECIAIS CARDIOLOGIA</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date Facility         Payer                 SpecificService  \\\n",
       "0  14/09/2017     HCIS          ADSE  SERVIÇOS ESPECIAIS CARDIOLOGIA   \n",
       "1  21/08/2017    CCSJM  PARTICULARES      SERVIÇOS E TÉCNICAS GERAIS   \n",
       "2  21/08/2017    CCSJM  PARTICULARES      SERVIÇOS E TÉCNICAS GERAIS   \n",
       "3  18/12/2017     CCTV          ADSE     SERVIÇOS ESPECIAIS UROLOGIA   \n",
       "4  24/04/2017    CCSJM  PARTICULARES  SERVIÇOS ESPECIAIS CARDIOLOGIA   \n",
       "\n",
       "            CategoryofService  BirthYear       Sex  \\\n",
       "0            EXAMES ESPECIAIS     1980.0  Feminino   \n",
       "1            EXAMES ESPECIAIS     1980.0  Feminino   \n",
       "2  SERVIÇOS E TÉCNICAS GERAIS     1980.0  Feminino   \n",
       "3            EXAMES ESPECIAIS     1980.0  Feminino   \n",
       "4            EXAMES ESPECIAIS     1980.0  Feminino   \n",
       "\n",
       "                           UniqueID  \n",
       "0  91e9d2627cd4cc24958988333c4220a3  \n",
       "1  91e9d2627cd4cc24958988333c4220a3  \n",
       "2  91e9d2627cd4cc24958988333c4220a3  \n",
       "3  91e9d2627cd4cc24958988333c4220a3  \n",
       "4  91e9d2627cd4cc24958988333c4220a3  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "data[\"Date\"] = pd.to_datetime(data.Date, errors=\"coerce\") #converting to date time \n",
    "data[\"Age\"] = data[\"Date\"].dt.year - data[\"BirthYear\"] # Calculate Approximate Age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[\"Date\"]= data[\"Date\"].dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Date</th>\n",
       "      <th>Facility</th>\n",
       "      <th>Payer</th>\n",
       "      <th>SpecificService</th>\n",
       "      <th>CategoryofService</th>\n",
       "      <th>BirthYear</th>\n",
       "      <th>Sex</th>\n",
       "      <th>UniqueID</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>HCIS</td>\n",
       "      <td>ADSE</td>\n",
       "      <td>SERVIÇOS ESPECIAIS CARDIOLOGIA</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>CCSJM</td>\n",
       "      <td>PARTICULARES</td>\n",
       "      <td>SERVIÇOS E TÉCNICAS GERAIS</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>CCSJM</td>\n",
       "      <td>PARTICULARES</td>\n",
       "      <td>SERVIÇOS E TÉCNICAS GERAIS</td>\n",
       "      <td>SERVIÇOS E TÉCNICAS GERAIS</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>CCTV</td>\n",
       "      <td>ADSE</td>\n",
       "      <td>SERVIÇOS ESPECIAIS UROLOGIA</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>CCSJM</td>\n",
       "      <td>PARTICULARES</td>\n",
       "      <td>SERVIÇOS ESPECIAIS CARDIOLOGIA</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Date Facility         Payer                 SpecificService  \\\n",
       "0  2017     HCIS          ADSE  SERVIÇOS ESPECIAIS CARDIOLOGIA   \n",
       "1  2017    CCSJM  PARTICULARES      SERVIÇOS E TÉCNICAS GERAIS   \n",
       "2  2017    CCSJM  PARTICULARES      SERVIÇOS E TÉCNICAS GERAIS   \n",
       "3  2017     CCTV          ADSE     SERVIÇOS ESPECIAIS UROLOGIA   \n",
       "4  2017    CCSJM  PARTICULARES  SERVIÇOS ESPECIAIS CARDIOLOGIA   \n",
       "\n",
       "            CategoryofService  BirthYear       Sex  \\\n",
       "0            EXAMES ESPECIAIS     1980.0  Feminino   \n",
       "1            EXAMES ESPECIAIS     1980.0  Feminino   \n",
       "2  SERVIÇOS E TÉCNICAS GERAIS     1980.0  Feminino   \n",
       "3            EXAMES ESPECIAIS     1980.0  Feminino   \n",
       "4            EXAMES ESPECIAIS     1980.0  Feminino   \n",
       "\n",
       "                           UniqueID   Age  \n",
       "0  91e9d2627cd4cc24958988333c4220a3  37.0  \n",
       "1  91e9d2627cd4cc24958988333c4220a3  37.0  \n",
       "2  91e9d2627cd4cc24958988333c4220a3  37.0  \n",
       "3  91e9d2627cd4cc24958988333c4220a3  37.0  \n",
       "4  91e9d2627cd4cc24958988333c4220a3  37.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Delete Uneeded Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Date</th>\n",
       "      <th>Facility</th>\n",
       "      <th>Payer</th>\n",
       "      <th>SpecificService</th>\n",
       "      <th>CategoryofService</th>\n",
       "      <th>Sex</th>\n",
       "      <th>UniqueID</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>HCIS</td>\n",
       "      <td>ADSE</td>\n",
       "      <td>SERVIÇOS ESPECIAIS CARDIOLOGIA</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>CCSJM</td>\n",
       "      <td>PARTICULARES</td>\n",
       "      <td>SERVIÇOS E TÉCNICAS GERAIS</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Date Facility         Payer                 SpecificService  \\\n",
       "0  2017     HCIS          ADSE  SERVIÇOS ESPECIAIS CARDIOLOGIA   \n",
       "1  2017    CCSJM  PARTICULARES      SERVIÇOS E TÉCNICAS GERAIS   \n",
       "\n",
       "  CategoryofService       Sex                          UniqueID   Age  \n",
       "0  EXAMES ESPECIAIS  Feminino  91e9d2627cd4cc24958988333c4220a3  37.0  \n",
       "1  EXAMES ESPECIAIS  Feminino  91e9d2627cd4cc24958988333c4220a3  37.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del data[\"BirthYear\"]\n",
    "data.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data[data.Age>=0] #eliminate negative errors "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45.92886010773716"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.Age.median() #old median\n",
    "data.Age.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "46.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age_median = data.loc[data['Age']<110, 'Age'].median()\n",
    "age_median #median without zero and outliers of 117"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[data.Age > 110, 'Age'] = np.nan \n",
    "#fill the outliers with nan and replace with median later"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[data.Sex =='-', 'Sex'] = np.nan "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[data.SpecificService =='-', 'SpecificService'] = np.nan "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[data.CategoryofService =='-', 'CategoryofService'] = np.nan "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check for missing values "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date | 0.0 | int64\n",
      "Facility | 0.0 | object\n",
      "Payer | 8.735618746766296e-05 | object\n",
      "SpecificService | 0.005831025513466502 | object\n",
      "CategoryofService | 0.005831025513466502 | object\n",
      "Sex | 0.002798449042277504 | object\n",
      "UniqueID | 0.0 | object\n",
      "Age | 8.751676869462556e-05 | float64\n"
     ]
    }
   ],
   "source": [
    "n_records = len(data)\n",
    "def missing_values_df(df):\n",
    "    for column in df:\n",
    "        print(\"{} | {} | {}\".format(\n",
    "            column, len(df[df[column].isnull()]) / (1.0*n_records), df[column].dtype\n",
    "        ))\n",
    "\n",
    "missing_values_df(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Date</th>\n",
       "      <th>Facility</th>\n",
       "      <th>Payer</th>\n",
       "      <th>SpecificService</th>\n",
       "      <th>CategoryofService</th>\n",
       "      <th>Sex</th>\n",
       "      <th>UniqueID</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2017</td>\n",
       "      <td>HCP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BLOCO CIRURGIA AMBULATÓRIO</td>\n",
       "      <td>BLOCOS</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2017</td>\n",
       "      <td>HCP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BLOCO DE PARTOS</td>\n",
       "      <td>BLOCOS</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Date Facility Payer             SpecificService CategoryofService  \\\n",
       "19  2017      HCP   NaN  BLOCO CIRURGIA AMBULATÓRIO            BLOCOS   \n",
       "20  2017      HCP   NaN             BLOCO DE PARTOS            BLOCOS   \n",
       "\n",
       "         Sex                          UniqueID   Age  \n",
       "19  Feminino  91e9d2627cd4cc24958988333c4220a3  37.0  \n",
       "20  Feminino  91e9d2627cd4cc24958988333c4220a3  37.0  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_data = data[data.isnull().any(axis=1)] #looking to see if the missing values where consistently missing for a single cutsomer\n",
    "null_data.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.Payer = data.Payer.fillna(\"Missing\")\n",
    "data.SpecificService = data.SpecificService.fillna(\"Missing\")\n",
    "data.CategoryofService = data.CategoryofService.fillna(\"Missing\")\n",
    "data.Sex = data.Sex.fillna(\"Missing\")\n",
    "data.Age = data.Age.fillna(age_median)\n",
    "\n",
    "#replace missing strings with the string \"Missing\" \n",
    "#missing age/anything above 110 was replaced with the median of values under 110"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date | 0.0 | int64\n",
      "Facility | 0.0 | object\n",
      "Payer | 0.0 | object\n",
      "SpecificService | 0.0 | object\n",
      "CategoryofService | 0.0 | object\n",
      "Sex | 0.0 | object\n",
      "UniqueID | 0.0 | object\n",
      "Age | 0.0 | float64\n"
     ]
    }
   ],
   "source": [
    "missing_values_df(data) #no more missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data[data.UniqueID != '6bb61e3b7bce0931da574d19d1d82c88'] \n",
    "#deleting a virtual customer from the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x10dc50748>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXoAAAD8CAYAAAB5Pm/hAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAADMdJREFUeJzt3X+o3fV9x/Hnq0ap1bZaf1yyRLwOQquzlNmL2ArjqGPUOma26lDGFrqM+0/XdetgZvtHxhhEGGu7sY1d1C2F0mpth2IkrWQ5G/1j2UxrV21wBms1M/4oVde4gpW+98f9OlK9aXLP93zvTT73+YCQe773e87nHTh53i+fe8+5qSokSe16y2oPIEkalqGXpMYZeklqnKGXpMYZeklqnKGXpMYZeklqnKGXpMYZeklq3LrVHgDg3HPPrdnZ2dUeQ3qTV155hTPOOGO1x5CWtG/fvu9V1XnHOu+ECP3s7CwPPfTQao8hvcl4PGY0Gq32GNKSknz3eM5z60aSGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGmfoJalxJ8QLpqSVkmRF1vF3MetE4hW91pSqWtafC2+5f9n3MfI60Rh6SWqcoZekxhl6SWqcoZekxhl6SWqcoZekxhl6SWqcoZekxh0z9EnuTPJ8kkeOOPauJA8mebz7++zueJL8VZIDSf4zyWVDDi9JOrbjuaL/R+BDbzi2DdhdVZuA3d1tgGuBTd2feeDvpjOmJGlSxwx9Vf0r8P03HL4e2NF9vAPYfMTxz9aifwPOSrJ+WsNKkpZv0jc1m6mqQwBVdSjJ+d3xDcDTR5x3sDt26I0PkGSexat+ZmZmGI/HE44iDcvnpk520373yqXeGnDJd3iqqgVgAWBubq5Go9GUR5GmYNdOfG7qZDfpT9089/qWTPf3893xg8AFR5y3EXhm8vEkSX1NGvr7gC3dx1uAe484/lvdT99cAbz8+haPJGl1HHPrJsnngRFwbpKDwK3AduDuJFuBp4Abu9MfAD4MHAD+F/joADNLkpbhmKGvqpuP8qlrlji3gI/1HUqSND2+MlaSGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGmfoJalxhl6SGtcr9En+IMmjSR5J8vkkb01yUZK9SR5PcleS06Y1rCRp+SYOfZINwO8Bc1V1KXAKcBNwG/CpqtoEvAhsncagkqTJ9N26WQecnmQd8DbgEHA1cE/3+R3A5p5rSJJ6mDj0VfXfwF8AT7EY+JeBfcBLVfVad9pBYEPfISVJk1s36R2TnA1cD1wEvAR8Ebh2iVPrKPefB+YBZmZmGI/Hk44iDcrnpk52E4ce+EXgO1X1AkCSLwMfBM5Ksq67qt8IPLPUnatqAVgAmJubq9Fo1GMUaSC7duJzUye7Pnv0TwFXJHlbkgDXAN8G9gA3dOdsAe7tN6IkqY8+e/R7Wfym69eBb3WPtQDcAnwyyQHgHOCOKcwpSZpQn60bqupW4NY3HH4CuLzP40qSpsdXxkpS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS49at9gDSpN73p1/l5R/+aPB1ZrftHHyNd55+Kt+89ZcGX0drU6/QJzkLuB24FCjgt4HHgLuAWeBJ4Ner6sVeU0pLePmHP+LJ7dcNusZ4PGY0Gg26BqzMFxOtXX23bj4D7Kqq9wDvA/YD24DdVbUJ2N3dliStkolDn+QdwC8AdwBU1atV9RJwPbCjO20HsLnvkJKkyfW5ov9Z4AXgH5J8I8ntSc4AZqrqEED39/lTmFOSNKE+e/TrgMuAj1fV3iSfYRnbNEnmgXmAmZkZxuNxj1G0Vg39vDl8+PCKPTf9P6Ch9An9QeBgVe3tbt/DYuifS7K+qg4lWQ88v9Sdq2oBWACYm5urlfiGlxqza+fg3yhdqW/GrsS/RWvXxFs3VfUs8HSSd3eHrgG+DdwHbOmObQHu7TWhJKmXvj9H/3Hgc0lOA54APsriF4+7k2wFngJu7LmGJKmHXqGvqoeBuSU+dU2fx5UkTY9vgSBJjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjVu32gNIk3r7xdt4745twy+0Y/gl3n4xwHXDL6Q1ydDrpPWD/dt5cvuwcRyPx4xGo0HXAJjdtnPwNbR2uXUjSY0z9JLUOEMvSY0z9JLUOEMvSY3rHfokpyT5RpL7u9sXJdmb5PEkdyU5rf+YkqRJTeOK/hPA/iNu3wZ8qqo2AS8CW6ewhiRpQr1Cn2Qji6/yuL27HeBq4J7ulB3A5j5rSJL66XtF/2ngj4Afd7fPAV6qqte62weBDT3XkCT1MPErY5P8MvB8Ve1LMnr98BKn1lHuPw/MA8zMzDAejycdRWvY0M+bw4cPr9hz0/8DGkqft0C4EviVJB8G3gq8g8Ur/LOSrOuu6jcCzyx156paABYA5ubmaiVeZq7G7No5+NsTrNRbIKzEv0Vr18RbN1X1x1W1sapmgZuAf66q3wD2ADd0p20B7u09pSRpYkP8HP0twCeTHGBxz/6OAdaQJB2nqbx7ZVWNgXH38RPA5dN4XElSf74yVpIaZ+glqXGGXpIaZ+glqXGGXpIaZ+glqXGGXpIaZ+glqXGGXpIaZ+glqXFTeQsEabXMbts5/CK7hl/jnaefOvgaWrsMvU5aT26/bvA1ZrftXJF1pCG5dSNJjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktQ4Qy9JjTP0ktS4iUOf5IIke5LsT/Jokk90x9+V5MEkj3d/nz29cSVJy9Xniv414A+r6mLgCuBjSS4BtgG7q2oTsLu7LUlaJROHvqoOVdXXu49/AOwHNgDXAzu603YAm/sOKUma3FT26JPMAj8P7AVmquoQLH4xAM6fxhqSpMn0/uXgSc4EvgT8flX9T5Ljvd88MA8wMzPDeDzuO4o0CJ+bOtn1Cn2SU1mM/Oeq6svd4eeSrK+qQ0nWA88vdd+qWgAWAObm5mo0GvUZRRrGrp343NTJrs9P3QS4A9hfVX95xKfuA7Z0H28B7p18PElSX32u6K8EfhP4VpKHu2N/AmwH7k6yFXgKuLHfiJKkPiYOfVV9DTjahvw1kz6uJGm6fGWsJDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wy9JDXO0EtS4wYJfZIPJXksyYEk24ZYQ5J0fKYe+iSnAH8DXAtcAtyc5JJpryNJOj5DXNFfDhyoqieq6lXgC8D1A6wjSToOQ4R+A/D0EbcPdsckSatg3QCPmSWO1ZtOSuaBeYCZmRnG4/EAo0g/6aqrrlr2fXLb8tfZs2fP8u8kDWSI0B8ELjji9kbgmTeeVFULwALA3NxcjUajAUaRflLVm645fqrxeIzPTZ3shti6+Q9gU5KLkpwG3ATcN8A6kqTjMPUr+qp6LcnvAl8BTgHurKpHp72OJOn4DLF1Q1U9ADwwxGNLkpbHV8ZKUuMMvSQ1ztBLUuMMvSQ1ztBLUuOy3BeQDDJE8gLw3dWeQ1rCucD3VnsI6SgurKrzjnXSCRF66USV5KGqmlvtOaQ+3LqRpMYZeklqnKGXfrqF1R5A6ss9eklqnFf0ktQ4Q681L8mvJqkk71ntWaQhGHoJbga+xuLvTpCaY+i1piU5E7gS2EoX+iRvSfK3SR5Ncn+SB5Lc0H3u/Un+Jcm+JF9Jsn4Vx5eOi6HXWrcZ2FVV/wV8P8llwK8Bs8B7gd8BPgCQ5FTgr4Ebqur9wJ3An6/G0NJyDPKLR6STyM3Ap7uPv9DdPhX4YlX9GHg2yeu/6fvdwKXAg0lg8TeoHVrZcaXlM/Ras5KcA1wNXJqkWAx3Af90tLsAj1bVB1ZoRGkq3LrRWnYD8NmqurCqZqvqAuA7LL6J2Ue6vfoZYNSd/xhwXpL/38pJ8nOrMbi0HIZea9nNvPnq/UvAzwAHgUeAvwf2Ai9X1assfnG4Lck3gYeBD67cuNJkfGWstIQkZ1bV4W5759+BK6vq2dWeS5qEe/TS0u5PchZwGvBnRl4nM6/oJalx7tFLUuMMvSQ1ztBLUuMMvSQ1ztBLUuMMvSQ17v8AzOrmdjLznigAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "data.boxplot(column='Age')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    6.227378e+06\n",
       "mean     4.592265e+01\n",
       "std      2.212655e+01\n",
       "min      0.000000e+00\n",
       "25%      3.200000e+01\n",
       "50%      4.600000e+01\n",
       "75%      6.300000e+01\n",
       "max      1.070000e+02\n",
       "Name: Age, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.Age.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Variable Grouping by Age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Age_Group']= 'hi'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date                   int64\n",
       "Facility              object\n",
       "Payer                 object\n",
       "SpecificService       object\n",
       "CategoryofService     object\n",
       "Sex                   object\n",
       "UniqueID              object\n",
       "Age                  float64\n",
       "Age_Group             object\n",
       "dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Age_Group'][(data[\"Age\"]<18) & (data[\"Age\"]>=5)] = \"Child\"\n",
    "data['Age_Group'][(data[\"Age\"]>=18) & (data[\"Age\"]<44)] = \"YoungAdult\"\n",
    "data['Age_Group'][(data[\"Age\"]>=44) & (data[\"Age\"]<64)] = \"Adult\"\n",
    "data['Age_Group'][(data[\"Age\"]>=64) & (data[\"Age\"]<81)] = \"Senior\"\n",
    "data['Age_Group'][data[\"Age\"]>=81] = \"Elderly\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Age_Group'] = data['Age_Group'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date                    int64\n",
       "Facility               object\n",
       "Payer                  object\n",
       "SpecificService        object\n",
       "CategoryofService      object\n",
       "Sex                    object\n",
       "UniqueID               object\n",
       "Age                   float64\n",
       "Age_Group            category\n",
       "dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Date</th>\n",
       "      <th>Facility</th>\n",
       "      <th>Payer</th>\n",
       "      <th>SpecificService</th>\n",
       "      <th>CategoryofService</th>\n",
       "      <th>Sex</th>\n",
       "      <th>UniqueID</th>\n",
       "      <th>Age</th>\n",
       "      <th>Age_Group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>HCIS</td>\n",
       "      <td>ADSE</td>\n",
       "      <td>SERVIÇOS ESPECIAIS CARDIOLOGIA</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "      <td>YoungAdult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>CCSJM</td>\n",
       "      <td>PARTICULARES</td>\n",
       "      <td>SERVIÇOS E TÉCNICAS GERAIS</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "      <td>YoungAdult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>CCSJM</td>\n",
       "      <td>PARTICULARES</td>\n",
       "      <td>SERVIÇOS E TÉCNICAS GERAIS</td>\n",
       "      <td>SERVIÇOS E TÉCNICAS GERAIS</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "      <td>YoungAdult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>CCTV</td>\n",
       "      <td>ADSE</td>\n",
       "      <td>SERVIÇOS ESPECIAIS UROLOGIA</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "      <td>YoungAdult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>CCSJM</td>\n",
       "      <td>PARTICULARES</td>\n",
       "      <td>SERVIÇOS ESPECIAIS CARDIOLOGIA</td>\n",
       "      <td>EXAMES ESPECIAIS</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>91e9d2627cd4cc24958988333c4220a3</td>\n",
       "      <td>37.0</td>\n",
       "      <td>YoungAdult</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Date Facility         Payer                 SpecificService  \\\n",
       "0  2017     HCIS          ADSE  SERVIÇOS ESPECIAIS CARDIOLOGIA   \n",
       "1  2017    CCSJM  PARTICULARES      SERVIÇOS E TÉCNICAS GERAIS   \n",
       "2  2017    CCSJM  PARTICULARES      SERVIÇOS E TÉCNICAS GERAIS   \n",
       "3  2017     CCTV          ADSE     SERVIÇOS ESPECIAIS UROLOGIA   \n",
       "4  2017    CCSJM  PARTICULARES  SERVIÇOS ESPECIAIS CARDIOLOGIA   \n",
       "\n",
       "            CategoryofService       Sex                          UniqueID  \\\n",
       "0            EXAMES ESPECIAIS  Feminino  91e9d2627cd4cc24958988333c4220a3   \n",
       "1            EXAMES ESPECIAIS  Feminino  91e9d2627cd4cc24958988333c4220a3   \n",
       "2  SERVIÇOS E TÉCNICAS GERAIS  Feminino  91e9d2627cd4cc24958988333c4220a3   \n",
       "3            EXAMES ESPECIAIS  Feminino  91e9d2627cd4cc24958988333c4220a3   \n",
       "4            EXAMES ESPECIAIS  Feminino  91e9d2627cd4cc24958988333c4220a3   \n",
       "\n",
       "    Age   Age_Group  \n",
       "0  37.0  YoungAdult  \n",
       "1  37.0  YoungAdult  \n",
       "2  37.0  YoungAdult  \n",
       "3  37.0  YoungAdult  \n",
       "4  37.0  YoungAdult  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.Age_Group.unique()\n",
    "data['Age_Group'] = data['Age_Group'].replace({'hi':'Missing'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['YoungAdult', 'Senior', 'Adult', 'Elderly', 'Child', 'Missing'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.Age_Group.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isnull().values.any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a customer dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_visits = data.UniqueID.value_counts(sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "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>UniqueID</th>\n",
       "      <th>FrequencyofVisits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5962ec84553dcb84e508e2fc45789278</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>547c0d52bbfe1b93b9411d7b55f3bd82</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1770ad573874abe83cd04bfaf89c8eb6</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11b9573f2fc9dd49594cd1f301cb7be6</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>84fdd6d40ab2a9b95f3c957237225d33</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           UniqueID  FrequencyofVisits\n",
       "0  5962ec84553dcb84e508e2fc45789278                 12\n",
       "1  547c0d52bbfe1b93b9411d7b55f3bd82                  3\n",
       "2  1770ad573874abe83cd04bfaf89c8eb6                  2\n",
       "3  11b9573f2fc9dd49594cd1f301cb7be6                 16\n",
       "4  84fdd6d40ab2a9b95f3c957237225d33                  1"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers = patient_visits.rename_axis('UniqueID').reset_index(name='FrequencyofVisits')\n",
    "customers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.isnull().values.any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "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>UniqueID</th>\n",
       "      <th>FrequencyofVisits</th>\n",
       "      <th>Age</th>\n",
       "      <th>Sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5962ec84553dcb84e508e2fc45789278</td>\n",
       "      <td>12</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>547c0d52bbfe1b93b9411d7b55f3bd82</td>\n",
       "      <td>3</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1770ad573874abe83cd04bfaf89c8eb6</td>\n",
       "      <td>2</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11b9573f2fc9dd49594cd1f301cb7be6</td>\n",
       "      <td>16</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>84fdd6d40ab2a9b95f3c957237225d33</td>\n",
       "      <td>1</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           UniqueID  FrequencyofVisits   Age       Sex\n",
       "0  5962ec84553dcb84e508e2fc45789278                 12  37.0  Feminino\n",
       "1  547c0d52bbfe1b93b9411d7b55f3bd82                  3  37.0  Feminino\n",
       "2  1770ad573874abe83cd04bfaf89c8eb6                  2  37.0  Feminino\n",
       "3  11b9573f2fc9dd49594cd1f301cb7be6                 16  37.0  Feminino\n",
       "4  84fdd6d40ab2a9b95f3c957237225d33                  1  37.0  Feminino"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers[[\"Age\",\"Sex\"]]= data[[\"Age\",\"Sex\"]]\n",
    "customers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "age_median = customers.loc[customers['Age']<110, 'Age'].median()\n",
    "customers.Age = customers.Age.fillna(age_median)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers.Sex = customers.Sex.fillna(\"Missing\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "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>UniqueID</th>\n",
       "      <th>FrequencyofVisits</th>\n",
       "      <th>Age</th>\n",
       "      <th>Sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [UniqueID, FrequencyofVisits, Age, Sex]\n",
       "Index: []"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_customers = customers[customers.isnull().any(axis=1)] #looking to see if the missing values where consistently missing for a single cutsomer\n",
    "null_customers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "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>UniqueID</th>\n",
       "      <th>FrequencyofVisits</th>\n",
       "      <th>Age</th>\n",
       "      <th>Sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5962ec84553dcb84e508e2fc45789278</td>\n",
       "      <td>12</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>547c0d52bbfe1b93b9411d7b55f3bd82</td>\n",
       "      <td>3</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1770ad573874abe83cd04bfaf89c8eb6</td>\n",
       "      <td>2</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11b9573f2fc9dd49594cd1f301cb7be6</td>\n",
       "      <td>16</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>84fdd6d40ab2a9b95f3c957237225d33</td>\n",
       "      <td>1</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           UniqueID  FrequencyofVisits   Age       Sex\n",
       "0  5962ec84553dcb84e508e2fc45789278                 12  37.0  Feminino\n",
       "1  547c0d52bbfe1b93b9411d7b55f3bd82                  3  37.0  Feminino\n",
       "2  1770ad573874abe83cd04bfaf89c8eb6                  2  37.0  Feminino\n",
       "3  11b9573f2fc9dd49594cd1f301cb7be6                 16  37.0  Feminino\n",
       "4  84fdd6d40ab2a9b95f3c957237225d33                  1  37.0  Feminino"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "modes = data.groupby(\"UniqueID\")[\"Payer\"].agg(pd.Series.mode).to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "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>Payer</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UniqueID</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>00000f7264c27ba6fea0c837ed6aa0aa</th>\n",
       "      <td>MÉDIS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00001e984eba85527fd3122056451279</th>\n",
       "      <td>ALLIANZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000028899fe7782862d40bb1b87807ee</th>\n",
       "      <td>ALLIANZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00003e3bc9cdb8bfcc589d9f5ec13251</th>\n",
       "      <td>FUTURE HEALTHCARE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000057d42f71fc08c6ebfc62ada8ebfc</th>\n",
       "      <td>MÉDIS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Payer\n",
       "UniqueID                                           \n",
       "00000f7264c27ba6fea0c837ed6aa0aa              MÉDIS\n",
       "00001e984eba85527fd3122056451279            ALLIANZ\n",
       "000028899fe7782862d40bb1b87807ee            ALLIANZ\n",
       "00003e3bc9cdb8bfcc589d9f5ec13251  FUTURE HEALTHCARE\n",
       "000057d42f71fc08c6ebfc62ada8ebfc              MÉDIS"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "modes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "modes[\"Facility\"]= data.groupby(\"UniqueID\")[\"Facility\"].agg(pd.Series.mode).to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "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>Payer</th>\n",
       "      <th>Facility</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UniqueID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>00000f7264c27ba6fea0c837ed6aa0aa</th>\n",
       "      <td>MÉDIS</td>\n",
       "      <td>HCP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00001e984eba85527fd3122056451279</th>\n",
       "      <td>ALLIANZ</td>\n",
       "      <td>HCIS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000028899fe7782862d40bb1b87807ee</th>\n",
       "      <td>ALLIANZ</td>\n",
       "      <td>HCD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00003e3bc9cdb8bfcc589d9f5ec13251</th>\n",
       "      <td>FUTURE HEALTHCARE</td>\n",
       "      <td>HCD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000057d42f71fc08c6ebfc62ada8ebfc</th>\n",
       "      <td>MÉDIS</td>\n",
       "      <td>HCIS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Payer Facility\n",
       "UniqueID                                                    \n",
       "00000f7264c27ba6fea0c837ed6aa0aa              MÉDIS      HCP\n",
       "00001e984eba85527fd3122056451279            ALLIANZ     HCIS\n",
       "000028899fe7782862d40bb1b87807ee            ALLIANZ      HCD\n",
       "00003e3bc9cdb8bfcc589d9f5ec13251  FUTURE HEALTHCARE      HCD\n",
       "000057d42f71fc08c6ebfc62ada8ebfc              MÉDIS     HCIS"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "modes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "ename": "Exception",
     "evalue": "Must produce aggregated value",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/groupby/ops.py\u001b[0m in \u001b[0;36magg_series\u001b[0;34m(self, obj, func)\u001b[0m\n\u001b[1;32m    589\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 590\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_aggregate_series_fast\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    591\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/groupby/ops.py\u001b[0m in \u001b[0;36m_aggregate_series_fast\u001b[0;34m(self, obj, func)\u001b[0m\n\u001b[1;32m    609\u001b[0m                                           dummy)\n\u001b[0;32m--> 610\u001b[0;31m         \u001b[0mresult\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcounts\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgrouper\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    611\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcounts\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/reduction.pyx\u001b[0m in \u001b[0;36mpandas._libs.reduction.SeriesGrouper.get_result\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/reduction.pyx\u001b[0m in \u001b[0;36mpandas._libs.reduction.SeriesGrouper.get_result\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/reduction.pyx\u001b[0m in \u001b[0;36mpandas._libs.reduction._get_result_array\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mValueError\u001b[0m: function does not reduce",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/groupby/generic.py\u001b[0m in \u001b[0;36maggregate\u001b[0;34m(self, func_or_funcs, *args, **kwargs)\u001b[0m\n\u001b[1;32m    775\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 776\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_python_agg_general\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfunc_or_funcs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    777\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/groupby/groupby.py\u001b[0m in \u001b[0;36m_python_agg_general\u001b[0;34m(self, func, *args, **kwargs)\u001b[0m\n\u001b[1;32m    849\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 850\u001b[0;31m                 \u001b[0mresult\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcounts\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgrouper\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0magg_series\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    851\u001b[0m                 \u001b[0moutput\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_try_cast\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mobj\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnumeric_only\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/groupby/ops.py\u001b[0m in \u001b[0;36magg_series\u001b[0;34m(self, obj, func)\u001b[0m\n\u001b[1;32m    591\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 592\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_aggregate_series_pure_python\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    593\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/groupby/ops.py\u001b[0m in \u001b[0;36m_aggregate_series_pure_python\u001b[0;34m(self, obj, func)\u001b[0m\n\u001b[1;32m    625\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mres\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mSeries\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mIndex\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mndarray\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 626\u001b[0;31m                     \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Function does not reduce'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    627\u001b[0m                 \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mempty\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mngroups\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'O'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mValueError\u001b[0m: Function does not reduce",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mException\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-159-9434f384fdd3>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mmodes\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"SpecificService\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m=\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"UniqueID\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"SpecificService\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0magg\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mSeries\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmode\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_frame\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/groupby/generic.py\u001b[0m in \u001b[0;36maggregate\u001b[0;34m(self, func_or_funcs, *args, **kwargs)\u001b[0m\n\u001b[1;32m    776\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_python_agg_general\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfunc_or_funcs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    777\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 778\u001b[0;31m                 \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_aggregate_named\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfunc_or_funcs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    779\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    780\u001b[0m             \u001b[0mindex\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mIndex\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msorted\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgrouper\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnames\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/groupby/generic.py\u001b[0m in \u001b[0;36m_aggregate_named\u001b[0;34m(self, func, *args, **kwargs)\u001b[0m\n\u001b[1;32m    907\u001b[0m             \u001b[0moutput\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mgroup\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    908\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0moutput\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mSeries\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mIndex\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mndarray\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 909\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Must produce aggregated value'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    910\u001b[0m             \u001b[0mresult\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_try_cast\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0moutput\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mgroup\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    911\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mException\u001b[0m: Must produce aggregated value"
     ]
    }
   ],
   "source": [
    "modes[\"SpecificService\"]= data.groupby(\"UniqueID\")[\"SpecificService\"].agg(pd.Series.mode).to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers = pd.merge(customers, modes, left_on='UniqueID', right_on='UniqueID', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "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>UniqueID</th>\n",
       "      <th>FrequencyofVisits</th>\n",
       "      <th>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Payer</th>\n",
       "      <th>Facility</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5962ec84553dcb84e508e2fc45789278</td>\n",
       "      <td>12</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>ADVANCECARE</td>\n",
       "      <td>CCB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>547c0d52bbfe1b93b9411d7b55f3bd82</td>\n",
       "      <td>3</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>ADVANCECARE</td>\n",
       "      <td>HCP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1770ad573874abe83cd04bfaf89c8eb6</td>\n",
       "      <td>2</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>ENTIDADES COM DESCONTO</td>\n",
       "      <td>CCB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11b9573f2fc9dd49594cd1f301cb7be6</td>\n",
       "      <td>16</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>MÉDIS</td>\n",
       "      <td>HCS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>84fdd6d40ab2a9b95f3c957237225d33</td>\n",
       "      <td>1</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>PARTICULARES</td>\n",
       "      <td>CCB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           UniqueID  FrequencyofVisits   Age       Sex  \\\n",
       "0  5962ec84553dcb84e508e2fc45789278                 12  37.0  Feminino   \n",
       "1  547c0d52bbfe1b93b9411d7b55f3bd82                  3  37.0  Feminino   \n",
       "2  1770ad573874abe83cd04bfaf89c8eb6                  2  37.0  Feminino   \n",
       "3  11b9573f2fc9dd49594cd1f301cb7be6                 16  37.0  Feminino   \n",
       "4  84fdd6d40ab2a9b95f3c957237225d33                  1  37.0  Feminino   \n",
       "\n",
       "                    Payer Facility  \n",
       "0             ADVANCECARE      CCB  \n",
       "1             ADVANCECARE      HCP  \n",
       "2  ENTIDADES COM DESCONTO      CCB  \n",
       "3                   MÉDIS      HCS  \n",
       "4            PARTICULARES      CCB  "
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export Dataset\n",
    "Use pickle format so that df reads into notebook faster "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is the long format, the transaction table \n",
    "data.to_parquet('Data/cleaned_data.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is the wide format, the customer table \n",
    "customers.to_csv(\"Data/customer_data.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}