cspro2csv.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 287,
   "id": "3c6dafd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import itertools\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "id": "91e2cd05",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = './data/household05032024/table08.tbw'\n",
    "out_path = './data/out05032024/table08.csv'\n",
    "dic_path = './data/CensusMainShortForm_new.dcf'\n",
    "\n",
    "f = open(file_path, 'r')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "id": "1af76dab",
   "metadata": {},
   "outputs": [],
   "source": [
    "dimension = []\n",
    "all_values = []\n",
    "all_codes= []\n",
    "all_rows = []\n",
    "values = []\n",
    "area_names = []\n",
    "area_codes = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 290,
   "id": "3d14e500",
   "metadata": {},
   "outputs": [],
   "source": [
    "i=1\n",
    "notFirst=False\n",
    "while True:\n",
    "        \n",
    "        line = f.readline()\n",
    "        if not line:\n",
    "            if notFirst:\n",
    "                all_values.append(values)\n",
    "            break\n",
    "        else:\n",
    "            if line.find('[Variable]') != -1:\n",
    "                if notFirst:\n",
    "                    all_values.append(values)\n",
    "                name = f.readline()\n",
    "                name = name.replace(\"\\n\", \"\")\n",
    "                name =  name[5:]\n",
    "                dimension.append(name)\n",
    "                values = []\n",
    "                notFirst = True\n",
    "            if line.find('[Value]') != -1:\n",
    "                label = f.readline()\n",
    "                label = label.replace(\"\\n\", \"\")\n",
    "                label = label[6:]\n",
    "                #print(label)\n",
    "                values.append(label)\n",
    "            if line.find('AreaName') == 0: \n",
    "                area = line[9:]\n",
    "                #print(area)\n",
    "                area_names.append(area.strip())\n",
    "            if line.find('BreakKey') == 0: \n",
    "                code = line[9:]\n",
    "                code = code.replace(';', '')\n",
    "                #print(area)\n",
    "                area_codes.append(code.strip())\n",
    "            if line.find('Row') == 0: \n",
    "                #row = f.readline()\n",
    "                row = line[4:]\n",
    "                i = i+1\n",
    "                arr = [ x.strip() for x in row.split('  ')]\n",
    "                all_rows += arr\n",
    "              \n",
    "\n",
    "               "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "id": "33fbba81",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['%AreaName%'],\n",
       " ['Total',\n",
       "  'None/Never Attended',\n",
       "  'Other (Informal)',\n",
       "  'Primary',\n",
       "  'Secondary',\n",
       "  'Tertiary',\n",
       "  'Not Stated/DK'],\n",
       " ['Total', 'Yes', 'No', 'DK']]"
      ]
     },
     "execution_count": 291,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "id": "81101699",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['SYSTEM_TOTAL', 'EDUC_COMP_HH_HEAD_VS1', 'H19_3_VS1']\n",
      "[['%AreaName%'], ['Total', 'None/Never Attended', 'Other (Informal)', 'Primary', 'Secondary', 'Tertiary', 'Not Stated/DK'], ['Total', 'Yes', 'No', 'DK']]\n"
     ]
    }
   ],
   "source": [
    "#print(area_codes)              \n",
    "#all_values[0] = area_codes                \n",
    "print(dimension)\n",
    "#print(area_names)\n",
    "print(all_values)\n",
    "#print(all_rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "id": "80e55803",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['1', 'None/Never Attended', 'EDUC_COMP_HH_HEAD_VS1\\n'], ['2', 'Other (Informal)', 'EDUC_COMP_HH_HEAD_VS1\\n'], ['3', 'Primary', 'EDUC_COMP_HH_HEAD_VS1\\n'], ['4', 'Secondary', 'EDUC_COMP_HH_HEAD_VS1\\n'], ['5', 'Tertiary', 'EDUC_COMP_HH_HEAD_VS1\\n'], ['6', 'Not Stated/DK', 'EDUC_COMP_HH_HEAD_VS1\\n'], ['T', 'Total', 'EDUC_COMP_HH_HEAD_VS1']]\n",
      "\n",
      "\n",
      "[['1', 'Yes', 'H19_3_VS1\\n'], ['2', 'No', 'H19_3_VS1\\n'], ['9', 'DK', 'H19_3_VS1\\n'], ['T', 'Total', 'H19_3_VS1']]\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "d = open(dic_path, 'r', encoding='utf-8')\n",
    "header =['cod','desc', 'cod_list']\n",
    "dim_values = []\n",
    "total = []\n",
    "a =  np.empty((0, 3))\n",
    "\n",
    "def getCodes(d, dim):\n",
    "    d.seek(0)\n",
    "    values = []\n",
    "    dim=dim + \"\\n\"\n",
    "    while True:\n",
    "        line = d.readline()\n",
    "        if not line:\n",
    "            break\n",
    "        if line.find(dim) != -1:\n",
    "            line = d.readline()\n",
    "            while line.find('Value') != -1:\n",
    "                line = line.replace(\"\\n\", \"\")\n",
    "                line = line[6:]\n",
    "                line = line + ';' + dim\n",
    "                line = line.replace(':', '-')\n",
    "                item = line.split(';')\n",
    "                \n",
    "                values.append(item)\n",
    "                line = d.readline()\n",
    "                if line.find('Note=') != -1:\n",
    "                    line = d.readline()\n",
    "    return values\n",
    "\n",
    "\n",
    "for dim in dimension[1:]:\n",
    "    values = getCodes(d, dim)\n",
    "    total = ['T', 'Total', dim]\n",
    "    values.append(total)\n",
    "    print(values)\n",
    "    print(\"\\n\")\n",
    "    a = np.concatenate((a, values), axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 294,
   "id": "57df6ce7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['1', 'None/Never Attended', 'EDUC_COMP_HH_HEAD_VS1\\n'],\n",
       "       ['2', 'Other (Informal)', 'EDUC_COMP_HH_HEAD_VS1\\n'],\n",
       "       ['3', 'Primary', 'EDUC_COMP_HH_HEAD_VS1\\n'],\n",
       "       ['4', 'Secondary', 'EDUC_COMP_HH_HEAD_VS1\\n'],\n",
       "       ['5', 'Tertiary', 'EDUC_COMP_HH_HEAD_VS1\\n'],\n",
       "       ['6', 'Not Stated/DK', 'EDUC_COMP_HH_HEAD_VS1\\n'],\n",
       "       ['T', 'Total', 'EDUC_COMP_HH_HEAD_VS1'],\n",
       "       ['1', 'Yes', 'H19_3_VS1\\n'],\n",
       "       ['2', 'No', 'H19_3_VS1\\n'],\n",
       "       ['9', 'DK', 'H19_3_VS1\\n'],\n",
       "       ['T', 'Total', 'H19_3_VS1']], dtype='<U32')"
      ]
     },
     "execution_count": 294,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1e356e1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "id": "0d81acbf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['00',\n",
       " '01',\n",
       " '0101',\n",
       " '0102',\n",
       " '0103',\n",
       " '0104',\n",
       " '0105',\n",
       " '0106',\n",
       " '02',\n",
       " '0201',\n",
       " '0202',\n",
       " '0203',\n",
       " '0204',\n",
       " '0205',\n",
       " '03',\n",
       " '0301',\n",
       " '0302',\n",
       " '0303',\n",
       " '0304',\n",
       " '0305',\n",
       " '0306',\n",
       " '0307',\n",
       " '0308',\n",
       " '0309',\n",
       " '04',\n",
       " '0401',\n",
       " '0402',\n",
       " '0403',\n",
       " '05',\n",
       " '0501',\n",
       " '0502',\n",
       " '06',\n",
       " '0601',\n",
       " '0602',\n",
       " '0603',\n",
       " '0604',\n",
       " '07',\n",
       " '0701',\n",
       " '0702',\n",
       " '0703',\n",
       " '0704',\n",
       " '0705',\n",
       " '0706',\n",
       " '0707',\n",
       " '08',\n",
       " '0801',\n",
       " '0802',\n",
       " '0803',\n",
       " '0804',\n",
       " '0805',\n",
       " '0806',\n",
       " '0807',\n",
       " '0808',\n",
       " '09',\n",
       " '0901',\n",
       " '0902',\n",
       " '0903',\n",
       " '0904',\n",
       " '0905',\n",
       " '0906',\n",
       " '0907',\n",
       " '10',\n",
       " '1001',\n",
       " '1002',\n",
       " '1003',\n",
       " '1004',\n",
       " '1005',\n",
       " '1006',\n",
       " '1007',\n",
       " '11',\n",
       " '1101',\n",
       " '1102',\n",
       " '1103',\n",
       " '12',\n",
       " '1201',\n",
       " '1202',\n",
       " '1203',\n",
       " '1204',\n",
       " '1205',\n",
       " '1206',\n",
       " '1207',\n",
       " '1208',\n",
       " '1209',\n",
       " '1210',\n",
       " '1211',\n",
       " '1212',\n",
       " '1213',\n",
       " '13',\n",
       " '1301',\n",
       " '1302',\n",
       " '1303',\n",
       " '1304',\n",
       " '1305',\n",
       " '1306',\n",
       " '14',\n",
       " '1401',\n",
       " '1402',\n",
       " '1403',\n",
       " '1404',\n",
       " '1405',\n",
       " '1406',\n",
       " '15',\n",
       " '1501',\n",
       " '1502',\n",
       " '1503',\n",
       " '1504',\n",
       " '1505',\n",
       " '1506',\n",
       " '1507',\n",
       " '1508',\n",
       " '1509',\n",
       " '1510',\n",
       " '1511',\n",
       " '1512',\n",
       " '1513',\n",
       " '1514',\n",
       " '1515',\n",
       " '1516',\n",
       " '1517',\n",
       " '1518',\n",
       " '16',\n",
       " '1601',\n",
       " '1602',\n",
       " '1603',\n",
       " '1604',\n",
       " '1605',\n",
       " '1606',\n",
       " '1607',\n",
       " '1608',\n",
       " '1609',\n",
       " '17',\n",
       " '1701',\n",
       " '1702',\n",
       " '1703',\n",
       " '1704',\n",
       " '1705',\n",
       " '1706',\n",
       " '1707',\n",
       " '1708',\n",
       " '1709',\n",
       " '18',\n",
       " '1801',\n",
       " '1802',\n",
       " '1803',\n",
       " '1804',\n",
       " '1805',\n",
       " '1806',\n",
       " '1807',\n",
       " '1808',\n",
       " '19',\n",
       " '1901',\n",
       " '1902',\n",
       " '1903',\n",
       " '1904',\n",
       " '1905',\n",
       " '1906',\n",
       " '1907',\n",
       " '1908',\n",
       " '1909',\n",
       " '1910',\n",
       " '20',\n",
       " '2001',\n",
       " '2002',\n",
       " '2003',\n",
       " '2004',\n",
       " '2005',\n",
       " '2006',\n",
       " '21',\n",
       " '2101',\n",
       " '2102',\n",
       " '2103',\n",
       " '2104',\n",
       " '2105',\n",
       " '2106',\n",
       " '2107',\n",
       " '2108',\n",
       " '2109',\n",
       " '22',\n",
       " '2201',\n",
       " '2202',\n",
       " '2203',\n",
       " '2204',\n",
       " '2205',\n",
       " '2206',\n",
       " '2207',\n",
       " '2208',\n",
       " '2209',\n",
       " '2210',\n",
       " '2211',\n",
       " '2212',\n",
       " '2213',\n",
       " '23',\n",
       " '2301',\n",
       " '2302',\n",
       " '2303',\n",
       " '2304',\n",
       " '2305',\n",
       " '2306',\n",
       " '2307',\n",
       " '24',\n",
       " '2401',\n",
       " '2402',\n",
       " '2403',\n",
       " '2404',\n",
       " '2405',\n",
       " '25',\n",
       " '2501',\n",
       " '2502',\n",
       " '2503',\n",
       " '26',\n",
       " '2601',\n",
       " '2602',\n",
       " '2603',\n",
       " '2604',\n",
       " '2605',\n",
       " '27',\n",
       " '2701',\n",
       " '2702',\n",
       " '2703',\n",
       " '2704',\n",
       " '2705',\n",
       " '2706',\n",
       " '28',\n",
       " '2801',\n",
       " '2802',\n",
       " '2803',\n",
       " '2804',\n",
       " '29',\n",
       " '2901',\n",
       " '2902',\n",
       " '2903',\n",
       " '2904',\n",
       " '2905',\n",
       " '2906',\n",
       " '30',\n",
       " '3001',\n",
       " '3002',\n",
       " '3003',\n",
       " '3004',\n",
       " '3005',\n",
       " '3006',\n",
       " '3007',\n",
       " '31',\n",
       " '3101',\n",
       " '3102',\n",
       " '3103',\n",
       " '3104',\n",
       " '3105',\n",
       " '32',\n",
       " '3201',\n",
       " '3202',\n",
       " '3203',\n",
       " '3204',\n",
       " '3205',\n",
       " '3206',\n",
       " '3207',\n",
       " '3208',\n",
       " '3209',\n",
       " '3210',\n",
       " '3211',\n",
       " '33',\n",
       " '3301',\n",
       " '3302',\n",
       " '3303',\n",
       " '3304',\n",
       " '3305',\n",
       " '3306',\n",
       " '3307',\n",
       " '34',\n",
       " '3401',\n",
       " '3402',\n",
       " '3403',\n",
       " '3404',\n",
       " '3405',\n",
       " '3406',\n",
       " '35',\n",
       " '3501',\n",
       " '3502',\n",
       " '3503',\n",
       " '3504',\n",
       " '3505',\n",
       " '3506',\n",
       " '36',\n",
       " '3601',\n",
       " '3602',\n",
       " '3603',\n",
       " '3604',\n",
       " '3605',\n",
       " '37',\n",
       " '3701',\n",
       " '3702',\n",
       " '3703',\n",
       " '3704',\n",
       " '3705',\n",
       " '3706',\n",
       " '3707',\n",
       " '3708',\n",
       " '3709',\n",
       " '3710',\n",
       " '3711',\n",
       " '3712',\n",
       " '3713',\n",
       " '38',\n",
       " '3801',\n",
       " '3802',\n",
       " '3803',\n",
       " '3804',\n",
       " '3805',\n",
       " '3806',\n",
       " '39',\n",
       " '3901',\n",
       " '3902',\n",
       " '3903',\n",
       " '3904',\n",
       " '3905',\n",
       " '3906',\n",
       " '3907',\n",
       " '3908',\n",
       " '3909',\n",
       " '3910',\n",
       " '3911',\n",
       " '3912',\n",
       " '40',\n",
       " '4001',\n",
       " '4002',\n",
       " '4003',\n",
       " '4004',\n",
       " '4005',\n",
       " '4006',\n",
       " '4007',\n",
       " '41',\n",
       " '4101',\n",
       " '4102',\n",
       " '4103',\n",
       " '4104',\n",
       " '4105',\n",
       " '4106',\n",
       " '42',\n",
       " '4201',\n",
       " '4202',\n",
       " '4203',\n",
       " '4204',\n",
       " '4205',\n",
       " '4206',\n",
       " '4207',\n",
       " '43',\n",
       " '4301',\n",
       " '4302',\n",
       " '4303',\n",
       " '4304',\n",
       " '4305',\n",
       " '4306',\n",
       " '4307',\n",
       " '4308',\n",
       " '44',\n",
       " '4401',\n",
       " '4402',\n",
       " '4403',\n",
       " '4404',\n",
       " '4405',\n",
       " '4406',\n",
       " '4407',\n",
       " '4408',\n",
       " '45',\n",
       " '4501',\n",
       " '4502',\n",
       " '4503',\n",
       " '4504',\n",
       " '4505',\n",
       " '4506',\n",
       " '4507',\n",
       " '4508',\n",
       " '4509',\n",
       " '4510',\n",
       " '4511',\n",
       " '46',\n",
       " '4601',\n",
       " '4602',\n",
       " '4603',\n",
       " '4604',\n",
       " '4605',\n",
       " '47',\n",
       " '4701',\n",
       " '4702',\n",
       " '4703',\n",
       " '4704',\n",
       " '4705',\n",
       " '4706',\n",
       " '4707',\n",
       " '4708',\n",
       " '4709',\n",
       " '4710',\n",
       " '4711']"
      ]
     },
     "execution_count": 295,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#top level territorial dimension\n",
    "area_codes[0]='00'\n",
    "area_codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "id": "ff41dc83",
   "metadata": {},
   "outputs": [],
   "source": [
    "area_varname = [dimension[0]] * len(area_codes)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 297,
   "id": "e2e7896b",
   "metadata": {},
   "outputs": [],
   "source": [
    "b = np.column_stack((area_codes, area_names, area_varname))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "id": "ca9ef56c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['00', 'Kenya', 'SYSTEM_TOTAL'],\n",
       "       ['01', 'Mombasa', 'SYSTEM_TOTAL'],\n",
       "       ['0101', 'Changamwe', 'SYSTEM_TOTAL'],\n",
       "       ...,\n",
       "       ['4709', 'Njiru', 'SYSTEM_TOTAL'],\n",
       "       ['4710', 'Starehe', 'SYSTEM_TOTAL'],\n",
       "       ['4711', 'Westlands', 'SYSTEM_TOTAL']], dtype='<U22')"
      ]
     },
     "execution_count": 298,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "id": "3e4ba716",
   "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>cod</th>\n",
       "      <th>desc</th>\n",
       "      <th>cod_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00</td>\n",
       "      <td>Kenya</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>Mombasa</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0101</td>\n",
       "      <td>Changamwe</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0102</td>\n",
       "      <td>Jomvu</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0103</td>\n",
       "      <td>Kisauni</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>T</td>\n",
       "      <td>Total</td>\n",
       "      <td>EDUC_COMP_HH_HEAD_VS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>400</th>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "      <td>H19_3_VS1\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>401</th>\n",
       "      <td>2</td>\n",
       "      <td>No</td>\n",
       "      <td>H19_3_VS1\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402</th>\n",
       "      <td>9</td>\n",
       "      <td>DK</td>\n",
       "      <td>H19_3_VS1\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>403</th>\n",
       "      <td>T</td>\n",
       "      <td>Total</td>\n",
       "      <td>H19_3_VS1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>404 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      cod       desc               cod_list\n",
       "0      00      Kenya           SYSTEM_TOTAL\n",
       "1      01    Mombasa           SYSTEM_TOTAL\n",
       "2    0101  Changamwe           SYSTEM_TOTAL\n",
       "3    0102      Jomvu           SYSTEM_TOTAL\n",
       "4    0103    Kisauni           SYSTEM_TOTAL\n",
       "..    ...        ...                    ...\n",
       "399     T      Total  EDUC_COMP_HH_HEAD_VS1\n",
       "400     1        Yes            H19_3_VS1\\n\n",
       "401     2         No            H19_3_VS1\\n\n",
       "402     9         DK            H19_3_VS1\\n\n",
       "403     T      Total              H19_3_VS1\n",
       "\n",
       "[404 rows x 3 columns]"
      ]
     },
     "execution_count": 299,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = np.concatenate((b, a), axis=0)\n",
    "\n",
    "codes = pd.DataFrame(c)\n",
    "\n",
    "codes.columns =  header\n",
    "\n",
    "codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 300,
   "id": "ce32b497",
   "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>cod</th>\n",
       "      <th>desc</th>\n",
       "      <th>cod_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00</td>\n",
       "      <td>Kenya</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>Mombasa</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0101</td>\n",
       "      <td>Changamwe</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0102</td>\n",
       "      <td>Jomvu</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0103</td>\n",
       "      <td>Kisauni</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>T</td>\n",
       "      <td>Total</td>\n",
       "      <td>EDUC_COMP_HH_HEAD_VS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>400</th>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "      <td>H19_3_VS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>401</th>\n",
       "      <td>2</td>\n",
       "      <td>No</td>\n",
       "      <td>H19_3_VS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402</th>\n",
       "      <td>9</td>\n",
       "      <td>DK</td>\n",
       "      <td>H19_3_VS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>403</th>\n",
       "      <td>T</td>\n",
       "      <td>Total</td>\n",
       "      <td>H19_3_VS1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>404 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      cod       desc               cod_list\n",
       "0      00      Kenya           SYSTEM_TOTAL\n",
       "1      01    Mombasa           SYSTEM_TOTAL\n",
       "2    0101  Changamwe           SYSTEM_TOTAL\n",
       "3    0102      Jomvu           SYSTEM_TOTAL\n",
       "4    0103    Kisauni           SYSTEM_TOTAL\n",
       "..    ...        ...                    ...\n",
       "399     T      Total  EDUC_COMP_HH_HEAD_VS1\n",
       "400     1        Yes              H19_3_VS1\n",
       "401     2         No              H19_3_VS1\n",
       "402     9         DK              H19_3_VS1\n",
       "403     T      Total              H19_3_VS1\n",
       "\n",
       "[404 rows x 3 columns]"
      ]
     },
     "execution_count": 300,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "codes['cod_list'].replace(regex=True,inplace=True,to_replace=r'\\n',value=r'')\n",
    "codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 301,
   "id": "a43c9efa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7\n",
      "4\n"
     ]
    }
   ],
   "source": [
    "all_codes = []\n",
    "all_codes.append(area_codes)\n",
    "\n",
    "for i, dim in enumerate(dimension[1:]):\n",
    "    #print(i, dim)\n",
    "    arr_cod = []\n",
    "    for arr in all_values[i+1]:\n",
    "\n",
    "        d =  ''\n",
    "        result = codes.loc[(codes['cod_list'] == dim) & (codes['desc'] == arr)]\n",
    "        try:\n",
    "            d = result['cod'].values[0]\n",
    "            arr_cod.append(d)\n",
    "        except:\n",
    "            pass\n",
    "    print(len(arr_cod))\n",
    "    all_codes.append(arr_cod)\n",
    "        \n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "id": "4994310b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11004\n"
     ]
    }
   ],
   "source": [
    "result = list(itertools.product(*all_codes))\n",
    "\n",
    "print(len(result))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 303,
   "id": "484a8812",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 11004 entries, 0 to 11003\n",
      "Data columns (total 3 columns):\n",
      " #   Column                 Non-Null Count  Dtype \n",
      "---  ------                 --------------  ----- \n",
      " 0   SYSTEM_TOTAL           11004 non-null  object\n",
      " 1   EDUC_COMP_HH_HEAD_VS1  11004 non-null  object\n",
      " 2   H19_3_VS1              11004 non-null  object\n",
      "dtypes: object(3)\n",
      "memory usage: 258.0+ KB\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame(result)\n",
    "df.columns = dimension\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "id": "6045150f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11004"
      ]
     },
     "execution_count": 304,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(all_rows)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 305,
   "id": "49cc0f76",
   "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>SYSTEM_TOTAL</th>\n",
       "      <th>EDUC_COMP_HH_HEAD_VS1</th>\n",
       "      <th>H19_3_VS1</th>\n",
       "      <th>VALUES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>12040701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00</td>\n",
       "      <td>T</td>\n",
       "      <td>1</td>\n",
       "      <td>23519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00</td>\n",
       "      <td>T</td>\n",
       "      <td>2</td>\n",
       "      <td>11992666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00</td>\n",
       "      <td>T</td>\n",
       "      <td>9</td>\n",
       "      <td>24516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00</td>\n",
       "      <td>1</td>\n",
       "      <td>T</td>\n",
       "      <td>2130090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10999</th>\n",
       "      <td>4711</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11000</th>\n",
       "      <td>4711</td>\n",
       "      <td>6</td>\n",
       "      <td>T</td>\n",
       "      <td>1488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11001</th>\n",
       "      <td>4711</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11002</th>\n",
       "      <td>4711</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>1472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003</th>\n",
       "      <td>4711</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11004 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      SYSTEM_TOTAL EDUC_COMP_HH_HEAD_VS1 H19_3_VS1    VALUES\n",
       "0               00                     T         T  12040701\n",
       "1               00                     T         1     23519\n",
       "2               00                     T         2  11992666\n",
       "3               00                     T         9     24516\n",
       "4               00                     1         T   2130090\n",
       "...            ...                   ...       ...       ...\n",
       "10999         4711                     5         9        20\n",
       "11000         4711                     6         T      1488\n",
       "11001         4711                     6         1          \n",
       "11002         4711                     6         2      1472\n",
       "11003         4711                     6         9        16\n",
       "\n",
       "[11004 rows x 4 columns]"
      ]
     },
     "execution_count": 305,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['VALUES'] = all_rows\n",
    "df['VALUES'] = df['VALUES'].str.replace(',000000000', '')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "id": "e06a875a",
   "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>cod</th>\n",
       "      <th>desc</th>\n",
       "      <th>cod_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00</td>\n",
       "      <td>Kenya</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>Mombasa</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0101</td>\n",
       "      <td>Changamwe</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0102</td>\n",
       "      <td>Jomvu</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0103</td>\n",
       "      <td>Kisauni</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>T</td>\n",
       "      <td>Total</td>\n",
       "      <td>EDUC_COMP_HH_HEAD_VS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>400</th>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "      <td>H19_3_VS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>401</th>\n",
       "      <td>2</td>\n",
       "      <td>No</td>\n",
       "      <td>H19_3_VS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402</th>\n",
       "      <td>9</td>\n",
       "      <td>DK</td>\n",
       "      <td>H19_3_VS1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>403</th>\n",
       "      <td>T</td>\n",
       "      <td>Total</td>\n",
       "      <td>H19_3_VS1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>404 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      cod       desc               cod_list\n",
       "0      00      Kenya           SYSTEM_TOTAL\n",
       "1      01    Mombasa           SYSTEM_TOTAL\n",
       "2    0101  Changamwe           SYSTEM_TOTAL\n",
       "3    0102      Jomvu           SYSTEM_TOTAL\n",
       "4    0103    Kisauni           SYSTEM_TOTAL\n",
       "..    ...        ...                    ...\n",
       "399     T      Total  EDUC_COMP_HH_HEAD_VS1\n",
       "400     1        Yes              H19_3_VS1\n",
       "401     2         No              H19_3_VS1\n",
       "402     9         DK              H19_3_VS1\n",
       "403     T      Total              H19_3_VS1\n",
       "\n",
       "[404 rows x 3 columns]"
      ]
     },
     "execution_count": 306,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 307,
   "id": "2bc13d2c",
   "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>cod</th>\n",
       "      <th>desc</th>\n",
       "      <th>cod_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00</td>\n",
       "      <td>Kenya</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>Mombasa</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0101</td>\n",
       "      <td>Changamwe</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0102</td>\n",
       "      <td>Jomvu</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0103</td>\n",
       "      <td>Kisauni</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>4707</td>\n",
       "      <td>Makadara</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td>4708</td>\n",
       "      <td>Mathare</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td>4709</td>\n",
       "      <td>Njiru</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391</th>\n",
       "      <td>4710</td>\n",
       "      <td>Starehe</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>4711</td>\n",
       "      <td>Westlands</td>\n",
       "      <td>SYSTEM_TOTAL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>393 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      cod       desc      cod_list\n",
       "0      00      Kenya  SYSTEM_TOTAL\n",
       "1      01    Mombasa  SYSTEM_TOTAL\n",
       "2    0101  Changamwe  SYSTEM_TOTAL\n",
       "3    0102      Jomvu  SYSTEM_TOTAL\n",
       "4    0103    Kisauni  SYSTEM_TOTAL\n",
       "..    ...        ...           ...\n",
       "388  4707   Makadara  SYSTEM_TOTAL\n",
       "389  4708    Mathare  SYSTEM_TOTAL\n",
       "390  4709      Njiru  SYSTEM_TOTAL\n",
       "391  4710    Starehe  SYSTEM_TOTAL\n",
       "392  4711  Westlands  SYSTEM_TOTAL\n",
       "\n",
       "[393 rows x 3 columns]"
      ]
     },
     "execution_count": 307,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d = codes.loc[codes['cod_list']=='SYSTEM_TOTAL']\n",
    "d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 308,
   "id": "a3e19dae",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SYSTEM_TOTAL\n",
      "EDUC_COMP_HH_HEAD_VS1\n",
      "H19_3_VS1\n"
     ]
    },
    {
     "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>SYSTEM_TOTAL</th>\n",
       "      <th>EDUC_COMP_HH_HEAD_VS1</th>\n",
       "      <th>H19_3_VS1</th>\n",
       "      <th>VALUES</th>\n",
       "      <th>DESC_SYSTEM_TOTAL</th>\n",
       "      <th>DESC_EDUC_COMP_HH_HEAD_VS1</th>\n",
       "      <th>DESC_H19_3_VS1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>12040701</td>\n",
       "      <td>Kenya</td>\n",
       "      <td>Total</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>376212</td>\n",
       "      <td>Mombasa</td>\n",
       "      <td>Total</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0101</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>46437</td>\n",
       "      <td>Changamwe</td>\n",
       "      <td>Total</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0102</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>53209</td>\n",
       "      <td>Jomvu</td>\n",
       "      <td>Total</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0103</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>88128</td>\n",
       "      <td>Kisauni</td>\n",
       "      <td>Total</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10999</th>\n",
       "      <td>4707</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>75</td>\n",
       "      <td>Makadara</td>\n",
       "      <td>Not Stated/DK</td>\n",
       "      <td>DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11000</th>\n",
       "      <td>4708</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>Mathare</td>\n",
       "      <td>Not Stated/DK</td>\n",
       "      <td>DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11001</th>\n",
       "      <td>4709</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>Njiru</td>\n",
       "      <td>Not Stated/DK</td>\n",
       "      <td>DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11002</th>\n",
       "      <td>4710</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>Starehe</td>\n",
       "      <td>Not Stated/DK</td>\n",
       "      <td>DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003</th>\n",
       "      <td>4711</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>16</td>\n",
       "      <td>Westlands</td>\n",
       "      <td>Not Stated/DK</td>\n",
       "      <td>DK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11004 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      SYSTEM_TOTAL EDUC_COMP_HH_HEAD_VS1 H19_3_VS1    VALUES  \\\n",
       "0               00                     T         T  12040701   \n",
       "1               01                     T         T    376212   \n",
       "2             0101                     T         T     46437   \n",
       "3             0102                     T         T     53209   \n",
       "4             0103                     T         T     88128   \n",
       "...            ...                   ...       ...       ...   \n",
       "10999         4707                     6         9        75   \n",
       "11000         4708                     6         9         3   \n",
       "11001         4709                     6         9        27   \n",
       "11002         4710                     6         9        11   \n",
       "11003         4711                     6         9        16   \n",
       "\n",
       "      DESC_SYSTEM_TOTAL DESC_EDUC_COMP_HH_HEAD_VS1 DESC_H19_3_VS1  \n",
       "0                 Kenya                      Total          Total  \n",
       "1               Mombasa                      Total          Total  \n",
       "2             Changamwe                      Total          Total  \n",
       "3                 Jomvu                      Total          Total  \n",
       "4               Kisauni                      Total          Total  \n",
       "...                 ...                        ...            ...  \n",
       "10999          Makadara              Not Stated/DK             DK  \n",
       "11000           Mathare              Not Stated/DK             DK  \n",
       "11001             Njiru              Not Stated/DK             DK  \n",
       "11002           Starehe              Not Stated/DK             DK  \n",
       "11003         Westlands              Not Stated/DK             DK  \n",
       "\n",
       "[11004 rows x 7 columns]"
      ]
     },
     "execution_count": 308,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for cod in dimension:\n",
    "    \n",
    "    cod_col = 'DESC_'+cod\n",
    "    print(cod)\n",
    "    d = codes.loc[codes['cod_list']==cod]\n",
    "    df = pd.merge(df, d, how='inner', left_on=cod, right_on='cod')\n",
    "    df = df.drop('cod', axis=1)\n",
    "    df = df.drop('cod_list', axis=1)\n",
    "    df.rename(columns={'desc': cod_col}, inplace=True) \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "id": "bc7d400c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(out_path, index=True, index_label='id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1275b486",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aec9f30a",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}