Bundesamt_für_Statistik_2.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "private_outputs": true,
      "provenance": [],
      "authorship_tag": "ABX9TyN0IcOaaZW7NnpfKN0t3N++",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/RaminParker/bundesamt_fuer_statistik/blob/main/Bundesamt_f%C3%BCr_Statistik_2.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "! pip install feedparser"
      ],
      "metadata": {
        "id": "jEIkGw28a0Ws"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AxLQsA2Saq-X"
      },
      "outputs": [],
      "source": [
        "import feedparser\n",
        "import re\n",
        "import datetime\n",
        "from datetime import timedelta\n",
        "import requests\n",
        "from pathlib import Path"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Genesis API credentials**\n",
        "\n",
        "get yours for free if you haven't already\n",
        "https://www-genesis.destatis.de/genesis/online?Menu=Registrierung"
      ],
      "metadata": {
        "id": "B-vB8Diea8Sj"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Mount your Google Drive on your Google Colab runtime\n",
        "from google.colab import drive\n",
        "drive.mount('/content/drive/')\n",
        "\n",
        "path_to_key = \"/content/drive/MyDrive/Colab Notebooks/configs/configs_statistisches_bundesamt/passwort.txt\" \n",
        "file1 = open(path_to_key, \"r\")\n",
        "passwort = file1.read()\n",
        "\n",
        "path_to_username = \"/content/drive/MyDrive/Colab Notebooks/configs/configs_statistisches_bundesamt/kennung.txt\"\n",
        "file2 = open(path_to_username, \"r\")\n",
        "kennung = file2.read()"
      ],
      "metadata": {
        "id": "6o5nomuRavrj"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# insert your credentials here before you proceed\n",
        "# Genesis Online, Regionaldatenbank and Zensusdatenbank have each their own registration and usernames\n",
        "KENNUNG = kennung\n",
        "PASSWORT = passwort"
      ],
      "metadata": {
        "id": "UeIeyl0VoimM"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# api login test - check credentials first\n",
        "checkUrl = \"https://www-genesis.destatis.de/genesisWS/rest/2020/helloworld/logincheck?username=\" \\\n",
        "            +KENNUNG+ \"&password=\" +PASSWORT\n",
        "\n",
        "response = requests.get(checkUrl, timeout=120)\n",
        "response.json()"
      ],
      "metadata": {
        "id": "dlHv0uprbI5-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Latest updates via Genesis rss feed\n",
        "feed = feedparser.parse(\"https://www-genesis.destatis.de/genesis/online/news?language=de\")"
      ],
      "metadata": {
        "id": "EuZ8KWXibKr1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "today = datetime.datetime.now()\n",
        "today\n",
        "\n",
        "# today = datetime.datetime(2022, 1, 2) # yyyy m d\n",
        "# today"
      ],
      "metadata": {
        "id": "_IkMd5yMbQQ1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "days_past = 1\n",
        "yesterday = (today - timedelta(days=days_past)).strftime(\"%d.%m.%Y\")\n",
        "yesterday"
      ],
      "metadata": {
        "id": "zwZlT6ZBbSuW"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# create and populate list of updated statistics from newsfeed\n",
        "updatedStatistics = []\n",
        "\n",
        "for entry in feed.entries:\n",
        "    \n",
        "    # filter feed to new items of the last 2 days\n",
        "    if (today - datetime.datetime(*entry.published_parsed[:4])).days < days_past:\n",
        "    \n",
        "        myCode = re.findall(r\"\\d{5}\",entry.title)[0] # parse table code\n",
        "        updatedStatistics.append(myCode)\n",
        "        \n",
        "        print(myCode, entry.published, entry.title[17:85])"
      ],
      "metadata": {
        "id": "11HWtMpBbcy1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "len(updatedStatistics)"
      ],
      "metadata": {
        "id": "NSu2jwfPoRCU"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# create and populate list of tables that belong to each updated statistic\n",
        "updatedTables = []\n",
        "\n",
        "for statistic in updatedStatistics:\n",
        "    catUrl = \"https://www-genesis.destatis.de/genesisWS/rest/2020/catalogue/tables2statistic?username=\" \\\n",
        "                +KENNUNG+\"&password=\"+PASSWORT+\"&name=\"+statistic\n",
        "    catResponse = requests.get(catUrl, timeout=120)\n",
        "\n",
        "    for code in catResponse.json()[\"List\"]:\n",
        "        updatedTables.append(code[\"Code\"])\n",
        "\n",
        "print(updatedTables)"
      ],
      "metadata": {
        "id": "01bQCFUKbjBK"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "konsumausgaben = [\"81000\", \"63221\"]\n",
        "einkommen = [\"12211\", \"21421\", \"22151\", \"61351\", \"62361\", \"63121\", \"63221\", \"13311\", \"62321\", \"62361\", \"13111\"]\n",
        "einzelhandel = [\"12211\", \"13111\", \"21211\", \"23611\", \"45212\", \"45341\", \"45211\", \"45212\"]\n",
        "lebensmittel = [\"51000\", \"45341\"]\n",
        "kfz = [\"45214\"]\n",
        "gastgewerbe = [\"45213\"]\n",
        "verbraucherpreise = [\"61351\", \"61111\", \"61241\"]\n",
        "konsumklima = [\"52411\", \"61241\", \"61111\", \"61311\", \"47415\", \"52111\", \"52911\", \"91211\", \"99911\"]\n",
        "sonstiges = [\"91111\", \"31111\", \"32121\", \"42231\", \"43531\", \"81000\"]\n",
        "\n",
        "importantTables = konsumausgaben + einkommen + einzelhandel + lebensmittel + kfz + gastgewerbe + verbraucherpreise + konsumklima + sonstiges\n",
        "importantTables"
      ],
      "metadata": {
        "id": "_4zhpGCypa5b"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Remove duplicates from list\n",
        "importantTables = list(set(importantTables))"
      ],
      "metadata": {
        "id": "S4nNrhtyuIWu"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Apply filter\n",
        "updatedTables = [x for x in updatedTables for y in importantTables if str(y) in x]"
      ],
      "metadata": {
        "id": "CnZ3SEp5nX84"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Where to download (folder name)\n",
        "current_year = str(datetime.datetime.now().year)\n",
        "destination = f\"{current_year}/\"\n",
        "Path(destination).mkdir(parents=True, exist_ok=True)"
      ],
      "metadata": {
        "id": "mbULQO7Vx5wv"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Download XLSX-files for each updated Table Nr\n",
        "tabUrl = \"https://www-genesis.destatis.de/genesisWS/rest/2020/data/tablefile?username=\" \\\n",
        "                +KENNUNG+\"&password=\"+PASSWORT+\"&format=csv&name=\" # format: xlsx\n",
        "# Get Table Title for each updated Table Nr\n",
        "namUrl = \"https://www-genesis.destatis.de/genesisWS/rest/2020/catalogue/tables?username=\" \\\n",
        "                +KENNUNG+\"&password=\"+PASSWORT+\"&selection=\"\n",
        "\n",
        "for name in updatedTables:\n",
        "    # only download tables that have been updated since yesterday\n",
        "    response = requests.get(tabUrl+name+\"&stand=\"+yesterday, timeout=120)\n",
        "    \n",
        "    if response.status_code == 200:\n",
        "        # a statistic may have been updated with values for June but tables with yearly data have not \n",
        "        if re.search(\"Keine aktualisierten Daten vorhanden.\",str(response.content)):\n",
        "            print(name, \"Es liegen keine aktualisierten Daten vor.\")\n",
        "\n",
        "        else:     \n",
        "            # enhance filename with human readable title \n",
        "            metaresp = requests.get(namUrl+name, timeout=120)\n",
        "            # replace characters that are not suitable for filenames \n",
        "            filetitle = metaresp.json()[\"List\"][0][\"Content\"].replace(\":\",\"_\").replace(\"\\n\",\" \").replace(\"/\",\"_\")\n",
        "            # filename (statistics id + title) and directory\n",
        "            with open(destination+name+\"_\"+filetitle+\".csv\", 'wb') as f:\n",
        "                f.write(response.content)"
      ],
      "metadata": {
        "id": "R841tL4MbopL"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "uYTdGksZDs5J"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "RVq_5kWfDs8S"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "mGimE6tuoM5r"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "jq36KxAyoM8s"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "HKoq4FgcoM_8"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "oa_QP-vdj8ZI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "Qqlt9JQlj8b6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "NfFsJHYEj8fT"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}