query_keys_SDMX.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from io import StringIO\n",
    "import json\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pandasdmx as pdsdmx\n",
    "import re\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# API headers (compress response)\n",
    "comp_head = {\"Accept-Encoding\": \"gzip\"}\n",
    "# call to Unicef WH\n",
    "Unicef = pdsdmx.Request(\"UNICEF\", backend=\"memory\", headers=comp_head)\n",
    "\n",
    "agency = \"ECARO\"\n",
    "# dataflow\n",
    "dfd = \"TRANSMONEE\"\n",
    "dsd_name = f\"DSD_{agency}_{dfd}\"\n",
    "# call to TMEE DSD\n",
    "Dsd_tm = Unicef.datastructure(dsd_name, provider=agency)\n",
    "\n",
    "# indicators dictionary in DSD response: components position 1\n",
    "tm_indicators = (\n",
    "    Dsd_tm.structure['DSD_ECARO_TRANSMONEE'].dimensions.components[1]\n",
    "    .local_representation.enumerated.items\n",
    ")\n",
    "# code to label access as:\n",
    "# tm_indicators[code].name.localizations[\"en\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unicef_api_url = \"https://sdmx.data.unicef.org/ws/public/sdmxapi/rest/data/\"\n",
    "rest_param = {\n",
    "    \"detail\": \"serieskeysonly\",\n",
    "    \"format\": \"sdmx-csv\",\n",
    "    \"labels\": \"id\",\n",
    "}\n",
    "# rest_head = {\n",
    "#     **comp_head,\n",
    "#     \"Accept\": \"application/vnd.sdmx.data+csv;version=1.0.0\",\n",
    "# }\n",
    "\n",
    "# tm dimensions\n",
    "tm_db_dim = [\"SEX\", \"AGE\", \"RESIDENCE\", \"WEALTH_QUINTILE\"]\n",
    "\n",
    "# standard disaggregation\n",
    "std_disagg = {\n",
    "    \"SEX\": [\"F\", \"M\"],\n",
    "    \"AGE\": [\"fill me\"],\n",
    "    \"RESIDENCE\": [\"R\", \"U\"],\n",
    "    \"WEALTH_QUINTILE\": [f\"Q{i}\" for i in range(1,6)],\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# loop on indicators\n",
    "ind_query_dict = {}\n",
    "for key in tm_indicators:\n",
    "    ind_keys = requests.get(\n",
    "        url=f\"{unicef_api_url}/{agency},{dfd}/.{key}....\",\n",
    "        params=rest_param,\n",
    "        headers=comp_head,\n",
    "    )\n",
    "    \n",
    "    # requests satisfactory\n",
    "    if ind_keys.status_code == 200:\n",
    "        keys_df = pd.read_csv(StringIO(ind_keys.text))\n",
    "        # disagg\n",
    "        disagg = [dim for dim in tm_db_dim if len(keys_df[dim].unique()) > 1]\n",
    "\n",
    "        if not disagg:\n",
    "            ind_query_dict[key] = {\"TOTAL\": \"...\"}\n",
    "\n",
    "        else:\n",
    "            keys_in_disagg = {dim: keys_df[dim].unique() for dim in disagg}\n",
    "            total_not_in_disagg = {\n",
    "                dim: \"_T\" not in keys_in_disagg[dim] for dim in disagg\n",
    "            }\n",
    "            check_std_disagg = {\n",
    "                dim: (\n",
    "                    not all([code in keys_in_disagg[dim] for code in std_disagg[dim]])\n",
    "                    if dim != \"AGE\"\n",
    "                    else total_not_in_disagg[\"AGE\"]\n",
    "                )\n",
    "                for dim in disagg\n",
    "            }\n",
    "\n",
    "            # total codes if present in disaggregation\n",
    "            total_codes = {\n",
    "                dim: (\"fill me\" if total_not_in_disagg[dim] else \"_T\")\n",
    "                for dim in disagg\n",
    "            }\n",
    "\n",
    "            # update age disaggregation if total contained\n",
    "            if \"AGE\" in disagg:\n",
    "                if total_not_in_disagg[\"AGE\"]:\n",
    "                    std_disagg.update({\"AGE\": [\"fill me\"]})\n",
    "                else:\n",
    "                    std_disagg.update({\n",
    "                        \"AGE\": list(\n",
    "                            np.delete(\n",
    "                                keys_in_disagg[\"AGE\"],\n",
    "                                keys_in_disagg[\"AGE\"] == \"_T\"\n",
    "                            )\n",
    "                        )\n",
    "                    })\n",
    "\n",
    "            # actual disaggregation\n",
    "            actual_disagg = {\n",
    "                dim: (\n",
    "                    [\"fill me\"]\n",
    "                    if check_std_disagg[dim]\n",
    "                    else std_disagg[dim]\n",
    "                )\n",
    "                for dim in check_std_disagg\n",
    "            }\n",
    "\n",
    "            # use actual disaggregation in query\n",
    "            ind_query_dict[key] = {\n",
    "                dim_disagg: \".\".join(\n",
    "                    [\n",
    "                        (\n",
    "                            \"+\".join(actual_disagg[dim])\n",
    "                            if dim == dim_disagg\n",
    "                            else total_codes[dim]\n",
    "                        ) if dim in disagg else \"\"\n",
    "                        for dim in tm_db_dim\n",
    "                    ]\n",
    "                )\n",
    "                for dim_disagg in disagg\n",
    "            }\n",
    "\n",
    "            # total if disaggregation query\n",
    "            ind_query_dict[key].update({\n",
    "                \"TOTAL\": \".\".join([\n",
    "                    total_codes[dim] if dim in disagg else \"\" for dim in tm_db_dim\n",
    "                ])\n",
    "            })\n",
    "\n",
    "    else:\n",
    "        ind_query_dict[key] = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('config_query.json', 'w') as file_write:\n",
    "    json.dump(ind_query_dict, indent=4, sort_keys=True, fp=file_write)\n",
    "# print(json.dumps(ind_query_dict, indent=4, sort_keys=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create dataframe to complete fillings\n",
    "cols = ['Code', 'Name', 'SEX', 'AGE', 'RESIDENCE', 'WEALTH', 'to_fill']\n",
    "to_fill_list = []\n",
    "\n",
    "for key in ind_query_dict:\n",
    "    if np.concatenate([\n",
    "        re.findall(\"fill\", elem)\n",
    "        for elem in ind_query_dict[key].values()\n",
    "    ]).size > 0:        \n",
    "        ind_keys = requests.get(\n",
    "            url=f\"{unicef_api_url}/{agency},{dfd}/.{key}....\",\n",
    "            params=rest_param,\n",
    "            headers=comp_head,\n",
    "        )\n",
    "        keys_df = pd.read_csv(StringIO(ind_keys.text))\n",
    "        \n",
    "        row_in_df = [\n",
    "            key,\n",
    "            tm_indicators[key].name.localizations[\"en\"],\n",
    "            keys_df[\"SEX\"].unique(),\n",
    "            keys_df[\"AGE\"].unique(),\n",
    "            keys_df[\"RESIDENCE\"].unique(),\n",
    "            keys_df[\"WEALTH_QUINTILE\"].unique(),\n",
    "            \"YES\",\n",
    "        ]\n",
    "        \n",
    "        to_fill_list.append(pd.DataFrame({\n",
    "            col: [str(row_in_df[i])] for i, col in enumerate(cols)\n",
    "        }))\n",
    "\n",
    "    elif \"AGE\" in ind_query_dict[key]:\n",
    "        ind_keys = requests.get(\n",
    "            url=f\"{unicef_api_url}/{agency},{dfd}/.{key}....\",\n",
    "            params=rest_param,\n",
    "            headers=comp_head,\n",
    "        )\n",
    "        keys_df = pd.read_csv(StringIO(ind_keys.text))\n",
    "\n",
    "        row_in_df = [\n",
    "            key,\n",
    "            tm_indicators[key].name.localizations[\"en\"],\n",
    "            keys_df[\"SEX\"].unique(),\n",
    "            keys_df[\"AGE\"].unique(),\n",
    "            keys_df[\"RESIDENCE\"].unique(),\n",
    "            keys_df[\"WEALTH_QUINTILE\"].unique(),\n",
    "            \"NO\",\n",
    "        ]\n",
    "        \n",
    "        to_fill_list.append(pd.DataFrame({\n",
    "            col: [str(row_in_df[i])] for i, col in enumerate(cols)\n",
    "        }))\n",
    "\n",
    "df_to_fill = pd.concat(to_fill_list,ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "excel_file = \"indicators_to_action.xlsx\"\n",
    "df_to_fill.to_excel(excel_file, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reshape config query json file\n",
    "filled_json = \"config_query_filled.json\"\n",
    "file_to_read = open(filled_json)\n",
    "filled_queries = json.load(file_to_read)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "config_query_ind = {}\n",
    "for key in filled_queries:\n",
    "    if not((\"TOTAL\" in filled_queries[key]) and (filled_queries[key][\"TOTAL\"] == \"...\")):\n",
    "        config_query_ind[key] = {\n",
    "            dim_disagg: {\n",
    "                dim: filled_queries[key][dim_disagg].split(\".\")[i].split(\"+\")\n",
    "                for i, dim in enumerate(tm_db_dim)\n",
    "                if filled_queries[key][dim_disagg].split(\".\")[i] != \"\"\n",
    "            }\n",
    "            for dim_disagg in filled_queries[key]\n",
    "        }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('config_query_ind.json', 'w') as file_write:\n",
    "    json.dump(config_query_ind, indent=4, sort_keys=True, fp=file_write)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# indicator check on units (using a config call: no possible call on attributes)\n",
    "rest_param_last = {\n",
    "    **rest_param,\n",
    "    \"lastNObservations\": \"1\",\n",
    "}\n",
    "rest_param_last.update({\"detail\": \"full\"})\n",
    "\n",
    "# hard-coded accepted non-numerics: indicators with observations containing \"<|>\"\n",
    "ind_accept_non_num = [\n",
    "    'HVA_EPI_INF_ANN_15-24',\n",
    "    'HVA_EPI_INF_RT_0-14',\n",
    "    'HVA_EPI_INF_RT_10-19',\n",
    "    'HVA_EPI_LHIV_0-19',\n",
    "    'HVA_EPI_LHIV_15-24',\n",
    "    'HVA_PED_ART_CVG',\n",
    "    'HVA_PMTCT_ARV_CVG',\n",
    "    'MG_INTNL_MG_CNTRY_DEST_PS',\n",
    "]\n",
    "\n",
    "for key in filled_queries:\n",
    "    db_call = (\n",
    "        \"TOTAL\" if \"TOTAL\" in filled_queries[key] else next(iter(filled_queries[key]))\n",
    "    )\n",
    "    ind_last_1 = requests.get(\n",
    "        url=f\"{unicef_api_url}/{agency},{dfd}/.{key}.{filled_queries[key][db_call]}\",\n",
    "        params=rest_param_last,\n",
    "        headers=comp_head,\n",
    "    )\n",
    "    ind_last_df = pd.read_csv(StringIO(ind_last_1.text))\n",
    "    if ind_last_df.UNIT_MEASURE.unique()[0] == \"YES_NO\":\n",
    "        if key in config_query_ind:\n",
    "            config_query_ind[key].update({\"DTYPE\": \"str\"})\n",
    "        else:\n",
    "            config_query_ind.update({\n",
    "                key: {\"DTYPE\": \"str\"}\n",
    "            })\n",
    "    elif key in ind_accept_non_num:\n",
    "        if key in config_query_ind:\n",
    "            config_query_ind[key].update({\"DTYPE\": \"str\"})\n",
    "        else:\n",
    "            config_query_ind.update({\n",
    "                key: {\"DTYPE\": \"str\"}\n",
    "            })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# with open('config_query_ind.json', 'w') as file_write:\n",
    "#     json.dump(config_query_ind, indent=4, sort_keys=True, fp=file_write)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use `config_query_ind.json` to check indicators without totals\n",
    "filled_json = \"transmonee_dashboard/src/transmonee_dashboard/assets/indicator_config.json\"\n",
    "file_to_read = open(filled_json)\n",
    "config_json = json.load(file_to_read)\n",
    "\n",
    "for key in config_json:\n",
    "    if \"TOTAL\" not in config_json[key] and not (\"DTYPE\" in config_json[key] and len(config_json[key]) == 1):\n",
    "        print(key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# key = \"PT_CHLD_Y0T4_REG\"\n",
    "# ind_keys = requests.get(\n",
    "#     url=f\"{unicef_api_url}/{agency},{dfd}/.{key}..._T._T\",\n",
    "#     params=rest_param,\n",
    "#     headers=comp_head,\n",
    "# )\n",
    "# keys_df = pd.read_csv(StringIO(ind_keys.text))\n",
    "# len(keys_df.REF_AREA.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # rename column names: only code\n",
    "    # cols = keys_df.columns.values\n",
    "    # ren_dict = {k: v.split(\":\")[0] for k, v in zip(cols, cols)}\n",
    "    # keys_df.rename(columns=ren_dict, inplace=True)\n",
    "    # # retain codes only in dimension columns\n",
    "    # for dim in tm_db_dim:\n",
    "    #     keys_df.loc[:, dim] = keys_df[dim].apply(lambda x: x.split(\":\")[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "            # std_disagg.update({\n",
    "            #     \"AGE\": list(\n",
    "            #         np.delete(keys_in_disagg[\"AGE\"], keys_in_disagg[\"AGE\"] == \"_T\")\n",
    "            #     ) if (\"AGE\" in disagg) and all(total_in_disagg)\n",
    "            #     else []\n",
    "            # })\n",
    "            # ind_query_dict[key] = {\"TOTAL\": []}\n",
    "            # ind_query_dict[key].update({dim: [] for dim in disagg})\n",
    "            \n",
    "            # if all(total_in_disagg):\n",
    "            #     ind_query_dict[key].update({\n",
    "            #         \"TOTAL\": \".\".join(\n",
    "            #             [\"_T\" if dim in disagg else \"\" for dim in tm_db_dim]\n",
    "            #         )\n",
    "            #     })\n",
    "            #     ind_query_dict[key].update({\n",
    "            #         dim_disagg: \".\".join(\n",
    "            #             [\"_T\" if dim in disagg else \"\" for dim in tm_db_dim]\n",
    "            #         )\n",
    "            #     })\n",
    "\n",
    "                # if \"AGE\" in disagg:\n",
    "                #     ind_query_dict[key][\"AGE\"] = list(\n",
    "                #         np.delete(\n",
    "                #             keys_in_disagg[\"AGE\"],\n",
    "                #             keys_in_disagg[\"AGE\"] == \"_T\"\n",
    "                #         )\n",
    "                #     )\n",
    "                \n",
    "                # else:\n",
    "                #     ind_query_dict[key][\"TOTAL\"] = []\n",
    "\n",
    "            # check_std_disagg = {\n",
    "            #     dim: [code in keys_in_disagg[dim] for code in std_disagg[dim]]\n",
    "            #     for dim in disagg\n",
    "            #     if dim != \"AGE\"\n",
    "            # }\n",
    "            # check_disagg_concat = np.concatenate(list(check_std_disagg.values()))"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "949777d72b0d2535278d3dc13498b2535136f6dfe0678499012e853ee9abcab1"
  },
  "kernelspec": {
   "display_name": "Python 3.8.5 64-bit",
   "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.10"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}