census_database_toc.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from dotenv import dotenv_values, load_dotenv\n",
    "import requests\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2.1.1'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## German Census Database (Genesis) – Create Table of Contents via API\n",
    "  \n",
    "*creating the complete Excel file may take up to 90 minutes*\n",
    "\n",
    "for full api documentation (in German), see  \n",
    "https://ergebnisse2011.zensus2022.de/datenbank/misc/ZENSUS-Webservices_Einfuehrung.pdf\n",
    "\n",
    "code inspiration thanks to  \n",
    "https://github.com/sjockers/genesis-api-example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set preferred language (de|en)\n",
    "langPref = \"en\"\n",
    "# expand text output\n",
    "pd.set_option('display.max_colwidth', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load credentials from .env file\n",
    "load_dotenv()  \n",
    "USERNAME, PASSWORD = dotenv_values().values()\n",
    "\n",
    "# Set base path for API calls\n",
    "BASE_URL = 'https://ergebnisse2011.zensus2022.de/api/rest/2020/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check login credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'You have been logged in and out successfully!'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hello = requests.get(BASE_URL + 'helloworld/logincheck', params={\n",
    "    'username': USERNAME,\n",
    "    'password': PASSWORD,\n",
    "    'language': langPref\n",
    "})\n",
    "hello.json()[\"Status\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Catalogue Service"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "variables = requests.get(BASE_URL + 'catalogue/variables', params={\n",
    "    'username': USERNAME,\n",
    "    'password': PASSWORD,\n",
    "    'language': langPref,\n",
    "    'pagelength': 200\n",
    "})\n",
    "variableCodes = variables.json()[\"List\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "134"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(variableCodes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Code': 'WHGFL3',\n",
       "  'Content': 'Floor area of the dwelling (20m² intervals)',\n",
       "  'Type': 'Subject',\n",
       "  'Values': '10',\n",
       "  'Information': 'true'},\n",
       " {'Code': 'WHGNZ1',\n",
       "  'Content': 'Type of use of the dwelling',\n",
       "  'Type': 'Subject',\n",
       "  'Values': '5',\n",
       "  'Information': 'true'},\n",
       " {'Code': 'WHGRM2',\n",
       "  'Content': 'Rooms',\n",
       "  'Type': 'Subject',\n",
       "  'Values': '7',\n",
       "  'Information': 'true'}]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "variableCodes[-3:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {},
   "outputs": [],
   "source": [
    "def metaVal(var):\n",
    "\n",
    "    metadata = requests.get(BASE_URL + 'catalogue/values2variable', params={\n",
    "        'username': USERNAME,\n",
    "        'password': PASSWORD,\n",
    "        'language': langPref,\n",
    "        'pagelength': 15000,  # there are 11.340 municipality names as of 2011\n",
    "        'name': var\n",
    "    })\n",
    "    \n",
    "    return(metadata.json()[\"List\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadataValues = pd.DataFrame()\n",
    "\n",
    "for obj in variableCodes:\n",
    "\n",
    "    myList = metaVal(obj[\"Code\"])\n",
    "\n",
    "    try:\n",
    "        len(myList)\n",
    "    except:\n",
    "        myList = [{\"Code\":\"\",\"Content\":\"\"}]\n",
    "    \n",
    "    for item in myList:\n",
    "        \n",
    "        metadataValues = pd.concat([metadataValues, pd.DataFrame({ \"Variable\": obj[\"Code\"], \\\n",
    "                                                                  \"VarLabel\": obj[\"Content\"], \\\n",
    "                                                                  \"Code\": item[\"Code\"], \\\n",
    "                                                                  \"Content\": item[\"Content\"]}, \\\n",
    "                                                                     index=[0])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "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>Variable</th>\n",
       "      <th>VarLabel</th>\n",
       "      <th>Code</th>\n",
       "      <th>Content</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTER1</td>\n",
       "      <td>Age (yearly stages)</td>\n",
       "      <td>ALTERU01</td>\n",
       "      <td>Under 1 year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTER1</td>\n",
       "      <td>Age (yearly stages)</td>\n",
       "      <td>ALTER001</td>\n",
       "      <td>1 year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTER1</td>\n",
       "      <td>Age (yearly stages)</td>\n",
       "      <td>ALTER002</td>\n",
       "      <td>2 years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTER1</td>\n",
       "      <td>Age (yearly stages)</td>\n",
       "      <td>ALTER003</td>\n",
       "      <td>3 years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTER1</td>\n",
       "      <td>Age (yearly stages)</td>\n",
       "      <td>ALTER004</td>\n",
       "      <td>4 years</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>0</th>\n",
       "      <td>WHGRM2</td>\n",
       "      <td>Rooms</td>\n",
       "      <td>RAUM03</td>\n",
       "      <td>3 rooms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WHGRM2</td>\n",
       "      <td>Rooms</td>\n",
       "      <td>RAUM04</td>\n",
       "      <td>4 rooms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WHGRM2</td>\n",
       "      <td>Rooms</td>\n",
       "      <td>RAUM05</td>\n",
       "      <td>5 rooms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WHGRM2</td>\n",
       "      <td>Rooms</td>\n",
       "      <td>RAUM06</td>\n",
       "      <td>6 rooms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WHGRM2</td>\n",
       "      <td>Rooms</td>\n",
       "      <td>RAUM07UM</td>\n",
       "      <td>7 and more rooms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13977 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Variable             VarLabel      Code           Content\n",
       "0    ALTER1  Age (yearly stages)  ALTERU01      Under 1 year\n",
       "0    ALTER1  Age (yearly stages)  ALTER001            1 year\n",
       "0    ALTER1  Age (yearly stages)  ALTER002           2 years\n",
       "0    ALTER1  Age (yearly stages)  ALTER003           3 years\n",
       "0    ALTER1  Age (yearly stages)  ALTER004           4 years\n",
       "..      ...                  ...       ...               ...\n",
       "0    WHGRM2                Rooms    RAUM03           3 rooms\n",
       "0    WHGRM2                Rooms    RAUM04           4 rooms\n",
       "0    WHGRM2                Rooms    RAUM05           5 rooms\n",
       "0    WHGRM2                Rooms    RAUM06           6 rooms\n",
       "0    WHGRM2                Rooms  RAUM07UM  7 and more rooms\n",
       "\n",
       "[13977 rows x 4 columns]"
      ]
     },
     "execution_count": 289,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metadataValues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = requests.get(BASE_URL + 'catalogue/tables', params={\n",
    "    'username': USERNAME,\n",
    "    'password': PASSWORD,\n",
    "    'language': langPref,\n",
    "    'pagelength': 1500\n",
    "})\n",
    "tableCodes = tables.json()[\"List\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "754"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(tableCodes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Code': '6000F-2004',\n",
       "  'Content': 'Families: Size of family nucleus/Type of family nucleus\\n(by family) - Equipment in dwelling/Floor area of the\\ndwelling (20m² intervals)/Rooms',\n",
       "  'Time': '2011-05-09 to 2011-05-09'},\n",
       " {'Code': '6000F-3001',\n",
       "  'Content': 'Families: Size of private household - Senior citizen status\\n- Type of private household (by family)',\n",
       "  'Time': '2011-05-09 to 2011-05-09'},\n",
       " {'Code': '6000F-3002',\n",
       "  'Content': 'Families: Size of family nucleus - Type of family nucleus\\n(by family) - Equipment in dwelling/Floor area of the\\ndwelling (20m² intervals)/Rooms',\n",
       "  'Time': '2011-05-09 to 2011-05-09'}]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tableCodes[-3:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Metadata Service (Variables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "varCodes=[]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "for item in variableCodes:\n",
    "    varCodes.append(item.get(\"Code\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ALTER1', 'ALTGR1', 'ALTGR2', 'ALTGR3', 'ALTGR4']"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "varCodes[0:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "def metaVar(var):\n",
    "\n",
    "    metadata = requests.get(BASE_URL + 'metadata/variable', params={\n",
    "        'username': USERNAME,\n",
    "        'password': PASSWORD,\n",
    "        'language': langPref,\n",
    "        'name': var\n",
    "    })\n",
    "    \n",
    "    return(metadata.json()[\"Object\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadataVariable = pd.DataFrame()\n",
    "\n",
    "for code in varCodes:\n",
    "\n",
    "    myObject = metaVar(code)\n",
    "    \n",
    "    try:\n",
    "        myInformation = myObject[\"Information\"].replace(\"wiki\",\"\").replace(\"\\n\",\" \")\n",
    "    except:\n",
    "        myInformation = \"\"\n",
    "\n",
    "    metadataVariable = pd.concat([metadataVariable, pd.DataFrame({\"Code\": myObject[\"Code\"], \\\n",
    "                                                          \"Content\": myObject[\"Content\"], \\\n",
    "                                                          \"Values\" : myObject[\"Values\"], \\\n",
    "                                                          \"Information\": myInformation, \\\n",
    "                                                          \"Updated\": myObject[\"Updated\"]},\\\n",
    "                                                          index=[0])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "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>Code</th>\n",
       "      <th>Content</th>\n",
       "      <th>Values</th>\n",
       "      <th>Information</th>\n",
       "      <th>Updated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTER1</td>\n",
       "      <td>Age (yearly stages)</td>\n",
       "      <td>101</td>\n",
       "      <td>The age of the person refers to the completed year of life  on 9 May 2011. Broken down into individual years of age.</td>\n",
       "      <td>2022-05-09 15:37:41h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTGR1</td>\n",
       "      <td>Age (five years age groups)</td>\n",
       "      <td>19</td>\n",
       "      <td>The age of the person refers to the completed year of life  on 9 May 2011. This variable indicates a person's age  group for reference date 9 May 2011. The ages are cited in  five years age groups.</td>\n",
       "      <td>2020-10-28 10:42:15h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTGR2</td>\n",
       "      <td>Age (ten years age groups)</td>\n",
       "      <td>9</td>\n",
       "      <td>The age of the person refers to the completed year of life  on 9 May 2011. This variable indicates a person's age  group for reference date 9 May 2011. The ages are cited in  ten years age groups.</td>\n",
       "      <td>2020-10-28 10:42:19h</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Code                      Content Values  \\\n",
       "0  ALTER1          Age (yearly stages)    101   \n",
       "0  ALTGR1  Age (five years age groups)     19   \n",
       "0  ALTGR2   Age (ten years age groups)      9   \n",
       "\n",
       "                                                                                                                                                                                              Information  \\\n",
       "0                                                                                    The age of the person refers to the completed year of life  on 9 May 2011. Broken down into individual years of age.   \n",
       "0   The age of the person refers to the completed year of life  on 9 May 2011. This variable indicates a person's age  group for reference date 9 May 2011. The ages are cited in  five years age groups.   \n",
       "0    The age of the person refers to the completed year of life  on 9 May 2011. This variable indicates a person's age  group for reference date 9 May 2011. The ages are cited in  ten years age groups.   \n",
       "\n",
       "                Updated  \n",
       "0  2022-05-09 15:37:41h  \n",
       "0  2020-10-28 10:42:15h  \n",
       "0  2020-10-28 10:42:19h  "
      ]
     },
     "execution_count": 285,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metadataVariable.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "metadata": {},
   "outputs": [],
   "source": [
    "def metaTab2Var(var):\n",
    "\n",
    "    metadata = requests.get(BASE_URL + 'catalogue/tables2variable', params={\n",
    "        'username': USERNAME,\n",
    "        'password': PASSWORD,\n",
    "        'language': langPref,\n",
    "        'pagelength': 1000,    # variables like GEODL1 occur in almost all tables\n",
    "        'name': var\n",
    "    })\n",
    "\n",
    "    myList = metadata.json()[\"List\"]\n",
    "    \n",
    "    try:\n",
    "        len(myList)\n",
    "    except:\n",
    "        myList = [{\"Code\": \"-\"}]\n",
    "\n",
    "    myTables = []\n",
    "    for item in myList:\n",
    "        myTables.append(item.get(\"Code\"))\n",
    "\n",
    "    return(myTables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 300,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get a list of tables where each variable is available\n",
    "# this might take an hour\n",
    "metadataVariable[\"TableList\"] = metadataVariable[\"Code\"].apply(lambda x: metaTab2Var(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadataVariable[\"TableCount\"] = metadataVariable[\"TableList\"].apply(lambda x: len(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 303,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert list to string for prettier handling in Excel\n",
    "def list2str(myListObj):\n",
    "    myL = pd.Series(myListObj)\n",
    "    return(myL.to_string(index=False).replace(\"\\n\",\", \"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadataVariable[\"TableListStr\"] = metadataVariable[\"TableList\"].apply(lambda x: list2str(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 305,
   "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>Code</th>\n",
       "      <th>Content</th>\n",
       "      <th>Values</th>\n",
       "      <th>Information</th>\n",
       "      <th>Updated</th>\n",
       "      <th>TableList</th>\n",
       "      <th>TableCount</th>\n",
       "      <th>TableListStr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTER1</td>\n",
       "      <td>Age (yearly stages)</td>\n",
       "      <td>101</td>\n",
       "      <td>The age of the person refers to the completed year of life  on 9 May 2011. Broken down into individual years of age.</td>\n",
       "      <td>2022-05-09 15:37:41h</td>\n",
       "      <td>[1000A-1006, 1000A-2007, 1000A-2008, 1000A-2012, 1000A-3010, 2000S-2015, 2000S-2082, 2000S-2086, 2000S-3028, 2000S-3169, 2000S-3173]</td>\n",
       "      <td>11</td>\n",
       "      <td>1000A-1006, 1000A-2007, 1000A-2008, 1000A-2012, 1000A-3010, 2000S-2015, 2000S-2082, 2000S-2086, 2000S-3028, 2000S-3169, 2000S-3173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTGR1</td>\n",
       "      <td>Age (five years age groups)</td>\n",
       "      <td>19</td>\n",
       "      <td>The age of the person refers to the completed year of life  on 9 May 2011. This variable indicates a person's age  group for reference date 9 May 2011. The ages are cited in  five years age groups.</td>\n",
       "      <td>2020-10-28 10:42:15h</td>\n",
       "      <td>[1000A-1004, 1000A-2005, 1000A-2006, 1000A-2012, 1000A-2024, 1000A-2027, 1000A-2028, 1000A-2044, 1000A-3005, 1000A-3011, 1000A-3018, 1000A-3020, 1000A-3026, 1000A-3027, 1000A-3028, 1000A-3040, 1000A-3041, 1000A-3042, 1000A-4005, 1000A-4006, 1000A-4007, 1000A-4012, 1000A-4013, 1000A-4014, 1000A-4015, 1000A-5001, 2000S-2001, 2000S-2006, 2000S-2009, 2000S-2021, 2000S-2023, 2000S-2027, 2000S-2037, 2000S-2077, 2000S-3001, 2000S-3008, 2000S-3009, 2000S-3010, 2000S-3015, 2000S-3018, 2000S-3021, 2000S-3023, 2000S-3041, 2000S-3042, 2000S-3045, 2000S-3046, 2000S-3047, 2000S-3056, 2000S-3057, 2000S-3058, 2000S-3060, 2000S-3065, 2000S-3072, 2000S-3073, 2000S-3162, 2000S-4001, 2000S-4002, 2000S-4010, 2000S-4012, 2000S-4013, 2000S-4014, 2000S-4015, 2000S-4026, 2000S-4027, 2000S-4028, 2000S-4030, 2000S-4031, 2000S-4037, 2000S-4039, 2000S-4042, 2000S-4047, 2000S-4048, 2000S-5001, 2000S-5003, 2000S-5004, 2000S-5006, 2000S-5012]</td>\n",
       "      <td>77</td>\n",
       "      <td>1000A-1004, 1000A-2005, 1000A-2006, 1000A-2012, 1000A-2024, 1000A-2027, 1000A-2028, 1000A-2044, 1000A-3005, 1000A-3011, 1000A-3018, 1000A-3020, 1000A-3026, 1000A-3027, 1000A-3028, 1000A-3040, 1000A-3041, 1000A-3042, 1000A-4005, 1000A-4006, 1000A-4007, 1000A-4012, 1000A-4013, 1000A-4014, 1000A-4015, 1000A-5001, 2000S-2001, 2000S-2006, 2000S-2009, 2000S-2021, 2000S-2023, 2000S-2027, 2000S-2037, 2000S-2077, 2000S-3001, 2000S-3008, 2000S-3009, 2000S-3010, 2000S-3015, 2000S-3018, 2000S-3021, 2000S-3023, 2000S-3041, 2000S-3042, 2000S-3045, 2000S-3046, 2000S-3047, 2000S-3056, 2000S-3057, 2000S-3058, 2000S-3060, 2000S-3065, 2000S-3072, 2000S-3073, 2000S-3162, 2000S-4001, 2000S-4002, 2000S-4010, 2000S-4012, 2000S-4013, 2000S-4014, 2000S-4015, 2000S-4026, 2000S-4027, 2000S-4028, 2000S-4030, 2000S-4031, 2000S-4037, 2000S-4039, 2000S-4042, 2000S-4047, 2000S-4048, 2000S-5001, 2000S-5003, 2000S-5004, 2000S-5006, 2000S-5012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTGR2</td>\n",
       "      <td>Age (ten years age groups)</td>\n",
       "      <td>9</td>\n",
       "      <td>The age of the person refers to the completed year of life  on 9 May 2011. This variable indicates a person's age  group for reference date 9 May 2011. The ages are cited in  ten years age groups.</td>\n",
       "      <td>2020-10-28 10:42:19h</td>\n",
       "      <td>[1000A-1001, 1000A-2001, 1000A-2002, 1000A-2012, 1000A-2022, 1000A-2023, 1000A-2024, 1000A-2043, 1000A-3001, 1000A-3004, 1000A-3005, 1000A-3006, 1000A-3007, 1000A-3009, 1000A-3015, 1000A-3021, 1000A-3022, 1000A-3023, 1000A-3037, 1000A-3038, 1000A-4001, 1000A-4002, 1000A-4003, 1000A-4011, 2000S-2002, 2000S-2005, 2000S-2011, 2000S-2014, 2000S-2020, 2000S-2023, 2000S-2026, 2000S-2034, 2000S-2042, 2000S-2046, 2000S-2049, 2000S-2053, 2000S-2056, 2000S-2059, 2000S-2068, 2000S-2071, 2000S-2075, 2000S-2085, 2000S-2090, 2000S-3002, 2000S-3007, 2000S-3010, 2000S-3019, 2000S-3024, 2000S-3025, 2000S-3026, 2000S-3039, 2000S-3042, 2000S-3046, 2000S-3048, 2000S-3049, 2000S-3053, 2000S-3054, 2000S-3070, 2000S-3071, 2000S-3074, 2000S-3081, 2000S-3082, 2000S-3090, 2000S-3091, 2000S-3097, 2000S-3100, 2000S-3101, 2000S-3104, 2000S-3106, 2000S-3109, 2000S-3111, 2000S-3114, 2000S-3115, 2000S-3131, 2000S-3132, 2000S-3135, 2000S-3145, 2000S-3147, 2000S-3150, 2000S-3153, 2000S-3155, 2000S-3168, 2000S-3176, 2000S-3177, 2000S-3178, 2000S-4003, 2000S-4018, 2000S-4026, 2000S-4032, 2000S-4033, 2000S-4051, 2000S-4052, 2000S-4056, 2000S-4057, 2000S-4063, 2000S-4064, 2000S-4069, 2000S-4071, 2000S-4072, 2000S-4077, ...]</td>\n",
       "      <td>117</td>\n",
       "      <td>1000A-1001, 1000A-2001, 1000A-2002, 1000A-2012, 1000A-2022, 1000A-2023, 1000A-2024, 1000A-2043, 1000A-3001, 1000A-3004, 1000A-3005, 1000A-3006, 1000A-3007, 1000A-3009, 1000A-3015, 1000A-3021, 1000A-3022, 1000A-3023, 1000A-3037, 1000A-3038, 1000A-4001, 1000A-4002, 1000A-4003, 1000A-4011, 2000S-2002, 2000S-2005, 2000S-2011, 2000S-2014, 2000S-2020, 2000S-2023, 2000S-2026, 2000S-2034, 2000S-2042, 2000S-2046, 2000S-2049, 2000S-2053, 2000S-2056, 2000S-2059, 2000S-2068, 2000S-2071, 2000S-2075, 2000S-2085, 2000S-2090, 2000S-3002, 2000S-3007, 2000S-3010, 2000S-3019, 2000S-3024, 2000S-3025, 2000S-3026, 2000S-3039, 2000S-3042, 2000S-3046, 2000S-3048, 2000S-3049, 2000S-3053, 2000S-3054, 2000S-3070, 2000S-3071, 2000S-3074, 2000S-3081, 2000S-3082, 2000S-3090, 2000S-3091, 2000S-3097, 2000S-3100, 2000S-3101, 2000S-3104, 2000S-3106, 2000S-3109, 2000S-3111, 2000S-3114, 2000S-3115, 2000S-3131, 2000S-3132, 2000S-3135, 2000S-3145, 2000S-3147, 2000S-3150, 2000S-3153, 2000S-3155, 2000S-3168, 2000S-3176, 2000S-3177, 2000S-3178, 2000S-4003, 2000S-4018, 2000S-4026, 2000S-4032, 2000S-4033, 2000S-4051, 2000S-4052, 2000S-4056, 2000S-4057, 2000S-4063, 2000S-4064, 2000S-4069, 2000S-4071, 2000S-4072, 2000S-4077, 2000S-4081, 2000S-4094, 2000S-4095, 2000S-4096, 2000S-4103, 2000S-4104, 2000S-4109, 2000S-4110, 2000S-4121, 2000S-5007, 2000S-5014, 2000S-5015, 2000S-5017, 2000S-5021, 2000S-5025, 2000S-5030, 2000S-5032</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Code                      Content Values  \\\n",
       "0  ALTER1          Age (yearly stages)    101   \n",
       "0  ALTGR1  Age (five years age groups)     19   \n",
       "0  ALTGR2   Age (ten years age groups)      9   \n",
       "\n",
       "                                                                                                                                                                                              Information  \\\n",
       "0                                                                                    The age of the person refers to the completed year of life  on 9 May 2011. Broken down into individual years of age.   \n",
       "0   The age of the person refers to the completed year of life  on 9 May 2011. This variable indicates a person's age  group for reference date 9 May 2011. The ages are cited in  five years age groups.   \n",
       "0    The age of the person refers to the completed year of life  on 9 May 2011. This variable indicates a person's age  group for reference date 9 May 2011. The ages are cited in  ten years age groups.   \n",
       "\n",
       "                Updated  \\\n",
       "0  2022-05-09 15:37:41h   \n",
       "0  2020-10-28 10:42:15h   \n",
       "0  2020-10-28 10:42:19h   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               TableList  \\\n",
       "0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   [1000A-1006, 1000A-2007, 1000A-2008, 1000A-2012, 1000A-3010, 2000S-2015, 2000S-2082, 2000S-2086, 2000S-3028, 2000S-3169, 2000S-3173]   \n",
       "0                                                                                                                                                                                                                                                                                           [1000A-1004, 1000A-2005, 1000A-2006, 1000A-2012, 1000A-2024, 1000A-2027, 1000A-2028, 1000A-2044, 1000A-3005, 1000A-3011, 1000A-3018, 1000A-3020, 1000A-3026, 1000A-3027, 1000A-3028, 1000A-3040, 1000A-3041, 1000A-3042, 1000A-4005, 1000A-4006, 1000A-4007, 1000A-4012, 1000A-4013, 1000A-4014, 1000A-4015, 1000A-5001, 2000S-2001, 2000S-2006, 2000S-2009, 2000S-2021, 2000S-2023, 2000S-2027, 2000S-2037, 2000S-2077, 2000S-3001, 2000S-3008, 2000S-3009, 2000S-3010, 2000S-3015, 2000S-3018, 2000S-3021, 2000S-3023, 2000S-3041, 2000S-3042, 2000S-3045, 2000S-3046, 2000S-3047, 2000S-3056, 2000S-3057, 2000S-3058, 2000S-3060, 2000S-3065, 2000S-3072, 2000S-3073, 2000S-3162, 2000S-4001, 2000S-4002, 2000S-4010, 2000S-4012, 2000S-4013, 2000S-4014, 2000S-4015, 2000S-4026, 2000S-4027, 2000S-4028, 2000S-4030, 2000S-4031, 2000S-4037, 2000S-4039, 2000S-4042, 2000S-4047, 2000S-4048, 2000S-5001, 2000S-5003, 2000S-5004, 2000S-5006, 2000S-5012]   \n",
       "0  [1000A-1001, 1000A-2001, 1000A-2002, 1000A-2012, 1000A-2022, 1000A-2023, 1000A-2024, 1000A-2043, 1000A-3001, 1000A-3004, 1000A-3005, 1000A-3006, 1000A-3007, 1000A-3009, 1000A-3015, 1000A-3021, 1000A-3022, 1000A-3023, 1000A-3037, 1000A-3038, 1000A-4001, 1000A-4002, 1000A-4003, 1000A-4011, 2000S-2002, 2000S-2005, 2000S-2011, 2000S-2014, 2000S-2020, 2000S-2023, 2000S-2026, 2000S-2034, 2000S-2042, 2000S-2046, 2000S-2049, 2000S-2053, 2000S-2056, 2000S-2059, 2000S-2068, 2000S-2071, 2000S-2075, 2000S-2085, 2000S-2090, 2000S-3002, 2000S-3007, 2000S-3010, 2000S-3019, 2000S-3024, 2000S-3025, 2000S-3026, 2000S-3039, 2000S-3042, 2000S-3046, 2000S-3048, 2000S-3049, 2000S-3053, 2000S-3054, 2000S-3070, 2000S-3071, 2000S-3074, 2000S-3081, 2000S-3082, 2000S-3090, 2000S-3091, 2000S-3097, 2000S-3100, 2000S-3101, 2000S-3104, 2000S-3106, 2000S-3109, 2000S-3111, 2000S-3114, 2000S-3115, 2000S-3131, 2000S-3132, 2000S-3135, 2000S-3145, 2000S-3147, 2000S-3150, 2000S-3153, 2000S-3155, 2000S-3168, 2000S-3176, 2000S-3177, 2000S-3178, 2000S-4003, 2000S-4018, 2000S-4026, 2000S-4032, 2000S-4033, 2000S-4051, 2000S-4052, 2000S-4056, 2000S-4057, 2000S-4063, 2000S-4064, 2000S-4069, 2000S-4071, 2000S-4072, 2000S-4077, ...]   \n",
       "\n",
       "   TableCount  \\\n",
       "0          11   \n",
       "0          77   \n",
       "0         117   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 TableListStr  \n",
       "0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          1000A-1006, 1000A-2007, 1000A-2008, 1000A-2012, 1000A-3010, 2000S-2015, 2000S-2082, 2000S-2086, 2000S-3028, 2000S-3169, 2000S-3173  \n",
       "0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  1000A-1004, 1000A-2005, 1000A-2006, 1000A-2012, 1000A-2024, 1000A-2027, 1000A-2028, 1000A-2044, 1000A-3005, 1000A-3011, 1000A-3018, 1000A-3020, 1000A-3026, 1000A-3027, 1000A-3028, 1000A-3040, 1000A-3041, 1000A-3042, 1000A-4005, 1000A-4006, 1000A-4007, 1000A-4012, 1000A-4013, 1000A-4014, 1000A-4015, 1000A-5001, 2000S-2001, 2000S-2006, 2000S-2009, 2000S-2021, 2000S-2023, 2000S-2027, 2000S-2037, 2000S-2077, 2000S-3001, 2000S-3008, 2000S-3009, 2000S-3010, 2000S-3015, 2000S-3018, 2000S-3021, 2000S-3023, 2000S-3041, 2000S-3042, 2000S-3045, 2000S-3046, 2000S-3047, 2000S-3056, 2000S-3057, 2000S-3058, 2000S-3060, 2000S-3065, 2000S-3072, 2000S-3073, 2000S-3162, 2000S-4001, 2000S-4002, 2000S-4010, 2000S-4012, 2000S-4013, 2000S-4014, 2000S-4015, 2000S-4026, 2000S-4027, 2000S-4028, 2000S-4030, 2000S-4031, 2000S-4037, 2000S-4039, 2000S-4042, 2000S-4047, 2000S-4048, 2000S-5001, 2000S-5003, 2000S-5004, 2000S-5006, 2000S-5012  \n",
       "0  1000A-1001, 1000A-2001, 1000A-2002, 1000A-2012, 1000A-2022, 1000A-2023, 1000A-2024, 1000A-2043, 1000A-3001, 1000A-3004, 1000A-3005, 1000A-3006, 1000A-3007, 1000A-3009, 1000A-3015, 1000A-3021, 1000A-3022, 1000A-3023, 1000A-3037, 1000A-3038, 1000A-4001, 1000A-4002, 1000A-4003, 1000A-4011, 2000S-2002, 2000S-2005, 2000S-2011, 2000S-2014, 2000S-2020, 2000S-2023, 2000S-2026, 2000S-2034, 2000S-2042, 2000S-2046, 2000S-2049, 2000S-2053, 2000S-2056, 2000S-2059, 2000S-2068, 2000S-2071, 2000S-2075, 2000S-2085, 2000S-2090, 2000S-3002, 2000S-3007, 2000S-3010, 2000S-3019, 2000S-3024, 2000S-3025, 2000S-3026, 2000S-3039, 2000S-3042, 2000S-3046, 2000S-3048, 2000S-3049, 2000S-3053, 2000S-3054, 2000S-3070, 2000S-3071, 2000S-3074, 2000S-3081, 2000S-3082, 2000S-3090, 2000S-3091, 2000S-3097, 2000S-3100, 2000S-3101, 2000S-3104, 2000S-3106, 2000S-3109, 2000S-3111, 2000S-3114, 2000S-3115, 2000S-3131, 2000S-3132, 2000S-3135, 2000S-3145, 2000S-3147, 2000S-3150, 2000S-3153, 2000S-3155, 2000S-3168, 2000S-3176, 2000S-3177, 2000S-3178, 2000S-4003, 2000S-4018, 2000S-4026, 2000S-4032, 2000S-4033, 2000S-4051, 2000S-4052, 2000S-4056, 2000S-4057, 2000S-4063, 2000S-4064, 2000S-4069, 2000S-4071, 2000S-4072, 2000S-4077, 2000S-4081, 2000S-4094, 2000S-4095, 2000S-4096, 2000S-4103, 2000S-4104, 2000S-4109, 2000S-4110, 2000S-4121, 2000S-5007, 2000S-5014, 2000S-5015, 2000S-5017, 2000S-5021, 2000S-5025, 2000S-5030, 2000S-5032  "
      ]
     },
     "execution_count": 305,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metadataVariable.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Metadata Service (Tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "tabCodes=[]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "for item in tableCodes:\n",
    "    tabCodes.append(item.get(\"Code\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['6000F-2002', '6000F-2003', '6000F-2004', '6000F-3001', '6000F-3002']"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tabCodes[-5:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "def metaTab(tab):\n",
    "\n",
    "    metadata = requests.get(BASE_URL + 'metadata/table', params={\n",
    "        'username': USERNAME,\n",
    "        'password': PASSWORD,\n",
    "        'language': langPref,\n",
    "        'name': tab\n",
    "    })\n",
    "    \n",
    "    return(metadata.json()[\"Object\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadataTable = pd.DataFrame()\n",
    "\n",
    "# get metadata for all tables (754 for the 2011 census) - this might take more than an hour\n",
    "\n",
    "for code in tabCodes:\n",
    "\n",
    "    myObject = metaTab(code)\n",
    "    \n",
    "    try:\n",
    "        myCol2= myObject[\"Structure\"][\"Columns\"][1][\"Code\"]\n",
    "    except:\n",
    "        myCol2= \"\"\n",
    "    try:\n",
    "        myCol3= myObject[\"Structure\"][\"Columns\"][2][\"Code\"]\n",
    "    except:\n",
    "        myCol3= \"\"\n",
    "    \n",
    "    try:\n",
    "        myRow2= myObject[\"Structure\"][\"Rows\"][0][\"Structure\"][0][\"Code\"]\n",
    "    except:\n",
    "        myRow2= \"\"     \n",
    "    try:\n",
    "        myRow3= myObject[\"Structure\"][\"Rows\"][0][\"Structure\"][0][\"Structure\"][0][\"Code\"]\n",
    "    except:\n",
    "        myRow3= \"\"\n",
    "    try:\n",
    "        myRow4= myObject[\"Structure\"][\"Rows\"][0][\"Structure\"][0][\"Structure\"][0][\"Structure\"][0][\"Code\"]\n",
    "    except:\n",
    "        myRow4= \"\"\n",
    "    try:\n",
    "        myRow5= myObject[\"Structure\"][\"Rows\"][0][\"Structure\"][0][\"Structure\"][0][\"Structure\"][0][\"Structure\"][0][\"Code\"]\n",
    "    except:\n",
    "        myRow5= \"\"\n",
    "        \n",
    "    metadataTable = pd.concat([metadataTable, pd.DataFrame({\"Code\": myObject[\"Code\"], \\\n",
    "                                                          \"Content\": myObject[\"Content\"], \\\n",
    "                                                          \"Col1\": myObject[\"Structure\"][\"Columns\"][0][\"Code\"], \\\n",
    "                                                          \"Col2\": myCol2, \\\n",
    "                                                          \"Col3\": myCol3, \\\n",
    "                                                          \"Row1\": myObject[\"Structure\"][\"Rows\"][0][\"Code\"], \\\n",
    "                                                          \"Row2\": myRow2, \\\n",
    "                                                          \"Row3\": myRow3, \\\n",
    "                                                          \"Row4\": myRow4, \\\n",
    "                                                          \"Row5\": myRow5, \\\n",
    "                                                          \"Updated\": myObject[\"Updated\"]},\\\n",
    "                                                          index=[0])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "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>Code</th>\n",
       "      <th>Content</th>\n",
       "      <th>Col1</th>\n",
       "      <th>Col2</th>\n",
       "      <th>Col3</th>\n",
       "      <th>Row1</th>\n",
       "      <th>Row2</th>\n",
       "      <th>Row3</th>\n",
       "      <th>Row4</th>\n",
       "      <th>Row5</th>\n",
       "      <th>Updated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000A-0001</td>\n",
       "      <td>Persons: Official population and area (municipalities)</td>\n",
       "      <td>PRS018</td>\n",
       "      <td>FLC001</td>\n",
       "      <td>PRS017</td>\n",
       "      <td>GEOGM1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2022-01-20 08:17:38h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000A-1001</td>\n",
       "      <td>Persons: Age (ten years age groups)</td>\n",
       "      <td>GEODL1</td>\n",
       "      <td>GEOBL1</td>\n",
       "      <td></td>\n",
       "      <td>ALTGR2</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2022-03-21 13:56:14h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000A-1002</td>\n",
       "      <td>Persons: Age (eleven classes of years)</td>\n",
       "      <td>GEODL1</td>\n",
       "      <td>GEOBL1</td>\n",
       "      <td></td>\n",
       "      <td>ALTKL2</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2022-03-21 13:56:14h</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Code                                                 Content    Col1  \\\n",
       "0  1000A-0001  Persons: Official population and area (municipalities)  PRS018   \n",
       "0  1000A-1001                     Persons: Age (ten years age groups)  GEODL1   \n",
       "0  1000A-1002                  Persons: Age (eleven classes of years)  GEODL1   \n",
       "\n",
       "     Col2    Col3    Row1 Row2 Row3 Row4 Row5               Updated  \n",
       "0  FLC001  PRS017  GEOGM1                      2022-01-20 08:17:38h  \n",
       "0  GEOBL1          ALTGR2                      2022-03-21 13:56:14h  \n",
       "0  GEOBL1          ALTKL2                      2022-03-21 13:56:14h  "
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metadataTable.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cross reference which variables can be selected in a given table\n",
    "# will be calculated from previously retrieved data, no api requests\n",
    "\n",
    "metadataVar2Tab = pd.DataFrame()\n",
    "\n",
    "for tcode in tabCodes:\n",
    "\n",
    "    myVarList = []\n",
    "\n",
    "    for vcode in varCodes:\n",
    "\n",
    "        myTableList = metadataVariable[metadataVariable[\"Code\"]== vcode][\"TableList\"].to_list()[0]\n",
    "\n",
    "        if tcode in myTableList:\n",
    "    \n",
    "            myVarList.append(vcode)\n",
    "\n",
    "    metadataVar2Tab = pd.concat([metadataVar2Tab, pd.DataFrame({\"Code2\": tcode, \\\n",
    "                                                          \"VarCount\": len(myVarList), \\\n",
    "                                                          \"VarList\": ', '.join(myVarList) },\\\n",
    "                                                          index=[0])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "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>Code2</th>\n",
       "      <th>VarCount</th>\n",
       "      <th>VarList</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000A-0001</td>\n",
       "      <td>5</td>\n",
       "      <td>FLC001, GEOGM1, PRS017, PRS018, STAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000A-1001</td>\n",
       "      <td>10</td>\n",
       "      <td>ALTGR2, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000A-1002</td>\n",
       "      <td>10</td>\n",
       "      <td>ALTKL2, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000A-1003</td>\n",
       "      <td>10</td>\n",
       "      <td>ALTKL1, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000A-1004</td>\n",
       "      <td>10</td>\n",
       "      <td>ALTGR1, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6000F-2002</td>\n",
       "      <td>11</td>\n",
       "      <td>FAM001, GEOBL1, GEODL1, GEOGM2, GEOLK1, GEORB1, GEOVB3, HSHGR2, HSHSE1, HSHTP1, STAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6000F-2003</td>\n",
       "      <td>10</td>\n",
       "      <td>FAM001, GEOBL1, GEODL1, GEOGM2, GEOLK1, GEORB1, GEOVB3, HSHSE1, HSHTP1, STAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6000F-2004</td>\n",
       "      <td>13</td>\n",
       "      <td>FAM002, FAMGR2, FAMTP1, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, STAG, WHGAS1, WHGFL3, WHGRM2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6000F-3001</td>\n",
       "      <td>11</td>\n",
       "      <td>FAM001, GEOBL1, GEODL1, GEOGM2, GEOLK1, GEORB1, GEOVB3, HSHGR2, HSHSE1, HSHTP1, STAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6000F-3002</td>\n",
       "      <td>13</td>\n",
       "      <td>FAM002, FAMGR2, FAMTP1, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, STAG, WHGAS1, WHGFL3, WHGRM2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>754 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Code2  VarCount  \\\n",
       "0   1000A-0001         5   \n",
       "0   1000A-1001        10   \n",
       "0   1000A-1002        10   \n",
       "0   1000A-1003        10   \n",
       "0   1000A-1004        10   \n",
       "..         ...       ...   \n",
       "0   6000F-2002        11   \n",
       "0   6000F-2003        10   \n",
       "0   6000F-2004        13   \n",
       "0   6000F-3001        11   \n",
       "0   6000F-3002        13   \n",
       "\n",
       "                                                                                                 VarList  \n",
       "0                                                                   FLC001, GEOGM1, PRS017, PRS018, STAG  \n",
       "0                           ALTGR2, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG  \n",
       "0                           ALTKL2, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG  \n",
       "0                           ALTKL1, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG  \n",
       "0                           ALTGR1, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG  \n",
       "..                                                                                                   ...  \n",
       "0                   FAM001, GEOBL1, GEODL1, GEOGM2, GEOLK1, GEORB1, GEOVB3, HSHGR2, HSHSE1, HSHTP1, STAG  \n",
       "0                           FAM001, GEOBL1, GEODL1, GEOGM2, GEOLK1, GEORB1, GEOVB3, HSHSE1, HSHTP1, STAG  \n",
       "0   FAM002, FAMGR2, FAMTP1, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, STAG, WHGAS1, WHGFL3, WHGRM2  \n",
       "0                   FAM001, GEOBL1, GEODL1, GEOGM2, GEOLK1, GEORB1, GEOVB3, HSHGR2, HSHSE1, HSHTP1, STAG  \n",
       "0   FAM002, FAMGR2, FAMTP1, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, STAG, WHGAS1, WHGFL3, WHGRM2  \n",
       "\n",
       "[754 rows x 3 columns]"
      ]
     },
     "execution_count": 237,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metadataVar2Tab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadataTable2 = metadataTable.set_index(\"Code\").join(metadataVar2Tab.set_index(\"Code2\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "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>Content</th>\n",
       "      <th>Col1</th>\n",
       "      <th>Col2</th>\n",
       "      <th>Col3</th>\n",
       "      <th>Row1</th>\n",
       "      <th>Row2</th>\n",
       "      <th>Row3</th>\n",
       "      <th>Row4</th>\n",
       "      <th>Row5</th>\n",
       "      <th>Updated</th>\n",
       "      <th>VarCount</th>\n",
       "      <th>VarList</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>1000A-0001</th>\n",
       "      <td>Persons: Official population and area (municipalities)</td>\n",
       "      <td>PRS018</td>\n",
       "      <td>FLC001</td>\n",
       "      <td>PRS017</td>\n",
       "      <td>GEOGM1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2022-01-20 08:17:38h</td>\n",
       "      <td>5</td>\n",
       "      <td>FLC001, GEOGM1, PRS017, PRS018, STAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000A-1001</th>\n",
       "      <td>Persons: Age (ten years age groups)</td>\n",
       "      <td>GEODL1</td>\n",
       "      <td>GEOBL1</td>\n",
       "      <td></td>\n",
       "      <td>ALTGR2</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2022-03-21 13:56:14h</td>\n",
       "      <td>10</td>\n",
       "      <td>ALTGR2, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000A-1002</th>\n",
       "      <td>Persons: Age (eleven classes of years)</td>\n",
       "      <td>GEODL1</td>\n",
       "      <td>GEOBL1</td>\n",
       "      <td></td>\n",
       "      <td>ALTKL2</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2022-03-21 13:56:14h</td>\n",
       "      <td>10</td>\n",
       "      <td>ALTKL2, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                           Content    Col1  \\\n",
       "Code                                                                         \n",
       "1000A-0001  Persons: Official population and area (municipalities)  PRS018   \n",
       "1000A-1001                     Persons: Age (ten years age groups)  GEODL1   \n",
       "1000A-1002                  Persons: Age (eleven classes of years)  GEODL1   \n",
       "\n",
       "              Col2    Col3    Row1 Row2 Row3 Row4 Row5               Updated  \\\n",
       "Code                                                                           \n",
       "1000A-0001  FLC001  PRS017  GEOGM1                      2022-01-20 08:17:38h   \n",
       "1000A-1001  GEOBL1          ALTGR2                      2022-03-21 13:56:14h   \n",
       "1000A-1002  GEOBL1          ALTKL2                      2022-03-21 13:56:14h   \n",
       "\n",
       "            VarCount  \\\n",
       "Code                   \n",
       "1000A-0001         5   \n",
       "1000A-1001        10   \n",
       "1000A-1002        10   \n",
       "\n",
       "                                                                                 VarList  \n",
       "Code                                                                                      \n",
       "1000A-0001                                          FLC001, GEOGM1, PRS017, PRS018, STAG  \n",
       "1000A-1001  ALTGR2, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG  \n",
       "1000A-1002  ALTKL2, GEOBL1, GEODL1, GEOGM1, GEOLK1, GEORB1, GEOVB1, PRS001, PRS018, STAG  "
      ]
     },
     "execution_count": 268,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metadataTable2.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export to a single styled Excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl.styles import Alignment\n",
    "from openpyxl.styles import DEFAULT_FONT\n",
    "from openpyxl.styles import Font"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "_font = Font(name=\"Arial\", sz=8)\n",
    "defs = {k: setattr(DEFAULT_FONT, k, v) for k, v in _font.__dict__.items()}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(\"census2011_toc_\"+langPref+\".xlsx\") as writer:  \n",
    "\n",
    "    metadataVariable[[\"Code\", \"Content\", \"Values\", \"Information\", \"Updated\", \"TableCount\", \"TableListStr\"]]\\\n",
    "                    .to_excel(writer, sheet_name='variables', index=False)\n",
    "    metadataValues.to_excel(writer, sheet_name='values', index=False)\n",
    "    metadataTable2.to_excel(writer, sheet_name='tables', index=True)\n",
    "\n",
    "    # some styling, could be skipped\n",
    "    \n",
    "    writer.book[\"variables\"].column_dimensions[\"A\"].width = 15\n",
    "    writer.book[\"variables\"].column_dimensions[\"B\"].width = 25\n",
    "    writer.book[\"variables\"].column_dimensions[\"D\"].width = 60\n",
    "    writer.book[\"variables\"].column_dimensions[\"E\"].width = 20\n",
    "    writer.book[\"variables\"].column_dimensions[\"F\"].width = 20\n",
    "    writer.book[\"variables\"].column_dimensions[\"G\"].width = 150\n",
    "\n",
    "    for idx in enumerate(metadataVariable[\"Code\"]):\n",
    "        \n",
    "        writer.book[\"variables\"].row_dimensions[idx[0]+2].height = 50\n",
    "        writer.book[\"variables\"][\"B\"+str(idx[0]+2)].alignment = Alignment(wrap_text=True, vertical=\"bottom\")\n",
    "        writer.book[\"variables\"][\"D\"+str(idx[0]+2)].alignment = Alignment(wrap_text=True, vertical=\"bottom\")\n",
    "        writer.book[\"variables\"][\"G\"+str(idx[0]+2)].alignment = Alignment(wrap_text=True, vertical=\"bottom\")\n",
    "        writer.book[\"variables\"][\"C\"+str(idx[0]+2)].alignment = Alignment(wrap_text=False, horizontal=\"center\")\n",
    "        writer.book[\"variables\"][\"E\"+str(idx[0]+2)].alignment = Alignment(wrap_text=False, horizontal=\"center\")\n",
    "        writer.book[\"variables\"][\"F\"+str(idx[0]+2)].alignment = Alignment(wrap_text=False, horizontal=\"center\")\n",
    "\n",
    "    writer.book[\"values\"].column_dimensions[\"A\"].width = 15\n",
    "    writer.book[\"values\"].column_dimensions[\"B\"].width = 50\n",
    "    writer.book[\"values\"].column_dimensions[\"C\"].width = 25\n",
    "    writer.book[\"values\"].column_dimensions[\"D\"].width = 50\n",
    "\n",
    "    writer.book[\"tables\"].column_dimensions[\"A\"].width = 15\n",
    "    writer.book[\"tables\"].column_dimensions[\"B\"].width = 50\n",
    "    writer.book[\"tables\"].column_dimensions[\"K\"].width = 20\n",
    "    writer.book[\"tables\"].column_dimensions[\"L\"].width = 15\n",
    "    writer.book[\"tables\"].column_dimensions[\"M\"].width = 90\n",
    "\n",
    "    for idx in enumerate(metadataTable[\"Code\"]):\n",
    "        \n",
    "        writer.book[\"tables\"].row_dimensions[idx[0]+2].height = 35\n",
    "        writer.book[\"tables\"][\"A\"+str(idx[0]+2)].alignment = Alignment(wrap_text=False, vertical=\"bottom\")\n",
    "        writer.book[\"tables\"][\"B\"+str(idx[0]+2)].alignment = Alignment(wrap_text=True, vertical=\"bottom\")\n",
    "        writer.book[\"tables\"][\"K\"+str(idx[0]+2)].alignment = Alignment(wrap_text=False, horizontal=\"center\")\n",
    "        writer.book[\"tables\"][\"L\"+str(idx[0]+2)].alignment = Alignment(wrap_text=False, horizontal=\"center\")\n",
    "        writer.book[\"tables\"][\"M\"+str(idx[0]+2)].alignment = Alignment(wrap_text=True, vertical=\"bottom\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}