gridded_data_tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "382991c9-937f-4df0-bac1-26fe02d732e2",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Handling Grid-Data from the German Census – A Beginners' Tutorial  \n",
    "We're using Python and Pandas to preprocess the csv-files offered from the official website. You could use R or any other suitable software as well but files are too large for spreadsheet programmes like Excel oder LibreOffice Calc. An example using the open source software [QGIS](https://qgis.org) is provided as well.\n",
    "\n",
    "You may want to familiarize yourself with the data beforehand. There is an interactive atlas with some choropleth maps for the 1km-grid at https://atlas.zensus2011.de/  \n",
    "\n",
    "Grid-based data can be downloaded from \n",
    "https://www.zensus2011.de/DE/Home/Aktuelles/DemografischeGrunddaten.html?nn=559100#Gitter  \n",
    "each ZIP-file contains the csv and an Excel spreadsheet with documentation (in German)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ef9b0c54-7ba1-4883-81da-a845311c0709",
   "metadata": {},
   "outputs": [],
   "source": [
    "# the following code has been tested with Pandas version 1.5.2\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9b3b3a4-4b65-488e-af25-170999218a32",
   "metadata": {},
   "source": [
    "## Download and prepare the data\n",
    "\n",
    "### Example: Dwellings from the 2011 German Census, 100m-Grid\n",
    "\n",
    "Data on the 100m-grid for the whole of Germany can result in quite large filesizes.  \n",
    "You may therefore keep the files as zipped archives.   \n",
    "\n",
    "Download the file `csv_Wohnungen_100m_Gitter.zip` from  \n",
    "https://www.zensus2011.de/SharedDocs/Downloads/DE/Pressemitteilung/DemografischeGrunddaten/csv_Wohnungen_100m_Gitter.zip?__blob=publicationFile&v=2  \n",
    "this is a 256 MB download, make sure it has completed and keep it in your current directory \n",
    "\n",
    "The above ZIP archive contains two files  \n",
    "`Datensatzbeschreibung_Gitter100m_GebWhg.xlsx` : Documentation of the csv in German (100 kB)  \n",
    "`Wohnungen100m.csv` : the data (**4.6 GB**)  \n",
    "\n",
    "csv-files from official german sources may come in encodings other than `utf-8` in this case it is `windows 1252` which pandas can handle as long as we tell it to do so"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "596ee8d1-f67c-45df-bbda-454cc2fbf692",
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://stackoverflow.com/questions/44575251/reading-multiple-files-contained-in-a-zip-file-with-pandas\n",
    "  \n",
    "from zipfile import ZipFile\n",
    "\n",
    "zip_file = ZipFile('csv_Wohnungen_100m_Gitter.zip')\n",
    "\n",
    "csv_df_dict_from_zip = {text_file.filename: pd.read_csv(zip_file.open(text_file.filename), encoding=\"cp1252\")\n",
    "       for text_file in zip_file.infolist()\n",
    "       if text_file.filename.endswith('.csv')}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "68b706e4-3919-4aad-abea-8093edbf7a70",
   "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>Gitter_ID_100m</th>\n",
       "      <th>Gitter_ID_100m_neu</th>\n",
       "      <th>Merkmal</th>\n",
       "      <th>Auspraegung_Code</th>\n",
       "      <th>Auspraegung_Text</th>\n",
       "      <th>Anzahl</th>\n",
       "      <th>Anzahl_q</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100mN26865E43357</td>\n",
       "      <td>CRS3035RES100mN2686500E4335700</td>\n",
       "      <td>INSGESAMT</td>\n",
       "      <td>0</td>\n",
       "      <td>Einheiten insgesamt</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100mN26865E43357</td>\n",
       "      <td>CRS3035RES100mN2686500E4335700</td>\n",
       "      <td>HEIZTYP</td>\n",
       "      <td>4</td>\n",
       "      <td>Zentralheizung</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100mN26865E43357</td>\n",
       "      <td>CRS3035RES100mN2686500E4335700</td>\n",
       "      <td>WOHNEIGENTUM</td>\n",
       "      <td>99</td>\n",
       "      <td>Trifft nicht zu (da keine Eigentumswohnung)</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100mN26865E43357</td>\n",
       "      <td>CRS3035RES100mN2686500E4335700</td>\n",
       "      <td>ZAHLWOHNGN_HHG</td>\n",
       "      <td>1</td>\n",
       "      <td>1 Wohnung</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100mN26891E43370</td>\n",
       "      <td>CRS3035RES100mN2689100E4337000</td>\n",
       "      <td>INSGESAMT</td>\n",
       "      <td>0</td>\n",
       "      <td>Einheiten insgesamt</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100mN26891E43370</td>\n",
       "      <td>CRS3035RES100mN2689100E4337000</td>\n",
       "      <td>BAUJAHR_MZ</td>\n",
       "      <td>3</td>\n",
       "      <td>1949 - 1978</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100mN26891E43370</td>\n",
       "      <td>CRS3035RES100mN2689100E4337000</td>\n",
       "      <td>GEBTYPBAUWEISE</td>\n",
       "      <td>4</td>\n",
       "      <td>Anderer Gebäudetyp</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Gitter_ID_100m              Gitter_ID_100m_neu         Merkmal  \\\n",
       "0  100mN26865E43357  CRS3035RES100mN2686500E4335700       INSGESAMT   \n",
       "1  100mN26865E43357  CRS3035RES100mN2686500E4335700         HEIZTYP   \n",
       "2  100mN26865E43357  CRS3035RES100mN2686500E4335700    WOHNEIGENTUM   \n",
       "3  100mN26865E43357  CRS3035RES100mN2686500E4335700  ZAHLWOHNGN_HHG   \n",
       "4  100mN26891E43370  CRS3035RES100mN2689100E4337000       INSGESAMT   \n",
       "5  100mN26891E43370  CRS3035RES100mN2689100E4337000      BAUJAHR_MZ   \n",
       "6  100mN26891E43370  CRS3035RES100mN2689100E4337000  GEBTYPBAUWEISE   \n",
       "\n",
       "   Auspraegung_Code                             Auspraegung_Text  Anzahl  \\\n",
       "0                 0                          Einheiten insgesamt       3   \n",
       "1                 4                               Zentralheizung       3   \n",
       "2                99  Trifft nicht zu (da keine Eigentumswohnung)       3   \n",
       "3                 1                                    1 Wohnung       3   \n",
       "4                 0                          Einheiten insgesamt       3   \n",
       "5                 3                                  1949 - 1978       3   \n",
       "6                 4                           Anderer Gebäudetyp       3   \n",
       "\n",
       "   Anzahl_q  \n",
       "0         0  \n",
       "1         0  \n",
       "2         0  \n",
       "3         0  \n",
       "4         0  \n",
       "5         0  \n",
       "6         0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the above results in a dictionary of dataframes read from csv-files, in this case just the one\n",
    "csv_df_dict_from_zip['Wohnungen100m.csv'].head(7)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "178665b8-9e5a-4dd9-a43f-2f969dc5d6d6",
   "metadata": {},
   "source": [
    "## Filtering the data\n",
    "\n",
    "### What does the quality flag mean?\n",
    "\n",
    "Due to disclosure control measures data might be more or less reliable.  \n",
    "The quality variable `Anzahl_q` (\"Geheimhaltungskennzeichnung\") denotes \n",
    "\n",
    "`q=0` means no or minimal difference before/after disclosure control  \n",
    "`q=1` means strong difference before/after disclosure control  \n",
    "`q=2` means difference before/after disclosure control not acceptable  \n",
    "\n",
    "Let's get an idea first if this matters for our research:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "eee0634a-9565-4262-bbf6-3ba480b51e48",
   "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>Anzahl_q</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Merkmal</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BAUJAHR_MZ</th>\n",
       "      <td>4154528</td>\n",
       "      <td>98108</td>\n",
       "      <td>716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EIGENTUM</th>\n",
       "      <td>3527069</td>\n",
       "      <td>72519</td>\n",
       "      <td>635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEBAEUDEART_SYS</th>\n",
       "      <td>2904853</td>\n",
       "      <td>208</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEBTYPBAUWEISE</th>\n",
       "      <td>3860539</td>\n",
       "      <td>13595</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEBTYPGROESSE</th>\n",
       "      <td>5402101</td>\n",
       "      <td>19898</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HEIZTYP</th>\n",
       "      <td>3419324</td>\n",
       "      <td>58510</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INSGESAMT</th>\n",
       "      <td>2737253</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUTZUNG_DETAIL_HHGEN</th>\n",
       "      <td>4231895</td>\n",
       "      <td>9272</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RAUMANZAHL</th>\n",
       "      <td>6215813</td>\n",
       "      <td>42418</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WOHNEIGENTUM</th>\n",
       "      <td>3418904</td>\n",
       "      <td>9588</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WOHNFLAECHE_10S</th>\n",
       "      <td>6119868</td>\n",
       "      <td>62660</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZAHLWOHNGN_HHG</th>\n",
       "      <td>4941173</td>\n",
       "      <td>20844</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Anzahl_q                    0      1    2\n",
       "Merkmal                                  \n",
       "BAUJAHR_MZ            4154528  98108  716\n",
       "EIGENTUM              3527069  72519  635\n",
       "GEBAEUDEART_SYS       2904853    208    0\n",
       "GEBTYPBAUWEISE        3860539  13595    0\n",
       "GEBTYPGROESSE         5402101  19898    5\n",
       "HEIZTYP               3419324  58510  269\n",
       "INSGESAMT             2737253      0    0\n",
       "NUTZUNG_DETAIL_HHGEN  4231895   9272    0\n",
       "RAUMANZAHL            6215813  42418    6\n",
       "WOHNEIGENTUM          3418904   9588    0\n",
       "WOHNFLAECHE_10S       6119868  62660   30\n",
       "ZAHLWOHNGN_HHG        4941173  20844   16"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(csv_df_dict_from_zip['Wohnungen100m.csv'][\"Merkmal\"], csv_df_dict_from_zip['Wohnungen100m.csv'][\"Anzahl_q\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1810494-681d-4e24-a53f-1ced6242949e",
   "metadata": {},
   "source": [
    "While this is no big deal and at least the *not acceptable* category (Anzahl_q=2) is negligible, we will opt for the most reliable data here but you can adjust the filter according to your research needs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b55f1aa4-d667-4247-a054-b8411049297c",
   "metadata": {},
   "outputs": [],
   "source": [
    "dwellings100m = csv_df_dict_from_zip['Wohnungen100m.csv'][csv_df_dict_from_zip['Wohnungen100m.csv'][\"Anzahl_q\"]==0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57e80a75-d0e2-456b-a417-f8e59e219629",
   "metadata": {},
   "source": [
    "### Get dominating year of construction of dwellings for grid cells in built-up areas\n",
    "\n",
    "For this example we are only considering the variable `year of construction` (\"BAUJAHR_MZ\").  \n",
    "We therefore pivot the dataframe to have just the dwelling count per year of construction epoch."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7e7ef5e7-d184-4ed2-8851-700bc5da0dc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "dwell100m_YrConstruct = dwellings100m[dwellings100m[\"Merkmal\"]==\"BAUJAHR_MZ\"] \\\n",
    "                        .pivot(index=[\"Gitter_ID_100m\"], columns=[\"Auspraegung_Text\"], values=[\"Anzahl\"])                         "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "07473d6a-70ee-4a60-80de-587b8e03aed6",
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"10\" halign=\"left\">Anzahl</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Auspraegung_Text</th>\n",
       "      <th>1919 - 1948</th>\n",
       "      <th>1949 - 1978</th>\n",
       "      <th>1979 - 1986</th>\n",
       "      <th>1987 - 1990</th>\n",
       "      <th>1991 - 1995</th>\n",
       "      <th>1996 - 2000</th>\n",
       "      <th>2001 - 2004</th>\n",
       "      <th>2005 - 2008</th>\n",
       "      <th>2009 und später</th>\n",
       "      <th>Vor 1919</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gitter_ID_100m</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>100mN26891E43370</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100mN26912E43412</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100mN26917E43411</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Anzahl                                                  \\\n",
       "Auspraegung_Text 1919 - 1948 1949 - 1978 1979 - 1986 1987 - 1990 1991 - 1995   \n",
       "Gitter_ID_100m                                                                 \n",
       "100mN26891E43370         NaN         3.0         NaN         NaN         NaN   \n",
       "100mN26912E43412         NaN         NaN         NaN         NaN         NaN   \n",
       "100mN26917E43411         NaN         3.0         NaN         3.0         NaN   \n",
       "\n",
       "                                                                               \n",
       "Auspraegung_Text 1996 - 2000 2001 - 2004 2005 - 2008 2009 und später Vor 1919  \n",
       "Gitter_ID_100m                                                                 \n",
       "100mN26891E43370         NaN         NaN         NaN             NaN      NaN  \n",
       "100mN26912E43412         NaN         NaN         NaN             NaN      3.0  \n",
       "100mN26917E43411         NaN         NaN         NaN             NaN      NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dwell100m_YrConstruct.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "37040e15-1c54-4419-b3f8-845777b7c1bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "dwell100m_YrConstruct.columns = dwell100m_YrConstruct.columns.droplevel(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "fc9d9e45-5ed5-4331-b9d7-a1d8c3b713f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "dwell100m_YrConstruct.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "05a3e705-0296-40f3-af14-bf0ee02531c2",
   "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>Auspraegung_Text</th>\n",
       "      <th>Gitter_ID_100m</th>\n",
       "      <th>1919 - 1948</th>\n",
       "      <th>1949 - 1978</th>\n",
       "      <th>1979 - 1986</th>\n",
       "      <th>1987 - 1990</th>\n",
       "      <th>1991 - 1995</th>\n",
       "      <th>1996 - 2000</th>\n",
       "      <th>2001 - 2004</th>\n",
       "      <th>2005 - 2008</th>\n",
       "      <th>2009 und später</th>\n",
       "      <th>Vor 1919</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100mN26891E43370</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100mN26912E43412</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100mN26917E43411</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Auspraegung_Text    Gitter_ID_100m  1919 - 1948  1949 - 1978  1979 - 1986  \\\n",
       "0                 100mN26891E43370          NaN          3.0          NaN   \n",
       "1                 100mN26912E43412          NaN          NaN          NaN   \n",
       "2                 100mN26917E43411          NaN          3.0          NaN   \n",
       "\n",
       "Auspraegung_Text  1987 - 1990  1991 - 1995  1996 - 2000  2001 - 2004  \\\n",
       "0                         NaN          NaN          NaN          NaN   \n",
       "1                         NaN          NaN          NaN          NaN   \n",
       "2                         3.0          NaN          NaN          NaN   \n",
       "\n",
       "Auspraegung_Text  2005 - 2008  2009 und später  Vor 1919  \n",
       "0                         NaN              NaN       NaN  \n",
       "1                         NaN              NaN       3.0  \n",
       "2                         NaN              NaN       NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dwell100m_YrConstruct.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "052d8e53-00b9-4bdf-abd6-d22c28704255",
   "metadata": {},
   "outputs": [],
   "source": [
    "# find dominating building age\n",
    "# NA/null values are excluded by default => built-up areas only\n",
    "dwell100m_YrConstruct[\"dominBuildAge\"] = dwell100m_YrConstruct.idxmax(axis=1, numeric_only=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81bb0b86-d69c-4503-b79b-dd677c59621d",
   "metadata": {},
   "source": [
    "`numeric_only` requires Pandas 1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e15b7851-afe7-4017-9810-dcf6efca21f0",
   "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>Auspraegung_Text</th>\n",
       "      <th>Gitter_ID_100m</th>\n",
       "      <th>1919 - 1948</th>\n",
       "      <th>1949 - 1978</th>\n",
       "      <th>1979 - 1986</th>\n",
       "      <th>1987 - 1990</th>\n",
       "      <th>1991 - 1995</th>\n",
       "      <th>1996 - 2000</th>\n",
       "      <th>2001 - 2004</th>\n",
       "      <th>2005 - 2008</th>\n",
       "      <th>2009 und später</th>\n",
       "      <th>Vor 1919</th>\n",
       "      <th>dominBuildAge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1582037</th>\n",
       "      <td>100mN31895E40871</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1949 - 1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1175390</th>\n",
       "      <td>100mN31066E41114</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1919 - 1948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2071644</th>\n",
       "      <td>100mN33584E41602</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1979 - 1986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1370305</th>\n",
       "      <td>100mN31435E41477</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1987 - 1990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1324670</th>\n",
       "      <td>100mN31354E41338</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1949 - 1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>848311</th>\n",
       "      <td>100mN30204E42781</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1949 - 1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1538546</th>\n",
       "      <td>100mN31762E42059</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1949 - 1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2061388</th>\n",
       "      <td>100mN33525E45600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Vor 1919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2187925</th>\n",
       "      <td>100mN34057E45030</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1979 - 1986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1736042</th>\n",
       "      <td>100mN32342E42190</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1949 - 1978</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Auspraegung_Text    Gitter_ID_100m  1919 - 1948  1949 - 1978  1979 - 1986  \\\n",
       "1582037           100mN31895E40871          3.0          5.0          NaN   \n",
       "1175390           100mN31066E41114          4.0          NaN          NaN   \n",
       "2071644           100mN33584E41602          NaN          NaN          4.0   \n",
       "1370305           100mN31435E41477          NaN          3.0          NaN   \n",
       "1324670           100mN31354E41338          NaN         12.0          NaN   \n",
       "848311            100mN30204E42781          NaN          9.0          NaN   \n",
       "1538546           100mN31762E42059          NaN          9.0          3.0   \n",
       "2061388           100mN33525E45600          NaN          NaN          NaN   \n",
       "2187925           100mN34057E45030          NaN          4.0          8.0   \n",
       "1736042           100mN32342E42190          NaN          3.0          NaN   \n",
       "\n",
       "Auspraegung_Text  1987 - 1990  1991 - 1995  1996 - 2000  2001 - 2004  \\\n",
       "1582037                   NaN          NaN          NaN          4.0   \n",
       "1175390                   NaN          3.0          NaN          NaN   \n",
       "2071644                   NaN          NaN          NaN          NaN   \n",
       "1370305                   8.0          3.0          NaN          NaN   \n",
       "1324670                   NaN          NaN          NaN          NaN   \n",
       "848311                    NaN          NaN          NaN          NaN   \n",
       "1538546                   NaN          NaN          NaN          NaN   \n",
       "2061388                   NaN          NaN          NaN          NaN   \n",
       "2187925                   NaN          NaN          3.0          NaN   \n",
       "1736042                   NaN          NaN          NaN          NaN   \n",
       "\n",
       "Auspraegung_Text  2005 - 2008  2009 und später  Vor 1919 dominBuildAge  \n",
       "1582037                   NaN              NaN       4.0   1949 - 1978  \n",
       "1175390                   NaN              NaN       NaN   1919 - 1948  \n",
       "2071644                   NaN              NaN       NaN   1979 - 1986  \n",
       "1370305                   NaN              NaN       NaN   1987 - 1990  \n",
       "1324670                   NaN              NaN       NaN   1949 - 1978  \n",
       "848311                    NaN              NaN       NaN   1949 - 1978  \n",
       "1538546                   NaN              NaN       NaN   1949 - 1978  \n",
       "2061388                   NaN              NaN       3.0      Vor 1919  \n",
       "2187925                   NaN              NaN       NaN   1979 - 1986  \n",
       "1736042                   NaN              NaN       NaN   1949 - 1978  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dwell100m_YrConstruct.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcb0b3b0-f33d-49db-97f0-1890b808c809",
   "metadata": {},
   "source": [
    "### Why is 3 a frequent frequency?\n",
    "This is also due to disclosure control. Apart from overlaying noise to the data, the controlling algorithm makes sure there are always at least 3 of everything. Therefore 2 occurences will be rounded up to 3 and 1 will be rounded down to 0. While this might even out in theory, especially with deeper breakdowns you can expect more 1 to 0 roundings than 2 to 3."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22772f4b-11c9-49fa-bcb3-a6152903c8fa",
   "metadata": {},
   "source": [
    "### Extract x,y-coordinates from grid ID (INSPIRE Standard)\n",
    "Grid cells are referenced by the lower left corner, e.g. `100mN26891E43370`    \n",
    "To get the center coordinates, half the grid width has to be added  \n",
    "grid id uses first 5 digits of North/East coordinates => *100 = metres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "b54e24be-c9cd-431b-9f9a-6cebcecb8f04",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "dwell100m_YrConstruct[\"center_x\"] = dwell100m_YrConstruct[\"Gitter_ID_100m\"].apply(lambda x: x[11:16]+\"50\")\n",
    "dwell100m_YrConstruct[\"center_Y\"] = dwell100m_YrConstruct[\"Gitter_ID_100m\"].apply(lambda y: y[5:10]+\"50\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7e6948a5-e3fa-4bf6-a5a7-a2d9de072291",
   "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>Auspraegung_Text</th>\n",
       "      <th>Gitter_ID_100m</th>\n",
       "      <th>1919 - 1948</th>\n",
       "      <th>1949 - 1978</th>\n",
       "      <th>1979 - 1986</th>\n",
       "      <th>1987 - 1990</th>\n",
       "      <th>1991 - 1995</th>\n",
       "      <th>1996 - 2000</th>\n",
       "      <th>2001 - 2004</th>\n",
       "      <th>2005 - 2008</th>\n",
       "      <th>2009 und später</th>\n",
       "      <th>Vor 1919</th>\n",
       "      <th>dominBuildAge</th>\n",
       "      <th>center_x</th>\n",
       "      <th>center_Y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100mN26891E43370</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1949 - 1978</td>\n",
       "      <td>4337050</td>\n",
       "      <td>2689150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100mN26912E43412</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Vor 1919</td>\n",
       "      <td>4341250</td>\n",
       "      <td>2691250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100mN26917E43411</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1949 - 1978</td>\n",
       "      <td>4341150</td>\n",
       "      <td>2691750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Auspraegung_Text    Gitter_ID_100m  1919 - 1948  1949 - 1978  1979 - 1986  \\\n",
       "0                 100mN26891E43370          NaN          3.0          NaN   \n",
       "1                 100mN26912E43412          NaN          NaN          NaN   \n",
       "2                 100mN26917E43411          NaN          3.0          NaN   \n",
       "\n",
       "Auspraegung_Text  1987 - 1990  1991 - 1995  1996 - 2000  2001 - 2004  \\\n",
       "0                         NaN          NaN          NaN          NaN   \n",
       "1                         NaN          NaN          NaN          NaN   \n",
       "2                         3.0          NaN          NaN          NaN   \n",
       "\n",
       "Auspraegung_Text  2005 - 2008  2009 und später  Vor 1919 dominBuildAge  \\\n",
       "0                         NaN              NaN       NaN   1949 - 1978   \n",
       "1                         NaN              NaN       3.0      Vor 1919   \n",
       "2                         NaN              NaN       NaN   1949 - 1978   \n",
       "\n",
       "Auspraegung_Text center_x center_Y  \n",
       "0                 4337050  2689150  \n",
       "1                 4341250  2691250  \n",
       "2                 4341150  2691750  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dwell100m_YrConstruct.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e9da028-ea85-4bf6-88ae-02c934b6872a",
   "metadata": {},
   "source": [
    "### Output filtered data to csv (64 MB)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "8a0e03e7-d212-4e85-a5ed-05ffd41b3ef7",
   "metadata": {},
   "outputs": [],
   "source": [
    "dwell100m_YrConstruct[[\"center_x\", \"center_y\", \"dominBuildAge\"]].to_csv(\"dwell100m_dominBuildAge.csv\", index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60d952ca-9cd6-4fe6-890d-b8afcec87918",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Building a Choropleth Map from Gridded Data using QGis\n",
    "The following screenshots show QGis version 3.28 on MacOS but the process will work on earlier versions and operating systems just as well.  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0927c413-13d2-4494-8d1f-438a0c755d5e",
   "metadata": {},
   "source": [
    "### Prepare a Background Map\n",
    "\n",
    "To make sense of gridded data we will use mapping tiles from the German Mapping Authority (BKG), which we load as WMS from https://basemap.de (OpenData)\n",
    "\n",
    "Since we want to overlay the census data in different colors to denote certain statistical values, the background map is best used in gray shading only."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "685a2b59-11c8-480e-b3e2-181f8c7501fa",
   "metadata": {},
   "source": [
    "<img src=\"screenshots/01_wms_manager.jpg\" width=\"600\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da75b3df-9e7c-480e-8f8c-10485021e586",
   "metadata": {},
   "source": [
    "1) If you haven't already, add the WMS basemap service https://basemap.de/dienste/wms_capabilities_web_raster.xml \n",
    "2) Connect to the basemap service and choose the desired tileset "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ff82c67-a49c-444e-9277-9d5bb732f762",
   "metadata": {
    "tags": []
   },
   "source": [
    "<img src=\"screenshots/02_basemap.jpg\" width=\"800\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a187273-b42a-45a7-b2e4-c855e30185cd",
   "metadata": {
    "tags": []
   },
   "source": [
    "Since the gridded census data conforms to the INSPIRE standard, its projection is `EPSG:3035` (LAEA). So make sure that the background map is projected that way."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1afb492f-49fb-4ca9-8501-5ebab2b58b94",
   "metadata": {},
   "source": [
    "### Load csv Delimited Text as Point Coordinates"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84ca97e6-1532-4bd3-bb26-fa4a1467d279",
   "metadata": {},
   "source": [
    "<img src=\"screenshots/03_csv_projected_points.jpg\" width=\"800\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c00785c6-7639-4c11-b6d1-face77c808f6",
   "metadata": {},
   "source": [
    "1) In the Data Source Manager select the file `dwell100m_dominBuildAge.csv` in the *Delimited Text* section. Make sure the Geometry Definition is set to *Point Coordinates* and that the `x/y-fields` match the respective data columns\n",
    "2) Set the projection to `EPSG:3035`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47c0715a-ff81-42b3-987b-0567f9e53ed3",
   "metadata": {},
   "source": [
    "### Check if data is loaded correctly\n",
    "By default for each coordinate pair or each row of the dataset a circle symbol is drawn on the map. Because we included only grid-cells that contain dwellings we can check against the underlying map if the built-up areas match. If they don't, make sure the projections of all layers are `EPSG:3035` and that x,y-values are set correctly."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51da9758-8af8-4d7d-ae58-2e9251eeee0a",
   "metadata": {},
   "source": [
    "<img src=\"screenshots/04_default_circle_symbols_match_builtup_area.jpg\" width=\"800\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c30f9b42-a572-43ff-9d43-eeabc2b49148",
   "metadata": {},
   "source": [
    "### Adjust the symbol settings to achieve a contiguous choropleth map"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf3981a2-5473-4616-aafe-52cb2e787da9",
   "metadata": {},
   "source": [
    "<img src=\"screenshots/05_symbol_settings.jpg\" width=\"400\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a2073a9-3ff6-4e12-ab36-008bae82e140",
   "metadata": {},
   "source": [
    "1) Select a square as simple marker\n",
    "2) Set the size of that marker to map units so that it will span 100x100m\n",
    "3) The way we preprocessed the coordinates we need the anchor point of the symbol to be its center\n",
    "4) The symbol should have a solid fill and no stroke"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "102c5bea-5d59-4bab-869e-32050fa9cab6",
   "metadata": {},
   "source": [
    "### Make a Thematic Layer – Adjusting Symbology "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "787a3fca-c5d6-4fc2-b0b3-67eb6405ebf1",
   "metadata": {},
   "source": [
    "<img src=\"screenshots/06_symbology.jpg\" width=\"600\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfdb5c05-96b2-4515-83f9-93d7093b943a",
   "metadata": {},
   "source": [
    "1) We have prepared categorical data and thus choose `Categorized` in the *Symbology* panel\n",
    "2) The value to be mapped in this example is `dominBuildAge` - press *Classify* after selecting the Value Variable to get an overview of the data\n",
    "3) To start simple we only want to color-shade areas that have mainly been `built before 1919`\n",
    "4) Make use of the powerful *Blending modes* provided by QGis that you might know from PhotoShop or the Gimp. In this case `Multiply` results in a glazing effect like a textmarker and underlying structures remain visible without reducing opacity"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bb772b9-f02f-4284-b171-ea133b70bb10",
   "metadata": {},
   "source": [
    "<img src=\"screenshots/07_finish.jpg\" width=\"1000\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b212ef5-79c7-4854-9fd7-d918580755ed",
   "metadata": {},
   "source": [
    "### The End"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}