{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cell-0",
   "metadata": {
    "editable": false,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "# Aufgabe - Grundlagen (max. 20 Punkte)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-1",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "### 1.1 Aufgabe (Codierung) \n",
    "\n",
    "Wir haben als einfache Möglichkeit zur Darstellung von Bildern die pixelweise Speicherung \n",
    "kennengelernt (*Bitmap Bilder*). Sie möchten nun ein Bild mit halbem Speicherbedarf speichern. \n",
    "\n",
    "Um welchen Faktor müssen Sie die Auflösung in Breite und Länge jeweils reduzieren?\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "cell-2",
   "metadata": {},
   "source": [
    "(x⋅Breite)×(x⋅Höhe) = 1/2 * (Breite * Höhe)\n",
    "x * x = 1/2\n",
    "x = 1 / Quadradwurzel 2\n",
    "\n",
    "Der Faktor ist 0,707"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-3",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "### 1.2 Aufgabe (Speicherung von Daten) \n",
    "Sie befinden sich in dem Verzeichnis `/Users/IHRE_MATRIKELNR/Downloads` ihres Computers (wobei Sie die Matrikelnummer durch ihre eigene Matrikelnummer ersetzen). Sie möchten eine Datei aus diesem Verzeichnis im Verzeichnis `/Users/IHRE_MATRIKELNR/Dateien/hochschule` speichern?\n",
    "\n",
    "\n",
    "Mit welchem relativen Pfad können Sie den Zielpfad erreichen!\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "cell-4",
   "metadata": {},
   "source": [
    "Vom Ausgangspfad muss man ein Verzeichnis aufsteigen (../), in das Unterverzeichnis Dateien absteigen (../Dateien/) und dann noch nach hochschule absteigen (../Dateien/hochschule)\n",
    "\n",
    "../Dateien/hochschule"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-5",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "### 1.3 Aufgabe (Informationssicherheit) \n",
    "\n",
    "Betrachten Sie sich das Zertifikat der Internetseite [https://ibix.fbw.hs-bochum.de](https://ibix.fbw.hs-bochum.de) (nur über VPN erreichbar). Prüfen Sie die wesentlichen Eigenschaften dieses Zertifikats und schließen Sie daraus, ob die Seite sicher besucht werden kann.\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "cell-6",
   "metadata": {},
   "source": [
    "Die Seite kann sicher besucht werden.\n",
    "Das Zertifikat ist zeitlich gültig. Das Zertifikat ist für wiinf.fbw.hs-bochum.de ausgestellt, ibix.fbw.hs-bochum.de ist aber unter den alternativen Namen angegeben."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-7",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "# Aufgabe - grundlegende Python Funktionen (max. 20 Punkte)\n",
    "\n",
    "Sie verleihen Tontechnik und berechnen mit einer Python-Funktion die Preise für den Verleih.\n",
    "\n",
    "Die folgende Tabelle zeigt einen Überblick über ihre Aufträge und die Verleihkosten:\n",
    "\n",
    "<table><tr><th>Kunde</th><th>Personenzahl</th><th>Raumgröße</th><th>Qualität</th><th>Sonderwünsche</th><th>Preis Tontechnik</th></tr><tr><td>Meier</td><td>10020</td><td>6824</td><td>Musik</td><td></td><td>15149.82</td></tr><tr><td>Schneider</td><td>10010</td><td>1475</td><td>Basslastig</td><td>Funkstrecken</td><td>30073.8</td></tr><tr><td>Kohler</td><td>15090</td><td>9822</td><td>Musik</td><td>Funkstrecken</td><td>26342.66</td></tr><tr><td>Meyer</td><td>8640</td><td>6585</td><td>Basslastig</td><td></td><td>22406.16</td></tr><tr><td>Müller</td><td>6930</td><td>6420</td><td>Sprache</td><td></td><td>5497.59</td></tr><tr><td>Schmidt</td><td>8340</td><td>796</td><td>Sprache</td><td>Funkstrecken</td><td>7625.17</td></tr><tr><td>Neumann</td><td>1860</td><td>7891</td><td>Musik</td><td></td><td>2983.26</td></tr><tr><td>Neumann</td><td>11780</td><td>7676</td><td>Musik</td><td>Funkstrecken</td><td>20617.82</td></tr></table>\n",
    "\n",
    "Die Dimensionierung der Tontechnikanlage beruht auf der Personenzahl, die beschallt werden soll. Für je 100 Personen benötigen Sie 68 Watt Musikleistung. Die Kosten für die Musikleistung ergibt sich dann wie folgt:\n",
    "\n",
    "   - Für Qualitaet: Sprache berechnen Sie 76,30 Euro je 68 Watt Musikleistung\n",
    "   - Für  Qualitaet: Musik berechnen Sie 149,10 Euro je 68 Watt Musikleistung\n",
    "   - Für  Qualitaet: Basslastig berechnen Sie 256,90 Euro je 68 Watt Musikleistung\n",
    "\n",
    "Für die Bereitstellung der Tontechnik werden pauschal 210 Euro berechnet.\n",
    "\n",
    "Außerdem gibt es einen Aufschlag von 16 Prozent auf den Gesamtpreis, wenn Funkstrecken für die Tonübertragung verwendet werden sollen.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "cell-8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:52.446811Z",
     "iopub.status.busy": "2025-06-30T08:21:52.446431Z",
     "iopub.status.idle": "2025-06-30T08:21:52.452184Z",
     "shell.execute_reply": "2025-06-30T08:21:52.451387Z",
     "shell.execute_reply.started": "2025-06-30T08:21:52.446779Z"
    }
   },
   "outputs": [],
   "source": [
    "testDaten = [\n",
    "   ('Meier', 10020, 6824, 'Musik', ''),\n",
    "   ('Schneider', 10010, 1475, 'Basslastig', 'Funkstrecken'),\n",
    "   ('Kohler', 15090, 9822, 'Musik', 'Funkstrecken'),\n",
    "   ('Meyer', 8640, 6585, 'Basslastig', ''),\n",
    "   ('Müller', 6930, 6420, 'Sprache', ''),\n",
    "   ('Schmidt', 8340, 796, 'Sprache', 'Funkstrecken'),\n",
    "   ('Neumann', 1860, 7891, 'Musik', ''),\n",
    "   ('Neumann', 11780, 7676, 'Musik', 'Funkstrecken')\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-9",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "### 2.1 Aufgabe \n",
    "\n",
    "Programmieren Sie eine Funktion `berechnePreistontechnik(...)`, der Sie die Anzahl der Besucher, die Musikqualität und die Sonderwünsche übergeben. (Die Datei mit den Beispieldaten enthält die richtigen Ergebnisse.) \n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "cell-10",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:52.735010Z",
     "iopub.status.busy": "2025-06-30T08:21:52.734697Z",
     "iopub.status.idle": "2025-06-30T08:21:52.740115Z",
     "shell.execute_reply": "2025-06-30T08:21:52.739305Z",
     "shell.execute_reply.started": "2025-06-30T08:21:52.734982Z"
    }
   },
   "outputs": [],
   "source": [
    "# Hier ist Platz fuer ihre Loesung zu Aufgabe 2.1:\n",
    "def berechnePreistontechnik(personenzahl, qualitaet, sonderwuensche):\n",
    "    if qualitaet == \"Sprache\":\n",
    "        grundkosten = 76.30\n",
    "    elif qualitaet == \"Musik\":\n",
    "        grundkosten = 149.10\n",
    "    else:\n",
    "        grundkosten = 256.90\n",
    "\n",
    "    kosten = personenzahl / 100 * grundkosten + 210\n",
    "    if sonderwuensche == \"Funkstrecken\":\n",
    "        kosten = 1.16 * kosten\n",
    "    return kosten"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "cell-11",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:53.238722Z",
     "iopub.status.busy": "2025-06-30T08:21:53.238404Z",
     "iopub.status.idle": "2025-06-30T08:21:53.243646Z",
     "shell.execute_reply": "2025-06-30T08:21:53.242839Z",
     "shell.execute_reply.started": "2025-06-30T08:21:53.238694Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Meier', 10020, 6824, 'Musik', '') : 15149.82\n",
      "('Schneider', 10010, 1475, 'Basslastig', 'Funkstrecken') : 30073.800399999993\n",
      "('Kohler', 15090, 9822, 'Musik', 'Funkstrecken') : 26342.660399999997\n",
      "('Meyer', 8640, 6585, 'Basslastig', '') : 22406.16\n",
      "('Müller', 6930, 6420, 'Sprache', '') : 5497.589999999999\n",
      "('Schmidt', 8340, 796, 'Sprache', 'Funkstrecken') : 7625.1672\n",
      "('Neumann', 1860, 7891, 'Musik', '') : 2983.26\n",
      "('Neumann', 11780, 7676, 'Musik', 'Funkstrecken') : 20617.816799999997\n"
     ]
    }
   ],
   "source": [
    "for tupel in testDaten:\n",
    "    (name, personenzahl, raumgroesse, qualitaet, sonderwuensche) = tupel\n",
    "    print (tupel, \":\", berechnePreistontechnik(personenzahl, qualitaet, sonderwuensche))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-12",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "### 2.2 Aufgabe \n",
    "\n",
    "Programmieren Sie eine Funktion `berechne_statistikwert(...)`, der Sie die\n",
    "Liste von Tupeln entsprechend der obigen Tabelle übergeben. \n",
    "\n",
    "Die Funktion berechnet mit Hilfe einer Schleife die durchschnittliche Raumgröße, \n",
    "die bei Datensätzen mit Funkstrecken beschallt werden soll.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "cell-13",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:53.401650Z",
     "iopub.status.busy": "2025-06-30T08:21:53.401329Z",
     "iopub.status.idle": "2025-06-30T08:21:53.406200Z",
     "shell.execute_reply": "2025-06-30T08:21:53.405516Z",
     "shell.execute_reply.started": "2025-06-30T08:21:53.401621Z"
    }
   },
   "outputs": [],
   "source": [
    "# Hier ist Platz fuer ihre Loesung zu Aufgabe 2.2:\n",
    "def berechne_statistikwert(tupelliste):\n",
    "    summeRaumgroesse = 0\n",
    "    anzahlFunkstrecken = 0\n",
    "    for tupel in tupelliste:\n",
    "        if tupel[4]==\"Funkstrecken\":\n",
    "            summeRaumgroesse=summeRaumgroesse+tupel[2]\n",
    "            anzahlFunkstrecken += 1\n",
    "    return summeRaumgroesse / anzahlFunkstrecken"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "cell-14",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:53.485541Z",
     "iopub.status.busy": "2025-06-30T08:21:53.485234Z",
     "iopub.status.idle": "2025-06-30T08:21:53.491057Z",
     "shell.execute_reply": "2025-06-30T08:21:53.490086Z",
     "shell.execute_reply.started": "2025-06-30T08:21:53.485513Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4942.25"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "berechne_statistikwert(testDaten)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-15",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "# Aufgabe - Datenanalyse (max. 40 Punkte)\n",
    "\n",
    "\n",
    "In dieser Aufgabe geht es erneut um die Tankpreis-Daten. Die Daten zu den Preisen\n",
    "finden sich unter der URL\n",
    "[https://data.hsbo.de/tankpreise_Bochum_2020.csv](https://data.hsbo.de/tankpreise_Bochum_2020.csv). Die Datei enthält eine Liste mit Benzinpreisen an Tankstellen zu verschiedenen Zeitpunkten. Die Tankstellen werden durch eine ´station_uuid´ identifiziert.\n",
    "\n",
    "Weitere Informationen zu den Tankstellen finden Sie in einer weitere Datei, die Sie unter \n",
    "[https://data.hsbo.de/tankstellen.csv](https://data.hsbo.de/tankstellen.csv)\n",
    "finden.\n",
    "\n",
    "\n",
    "Lesen Sie die Daten mit Hilfe von Pandas in zwei DataFrames ein und verschaffen Sie \n",
    "sich einen Überblick über die Spalten und Größe der Tabellen.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "d86db12f-1b8d-4c50-942b-e1720c7a3c7b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:53.652182Z",
     "iopub.status.busy": "2025-06-30T08:21:53.651854Z",
     "iopub.status.idle": "2025-06-30T08:21:54.590089Z",
     "shell.execute_reply": "2025-06-30T08:21:54.589252Z",
     "shell.execute_reply.started": "2025-06-30T08:21:53.652151Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas import Series\n",
    "from pandas import DataFrame\n",
    "\n",
    "preiseDF = pd.read_csv(\"https://data.hsbo.de/tankpreise_Bochum_2020.csv\")\n",
    "tankstellenDF = pd.read_csv(\"https://data.hsbo.de/tankstellen.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "2cbffb01-e206-4e2b-b7d9-74280c22973e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:54.591667Z",
     "iopub.status.busy": "2025-06-30T08:21:54.591360Z",
     "iopub.status.idle": "2025-06-30T08:21:54.601638Z",
     "shell.execute_reply": "2025-06-30T08:21:54.601155Z",
     "shell.execute_reply.started": "2025-06-30T08:21:54.591638Z"
    }
   },
   "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>date</th>\n",
       "      <th>station_uuid</th>\n",
       "      <th>diesel</th>\n",
       "      <th>e5</th>\n",
       "      <th>e10</th>\n",
       "      <th>dieselchange</th>\n",
       "      <th>e5change</th>\n",
       "      <th>e10change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01 02:03:03+01</td>\n",
       "      <td>b7dc5a11-661a-419f-8d30-d9763b2897c7</td>\n",
       "      <td>1.289</td>\n",
       "      <td>1.409</td>\n",
       "      <td>1.369</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-01 04:36:04+01</td>\n",
       "      <td>51d4b638-a095-1aa0-e100-80009459e03a</td>\n",
       "      <td>1.309</td>\n",
       "      <td>1.429</td>\n",
       "      <td>1.409</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-01 04:36:04+01</td>\n",
       "      <td>51d4b671-a095-1aa0-e100-80009459e03a</td>\n",
       "      <td>1.319</td>\n",
       "      <td>1.439</td>\n",
       "      <td>1.399</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01 04:36:04+01</td>\n",
       "      <td>51d4b500-a095-1aa0-e100-80009459e03a</td>\n",
       "      <td>1.319</td>\n",
       "      <td>1.439</td>\n",
       "      <td>1.419</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01 04:36:04+01</td>\n",
       "      <td>cd8b8686-d085-1ed7-a59b-163f66bf05c4</td>\n",
       "      <td>1.319</td>\n",
       "      <td>1.439</td>\n",
       "      <td>1.419</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     date                          station_uuid  diesel  \\\n",
       "0  2020-01-01 02:03:03+01  b7dc5a11-661a-419f-8d30-d9763b2897c7   1.289   \n",
       "1  2020-01-01 04:36:04+01  51d4b638-a095-1aa0-e100-80009459e03a   1.309   \n",
       "2  2020-01-01 04:36:04+01  51d4b671-a095-1aa0-e100-80009459e03a   1.319   \n",
       "3  2020-01-01 04:36:04+01  51d4b500-a095-1aa0-e100-80009459e03a   1.319   \n",
       "4  2020-01-01 04:36:04+01  cd8b8686-d085-1ed7-a59b-163f66bf05c4   1.319   \n",
       "\n",
       "      e5    e10  dieselchange  e5change  e10change  \n",
       "0  1.409  1.369             0         0          1  \n",
       "1  1.429  1.409             1         1          1  \n",
       "2  1.439  1.399             1         1          1  \n",
       "3  1.439  1.419             1         1          1  \n",
       "4  1.439  1.419             1         1          1  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "preiseDF.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "ad852af4-621d-4ac1-a392-ee38c6010b49",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:54.602471Z",
     "iopub.status.busy": "2025-06-30T08:21:54.602248Z",
     "iopub.status.idle": "2025-06-30T08:21:54.615542Z",
     "shell.execute_reply": "2025-06-30T08:21:54.615077Z",
     "shell.execute_reply.started": "2025-06-30T08:21:54.602452Z"
    }
   },
   "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>uuid</th>\n",
       "      <th>name</th>\n",
       "      <th>brand</th>\n",
       "      <th>street</th>\n",
       "      <th>house_number</th>\n",
       "      <th>post_code</th>\n",
       "      <th>city</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00060723-0001-4444-8888-acdc00000001</td>\n",
       "      <td>BAGeno Raiffeisen eG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Künzelsauer Strasse</td>\n",
       "      <td>7</td>\n",
       "      <td>74653</td>\n",
       "      <td>Ingelfingen</td>\n",
       "      <td>49.296822</td>\n",
       "      <td>9.661385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>005056ba-7cb6-1ed2-bceb-5332ab168d12</td>\n",
       "      <td>famila Tankstelle</td>\n",
       "      <td>FAMILA</td>\n",
       "      <td>Pascalstrasse</td>\n",
       "      <td>9</td>\n",
       "      <td>25442</td>\n",
       "      <td>Quickborn</td>\n",
       "      <td>53.742150</td>\n",
       "      <td>9.941240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>005056ba-7cb6-1ed2-bceb-573c18314d16</td>\n",
       "      <td>star Tankstelle</td>\n",
       "      <td>STAR</td>\n",
       "      <td>Riehler Strasse</td>\n",
       "      <td>240</td>\n",
       "      <td>50735</td>\n",
       "      <td>Köln</td>\n",
       "      <td>50.961800</td>\n",
       "      <td>6.980070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>005056ba-7cb6-1ed2-bceb-662ba1a94d1f</td>\n",
       "      <td>star Tankstelle</td>\n",
       "      <td>STAR</td>\n",
       "      <td>BAB 10 / Seeberg Ost</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15345</td>\n",
       "      <td>Altlandsberg</td>\n",
       "      <td>52.550160</td>\n",
       "      <td>13.682120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>005056ba-7cb6-1ed2-bceb-6f7b23564d23</td>\n",
       "      <td>star Tankstelle</td>\n",
       "      <td>STAR</td>\n",
       "      <td>Duisburger Straße</td>\n",
       "      <td>130</td>\n",
       "      <td>47166</td>\n",
       "      <td>Duisburg</td>\n",
       "      <td>51.489790</td>\n",
       "      <td>6.783730</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   uuid                  name   brand  \\\n",
       "0  00060723-0001-4444-8888-acdc00000001  BAGeno Raiffeisen eG     NaN   \n",
       "1  005056ba-7cb6-1ed2-bceb-5332ab168d12     famila Tankstelle  FAMILA   \n",
       "2  005056ba-7cb6-1ed2-bceb-573c18314d16       star Tankstelle    STAR   \n",
       "3  005056ba-7cb6-1ed2-bceb-662ba1a94d1f       star Tankstelle    STAR   \n",
       "4  005056ba-7cb6-1ed2-bceb-6f7b23564d23       star Tankstelle    STAR   \n",
       "\n",
       "                 street house_number post_code          city   latitude  \\\n",
       "0   Künzelsauer Strasse            7     74653  Ingelfingen   49.296822   \n",
       "1         Pascalstrasse            9     25442     Quickborn  53.742150   \n",
       "2       Riehler Strasse          240     50735          Köln  50.961800   \n",
       "3  BAB 10 / Seeberg Ost          NaN     15345  Altlandsberg  52.550160   \n",
       "4     Duisburger Straße          130     47166      Duisburg  51.489790   \n",
       "\n",
       "   longitude  \n",
       "0   9.661385  \n",
       "1   9.941240  \n",
       "2   6.980070  \n",
       "3  13.682120  \n",
       "4   6.783730  "
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tankstellenDF.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-16",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "### 3.1 Aufgabe \n",
    "\n",
    "Schreiben Sie eine Funktion `uebersicht(df)`, die den DataFrame mit den \n",
    "Bestellpositionen als Parameter bekommt und ein Tupel mit \n",
    "\n",
    "  - Anzahl der unterschiedlichen Tankstellen\n",
    "  - maximaler Preis für Diesel\n",
    "  - Gesamtzahl der Preisänderungen\n",
    " \n",
    " zurückliefert.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "cell-17",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:54.616938Z",
     "iopub.status.busy": "2025-06-30T08:21:54.616711Z",
     "iopub.status.idle": "2025-06-30T08:21:54.621872Z",
     "shell.execute_reply": "2025-06-30T08:21:54.621367Z",
     "shell.execute_reply.started": "2025-06-30T08:21:54.616913Z"
    }
   },
   "outputs": [],
   "source": [
    "# Hier ist Platz fuer ihre Loesung zu Aufgabe 3.1:\n",
    "def uebersicht(df):\n",
    "    unterschiedlicheTankstellen=df['station_uuid'].nunique()\n",
    "    maxPreisDiesel=df['diesel'].max()\n",
    "    anzahlAenderungen=len(df)\n",
    "    return (unterschiedlicheTankstellen, maxPreisDiesel, anzahlAenderungen)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "cell-18",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:54.622580Z",
     "iopub.status.busy": "2025-06-30T08:21:54.622387Z",
     "iopub.status.idle": "2025-06-30T08:21:54.674366Z",
     "shell.execute_reply": "2025-06-30T08:21:54.674016Z",
     "shell.execute_reply.started": "2025-06-30T08:21:54.622560Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(54, 1.489, 486478)"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uebersicht(preiseDF)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-19",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "### 3.2 Aufgabe  \n",
    "\n",
    "Schreiben Sie eine Funktion `anzahl(df,tankstellen_id)`, die den DataFrame mit den \n",
    "Preisdaten und die uuid einer Tankstelle bekommt und die Anzahl der Preismeldungen für diese Tankstelle als Ergebnis zurückliefert.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "cell-20",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:54.675026Z",
     "iopub.status.busy": "2025-06-30T08:21:54.674833Z",
     "iopub.status.idle": "2025-06-30T08:21:54.677956Z",
     "shell.execute_reply": "2025-06-30T08:21:54.677447Z",
     "shell.execute_reply.started": "2025-06-30T08:21:54.675006Z"
    }
   },
   "outputs": [],
   "source": [
    "# Hier ist Platz fuer ihre Loesung zu Aufgabe 3.2:\n",
    "def anzahl(df, tankstellen_id):\n",
    "    return len(df[df['station_uuid']==tankstellen_id])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "cell-21",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:54.678690Z",
     "iopub.status.busy": "2025-06-30T08:21:54.678496Z",
     "iopub.status.idle": "2025-06-30T08:21:54.716387Z",
     "shell.execute_reply": "2025-06-30T08:21:54.716031Z",
     "shell.execute_reply.started": "2025-06-30T08:21:54.678670Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7826"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "anzahl(preiseDF, \"b7dc5a11-661a-419f-8d30-d9763b2897c7\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-22",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "### 3.3 Aufgabe \n",
    "\n",
    "Schreiben Sie eine Funktion `tankstellen_nach_marke(df)`, die den DataFrame \n",
    "mit den Tankstellen als Parameter bekommt und einen DataFrame mit einer \n",
    "Spalte `brand` und einer Spalte für die Anzahl dazugehörigen Tankstellen zurückliefert. \n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "cell-23",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:54.716846Z",
     "iopub.status.busy": "2025-06-30T08:21:54.716720Z",
     "iopub.status.idle": "2025-06-30T08:21:54.719347Z",
     "shell.execute_reply": "2025-06-30T08:21:54.719064Z",
     "shell.execute_reply.started": "2025-06-30T08:21:54.716835Z"
    }
   },
   "outputs": [],
   "source": [
    "# Hier ist Platz fuer ihre Loesung zu Aufgabe 3.3:\n",
    "def tankstellen_nach_marke(df):\n",
    "    return df[[\"uuid\", \"brand\"]].groupby('brand').count()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "cell-24",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:54.719757Z",
     "iopub.status.busy": "2025-06-30T08:21:54.719646Z",
     "iopub.status.idle": "2025-06-30T08:21:54.728461Z",
     "shell.execute_reply": "2025-06-30T08:21:54.728190Z",
     "shell.execute_reply.started": "2025-06-30T08:21:54.719746Z"
    }
   },
   "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>uuid</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>brand</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AVIA Xpress</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Autohof Frechen (Mundorf Tank)</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F T B</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Freie Tankstelle Hirschmann</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1A Tank</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>roth</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ssd GmbH</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>super wash</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tankpool24</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>899 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 uuid\n",
       "brand                                \n",
       " AVIA Xpress                        1\n",
       " Autohof Frechen (Mundorf Tank)     1\n",
       " F T B                              1\n",
       " Freie Tankstelle Hirschmann        1\n",
       "1A Tank                             5\n",
       "...                               ...\n",
       "roth                                1\n",
       "ssd GmbH                            1\n",
       "super wash                          1\n",
       "tankpool24                          6\n",
       "team                               79\n",
       "\n",
       "[899 rows x 1 columns]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tankstellen_nach_marke(tankstellenDF)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-25",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "### 3.4 Aufgabe \n",
    "\n",
    "Schreiben Sie eine Funktion `preise_nach_kalenderwoche(df)`, die für die Preisdaten \n",
    "einen neuen DataFrame berechnet und zurückgibt, welche Dieselpreise in den verschiedenen Kalenderwochen durchschnittlichen galten.\n",
    "\n",
    "*Beachten Sie*: \n",
    "Um aus einen (Datetime-)Datum das die Kalenderwoche zu extrahieren, können \n",
    "Sie `dt.isocalendar().week` verwenden (genauso wie bei `dt.month` für den Monat).\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "cell-26",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:54.743295Z",
     "iopub.status.busy": "2025-06-30T08:21:54.743108Z",
     "iopub.status.idle": "2025-06-30T08:21:54.745699Z",
     "shell.execute_reply": "2025-06-30T08:21:54.745368Z",
     "shell.execute_reply.started": "2025-06-30T08:21:54.743277Z"
    }
   },
   "outputs": [],
   "source": [
    "# Hier ist Platz fuer ihre Loesung zu Aufgabe 3.4:\n",
    "def preise_nach_kalenderwoche(df):\n",
    "    df['Kalenderwoche']=pd.to_datetime(df['date'], utc=True).dt.isocalendar().week\n",
    "    return df[['Kalenderwoche', 'diesel']].groupby('Kalenderwoche').mean()\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "cell-27",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:54.829119Z",
     "iopub.status.busy": "2025-06-30T08:21:54.828903Z",
     "iopub.status.idle": "2025-06-30T08:21:55.544354Z",
     "shell.execute_reply": "2025-06-30T08:21:55.543957Z",
     "shell.execute_reply.started": "2025-06-30T08:21:54.829100Z"
    }
   },
   "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>diesel</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kalenderwoche</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.321682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.315491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.291023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.273853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.243170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1.238018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1.233762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1.241917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1.205290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1.201091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1.162729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1.122293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1.112065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1.109260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1.098156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1.085063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1.043801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1.028793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1.013699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1.017654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1.035383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1.047293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1.043405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1.054265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1.078442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1.079698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1.081756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1.078639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1.067454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1.075700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1.074172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1.076426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1.078573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>1.080227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1.065456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1.061559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>1.046300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1.030291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1.036611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1.037012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>1.033463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>1.045844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>1.030903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>1.019164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>1.017231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>1.036309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1.062909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>1.067708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>1.070647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>1.090859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>1.094641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>1.121449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>1.153897</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 diesel\n",
       "Kalenderwoche          \n",
       "1              1.321682\n",
       "2              1.315491\n",
       "3              1.291023\n",
       "4              1.273853\n",
       "5              1.243170\n",
       "6              1.238018\n",
       "7              1.233762\n",
       "8              1.241917\n",
       "9              1.205290\n",
       "10             1.201091\n",
       "11             1.162729\n",
       "12             1.122293\n",
       "13             1.112065\n",
       "14             1.109260\n",
       "15             1.098156\n",
       "16             1.085063\n",
       "17             1.043801\n",
       "18             1.028793\n",
       "19             1.013699\n",
       "20             1.017654\n",
       "21             1.035383\n",
       "22             1.047293\n",
       "23             1.043405\n",
       "24             1.054265\n",
       "25             1.078442\n",
       "26             1.079698\n",
       "27             1.081756\n",
       "28             1.078639\n",
       "29             1.067454\n",
       "30             1.075700\n",
       "31             1.074172\n",
       "32             1.076426\n",
       "33             1.078573\n",
       "34             1.080227\n",
       "35             1.065456\n",
       "36             1.061559\n",
       "37             1.046300\n",
       "38             1.030291\n",
       "39             1.036611\n",
       "40             1.037012\n",
       "41             1.033463\n",
       "42             1.045844\n",
       "43             1.030903\n",
       "44             1.019164\n",
       "45             1.017231\n",
       "46             1.036309\n",
       "47             1.062909\n",
       "48             1.067708\n",
       "49             1.070647\n",
       "50             1.090859\n",
       "51             1.094641\n",
       "52             1.121449\n",
       "53             1.153897"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "preise_nach_kalenderwoche(preiseDF)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-28",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "### 3.5 Aufgabe \n",
    "\n",
    "Schreiben Sie eine Funktion `preise_nach_plz(tankstellen_df, tankpreise_df)`,\n",
    "die einen DataFrame mit den durchschnittlichen Preisen für Diesel, E5 und \n",
    "E10 pro Postleitzahl berechnet.\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "cell-29",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:55.545086Z",
     "iopub.status.busy": "2025-06-30T08:21:55.544941Z",
     "iopub.status.idle": "2025-06-30T08:21:55.547362Z",
     "shell.execute_reply": "2025-06-30T08:21:55.547087Z",
     "shell.execute_reply.started": "2025-06-30T08:21:55.545072Z"
    }
   },
   "outputs": [],
   "source": [
    "# Hier ist Platz fuer ihre Loesung zu Aufgabe 3.5:\n",
    "def preise_nach_plz(tankstellen_df, tankpreise_df):\n",
    "    df = tankpreise_df.merge(tankstellen_df, left_on=\"station_uuid\", right_on=\"uuid\")\n",
    "    return df[[\"post_code\", \"diesel\", \"e5\", \"e10\"]].groupby(\"post_code\").mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "cell-30",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:21:55.547788Z",
     "iopub.status.busy": "2025-06-30T08:21:55.547676Z",
     "iopub.status.idle": "2025-06-30T08:21:55.664765Z",
     "shell.execute_reply": "2025-06-30T08:21:55.664381Z",
     "shell.execute_reply.started": "2025-06-30T08:21:55.547778Z"
    }
   },
   "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>diesel</th>\n",
       "      <th>e5</th>\n",
       "      <th>e10</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>post_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44787</th>\n",
       "      <td>1.076055</td>\n",
       "      <td>1.257486</td>\n",
       "      <td>1.220406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44789</th>\n",
       "      <td>1.105354</td>\n",
       "      <td>1.285039</td>\n",
       "      <td>1.249071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44791</th>\n",
       "      <td>1.095637</td>\n",
       "      <td>1.276390</td>\n",
       "      <td>1.239803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44793</th>\n",
       "      <td>1.091841</td>\n",
       "      <td>1.271549</td>\n",
       "      <td>1.234035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44795</th>\n",
       "      <td>1.104093</td>\n",
       "      <td>1.280253</td>\n",
       "      <td>1.244203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44797</th>\n",
       "      <td>1.099091</td>\n",
       "      <td>1.276876</td>\n",
       "      <td>1.240937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44799</th>\n",
       "      <td>1.109524</td>\n",
       "      <td>1.288415</td>\n",
       "      <td>1.251704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44803</th>\n",
       "      <td>1.092145</td>\n",
       "      <td>1.272344</td>\n",
       "      <td>1.236134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44805</th>\n",
       "      <td>1.114762</td>\n",
       "      <td>1.294354</td>\n",
       "      <td>1.258859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44807</th>\n",
       "      <td>1.103791</td>\n",
       "      <td>1.286213</td>\n",
       "      <td>1.249808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44809</th>\n",
       "      <td>1.087649</td>\n",
       "      <td>1.269947</td>\n",
       "      <td>1.233162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44866</th>\n",
       "      <td>1.114702</td>\n",
       "      <td>1.296657</td>\n",
       "      <td>1.263559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44867</th>\n",
       "      <td>1.087711</td>\n",
       "      <td>1.266949</td>\n",
       "      <td>1.231208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44869</th>\n",
       "      <td>1.077850</td>\n",
       "      <td>1.258900</td>\n",
       "      <td>1.225337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44879</th>\n",
       "      <td>1.081344</td>\n",
       "      <td>1.260688</td>\n",
       "      <td>1.224956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44892</th>\n",
       "      <td>1.092089</td>\n",
       "      <td>1.271568</td>\n",
       "      <td>1.235104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44894</th>\n",
       "      <td>1.102400</td>\n",
       "      <td>1.281909</td>\n",
       "      <td>1.245798</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             diesel        e5       e10\n",
       "post_code                              \n",
       "44787      1.076055  1.257486  1.220406\n",
       "44789      1.105354  1.285039  1.249071\n",
       "44791      1.095637  1.276390  1.239803\n",
       "44793      1.091841  1.271549  1.234035\n",
       "44795      1.104093  1.280253  1.244203\n",
       "44797      1.099091  1.276876  1.240937\n",
       "44799      1.109524  1.288415  1.251704\n",
       "44803      1.092145  1.272344  1.236134\n",
       "44805      1.114762  1.294354  1.258859\n",
       "44807      1.103791  1.286213  1.249808\n",
       "44809      1.087649  1.269947  1.233162\n",
       "44866      1.114702  1.296657  1.263559\n",
       "44867      1.087711  1.266949  1.231208\n",
       "44869      1.077850  1.258900  1.225337\n",
       "44879      1.081344  1.260688  1.224956\n",
       "44892      1.092089  1.271568  1.235104\n",
       "44894      1.102400  1.281909  1.245798"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "preise_nach_plz(tankstellenDF, preiseDF)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-31",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "# Datenbankaufgabe (40 Punkte) \n",
    "\n",
    "Sie sollen ein Datenbankmodell für Ihre Tontechnikaufträge entwickeln.\n",
    "\n",
    "In Ihrer Datenbank möchten Sie die Veranstalter speichern, an die Sie vermieten. Zu den Veranstaltern speichern Sie den Namen und die Adresse.\n",
    "\n",
    "Weiterhin speichern Sie Veranstaltungsorte, auch hier benötigen Sie einen Namen und eine Adresse. Zu jedem Veranstaltungsort gibt es genau eine Ansprechperson, zu der Sie den Namen und die Telefonnummer speichern. Eine Ansprechperson kann für mehrere Veranstaltungsorte verantwortlich sein, Ansprechpersonen ohne einen Veranstaltungsort gibt es nicht.\n",
    "\n",
    "Sie bieten verschiedene Leistungen an. Zu den Leistungen speichern Sie einen Namen und den Preis. Zu den Leistungen speichern Sie die Produkte, die für die Erbringung der Leistung genutzt werden können. Zu den Produkten speichern Sie die Bezeichnung, den Herstellernamen und die vorhandene Anzahl. Eine Leistung kann mithilfe mehrerer Produkte erbracht werden, genauso wie ein Produkt mehreren Leistungen zugeordnet sein kann. Leistungen ohne Produkt gibt es genausowenig wie Produkte ohne Leistungen.\n",
    "\n",
    "Bei den Produkten wird zwischen drei Varianten unterschieden: Licht, Ton und Stroboskop. Für Licht speichern Sie die Farbtemperatur, für Ton die Wattzahl und für Stroboskop die Frequenz.\n",
    "\n",
    "Zusätzlich soll erfasst werden, welche Leistungen an welchen Veranstaltungsorten erbracht werden können. Leistungen können an beliebig vielen Orten erbracht werden, ebenso können an einem Ort mehrere Leistungen erbracht werden. Orte ohne mögliche Leistungen gibt es nicht. \n",
    "\n",
    "Für die eigentlichen Aufträge speichern Sie, welche Leistungen Sie für welchen Veranstalter an welchem Ort erbracht haben. Speichern Sie auch das Datum der Aufträge.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-32",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "## 4.1 Ableiten der Datenbanktabellen\n",
    "\n",
    "Zu dem vorliegenden Anwendungsfall existiert bereits das folgende ER-Modell, das auch schon die Kardinalitäten für die Beziehungen enthält:\n",
    "\n",
    "  <div style=\"margin: auto; text-align: center\">\n",
    "    <img src=\"https://data.hsbo.de/093e32feb58bb54085a8f6bac33633a3.png\" style=\"size: 70%;\">\n",
    "  </div>\n",
    "\n",
    "Das ER-Modell dient ja als grundsätzliches Datenbank-Modell um daraus die entsprechenden \n",
    "Datenbank Tabellen zu erzeugen. Geben Sie im Folgenden die Tabellen an, die sich aus dem \n",
    "obigen ER-Modell ergeben:\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-33",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "| Tabelle | Primärschlüssel | weitere Attribute |\n",
    "| ---     | ---             | ---               |\n",
    "| Veranstalter | VeranstalterNr | Name, PLZ, Ort, Strasse |\n",
    "| Veranstaltungsort | VeranstaltungsortNr | _AnsprechpersonNr_, Name, PLZ, Ort, Strasse |\n",
    "| Ansprechperson | AnsprechpersonNr | Vorname, Nachname, Telefon |\n",
    "| Leistung | LeistungNr | Name, Preis |\n",
    "| Produkt | ProduktNr | Bezeichnung,  Herstellernamen, Anzahl |\n",
    "| Licht | ProduktNr | Farbtemperatur |\n",
    "| Ton | ProduktNr | Wattzahl |\n",
    "| Stroboskop | ProduktNr | Frequenz |\n",
    "| VeranstaltungsortLeistung | _VeranstaltungsortNr_, _LeistungNr_ |  |\n",
    "| ProduktLeistung | _ProduktNr_, _LeistungNr_ |  |\n",
    "| Auftrag | _VeranstalterNr_, _VeranstaltungsortNr_, _LeistungNr_ | Datum |\n",
    "|  |  |  |\n",
    "|  |  |  |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-34",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-35",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "## 4.2 SQL Abfragen\n",
    "\n",
    "Geben Sie SQL-Befehle für die folgenden Fragestellungen an:\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-36",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "#### 1. Die Telefonnummer der Ansprechperson `Willi Mustermann`\n",
    "\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "cell-37",
   "metadata": {
    "tags": [
     "sql"
    ]
   },
   "source": [
    "Select Telefon from Ansprechperson where Vorname = \"Willi\" and Nachname = \"Mustermann\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-38",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-39",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "#### 2. Die Namen aller Veranstaltungsorte, die mit `Halle` beginnen\n",
    "\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "cell-40",
   "metadata": {
    "tags": [
     "sql"
    ]
   },
   "source": [
    "Select Name from Veranstaltungsort where Name like \"Halle%\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-41",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-42",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "#### 3. Den durchschnittlichen Preis der Leistungen\n",
    "\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "cell-43",
   "metadata": {
    "tags": [
     "sql"
    ]
   },
   "source": [
    "Select avg(Preis) from Leistung"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-44",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-45",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "#### 4. Die Namen der Ansprechpersonen von Veranstaltungsorten in Bochum\n",
    "\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "cell-46",
   "metadata": {
    "tags": [
     "sql"
    ]
   },
   "source": [
    "Select Ansprechperson.Vorname, Ansprechperson.Nachname from Ansprechperson \n",
    "join Veranstaltungsort on (Ansprechperson.AnsprechpersonNr = Veranstaltungsort.AnsprechpersonNr)\n",
    "where Veranstaltungsort.Ort = \"Bochum\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-47",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-48",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "#### 5. Die Namen der Leistungen, die am Veranstaltungsort Westfalenhalle angeboten werden können\n",
    "\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "cell-49",
   "metadata": {
    "tags": [
     "sql"
    ]
   },
   "source": [
    "Select Leistung.Name from Leistung\n",
    "join VeranstaltungsortLeistung on (Leistung.LeistungNr = VeranstaltungsortLeistung.Leistung)\n",
    "join Veranstaltungsort on (VeranstaltungsortLeistung.VeranstaltungsortNr = Veranstaltungsort.VeranstaltungsortNr)\n",
    "where Veranstaltungsort.Name = \"Westfalenhalle\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-50",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-51",
   "metadata": {
    "editable": false,
    "tags": [
     "aufgabe"
    ]
   },
   "source": [
    "#### 6. Die Namen der Produkte und den Durchschnitt der Preise der dazugehörigen Leistungen\n",
    "\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "cell-52",
   "metadata": {
    "tags": [
     "sql"
    ]
   },
   "source": [
    "Select Produkt.Name, avg(Leistung.Preis) from Produkt \n",
    "join ProduktLeistung on (Produkt.ProduktNr = ProduktLeistung.ProduktNr)\n",
    "join Leistung on (Leistung.LeistungNr = ProduktLeistung.LeistungNr)\n",
    "group by Produkt.Name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "421accc4-260d-4d96-baec-731cf3beee48",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-06-30T08:19:35.829772Z",
     "iopub.status.busy": "2025-06-30T08:19:35.829401Z",
     "iopub.status.idle": "2025-06-30T08:19:35.835561Z",
     "shell.execute_reply": "2025-06-30T08:19:35.834575Z",
     "shell.execute_reply.started": "2025-06-30T08:19:35.829741Z"
    }
   },
   "source": [
    "_In der Klausur Sommersemester 1025 wird es insgesamt 8 SQL-Aufgaben geben!_"
   ]
  }
 ],
 "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}