ABS Mesh Block counts to CSV.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1fd002cf",
   "metadata": {},
   "source": [
    "# ABS 2021 Mesh Block dwelling counts to CSV\n",
    "\n",
    "As of 31 August 2022, the Australian Bureau of Statistics has only released the [2021 census Mesh Block dwelling counts](https://www.abs.gov.au/census/guide-census-data/mesh-block-counts/2021) as an [Excel file](https://www.abs.gov.au/census/guide-census-data/mesh-block-counts/2021/Mesh%20Block%20Counts%2C%202021.xlsx), with data stratified across 12 worksheets.  This is inconvenient for users who wish to link this data with digital boundaries, and not useful for data posterity.  In addition to completing a feedback survey for the ABS, suggesting that a CSV download should be provided (which the Excel file itself suggests is the case in its Explanatory Notes, and was the case with the 2011 and 2016 census releases), I thought it would be useful to take the time to compile these and make them public for myself and others until an official release is produced.\n",
    "\n",
    "Carl Higgs\n",
    "31 August 2022"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c6a3315c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# setup\n",
    "import urllib.request\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2c214749",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('ABS 2021 Mesh Block Counts.xlsx', <http.client.HTTPMessage at 0x2bc1170f8e0>)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the data\n",
    "meshblocks_url = \"https://www.abs.gov.au/census/guide-census-data/mesh-block-counts/2021/Mesh%20Block%20Counts%2C%202021.xlsx\"\n",
    "file = \"ABS 2021 Mesh Block Counts.xlsx\"\n",
    "urllib.request.urlretrieve(meshblocks_url, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4fff60f3",
   "metadata": {},
   "source": [
    "By manually opening up the download, it is confirmed that the Mesh Block counts data is stratified as follows:\n",
    "\n",
    "Sheet      | State or Territory            | Header row | Last row | Footer length\n",
    "-----------|-------------------------------|-----------:|----------|--------------\n",
    "Table 1   | New South Wales               | 7          | 60000    | 4\n",
    "Table 1.1 | New South Wales               | 7          | 52752    | 4\n",
    "Table 2   | Victoria                      | 7          | 60000    | 4\n",
    "Table 2.1 | Victoria                      | 7          | 28753    | 4\n",
    "Table 3   | Queensland                    | 7          | 60000    | 4\n",
    "Table 3.1 | Queensland                    | 7          | 11876    | 4\n",
    "Table 4   | South Australia               | 7          | 28438    | 4\n",
    "Table 5   | Western Australia             | 7          | 43329    | 4\n",
    "Table 6   | Tasmania                      | 7          | 13041    | 4\n",
    "Table 7   | Northern Territory            | 7          | 3368     | 4\n",
    "Table 8   | Australian Capital Territory  | 7          | 6669     | 4\n",
    "Table 9   | Other Territories             | 7          | 143      | 4\n",
    "\n",
    "The table header was on row 7 of each sheet, and contained the following fields:\n",
    "\n",
    "Variable              | Data type\n",
    "----------------------|-----------\n",
    "MB_CODE_2021          | str\n",
    "MB_CATEGORY_NAME_2021 | str\n",
    "AREA_ALBERS_SQKM      | float\n",
    "Dwelling              | int\n",
    "Person                | int\n",
    "State                 | int\n",
    "\n",
    "While the MB_CODE_2021 variable is technically a really big integer (11 digits long; ie. longer than a 32-bit integer can be represented without an [overflow error](https://numpy.org/doc/stable/user/basics.types.html#overflow-errors)); therefore, the ABS represents the Mesh Block code as a string, and so this convention has been maintained in this dataset.  Alternatively, the MB_CODE_2021 category could be represented using a 64-bit integer, and I expect there could be some optimisation benefits in doing that for indexed queries of large databases.  But I won't worry about that here.\n",
    "\n",
    "Some records may have nulls, so [Int64](https://pandas.pydata.org/docs/reference/arrays.html#nullable-integer) data type will be used to represent integers, allowing for these ocurrances to be correctly retained. \n",
    "\n",
    "The last row for each table was checked, with any records going beyond row 60,000 cascading to a subsequent table for that State or Territory.  It was also confirmed that the length of footer on each page was consistently 4 rows.\n",
    "\n",
    "So, we'll create a data structure reflecting the above table, so we can iterate over and compile each State and Territories' Mesh Block records into a single master table for export to CSV.  I'll then upload that to a public repository for myself and others to use, until an official single CSV release becomes available.\n",
    "\n",
    "The data also includes the explanatory note, \"Cells in this table have been randomly adjusted to avoid the release of confidential data.\" and \"© Commonwealth of Australia 2022\"; users should note this, and that this data was made available under a  [Creative Commons Attribution 4.0 International licence](http://creativecommons.org/licenses/by/4.0/) as per https://www.abs.gov.au/website-privacy-copyright-and-disclaimer#copyright-and-creative-commons.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c92f2347",
   "metadata": {},
   "outputs": [],
   "source": [
    "sheets = {\n",
    "\"New South Wales\"               :[{\"sheet_name\":\"Table 1\",\"header\":7,\"skip_footer\":4},{\"sheet_name\":\"Table 1.1\",\"header\":7,\"skip_footer\":4}],\n",
    "\"Victoria\"                      :[{\"sheet_name\":\"Table 2\",\"header\":7,\"skip_footer\":4},{\"sheet_name\":\"Table 2.1\",\"header\":7,\"skip_footer\":4}],\n",
    "\"Queensland\"                    :[{\"sheet_name\":\"Table 3\",\"header\":7,\"skip_footer\":4},{\"sheet_name\":\"Table 3.1\",\"header\":7,\"skip_footer\":4}],\n",
    "\"South Australia\"               :[{\"sheet_name\":\"Table 4\",\"header\":7,\"skip_footer\":4}],\n",
    "\"Western Australia\"             :[{\"sheet_name\":\"Table 5\",\"header\":7,\"skip_footer\":4}],\n",
    "\"Tasmania\"                      :[{\"sheet_name\":\"Table 6\",\"header\":7,\"skip_footer\":4}],\n",
    "\"Northern Territory\"            :[{\"sheet_name\":\"Table 7\",\"header\":7,\"skip_footer\":4}],\n",
    "\"Australian Capital Territory\"  :[{\"sheet_name\":\"Table 8\",\"header\":7,\"skip_footer\":4}],\n",
    "\"Other Territories\"             :[{\"sheet_name\":\"Table 9\",\"header\":7,\"skip_footer\":4}],\n",
    "}\n",
    "column_types = {\n",
    "'MB_CODE_2021'         :'object',\n",
    "'MB_CATEGORY_NAME_2021':'object',\n",
    "'AREA_ALBERS_SQKM'     :'float64',\n",
    "'Dwelling'             :'Int64',\n",
    "'Person'               :'Int64',\n",
    "'State'                :'Int64',\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3da1bf47",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load up Mesh Block count dataframes for each State and Territory\n",
    "dfs = {}\n",
    "for state in sheets.keys():\n",
    "    for s in sheets[state]:\n",
    "        df = pd.read_excel(file, \n",
    "                           sheet_name=s['sheet_name'],\n",
    "                           header=s['header']-1,\n",
    "                           usecols=\"A:F\",\n",
    "                           dtype=column_types,\n",
    "                           skipfooter=s['skip_footer'])\n",
    "        if state not in dfs.keys():\n",
    "            dfs[state]=df.copy()\n",
    "        else:\n",
    "            dfs[state]=dfs[state].append(df.copy())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f454622a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine Mesh Block count dataframes\n",
    "df = pd.concat(dfs)\n",
    "# drop the within-state sequential index\n",
    "df = df.droplevel(1).reset_index()\n",
    "# Rename the State/Territory identifier columns\n",
    "df.columns = ['STATE_NAME_2021'] + list(column_types.keys())[:-1] + ['STATE_CODE_2021']\n",
    "# Re-order the columns so State name comes after Mesh Block Category\n",
    "# State name will be retained so people don't have to mess around looking up State codes\n",
    "df = df[['MB_CODE_2021',\n",
    " 'MB_CATEGORY_NAME_2021',\n",
    " 'STATE_CODE_2021',\n",
    " 'STATE_NAME_2021',\n",
    " 'AREA_ALBERS_SQKM',\n",
    " 'Dwelling',\n",
    " 'Person']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "302b62c9",
   "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>MB_CODE_2021</th>\n",
       "      <th>MB_CATEGORY_NAME_2021</th>\n",
       "      <th>STATE_CODE_2021</th>\n",
       "      <th>STATE_NAME_2021</th>\n",
       "      <th>AREA_ALBERS_SQKM</th>\n",
       "      <th>Dwelling</th>\n",
       "      <th>Person</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000010000</td>\n",
       "      <td>Residential</td>\n",
       "      <td>1</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>0.0209</td>\n",
       "      <td>44</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000021000</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>1</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>0.0829</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000022000</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>1</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>0.0388</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000023000</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>1</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>0.0254</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000024000</td>\n",
       "      <td>Residential</td>\n",
       "      <td>1</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>0.0376</td>\n",
       "      <td>15</td>\n",
       "      <td>22</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>368280</th>\n",
       "      <td>97500000000</td>\n",
       "      <td>Parkland</td>\n",
       "      <td>9</td>\n",
       "      <td>Other Territories</td>\n",
       "      <td>27.6524</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368281</th>\n",
       "      <td>97000001777</td>\n",
       "      <td>SHIPPING</td>\n",
       "      <td>9</td>\n",
       "      <td>Other Territories</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368282</th>\n",
       "      <td>97000002777</td>\n",
       "      <td>SHIPPING</td>\n",
       "      <td>9</td>\n",
       "      <td>Other Territories</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368283</th>\n",
       "      <td>97000003777</td>\n",
       "      <td>SHIPPING</td>\n",
       "      <td>9</td>\n",
       "      <td>Other Territories</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368284</th>\n",
       "      <td>90000009499</td>\n",
       "      <td>NOUSUALRESIDENCE</td>\n",
       "      <td>9</td>\n",
       "      <td>Other Territories</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>368285 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       MB_CODE_2021 MB_CATEGORY_NAME_2021  STATE_CODE_2021    STATE_NAME_2021  \\\n",
       "0       10000010000           Residential                1    New South Wales   \n",
       "1       10000021000            Commercial                1    New South Wales   \n",
       "2       10000022000            Commercial                1    New South Wales   \n",
       "3       10000023000            Commercial                1    New South Wales   \n",
       "4       10000024000           Residential                1    New South Wales   \n",
       "...             ...                   ...              ...                ...   \n",
       "368280  97500000000              Parkland                9  Other Territories   \n",
       "368281  97000001777              SHIPPING                9  Other Territories   \n",
       "368282  97000002777              SHIPPING                9  Other Territories   \n",
       "368283  97000003777              SHIPPING                9  Other Territories   \n",
       "368284  90000009499      NOUSUALRESIDENCE                9  Other Territories   \n",
       "\n",
       "        AREA_ALBERS_SQKM  Dwelling  Person  \n",
       "0                 0.0209        44      63  \n",
       "1                 0.0829         0       0  \n",
       "2                 0.0388         5       6  \n",
       "3                 0.0254         4       4  \n",
       "4                 0.0376        15      22  \n",
       "...                  ...       ...     ...  \n",
       "368280           27.6524        10      11  \n",
       "368281               NaN         0       0  \n",
       "368282               NaN         0       0  \n",
       "368283               NaN         0       0  \n",
       "368284               NaN      <NA>      11  \n",
       "\n",
       "[368285 rows x 7 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "04cb6330",
   "metadata": {},
   "outputs": [],
   "source": [
    "# output national combined Mesh Block counts dataset\n",
    "df.to_csv(f\"{file.split('.')[0]}.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f030bf61",
   "metadata": {},
   "outputs": [],
   "source": [
    "# output state specific Mesh Block counts datasets\n",
    "for state in dfs.keys():\n",
    "    dfs[state].to_csv(f\"{file.split('.')[0]} - {state}.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "321a8269",
   "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>AREA_ALBERS_SQKM</th>\n",
       "      <th>Dwelling</th>\n",
       "      <th>Person</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STATE_NAME_2021</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Australian Capital Territory</th>\n",
       "      <td>2.358133e+03</td>\n",
       "      <td>187063</td>\n",
       "      <td>454369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New South Wales</th>\n",
       "      <td>8.007977e+05</td>\n",
       "      <td>3361910</td>\n",
       "      <td>8069912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northern Territory</th>\n",
       "      <td>1.348134e+06</td>\n",
       "      <td>96348</td>\n",
       "      <td>232509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other Territories</th>\n",
       "      <td>2.557420e+02</td>\n",
       "      <td>2198</td>\n",
       "      <td>4796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queensland</th>\n",
       "      <td>1.730171e+06</td>\n",
       "      <td>2193417</td>\n",
       "      <td>5155857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Australia</th>\n",
       "      <td>9.842314e+05</td>\n",
       "      <td>807782</td>\n",
       "      <td>1781087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tasmania</th>\n",
       "      <td>6.801754e+04</td>\n",
       "      <td>259156</td>\n",
       "      <td>557368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Victoria</th>\n",
       "      <td>2.274962e+05</td>\n",
       "      <td>2809056</td>\n",
       "      <td>6502955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Western Australia</th>\n",
       "      <td>2.526632e+06</td>\n",
       "      <td>1149491</td>\n",
       "      <td>2659569</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              AREA_ALBERS_SQKM  Dwelling   Person\n",
       "STATE_NAME_2021                                                  \n",
       "Australian Capital Territory      2.358133e+03    187063   454369\n",
       "New South Wales                   8.007977e+05   3361910  8069912\n",
       "Northern Territory                1.348134e+06     96348   232509\n",
       "Other Territories                 2.557420e+02      2198     4796\n",
       "Queensland                        1.730171e+06   2193417  5155857\n",
       "South Australia                   9.842314e+05    807782  1781087\n",
       "Tasmania                          6.801754e+04    259156   557368\n",
       "Victoria                          2.274962e+05   2809056  6502955\n",
       "Western Australia                 2.526632e+06   1149491  2659569"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('STATE_NAME_2021')[['AREA_ALBERS_SQKM','Dwelling','Person']].sum()"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}