01_Check_For_Survey_Updates.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identify updated / new DHS surveys\n",
    "\n",
    "Compare what's available on the website to what we have in the database already. \n",
    "\n",
    "Any missing may be new, or we may not have access to them, or we may simply have chosen not to use them.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pandas.io.sql as psql\n",
    "#import psycopg2 as pg\n",
    "from sqlalchemy import create_engine\n",
    "import sqlalchemy as sa\n",
    "import os\n",
    "import glob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('config/pg_conn.txt') as conn_details:\n",
    "    conn_str_psyco = conn_details.readline()\n",
    "    conn_str_sqlalchemy = conn_details.readline()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(conn_str_sqlalchemy)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We maintain a CSV of known issues to explain discrepancies between the listed survey availability and our actual holdings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>surveynum</th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>issue</th>\n",
       "      <th>data issue</th>\n",
       "      <th>gps issue</th>\n",
       "      <th>change expected</th>\n",
       "      <th>last manual check</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23</td>\n",
       "      <td>botswana</td>\n",
       "      <td>1988</td>\n",
       "      <td>NaN</td>\n",
       "      <td>restricted data</td>\n",
       "      <td>not collected</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>73</td>\n",
       "      <td>eritrea</td>\n",
       "      <td>1995</td>\n",
       "      <td>NaN</td>\n",
       "      <td>restricted data</td>\n",
       "      <td>not collected</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>yemen</td>\n",
       "      <td>1997</td>\n",
       "      <td>NaN</td>\n",
       "      <td>restricted data</td>\n",
       "      <td>not collected</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>179</td>\n",
       "      <td>turkmenistan</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>not in public domain</td>\n",
       "      <td>not collected</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>160</td>\n",
       "      <td>mauritania</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>restricted data</td>\n",
       "      <td>not collected</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   surveynum       country  year issue            data issue      gps issue  \\\n",
       "0         23      botswana  1988   NaN       restricted data  not collected   \n",
       "1         73       eritrea  1995   NaN       restricted data  not collected   \n",
       "2        103         yemen  1997   NaN       restricted data  not collected   \n",
       "3        179  turkmenistan  2000   NaN  not in public domain  not collected   \n",
       "4        160    mauritania  2000   NaN       restricted data  not collected   \n",
       "\n",
       "  change expected last manual check  \n",
       "0              no        04/02/2019  \n",
       "1              no        04/02/2019  \n",
       "2              no        04/02/2019  \n",
       "3              no        04/02/2019  \n",
       "4              no        04/02/2019  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "known_issues_all = pd.read_csv('~/notebooks/DHS-DataExtraction/DHS_Preprocess_And_Ingestion/01_Check_Survey_Updates/missing_and_unavailable.csv')\n",
    "known_issues_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Available surveys\n",
    "\n",
    "Query the DHS API for available surveys (may include ones our account does not have download access to)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note: presently this returns all surveys. In the past it has been paginated \n",
    "# so if that was to occur again we'd have to loop\n",
    "surveys = pd.read_csv(\"http://api.dhsprogram.com/rest/dhs/surveys?f=csv\",\n",
    "                      usecols=['SurveyId', 'SurveyNum', 'ReleaseDate', 'SurveyCharacteristicIds', 'CountryName', 'RegionName'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SurveyId</th>\n",
       "      <th>CountryName</th>\n",
       "      <th>SurveyNum</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>ReleaseDate</th>\n",
       "      <th>SurveyCharacteristicIds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AF2015DHS</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>471</td>\n",
       "      <td>South &amp; Southeast Asia</td>\n",
       "      <td>2017-02-15</td>\n",
       "      <td>92, 39, 6, 1, 101, 79, 31, 14, 57, 8, 34, 17, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL2008DHS</td>\n",
       "      <td>Albania</td>\n",
       "      <td>327</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2010-03-15</td>\n",
       "      <td>33, 10, 43, 41, 31, 22, 26, 24, 21, 59, 11, 34...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL2017DHS</td>\n",
       "      <td>Albania</td>\n",
       "      <td>525</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2019-01-11</td>\n",
       "      <td>70, 93, 33, 41, 10, 43, 11, 34, 5, 60, 67, 31,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AO2006MIS</td>\n",
       "      <td>Angola</td>\n",
       "      <td>282</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2008-02-27</td>\n",
       "      <td>8, 41, 57, 36, 68, 63, 61, 26, 101, 79, 89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AO2011MIS</td>\n",
       "      <td>Angola</td>\n",
       "      <td>395</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2012-01-24</td>\n",
       "      <td>101, 79, 90, 41, 26, 57, 8, 17, 89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    SurveyId  CountryName  SurveyNum                     RegionName  \\\n",
       "0  AF2015DHS  Afghanistan        471         South & Southeast Asia   \n",
       "1  AL2008DHS      Albania        327  North Africa/West Asia/Europe   \n",
       "2  AL2017DHS      Albania        525  North Africa/West Asia/Europe   \n",
       "3  AO2006MIS       Angola        282             Sub-Saharan Africa   \n",
       "4  AO2011MIS       Angola        395             Sub-Saharan Africa   \n",
       "\n",
       "  ReleaseDate                            SurveyCharacteristicIds  \n",
       "0  2017-02-15  92, 39, 6, 1, 101, 79, 31, 14, 57, 8, 34, 17, ...  \n",
       "1  2010-03-15  33, 10, 43, 41, 31, 22, 26, 24, 21, 59, 11, 34...  \n",
       "2  2019-01-11  70, 93, 33, 41, 10, 43, 11, 34, 5, 60, 67, 31,...  \n",
       "3  2008-02-27         8, 41, 57, 36, 68, 63, 61, 26, 101, 79, 89  \n",
       "4  2012-01-24                 101, 79, 90, 41, 26, 57, 8, 17, 89  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "surveys.head() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Updated surveys\n",
    "\n",
    "Query the DHS API for surveys updated since 2015 (may include ones our account does not have download access to)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "recent_updates = pd.read_csv(\"http://api.dhsprogram.com/rest/dhs/dataupdates?lastUpdate=20150901&f=csv\",\n",
    "                            usecols=['SurveyId', 'SurveyYear', 'DHS_CountryCode', 'CountryName', 'UpdateDate'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SurveyId</th>\n",
       "      <th>SurveyYear</th>\n",
       "      <th>DHS_CountryCode</th>\n",
       "      <th>CountryName</th>\n",
       "      <th>UpdateDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TD2014DHS</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>TD</td>\n",
       "      <td>Chad</td>\n",
       "      <td>2016-07-15 19:17:14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ZW2010DHS</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>ZW</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2016-12-07 20:54:25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GH2016MIS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>GH</td>\n",
       "      <td>Ghana</td>\n",
       "      <td>2017-07-27 17:52:17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ET2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>ET</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>2017-08-16 21:17:18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AM2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>AM</td>\n",
       "      <td>Armenia</td>\n",
       "      <td>2017-08-30 17:46:51.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    SurveyId  SurveyYear DHS_CountryCode CountryName             UpdateDate\n",
       "0  TD2014DHS      2014.0              TD        Chad  2016-07-15 19:17:14.0\n",
       "1  ZW2010DHS      2010.0              ZW    Zimbabwe  2016-12-07 20:54:25.0\n",
       "2  GH2016MIS      2016.0              GH       Ghana  2017-07-27 17:52:17.0\n",
       "3  ET2016DHS      2016.0              ET    Ethiopia  2017-08-16 21:17:18.0\n",
       "4  AM2016DHS      2016.0              AM     Armenia  2017-08-30 17:46:51.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recent_updates.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The recent updates API endpoint doesn't return all details we need, so merge with the data from the main surveys endpoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SurveyId</th>\n",
       "      <th>SurveyYear</th>\n",
       "      <th>DHS_CountryCode</th>\n",
       "      <th>CountryName_x</th>\n",
       "      <th>UpdateDate</th>\n",
       "      <th>CountryName_y</th>\n",
       "      <th>SurveyNum</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>ReleaseDate</th>\n",
       "      <th>SurveyCharacteristicIds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TD2014DHS</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>TD</td>\n",
       "      <td>Chad</td>\n",
       "      <td>2016-07-15 19:17:14.0</td>\n",
       "      <td>Chad</td>\n",
       "      <td>465</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2016-07-11</td>\n",
       "      <td>10, 33, 67, 32, 39, 34, 5, 6, 4, 1, 101, 104, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ZW2010DHS</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>ZW</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2016-12-07 20:54:25.0</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>367</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2012-04-05</td>\n",
       "      <td>33, 17, 3, 79, 69, 24, 21, 23, 41, 10, 31, 22,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GH2016MIS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>GH</td>\n",
       "      <td>Ghana</td>\n",
       "      <td>2017-07-27 17:52:17.0</td>\n",
       "      <td>Ghana</td>\n",
       "      <td>516</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2017-07-27</td>\n",
       "      <td>89, 92, 41, 70, 15, 57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ET2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>ET</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>2017-08-16 21:17:18.0</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>478</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2017-08-15</td>\n",
       "      <td>91, 33, 15, 41, 10, 31, 22, 39, 26, 69, 24, 21...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AM2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>AM</td>\n",
       "      <td>Armenia</td>\n",
       "      <td>2017-08-30 17:46:51.0</td>\n",
       "      <td>Armenia</td>\n",
       "      <td>492</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2017-08-30</td>\n",
       "      <td>16, 33, 31, 22, 26, 38, 69, 24, 21, 17, 87, 94...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AM2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>AM</td>\n",
       "      <td>Armenia</td>\n",
       "      <td>2017-10-05 21:50:48.0</td>\n",
       "      <td>Armenia</td>\n",
       "      <td>492</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2017-08-30</td>\n",
       "      <td>16, 33, 31, 22, 26, 38, 69, 24, 21, 17, 87, 94...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SN2015DHS</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>SN</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>2017-10-05 21:50:48.0</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>489</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2016-11-04</td>\n",
       "      <td>90, 79, 34, 14, 4, 31, 32, 69, 15, 26, 57, 41,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SN2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>SN</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>2017-10-05 21:50:48.0</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>524</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>80, 26, 69, 14, 11, 34, 4, 15, 70, 22, 57, 79,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SL2016MIS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>SL</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>2017-10-05 21:50:48.0</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>515</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>41, 90, 89, 79, 70, 26, 57, 92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NP2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>NP</td>\n",
       "      <td>Nepal</td>\n",
       "      <td>2017-11-13 15:29:15.0</td>\n",
       "      <td>Nepal</td>\n",
       "      <td>472</td>\n",
       "      <td>South &amp; Southeast Asia</td>\n",
       "      <td>2017-11-13</td>\n",
       "      <td>6, 31, 14, 20, 17, 34, 36, 13, 9, 60, 3, 22, 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>MZ2015AIS</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>MZ</td>\n",
       "      <td>Mozambique</td>\n",
       "      <td>2018-02-26 04:32:38.0</td>\n",
       "      <td>Mozambique</td>\n",
       "      <td>467</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2018-02-26</td>\n",
       "      <td>24, 81, 83, 91, 41, 47, 11, 51, 6, 92, 101, 89...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>UG2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>UG</td>\n",
       "      <td>Uganda</td>\n",
       "      <td>2018-03-12 03:12:13.0</td>\n",
       "      <td>Uganda</td>\n",
       "      <td>504</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2018-03-12</td>\n",
       "      <td>70, 24, 21, 11, 6, 4, 1, 10, 52, 31, 22, 67, 7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>BU2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>BU</td>\n",
       "      <td>Burundi</td>\n",
       "      <td>2018-04-07 00:41:44.0</td>\n",
       "      <td>Burundi</td>\n",
       "      <td>463</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>26, 45, 80, 86, 91, 94, 70, 67, 5, 1, 33, 32, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>TL2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>TL</td>\n",
       "      <td>Timor-Leste</td>\n",
       "      <td>2018-04-11 21:15:54.0</td>\n",
       "      <td>Timor-Leste</td>\n",
       "      <td>514</td>\n",
       "      <td>South &amp; Southeast Asia</td>\n",
       "      <td>2018-04-10</td>\n",
       "      <td>33, 68, 79, 63, 80, 32, 24, 21, 14, 59, 20, 34...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>MW2017MIS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>MW</td>\n",
       "      <td>Malawi</td>\n",
       "      <td>2018-04-25 17:31:14.0</td>\n",
       "      <td>Malawi</td>\n",
       "      <td>512</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2018-04-25</td>\n",
       "      <td>41, 70, 92, 90, 89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>TG2017MIS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>TG</td>\n",
       "      <td>Togo</td>\n",
       "      <td>2018-08-08 15:31:54.0</td>\n",
       "      <td>Togo</td>\n",
       "      <td>497</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>70, 41, 90, 89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>HT2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>HT</td>\n",
       "      <td>Haiti</td>\n",
       "      <td>2018-08-21 17:39:29.0</td>\n",
       "      <td>Haiti</td>\n",
       "      <td>503</td>\n",
       "      <td>Latin America &amp; Caribbean</td>\n",
       "      <td>2018-08-21</td>\n",
       "      <td>85, 24, 21, 91, 16, 41, 10, 43, 70, 67, 11, 6,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>RW2017MIS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>RW</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>2018-09-10 20:38:33.0</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>540</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2018-09-07</td>\n",
       "      <td>89, 90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>SN2017DHS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>SN</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>2018-09-27 17:32:30.0</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>534</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2018-09-28</td>\n",
       "      <td>10, 22, 23, 57, 15, 41, 31, 70, 79, 80, 26, 69...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>SN2017DHS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>SN</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>2018-09-27 17:33:30.0</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>534</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2018-09-28</td>\n",
       "      <td>10, 22, 23, 57, 15, 41, 31, 70, 79, 80, 26, 69...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>PH2017DHS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>PH</td>\n",
       "      <td>Philippines</td>\n",
       "      <td>2018-10-03 20:22:23.0</td>\n",
       "      <td>Philippines</td>\n",
       "      <td>510</td>\n",
       "      <td>South &amp; Southeast Asia</td>\n",
       "      <td>2018-10-03</td>\n",
       "      <td>24, 69, 33, 38, 86, 30, 60, 21, 92, 87, 70, 6, 94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>TZ2017MIS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>TZ</td>\n",
       "      <td>Tanzania</td>\n",
       "      <td>2018-10-22 20:34:21.0</td>\n",
       "      <td>Tanzania</td>\n",
       "      <td>529</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2018-10-22</td>\n",
       "      <td>70, 26, 92, 89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AL2017DHS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>AL</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2019-01-11 23:07:49.0</td>\n",
       "      <td>Albania</td>\n",
       "      <td>525</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2019-01-11</td>\n",
       "      <td>70, 93, 33, 41, 10, 43, 11, 34, 5, 60, 67, 31,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>ZA2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>ZA</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>2019-01-30 18:59:38.0</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>390</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2019-01-30</td>\n",
       "      <td>26, 91, 22, 79, 16, 23, 11, 41, 10, 43, 68, 67...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>PK2017DHS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>PK</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>2019-02-25 23:42:49.0</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>523</td>\n",
       "      <td>South &amp; Southeast Asia</td>\n",
       "      <td>2019-02-25</td>\n",
       "      <td>30, 16, 10, 78, 11, 6, 31, 85, 92, 26, 60, 2, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>MV2016DHS</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>MV</td>\n",
       "      <td>Maldives</td>\n",
       "      <td>2019-03-01 16:08:45.0</td>\n",
       "      <td>Maldives</td>\n",
       "      <td>494</td>\n",
       "      <td>South &amp; Southeast Asia</td>\n",
       "      <td>2019-02-25</td>\n",
       "      <td>86, 11, 22, 70, 32, 38, 6, 4, 80, 41, 10, 94, 87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>JO2017DHS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>JO</td>\n",
       "      <td>Jordan</td>\n",
       "      <td>2019-03-26 16:01:30.0</td>\n",
       "      <td>Jordan</td>\n",
       "      <td>500</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2019-03-26</td>\n",
       "      <td>22, 11, 41, 10, 70, 67, 80, 38, 21, 6, 87, 16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>BJ2017DHS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>BJ</td>\n",
       "      <td>Benin</td>\n",
       "      <td>2019-04-02 15:29:46.0</td>\n",
       "      <td>Benin</td>\n",
       "      <td>491</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>6, 1, 67, 80, 90, 89, 86, 5, 11, 15, 41, 10, 4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>TR2008DHS</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>TR</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>2019-06-30 21:56:54.0</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>547</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2018-07-30</td>\n",
       "      <td>16, 10, 31, 69, 14, 34, 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>TR2013DHS</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>TR</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>2019-06-30 21:56:54.0</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>486</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2018-07-30</td>\n",
       "      <td>10, 22, 3, 101, 69, 16, 31, 32, 38, 20, 34, 60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>BF2017MIS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>BF</td>\n",
       "      <td>Burkina Faso</td>\n",
       "      <td>2019-07-11 15:10:31.0</td>\n",
       "      <td>Burkina Faso</td>\n",
       "      <td>526</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2019-07-08</td>\n",
       "      <td>41, 90, 89, 101, 92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>MZ2018MIS</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>MZ</td>\n",
       "      <td>Mozambique</td>\n",
       "      <td>2019-07-21 22:13:23.0</td>\n",
       "      <td>Mozambique</td>\n",
       "      <td>527</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2019-07-19</td>\n",
       "      <td>41, 70, 89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>ML2018DHS</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>ML</td>\n",
       "      <td>Mali</td>\n",
       "      <td>2019-09-19 14:31:56.0</td>\n",
       "      <td>Mali</td>\n",
       "      <td>517</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2019-09-19</td>\n",
       "      <td>22, 85, 39, 11, 6, 4, 1, 41, 10, 70, 89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>GN2018DHS</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>GN</td>\n",
       "      <td>Guinea</td>\n",
       "      <td>2019-10-31 02:27:13.0</td>\n",
       "      <td>Guinea</td>\n",
       "      <td>539</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2019-10-30</td>\n",
       "      <td>70, 39, 26, 38, 24, 21, 23, 11, 4, 41, 10, 67,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>PG2017DHS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>PG</td>\n",
       "      <td>Papua New Guinea</td>\n",
       "      <td>2019-11-25 15:05:35.0</td>\n",
       "      <td>Papua New Guinea</td>\n",
       "      <td>499</td>\n",
       "      <td>Oceania</td>\n",
       "      <td>2019-11-25</td>\n",
       "      <td>10, 78, 11, 6, 1, 74, 31, 22, 79, 69, 24, 21, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>ZM2018DHS</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>ZM</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>2020-02-18 16:59:21.0</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>542</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-02-18</td>\n",
       "      <td>41, 10, 39, 23, 14, 6, 1, 59, 92, 26, 100, 17,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>UG2018MIS</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>UG</td>\n",
       "      <td>Uganda</td>\n",
       "      <td>2020-04-23 12:50:28.0</td>\n",
       "      <td>Uganda</td>\n",
       "      <td>549</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-04-23</td>\n",
       "      <td>41, 90, 92, 70, 89, 79, 26, 57, 17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>CM2018DHS</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>CM</td>\n",
       "      <td>Cameroon</td>\n",
       "      <td>2020-06-10 14:40:05.0</td>\n",
       "      <td>Cameroon</td>\n",
       "      <td>511</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-06-10</td>\n",
       "      <td>23, 16, 100, 70, 89, 11, 26, 68, 91, 104, 33, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>SN2018DHS</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>SN</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>2020-06-17 16:08:04.0</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>580</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-06-17</td>\n",
       "      <td>31, 17, 3, 80, 26, 11, 10, 14, 6, 4, 32, 69, 8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>SN2018DHS</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>SN</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>2020-06-17 16:31:16.0</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>580</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-06-17</td>\n",
       "      <td>31, 17, 3, 80, 26, 11, 10, 14, 6, 4, 32, 69, 8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>GH2019MIS</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>GH</td>\n",
       "      <td>Ghana</td>\n",
       "      <td>2020-07-28 13:00:49.0</td>\n",
       "      <td>Ghana</td>\n",
       "      <td>557</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-07-28</td>\n",
       "      <td>90, 17, 26, 70, 89, 92, 41, 79, 69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>SL2019DHS</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>SL</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>2020-12-11 16:01:50.0</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>545</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-12-11</td>\n",
       "      <td>11, 41, 23, 70, 104, 1, 6, 4, 10, 89, 99, 26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>BD2017DHS</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>BD</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>2020-12-14 14:38:12.0</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>536</td>\n",
       "      <td>South &amp; Southeast Asia</td>\n",
       "      <td>2020-12-14</td>\n",
       "      <td>38, 22, 70, 92, 31, 10, 43, 46, 9, 2, 41, 68, ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     SurveyId  SurveyYear DHS_CountryCode     CountryName_x  \\\n",
       "0   TD2014DHS      2014.0              TD              Chad   \n",
       "1   ZW2010DHS      2010.0              ZW          Zimbabwe   \n",
       "2   GH2016MIS      2016.0              GH             Ghana   \n",
       "3   ET2016DHS      2016.0              ET          Ethiopia   \n",
       "4   AM2016DHS      2016.0              AM           Armenia   \n",
       "5   AM2016DHS      2016.0              AM           Armenia   \n",
       "6   SN2015DHS      2015.0              SN           Senegal   \n",
       "7   SN2016DHS      2016.0              SN           Senegal   \n",
       "8   SL2016MIS      2016.0              SL      Sierra Leone   \n",
       "9   NP2016DHS      2016.0              NP             Nepal   \n",
       "10  MZ2015AIS      2015.0              MZ        Mozambique   \n",
       "11  UG2016DHS      2016.0              UG            Uganda   \n",
       "12  BU2016DHS      2016.0              BU           Burundi   \n",
       "13  TL2016DHS      2016.0              TL       Timor-Leste   \n",
       "14  MW2017MIS      2017.0              MW            Malawi   \n",
       "15  TG2017MIS      2017.0              TG              Togo   \n",
       "16  HT2016DHS      2016.0              HT             Haiti   \n",
       "17  RW2017MIS      2017.0              RW            Rwanda   \n",
       "18  SN2017DHS      2017.0              SN           Senegal   \n",
       "19  SN2017DHS      2017.0              SN           Senegal   \n",
       "20  PH2017DHS      2017.0              PH       Philippines   \n",
       "21  TZ2017MIS      2017.0              TZ          Tanzania   \n",
       "22  AL2017DHS      2017.0              AL           Albania   \n",
       "23  ZA2016DHS      2016.0              ZA      South Africa   \n",
       "24  PK2017DHS      2017.0              PK          Pakistan   \n",
       "25  MV2016DHS      2016.0              MV          Maldives   \n",
       "26  JO2017DHS      2017.0              JO            Jordan   \n",
       "27  BJ2017DHS      2017.0              BJ             Benin   \n",
       "28  TR2008DHS      2008.0              TR            Turkey   \n",
       "29  TR2013DHS      2013.0              TR            Turkey   \n",
       "30  BF2017MIS      2017.0              BF      Burkina Faso   \n",
       "31  MZ2018MIS      2018.0              MZ        Mozambique   \n",
       "32  ML2018DHS      2018.0              ML              Mali   \n",
       "33  GN2018DHS      2018.0              GN            Guinea   \n",
       "34  PG2017DHS      2017.0              PG  Papua New Guinea   \n",
       "35  ZM2018DHS      2018.0              ZM            Zambia   \n",
       "36  UG2018MIS      2018.0              UG            Uganda   \n",
       "37  CM2018DHS      2018.0              CM          Cameroon   \n",
       "38  SN2018DHS      2018.0              SN           Senegal   \n",
       "39  SN2018DHS      2018.0              SN           Senegal   \n",
       "40  GH2019MIS      2019.0              GH             Ghana   \n",
       "41  SL2019DHS      2019.0              SL      Sierra Leone   \n",
       "42  BD2017DHS      2017.0              BD        Bangladesh   \n",
       "\n",
       "               UpdateDate     CountryName_y  SurveyNum  \\\n",
       "0   2016-07-15 19:17:14.0              Chad        465   \n",
       "1   2016-12-07 20:54:25.0          Zimbabwe        367   \n",
       "2   2017-07-27 17:52:17.0             Ghana        516   \n",
       "3   2017-08-16 21:17:18.0          Ethiopia        478   \n",
       "4   2017-08-30 17:46:51.0           Armenia        492   \n",
       "5   2017-10-05 21:50:48.0           Armenia        492   \n",
       "6   2017-10-05 21:50:48.0           Senegal        489   \n",
       "7   2017-10-05 21:50:48.0           Senegal        524   \n",
       "8   2017-10-05 21:50:48.0      Sierra Leone        515   \n",
       "9   2017-11-13 15:29:15.0             Nepal        472   \n",
       "10  2018-02-26 04:32:38.0        Mozambique        467   \n",
       "11  2018-03-12 03:12:13.0            Uganda        504   \n",
       "12  2018-04-07 00:41:44.0           Burundi        463   \n",
       "13  2018-04-11 21:15:54.0       Timor-Leste        514   \n",
       "14  2018-04-25 17:31:14.0            Malawi        512   \n",
       "15  2018-08-08 15:31:54.0              Togo        497   \n",
       "16  2018-08-21 17:39:29.0             Haiti        503   \n",
       "17  2018-09-10 20:38:33.0            Rwanda        540   \n",
       "18  2018-09-27 17:32:30.0           Senegal        534   \n",
       "19  2018-09-27 17:33:30.0           Senegal        534   \n",
       "20  2018-10-03 20:22:23.0       Philippines        510   \n",
       "21  2018-10-22 20:34:21.0          Tanzania        529   \n",
       "22  2019-01-11 23:07:49.0           Albania        525   \n",
       "23  2019-01-30 18:59:38.0      South Africa        390   \n",
       "24  2019-02-25 23:42:49.0          Pakistan        523   \n",
       "25  2019-03-01 16:08:45.0          Maldives        494   \n",
       "26  2019-03-26 16:01:30.0            Jordan        500   \n",
       "27  2019-04-02 15:29:46.0             Benin        491   \n",
       "28  2019-06-30 21:56:54.0            Turkey        547   \n",
       "29  2019-06-30 21:56:54.0            Turkey        486   \n",
       "30  2019-07-11 15:10:31.0      Burkina Faso        526   \n",
       "31  2019-07-21 22:13:23.0        Mozambique        527   \n",
       "32  2019-09-19 14:31:56.0              Mali        517   \n",
       "33  2019-10-31 02:27:13.0            Guinea        539   \n",
       "34  2019-11-25 15:05:35.0  Papua New Guinea        499   \n",
       "35  2020-02-18 16:59:21.0            Zambia        542   \n",
       "36  2020-04-23 12:50:28.0            Uganda        549   \n",
       "37  2020-06-10 14:40:05.0          Cameroon        511   \n",
       "38  2020-06-17 16:08:04.0           Senegal        580   \n",
       "39  2020-06-17 16:31:16.0           Senegal        580   \n",
       "40  2020-07-28 13:00:49.0             Ghana        557   \n",
       "41  2020-12-11 16:01:50.0      Sierra Leone        545   \n",
       "42  2020-12-14 14:38:12.0        Bangladesh        536   \n",
       "\n",
       "                       RegionName ReleaseDate  \\\n",
       "0              Sub-Saharan Africa  2016-07-11   \n",
       "1              Sub-Saharan Africa  2012-04-05   \n",
       "2              Sub-Saharan Africa  2017-07-27   \n",
       "3              Sub-Saharan Africa  2017-08-15   \n",
       "4   North Africa/West Asia/Europe  2017-08-30   \n",
       "5   North Africa/West Asia/Europe  2017-08-30   \n",
       "6              Sub-Saharan Africa  2016-11-04   \n",
       "7              Sub-Saharan Africa  2017-09-06   \n",
       "8              Sub-Saharan Africa  2017-09-21   \n",
       "9          South & Southeast Asia  2017-11-13   \n",
       "10             Sub-Saharan Africa  2018-02-26   \n",
       "11             Sub-Saharan Africa  2018-03-12   \n",
       "12             Sub-Saharan Africa  2018-04-05   \n",
       "13         South & Southeast Asia  2018-04-10   \n",
       "14             Sub-Saharan Africa  2018-04-25   \n",
       "15             Sub-Saharan Africa  2018-08-08   \n",
       "16      Latin America & Caribbean  2018-08-21   \n",
       "17             Sub-Saharan Africa  2018-09-07   \n",
       "18             Sub-Saharan Africa  2018-09-28   \n",
       "19             Sub-Saharan Africa  2018-09-28   \n",
       "20         South & Southeast Asia  2018-10-03   \n",
       "21             Sub-Saharan Africa  2018-10-22   \n",
       "22  North Africa/West Asia/Europe  2019-01-11   \n",
       "23             Sub-Saharan Africa  2019-01-30   \n",
       "24         South & Southeast Asia  2019-02-25   \n",
       "25         South & Southeast Asia  2019-02-25   \n",
       "26  North Africa/West Asia/Europe  2019-03-26   \n",
       "27             Sub-Saharan Africa  2019-04-02   \n",
       "28  North Africa/West Asia/Europe  2018-07-30   \n",
       "29  North Africa/West Asia/Europe  2018-07-30   \n",
       "30             Sub-Saharan Africa  2019-07-08   \n",
       "31             Sub-Saharan Africa  2019-07-19   \n",
       "32             Sub-Saharan Africa  2019-09-19   \n",
       "33             Sub-Saharan Africa  2019-10-30   \n",
       "34                        Oceania  2019-11-25   \n",
       "35             Sub-Saharan Africa  2020-02-18   \n",
       "36             Sub-Saharan Africa  2020-04-23   \n",
       "37             Sub-Saharan Africa  2020-06-10   \n",
       "38             Sub-Saharan Africa  2020-06-17   \n",
       "39             Sub-Saharan Africa  2020-06-17   \n",
       "40             Sub-Saharan Africa  2020-07-28   \n",
       "41             Sub-Saharan Africa  2020-12-11   \n",
       "42         South & Southeast Asia  2020-12-14   \n",
       "\n",
       "                              SurveyCharacteristicIds  \n",
       "0   10, 33, 67, 32, 39, 34, 5, 6, 4, 1, 101, 104, ...  \n",
       "1   33, 17, 3, 79, 69, 24, 21, 23, 41, 10, 31, 22,...  \n",
       "2                              89, 92, 41, 70, 15, 57  \n",
       "3   91, 33, 15, 41, 10, 31, 22, 39, 26, 69, 24, 21...  \n",
       "4   16, 33, 31, 22, 26, 38, 69, 24, 21, 17, 87, 94...  \n",
       "5   16, 33, 31, 22, 26, 38, 69, 24, 21, 17, 87, 94...  \n",
       "6   90, 79, 34, 14, 4, 31, 32, 69, 15, 26, 57, 41,...  \n",
       "7   80, 26, 69, 14, 11, 34, 4, 15, 70, 22, 57, 79,...  \n",
       "8                      41, 90, 89, 79, 70, 26, 57, 92  \n",
       "9   6, 31, 14, 20, 17, 34, 36, 13, 9, 60, 3, 22, 3...  \n",
       "10  24, 81, 83, 91, 41, 47, 11, 51, 6, 92, 101, 89...  \n",
       "11  70, 24, 21, 11, 6, 4, 1, 10, 52, 31, 22, 67, 7...  \n",
       "12  26, 45, 80, 86, 91, 94, 70, 67, 5, 1, 33, 32, ...  \n",
       "13  33, 68, 79, 63, 80, 32, 24, 21, 14, 59, 20, 34...  \n",
       "14                                 41, 70, 92, 90, 89  \n",
       "15                                     70, 41, 90, 89  \n",
       "16  85, 24, 21, 91, 16, 41, 10, 43, 70, 67, 11, 6,...  \n",
       "17                                             89, 90  \n",
       "18  10, 22, 23, 57, 15, 41, 31, 70, 79, 80, 26, 69...  \n",
       "19  10, 22, 23, 57, 15, 41, 31, 70, 79, 80, 26, 69...  \n",
       "20  24, 69, 33, 38, 86, 30, 60, 21, 92, 87, 70, 6, 94  \n",
       "21                                     70, 26, 92, 89  \n",
       "22  70, 93, 33, 41, 10, 43, 11, 34, 5, 60, 67, 31,...  \n",
       "23  26, 91, 22, 79, 16, 23, 11, 41, 10, 43, 68, 67...  \n",
       "24  30, 16, 10, 78, 11, 6, 31, 85, 92, 26, 60, 2, ...  \n",
       "25   86, 11, 22, 70, 32, 38, 6, 4, 80, 41, 10, 94, 87  \n",
       "26      22, 11, 41, 10, 70, 67, 80, 38, 21, 6, 87, 16  \n",
       "27  6, 1, 67, 80, 90, 89, 86, 5, 11, 15, 41, 10, 4...  \n",
       "28                          16, 10, 31, 69, 14, 34, 3  \n",
       "29     10, 22, 3, 101, 69, 16, 31, 32, 38, 20, 34, 60  \n",
       "30                                41, 90, 89, 101, 92  \n",
       "31                                         41, 70, 89  \n",
       "32            22, 85, 39, 11, 6, 4, 1, 41, 10, 70, 89  \n",
       "33  70, 39, 26, 38, 24, 21, 23, 11, 4, 41, 10, 67,...  \n",
       "34  10, 78, 11, 6, 1, 74, 31, 22, 79, 69, 24, 21, ...  \n",
       "35  41, 10, 39, 23, 14, 6, 1, 59, 92, 26, 100, 17,...  \n",
       "36                 41, 90, 92, 70, 89, 79, 26, 57, 17  \n",
       "37  23, 16, 100, 70, 89, 11, 26, 68, 91, 104, 33, ...  \n",
       "38  31, 17, 3, 80, 26, 11, 10, 14, 6, 4, 32, 69, 8...  \n",
       "39  31, 17, 3, 80, 26, 11, 10, 14, 6, 4, 32, 69, 8...  \n",
       "40                 90, 17, 26, 70, 89, 92, 41, 79, 69  \n",
       "41       11, 41, 23, 70, 104, 1, 6, 4, 10, 89, 99, 26  \n",
       "42  38, 22, 70, 92, 31, 10, 43, 46, 9, 2, 41, 68, ...  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "potential_recent_updates = recent_updates.merge(surveys, how='inner', on='SurveyId')\n",
    "potential_recent_updates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Our surveys\n",
    "\n",
    "Define a survey as being in our database if it has an entry in any of the RECH1, REC01, MREC01 tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_surveys = pd.read_sql('''\n",
    "    SELECT DISTINCT (surveyid) FROM dhs_data_tables.\"RECH1\" \n",
    "    UNION \n",
    "    SELECT DISTINCT (surveyid) FROM dhs_data_tables.\"REC01\"\n",
    "    UNION \n",
    "    SELECT DISTINCT (surveyid) FROM dhs_data_tables.\"MREC01\"''',\n",
    "                         con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>surveyid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  surveyid\n",
       "0      354\n",
       "1      497\n",
       "2       13\n",
       "3      383\n",
       "4       93"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "our_surveys.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing surveys\n",
    "\n",
    "List of all surveys that the API advertised but aren't in our DB for whatever reason"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "surveys_we_dont_have = surveys[~surveys['SurveyNum'].isin(our_surveys['surveyid'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SurveyId</th>\n",
       "      <th>CountryName</th>\n",
       "      <th>SurveyNum</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>ReleaseDate</th>\n",
       "      <th>SurveyCharacteristicIds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>BD2017DHS</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>536</td>\n",
       "      <td>South &amp; Southeast Asia</td>\n",
       "      <td>2020-12-14</td>\n",
       "      <td>38, 22, 70, 92, 31, 10, 43, 46, 9, 2, 41, 68, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>BT1988DHS</td>\n",
       "      <td>Botswana</td>\n",
       "      <td>23</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>1989-08-01</td>\n",
       "      <td>79, 3, 21, 20, 17, 101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>ER1995DHS</td>\n",
       "      <td>Eritrea</td>\n",
       "      <td>73</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>1997-03-01</td>\n",
       "      <td>1, 2, 4, 10, 11, 21, 24, 79, 101, 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>ER2002DHS</td>\n",
       "      <td>Eritrea</td>\n",
       "      <td>170</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2003-05-01</td>\n",
       "      <td>4, 13, 57, 17, 10, 14, 79, 101, 5, 3, 60, 20, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>GH2019MIS</td>\n",
       "      <td>Ghana</td>\n",
       "      <td>557</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-07-28</td>\n",
       "      <td>90, 17, 26, 70, 89, 92, 41, 79, 69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>MR2000DHS</td>\n",
       "      <td>Mauritania</td>\n",
       "      <td>160</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2001-12-01</td>\n",
       "      <td>1, 2, 4, 11, 21, 24, 57, 14, 32, 79, 101, 10, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>PG2017DHS</td>\n",
       "      <td>Papua New Guinea</td>\n",
       "      <td>499</td>\n",
       "      <td>Oceania</td>\n",
       "      <td>2019-11-25</td>\n",
       "      <td>10, 78, 11, 6, 1, 74, 31, 22, 79, 69, 24, 21, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>SN2018DHS</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>580</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-06-17</td>\n",
       "      <td>31, 17, 3, 80, 26, 11, 10, 14, 6, 4, 32, 69, 8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>SL2019DHS</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>545</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-12-11</td>\n",
       "      <td>11, 41, 23, 70, 104, 1, 6, 4, 10, 89, 99, 26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>TR2008DHS</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>547</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2018-07-30</td>\n",
       "      <td>16, 10, 31, 69, 14, 34, 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>TR2013DHS</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>486</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2018-07-30</td>\n",
       "      <td>10, 22, 3, 101, 69, 16, 31, 32, 38, 20, 34, 60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>TM2000DHS</td>\n",
       "      <td>Turkmenistan</td>\n",
       "      <td>179</td>\n",
       "      <td>Central Asia</td>\n",
       "      <td>2001-09-01</td>\n",
       "      <td>16, 22, 36, 41, 43, 21, 15, 10, 14, 20, 13, 10...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>UG2004AIS</td>\n",
       "      <td>Uganda</td>\n",
       "      <td>224</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2006-03-01</td>\n",
       "      <td>24, 31, 53, 54, 59, 36, 47, 40, 21, 11, 101, 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>318</th>\n",
       "      <td>YE1997DHS</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>103</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>1998-11-01</td>\n",
       "      <td>1, 4, 14, 20, 17, 101, 34, 61, 3, 10, 79, 94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      SurveyId       CountryName  SurveyNum                     RegionName  \\\n",
       "18   BD2017DHS        Bangladesh        536         South & Southeast Asia   \n",
       "29   BT1988DHS          Botswana         23             Sub-Saharan Africa   \n",
       "90   ER1995DHS           Eritrea         73             Sub-Saharan Africa   \n",
       "91   ER2002DHS           Eritrea        170             Sub-Saharan Africa   \n",
       "107  GH2019MIS             Ghana        557             Sub-Saharan Africa   \n",
       "187  MR2000DHS        Mauritania        160             Sub-Saharan Africa   \n",
       "226  PG2017DHS  Papua New Guinea        499                        Oceania   \n",
       "265  SN2018DHS           Senegal        580             Sub-Saharan Africa   \n",
       "269  SL2019DHS      Sierra Leone        545             Sub-Saharan Africa   \n",
       "298  TR2008DHS            Turkey        547  North Africa/West Asia/Europe   \n",
       "299  TR2013DHS            Turkey        486  North Africa/West Asia/Europe   \n",
       "300  TM2000DHS      Turkmenistan        179                   Central Asia   \n",
       "304  UG2004AIS            Uganda        224             Sub-Saharan Africa   \n",
       "318  YE1997DHS             Yemen        103  North Africa/West Asia/Europe   \n",
       "\n",
       "    ReleaseDate                            SurveyCharacteristicIds  \n",
       "18   2020-12-14  38, 22, 70, 92, 31, 10, 43, 46, 9, 2, 41, 68, ...  \n",
       "29   1989-08-01                             79, 3, 21, 20, 17, 101  \n",
       "90   1997-03-01                1, 2, 4, 10, 11, 21, 24, 79, 101, 3  \n",
       "91   2003-05-01  4, 13, 57, 17, 10, 14, 79, 101, 5, 3, 60, 20, ...  \n",
       "107  2020-07-28                 90, 17, 26, 70, 89, 92, 41, 79, 69  \n",
       "187  2001-12-01  1, 2, 4, 11, 21, 24, 57, 14, 32, 79, 101, 10, ...  \n",
       "226  2019-11-25  10, 78, 11, 6, 1, 74, 31, 22, 79, 69, 24, 21, ...  \n",
       "265  2020-06-17  31, 17, 3, 80, 26, 11, 10, 14, 6, 4, 32, 69, 8...  \n",
       "269  2020-12-11       11, 41, 23, 70, 104, 1, 6, 4, 10, 89, 99, 26  \n",
       "298  2018-07-30                          16, 10, 31, 69, 14, 34, 3  \n",
       "299  2018-07-30     10, 22, 3, 101, 69, 16, 31, 32, 38, 20, 34, 60  \n",
       "300  2001-09-01  16, 22, 36, 41, 43, 21, 15, 10, 14, 20, 13, 10...  \n",
       "304  2006-03-01  24, 31, 53, 54, 59, 36, 47, 40, 21, 11, 101, 1...  \n",
       "318  1998-11-01       1, 4, 14, 20, 17, 101, 34, 61, 3, 10, 79, 94  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "surveys_we_dont_have"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List of surveys for which we've previously noted that we'll never get the data due to known issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>surveynum</th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>issue</th>\n",
       "      <th>data issue</th>\n",
       "      <th>gps issue</th>\n",
       "      <th>change expected</th>\n",
       "      <th>last manual check</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23</td>\n",
       "      <td>botswana</td>\n",
       "      <td>1988</td>\n",
       "      <td>NaN</td>\n",
       "      <td>restricted data</td>\n",
       "      <td>not collected</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>73</td>\n",
       "      <td>eritrea</td>\n",
       "      <td>1995</td>\n",
       "      <td>NaN</td>\n",
       "      <td>restricted data</td>\n",
       "      <td>not collected</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>yemen</td>\n",
       "      <td>1997</td>\n",
       "      <td>NaN</td>\n",
       "      <td>restricted data</td>\n",
       "      <td>not collected</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>179</td>\n",
       "      <td>turkmenistan</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>not in public domain</td>\n",
       "      <td>not collected</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>160</td>\n",
       "      <td>mauritania</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>restricted data</td>\n",
       "      <td>not collected</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>170</td>\n",
       "      <td>eritrea</td>\n",
       "      <td>2002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>restricted data</td>\n",
       "      <td>restricted data</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>224</td>\n",
       "      <td>uganda</td>\n",
       "      <td>2004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>restricted data</td>\n",
       "      <td>not collected</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>525</td>\n",
       "      <td>albania</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>we don't care about it</td>\n",
       "      <td>we are not approved for gps</td>\n",
       "      <td>no</td>\n",
       "      <td>04/02/2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   surveynum       country  year issue              data issue  \\\n",
       "0         23      botswana  1988   NaN         restricted data   \n",
       "1         73       eritrea  1995   NaN         restricted data   \n",
       "2        103         yemen  1997   NaN         restricted data   \n",
       "3        179  turkmenistan  2000   NaN    not in public domain   \n",
       "4        160    mauritania  2000   NaN         restricted data   \n",
       "5        170       eritrea  2002   NaN         restricted data   \n",
       "6        224        uganda  2004   NaN         restricted data   \n",
       "7        525       albania  2017   NaN  we don't care about it   \n",
       "\n",
       "                     gps issue change expected last manual check  \n",
       "0                not collected              no        04/02/2019  \n",
       "1                not collected              no        04/02/2019  \n",
       "2                not collected              no        04/02/2019  \n",
       "3                not collected              no        04/02/2019  \n",
       "4                not collected              no        04/02/2019  \n",
       "5              restricted data              no        04/02/2019  \n",
       "6                not collected              no        04/02/2019  \n",
       "7  we are not approved for gps              no        04/02/2019  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "known_survey_issues = known_issues_all.loc[\n",
    "    (known_issues_all['change expected']=='no') & (known_issues_all['data issue'] != 'no issue')\n",
    "]\n",
    "known_survey_issues"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Listing of surveys that we don't have and might want to get"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SurveyId</th>\n",
       "      <th>CountryName</th>\n",
       "      <th>SurveyNum</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>ReleaseDate</th>\n",
       "      <th>SurveyCharacteristicIds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>BD2017DHS</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>536</td>\n",
       "      <td>South &amp; Southeast Asia</td>\n",
       "      <td>2020-12-14</td>\n",
       "      <td>38, 22, 70, 92, 31, 10, 43, 46, 9, 2, 41, 68, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>GH2019MIS</td>\n",
       "      <td>Ghana</td>\n",
       "      <td>557</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-07-28</td>\n",
       "      <td>90, 17, 26, 70, 89, 92, 41, 79, 69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>PG2017DHS</td>\n",
       "      <td>Papua New Guinea</td>\n",
       "      <td>499</td>\n",
       "      <td>Oceania</td>\n",
       "      <td>2019-11-25</td>\n",
       "      <td>10, 78, 11, 6, 1, 74, 31, 22, 79, 69, 24, 21, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>SN2018DHS</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>580</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-06-17</td>\n",
       "      <td>31, 17, 3, 80, 26, 11, 10, 14, 6, 4, 32, 69, 8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>SL2019DHS</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>545</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-12-11</td>\n",
       "      <td>11, 41, 23, 70, 104, 1, 6, 4, 10, 89, 99, 26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>TR2008DHS</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>547</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2018-07-30</td>\n",
       "      <td>16, 10, 31, 69, 14, 34, 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>TR2013DHS</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>486</td>\n",
       "      <td>North Africa/West Asia/Europe</td>\n",
       "      <td>2018-07-30</td>\n",
       "      <td>10, 22, 3, 101, 69, 16, 31, 32, 38, 20, 34, 60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      SurveyId       CountryName  SurveyNum                     RegionName  \\\n",
       "18   BD2017DHS        Bangladesh        536         South & Southeast Asia   \n",
       "107  GH2019MIS             Ghana        557             Sub-Saharan Africa   \n",
       "226  PG2017DHS  Papua New Guinea        499                        Oceania   \n",
       "265  SN2018DHS           Senegal        580             Sub-Saharan Africa   \n",
       "269  SL2019DHS      Sierra Leone        545             Sub-Saharan Africa   \n",
       "298  TR2008DHS            Turkey        547  North Africa/West Asia/Europe   \n",
       "299  TR2013DHS            Turkey        486  North Africa/West Asia/Europe   \n",
       "\n",
       "    ReleaseDate                            SurveyCharacteristicIds  \n",
       "18   2020-12-14  38, 22, 70, 92, 31, 10, 43, 46, 9, 2, 41, 68, ...  \n",
       "107  2020-07-28                 90, 17, 26, 70, 89, 92, 41, 79, 69  \n",
       "226  2019-11-25  10, 78, 11, 6, 1, 74, 31, 22, 79, 69, 24, 21, ...  \n",
       "265  2020-06-17  31, 17, 3, 80, 26, 11, 10, 14, 6, 4, 32, 69, 8...  \n",
       "269  2020-12-11       11, 41, 23, 70, 104, 1, 6, 4, 10, 89, 99, 26  \n",
       "298  2018-07-30                          16, 10, 31, 69, 14, 34, 3  \n",
       "299  2018-07-30     10, 22, 3, 101, 69, 16, 31, 32, 38, 20, 34, 60  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_ = ~surveys_we_dont_have['SurveyNum'].isin(known_survey_issues['surveynum'])\n",
    "survey_data_to_look_for = surveys_we_dont_have[_]\n",
    "survey_data_to_look_for"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GPS data\n",
    "\n",
    "Listing of all the surveys we have GPS data for"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "our_gps_data = pd.read_sql('''\n",
    "    SELECT DISTINCT (surveyid) FROM dhs_data_locations.dhs_cluster_locs''',\n",
    "                         con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>surveyid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>384</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   surveyid\n",
       "0       145\n",
       "1       435\n",
       "2        58\n",
       "3       228\n",
       "4       384"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "our_gps_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Listing of all the surveys for which GPS data are available, according to the API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      471\n",
       "1      327\n",
       "3      282\n",
       "4      395\n",
       "5      477\n",
       "      ... \n",
       "325    542\n",
       "328    184\n",
       "329    260\n",
       "330    367\n",
       "331    475\n",
       "Name: SurveyNum, Length: 201, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "available_gps_data = surveys[surveys['SurveyCharacteristicIds'].str.contains('26')]['SurveyNum']\n",
    "available_gps_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Listing of all the GPS data which are advertised as available but we don't have"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      471\n",
       "7      262\n",
       "47     101\n",
       "91     170\n",
       "107    557\n",
       "147    115\n",
       "165    207\n",
       "213    277\n",
       "214    407\n",
       "224    419\n",
       "235    426\n",
       "236    433\n",
       "237    434\n",
       "245    165\n",
       "264    534\n",
       "265    580\n",
       "269    545\n",
       "277     82\n",
       "319    358\n",
       "322    206\n",
       "Name: SurveyNum, dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gps_data_we_dont_have = available_gps_data[~available_gps_data.isin(our_gps_data['surveyid'])]\n",
    "gps_data_we_dont_have"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Listing of surveys for which we've previously noted that we'll never get the GPS data due to known issues "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "jupyter": {
     "outputs_hidden": true
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      23\n",
       "1      73\n",
       "2     103\n",
       "3     179\n",
       "4     160\n",
       "5     170\n",
       "6     224\n",
       "7     525\n",
       "8     101\n",
       "9     115\n",
       "10    165\n",
       "11    206\n",
       "12    207\n",
       "13    262\n",
       "14    277\n",
       "15    358\n",
       "16    407\n",
       "17    419\n",
       "18    426\n",
       "19    433\n",
       "20    434\n",
       "21    471\n",
       "23     82\n",
       "Name: surveynum, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "known_gps_issues = known_issues_all.loc[\n",
    "    (known_issues_all['change expected']=='no') & (known_issues_all['gps issue'] != 'no issue')\n",
    "]['surveynum']\n",
    "known_gps_issues"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Listing of GPS data that we don't have and might want to get"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "107    557\n",
       "264    534\n",
       "265    580\n",
       "269    545\n",
       "Name: SurveyNum, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gps_data_to_look_for = gps_data_we_dont_have[~gps_data_we_dont_have.isin(known_gps_issues)]\n",
    "gps_data_to_look_for"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SurveyId</th>\n",
       "      <th>CountryName</th>\n",
       "      <th>SurveyNum</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>ReleaseDate</th>\n",
       "      <th>SurveyCharacteristicIds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>GH2019MIS</td>\n",
       "      <td>Ghana</td>\n",
       "      <td>557</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-07-28</td>\n",
       "      <td>90, 17, 26, 70, 89, 92, 41, 79, 69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>264</th>\n",
       "      <td>SN2017DHS</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>534</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2018-09-28</td>\n",
       "      <td>10, 22, 23, 57, 15, 41, 31, 70, 79, 80, 26, 69...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>SN2018DHS</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>580</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-06-17</td>\n",
       "      <td>31, 17, 3, 80, 26, 11, 10, 14, 6, 4, 32, 69, 8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>SL2019DHS</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>545</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>2020-12-11</td>\n",
       "      <td>11, 41, 23, 70, 104, 1, 6, 4, 10, 89, 99, 26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      SurveyId   CountryName  SurveyNum          RegionName ReleaseDate  \\\n",
       "107  GH2019MIS         Ghana        557  Sub-Saharan Africa  2020-07-28   \n",
       "264  SN2017DHS       Senegal        534  Sub-Saharan Africa  2018-09-28   \n",
       "265  SN2018DHS       Senegal        580  Sub-Saharan Africa  2020-06-17   \n",
       "269  SL2019DHS  Sierra Leone        545  Sub-Saharan Africa  2020-12-11   \n",
       "\n",
       "                               SurveyCharacteristicIds  \n",
       "107                 90, 17, 26, 70, 89, 92, 41, 79, 69  \n",
       "264  10, 22, 23, 57, 15, 41, 31, 70, 79, 80, 26, 69...  \n",
       "265  31, 17, 3, 80, 26, 11, 10, 14, 6, 4, 32, 69, 8...  \n",
       "269       11, 41, 23, 70, 104, 1, 6, 4, 10, 89, 99, 26  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "surveys[surveys['SurveyNum'].isin(gps_data_to_look_for)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Next steps:\n",
    "\n",
    "Code is no longer provided to automatically download / scrape the data from the DHS website. They took steps to prevent this so we aren't going to try and circumvent it.  \n",
    "\n",
    "Instead, you should use the batch download tool they do provide in conjunction with a browser bulk download utility to get the necessary files.\n",
    "\n",
    "For each survey, you should download the \"Individual Recode - Hierarchical ASCII data (.dat)\" and the \"Men's Recode - Hierarchical ASCII data (.dat)\" files, as well as the Geographic Data where available.\n",
    "\n",
    "For any that turn out not to be available, suggesting a mismatch between the API and reality, update the CSV files if appropriate to note this.\n",
    "\n",
    "Once downloaded, unzip and rename the files using notebook 02."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}