{ "cells": [ { "cell_type": "markdown", "id": "81fab8dc-9615-482b-88b0-2bd8f78155ee", "metadata": { "editable": false, "slideshow": { "slide_type": "" }, "tags": [] }, "source": [ "
\n", "
\n", "
\n", "

Probeklausur Wirtschaftsinformatik

\n", "

Sommersemester 2026

\n", "
" ] }, { "cell_type": "markdown", "id": "cell-0", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "# Aufgabe - Grundlagen (max. 20 Punkte)\n", "\n" ] }, { "cell_type": "markdown", "id": "cell-1", "metadata": { "editable": false, "slideshow": { "slide_type": "" }, "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 in einer höheren Auflösung speichern. \n", "Dazu sollen in Breite und Höhe des Bildes jeweils 20% mehr Pixel gespeichert werden.\n", "\n", "Um welchen Faktor wächst der erforderliche Speicherplatz?\n", "\n", "\n" ] }, { "cell_type": "raw", "id": "cell-2", "metadata": {}, "source": [] }, { "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/photos/urlaub2024` ihres Computers (wobei Sie die Matrikelnummer durch ihre eigene Matrikelnummer ersetzen). In welchem Verzeichnis befinden Sie sich, wenn Sie von diesem Verzeichnis aus dem relativen Pfad `../hochzeit/fotograph` folgen?\n", "\n", "\n", "Geben Sie als Lösung den absoluten Pfad des neuen Verzeichnisses an!\n", "\n", "\n" ] }, { "cell_type": "raw", "id": "cell-4", "metadata": {}, "source": [] }, { "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://wiinftest.fbw.hs-bochum.de](https://wiinftest.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": [] }, { "cell_type": "markdown", "id": "cell-7", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "# Aufgabe - grundlegende Python Funktionen (max. 20 Punkte)\n", "\n", "Sie betreiben einen Ticketshop, mit dem Sie Tickets für Konzerte und Theater verkaufen.\n", "Die folgende Tabelle zeigt einen Überblick über Ticketbestellungen und deren Gesamtpreis:\n", "\n", "
KundeBesucherdavon KinderDatumVeranstaltungsartGesamtpreis
Meyer1672024-03-25 00:00:00Konzert1341.66
Meier942024-05-10 00:00:00Schauspiel244.58
Schneider632024-04-08 00:00:00Konzert632.7
Müller422024-09-09 00:00:00Schauspiel136.8
Schmidt202024-05-29 00:00:00Oper476.0
Kohler1532024-01-06 00:00:00Oper2763.89
Hoffmann10102024-05-22 00:00:00Konzert789.21
Hartmann14142024-02-25 00:00:00Oper2369.05
\n", "Die Bestelldaten aus der Tabelle liegen im Jupyter-Notebook als Liste von Tupeln vor,\n", "allerdings ohne die Spalte mit dem Gesamtpreis. Der Gesamtpreis soll in dieser Aufgabe\n", "berechnet werden. Die Tabelle können Sie nutzen, um ihre Berechnung zu testen.\n", "Für die Berechnung gelten die folgenden Rahmenbedingungen:\n", "\n", " - Tickets für Art: Konzert kosten 111 Euro je Besucher\n", " - Tickets für Art: Schauspiel kosten 36 Euro je Besucher\n", " - Tickets für Art: Oper kosten 238 Euro je Besucher\n", "\n", "Kinder-Tickets sind 10 Prozent günstiger.\n", "\n", "Außerdem gibt es einen Rabatt von 21 Prozent auf den Gesamtpreis, wenn mindestens 9 Tickets verkauft wurden. \n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": 4, "id": "cell-8", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T12:55:03.831965Z", "iopub.status.busy": "2026-07-01T12:55:03.831807Z", "iopub.status.idle": "2026-07-01T12:55:03.835201Z", "shell.execute_reply": "2026-07-01T12:55:03.834782Z", "shell.execute_reply.started": "2026-07-01T12:55:03.831950Z" } }, "outputs": [], "source": [ "testDaten = [\n", " ('Meyer', 16, 7, '2024-03-25 00:00:00', 'Konzert'),\n", " ('Meier', 9, 4, '2024-05-10 00:00:00', 'Schauspiel'),\n", " ('Schneider', 6, 3, '2024-04-08 00:00:00', 'Konzert'),\n", " ('Müller', 4, 2, '2024-09-09 00:00:00', 'Schauspiel'),\n", " ('Schmidt', 2, 0, '2024-05-29 00:00:00', 'Oper'),\n", " ('Kohler', 15, 3, '2024-01-06 00:00:00', 'Oper'),\n", " ('Hoffmann', 10, 10, '2024-05-22 00:00:00', 'Konzert'),\n", " ('Hartmann', 14, 14, '2024-02-25 00:00:00', 'Oper')\n", "]" ] }, { "cell_type": "code", "execution_count": 7, "id": "ee705c7a-342f-44ec-9fca-ff3fad40c427", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T12:55:48.639666Z", "iopub.status.busy": "2026-07-01T12:55:48.639508Z", "iopub.status.idle": "2026-07-01T12:55:48.642542Z", "shell.execute_reply": "2026-07-01T12:55:48.642017Z", "shell.execute_reply.started": "2026-07-01T12:55:48.639652Z" } }, "outputs": [], "source": [ "ersteZeile = testDaten[0]" ] }, { "cell_type": "code", "execution_count": 9, "id": "9670fc83-2074-4556-9c12-759a0deb482e", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T12:56:03.317188Z", "iopub.status.busy": "2026-07-01T12:56:03.317042Z", "iopub.status.idle": "2026-07-01T12:56:03.323129Z", "shell.execute_reply": "2026-07-01T12:56:03.319267Z", "shell.execute_reply.started": "2026-07-01T12:56:03.317175Z" } }, "outputs": [ { "data": { "text/plain": [ "('Meyer', 16, 7, '2024-03-25 00:00:00', 'Konzert')" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ersteZeile" ] }, { "cell_type": "code", "execution_count": 11, "id": "9e956f74-16db-457b-93fa-683fc913dd1c", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T12:57:13.935628Z", "iopub.status.busy": "2026-07-01T12:57:13.935468Z", "iopub.status.idle": "2026-07-01T12:57:13.940186Z", "shell.execute_reply": "2026-07-01T12:57:13.939820Z", "shell.execute_reply.started": "2026-07-01T12:57:13.935613Z" } }, "outputs": [], "source": [ "tickets = ersteZeile[1]\n", "kinder = ersteZeile[2]\n", "veranstaltungsart = ersteZeile[-1]" ] }, { "cell_type": "code", "execution_count": 15, "id": "1331be74-25b3-4a66-bd84-211ea824cd23", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T12:58:58.000999Z", "iopub.status.busy": "2026-07-01T12:58:58.000866Z", "iopub.status.idle": "2026-07-01T12:58:58.003447Z", "shell.execute_reply": "2026-07-01T12:58:58.003096Z", "shell.execute_reply.started": "2026-07-01T12:58:58.000987Z" } }, "outputs": [ { "data": { "text/plain": [ "1341.6570000000002" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "berechneGesamtpreis(16, 7, 'Konzert')" ] }, { "cell_type": "markdown", "id": "cell-9", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "### 2.1 Aufgabe \n", "\n", "Programmieren Sie eine Funktion `berechneGesamtpreis(...)`, der Sie die Anzahl der Besucher, der Kinder und die Art der Veranstaltung übergeben. (Die Datei mit den Beispieldaten enthält die richtigen Ergebnisse.) \n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": 28, "id": "cell-10", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:02:12.790412Z", "iopub.status.busy": "2026-07-01T13:02:12.790170Z", "iopub.status.idle": "2026-07-01T13:02:12.793189Z", "shell.execute_reply": "2026-07-01T13:02:12.792762Z", "shell.execute_reply.started": "2026-07-01T13:02:12.790396Z" } }, "outputs": [], "source": [ "# Hier ist Platz fuer ihre Loesung zu Aufgabe 2.1:\n", "def berechneGesamtpreis( anzahlBesucher, anzahlKinder, art):\n", " ticket_preis = 0\n", " if art == 'Konzert':\n", " ticket_preis = 111\n", " if art == 'Schauspiel':\n", " ticket_preis = 36\n", " if art == 'Oper':\n", " ticket_preis = 238\n", "\n", " anzahl_erw = anzahlBesucher - anzahlKinder\n", " ticket_preis_kinder = ticket_preis * 0.9\n", "\n", " preis = anzahl_erw * ticket_preis + anzahlKinder * ticket_preis_kinder\n", "\n", " if anzahlBesucher >= 9:\n", "# preis = preis * 0.79\n", " preis = preis - preis * 0.21\n", "\n", " return preis" ] }, { "cell_type": "code", "execution_count": 29, "id": "cell-11", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:02:13.459260Z", "iopub.status.busy": "2026-07-01T13:02:13.459130Z", "iopub.status.idle": "2026-07-01T13:02:13.461692Z", "shell.execute_reply": "2026-07-01T13:02:13.461305Z", "shell.execute_reply.started": "2026-07-01T13:02:13.459248Z" } }, "outputs": [ { "data": { "text/plain": [ "244.584" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "berechneGesamtpreis(9, 4, 'Schauspiel')" ] }, { "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 Summe aller Besucher berechnen, \n", "die eine Oper besucht haben.\n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": 36, "id": "cell-13", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:10:33.527622Z", "iopub.status.busy": "2026-07-01T13:10:33.527470Z", "iopub.status.idle": "2026-07-01T13:10:33.535064Z", "shell.execute_reply": "2026-07-01T13:10:33.532100Z", "shell.execute_reply.started": "2026-07-01T13:10:33.527607Z" } }, "outputs": [], "source": [ "# Hier ist Platz fuer ihre Loesung zu Aufgabe 2.2:\n", "def berechne_statistikwert(daten):\n", " anzahl = 0\n", "\n", " for zeile in daten:\n", " anzahl_besucher = zeile[1]\n", " veranstaltungsart = zeile[4]\n", "\n", " if veranstaltungsart == 'Oper':\n", " anzahl = anzahl + anzahl_besucher\n", "\n", " return anzahl\n", " " ] }, { "cell_type": "code", "execution_count": 37, "id": "b75fb5bc-63b3-4410-8586-7cff741a4999", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:10:33.869918Z", "iopub.status.busy": "2026-07-01T13:10:33.869663Z", "iopub.status.idle": "2026-07-01T13:10:33.872665Z", "shell.execute_reply": "2026-07-01T13:10:33.872297Z", "shell.execute_reply.started": "2026-07-01T13:10:33.869891Z" } }, "outputs": [ { "data": { "text/plain": [ "31" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "berechne_statistikwert(testDaten)" ] }, { "cell_type": "code", "execution_count": 30, "id": "cell-14", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:04:48.295938Z", "iopub.status.busy": "2026-07-01T13:04:48.295787Z", "iopub.status.idle": "2026-07-01T13:04:48.298570Z", "shell.execute_reply": "2026-07-01T13:04:48.298159Z", "shell.execute_reply.started": "2026-07-01T13:04:48.295924Z" } }, "outputs": [], "source": [ "testDaten = [\n", " ('Meyer', 16, 7, '2024-03-25 00:00:00', 'Konzert'),\n", " ('Meier', 9, 4, '2024-05-10 00:00:00', 'Schauspiel'),\n", " ('Schneider', 6, 3, '2024-04-08 00:00:00', 'Konzert'),\n", " ('Müller', 4, 2, '2024-09-09 00:00:00', 'Schauspiel'),\n", " ('Schmidt', 2, 0, '2024-05-29 00:00:00', 'Oper'),\n", " ('Kohler', 15, 3, '2024-01-06 00:00:00', 'Oper'),\n", " ('Hoffmann', 10, 10, '2024-05-22 00:00:00', 'Konzert'),\n", " ('Hartmann', 14, 14, '2024-02-25 00:00:00', 'Oper')\n", "]" ] }, { "cell_type": "markdown", "id": "cell-15", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "# Aufgabe - Datenanalyse (max. 40 Punkte)\n", "\n", "Die Bestellungen des Ticketshops werden in einer Datenbank gespeichert, \n", "dabei wird für jede Bestellung eine oder mehrere Bestellpositionen erfasst, \n", "die Informationen zu Sitzreihe und Sitzplatz enthält.\n", "Eine Bestellung, bei der 3 Tickets bestellt wurden hat also 3 Bestellpositionen.\n", "\n", "In der Datei\n", "[https://data.hsbo.de/theater_positionen.csv](https://data.hsbo.de/theater_positionen.csv) \n", "finden Sie eine Liste der Bestellpositionen, also die verkauften Tickets für die \n", "jeweiligen Veranstaltungen.\n", "\n", "\n", "In dem Theater werden vor Ort auch Getränke mit verkauft. Auch diese Verkäufe werden\n", "über die Kasse erfasst. Um die Getränkeversorgung sicher zu stellen, müssen natürlich \n", "ausreichend Getränke vorab bestellt werden.\n", "In der Datei\n", "[https://data.hsbo.de/theater_getraenke.csv](https://data.hsbo.de/theater_getraenke.csv)\n", "finden Sie die verkauften Getränke aus dem Kassensystem.\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": "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 verschiedenen Aufführungstermine\n", " - Anzahl der verschiedenen Bestellungen\n", " - Gesamtzahl aller verkaufter Tickets\n", " \n", " zurückliefert.\n", "\n" ] }, { "cell_type": "code", "execution_count": 38, "id": "cell-17", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:12:56.801412Z", "iopub.status.busy": "2026-07-01T13:12:56.801266Z", "iopub.status.idle": "2026-07-01T13:12:57.173937Z", "shell.execute_reply": "2026-07-01T13:12:57.173359Z", "shell.execute_reply.started": "2026-07-01T13:12:56.801400Z" } }, "outputs": [], "source": [ "# Hier ist Platz fuer ihre Loesung zu Aufgabe 3.1:\n", "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 39, "id": "8e2001c8-402b-44af-a99a-fb6ca8edc392", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:13:13.816701Z", "iopub.status.busy": "2026-07-01T13:13:13.816370Z", "iopub.status.idle": "2026-07-01T13:13:13.912973Z", "shell.execute_reply": "2026-07-01T13:13:13.912513Z", "shell.execute_reply.started": "2026-07-01T13:13:13.816684Z" } }, "outputs": [], "source": [ "bestellungen = pd.read_csv('https://data.hsbo.de/theater_positionen.csv')" ] }, { "cell_type": "code", "execution_count": 40, "id": "237e2249-c8a4-444c-9f17-ec2337e48b43", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:13:16.773734Z", "iopub.status.busy": "2026-07-01T13:13:16.773554Z", "iopub.status.idle": "2026-07-01T13:13:16.782483Z", "shell.execute_reply": "2026-07-01T13:13:16.782052Z", "shell.execute_reply.started": "2026-07-01T13:13:16.773716Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BestellNrPositionVorstellungDatumSitzbereichSitzplatzSitzreihe
0103001Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne1.01.0
1103011Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne2.01.0
2103012Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne3.01.0
3103021Hexenjagd, Arthur Miller2024-09-27 19:30:00Tribüne1.02.0
4103022Hexenjagd, Arthur Miller2024-09-27 19:30:00Tribüne2.02.0
\n", "
" ], "text/plain": [ " BestellNr Position Vorstellung Datum \\\n", "0 10300 1 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "1 10301 1 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "2 10301 2 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "3 10302 1 Hexenjagd, Arthur Miller 2024-09-27 19:30:00 \n", "4 10302 2 Hexenjagd, Arthur Miller 2024-09-27 19:30:00 \n", "\n", " Sitzbereich Sitzplatz Sitzreihe \n", "0 Tribüne 1.0 1.0 \n", "1 Tribüne 2.0 1.0 \n", "2 Tribüne 3.0 1.0 \n", "3 Tribüne 1.0 2.0 \n", "4 Tribüne 2.0 2.0 " ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bestellungen.head()" ] }, { "cell_type": "code", "execution_count": 43, "id": "cell-18", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:18:11.279654Z", "iopub.status.busy": "2026-07-01T13:18:11.279491Z", "iopub.status.idle": "2026-07-01T13:18:11.282293Z", "shell.execute_reply": "2026-07-01T13:18:11.281618Z", "shell.execute_reply.started": "2026-07-01T13:18:11.279638Z" } }, "outputs": [], "source": [ "anzahl_tickets = bestellungen['Position'].count()" ] }, { "cell_type": "code", "execution_count": 45, "id": "b08ba89d-0fa2-4d68-9bda-53cdf4c30665", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:21:50.360690Z", "iopub.status.busy": "2026-07-01T13:21:50.360526Z", "iopub.status.idle": "2026-07-01T13:21:50.362873Z", "shell.execute_reply": "2026-07-01T13:21:50.362325Z", "shell.execute_reply.started": "2026-07-01T13:21:50.360677Z" } }, "outputs": [], "source": [ "anzahl_tickets = bestellungen.shape[0]" ] }, { "cell_type": "code", "execution_count": 47, "id": "4c28f06b-1871-4587-9f70-08f4083bb8e0", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:23:43.231838Z", "iopub.status.busy": "2026-07-01T13:23:43.231667Z", "iopub.status.idle": "2026-07-01T13:23:43.240307Z", "shell.execute_reply": "2026-07-01T13:23:43.239847Z", "shell.execute_reply.started": "2026-07-01T13:23:43.231824Z" } }, "outputs": [], "source": [ "anzahl_bestellungen = bestellungen['BestellNr'].nunique()" ] }, { "cell_type": "code", "execution_count": 49, "id": "4a84829c-44d5-482c-b2ec-04393c49ad45", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:24:27.011573Z", "iopub.status.busy": "2026-07-01T13:24:27.011409Z", "iopub.status.idle": "2026-07-01T13:24:27.014355Z", "shell.execute_reply": "2026-07-01T13:24:27.013825Z", "shell.execute_reply.started": "2026-07-01T13:24:27.011558Z" } }, "outputs": [], "source": [ "anzahl_termine = bestellungen['Datum'].nunique()" ] }, { "cell_type": "code", "execution_count": 60, "id": "eec47888-17d0-4cf2-bb91-60a1238634e7", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:28:24.810494Z", "iopub.status.busy": "2026-07-01T13:28:24.810345Z", "iopub.status.idle": "2026-07-01T13:28:24.812850Z", "shell.execute_reply": "2026-07-01T13:28:24.812467Z", "shell.execute_reply.started": "2026-07-01T13:28:24.810479Z" } }, "outputs": [], "source": [ "def uebersicht(dataFrame):\n", " anzahl_tickets = dataFrame['Position'].count()\n", " anzahl_bestellungen = dataFrame['BestellNr'].nunique()\n", " anzahl_termine = dataFrame['Datum'].nunique()\n", "\n", " return (anzahl_tickets, anzahl_bestellungen, anzahl_termine)" ] }, { "cell_type": "code", "execution_count": 61, "id": "fa78c29b-d0cc-4daa-901f-22b3bb9f84f8", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:28:25.191661Z", "iopub.status.busy": "2026-07-01T13:28:25.191555Z", "iopub.status.idle": "2026-07-01T13:28:25.194128Z", "shell.execute_reply": "2026-07-01T13:28:25.193742Z", "shell.execute_reply.started": "2026-07-01T13:28:25.191649Z" } }, "outputs": [], "source": [ "ergebnis = uebersicht(bestellungen)" ] }, { "cell_type": "code", "execution_count": 62, "id": "4955f872-4b9d-48e5-a206-d977b5fa0773", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:28:26.027740Z", "iopub.status.busy": "2026-07-01T13:28:26.027623Z", "iopub.status.idle": "2026-07-01T13:28:26.029606Z", "shell.execute_reply": "2026-07-01T13:28:26.029302Z", "shell.execute_reply.started": "2026-07-01T13:28:26.027728Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(np.int64(696), 297, 26)\n" ] } ], "source": [ "print(ergebnis)" ] }, { "cell_type": "markdown", "id": "cell-19", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "### 3.2 Aufgabe \n", "\n", "Schreiben Sie eine Funktion `anzahl(df,titel)`, die den DataFrame mit den \n", "Bestellpositionen und den Titel der Vorstellung bekommt und die Anzahl der\n", "verkauften Tickets für diese Vorstellung als Ergebnis zurückliefert.\n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": 66, "id": "cell-20", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:29:49.585854Z", "iopub.status.busy": "2026-07-01T13:29:49.585694Z", "iopub.status.idle": "2026-07-01T13:29:49.592998Z", "shell.execute_reply": "2026-07-01T13:29:49.592605Z", "shell.execute_reply.started": "2026-07-01T13:29:49.585840Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BestellNrPositionVorstellungDatumSitzbereichSitzplatzSitzreihe
0103001Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne1.01.0
1103011Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne2.01.0
2103012Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne3.01.0
3103021Hexenjagd, Arthur Miller2024-09-27 19:30:00Tribüne1.02.0
4103022Hexenjagd, Arthur Miller2024-09-27 19:30:00Tribüne2.02.0
\n", "
" ], "text/plain": [ " BestellNr Position Vorstellung Datum \\\n", "0 10300 1 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "1 10301 1 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "2 10301 2 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "3 10302 1 Hexenjagd, Arthur Miller 2024-09-27 19:30:00 \n", "4 10302 2 Hexenjagd, Arthur Miller 2024-09-27 19:30:00 \n", "\n", " Sitzbereich Sitzplatz Sitzreihe \n", "0 Tribüne 1.0 1.0 \n", "1 Tribüne 2.0 1.0 \n", "2 Tribüne 3.0 1.0 \n", "3 Tribüne 1.0 2.0 \n", "4 Tribüne 2.0 2.0 " ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Hier ist Platz fuer ihre Loesung zu Aufgabe 3.2:\n", "bestellungen.head()" ] }, { "cell_type": "code", "execution_count": 67, "id": "cell-21", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:30:52.527198Z", "iopub.status.busy": "2026-07-01T13:30:52.527051Z", "iopub.status.idle": "2026-07-01T13:30:52.529437Z", "shell.execute_reply": "2026-07-01T13:30:52.528960Z", "shell.execute_reply.started": "2026-07-01T13:30:52.527183Z" } }, "outputs": [], "source": [ "def anzahl(df, titel):\n", " best = df[ df['Vorstellung'] == titel ]\n", " return best.shape[0] " ] }, { "cell_type": "code", "execution_count": 69, "id": "c9fcfdfb-31ac-4fbd-8cb0-4800a077e36c", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:31:25.664968Z", "iopub.status.busy": "2026-07-01T13:31:25.664801Z", "iopub.status.idle": "2026-07-01T13:31:25.668074Z", "shell.execute_reply": "2026-07-01T13:31:25.667652Z", "shell.execute_reply.started": "2026-07-01T13:31:25.664952Z" } }, "outputs": [ { "data": { "text/plain": [ "\n", "[ 'Hexenjagd, Arthur Miller',\n", " 'Der zerbrochne Krug, Heinrich von Kleist',\n", " 'Drei Männer im Schnee, Erich Kästner',\n", " 'Die Räuber, Friedrich Schiller',\n", " 'Viel Lärm um nichts, William Shakespeare']\n", "Length: 5, dtype: str" ] }, "execution_count": 69, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bestellungen['Vorstellung'].unique()" ] }, { "cell_type": "code", "execution_count": 70, "id": "e2f707b8-a036-4073-aa44-98f4d9471740", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:31:31.012376Z", "iopub.status.busy": "2026-07-01T13:31:31.012200Z", "iopub.status.idle": "2026-07-01T13:31:31.017342Z", "shell.execute_reply": "2026-07-01T13:31:31.016755Z", "shell.execute_reply.started": "2026-07-01T13:31:31.012360Z" } }, "outputs": [ { "data": { "text/plain": [ "118" ] }, "execution_count": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "anzahl(bestellungen, 'Drei Männer im Schnee, Erich Kästner')" ] }, { "cell_type": "markdown", "id": "cell-22", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "### 3.3 Aufgabe \n", "\n", "Schreiben Sie eine Funktion `tickets_nach_vorstellung(df)`, die den DataFrame \n", "mit den Bestellpositionen als Parameter bekommt und einen DataFrame mit einer \n", "Spalte `Vorstellung` und einer Spalte für die Anzahl der Tickets zurückliefert. \n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": 71, "id": "cell-23", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:32:30.676846Z", "iopub.status.busy": "2026-07-01T13:32:30.676679Z", "iopub.status.idle": "2026-07-01T13:32:30.678798Z", "shell.execute_reply": "2026-07-01T13:32:30.678379Z", "shell.execute_reply.started": "2026-07-01T13:32:30.676829Z" } }, "outputs": [], "source": [ "# Hier ist Platz fuer ihre Loesung zu Aufgabe 3.3:" ] }, { "cell_type": "code", "execution_count": 74, "id": "37c53bab-1521-4e06-b805-8fc9a5093b79", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:35:35.791241Z", "iopub.status.busy": "2026-07-01T13:35:35.790993Z", "iopub.status.idle": "2026-07-01T13:35:35.797973Z", "shell.execute_reply": "2026-07-01T13:35:35.797496Z", "shell.execute_reply.started": "2026-07-01T13:35:35.791218Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BestellNrPositionVorstellungDatumSitzbereichSitzplatzSitzreihe
0103001Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne1.01.0
1103011Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne2.01.0
2103012Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne3.01.0
3103021Hexenjagd, Arthur Miller2024-09-27 19:30:00Tribüne1.02.0
4103022Hexenjagd, Arthur Miller2024-09-27 19:30:00Tribüne2.02.0
\n", "
" ], "text/plain": [ " BestellNr Position Vorstellung Datum \\\n", "0 10300 1 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "1 10301 1 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "2 10301 2 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "3 10302 1 Hexenjagd, Arthur Miller 2024-09-27 19:30:00 \n", "4 10302 2 Hexenjagd, Arthur Miller 2024-09-27 19:30:00 \n", "\n", " Sitzbereich Sitzplatz Sitzreihe \n", "0 Tribüne 1.0 1.0 \n", "1 Tribüne 2.0 1.0 \n", "2 Tribüne 3.0 1.0 \n", "3 Tribüne 1.0 2.0 \n", "4 Tribüne 2.0 2.0 " ] }, "execution_count": 74, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bestellungen.head()" ] }, { "cell_type": "code", "execution_count": 75, "id": "a81b5357-3ed8-4a5f-928e-4d126eb1af60", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:35:52.027437Z", "iopub.status.busy": "2026-07-01T13:35:52.027258Z", "iopub.status.idle": "2026-07-01T13:35:52.031119Z", "shell.execute_reply": "2026-07-01T13:35:52.030572Z", "shell.execute_reply.started": "2026-07-01T13:35:52.027421Z" } }, "outputs": [], "source": [ "df_klein = bestellungen[['Vorstellung','Sitzplatz']]" ] }, { "cell_type": "code", "execution_count": 84, "id": "7127384c-0fd0-4594-8753-324fb4b55da9", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:42:15.278013Z", "iopub.status.busy": "2026-07-01T13:42:15.277842Z", "iopub.status.idle": "2026-07-01T13:42:15.283678Z", "shell.execute_reply": "2026-07-01T13:42:15.283242Z", "shell.execute_reply.started": "2026-07-01T13:42:15.277997Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Sitzplatz
Vorstellung
Der zerbrochne Krug, Heinrich von Kleist65
Die Räuber, Friedrich Schiller130
Drei Männer im Schnee, Erich Kästner118
Hexenjagd, Arthur Miller353
Viel Lärm um nichts, William Shakespeare30
\n", "
" ], "text/plain": [ " Sitzplatz\n", "Vorstellung \n", "Der zerbrochne Krug, Heinrich von Kleist 65\n", "Die Räuber, Friedrich Schiller 130\n", "Drei Männer im Schnee, Erich Kästner 118\n", "Hexenjagd, Arthur Miller 353\n", "Viel Lärm um nichts, William Shakespeare 30" ] }, "execution_count": 84, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_klein.groupby('Vorstellung').count()" ] }, { "cell_type": "code", "execution_count": 85, "id": "26c29426-07af-4b9c-b145-da04cfddaf67", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:42:47.043239Z", "iopub.status.busy": "2026-07-01T13:42:47.043067Z", "iopub.status.idle": "2026-07-01T13:42:47.045672Z", "shell.execute_reply": "2026-07-01T13:42:47.045167Z", "shell.execute_reply.started": "2026-07-01T13:42:47.043224Z" } }, "outputs": [], "source": [ "def tickets_nach_vorstellung(df):\n", " df_klein = df[['Vorstellung','Sitzplatz']]\n", " return df_klein.groupby('Vorstellung').count()" ] }, { "cell_type": "code", "execution_count": 86, "id": "86836f2e-3697-40fb-aa4d-543c20abddba", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:42:55.958089Z", "iopub.status.busy": "2026-07-01T13:42:55.957840Z", "iopub.status.idle": "2026-07-01T13:42:55.964203Z", "shell.execute_reply": "2026-07-01T13:42:55.963789Z", "shell.execute_reply.started": "2026-07-01T13:42:55.958075Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Sitzplatz
Vorstellung
Der zerbrochne Krug, Heinrich von Kleist65
Die Räuber, Friedrich Schiller130
Drei Männer im Schnee, Erich Kästner118
Hexenjagd, Arthur Miller353
Viel Lärm um nichts, William Shakespeare30
\n", "
" ], "text/plain": [ " Sitzplatz\n", "Vorstellung \n", "Der zerbrochne Krug, Heinrich von Kleist 65\n", "Die Räuber, Friedrich Schiller 130\n", "Drei Männer im Schnee, Erich Kästner 118\n", "Hexenjagd, Arthur Miller 353\n", "Viel Lärm um nichts, William Shakespeare 30" ] }, "execution_count": 86, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tickets_nach_vorstellung(bestellungen)" ] }, { "cell_type": "code", "execution_count": 77, "id": "cell-24", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:37:42.298003Z", "iopub.status.busy": "2026-07-01T13:37:42.297845Z", "iopub.status.idle": "2026-07-01T13:37:42.305092Z", "shell.execute_reply": "2026-07-01T13:37:42.304606Z", "shell.execute_reply.started": "2026-07-01T13:37:42.297988Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BestellNrPositionDatumSitzbereichSitzplatzSitzreihe
Vorstellung
Der zerbrochne Krug, Heinrich von Kleist656565656565
Die Räuber, Friedrich Schiller130130130130130130
Drei Männer im Schnee, Erich Kästner118118118118118118
Hexenjagd, Arthur Miller353353353353353353
Viel Lärm um nichts, William Shakespeare303030303030
\n", "
" ], "text/plain": [ " BestellNr Position Datum \\\n", "Vorstellung \n", "Der zerbrochne Krug, Heinrich von Kleist 65 65 65 \n", "Die Räuber, Friedrich Schiller 130 130 130 \n", "Drei Männer im Schnee, Erich Kästner 118 118 118 \n", "Hexenjagd, Arthur Miller 353 353 353 \n", "Viel Lärm um nichts, William Shakespeare 30 30 30 \n", "\n", " Sitzbereich Sitzplatz Sitzreihe \n", "Vorstellung \n", "Der zerbrochne Krug, Heinrich von Kleist 65 65 65 \n", "Die Räuber, Friedrich Schiller 130 130 130 \n", "Drei Männer im Schnee, Erich Kästner 118 118 118 \n", "Hexenjagd, Arthur Miller 353 353 353 \n", "Viel Lärm um nichts, William Shakespeare 30 30 30 " ] }, "execution_count": 77, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bestellungen.groupby('Vorstellung').count()" ] }, { "cell_type": "code", "execution_count": 79, "id": "bda77c21-815d-4a0e-b109-c00bd39029df", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:38:24.976980Z", "iopub.status.busy": "2026-07-01T13:38:24.976833Z", "iopub.status.idle": "2026-07-01T13:38:24.984387Z", "shell.execute_reply": "2026-07-01T13:38:24.983958Z", "shell.execute_reply.started": "2026-07-01T13:38:24.976967Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BestellNrPositionVorstellungDatumSitzbereichSitzplatzSitzreihe
0103001Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne1.01.0
1103011Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne2.01.0
2103012Hexenjagd, Arthur Miller2024-09-21 19:30:00Tribüne3.01.0
3103021Hexenjagd, Arthur Miller2024-09-27 19:30:00Tribüne1.02.0
4103022Hexenjagd, Arthur Miller2024-09-27 19:30:00Tribüne2.02.0
\n", "
" ], "text/plain": [ " BestellNr Position Vorstellung Datum \\\n", "0 10300 1 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "1 10301 1 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "2 10301 2 Hexenjagd, Arthur Miller 2024-09-21 19:30:00 \n", "3 10302 1 Hexenjagd, Arthur Miller 2024-09-27 19:30:00 \n", "4 10302 2 Hexenjagd, Arthur Miller 2024-09-27 19:30:00 \n", "\n", " Sitzbereich Sitzplatz Sitzreihe \n", "0 Tribüne 1.0 1.0 \n", "1 Tribüne 2.0 1.0 \n", "2 Tribüne 3.0 1.0 \n", "3 Tribüne 1.0 2.0 \n", "4 Tribüne 2.0 2.0 " ] }, "execution_count": 79, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bestellungen.head()" ] }, { "cell_type": "code", "execution_count": 80, "id": "df62594f-4ded-47c4-bd02-9bb91c71b951", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:38:44.264302Z", "iopub.status.busy": "2026-07-01T13:38:44.264146Z", "iopub.status.idle": "2026-07-01T13:38:44.267605Z", "shell.execute_reply": "2026-07-01T13:38:44.267182Z", "shell.execute_reply.started": "2026-07-01T13:38:44.264286Z" } }, "outputs": [], "source": [ "vorBest = bestellungen[['Vorstellung','BestellNr']]" ] }, { "cell_type": "code", "execution_count": 83, "id": "fb975776-3de0-495f-8edc-7f3ec0ed62b6", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:39:18.581223Z", "iopub.status.busy": "2026-07-01T13:39:18.581047Z", "iopub.status.idle": "2026-07-01T13:39:18.587683Z", "shell.execute_reply": "2026-07-01T13:39:18.587289Z", "shell.execute_reply.started": "2026-07-01T13:39:18.581207Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BestellNr
Vorstellung
Der zerbrochne Krug, Heinrich von Kleist34
Die Räuber, Friedrich Schiller46
Drei Männer im Schnee, Erich Kästner52
Hexenjagd, Arthur Miller154
Viel Lärm um nichts, William Shakespeare11
\n", "
" ], "text/plain": [ " BestellNr\n", "Vorstellung \n", "Der zerbrochne Krug, Heinrich von Kleist 34\n", "Die Räuber, Friedrich Schiller 46\n", "Drei Männer im Schnee, Erich Kästner 52\n", "Hexenjagd, Arthur Miller 154\n", "Viel Lärm um nichts, William Shakespeare 11" ] }, "execution_count": 83, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vorBest.groupby('Vorstellung').nunique()" ] }, { "cell_type": "markdown", "id": "cell-25", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "### 3.4 Aufgabe \n", "\n", "Schreiben Sie eine Funktion `getraenke_nach_wochentag(df)`, die für die Getränkedaten \n", "einen neuen DataFrame berechnet und zurückgibt, der die durchschnittlichen Anzahlen \n", "für Wein, Bier, Wasser und Softdrinks je Wochentag enthält.\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "id": "cell-26", "metadata": {}, "outputs": [], "source": [ "# Hier ist Platz fuer ihre Loesung zu Aufgabe 3.4:\n" ] }, { "cell_type": "code", "execution_count": 87, "id": "cell-27", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:43:54.150379Z", "iopub.status.busy": "2026-07-01T13:43:54.150193Z", "iopub.status.idle": "2026-07-01T13:43:54.183106Z", "shell.execute_reply": "2026-07-01T13:43:54.182299Z", "shell.execute_reply.started": "2026-07-01T13:43:54.150363Z" } }, "outputs": [], "source": [ "getraenke = pd.read_csv('https://data.hsbo.de/theater_getraenke.csv')" ] }, { "cell_type": "code", "execution_count": 89, "id": "3c1ac15f-2cdf-4741-9144-94328a840233", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:44:25.611455Z", "iopub.status.busy": "2026-07-01T13:44:25.611224Z", "iopub.status.idle": "2026-07-01T13:44:25.616677Z", "shell.execute_reply": "2026-07-01T13:44:25.616251Z", "shell.execute_reply.started": "2026-07-01T13:44:25.611441Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TagWeinBierWasserLimonade
02024-09-2061249
12024-09-21714510
22024-09-222413
32024-09-27815511
42024-09-29816612
\n", "
" ], "text/plain": [ " Tag Wein Bier Wasser Limonade\n", "0 2024-09-20 6 12 4 9\n", "1 2024-09-21 7 14 5 10\n", "2 2024-09-22 2 4 1 3\n", "3 2024-09-27 8 15 5 11\n", "4 2024-09-29 8 16 6 12" ] }, "execution_count": 89, "metadata": {}, "output_type": "execute_result" } ], "source": [ "getraenke.head()" ] }, { "cell_type": "code", "execution_count": 90, "id": "f0187b25-9d14-42c4-b4f0-29d847b1e40b", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:45:08.910424Z", "iopub.status.busy": "2026-07-01T13:45:08.910255Z", "iopub.status.idle": "2026-07-01T13:45:08.914997Z", "shell.execute_reply": "2026-07-01T13:45:08.914625Z", "shell.execute_reply.started": "2026-07-01T13:45:08.910409Z" } }, "outputs": [], "source": [ "getraenke['Datum'] = pd.to_datetime(getraenke['Tag'])" ] }, { "cell_type": "code", "execution_count": 92, "id": "c06ac7e9-32f6-4b19-bf21-597c228a592f", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:46:23.973649Z", "iopub.status.busy": "2026-07-01T13:46:23.973373Z", "iopub.status.idle": "2026-07-01T13:46:23.979510Z", "shell.execute_reply": "2026-07-01T13:46:23.979082Z", "shell.execute_reply.started": "2026-07-01T13:46:23.973622Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TagWeinBierWasserLimonadeDatum
02024-09-20612492024-09-20
12024-09-217145102024-09-21
22024-09-2224132024-09-22
32024-09-278155112024-09-27
42024-09-298166122024-09-29
\n", "
" ], "text/plain": [ " Tag Wein Bier Wasser Limonade Datum\n", "0 2024-09-20 6 12 4 9 2024-09-20\n", "1 2024-09-21 7 14 5 10 2024-09-21\n", "2 2024-09-22 2 4 1 3 2024-09-22\n", "3 2024-09-27 8 15 5 11 2024-09-27\n", "4 2024-09-29 8 16 6 12 2024-09-29" ] }, "execution_count": 92, "metadata": {}, "output_type": "execute_result" } ], "source": [ "getraenke.head()" ] }, { "cell_type": "code", "execution_count": 99, "id": "17b0e164-fb84-43e7-b241-776daebccae7", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:47:31.227702Z", "iopub.status.busy": "2026-07-01T13:47:31.227552Z", "iopub.status.idle": "2026-07-01T13:47:31.230916Z", "shell.execute_reply": "2026-07-01T13:47:31.230378Z", "shell.execute_reply.started": "2026-07-01T13:47:31.227689Z" } }, "outputs": [], "source": [ "getraenke['Wochentag'] = getraenke['Datum'].dt.day_name()" ] }, { "cell_type": "code", "execution_count": 101, "id": "9af19c45-b3f1-4f7f-9cc2-d42969ec15a2", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:48:13.299931Z", "iopub.status.busy": "2026-07-01T13:48:13.299762Z", "iopub.status.idle": "2026-07-01T13:48:13.306137Z", "shell.execute_reply": "2026-07-01T13:48:13.305687Z", "shell.execute_reply.started": "2026-07-01T13:48:13.299918Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TagWeinBierWasserLimonadeDatumWochentag
02024-09-20612492024-09-20Friday
12024-09-217145102024-09-21Saturday
22024-09-2224132024-09-22Sunday
32024-09-278155112024-09-27Friday
42024-09-298166122024-09-29Sunday
\n", "
" ], "text/plain": [ " Tag Wein Bier Wasser Limonade Datum Wochentag\n", "0 2024-09-20 6 12 4 9 2024-09-20 Friday\n", "1 2024-09-21 7 14 5 10 2024-09-21 Saturday\n", "2 2024-09-22 2 4 1 3 2024-09-22 Sunday\n", "3 2024-09-27 8 15 5 11 2024-09-27 Friday\n", "4 2024-09-29 8 16 6 12 2024-09-29 Sunday" ] }, "execution_count": 101, "metadata": {}, "output_type": "execute_result" } ], "source": [ "getraenke.head()" ] }, { "cell_type": "code", "execution_count": 102, "id": "fafacff1-fab6-473d-a6ab-9bb4f3706eb0", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:48:39.381843Z", "iopub.status.busy": "2026-07-01T13:48:39.381685Z", "iopub.status.idle": "2026-07-01T13:48:39.385475Z", "shell.execute_reply": "2026-07-01T13:48:39.384988Z", "shell.execute_reply.started": "2026-07-01T13:48:39.381829Z" } }, "outputs": [], "source": [ "df_klein = getraenke[['Wochentag','Wein','Bier','Wasser','Limonade']]" ] }, { "cell_type": "code", "execution_count": 104, "id": "85808461-ecd4-42c1-a6ae-46cf07eed6e7", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:49:36.018793Z", "iopub.status.busy": "2026-07-01T13:49:36.018622Z", "iopub.status.idle": "2026-07-01T13:49:36.025192Z", "shell.execute_reply": "2026-07-01T13:49:36.024708Z", "shell.execute_reply.started": "2026-07-01T13:49:36.018778Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
WochentagWeinBierWasserLimonade
0Friday61249
1Saturday714510
2Sunday2413
3Friday815511
4Sunday816612
\n", "
" ], "text/plain": [ " Wochentag Wein Bier Wasser Limonade\n", "0 Friday 6 12 4 9\n", "1 Saturday 7 14 5 10\n", "2 Sunday 2 4 1 3\n", "3 Friday 8 15 5 11\n", "4 Sunday 8 16 6 12" ] }, "execution_count": 104, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_klein.head()" ] }, { "cell_type": "code", "execution_count": 106, "id": "41ee5850-5571-47d0-b4d5-db71aa053b1a", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:51:58.880457Z", "iopub.status.busy": "2026-07-01T13:51:58.880292Z", "iopub.status.idle": "2026-07-01T13:51:58.887219Z", "shell.execute_reply": "2026-07-01T13:51:58.886756Z", "shell.execute_reply.started": "2026-07-01T13:51:58.880442Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
WeinBierWasserLimonade
Wochentag
Friday34692350
Saturday15311123
Sunday1020715
Thursday714510
Tuesday2413
\n", "
" ], "text/plain": [ " Wein Bier Wasser Limonade\n", "Wochentag \n", "Friday 34 69 23 50\n", "Saturday 15 31 11 23\n", "Sunday 10 20 7 15\n", "Thursday 7 14 5 10\n", "Tuesday 2 4 1 3" ] }, "execution_count": 106, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_klein.groupby('Wochentag').sum()" ] }, { "cell_type": "code", "execution_count": 107, "id": "d8f86627-7c0a-4970-b1f5-839e2063f8e3", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:53:16.316095Z", "iopub.status.busy": "2026-07-01T13:53:16.315907Z", "iopub.status.idle": "2026-07-01T13:53:16.318931Z", "shell.execute_reply": "2026-07-01T13:53:16.318583Z", "shell.execute_reply.started": "2026-07-01T13:53:16.316078Z" } }, "outputs": [], "source": [ "def getraenke_nach_wochentag(df):\n", " df['Datum'] = pd.to_datetime(df['Tag'])\n", " df['Wochentag'] = pd.to_datetime(df['Datum']).dt.day_name()\n", " df_klein = df[['Wochentag','Bier','Wein','Wasser','Limonade']]\n", " return df_klein.groupby('Wochentag').sum()" ] }, { "cell_type": "code", "execution_count": 108, "id": "92b0bf7d-1e45-4afa-812f-d19adae3f7b8", "metadata": { "execution": { "iopub.execute_input": "2026-07-01T13:53:25.961009Z", "iopub.status.busy": "2026-07-01T13:53:25.960860Z", "iopub.status.idle": "2026-07-01T13:53:25.969326Z", "shell.execute_reply": "2026-07-01T13:53:25.968773Z", "shell.execute_reply.started": "2026-07-01T13:53:25.960996Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BierWeinWasserLimonade
Wochentag
Friday69342350
Saturday31151123
Sunday2010715
Thursday147510
Tuesday4213
\n", "
" ], "text/plain": [ " Bier Wein Wasser Limonade\n", "Wochentag \n", "Friday 69 34 23 50\n", "Saturday 31 15 11 23\n", "Sunday 20 10 7 15\n", "Thursday 14 7 5 10\n", "Tuesday 4 2 1 3" ] }, "execution_count": 108, "metadata": {}, "output_type": "execute_result" } ], "source": [ "getraenke_nach_wochentag(getraenke)" ] }, { "cell_type": "markdown", "id": "cell-28", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "### 3.5 Aufgabe \n", "\n", "Schreiben Sie eine Funktion `getraenke_nach_vorstellung(positionen_df, getraenke_df)`,\n", "die einen DataFrame mit den durchschnittlichen Anzahlen für Wein, Bier, Wasser und \n", "Softdrinks pro Vorstellung berechnet.\n", "\n", "*Beachten Sie*: Für das Verbinden der beiden Datensätze müssen die Datumsfelder gleich sein. \n", "Um aus einen (Datetime-)Datum das reine Datum ohne Zeitinformationen zu extrahieren, können \n", "Sie `dt.date` verwenden (genauso wie bei `dt.month` für den Monat).\n", "\n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "id": "cell-29", "metadata": {}, "outputs": [], "source": [ "# Hier ist Platz fuer ihre Loesung zu Aufgabe 3.5:\n" ] }, { "cell_type": "code", "execution_count": null, "id": "cell-30", "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "markdown", "id": "cell-31", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "# 4. Datenbankaufgabe \n", "\n", "Sie sind Mitglied einer Theatergruppe und möchten eine Datenbank für Ihren Ticketverkauf erstellen.\n", "\n", "Ihre Gruppe bietet verschiedene Veranstaltungen an. Zu den Veranstaltungen speichern Sie einen Namen und eine Kurzbeschreibung. Zu jeder Veranstaltung gibt es mindestens einen Veranstaltungstermin, natürlich sind mehrere Termine möglich. Ein solcher Termin ist genau einer Veranstaltung zugeordnet. Zu den Terminen speichern Sie das Datum und die Uhrzeit.\n", "\n", "Jeder Veranstaltungstermin findet an einem Veranstaltungsort statt. Für diesen speichern Sie den Namen, die Adresse und die Region. Ein Termin findet an genau einem Veranstaltungsort statt, an einem Ort können beliebig viele, auch keine Termine stattfinden.\n", "\n", "Für die Veranstaltungen verkaufen Sie Produkte. Zu einer Veranstaltung kann es mehrere Produkte geben, genauso wie ein Produkt für mehrere Veranstaltungen angeboten werden kann. Es gibt zwar Produkte ohne Veranstaltungen, aber keine Veranstaltungen ohne Produkt.\n", "\n", "Die Produkte unterscheiden sich zwischen Eintrittsprodukten und Merchandisingprodukten. Zu Eintrittsprodukten speichern Sie zusätzlich zu Namen und Preis einen *QR-Code*. Merchandisingprodukte können z.B. Pullis oder T-Shirts sein, dazu speichern Sie die Größe.\n", "\n", "In Ihrer Datenbank möchten Sie außerdem Ihre Kunden speichern. Zu diesen speichern Sie den Namen und die E-Mail.\n", "\n", "Für die eigentlichen Verkäufe speichern Sie, welche Produkte Sie für welchen Veranstaltungstermin an welchen Kunden \n", "\n", "\n" ] }, { "cell_type": "markdown", "id": "cell-32", "metadata": { "editable": false, "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", "
\n", " \n", "
\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 }, "source": [ "| Tabelle | Primärschlüssel | weitere Attribute |\n", "| --- | --- | --- |\n", "| | | |\n", "\n" ] }, { "cell_type": "markdown", "id": "cell-34", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "\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 Region des Veranstaltungsortes Westfalenhalle\n", "\n" ] }, { "cell_type": "raw", "id": "cell-37", "metadata": { "tags": [ "sql" ] }, "source": [] }, { "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 Veranstaltungen, die `Peter Pan` im Namen haben\n", "\n" ] }, { "cell_type": "raw", "id": "cell-40", "metadata": { "tags": [ "sql" ] }, "source": [] }, { "cell_type": "markdown", "id": "cell-41", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "\n" ] }, { "cell_type": "markdown", "id": "cell-42", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "#### 3. Den höchsten Preis Ihrer Produkte\n", "\n" ] }, { "cell_type": "raw", "id": "cell-43", "metadata": { "tags": [ "sql" ] }, "source": [] }, { "cell_type": "markdown", "id": "cell-44", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "\n" ] }, { "cell_type": "markdown", "id": "cell-45", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "#### 4. Das Datum der Termine von Veranstaltungen mit dem Namen `Das Neinhorn`\n", "\n" ] }, { "cell_type": "raw", "id": "cell-46", "metadata": { "tags": [ "sql" ] }, "source": [] }, { "cell_type": "markdown", "id": "cell-47", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "\n" ] }, { "cell_type": "markdown", "id": "cell-48", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "#### 5. Die Namen der Produkte, die für Veranstaltungen mit dem Namen `Das Neinhorn` angeboten werden können\n", "\n" ] }, { "cell_type": "raw", "id": "cell-49", "metadata": { "tags": [ "sql" ] }, "source": [] }, { "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 Nummern und Namen der Veranstaltungen und die Anzahl der dazugehörigen Termine\n", "\n" ] }, { "cell_type": "raw", "id": "cell-52", "metadata": { "tags": [ "sql" ] }, "source": [] }, { "cell_type": "markdown", "id": "cell-53", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "\n" ] }, { "cell_type": "markdown", "id": "cell-54", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "#### 7. Die Anzahl der *verschiedenen* Kunden die Veranstaltungen in der *Westfalenhalle* gebucht haben.\n", "\n" ] }, { "cell_type": "raw", "id": "cell-55", "metadata": { "tags": [ "sql" ] }, "source": [] }, { "cell_type": "markdown", "id": "cell-56", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "\n" ] }, { "cell_type": "markdown", "id": "cell-57", "metadata": { "editable": false, "tags": [ "aufgabe" ] }, "source": [ "#### 8. Die Anzahl der T-Shirt Größen, die für die Veranstaltung `Das Neinhorn` in der Westfalenhalle verkauft wurden, gruppiert nach den Größen.\n", "\n" ] }, { "cell_type": "raw", "id": "cell-58", "metadata": { "tags": [ "sql" ] }, "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.13" } }, "nbformat": 4, "nbformat_minor": 5 }