01_data_quality_checks.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "# proGres v4 Data Quality Script- Egypt (Active and Hold- All Years)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Section 1: Setup / Import data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 1.1 Notebook setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import libraries needed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pickle\n",
    "import datetime as datetime\n",
    "import pyodbc\n",
    "import urllib\n",
    "import sqlalchemy\n",
    "import requests\n",
    "import time\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_colwidth', 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Set Server ip, Date Extracted and ISO code for Country of Interest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To change before running\n",
    "ip_of_server = \"**.***.**.**\"\n",
    "\n",
    "# date for data extraction from proGres v4\n",
    "\n",
    "# if using current date, use this: \n",
    "# date_extracted = datetime.datetime.now()\n",
    "\n",
    "# else if data was extracted on a different date use this:\n",
    "date_extracted = pd.Timestamp(\"2022-09-14\")\n",
    "\n",
    "# set date for script version\n",
    "script_version = str(pd.Timestamp(datetime.datetime.now()))\n",
    "\n",
    "# set country chosen for data quality check\n",
    "coa_country_iso_code = \"EGY\"\n",
    "coo_country_iso_code = \"SYR\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## 1.2 Import All Modules / Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Use commented lines to save username and password in text1 and text2 respectively if needed\n",
    "# text1 = ''\n",
    "# text2 = ''\n",
    "saveFile = open('pwd.txt', 'r+')\n",
    "# saveFile.write(text1 + ',' + text2)\n",
    "# saveFile.seek(0)\n",
    "uap = saveFile.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to server\n",
    "driver = 'SQL Server'\n",
    "ip = ip_of_server\n",
    "db_connection = pyodbc.connect(\n",
    "                                driver=driver,\n",
    "                                Server=ip,\n",
    "                                Port=\"1433\",\n",
    "                                Database=\"QualityCheck_Egypt\",\n",
    "                                UID=uap.split(',')[0],\n",
    "                                PWD=uap.split(',')[1]\n",
    "                                )\n",
    "\n",
    "# check\n",
    "# pyodbc.drivers()\n",
    "\n",
    "# check\n",
    "# type(db_connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Individual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in the individual table as dfi\n",
    "dfi = pd.read_sql_query(\"SELECT * FROM dbo.Filteredprogres_individual\", \n",
    "                        db_connection)\n",
    "dfi.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Registration Groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in the registration group table as dfr\n",
    "dfr = pd.read_sql_query(\"SELECT * FROM dbo.Filteredprogres_registrationgroup\", \n",
    "                        db_connection)\n",
    "dfr.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Focal Points (for each Registration Group) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dfi_fp is a subset of dfi for only focal points\n",
    "# we join this with dfr to get dfr_fp:\n",
    "# dfr_fp is the registration group table appended with demographic characteristics of focal points (if listed) for each registration group\n",
    "\n",
    "dfi_fp = dfi.copy()\n",
    "dfi_fp = dfi_fp[dfi_fp.progres_relationshiptofpname == \"Focal Point\"]\n",
    "dfi_fp_small = dfi_fp[[\n",
    "                     \"progres_relationshiptofpname\",\n",
    "                     \"progres_registrationgroupid\",\n",
    "                     \"progres_individualid\",\n",
    "                     'progres_countryofasylumidname', \n",
    "                     'progres_coalocationlevel1name',\n",
    "                     'progres_coalocationlevel2name',\n",
    "                     'progres_coalocationlevel3name',\n",
    "                     'progres_countryoforiginidname', \n",
    "                     'progres_coolocationlevel1name',\n",
    "                     'progres_coolocationlevel2name',\n",
    "                     'progres_coolocationlevel3name',\n",
    "                     'progres_maritalstatusname',\n",
    "                     'progres_sex', \n",
    "                     'progres_primaryphonenumber',\n",
    "                     'progres_hasphoto'\n",
    "                      ]]\n",
    "\n",
    "dfr_fp = pd.merge(dfr, \n",
    "                  dfi_fp_small,\n",
    "                  how=\"left\", \n",
    "                  on=\"progres_registrationgroupid\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Documents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in the documents table as dfd\n",
    "dfd = pd.read_sql_query(\"SELECT * FROM dbo.Filteredprogres_document\", \n",
    "                        db_connection)\n",
    "dfd.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in the address table as dfa\n",
    "dfa = pd.read_sql_query(\"SELECT * FROM dbo.Filteredprogres_address\", \n",
    "                        db_connection)\n",
    "dfa.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Specific Needs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in the specific needs table as dfs\n",
    "dfs = pd.read_sql_query(\"SELECT * FROM dbo.Filteredprogres_specificneed\", \n",
    "                        db_connection)\n",
    "dfs.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Combined Location-Pcode Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "coa_addresses = pd.read_sql_query(\"SELECT * FROM dbo.Egypt_locations\",\n",
    "                                  db_connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "coo_addresses = pd.read_sql_query(\"SELECT * FROM dbo.Syria_locations\",\n",
    "                                  db_connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Matched addresses are those where admin1_pcode_ocha is NOT Null (NOTE the '~' preceding condition)\n",
    "coa_matched = coa_addresses[\n",
    "                            (~coa_addresses.admin1_pcode_ocha.isna())  # & # if null, this pcode exists in v4 but not in OCHA\n",
    "                            # (~coa_addresses.progres_locationlevel.isna())  # if null, this pcode exists in OCHA but not in v4 ( can comment out to include these if we're checking the levels also)\n",
    "                            ]\n",
    "coo_matched = coo_addresses[\n",
    "                            (~coo_addresses.admin1_pcode_ocha.isna())  # & # if null, this pcode exists in v4 but not in OCHA\n",
    "                            # (~coo_addresses.len_pcode_v1.isna())  # if null, this pcode exists in v4 but not in OCHA ( cam comment out to include this if we're checking the levels also)\n",
    "                            ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get lists of UNHCR location names and OCHA location names at each level:\n",
    "coa_names_1 = coa_matched[coa_matched.progres_level == 1].progres_locationlevel.unique().tolist()\n",
    "coa_names_1_ocha = coa_matched[coa_matched.progres_level == 1].admin1_name_ocha.unique().tolist()\n",
    "\n",
    "coa_names_2 = coa_matched[coa_matched.progres_level == 2].progres_locationlevel.unique().tolist()\n",
    "coa_names_2_ocha = coa_matched[coa_matched.progres_level == 2].admin2_name_ocha.unique().tolist()\n",
    "\n",
    "coa_names_3 = coa_matched[coa_matched.progres_level == 3].progres_locationlevel.unique().tolist()\n",
    "coa_names_3_ocha = coa_matched[coa_matched.progres_level == 3].admin3_name_ocha.unique().tolist()\n",
    "\n",
    "coo_names_1 = coo_matched[coo_matched.progres_level == 1].progres_locationlevel.unique().tolist()\n",
    "coo_names_1_ocha = coo_matched[coo_matched.progres_level == 1].admin1_name_ocha.unique().tolist()\n",
    "\n",
    "coo_names_2 = coo_matched[coo_matched.progres_level == 2].progres_locationlevel.unique().tolist()\n",
    "coo_names_2_ocha = coo_matched[coo_matched.progres_level == 2].admin2_name_ocha.unique().tolist()\n",
    "\n",
    "coo_names_3 = coo_matched[coo_matched.progres_level == 3].progres_locationlevel.unique().tolist()\n",
    "coo_names_3_ocha = coo_matched[coo_matched.progres_level == 3].admin3_name_ocha.unique().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 1.3 Clean up tables as needed (statuscode, business unit, name of progres_id field etc.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Individual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter for statuscode [1, 125080000] \n",
    "# which correspond to statuscodename [Active, Hold] respectively\n",
    "dfi = dfi[dfi.statuscode.isin([1, 125080000])]\n",
    "\n",
    "# Only keep MENA business units\n",
    "list_mena_bu = ['Mauritania - Bassikounou',\n",
    "                'Mauritania - Urban',\n",
    "                'Tunisia - CO',\n",
    "                'Egypt - CO',\n",
    "                'Lebanon - North',\n",
    "                'Lebanon - BML',\n",
    "                'Lebanon - South',\n",
    "                'Lebanon - Bekaa',\n",
    "                'Algeria - CO',\n",
    "                'Iraq - CO',\n",
    "                'Israel - CO',\n",
    "                'Jordan - Camps',\n",
    "                'Jordan - Urban',\n",
    "                'Kuwait - CO',\n",
    "                'Morocco - CO',\n",
    "                'RO Riyadh',\n",
    "                'Syria - CO',\n",
    "                'UAE - CO']\n",
    "\n",
    "dfi = dfi[dfi.progres_businessunitname.isin(list_mena_bu)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Registration Group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfr = dfr[dfr.progres_businessunitname.isin(list_mena_bu)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# keep only statuscode = 1\n",
    "dfa = dfa[dfa.statuscode == 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Specific Needs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename individual to individualid\n",
    "dfs = dfs.rename(columns={\"progres_individual\" : \"progres_individualid\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filters: keep only Specific Needs Statusname = Valid and Statuscode = 1\n",
    "dfs = dfs[(dfs.progres_specificneedstatusname == 'Valid') &\n",
    "          (dfs.statuscode == 1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## 1.4 Examine / Drop any duplicated entries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dfs[~dfs.duplicated(keep=\"first\") == True].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## remove duplicates from specific needs table\n",
    "\n",
    "# dfi = dfi[~dfi.duplicated(keep=\"first\")]\n",
    "# dfr = dfr[~dfr.duplicated(keep=\"first\")]\n",
    "# dfd = dfd[~dfd.duplicated(keep=\"first\")]\n",
    "# dfa = dfa[~dfa.duplicated(keep=\"first\")]\n",
    "dfs = dfs[~dfs.duplicated(keep=\"first\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## 1.5 Keep only those document, address, and specific needs records that have a link to the individual table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfi_ids = dfi.progres_individualid.tolist()\n",
    "dfd = dfd[dfd.progres_individualid.isin(dfi_ids)]\n",
    "dfa = dfa[dfa.progres_individualid.isin(dfi_ids)]\n",
    "dfs = dfs[dfs.progres_individualid.isin(dfi_ids)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "end = time.time()\n",
    "print(end - start)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Section 2: Record Inconsistencies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.0 Specify Columns to include in the table of irregularities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for the individual table\n",
    "qc_cols = [\"progres_individualid\"]\n",
    "\n",
    "# for the registration table\n",
    "r_qc_cols = [\"progres_registrationgroupid\"]\n",
    "\n",
    "# fields to append from the individual table\n",
    "# to append once all inconsistencies have been extracted\n",
    "# these are fields that go into the PowerBi dashboard\n",
    "qc_cols_append = [\n",
    "                   \"progres_registrationdate\",\n",
    "                   \"progres_arrivaldate\",\n",
    "                   \"createdon\",\n",
    "                   \"modifiedon\",\n",
    "                   \"progres_individualid\", \n",
    "                   \"progres_id\",\n",
    "                   \"progres_businessunitname\",\n",
    "                   \"progres_countryoforiginidname\",\n",
    "                   \"progres_countryofasylumidname\",\n",
    "                   \"progres_refugeestatusname\",\n",
    "                   \"progres_dateofbirth\",\n",
    "                   \"progres_sexname\",\n",
    "                   \"progres_age\",\n",
    "                   \"progres_agecohortname\",\n",
    "                   \"createdbyname\", \n",
    "                   \"modifiedbyname\",\n",
    "                   ]\n",
    "\n",
    "\n",
    "# fields to append from the registration group table\n",
    "# once all inconsistencies have been extracted\n",
    "# these are fields that go into the PowerBi dashboard\n",
    "r_qc_cols_append = [\n",
    "                     'createdon',\n",
    "                     'modifiedon',\n",
    "                     'progres_registrationdate',\n",
    "                     'progres_registrationgroupid', \n",
    "                     'progres_registrationgroupbusinessid',\n",
    "                     'progres_businessunitname',\n",
    "                     \"progres_countryofasylumidname\",\n",
    "                     'progres_countryoforiginidname',\n",
    "                     'createdbyname',\n",
    "                     'modifiedbyname'\n",
    "                   ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 2.1. Individual Inconsistencies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 2.1.1 Fields with missing values in the individual table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# list of fields to check for missing values\n",
    "i_missing = ['progres_relationshiptofp', \n",
    "             'progres_age',\n",
    "             # 'progres_refugeestatus', # removed\n",
    "             'progres_familyname',\n",
    "             'progres_givenname',\n",
    "             'progres_maritalstatusname',\n",
    "             'progres_registrationgroupid',\n",
    "             'progres_sex',\n",
    "             'progres_arrivaldate',\n",
    "             'progres_registrationdate',\n",
    "             'progres_registrationreason',\n",
    "             'progres_fathersname', \n",
    "             'progres_dateofbirth',\n",
    "             'progres_countryoforiginidname',\n",
    "             'progres_countryofasylumidname',\n",
    "             'progres_nationalitylookupname',\n",
    "             'progres_refugeestatusname',\n",
    "             'progres_religionidname',\n",
    "             'progres_ethnicityidname',\n",
    "             # 'progres_hasphoto', # removed\n",
    "             # 'progres_biometricstatus', # removed\n",
    "             'progres_placeofbirthidname',\n",
    "             'progres_placeofbirthcity', \n",
    "             'progres_fleddate'\n",
    "             ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# description of corresponding fields\n",
    "i_missing_txt = [\"Individual's Relationship to Focal Point\",\n",
    "                 \"Individual's Age\",\n",
    "                 # \"Individual's Refugee Status\", # removed\n",
    "                 'Family Name of Individual',\n",
    "                 'Given Name of Individual',\n",
    "                 'Marital Status of Individual',\n",
    "                 'Registration Group ID of Individual',\n",
    "                 'Sex of Individual',\n",
    "                 'Arrival Date of Individual',\n",
    "                 'Registration Date of Individual',\n",
    "                 'Registration Reason of Individual',\n",
    "                 \"Father's Name\",\n",
    "                 'Date of Birth of Individual',\n",
    "                 'Country of Origin for Individual',\n",
    "                 'Country of Asylum for Individual',\n",
    "                 'Nationality of Individual',\n",
    "                 'Refugee Status of Individual',\n",
    "                 'Religion of Individual',\n",
    "                 'Ethnicity of Individual',\n",
    "                 # 'The Has Photo field for Individual', # removed\n",
    "                 # 'Biometric Status of Individual', # removed\n",
    "                 'Place of Birth of Individual',\n",
    "                 'Place of Birth (City) of Individual',\n",
    "                 'Individual Fled Date'\n",
    "                 ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# zip the two lists together into a dictionary\n",
    "i_missing_dict = dict(zip(i_missing, \n",
    "                          i_missing_txt))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "i_missing_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame()\n",
    "indicators_checked = pd.DataFrame()\n",
    "\n",
    "# For each field, \n",
    "# 1) identify the individuals for whom values are missingAND\n",
    "# 2) make note of fields checked for missing values in the \"indicators_checked\" table along with other info about this indicator\n",
    "\n",
    "for field in i_missing:\n",
    "    # 1)  identify the individuals for whom values are missing\n",
    "    dftemp = pd.DataFrame()\n",
    "    dftemp[\"progres_individualid\"] = dfi[(dfi[field].isna()) | (dfi[field] == '-')][qc_cols]\n",
    "    dftemp[\"Irregularity\"] = \"{} is missing or '-'\".format(i_missing_dict[field])\n",
    "    # append dftemp (which has information on individual id and related \"Irregularity\") to existing df\n",
    "    df = pd.concat([df, dftemp])\n",
    "    \n",
    "    # 2) add field to \"indicators_checked\" table along with other info about this indicator\n",
    "    temp_indicators = pd.DataFrame()\n",
    "    temp_indicators.loc[1,\"Irregularity\"] = \"{} is missing or '-'\".format(i_missing_dict[field])\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"{} is null or '-'\".format(field)\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"missing information\"\n",
    "    # append temp_indicators table (with information on the indicator screened in each iteration) to the indicators_checked table\n",
    "    indicators_checked = pd.concat([indicators_checked, temp_indicators])  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Irregularity.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 2.1.2. Other individual inconsistencies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Individual Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "    Each of the functions below takes as \n",
    "    input: pandas DataFrame (relevant table from proGres needed to check for given inconsistency)\n",
    "    and \n",
    "    outputs: \n",
    "    (1) dftemp (pandas DataFrame) that identifies individual ids (progres_individualid) associated with records that have the relevant irregularity (Irregularity)\n",
    "    (2) temp_indicators, which lists the indicator name along with additional information about the indicator, such as:\n",
    "    - proGres table: string which describes the proGres table(s) from which records were searched for the relevant irregularity \n",
    "    - Criteria: string which describes the criteria applied to filter records for this irregularlity\n",
    "    - Grouping: The overall grouping/category name that would characterize the type of irregularity\n",
    "    \n",
    "    Also Note: [qc_cols] specified in section 1.1. lists columns from the relevant proGres table needed to construct the summary tables needed for the PowerBi dashboard\n",
    "'''\n",
    "\n",
    "def ind_wo_reg():\n",
    "    dftemp = dfi[(dfi.progres_registrationgroupid.isna())|\n",
    "            (dfi.progres_registrationgroupid == '-')][qc_cols]\n",
    "    name_of_irr = 'This individualID does not belong to any registration group'\n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_registrationgroupid is null or '-'\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"missing information\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def missing_biometric():\n",
    "    dftemp = dfi[(dfi.progres_biometricstatus.isna())&\n",
    "            (dfi.progres_age>=5) # & # changed back to 5 after consultation with Reg&IM team\n",
    "            # (dfi.progres_relationshiptofpname == \"Focal Point\") \n",
    "             ][qc_cols]\n",
    "    name_of_irr = 'Individual (age>=5) with missing biometricstatus'\n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_biometricstatus is null for individual 5 years or older\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"missing information\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "    \n",
    "def reg_date_future():\n",
    "    dftemp = dfi[dfi.progres_registrationdate>date_extracted][qc_cols]    \n",
    "    name_of_irr = 'The registration date of this individual is in the future'\n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_registrationdate is later than date extracted\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"date issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def arr_date_reg_date():\n",
    "    dftemp = dfi[dfi.progres_arrivaldate>dfi.progres_registrationdate][qc_cols]\n",
    "    name_of_irr = 'The date of arrival is later than the registration date'\n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_arrival date is later than progres_registrationdate\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"date issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "\n",
    "def coo_coa_not_noc():\n",
    "    dftemp = dfi[(~dfi.progres_refugeestatusname.isin([\"Not of concern\", \n",
    "                                                       \"Other of concern\",\n",
    "                                                       # \"Stateless (non-refugee)\"\n",
    "                                                      ]\n",
    "                                                     ))&\n",
    "                 (dfi.progres_countryoforiginid == dfi.progres_countryofasylumid) &\n",
    "                 (~dfi.progres_countryoforiginid.isna())][qc_cols] \n",
    "    name_of_irr = \"Individual has COO = COA but refugee status is Refugee or Asylum Seeker or Stateless\"\n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_countryoforiginid == progres_countryofasylumid but progres_refugeestatus is not NOC or OOC\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"status issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def deceased_active_hold():\n",
    "    dftemp = dfi[(dfi.progres_isdeceased == True)&(dfi.statuscode.isin([1,125080000]))][qc_cols]\n",
    "    name_of_irr = 'The individual is marked as deceased but is still active/on hold'\n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_isdeceased is True but statuscode is Active or On Hold\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"status issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "    \n",
    "def ind_in_multiple_rg():\n",
    "    ind_in_multiple_reg = dfi.groupby([\"progres_individualid\"])[\"progres_registrationgroupid\"]\\\n",
    "                                    .count()\\\n",
    "                                    .reset_index()\\\n",
    "                                    .sort_values(by=\"progres_registrationgroupid\", ascending=False)\n",
    "    ind_in_multiple_reg_list = ind_in_multiple_reg[ind_in_multiple_reg.progres_registrationgroupid>1]\\\n",
    "                                    .progres_individualid.tolist()\n",
    "    dftemp = dfi[dfi.progres_individualid.isin(ind_in_multiple_reg_list)][qc_cols]\n",
    "    name_of_irr = 'This individual id number belongs to more than one registration group' \n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"groupby on progres_individualid lists multiple progres_registrationgroupid\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"status issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def rel_fp_sex():\n",
    "    female_rel = ['Aunt', 'Common Law Wife', 'Cousin (female)', 'Daughter',\n",
    "                   'Ex-wife', 'Focal Point', 'Foster daughter', 'Granddaughter',\n",
    "                   'Grandmother', 'Half-sister', 'In Law - Sister', 'In-Law (female)',\n",
    "                   'In-Law - Daughter', 'In-Law - Mother', 'Mother', 'Niece',\n",
    "                   'No blood relation (female)', 'Not specified/unknown (female)',\n",
    "                   'Other blood relation (female)', 'Partner (Female)', 'Sister',\n",
    "                   'Step-daughter', 'Step-mother', 'Step-sister', 'Wife']\n",
    "    male_rel = ['Son','Brother', 'Common Law Husband', 'Cousin (male)', 'Father',\n",
    "                   'Foster father', 'Foster son', 'Grandfather', 'Grandson',\n",
    "                   'Half-brother', 'Husband', 'In Law - Brother', 'In-Law (male)',\n",
    "                   'In-Law - Father', 'In-Law - Son', 'Nephew',\n",
    "                   'No blood relation (male)', 'Other blood relation (male)',\n",
    "                   'Partner (Male)', 'Step-brother', 'Step-father', 'Step-son',\n",
    "                   'Uncle']\n",
    "    dftemp = dfi[(((dfi.progres_sexname == \"Female\")&(dfi.progres_relationshiptofpname.isin(male_rel)))|\n",
    "                  ((dfi.progres_sexname == \"Male\")&(dfi.progres_relationshiptofpname.isin(female_rel))))&\n",
    "                 (dfi.progres_relationshiptofpname != \"Focal Point\")][qc_cols]\n",
    "    name_of_irr = 'The Relationship to Focal Point does not match individual sex' \n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"(progres_sexname == 'Female'& dfi.progres_relationshiptofpname is a male relative) OR \\\n",
    "                                    (progres_sexname == 'Male' and and dfi.progres_relationshiptofpname is a female relative)\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"relationship issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def fp_younger_children():\n",
    "    fp_age = dfi[dfi.progres_relationshiptofpname == \"Focal Point\"][[\"progres_individualid\", \n",
    "                                                                   \"progres_registrationgroupid\", \n",
    "                                                                   \"progres_relationshiptofpname\", \n",
    "                                                                   \"progres_age\"]]\n",
    "    r_age = dfi[dfi.progres_relationshiptofpname.isin([\"Son\",\"Daughter\"])][[\"progres_registrationgroupid\", \n",
    "                                                                            \"progres_relationshiptofpname\", \n",
    "                                                                            \"progres_age\"]]\n",
    "    dftemp = pd.merge(fp_age, r_age, how=\"inner\", on=\"progres_registrationgroupid\")\n",
    "    fp_age_issue = dftemp[dftemp.progres_age_x < dftemp.progres_age_y].progres_individualid.tolist()\n",
    "    dftemp = dfi[dfi.progres_individualid.isin(fp_age_issue)][qc_cols]\n",
    "    name_of_irr = 'Focal point is younger than his/her son/daughter'\n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"link individuals with relationship listed as Son or Daughter to their respective focal points and compare their ages. \\\n",
    "                          Flag as Irregularity if progres_age_x (age of focal point) < progres_age_y (age of son or daughter) \"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"relationship issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def fp_older_parents():\n",
    "    fp_age = dfi[dfi.progres_relationshiptofpname == \"Focal Point\"][[\"progres_individualid\", \n",
    "                                                                   \"progres_registrationgroupid\", \n",
    "                                                                   \"progres_relationshiptofpname\", \n",
    "                                                                   \"progres_age\", \n",
    "                                                                   \"progres_dateofbirth\"]]\n",
    "    r_age = dfi[dfi.progres_relationshiptofpname.isin([\"Mother\",\"Father\"])][[\"progres_registrationgroupid\", \n",
    "                                                                         \"progres_relationshiptofpname\", \n",
    "                                                                         \"progres_age\", \n",
    "                                                                         \"progres_dateofbirth\"]]\n",
    "    dftemp = pd.merge(fp_age, r_age, how=\"inner\", on=\"progres_registrationgroupid\")\n",
    "    fp_age_issue = dftemp[dftemp.progres_age_x > dftemp.progres_age_y].progres_individualid.tolist()\n",
    "    dftemp = dfi[dfi.progres_individualid.isin(fp_age_issue)][qc_cols]\n",
    "    name_of_irr = 'Focal point is older than his/her mother/father' \n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"link individuals with relationship listed as Mother or Father to their respective focal points and compare their ages. \\\n",
    "                          Flag as Irregularity if progres_age_x (age of focal point) > progres_age_y (age of mother or father) \"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"relationship issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def fp_hw_not_married():\n",
    "    dftemp = dfi[((dfi.progres_relationshiptofpname == \"Husband\")|\n",
    "               (dfi.progres_relationshiptofpname == \"Wife\"))&\n",
    "                 (~dfi.progres_maritalstatusname.isin([\"Married\", \"Common Law Married\"]))][qc_cols]\n",
    "    name_of_irr = \"Husband or Wife of Focal Point with Marital Status other than Married or Common Law Married\" \n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"records where progres_relationshiptofpname is Husband or Wife, \\\n",
    "                          but individual record has a different marital status\\\n",
    "                          other than Married or Common Law Married\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"relationship issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def active_noc():\n",
    "    dftemp = dfi[(dfi.statuscodename == \"Active\")&(dfi.progres_refugeestatusname == \"Not of concern\")][qc_cols]\n",
    "    name_of_irr = \"Active Individual with Legal Status 'Not of Concern'\" \n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"records where statuscode is Active but refugee status is Not of Concern \"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"status issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def dates_new_birth():\n",
    "    dfi_nb = dfi[dfi.progres_registrationreasonname == \"New Birth\"]\n",
    "    # date of birth not equal to arrival date OR\n",
    "    # date of arrival not equal to fled date OR\n",
    "    # date of birth not equal to fled date\n",
    "    dftemp = dfi_nb[(dfi_nb.progres_dateofbirth != dfi_nb.progres_arrivaldate)|\n",
    "                       (dfi_nb.progres_arrivaldate != dfi_nb.progres_fleddate)|\n",
    "                       (dfi_nb.progres_dateofbirth != dfi_nb.progres_fleddate)][qc_cols]\n",
    "    name_of_irr = \"Mismatched date of birth, arrival date, and fled date for Individuals with Registration Reason = 'New Birth'\" \n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"For individuals with progres_registrationreasonname=='New Birth', date of birth, arrival date and fled date do not match\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"date issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def fled_arrival_date():\n",
    "    dftemp = dfi[dfi.progres_fleddate > dfi.progres_arrivaldate][qc_cols]    \n",
    "    name_of_irr = \"Individual with Fled Date after Arrival Date\"\n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_fleddate is later than progres_arrivaldate\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"date issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def fled_reg_date():\n",
    "    dftemp = dfi[dfi.progres_fleddate > dfi.progres_registrationdate][qc_cols]\n",
    "    name_of_irr =  \"Individual with Fled Date after Registration Date\" \n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_fleddate is later than progres_registrationdate\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"date issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "   \n",
    "\n",
    "def dob_reg_date():\n",
    "    dftemp = dfi[dfi.progres_dateofbirth > dfi.progres_registrationdate][qc_cols]\n",
    "    name_of_irr = \"Individual with Date of Birth after Registration Date\" \n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_dateofbirth is later than progres_registrationdate\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"date issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "    \n",
    "\n",
    "# # removed after consultations with Reg&IM\n",
    "# def arrival_recent_reg():\n",
    "#     dftemp = dfi.copy()\n",
    "#     # year of arrival\n",
    "#     dftemp[\"yoa\"] = dftemp[\"progres_arrivaldate\"].apply(lambda x: x.year) \n",
    "#     # year of birth\n",
    "#     dftemp[\"yob\"] = dftemp[\"progres_dateofbirth\"].apply(lambda x: x.year)\n",
    "#     # year of registration\n",
    "#     dftemp[\"yor\"] = dftemp[\"progres_registrationdate\"].apply(lambda x: x.year)\n",
    "#     dftemp = dftemp[(dftemp.yoa<2000)& # arrival before the year 2000\n",
    "#                     (dftemp.yor>=2010)& # registration on or after the year 2010\n",
    "#                     (dftemp.yoa != dftemp.yob)& # year of arrival not equal to year of birth\n",
    "#                     (dftemp.progres_nationalitylookupname != dftemp.progres_countryofasylumidname) # nationality not same as COA\n",
    "#                    ][qc_cols]\n",
    "#     name_of_irr = \"Individual arrival date is before 2000 but recently registered after 2010\" \n",
    "#     dftemp[\"Irregularity\"] = name_of_irr\n",
    "\n",
    "#    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "#    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "#    temp_indicators.loc[1,\"Criteria\"] = \"Year of Arrival before 2000 but registered after 2010 and year of arrival not equal to year of birth and nationality not same as COA\"\n",
    "#    temp_indicators.loc[1,\"Grouping\"] = \"status issue\"\n",
    "    \n",
    "#    return dftemp, temp_indicators\n",
    "\n",
    "\n",
    "def coa_bu_mismatch():\n",
    "    temp = dfi.copy()\n",
    "    temp[\"COA_1\"] = temp[\"progres_businessunitname\"].apply(lambda x: x.split('-')[0][:-1])\n",
    "    temp[\"COA_1\"] = temp[\"COA_1\"].apply(lambda x: \"GCC except UAE\" if x == \"RO Riyad\" else x)\n",
    "    dftemp = temp[(temp.COA_1 != dfi.progres_countryofasylumidname)&(temp.COA_1 != \"GCC except UAE\")&(~temp.COA_1.isin([\"Syria\",\"UAE\"]))][qc_cols]\n",
    "    name_of_irr = 'Country of Asylum does not match country of business unit'\n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_businessunitname does not match progres_countryofasylumidname\\\n",
    "                         mismatch between the two values (accounting for the fact that some will have the following values: \\\n",
    "                         RO Riyad, GCC except UAE, Syria, UAE instead)\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"status issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "\n",
    "def ind_age():\n",
    "    temp = dfi.copy()\n",
    "    # 1 (convert string to timestamps)\n",
    "    temp['dob'] = pd.to_datetime(temp['progres_dateofbirth'], format='%m%d%y')    \n",
    "    # 2 (correct for format %m%d%y so year=52 will be read as 1952 instead of 2052\n",
    "    temp['dob'] = temp['dob'].where(temp['dob'] < date_extracted, temp['dob'] -  np.timedelta64(100, 'Y'))    \n",
    "    # 3 subtract dob from date_extracted to obtain timedelta\n",
    "    temp['calculated_age'] = (date_extracted - temp['dob']).astype('<m8[Y]')\n",
    "    # month of birth\n",
    "    temp[\"mob\"] = temp[\"dob\"].apply(lambda x: x.month)\n",
    "    # exact DD, i.e. day of birth\n",
    "    temp[\"dayob\"] = temp[\"dob\"].apply(lambda x: x.day)\n",
    "    # tag as irregularity if the difference between the age in individual table and calculated age is greater than 1\n",
    "    dftemp = temp[(temp.progres_age != temp.calculated_age)&\n",
    "            (abs(temp.progres_age - temp.calculated_age)>0)&\n",
    "             (temp.mob != date_extracted.month)&\n",
    "             (temp.dayob != date_extracted.day)\n",
    "                 ][qc_cols]\n",
    "    name_of_irr = \"Individual with erroneous age\"\n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "        \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_age does not match age calculated based on progres_dateofbirth and date_extracted\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"date issue\"\n",
    "    \n",
    "    return dftemp, temp_indicators\n",
    "    \n",
    "\n",
    "def ind_photo_missing():\n",
    "    dftemp = dfi[(dfi.progres_hasphoto == 0)|\n",
    "                   (dfi.progres_hasphoto.isna())][qc_cols]\n",
    "    name_of_irr = 'Individual does not have a photo' \n",
    "    dftemp[\"Irregularity\"] = name_of_irr\n",
    "        \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_hasphoto for focal point is null or 0\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"missing information\"\n",
    "    \n",
    "    return dftemp, temp_indicators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of function names corresponding to irregularities we want to check for:\n",
    "indicators_functions = [ind_wo_reg, \n",
    "                        missing_biometric, \n",
    "                        reg_date_future,\n",
    "                        arr_date_reg_date,\n",
    "                        coo_coa_not_noc,\n",
    "                        deceased_active_hold,\n",
    "                        ind_in_multiple_rg,\n",
    "                        rel_fp_sex, \n",
    "                        fp_younger_children,\n",
    "                        fp_older_parents,\n",
    "                        fp_hw_not_married,\n",
    "                        active_noc,\n",
    "                        dates_new_birth,\n",
    "                        fled_arrival_date,\n",
    "                        fled_reg_date,\n",
    "                        dob_reg_date,\n",
    "                        # arrival_recent_reg,\n",
    "                        coa_bu_mismatch,\n",
    "                        ind_age,\n",
    "                        ind_photo_missing]\n",
    "\n",
    "# Loop through list of functions and save outputs in df table and indicators_checked table: \n",
    "for funcs in indicators_functions :\n",
    "    func_returns = funcs()\n",
    "    \n",
    "    dftemp = func_returns[0]\n",
    "    df = pd.concat([df, dftemp])\n",
    "    \n",
    "    temp_indicators = func_returns[1]\n",
    "    indicators_checked = pd.concat([indicators_checked, temp_indicators])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### To find cases with this Irregularity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"Individual has COO = COA but refugee status is Refugee or Asylum Seeker\"\n",
    "irregularity_table = df\n",
    "search_table = dfi\n",
    "id_needed = \"progres_individualid\"\n",
    "\n",
    "issues = search_table[search_table[id_needed].isin(irregularity_table[irregularity_table.Irregularity == irregularity_name][id_needed].tolist())]\n",
    "issues.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"Individual with erroneous age\"\n",
    "irregularity_table = df\n",
    "search_table = dfi\n",
    "id_needed = \"progres_individualid\"\n",
    "\n",
    "issues = search_table[search_table[id_needed].isin(irregularity_table[irregularity_table.Irregularity == irregularity_name][id_needed].tolist())]\n",
    "issues[[\"progres_age\", \"progres_dateofbirth\"]].head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"Individual with Date of Birth after Registration Date\"\n",
    "irregularity_table = df\n",
    "search_table = dfi\n",
    "id_needed = \"progres_individualid\"\n",
    "\n",
    "issues = search_table[search_table[id_needed].isin(irregularity_table[irregularity_table.Irregularity == irregularity_name][id_needed].tolist())]\n",
    "issues[[\"progres_indvidualid\", \"progres_registrationdate\", \"progres_dateofbirth\", \"progres_refugeestatusname\", \"statuscodename\"]].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2. Registration Inconsistencies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create input tables to be used for registration group quality checks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use the r_i_join_all table for the following two functions in the subsequent section:\n",
    "# closed_reg_w_active_ind AND\n",
    "# active_rg_wo_active_ind\n",
    "\n",
    "# this table will have fields from the individual table appended to the registration group table\n",
    "r_i_join_all = pd.merge(dfr[[\"progres_registrationgroupid\",\"statuscode\"]], \n",
    "                   dfi, \n",
    "                   how=\"left\", \n",
    "                   on=[\"progres_registrationgroupid\"])\n",
    "\n",
    "# filter out everything except active / hold for all other functions / dq checks\n",
    "dfr = dfr[dfr.statuscode.isin([1, 125080000])]\n",
    "\n",
    "# this table will have fields from the individual table appended to the registration group table (but only for active/hold registration groups)\n",
    "r_i_join = pd.merge(dfr[[\"progres_registrationgroupid\",\"statuscode\"]], \n",
    "                   dfi, \n",
    "                   how=\"left\", \n",
    "                   on=[\"progres_registrationgroupid\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Registration functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "    Each of the functions below takes as \n",
    "    input: pandas DataFrame (relevant table from proGres needed to check for given inconsistency)\n",
    "    and \n",
    "    outputs: \n",
    "    (1) r_dftemp (pandas DataFrame) that identifies registration ids (progres_registrationgroupid) associated with records that have the relevant irregularity (Irregularity)\n",
    "    (2) temp_indicators, which lists the indicator name along with additional information about the indicator, such as:\n",
    "    - proGres table: string which describes the proGres table(s) from which records were searched for the relevant irregularity \n",
    "    - Criteria: string which describes the criteria applied to filter records for this irregularlity\n",
    "    - Grouping: The overall grouping/category name that would characterize the type of irregularity\n",
    "    \n",
    "    Also Note: [r_qc_cols] specified in section 1.1. lists columns from the relevant proGres table needed to construct the summary tables needed for the PowerBi dashboard\n",
    "'''\n",
    "\n",
    "\n",
    "def closed_reg_w_active_ind():\n",
    "    # closed, inactive, erroneous\n",
    "    closed_or_inactive_list = r_i_join_all[(r_i_join_all.statuscode_x.isin([125080001, \n",
    "                                                             125080002, \n",
    "                                                             2]))&\n",
    "                                      (r_i_join_all.statuscode_y.isin([1, 125080000]))].progres_registrationgroupid.tolist()\n",
    "    r_dftemp = dfr[dfr.progres_registrationgroupid.isin(closed_or_inactive_list)][r_qc_cols]\n",
    "    name_of_irr = \"Closed or Inactivated registration with at least one individual active or on hold\"\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged with individual table on progres_registrationgroupid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"join registration table with individual table on registrationgroupid; \\\n",
    "                          flag as irregularity if statuscode_x is closed, inactive, or erroeneous but statuscode_y is active\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"status issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "    \n",
    "\n",
    "def active_rg_wo_active_ind():\n",
    "    r_dftemp = r_i_join[(r_i_join.progres_individualid.isna())&\n",
    "                       (r_i_join.statuscode_x.isin([1, 125080000])) #recently added\n",
    "                       ][r_qc_cols]\n",
    "    name_of_irr = \"Active Registration Group with No Active Individual\"\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged with individual table on progres_registrationgroupid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"join registration table with individual table on registrationgroupid; \\\n",
    "                            flag as irregularity if statuscode_x is active but progres_individualid is null\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"status issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "\n",
    "def rg_size_mismatch():\n",
    "    reg_size = dfi.groupby([\"progres_registrationgroupid\"])\\\n",
    "                .progres_individualid\\\n",
    "                .count()\\\n",
    "                .reset_index()\\\n",
    "                .sort_values(by=\"progres_individualid\", ascending=False)\\\n",
    "                .rename(columns={\"progres_individualid\":\"rg_size\"})\n",
    "    dfr_size = pd.merge(dfr, reg_size, how=\"inner\", on=\"progres_registrationgroupid\")\n",
    "    r_dftemp = dfr_size[(dfr_size.progres_size != dfr_size.rg_size)][r_qc_cols]\n",
    "    name_of_irr = \"Size of registration group not equal to no. of active individuals in the group\"\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged with individual table on progres_registrationgroupid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_size on registration table not equal to number of active individuals linked to the registration group\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"status issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "\n",
    "def reg_date_future():\n",
    "    r_dftemp = dfr[dfr.progres_registrationdate >date_extracted][r_qc_cols]\n",
    "    name_of_irr = \"RegistrationGroup with registration date in the future\"\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_registrationdate later than date extracted\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"date issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "   \n",
    "\n",
    "def rg_wo_active_fp():\n",
    "    r_dftemp = dfr_fp[dfr_fp.progres_individualid.isna()][r_qc_cols]\n",
    "    name_of_irr = \"Active Registration Group without active focal point\"\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged on progres_registrationgroupid with individual records of focal points only\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_individualid for individual listed as focal point did not have a match in the registration table\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"status issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "    \n",
    "def rg_w_more_fp():\n",
    "    rg_fp = dfr_fp\\\n",
    "            .groupby([\"progres_registrationgroupid\"])\\\n",
    "            [\"progres_individualid\"]\\\n",
    "            .count()\\\n",
    "            .reset_index()\\\n",
    "            .sort_values(by=\"progres_individualid\",ascending=False)\n",
    "    rg_fp = rg_fp[rg_fp.progres_individualid>1]\n",
    "    rgid_with_multiple_fp = rg_fp.progres_registrationgroupid.tolist()\n",
    "    r_dftemp = dfr[dfr.progres_registrationgroupid.isin(rgid_with_multiple_fp)][r_qc_cols]\n",
    "    name_of_irr = 'Registration group has more than one focal point'\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged, on progres_registrationgroupid, with individual records of focal points only\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"count number of individual records with relationshiptofpname ==  'Focal Point' linked to a registration group\\\n",
    "                            flag as irregularity if count is more than 1\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"status issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "\n",
    "def fp_missing_phonenumber():\n",
    "    r_dftemp = dfr_fp[(~dfr_fp.progres_individualid.isna())&\n",
    "                      ((dfr_fp.progres_primaryphonenumber.isna())|\n",
    "                       (dfr_fp.progres_primaryphonenumber == '-'))][r_qc_cols]\n",
    "    name_of_irr = 'Focal Point has no phone number'\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged, on progres_registrationgroupid, with individual records of focal points only\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_primaryphonenumber for focal point is null or '-'\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"missing information\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "\n",
    "def fp_coo_1_missing():\n",
    "    rids = dfr_fp[(~dfr_fp.progres_individualid.isna())&\n",
    "              ((dfr_fp.progres_coolocationlevel1name.isna())|\n",
    "               (dfr_fp.progres_coolocationlevel1name == \"-\"))].progres_registrationgroupid.tolist()\n",
    "    r_dftemp = dfr[dfr.progres_registrationgroupid.isin(rids)][r_qc_cols]\n",
    "    name_of_irr = 'The Country of Origin Level 1 is missing for focal point'\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged, on progres_registrationgroupid, with individual records of focal points only\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_coolocationlevel1name is null or '-'\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"address issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "\n",
    "def fp_coa_1_missing():\n",
    "    rids = dfr_fp[(~dfr_fp.progres_individualid.isna())&\n",
    "              ((dfr_fp.progres_coalocationlevel1name.isna())|\n",
    "               (dfr_fp.progres_coalocationlevel1name == \"-\"))].progres_registrationgroupid.tolist()\n",
    "    r_dftemp = dfr[dfr.progres_registrationgroupid.isin(rids)][r_qc_cols]\n",
    "    name_of_irr = 'The Country of Asylum Level 1 is missing for focal point'\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged, on progres_registrationgroupid, with individual records of focal points only\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_coalocationlevel1name is null or '-'\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"address issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "\n",
    "def fp_coo_not_official():\n",
    "    '''\n",
    "    irregularity if focal point progres location level names don't match \n",
    "    1) either the names in OCHA OR \n",
    "    2) the names in the location level table in proGres v4 (matched against ocha pcodes)\n",
    "    '''\n",
    "    r_dftemp = dfr_fp[(~dfr_fp.progres_individualid.isna())&\n",
    "                       (dfr_fp.progres_countryoforiginidname == \"Syrian Arab Republic\")&\n",
    "                       (~dfr_fp.progres_coolocationlevel1name.isna())&\n",
    "                       (dfr_fp.progres_coolocationlevel1name != '-')&\n",
    "                       (\n",
    "                        (~dfr_fp.progres_coolocationlevel1name.isin(list(set(coo_names_1 + coo_names_1_ocha)))) |\n",
    "                        (~dfr_fp.progres_coolocationlevel2name.isin(list(set(coo_names_2 + coo_names_2_ocha)))) |\n",
    "                        (~dfr_fp.progres_coolocationlevel3name.isin(list(set(coo_names_3 + coo_names_3_ocha)))) \n",
    "                       )\n",
    "                      ][r_qc_cols]\n",
    "    name_of_irr = 'Focal Point COO address does not match official list (COO=Syria only)'\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged, on progres_registrationgroupid, with individual records of focal points only\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_coolocationlevelname does not match official admin names at levels 1 2 or 3\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"address issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "\n",
    "def fp_coa_not_official():\n",
    "    '''\n",
    "    irregularity if focal point progres location level names don't match \n",
    "    1) either the names in OCHA OR \n",
    "    2) the names in the location level table in proGres v4 (matched against ocha pcodes)\n",
    "    '''\n",
    "    r_dftemp = dfr_fp[(~dfr_fp.progres_individualid.isna())&\n",
    "                       (~dfr_fp.progres_coalocationlevel1name.isna())&\n",
    "                       (dfr_fp.progres_coalocationlevel1name != '-') &\n",
    "                       (\n",
    "                           (~dfr_fp.progres_coalocationlevel1name.isin(list(set(coa_names_1 + coa_names_1_ocha)))) |\n",
    "                           (~dfr_fp.progres_coalocationlevel2name.isin(list(set(coa_names_2 + coa_names_2_ocha)))) |\n",
    "                           (~dfr_fp.progres_coalocationlevel3name.isin(list(set(coa_names_3 + coa_names_3_ocha)))) \n",
    "                       )\n",
    "                     ][r_qc_cols]\n",
    "    name_of_irr = 'Focal Point COA address does not match official list'\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged, on progres_registrationgroupid, with individual records of focal points only\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_coalocationlevelname does not match official admin names at levels 1 2 or 3\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"address issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "\n",
    "def fp_coo_2_3():\n",
    "    r_dftemp = dfr_fp[(~dfr_fp.progres_individualid.isna())&\n",
    "       (((dfr_fp.progres_coolocationlevel2name.isna())\n",
    "         |(dfr_fp.progres_coolocationlevel2name == '-'))&\n",
    "         (~dfr_fp.progres_coolocationlevel3name.isna()))][r_qc_cols]\n",
    "    name_of_irr = 'COO Level 2 missing while Level 3 exists for focal point'\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged, on progres_registrationgroupid, with individual records of focal points only\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_coolocationlevel2name is missing but progres_coolocationlevel3name exists\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"address issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "\n",
    "def fp_coo_1_2():\n",
    "    r_dftemp = dfr_fp[(~dfr_fp.progres_individualid.isna())&\n",
    "       (((dfr_fp.progres_coolocationlevel1name.isna())\n",
    "         |(dfr_fp.progres_coolocationlevel1name == '-'))&\n",
    "         (~dfr_fp.progres_coolocationlevel2name.isna()))][r_qc_cols]\n",
    "    name_of_irr = 'COO Level 1 missing while Level 2 exists for focal point'\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged, on progres_registrationgroupid, with individual records of focal points only\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_coolocationlevel1name is missing but progres_coolocationlevel2name exists\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"address issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "\n",
    "def fp_coa_2_3():\n",
    "    r_dftemp = dfr_fp[(~dfr_fp.progres_individualid.isna())&\n",
    "       (((dfr_fp.progres_coalocationlevel2name.isna())\n",
    "         |(dfr_fp.progres_coalocationlevel2name == '-'))&\n",
    "         (~dfr_fp.progres_coalocationlevel3name.isna()))][r_qc_cols]\n",
    "    name_of_irr = 'COA Level 2 missing while Level 3 exists for focal point'\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged, on progres_registrationgroupid, with individual records of focal points only\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_coalocationlevel2name is missing but progres_coalocationlevel3name exists\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"address issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators\n",
    "\n",
    "def fp_coa_1_2():\n",
    "    r_dftemp = dfr_fp[(~dfr_fp.progres_individualid.isna())&\n",
    "       (((dfr_fp.progres_coalocationlevel1name.isna())\n",
    "         |(dfr_fp.progres_coalocationlevel1name == '-'))&\n",
    "         (~dfr_fp.progres_coalocationlevel2name.isna()))][r_qc_cols]\n",
    "    name_of_irr = 'COA Level 1 missing while Level 2 exists for focal point'\n",
    "    r_dftemp[\"Irregularity\"] = name_of_irr\n",
    "\n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"registration table merged, on progres_registrationgroupid, with individual records of focal points only\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"progres_coalocationlevel1name is missing but progres_coalocationlevel2name exists\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"address issue\"\n",
    "    \n",
    "    return r_dftemp, temp_indicators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of function names corresponding to irregularities we want to check for:\n",
    "\n",
    "reg_indicators_functions = [closed_reg_w_active_ind,\n",
    "                            active_rg_wo_active_ind,\n",
    "                            rg_size_mismatch,\n",
    "                            reg_date_future,\n",
    "                            rg_wo_active_fp,\n",
    "                            rg_w_more_fp,\n",
    "                            fp_missing_phonenumber,\n",
    "                            fp_coo_1_missing,\n",
    "                            fp_coa_1_missing,\n",
    "                            fp_coo_not_official,\n",
    "                            fp_coa_not_official,\n",
    "                            fp_coo_2_3,\n",
    "                            fp_coo_1_2,\n",
    "                            fp_coa_2_3,\n",
    "                            fp_coa_1_2,\n",
    "                           ]    \n",
    "# Loop through list of functions and save outputs in df table and indicators_checked table: \n",
    "rdf = pd.DataFrame()\n",
    "for funcs in reg_indicators_functions :\n",
    "    func_returns = funcs()\n",
    "    \n",
    "    r_dftemp = func_returns[0]\n",
    "    rdf = pd.concat([rdf, r_dftemp])\n",
    "    \n",
    "    temp_indicators = func_returns[1]\n",
    "    indicators_checked = pd.concat([indicators_checked, temp_indicators])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rdf.Irregularity.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### To find cases with this Irregularity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"Active Registration Group with No Active Individual\"\n",
    "irregularity_table = rdf # df\n",
    "search_table = dfr # dfi\n",
    "id_needed = \"progres_registrationgroupid\" # \"progres_individualid\"\n",
    "\n",
    "issues = search_table[search_table[id_needed].isin(irregularity_table[irregularity_table.Irregularity == irregularity_name][id_needed].tolist())]\n",
    "issues.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"Registration group has more than one focal point\"\n",
    "irregularity_table = rdf # df\n",
    "search_table = dfr # dfi\n",
    "id_needed = \"progres_registrationgroupid\" # \"progres_individualid\"\n",
    "\n",
    "issues = search_table[search_table[id_needed].isin(irregularity_table[irregularity_table.Irregularity == irregularity_name][id_needed].tolist())]\n",
    "issues.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rdf[~rdf.duplicated(keep=\"first\") == True].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rdf[rdf.duplicated(keep=False) == True].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 2.3. Address Inconsistencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fp_address = pd.merge(dfi_fp, dfa, how=\"left\", on=\"progres_individualid\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfi_fp.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fp_address.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "    Each of the functions below takes as \n",
    "    input: pandas DataFrame (relevant table from proGres needed to check for given inconsistency)\n",
    "    and \n",
    "    outputs: \n",
    "    (1) a_dftemp (pandas DataFrame) that identifies registration ids (progres_registrationgroupid) associated with records that have the relevant irregularity (Irregularity)\n",
    "    (2) temp_indicators, which lists the indicator name along with additional information about the indicator, such as:\n",
    "    - proGres table: string which describes the proGres table(s) from which records were searched for the relevant irregularity \n",
    "    - Criteria: string which describes the criteria applied to filter records for this irregularlity\n",
    "    - Grouping: The overall grouping/category name that would characterize the type of irregularity\n",
    "    \n",
    "    Also Note: [r_qc_cols] specified in section 1.1. lists columns from the relevant proGres table needed to construct the summary tables needed for the PowerBi dashboard\n",
    "'''\n",
    "\n",
    "\n",
    "def fp_coa_match_w_current():\n",
    "    a_dftemp = fp_address[(fp_address.progres_addresstypename == \"Country or territory of asylum - current\")&\n",
    "          (fp_address.progres_countryidname !=  fp_address.progres_countryofasylumidname)][r_qc_cols]\n",
    "    name_of_irr =  \"COA for focal point does not match current COA in address record\"\n",
    "    a_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual records of focal points merged with address table on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"COA name mismatch between individual table and address table\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"address issue\"\n",
    "    \n",
    "    return a_dftemp, temp_indicators\n",
    "\n",
    "def fp_coa_1_match_current():\n",
    "    a_dftemp = fp_address[(fp_address.progres_addresstypename == \"Country or territory of asylum - current\")&\n",
    "                        (fp_address.progres_coalocationlevel1name != fp_address.progres_locationlevel1idname)&\n",
    "                        (~fp_address.progres_locationlevel1idname.isna())&\n",
    "                        (fp_address.progres_locationlevel1idname != '-')][r_qc_cols]\n",
    "    name_of_irr =  \"COA Level 1 for focal point does not match current COA Level 1 in address table\"\n",
    "    a_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual records of focal points merged with address table on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"COA progres_locationlevel1idname mismatch between individual table and address table\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"address issue\"\n",
    "    \n",
    "    return a_dftemp, temp_indicators\n",
    "\n",
    "def fp_current_cor():\n",
    "    a_dftemp = fp_address[fp_address.progres_addresstypename == \"Country of Residence - Current\"][r_qc_cols]\n",
    "    name_of_irr =  \"Focal Point with address type Country of Residence - Current\"\n",
    "    a_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual records of focal points merged with address table on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"COA progres_locationlevel1idname mismatch between individual table and address table\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"address issue\"\n",
    "    \n",
    "    return a_dftemp, temp_indicators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of function names corresponding to irregularities we want to check for:\n",
    "add_indicators_functions = [fp_coa_match_w_current,\n",
    "                            # fp_coo_match_w_recent,\n",
    "                            # fp_coa_1_official,\n",
    "                            fp_coa_1_match_current,\n",
    "                            # fp_coo_1_match_recent,\n",
    "                            fp_current_cor\n",
    "                           ]    \n",
    "# Loop through list of functions and save outputs in df table and indicators_checked table: \n",
    "adf = pd.DataFrame()\n",
    "for funcs in add_indicators_functions :\n",
    "    func_returns = funcs()\n",
    "    \n",
    "    a_dftemp = func_returns[0]\n",
    "    adf = pd.concat([adf, a_dftemp])\n",
    "    \n",
    "    temp_indicators = func_returns[1]\n",
    "    indicators_checked = pd.concat([indicators_checked, temp_indicators])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "adf.Irregularity.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "adf[~adf.duplicated(keep=\"first\") == True].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "adf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "adf[adf.duplicated(keep=False) == True].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### To find cases with this Irregularity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"COO for focal point does not match current COO in address record\"\n",
    "irregularity_table = adf # df\n",
    "search_table = dfr # dfi\n",
    "id_needed = \"progres_registrationgroupid\" # \"progres_individualid\"\n",
    "\n",
    "issues = search_table[search_table[id_needed].isin(irregularity_table[irregularity_table.Irregularity == irregularity_name][id_needed].tolist())]\n",
    "issues.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"Focal Point with address type Country of Residence - Current\"\n",
    "irregularity_table = adf # df\n",
    "search_table = dfr # dfi\n",
    "id_needed = \"progres_registrationgroupid\" # \"progres_individualid\"\n",
    "\n",
    "issues = search_table[search_table[id_needed].isin(irregularity_table[irregularity_table.Irregularity == irregularity_name][id_needed].tolist())]\n",
    "issues.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfi[dfi.progres_individualid == \"AC7E814A-BB9A-EC11-811F-00155D5CC4A0\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfa[dfa.progres_individualid == \"AC7E814A-BB9A-EC11-811F-00155D5CC4A0\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## 2.4. Document Inconsistencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of ID documents we will check for\n",
    "# Some operations use vaccination card / book or birth book as form of ID, but these were not found in the list of documents\n",
    "list_of_id_docs = [\n",
    "                 'Birth notification (no event)',\n",
    "                 'Birth certificate (no event)',\n",
    "                 'National passport (no event)',\n",
    "                 'National identity card (no event)',\n",
    "                 'Family book'\n",
    "                 # 'Identity document for asylum-seeker (issued by UNHCR)',\n",
    "                 # 'Refugee identity document (no event)',\n",
    "                 # 'Identity document for refugee (issued by UNHCR)',\n",
    "                 # 'Other identity document (no event)',\n",
    "                 # 'ID Card',\n",
    "                 # 'Refugee certificate (no event)',\n",
    "                 # 'Identity document for asylum-seeker (issued by government)',\n",
    "                 # 'UNHCR ID card (no event)',\n",
    "                 # 'Identity document for refugee (issued by government)',\n",
    "                 # 'Camp identity card (no event)',\n",
    "                 # 'Identity document for stateless person (issued by government)'\n",
    "                                  ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of documents we will check for\n",
    "documents_of_interest = [\"Asylum seeker certificate (no event)\", \n",
    "                         \"Proof of Registration\",\n",
    "                         \"Refugee certificate (no event)\",\n",
    "                         # \"Refugee certificate issuance event\",\n",
    "                         \"Identity document for asylum-seeker (issued by UNHCR)\", \n",
    "                         \"Identity document for refugee (issued by UNHCR)\",\n",
    "                         \"UNHCR ID card (no event)\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep documents of interes and filter out entries that are not \"Valid\"\n",
    "dfd_interest = dfd[(dfd.progres_documenttypeidname.isin(documents_of_interest))&\n",
    "                  (dfd.progres_documentstatusname  == \"Valid\")]\n",
    "\n",
    "dfd_interest.progres_documenttypeidname.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfd_interest.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ind_with_multiple_unique_documents = dfd_interest.groupby([\"progres_individualid\", \n",
    "                                                        \"progres_documenttypeidname\"])\\\n",
    "                                         .progres_documentstatus.count().reset_index()\\\n",
    "                                         .sort_values(by=\"progres_documentstatus\", ascending=False)\n",
    "ind_with_multiple_unique_documents.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfd_interest[dfd_interest.progres_individualid == \"4BC58859-F6E8-EB11-812E-001DD8B71FE3\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "    Each of the functions below takes as \n",
    "    input: pandas DataFrame (relevant table from proGres needed to check for given inconsistency)\n",
    "    and \n",
    "    outputs: \n",
    "    (1) d_dftemp (pandas DataFrame) that identifies individual ids (progres_individualid) associated with records that have the relevant irregularity (Irregularity)\n",
    "    (2) temp_indicators, which lists the indicator name along with additional information about the indicator, such as:\n",
    "    - proGres table: string which describes the proGres table(s) from which records were searched for the relevant irregularity \n",
    "    - Criteria: string which describes the criteria applied to filter records for this irregularlity\n",
    "    - Grouping: The overall grouping/category name that would characterize the type of irregularity\n",
    "    \n",
    "    Also Note: [qc_cols] specified in section 1.1. lists columns from the relevant proGres table needed to construct the summary tables needed for the PowerBi dashboard\n",
    "'''\n",
    "\n",
    "def child_wo_id_birth():\n",
    "    id_doc = pd.merge(dfi[dfi.progres_age<=5], dfd[dfd.progres_documenttypeidname.isin(list_of_id_docs)],  how=\"left\", on=\"progres_individualid\")\n",
    "    infants_without_id = list(set(id_doc[id_doc.progres_documenttypeidname.isna()].progres_individualid.tolist()))\n",
    "    d_dftemp = dfi[dfi.progres_individualid.isin(infants_without_id)][qc_cols]\n",
    "    name_of_irr = \"Child(<=5 years old) without ID/birth document\"\n",
    "    d_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table filtered for children merged with document table on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"Child (progres_age<5) missing progres_documenttypeidname of types: birth notification, birth certificate, passport, identity card or family book\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"document issue\"\n",
    "    \n",
    "    return d_dftemp, temp_indicators\n",
    "\n",
    "def ind_missing_docs():\n",
    "    # join individual table to documents of interest\n",
    "    ind_wo_docs = pd.merge(dfi, dfd_interest, how=\"left\", on=\"progres_individualid\")\n",
    "    # individual records with no match found in the df with documents of interest\n",
    "    ind_wo_docs = ind_wo_docs[ind_wo_docs.progres_documenttypeidname.isna()]\n",
    "    d_dftemp = dfi[dfi.progres_individualid.isin(ind_wo_docs.progres_individualid.tolist())][qc_cols]\n",
    "    name_of_irr =  \"Individual without UNHCR-issued documents\"\n",
    "    d_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with document table (filtered for UNHCR documents) on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"individuals missing progres_documenttypeidname issued by UNHCR (see documents_of_interest in script for full list)\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"document issue\"\n",
    "    \n",
    "    return d_dftemp, temp_indicators\n",
    "    \n",
    "def ind_2_docs():\n",
    "    # use ind_with_multiple_documents below if we want to check for individuals with multiple documents\n",
    "    ind_with_multiple_documents = dfd_interest\\\n",
    "                                    .progres_individualid\\\n",
    "                                    .value_counts()\\\n",
    "                                    .reset_index()\n",
    "    ind_with_multiple_documents = ind_with_multiple_documents[ind_with_multiple_documents.progres_individualid>1][\"index\"].tolist()\n",
    "    d_dftemp = dfi[dfi.progres_individualid.isin(ind_with_multiple_documents)][qc_cols]\n",
    "    \n",
    "    # # use ind_with_multiple_unique_documents below if instead we want to check for individuals with multiple documents of a particular type / the same type\n",
    "    # ind_with_multiple_unique_documents = dfd_interest.groupby([\"progres_individualid\", \n",
    "    #                                                     \"progres_documenttypeidname\"])\\\n",
    "    #                                      .progres_documentstatus.count().reset_index()\\\n",
    "    #                                      .sort_values(by=\"progres_documentstatus\", ascending=False)\n",
    "    # ind_with_multiple_unique_documents = ind_with_multiple_unique_documents[ind_with_multiple_unique_documents.progres_documentstatus>1]\\\n",
    "    #                                     .progres_individualid.unique().tolist()\n",
    "    \n",
    "    d_dftemp = dfi[dfi.progres_individualid.isin(ind_with_multiple_unique_documents)][qc_cols]\n",
    "    name_of_irr =  \"Individual has two or more active and valid UNHCR-issued documents\"\n",
    "    d_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with document table (filtered for UNHCR documents) on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"individuals with multiple valid documents issued by UNHCR (see documents_of_interest in script for full list)\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"document issue\"\n",
    "    \n",
    "    return d_dftemp, temp_indicators\n",
    "\n",
    "def doc_issue_date():\n",
    "    d_dftemp = dfi[dfi.progres_individualid.isin(list\n",
    "                                                 (set(dfd_interest[dfd_interest.progres_dateofissue.isna()]\\\n",
    "                                                      .progres_individualid.tolist())))][qc_cols]\n",
    "    name_of_irr =  \"Individual has UNHCR document with no issue date\"\n",
    "    d_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with document table (filtered for UNHCR documents) on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"individuals with UNHCR document where progres_dateofissue is null\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"document issue\"\n",
    "    \n",
    "    return d_dftemp, temp_indicators\n",
    "\n",
    "def doc_expiry_date():\n",
    "    d_dftemp = dfi[dfi.progres_individualid.isin(list\n",
    "                                                 (set(dfd_interest[dfd_interest.progres_dateofexpiry.isna()]\\\n",
    "                                                      .progres_individualid.tolist())))][qc_cols]\n",
    "    name_of_irr = \"Individual has UNHCR document with no expiry date\"\n",
    "    d_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with document table (filtered for UNHCR documents) on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"individuals with UNHCR document where progres_dateofexpiry is null\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"document issue\"\n",
    "    \n",
    "    return d_dftemp, temp_indicators\n",
    "\n",
    "def doc_issued_future():\n",
    "    d_dftemp = dfi[dfi.progres_individualid.isin(list\n",
    "                                                 (set(dfd_interest[dfd_interest.progres_dateofissue > date_extracted]\\\n",
    "                                                      .progres_individualid.tolist())))][qc_cols]\n",
    "    name_of_irr =  \"Individual has UNHCR document with issue date in the future\"\n",
    "    d_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with document table (filtered for UNHCR documents) on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"individuals with UNHCR document where progres_dateofissue is later than date extracted\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"document issue\"\n",
    "    \n",
    "    return d_dftemp, temp_indicators\n",
    "\n",
    "def doc_exp_issue_date():\n",
    "    d_dftemp = dfi[dfi.progres_individualid.isin(list\n",
    "                                                 (set(dfd_interest[dfd_interest.progres_dateofissue > dfd_interest.progres_dateofexpiry]\\\n",
    "                                                      .progres_individualid.tolist())))][qc_cols]\n",
    "    name_of_irr =  \"Individual has UNHCR document with expiry date prior to issue date\"\n",
    "    d_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with document table (filtered for UNHCR documents) on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"individuals with UNHCR document where progres_dateofexpiry is earlier than progres_dateofissue\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"document issue\"\n",
    "    \n",
    "    return d_dftemp, temp_indicators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of function names corresponding to irregularities we want to check for:\n",
    "doc_indicators_functions = [child_wo_id_birth,\n",
    "                            ind_missing_docs,\n",
    "                            ind_2_docs,\n",
    "                            doc_issue_date,\n",
    "                            doc_expiry_date,\n",
    "                            doc_issued_future,\n",
    "                            doc_exp_issue_date\n",
    "                           ]    \n",
    "# Loop through list of functions and save outputs in df table and indicators_checked table: \n",
    "ddf = pd.DataFrame()\n",
    "for funcs in doc_indicators_functions :\n",
    "    func_returns = funcs()\n",
    "    \n",
    "    d_dftemp = func_returns[0]\n",
    "    ddf = pd.concat([ddf, d_dftemp])\n",
    "    \n",
    "    temp_indicators = func_returns[1]\n",
    "    indicators_checked = pd.concat([indicators_checked, temp_indicators])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ddf.Irregularity.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ddf[~ddf.duplicated(keep=\"first\") == True].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ddf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ddf[ddf.duplicated(keep=False) == True].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### To find cases with this Irregularity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"Individual without UNHCR-issued documents\"\n",
    "irregularity_table = ddf # df\n",
    "search_table = dfi\n",
    "id_needed = \"progres_individualid\"\n",
    "\n",
    "issues = search_table[search_table[id_needed].isin(irregularity_table[irregularity_table.Irregularity == irregularity_name][id_needed].tolist())]\n",
    "issues.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"Child(<=5 years old) without ID/birth document\"\n",
    "irregularity_table = ddf # df\n",
    "search_table = dfi\n",
    "id_needed = \"progres_individualid\"\n",
    "\n",
    "issues = search_table[search_table[id_needed].isin(irregularity_table[irregularity_table.Irregularity == irregularity_name][id_needed].tolist())]\n",
    "# issues[issues.progres_arrivaldate<issues.progres_dateofbirth][[\"progres_arrivaldate\", \"progres_dateofbirth\"]]\n",
    "issues.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfd[dfd.progres_individualid == \"AD49E28E-E1E9-EB11-812E-001DD8B71FE3\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 2.5. Specific Needs Inconsistencies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### All Specific Needs: Input table needed for Specific Needs Crosstab in PowerBi:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# inner join because not all listed spns had respective entries in the individual table\n",
    "all_sn = pd.merge(dfs, dfi, on=\"progres_individualid\", how=\"inner\")\n",
    "all_sn = all_sn[~all_sn.duplicated([\"progres_individualid\",\"progres_spncategory2name\"],keep=\"first\")]\n",
    "all_sn[\"Irregularity\"] = all_sn[\"progres_spncategory2name\"]\n",
    "all_sn[\"SpecificNeeds\"] = True\n",
    "\n",
    "spn_input_for_crosstab = all_sn.groupby([\"progres_spncategory2name\",\n",
    "                                        \"progres_agecohortname\",\n",
    "                                         \"progres_sexname\"\n",
    "                                        ],\n",
    "                                       dropna = False).progres_individualid.count().reset_index()\n",
    "\n",
    "all_sn = all_sn[qc_cols + [\"progres_spncategory2name\",\n",
    "                          \"progres_agecohortname\",\n",
    "                          \"progres_sexname\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is what the visual would look like in PowerBi\n",
    "pd.crosstab(all_sn[\"progres_spncategory2name\"],\n",
    "            [all_sn[\"progres_agecohortname\"], all_sn[\"progres_sexname\"]], \n",
    "            margins=True, \n",
    "            margins_name = \"Total\",\n",
    "           normalize=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create input tables to be used for specific needs table quality checks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this table merges the individual table with specific needs table \n",
    "# so specific needs can be cross-checked againt demographic characteristics\n",
    "df_i_s = pd.merge(dfi, dfs, on=\"progres_individualid\", how=\"outer\")\n",
    "\n",
    "# Extract year and month of birth from progres_dateofbirth field\n",
    "df_i_s[\"yob\"] = df_i_s[\"progres_dateofbirth\"].apply(lambda x: x.year)\n",
    "df_i_s[\"mob\"] = df_i_s[\"progres_dateofbirth\"].apply(lambda x: x.month)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SPN Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "    Each of the functions below takes as \n",
    "    input: pandas DataFrame (relevant table from proGres needed to check for given inconsistency)\n",
    "    and \n",
    "    outputs: \n",
    "    (1) sp_dftemp (pandas DataFrame) that identifies individual ids (progres_individualid) associated with records that have the relevant irregularity (Irregularity)\n",
    "    (2) temp_indicators, which lists the indicator name along with additional information about the indicator, such as:\n",
    "    - proGres table: string which describes the proGres table(s) from which records were searched for the relevant irregularity \n",
    "    - Criteria: string which describes the criteria applied to filter records for this irregularlity\n",
    "    - Grouping: The overall grouping/category name that would characterize the type of irregularity\n",
    "    \n",
    "    Also Note: [qc_cols] specified in section 1.1. lists columns from the relevant proGres table needed to construct the summary tables needed for the PowerBi dashboard\n",
    "'''\n",
    "\n",
    "def child_marital_status():\n",
    "    sp_dftemp = df_i_s[(df_i_s.progres_age < 10)&\n",
    "          (df_i_s.progres_maritalstatusname.isin(['Married',\n",
    "                                                     # 'Divorced',\n",
    "                                                     # 'Widowed',\n",
    "                                                     # 'Separated',\n",
    "                                                     'Partnership',\n",
    "                                                     'Common Law Married']))&\n",
    "          ((df_i_s.progres_spncategory2name != \"Child at risk\")|\n",
    "           (df_i_s.progres_spncategory2name.isna()))][qc_cols]\n",
    "    name_of_irr = 'Child (<10 years old) with Marital Status Name and No Specific Needs'\n",
    "    sp_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with specific needs table on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"individual with age <10 and marital status (married, partnership, or common law married) without the Child at Risk tag\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"specific needs issue\"\n",
    "    \n",
    "    return sp_dftemp, temp_indicators\n",
    "\n",
    "def older_person():\n",
    "    sp_dftemp = df_i_s[(df_i_s.progres_spncategory2name == 'Older person at risk')&\n",
    "       (df_i_s.progres_age < 60)][qc_cols]\n",
    "    name_of_irr = \"Older person at risk who is younger than 60\"\n",
    "    sp_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with specific needs table on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"Older person at risk tag for individual with progres_age<60\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"specific needs issue\"\n",
    "    \n",
    "    return sp_dftemp, temp_indicators\n",
    "\n",
    "def male_woman_risk():\n",
    "    sp_dftemp = df_i_s[(df_i_s.progres_spncategory2name == 'Woman at risk')&\n",
    "           (df_i_s.progres_sexname == \"Male\")][qc_cols]\n",
    "    name_of_irr = \"Male individual with Woman At Risk specific need\"\n",
    "    sp_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with specific needs table on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"Woman at risk tag for individual with progres_sex=Male\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"specific needs issue\"\n",
    "    \n",
    "    return sp_dftemp, temp_indicators\n",
    "\n",
    "def child_single_parent():\n",
    "    sp_dftemp = df_i_s[(df_i_s.progres_spncategory2name == 'Single parent')&\n",
    "                   (df_i_s.progres_age<=11)][qc_cols]\n",
    "    name_of_irr = \"Child (age<=11) tagged as Single Parent\"\n",
    "    sp_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with specific needs table on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"Single parent tag for child of age <=11\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"specific needs issue\"\n",
    "    \n",
    "    return sp_dftemp, temp_indicators\n",
    "\n",
    "def child_risk_18():\n",
    "    sp_dftemp = df_i_s[(df_i_s.progres_age>=18)&\n",
    "                       (df_i_s.progres_spncategory2name == \"Child at risk\")][qc_cols]\n",
    "    name_of_irr =  \"Child at risk older than or equal to age 18\"\n",
    "    sp_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with specific needs table on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"Child at risk tag for individual with progres_age>=18\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"specific needs issue\"\n",
    "    \n",
    "    return sp_dftemp, temp_indicators\n",
    "\n",
    "def usc_18_age():\n",
    "    sp_dftemp = df_i_s[(df_i_s.progres_spncategory2name == \"Unaccompanied or separated child\")&\n",
    "                       (df_i_s.progres_age>=18)][qc_cols]\n",
    "    name_of_irr = \"Unaccompanied or separated child tag for individual age 18 or above\"\n",
    "    sp_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with specific needs table on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"Unaccompanied or separated child tag for individual with progres_age>=18\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"specific needs issue\"\n",
    "    \n",
    "    return sp_dftemp, temp_indicators\n",
    "\n",
    "def sgbv_child():\n",
    "    sp_dftemp = df_i_s[(df_i_s.progres_spncategory2name == \"SGBV\")&\n",
    "                       (df_i_s.progres_age<=4)][qc_cols]\n",
    "    name_of_irr =  \"SGBV case for child age <4\"\n",
    "    sp_dftemp[\"Irregularity\"] = name_of_irr\n",
    "    \n",
    "    temp_indicators.loc[1,\"Irregularity\"] = name_of_irr\n",
    "    temp_indicators.loc[1,\"proGres_table\"] = \"individual table merged with specific needs table on progres_individualid\"\n",
    "    temp_indicators.loc[1,\"Criteria\"] = \"SBGV tag for individual with progres_age<=4\"\n",
    "    temp_indicators.loc[1,\"Grouping\"] = \"specific needs issue\"\n",
    "    \n",
    "    return sp_dftemp, temp_indicators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of function names corresponding to irregularities we want to check for:\n",
    "spn_indicators_functions = [child_marital_status,\n",
    "                            older_person,\n",
    "                            male_woman_risk,\n",
    "                            child_single_parent,\n",
    "                            child_risk_18,\n",
    "                            usc_18_age,\n",
    "                            sgbv_child\n",
    "                            ]\n",
    "\n",
    "# Loop through list of functions and save outputs in df table and indicators_checked table: \n",
    "sdf = pd.DataFrame()\n",
    "for funcs in spn_indicators_functions :\n",
    "    func_returns = funcs()\n",
    "    \n",
    "    sp_dftemp = func_returns[0] \n",
    "    sdf = pd.concat([sdf, sp_dftemp])\n",
    "    \n",
    "    temp_indicators = func_returns[1]\n",
    "    indicators_checked = pd.concat([indicators_checked, temp_indicators])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf.Irregularity.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### To find cases with this Irregularity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"Child (age<=11) tagged as Single Parent\"\n",
    "irregularity_table = sdf\n",
    "search_table = dfi\n",
    "id_needed = \"progres_individualid\"\n",
    "\n",
    "search_table[search_table[id_needed].isin(irregularity_table[irregularity_table.Irregularity == irregularity_name][id_needed].tolist())].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove Duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sdf[~sdf.duplicated(keep=\"first\")].shape\n",
    "\n",
    "# sdf[sdf.duplicated(keep=\"first\")].shape\n",
    "\n",
    "# sdf[~sdf.duplicated(keep=False)].shape\n",
    "\n",
    "# sdf[sdf.duplicated(keep=False)].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf[~sdf.duplicated(keep=\"first\")].Irregularity.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf[sdf.duplicated(keep=\"first\")].Irregularity.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf[(sdf.duplicated(keep=False))&\n",
    "   (sdf.Irregularity == \"Older person at risk who is younger than 60\")].groupby(\"progres_individualid\").Irregularity.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can also remove it for idf in the next step\n",
    "sdf = sdf[~sdf.duplicated(keep=\"first\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf.Irregularity.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Section 3.: Combine Irregularities from the Individual and Registration Group levels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 3.1 Combine all Individual-level inconsistencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine table of id and irregularity for ind, doc, and specific needs\n",
    "# Registration group and address irregularities (i.e. rdf and adf) will be combined later\n",
    "# Since they do not have fields relating to the individual\n",
    "\n",
    "# Combine irregularities found at the individual level\n",
    "idf = pd.concat([df, \n",
    "                 ddf, \n",
    "                 sdf\n",
    "                ])\n",
    "\n",
    "# check for / remove any duplicates\n",
    "idf = idf[~idf.duplicated(keep=\"first\")]\n",
    "\n",
    "# append additional columns with demographic characteristics of the individual that we need for the summary table\n",
    "idf = pd.merge(idf, dfi[qc_cols_append], how=\"left\", on=\"progres_individualid\")\n",
    "\n",
    "####  Re-format data in fields needed for the summary table\n",
    "idf.loc[:,\"QC_Group\"] = \"Individual\"\n",
    "idf.loc[:,\"GUID\"] = idf[\"progres_individualid\"]\n",
    "idf.loc[:,\"ID_Number\"] = idf[\"progres_id\"]\n",
    "idf.loc[:, \"Source_DQ\"] = np.where( ( (idf[\"createdbyname\"] == \"proGres v4 Migration Account 01\") | \n",
    "                                      (idf[\"createdbyname\"] == \"sa-pro4-PROD-Azure Service Account\" ) \n",
    "                                     # |\n",
    "                                     #  (idf[\"modifiedbyname\"] == \"proGres v4 Migration Account 01\") |\n",
    "                                     #  (idf[\"modifiedbyname\"] == \"proGres Production\")\n",
    "                                    ) , \"Migration\", \"User\")\n",
    "\n",
    "idf.loc[:,\"COO\"] = idf[\"progres_countryoforiginidname\"]\n",
    "idf[\"YearCreated\"] = idf[\"createdon\"].apply(lambda x: x.year)\n",
    "idf[\"YearModified\"] = idf[\"modifiedon\"].apply(lambda x: x.year)\n",
    "idf[\"YearArrival\"] = idf[\"progres_arrivaldate\"].apply(lambda x: x.year)\n",
    "idf[\"YearRegistered\"] = idf[\"progres_registrationdate\"].apply(lambda x: x.year)\n",
    "idf[\"COA\"] = idf[\"progres_businessunitname\"].apply(lambda x: x.split('-')[0][:-1])\n",
    "idf[\"COA\"] = idf[\"COA\"].apply(lambda x: \"GCC except UAE\" if x == \"RO Riyad\" else x)\n",
    "idf[\"YearOfBirth\"] = idf[\"progres_dateofbirth\"].apply(lambda x: x.year)\n",
    "idf[\"SpecificNeeds\"] = None\n",
    "\n",
    "# clean up df\n",
    "idf = idf.reset_index()\n",
    "idf = idf.drop(columns=[\"index\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.2 Combine all Registration Group inconsistencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check\n",
    "rdf[~rdf.duplicated(keep=\"first\")].shape == rdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine all case-level irregularities- i.e. those related to registration (rdf) and addresses (adf)\n",
    "rdf = pd.concat([rdf, adf])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# append additional columns with demographic characteristics of the individual that we need for the summary table\n",
    "rdf = pd.merge(rdf, dfr_fp[r_qc_cols_append], how=\"left\", on=\"progres_registrationgroupid\")\n",
    "\n",
    "####  Re-format data in fields needed for the summary table\n",
    "rdf = rdf.rename(columns={\"progres_countryoforiginidname\" : \"COO\"})\n",
    "rdf[\"COA\"] = rdf[\"progres_businessunitname\"].apply(lambda x: x.split('-')[0][:-1])\n",
    "rdf[\"COA\"] = rdf[\"COA\"].apply(lambda x: \"GCC except UAE\" if x == \"RO Riyad\" else x)\n",
    "rdf.loc[:, \"Source_DQ\"] = np.where( ( (rdf[\"createdbyname\"] == \"proGres v4 Migration Account 01\") | \n",
    "                                      (rdf[\"createdbyname\"] == \"sa-pro4-PROD-Azure Service Account\" )\n",
    "                                    ) , \"Migration\", \"User\")\n",
    "rdf[\"YearCreated\"] = rdf[\"createdon\"].apply(lambda x: x.year)\n",
    "rdf[\"YearModified\"] = rdf[\"modifiedon\"].apply(lambda x: x.year)\n",
    "rdf[\"YearRegistered\"] = rdf[\"progres_registrationdate\"].apply(lambda x: x.year)\n",
    "rdf[\"QC_Group\"] = \"RegistrationGroup\"\n",
    "rdf[\"GUID\"] = rdf[\"progres_registrationgroupid\"]\n",
    "rdf[\"ID_Number\"] = rdf[\"progres_registrationgroupbusinessid\"]\n",
    "rdf.reset_index(inplace=True)\n",
    "rdf = rdf.drop(columns=[\"index\"])\n",
    "# remove duplicates\n",
    "rdf = rdf[~rdf.duplicated(keep=\"first\")]\n",
    "rdf.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.3 Combine individual and case-level tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1) Combine table with ids and their associated irregularity descriptions from section 3.1 and 3.2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final = pd.concat([idf, rdf])\n",
    "final.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true
   },
   "source": [
    "##  3.4 Get List of Indicators Checked"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "indicators_list = indicators_checked.copy()\n",
    "indicators_list[\"DateExtracted\"] = str(date_extracted)\n",
    "indicators_list[\"ScriptVersion\"] = script_version\n",
    "indicators_list.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "indicators_list.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Section 4: Save to db in server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SERVER = ip_of_server\n",
    "DATABASE = 'QualityCheck_Egypt'\n",
    "DRIVER = 'SQL Server'\n",
    "USERNAME = uap.split(',')[0]\n",
    "PASSWORD = uap.split(',')[1]\n",
    "DATABASE_CONNECTION = f'mssql://{USERNAME}:{PASSWORD}@{SERVER}/{DATABASE}?driver={DRIVER}'\n",
    "\n",
    "engine = sqlalchemy.create_engine(DATABASE_CONNECTION)\n",
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## write the DataFrame to a table in the sql database\n",
    "final.to_sql(\"Egypt_Data_Quality_IDs_w_Irregularity_found_for_ID\", \n",
    "                engine, \n",
    "                # schema=\"dbo\",\n",
    "                if_exists='replace', # or append\n",
    "                index=False\n",
    "               )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "indicators_list.to_sql(\"Egypt_Indicators_List_without_counts\", \n",
    "                engine, \n",
    "                # schema=\"dbo\",\n",
    "                if_exists='replace', # or append\n",
    "                index=False\n",
    "               )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Section 5: To find cases with a particular Irregularity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Individuals with QC issus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "deets = pd.read_sql_query(\"SELECT * FROM dbo.Egypt_Data_Quality_IDs_w_Irregularity_found_for_ID\",\n",
    "                                  db_connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"Individual with Date of Birth after Registration Date\"\n",
    "cols_of_interest_for_crosschecking = [\"progres_id\", \"progres_indvidualid\", \"progres_dateofbirth\", \"progres_registrationdate\"]\n",
    "search_table = dfi\n",
    "id_needed = \"progres_individualid\"\n",
    "irregularity_table = deets\n",
    "\n",
    "def get_record(irregularity_name, cols_of_interest_for_crosschecking, irregularity_table):\n",
    "    ind_w_issue = irregularity_table.copy()\n",
    "    ind_w_issue[\"issue\"] = ind_w_issue.apply(lambda x: 1 if irregularity_name in x['Irregularity_List'] else 0, axis=1)\n",
    "    ind_w_issue = ind_w_issue[ind_w_issue.issue==1].GUID.tolist()\n",
    "    issues = search_table[search_table[id_needed].isin(ind_w_issue)]\n",
    "    return issues[cols_of_interest_for_crosschecking].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "irregularity_name = \"Active Registration Group without active focal point\"\n",
    "cols_of_interest_for_crosschecking = [\"progres_registrationgroupbusinessid\", \"progres_individualid\"]\n",
    "search_table = dfr_fp\n",
    "id_needed = \"progres_registrationgroupid\"\n",
    "irregularity_table = deets\n",
    "\n",
    "def get_record(irregularity_name, cols_of_interest_for_crosschecking, irregularity_table):\n",
    "    ind_w_issue = irregularity_table.copy()\n",
    "    ind_w_issue[\"issue\"] = ind_w_issue.apply(lambda x: 1 if irregularity_name in x['Irregularity_List'] else 0, axis=1)\n",
    "    ind_w_issue = ind_w_issue[ind_w_issue.issue==1].GUID.tolist()\n",
    "    issues = search_table[search_table[id_needed].isin(ind_w_issue)]\n",
    "    return issues[cols_of_interest_for_crosschecking].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_record(irregularity_name, cols_of_interest_for_crosschecking, irregularity_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "# Section 6: APPENDIX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overall figures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "idf[idf.SpecificNeeds.isna()].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "indicators_list.shape"
   ]
  }
 ],
 "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.9.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}