Match.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Matching Notebook Between IQVIA and TGF data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "matched_1st = pd.read_excel('./external/Gambia_css_results.xlsx')\n",
    "matched_1st = matched_1st.set_index('q100')\n",
    "\n",
    "iqvia = pd.read_csv('./output/GMB/tHFA-gambia-analysis_v2.9.csv')\n",
    "iqvia = iqvia.set_index('Q100')\n",
    "\n",
    "file_path = \"./Updated tHFA data/tHFA_GMB.xlsx\"\n",
    "df = pd.read_excel(file_path, header=None)\n",
    "df.columns = df.iloc[0]\n",
    "df = df[1:]\n",
    "df.set_index('Q100', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CSS-3 (CLM) comparison for Gambia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "matched_1st = pd.read_excel('./external/Gambia_css_results.xlsx')\n",
    "matched_1st = matched_1st.set_index('q100')\n",
    "iqvia = pd.read_csv('./output/GMB/tHFA-gambia-analysis_v2.10.csv')\n",
    "iqvia = iqvia.set_index('Q100')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>CLM_score</th>\n",
       "      <th>css_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>GMB_000104</th>\n",
       "      <td>16.666667</td>\n",
       "      <td>16.66667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000110</th>\n",
       "      <td>66.666667</td>\n",
       "      <td>66.66666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000105</th>\n",
       "      <td>16.666667</td>\n",
       "      <td>16.66667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000114</th>\n",
       "      <td>16.666667</td>\n",
       "      <td>16.66667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000100</th>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000025</th>\n",
       "      <td>16.666667</td>\n",
       "      <td>16.66667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000027</th>\n",
       "      <td>16.666667</td>\n",
       "      <td>16.66667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000087</th>\n",
       "      <td>16.666667</td>\n",
       "      <td>16.66667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000134</th>\n",
       "      <td>16.666667</td>\n",
       "      <td>16.66667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000148</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>120 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             CLM_score      css_3\n",
       "GMB_000104   16.666667   16.66667\n",
       "GMB_000110   66.666667   66.66666\n",
       "GMB_000105   16.666667   16.66667\n",
       "GMB_000114   16.666667   16.66667\n",
       "GMB_000100  100.000000  100.00000\n",
       "...                ...        ...\n",
       "GMB_000025   16.666667   16.66667\n",
       "GMB_000027   16.666667   16.66667\n",
       "GMB_000087   16.666667   16.66667\n",
       "GMB_000134   16.666667   16.66667\n",
       "GMB_000148    0.000000    0.00000\n",
       "\n",
       "[120 rows x 2 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "see = pd.merge(left=matched_1st,right=iqvia,right_index=True,left_index=True)\n",
    "look = see[round(see['CLM_score'],1)==round(see['css_3'],1)][['CLM_score','css_3']]\n",
    "look"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['GMB_000119', 'GMB_000117', 'GMB_000051', 'GMB_000092']"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(look.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>tHFA_B021</th>\n",
       "      <th>tHFA_B022</th>\n",
       "      <th>tHFA_B023</th>\n",
       "      <th>tHFA_B024</th>\n",
       "      <th>tHFA_B035</th>\n",
       "      <th>tHFA_B045</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Q100</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>GMB_000119</th>\n",
       "      <td>NEITHER AGREE NOR DISAGREE</td>\n",
       "      <td>AGREE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NEITHER AGREE NOR DISAGREE</td>\n",
       "      <td>STRONGLY AGREE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000117</th>\n",
       "      <td>NEITHER AGREE NOR DISAGREE</td>\n",
       "      <td>AGREE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AGREE</td>\n",
       "      <td>NEITHER AGREE NOR DISAGREE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000051</th>\n",
       "      <td>NEITHER AGREE NOR DISAGREE</td>\n",
       "      <td>STRONGLY AGREE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AGREE</td>\n",
       "      <td>STRONGLY AGREE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GMB_000092</th>\n",
       "      <td>NEITHER AGREE NOR DISAGREE</td>\n",
       "      <td>AGREE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DON’T KNOW</td>\n",
       "      <td>DISAGREE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0                            tHFA_B021       tHFA_B022 tHFA_B023 tHFA_B024  \\\n",
       "Q100                                                                         \n",
       "GMB_000119  NEITHER AGREE NOR DISAGREE           AGREE       NaN       NaN   \n",
       "GMB_000117  NEITHER AGREE NOR DISAGREE           AGREE       NaN       NaN   \n",
       "GMB_000051  NEITHER AGREE NOR DISAGREE  STRONGLY AGREE       NaN       NaN   \n",
       "GMB_000092  NEITHER AGREE NOR DISAGREE           AGREE       NaN       NaN   \n",
       "\n",
       "0                            tHFA_B035                   tHFA_B045  \n",
       "Q100                                                                \n",
       "GMB_000119  NEITHER AGREE NOR DISAGREE              STRONGLY AGREE  \n",
       "GMB_000117                       AGREE  NEITHER AGREE NOR DISAGREE  \n",
       "GMB_000051                       AGREE              STRONGLY AGREE  \n",
       "GMB_000092                  DON’T KNOW                    DISAGREE  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q = ['tHFA_B021', 'tHFA_B022', 'tHFA_B023', 'tHFA_B024', 'tHFA_B035', 'tHFA_B045']\n",
    "\n",
    "df.loc[list(look.index)][q]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Labs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>test-capac</th>\n",
       "      <th>Lab5</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Q100</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>MWI_000193</th>\n",
       "      <td>55.555556</td>\n",
       "      <td>55.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_001282</th>\n",
       "      <td>42.307692</td>\n",
       "      <td>40.740741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000166</th>\n",
       "      <td>37.037037</td>\n",
       "      <td>37.037037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_001312</th>\n",
       "      <td>62.962963</td>\n",
       "      <td>62.962963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_001109</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_001428</th>\n",
       "      <td>55.555556</td>\n",
       "      <td>55.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000122</th>\n",
       "      <td>51.851852</td>\n",
       "      <td>51.851852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000529</th>\n",
       "      <td>85.185185</td>\n",
       "      <td>85.185185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000496</th>\n",
       "      <td>48.148148</td>\n",
       "      <td>48.148148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000273</th>\n",
       "      <td>36.363636</td>\n",
       "      <td>36.363636</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>120 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            test-capac       Lab5\n",
       "Q100                             \n",
       "MWI_000193   55.555556  55.555556\n",
       "MWI_001282   42.307692  40.740741\n",
       "MWI_000166   37.037037  37.037037\n",
       "MWI_001312   62.962963  62.962963\n",
       "MWI_001109         NaN        NaN\n",
       "...                ...        ...\n",
       "MWI_001428   55.555556  55.555556\n",
       "MWI_000122   51.851852  51.851852\n",
       "MWI_000529   85.185185  85.185185\n",
       "MWI_000496   48.148148  48.148148\n",
       "MWI_000273   36.363636  36.363636\n",
       "\n",
       "[120 rows x 2 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lab = pd.read_excel('./external/Lab5-Differences.xlsx',sheet_name=1,index_col=0)\n",
    "lab = lab[['test-capac','Lab5']]\n",
    "lab['Lab5'] = lab['Lab5'] * 100\n",
    "lab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Q102', 'Q105', 'Q106', 'Q105_a', 'Q113', 'Q113_A', 'Q116', 'Q116_A',\n",
       "       'Q117', 'Q118', 'tHFA_A001', 'tHFA_A002', 'HIV', 'TB', 'Malaria', 'ANC',\n",
       "       'Immu', 'Quality_Score', 'HW_Competence_Score', 'HW_Check', 'ANC_score',\n",
       "       'ART_score', 'TB_score', 'Patient_Centeredness_Score', 'PC_Check',\n",
       "       'iss_score', 'iss_check', 'fac_tech', 'fac_stats', 'fac_group_PS',\n",
       "       'fac_comms_data', 'HTM-ANC', 'hiv testing', 'ART access', 'iptp scores',\n",
       "       'TB screening', 'system_CHW_score', 'chw_show', 'chw_iss',\n",
       "       'chw_contract', 'chw_paid', 'chw_tools', 'facility_composite',\n",
       "       'HTM-ANC.1', 'presence_score', 'caseload', 'ANC_score.1', 'DPT_score',\n",
       "       'TB_treatment_completion', 'ART_retention', 'CHW_paid', 'CHW_scores',\n",
       "       'CLM_score', 'guideline_score', 'guide_HIV', 'guide_TB',\n",
       "       'guide_malaria', 'guide_anc', 'guide_immu', 'guide_imci',\n",
       "       'oxygen_score', 'oxygen_avl', 'oxygen_avo', 'test-capac',\n",
       "       'test-capac_check', 'with lab', 'without lab', 'vacancy_score',\n",
       "       'all_funded_posts', 'all_unfilled_posts'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iqvia.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['vacancy_score', 'all_funded_posts', 'all_unfilled_posts',\n",
       "       'hiv_guidelines', 'art_guidelines', 'tb_guidelines', 'mal_guidelines',\n",
       "       'anc_guidelines', 'imm_guidelines', 'imci_guidelines',\n",
       "       'valid_services_hsg', 'guidelines_avail_services_hsg', 'hsg_indicator',\n",
       "       'oxy_avl', 'oxy_avo', 'rcs_1', 'test_wo_lab', 'test_w_lab', 'lab_5',\n",
       "       'hrh_1', 'hrh_1_physician', 'hrh_1_nurse', 'hrh_1_labtech',\n",
       "       'hrh_1_pharma', 'hrh_1_chw', 'hrh_5_att1', 'tot_services', 'flag_htm',\n",
       "       'hiv_content', 'tb_content', 'mal_content', 'anc_content',\n",
       "       'imm_content', 'child_content', 'any_content', 'hrh_5_att2',\n",
       "       'hrh_5_att3', 'hrh_5_att4', 'hrh_5_att5', 'miss_att', 'hrh_5_tot',\n",
       "       'valid_att', 'hrh_5', 'css_3_1', 'css_3_2', 'css_3_3', 'css_3_4',\n",
       "       'css_3_5', 'css_3_6', 'css_3_total', 'css_3', 'flag_hrh6_1',\n",
       "       'prov_avail', 'hrh_6_att2', 'avg_opd', 'flag_opd', 'hrh_6_att3',\n",
       "       'flag_hrh6_2', 'hrh_6_att5', 'hrh_o1_att1', 'hrh_o1_att2',\n",
       "       'hrh_7_anc_rr_score1', 'hrh_7_anc_rr_score2', 'hrh_7_anc_rr_score3',\n",
       "       'hrh_7_anc_rr_score4', 'hrh_7_anc_rr_score5', 'hrh_7_art_rr_score1',\n",
       "       'hrh_7_art_rr_score2', 'hrh_7_art_rr_score3', 'hrh_7_art_rr_score4',\n",
       "       'hrh_7_art_rr_score5', 'hrh_7_tb_rr_score1', 'hrh_7_tb_rr_score2',\n",
       "       'hrh_7_tb_rr_score3', 'hrh_7_tb_rr_score4', 'hrh_7_tb_rr_score5',\n",
       "       'hrh_7_d1_score', 'hrh_7_d1_merge', 'hrh_7_pei_rr', 'hrh_7_d2_comms',\n",
       "       'hrh_7_d2_resp', 'hrh_7_d2_auto', 'hrh_7_d2_conf', 'hrh_7_d2_social',\n",
       "       'hrh_7_d2_diag', 'hrh_7_d2_danger', 'hrh_7_d2_rude',\n",
       "       'hrh_7_d2_challenge', 'hrh_7_pei_merge', 'hrh_7_d2_score',\n",
       "       'hrh_7_score', 'kpi_s5_a1', 'kpi_s5_a2', 'kpi_s5_a3', 'kpi_s5_a4',\n",
       "       'kpi_s5', '_merge', 'hrh_7_anc_avg', 'hrh_7_art_avg', 'hrh_7_tb_avg'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matched_1st.columns[-100:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Testing "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "test = ['MWI_000193', 'MWI_001282','MWI_000166', 'MWI_001312', 'MWI_001109']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "oxy_i = ['oxygen_score', 'oxygen_avl', 'oxygen_avo']\n",
    "oxy = ['oxy_avl','oxy_avo',\t'rcs_1']\n",
    "lab_i = ['test-capac']\n",
    "lab = ['lab_5']\n",
    "guide = ['hiv_guidelines', 'art_guidelines',\n",
    "       'tb_guidelines', 'mal_guidelines', 'anc_guidelines', 'imm_guidelines',\n",
    "       'imci_guidelines', 'valid_services_hsg',\n",
    "       'guidelines_avail_services_hsg', 'hsg_indicator',]\n",
    "guide_i = ['guideline_score', 'guide_HIV', 'guide_TB',\n",
    "       'guide_malaria', 'guide_anc', 'guide_immu', 'guide_imci']\n",
    "\n",
    "# ISS comparison\n",
    "iss_i = ['iss_score', 'iss_check', 'fac_tech', 'fac_stats', 'fac_group_PS',\n",
    "       'fac_comms_data',]\n",
    "iss = ['hrh_5_att2','hrh_5_att3', 'hrh_5_att4', 'hrh_5_att5', 'hrh_5',]\n",
    "\n",
    "# CHW comparison\n",
    "chw_i = ['system_CHW_score', 'chw_show', 'chw_iss',\n",
    "       'chw_contract', 'chw_paid', 'chw_tools']\n",
    "chw = ['kpi_s5_a1', 'kpi_s5_a2', 'kpi_s5_a3',\n",
    "       'kpi_s5_a4', 'kpi_s5',]\n",
    "\n",
    "# High Quality Services\n",
    "hq_i = ['Quality_Score', 'HW_Competence_Score', 'HW_Check', 'ANC_score',\n",
    "       'ART_score', 'TB_score', 'Patient_Centeredness_Score', 'PC_Check',]\n",
    "hq = ['hrh_7_d1_score',\n",
    "       'hrh_7_d2_score', 'hrh_7_score',]\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### High Quality Services"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'hq' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[4], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m tgf \u001b[38;5;241m=\u001b[39m \u001b[43mhq\u001b[49m\n\u001b[0;32m      2\u001b[0m iqv \u001b[38;5;241m=\u001b[39m hq_i\n\u001b[0;32m      4\u001b[0m see \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mmerge(iqvia[iqv],matched_1st[tgf],left_index\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m,right_index\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m)\n",
      "\u001b[1;31mNameError\u001b[0m: name 'hq' is not defined"
     ]
    }
   ],
   "source": [
    "tgf = hq\n",
    "iqv = hq_i\n",
    "\n",
    "see = pd.merge(iqvia[iqv],matched_1st[tgf],left_index=True,right_index=True)\n",
    "# see = pd.merge(iqvia[iqv],lab,left_index=True,right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Patient_Centeredness_Score</th>\n",
       "      <th>hrh_7_d2_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>MWI_000166</th>\n",
       "      <td>73.333333</td>\n",
       "      <td>70.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_001167</th>\n",
       "      <td>61.061508</td>\n",
       "      <td>63.561508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_001019</th>\n",
       "      <td>57.430556</td>\n",
       "      <td>59.930557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000299</th>\n",
       "      <td>60.476190</td>\n",
       "      <td>62.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000019</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_001563</th>\n",
       "      <td>71.111111</td>\n",
       "      <td>80.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000350</th>\n",
       "      <td>54.513889</td>\n",
       "      <td>55.763889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000528</th>\n",
       "      <td>73.888889</td>\n",
       "      <td>78.333336</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Patient_Centeredness_Score  hrh_7_d2_score\n",
       "MWI_000166                   73.333333       70.000000\n",
       "MWI_001167                   61.061508       63.561508\n",
       "MWI_001019                   57.430556       59.930557\n",
       "MWI_000299                   60.476190       62.500000\n",
       "MWI_000019                         NaN             NaN\n",
       "MWI_001563                   71.111111       80.000000\n",
       "MWI_000350                   54.513889       55.763889\n",
       "MWI_000528                   73.888889       78.333336"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comp_i = 'Patient_Centeredness_Score'\n",
    "comp = 'hrh_7_d2_score'\n",
    "like = see.loc[test]\n",
    "see[round(see[comp_i]) != round(see[comp])][[comp_i,comp]]\n",
    "# like[[comp_i,comp]]\n",
    "# like"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This is the hrh_7_country_average from TGF:  60.11918179194132 and from IQVIA:  60.04304114971728\n",
      "hrh_7_d1_country_average from TGF:  49.80404421488444 and from IQVIA:  49.804044160104766\n",
      "hrh_7_d1_country_average from TGF:  70.71247937498974 and from IQVIA:  70.55891801164911\n"
     ]
    }
   ],
   "source": [
    "print(\"This is the hrh_7_country_average from TGF: \", np.nanmean(see['hrh_7_score']),\"and from IQVIA: \",np.nanmean(see['Quality_Score']))\n",
    "print(\"hrh_7_d1_country_average from TGF: \", np.nanmean(see['hrh_7_d1_score']),\"and from IQVIA: \",np.nanmean(see['HW_Competence_Score']))\n",
    "print(\"hrh_7_d1_country_average from TGF: \", np.nanmean(see['hrh_7_d2_score']),\"and from IQVIA: \",np.nanmean(see['Patient_Centeredness_Score']))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Do this this the updated version of Pankkuri's data\n",
    "\n",
    "# comp_i = 'HW_Competence_Score'\n",
    "# comp = 'hrh_7_d1_score'\n",
    "# see[round(see[comp_i])!= round(see[comp])][[comp_i,comp]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ISS scores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "tgf = iss\n",
    "iqv = iss_i\n",
    "\n",
    "see = pd.merge(iqvia[iqv],matched_1st[tgf],left_index=True,right_index=True)\n",
    "# see = pd.merge(iqvia[iqv],lab,left_index=True,right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>iss_score</th>\n",
       "      <th>hrh_5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [iss_score, hrh_5]\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comp_i = 'iss_score'\n",
    "comp = 'hrh_5'\n",
    "see[round(see[comp_i]) != round(see[comp])][[comp_i,comp]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CHW scores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>chw_iss</th>\n",
       "      <th>kpi_s5_a1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>MWI_001109</th>\n",
       "      <td>80.0</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000570</th>\n",
       "      <td>20.0</td>\n",
       "      <td>60.000004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_001168</th>\n",
       "      <td>100.0</td>\n",
       "      <td>80.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000339</th>\n",
       "      <td>20.0</td>\n",
       "      <td>60.000004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000914</th>\n",
       "      <td>20.0</td>\n",
       "      <td>40.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_001226</th>\n",
       "      <td>0.0</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000877</th>\n",
       "      <td>0.0</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_001428</th>\n",
       "      <td>60.0</td>\n",
       "      <td>80.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000529</th>\n",
       "      <td>80.0</td>\n",
       "      <td>40.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MWI_000273</th>\n",
       "      <td>20.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            chw_iss   kpi_s5_a1\n",
       "MWI_001109     80.0  100.000000\n",
       "MWI_000570     20.0   60.000004\n",
       "MWI_001168    100.0   80.000000\n",
       "MWI_000339     20.0   60.000004\n",
       "MWI_000914     20.0   40.000000\n",
       "...             ...         ...\n",
       "MWI_001226      0.0   20.000000\n",
       "MWI_000877      0.0  100.000000\n",
       "MWI_001428     60.0   80.000000\n",
       "MWI_000529     80.0   40.000000\n",
       "MWI_000273     20.0    0.000000\n",
       "\n",
       "[63 rows x 2 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tgf = chw\n",
    "iqv = chw_i\n",
    "see = pd.merge(iqvia[iqv],matched_1st[tgf],left_index=True,right_index=True)\n",
    "# see = pd.merge(iqvia[iqv],lab,left_index=True,right_index=True)\n",
    "# see['kpi_s5_a1'] = see['kpi_s5_a1'] * 100\n",
    "comp_i = 'chw_iss'\n",
    "comp = 'kpi_s5_a1'\n",
    "see[round(see[comp_i]) != round(see[comp])][[comp_i,comp]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Nigeria Matching"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "matched_nig = pd.read_excel('./external/TGF_Nigeria_v2.xlsx')\n",
    "matched_nig = matched_nig.set_index('q100')\n",
    "iqvia = pd.read_csv('./output/NGA/tHFA-nigeria-analysis_v2.3.csv')\n",
    "iqvia = iqvia.set_index('Q100')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ISS comparison\n",
    "iss_i = ['iss_score', 'iss_check', 'fac_tech', 'fac_stats', 'fac_group_PS',\n",
    "       'fac_comms_data',]\n",
    "iss = ['hrh_5_att2','hrh_5_att3', 'hrh_5_att4', 'hrh_5_att5', 'hrh_5',]\n",
    "\n",
    "# CHW comparison\n",
    "chw_i = ['system_CHW_score', 'chw_show', 'chw_iss',\n",
    "       'chw_contract', 'chw_paid', 'chw_tools']\n",
    "chw = ['kpi_s5_a1', 'kpi_s5_a2', 'kpi_s5_a3',\n",
    "       'kpi_s5_a4', 'kpi_s5',]\n",
    "\n",
    "# High Quality Services\n",
    "hq_i = ['Quality_Score', 'HW_Competence_Score', 'HW_Check', 'ANC_score',\n",
    "       'ART_score', 'TB_score', 'Patient_Centeredness_Score', 'PC_avg_Communication',\n",
    "       'PC_avg_Respect','PC_avg_Autonomy','PC_avg_Confidentiality', 'PC_avg_Social support', \n",
    "       'PC_avg_HW told', 'PC_avg_Danger signs', 'PC_avg_Rudeness', 'PC_avg_Challenges' ]\n",
    "hq = ['hrh_7_d1_score', 'hrh_7_anc_mean', 'hrh_7_art_mean', 'hrh_7_tb_mean',\n",
    "       'hrh_7_d2_score', 'hrh_7_score','hrh_7_d2_comms', 'hrh_7_d2_resp', 'hrh_7_d2_auto', 'hrh_7_d2_conf','hrh_7_d2_social', 'hrh_7_d2_diag', 'hrh_7_d2_danger', 'hrh_7_d2_rude', 'hrh_7_d2_challenge'\n",
    "]\n",
    "\n",
    "# Guide comparison\n",
    "guide = ['hiv_guidelines', 'art_guidelines',\n",
    "       'tb_guidelines', 'mal_guidelines', 'anc_guidelines', 'imm_guidelines',\n",
    "       'imci_guidelines', 'valid_services_hsg',\n",
    "       'guidelines_avail_services_hsg', 'hsg_indicator',]\n",
    "guide_i = ['guideline_score', 'guide_HIV_test','guide_ART', 'guide_TB',\n",
    "       'guide_malaria', 'guide_anc', 'guide_immu', 'guide_imci']\n",
    "\n",
    "# Effective services comparison\n",
    "effect = [ 'hrh_6_att2', 'avg_opd', 'hrh_6_att3',\n",
    "       'hrh_6_att5','v60','v64','v57']\n",
    "effect_i = ['facility_composite', 'presence_score', 'caseload',\n",
    "       'DPT_score', 'TB_treatment_completion', 'ART_retention',]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "matched_nig.columns[-110:-20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "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>v64</th>\n",
       "      <th>facility_composite</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NGA_000653</th>\n",
       "      <td>66.66666</td>\n",
       "      <td>63.599888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NGA_000679</th>\n",
       "      <td>0.00000</td>\n",
       "      <td>67.187500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NGA_000676</th>\n",
       "      <td>0.00000</td>\n",
       "      <td>54.444444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NGA_000690</th>\n",
       "      <td>100.00000</td>\n",
       "      <td>74.497268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NGA_000248</th>\n",
       "      <td>NaN</td>\n",
       "      <td>57.247024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NGA_000431</th>\n",
       "      <td>50.00000</td>\n",
       "      <td>57.638889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NGA_000311</th>\n",
       "      <td>0.00000</td>\n",
       "      <td>73.134690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NGA_000154</th>\n",
       "      <td>100.00000</td>\n",
       "      <td>55.143678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NGA_000046</th>\n",
       "      <td>100.00000</td>\n",
       "      <td>48.416399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NGA_000207</th>\n",
       "      <td>NaN</td>\n",
       "      <td>88.888889</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>238 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  v64  facility_composite\n",
       "NGA_000653   66.66666           63.599888\n",
       "NGA_000679    0.00000           67.187500\n",
       "NGA_000676    0.00000           54.444444\n",
       "NGA_000690  100.00000           74.497268\n",
       "NGA_000248        NaN           57.247024\n",
       "...               ...                 ...\n",
       "NGA_000431   50.00000           57.638889\n",
       "NGA_000311    0.00000           73.134690\n",
       "NGA_000154  100.00000           55.143678\n",
       "NGA_000046  100.00000           48.416399\n",
       "NGA_000207        NaN           88.888889\n",
       "\n",
       "[238 rows x 2 columns]"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "interest = effect\n",
    "\n",
    "tgf = effect\n",
    "iqv = effect_i\n",
    "# check = ['PC_avg_Danger signs', 'PC_avg_Rudeness', 'PC_avg_Challenges','hrh_7_d2_danger', 'hrh_7_d2_rude', 'hrh_7_d2_challenge']\n",
    "# check = ['Quality_Score','hrh_7_score','HW_Competence_Score','hrh_7_d1_score','Patient_Centeredness_Score','hrh_7_d2_score',]\n",
    "\n",
    "# check = ['Quality_Score','hrh_7_score',\n",
    "#          'HW_Competence_Score','hrh_7_d1_score',\n",
    "#          'ANC_score',   'ART_score', 'TB_score',\n",
    "#          'hrh_7_anc_mean', 'hrh_7_art_mean', 'hrh_7_tb_mean',]\n",
    "\n",
    "check = [tgf[-1],iqv[0],\n",
    "      #    tgf[2],\n",
    "      #  iqv[3]\n",
    "\n",
    "       ]\n",
    "\n",
    "see = pd.merge(iqvia[iqv],matched_nig[tgf],left_index=True,right_index=True)\n",
    "comp_i = iqv[0]\n",
    "comp = tgf[-1]\n",
    "\n",
    "see[round(see[comp_i]) != round(see[comp])][check]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = \"./Updated tHFA data/tHFA_NGA.xlsx\"\n",
    "\n",
    "df = pd.read_excel(file_path, header=None)\n",
    "df.columns = df.iloc[0]\n",
    "df = df[1:]\n",
    "df.set_index('Q100', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "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>R2419_01</th>\n",
       "      <th>R2419_03</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Q100</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NGA_000694</th>\n",
       "      <td>YES, OBSERVED</td>\n",
       "      <td>YES, OBSERVED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NGA_000528</th>\n",
       "      <td>YES, REPORTED, NOT SEEN</td>\n",
       "      <td>YES, REPORTED, NOT SEEN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NGA_000434</th>\n",
       "      <td>YES, OBSERVED</td>\n",
       "      <td>YES, OBSERVED</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0                          R2419_01                 R2419_03\n",
       "Q100                                                        \n",
       "NGA_000694            YES, OBSERVED            YES, OBSERVED\n",
       "NGA_000528  YES, REPORTED, NOT SEEN  YES, REPORTED, NOT SEEN\n",
       "NGA_000434            YES, OBSERVED            YES, OBSERVED"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d = ['R2419_01','R2419_03']\n",
    "df.loc[['NGA_000694','NGA_000528', 'NGA_000434']][d]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0\n",
       "Q13005_1_3      NO\n",
       "Q13004_8_3     YES\n",
       "Q13004_10_3     NO\n",
       "Q13004_11_3    YES\n",
       "Q13005_2_3      NO\n",
       "Q13005_5_3      NO\n",
       "Q13004_1_3      98\n",
       "Name: NGA_000117, dtype: object"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "i = 3\n",
    "q = [f'Q13005_1_{i}',f'Q13004_8_{i}',\n",
    "                    f'Q13004_10_{i}',\n",
    "                    f'Q13004_11_{i}',\n",
    "                    f'Q13005_2_{i}',\n",
    "                    f'Q13005_5_{i}',\n",
    "                    f'Q13004_1_{i}']\n",
    "df.loc['NGA_000117'][q]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['YES, REPORTED, NOT SEEN', 'YES, OBSERVED', nan,\n",
       "       'NO/NOT AVAILABLE'], dtype=object)"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['R2308_01'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0\n",
       "tHFA_E020_1                       YES\n",
       "tHFA_E021_1                       YES\n",
       "tHFA_E022_1                       YES\n",
       "tHFA_E023_1    DON’T KNOW OR NOT SURE\n",
       "tHFA_E020_2                       YES\n",
       "tHFA_E021_2                       YES\n",
       "tHFA_E022_2                       YES\n",
       "tHFA_E023_2                       YES\n",
       "tHFA_E020_3                       NaN\n",
       "tHFA_E021_3                       NaN\n",
       "tHFA_E022_3                       NaN\n",
       "tHFA_E023_3                       NaN\n",
       "tHFA_E020_4                       NaN\n",
       "tHFA_E021_4                       NaN\n",
       "tHFA_E022_4                       NaN\n",
       "tHFA_E023_4                       NaN\n",
       "tHFA_E020_5                       NaN\n",
       "tHFA_E021_5                       NaN\n",
       "tHFA_E022_5                       NaN\n",
       "tHFA_E023_5                       NaN\n",
       "Name: NGA_000431, dtype: object"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f  = [f'tHFA_E02{i}_{j}' for j in [1,2,3,4,5] for i in [0,1,2,3]]\n",
    "df.loc['NGA_000431'][f]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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.12.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}