02_DataAccess.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Zugang zu Daten\n",
    "\n",
    "Es bestehen viele Wege, um Daten für eine Auswertung zu beziehen. Wir wollen daher in diesem Abschnitt auf ein paar gängige Methoden des Datenbezugs eingehen:\n",
    "\n",
    "* Download und Import kleinerer Dateien wie z.B. csv- oder txt-Dateien\n",
    "* Verwendung von Datenbanken\n",
    "* Zugang zu Daten mittels APIs\n",
    "\n",
    "Dies soll lediglich als kleiner Einblick verstanden werden, um eine Idee davon zu erhalten, welche Wege des Datenzugangs bestehen. Darüber hinaus existieren viele weitere Möglichkeiten. Es gilt jedoch für nahezu jeden dieser Wege, dass er mit steigenden Ansprüchen komplexer werden kann und man für detaillierte Mittel und Wege weiteres Wissen aufbauen muss."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dateiimport\n",
    "\n",
    "Mit die gewöhnlichste Art Zugang zu Daten zu erhalten, ist das Teilen von Dateien. Übliche Formate sind csv oder txt Dateien, bei denen in der Regel jede Zeile einer Beobachtung entspricht und die Variablen durch Komma- oder andere Trennzeichen getrennt sind.\n",
    "\n",
    "Im der unteren Zelle sehen Sie wie mittels pandas eine csv-Datei eingelesen wird. Sehr ähnlich funktioniert dies auch für txt-Dateien. Je nachdem wie die Daten in der Datei getrennt sind (z.B. Leerzeichen, Komma, etc.), muss dies beim Einlesen der Datei explizit angegben werden. Genauere Hilfe hierzu erhalten Sie durch den *help(pd.read_csv)* Aufruf. Die Datei aus diesem Beispiel (dann mit aktuellen Daten) können Sie selbst relativ leicht unter diesem [Link](https://finance.yahoo.com/quote/DAX/history?p=DAX) durch Klicken auf den Download-Button herunterladen. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-07-19</td>\n",
       "      <td>23.180000</td>\n",
       "      <td>23.719999</td>\n",
       "      <td>23.170000</td>\n",
       "      <td>23.590000</td>\n",
       "      <td>22.978786</td>\n",
       "      <td>74900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-07-20</td>\n",
       "      <td>23.430000</td>\n",
       "      <td>23.600000</td>\n",
       "      <td>23.280001</td>\n",
       "      <td>23.350000</td>\n",
       "      <td>22.745005</td>\n",
       "      <td>17100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-07-21</td>\n",
       "      <td>23.150000</td>\n",
       "      <td>23.510000</td>\n",
       "      <td>23.150000</td>\n",
       "      <td>23.510000</td>\n",
       "      <td>22.900860</td>\n",
       "      <td>4900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-07-22</td>\n",
       "      <td>23.740000</td>\n",
       "      <td>23.740000</td>\n",
       "      <td>23.330000</td>\n",
       "      <td>23.330000</td>\n",
       "      <td>22.725523</td>\n",
       "      <td>4700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-07-25</td>\n",
       "      <td>23.600000</td>\n",
       "      <td>23.600000</td>\n",
       "      <td>23.340000</td>\n",
       "      <td>23.440001</td>\n",
       "      <td>22.832674</td>\n",
       "      <td>9000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>2023-07-12</td>\n",
       "      <td>29.770000</td>\n",
       "      <td>30.011000</td>\n",
       "      <td>29.770000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>6400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>2023-07-13</td>\n",
       "      <td>30.389999</td>\n",
       "      <td>30.459999</td>\n",
       "      <td>30.350000</td>\n",
       "      <td>30.459999</td>\n",
       "      <td>30.459999</td>\n",
       "      <td>4900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>2023-07-14</td>\n",
       "      <td>30.309999</td>\n",
       "      <td>30.309999</td>\n",
       "      <td>30.309999</td>\n",
       "      <td>30.309999</td>\n",
       "      <td>30.309999</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>2023-07-17</td>\n",
       "      <td>30.170000</td>\n",
       "      <td>30.430000</td>\n",
       "      <td>30.120001</td>\n",
       "      <td>30.430000</td>\n",
       "      <td>30.430000</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>2023-07-18</td>\n",
       "      <td>30.299999</td>\n",
       "      <td>30.410000</td>\n",
       "      <td>30.290001</td>\n",
       "      <td>30.410000</td>\n",
       "      <td>30.410000</td>\n",
       "      <td>26100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>251 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Date       Open       High        Low      Close  Adj Close  Volume\n",
       "0    2022-07-19  23.180000  23.719999  23.170000  23.590000  22.978786   74900\n",
       "1    2022-07-20  23.430000  23.600000  23.280001  23.350000  22.745005   17100\n",
       "2    2022-07-21  23.150000  23.510000  23.150000  23.510000  22.900860    4900\n",
       "3    2022-07-22  23.740000  23.740000  23.330000  23.330000  22.725523    4700\n",
       "4    2022-07-25  23.600000  23.600000  23.340000  23.440001  22.832674    9000\n",
       "..          ...        ...        ...        ...        ...        ...     ...\n",
       "246  2023-07-12  29.770000  30.011000  29.770000  30.000000  30.000000    6400\n",
       "247  2023-07-13  30.389999  30.459999  30.350000  30.459999  30.459999    4900\n",
       "248  2023-07-14  30.309999  30.309999  30.309999  30.309999  30.309999       0\n",
       "249  2023-07-17  30.170000  30.430000  30.120001  30.430000  30.430000    2900\n",
       "250  2023-07-18  30.299999  30.410000  30.290001  30.410000  30.410000   26100\n",
       "\n",
       "[251 rows x 7 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "dax_df = pd.read_csv(\"../Daten/DAX.csv\")\n",
    "dax_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Datenbanken\n",
    "\n",
    "Das Speichern und Abrufen mittels csv, txt oder ähnlicher Dateiformate ist in manchen Fällen nicht ausreichend. Beispielsweise erreichen csv oder Excel Dateien ihr Limit bei ca. einer Million Beobachtungen. Dies mag viel erscheinen, ist es jedoch in manchen Fällen nicht. Zudem ist es oft besser Daten in etwas komplexerer Form strukturieren zu können. Hierfür bieten sich Datenbanken an, über die Daten mittels einer \"query language\" abgerufen werden können. Eine der für diesen Zweck bekanntesten Sprachen (für relationelle Datenbanken) ist Structured Query Language - SQL. Hiermit können spezifische Werte durch einzelne Befehle aus der Datenbank abgefragt werden. Um Ihnen ein kleines Beispiel zu geben, sehen Sie in den nächsten drei Zellen, wie Daten in eine Datenbank geschrieben und wieder abgefragt werden können."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "\n",
    "# Aufbau einer Verbindung zur Datenbank dax.sqlite\n",
    "conn = sqlite3.connect(\"dax.sqlite\")\n",
    "# Schreiben von Daten mit Hilfe eines pandas DataFrame\n",
    "dax_df.to_sql(con = conn, name = \"ohlc\", if_exists=\"replace\")\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(0, '2022-07-19', 23.18, 23.719999, 23.17, 23.59, 22.978786, 74900)\n",
      "(1, '2022-07-20', 23.43, 23.6, 23.280001, 23.35, 22.745005, 17100)\n",
      "(2, '2022-07-21', 23.15, 23.51, 23.15, 23.51, 22.90086, 4900)\n",
      "(3, '2022-07-22', 23.74, 23.74, 23.33, 23.33, 22.725523, 4700)\n",
      "(4, '2022-07-25', 23.6, 23.6, 23.34, 23.440001, 22.832674, 9000)\n",
      "(5, '2022-07-26', 23.040001, 23.139999, 22.93, 22.965, 22.36998, 3500)\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "# Beispiel für den Abruf aller Daten aus dem Tabellenblatt ohlc\n",
    "conn = sqlite3.connect(\"dax.sqlite\")\n",
    "curs = conn.cursor()\n",
    "# dies ist der SQL Befehl\n",
    "sql_query = \"SELECT * FROM ohlc\"\n",
    "res = curs.execute(sql_query)\n",
    "for i, line in enumerate(res):\n",
    "    print(line)\n",
    "    if i == 5:\n",
    "        break\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(9, '2022-08-01', 24.1, 24.209999, 23.771, 24.004, 23.382059, 44200)\n",
      "(10, '2022-08-02', 23.82, 23.92, 23.639999, 23.639999, 23.027491, 18600)\n",
      "(11, '2022-08-03', 23.940001, 24.08, 23.700001, 24.040001, 23.417128, 21600)\n",
      "(12, '2022-08-04', 24.215, 24.32, 24.195, 24.23, 23.602203, 11200)\n",
      "(13, '2022-08-05', 24.0, 24.15, 23.940001, 24.120001, 23.495054, 4200)\n",
      "(14, '2022-08-08', 24.24, 24.35, 24.052, 24.156, 23.530123, 8000)\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "# Beispiel für den Abruf aller Daten ab einem bestimmten Daten aus dem Tabellenblatt ohlc\n",
    "conn = sqlite3.connect(\"dax.sqlite\")\n",
    "curs = conn.cursor()\n",
    "sql_query = \"SELECT * FROM ohlc WHERE Date>='2022-08-01'\"\n",
    "res = curs.execute(sql_query)\n",
    "for i, line in enumerate(res):\n",
    "    print(line)\n",
    "    if i == 5:\n",
    "        break\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## APIs\n",
    "\n",
    "Oft teilen Firmen Daten auf ihren Servern mittels eines Application Programming Interfaces (API). Sie können sich dies wie eine Speisekarte in einem Restaurant vorstellen. Der Anbieter (das Restaurant) hat Daten auf seinem Server (die Gerichte in der Küche). Um Essen bestellen zu können, benötigen Sie die Informationen zu den verschiedenen Gerichten. Dies wären die Endpunkte des APIs. Wenn Sie diese kennen, sind Sie in der Lage eine Ihren Wünschen entsprechende Bestellung aufzugeben und bekommen im besten Fall unmittelbar die angefragten Daten. \n",
    "\n",
    "Als kleines Beispiel benützen wir die Star Wars API. Folgende Endpunkte sind beispielsweise verfügbar:\n",
    "\n",
    "* https://swapi.dev/api/films\n",
    "* https://swapi.dev/api/people \n",
    "* https://swapi.dev/api/planets\n",
    "* https://swapi.dev/api/species\n",
    "* https://swapi.dev/api/starships\n",
    "* https://swapi.dev/api/vehicles\n",
    "\n",
    "Wenn Sie nun Informationen zu Personen aus dem Star Wars Universum benötigen, können Sie den Endpunkt https://swapi.dev/api/people verwenden. Mittels Python senden Sie eine GET Anfrage, um die Daten zu bestellen. Sie erhalten die entsprchenden Daten im json Format zurück, welches für diese Art der Datenabfrage sehr beliebt ist. Durch pandas lassen sich die Daten schnell und einfach in einen leicht zu analysierenden Datensatz konvertieren."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'count': 82,\n",
       " 'next': 'https://swapi.dev/api/people/?page=2',\n",
       " 'previous': None,\n",
       " 'results': [{'name': 'Luke Skywalker',\n",
       "   'height': '172',\n",
       "   'mass': '77',\n",
       "   'hair_color': 'blond',\n",
       "   'skin_color': 'fair',\n",
       "   'eye_color': 'blue',\n",
       "   'birth_year': '19BBY',\n",
       "   'gender': 'male',\n",
       "   'homeworld': 'https://swapi.dev/api/planets/1/',\n",
       "   'films': ['https://swapi.dev/api/films/1/',\n",
       "    'https://swapi.dev/api/films/2/',\n",
       "    'https://swapi.dev/api/films/3/',\n",
       "    'https://swapi.dev/api/films/6/'],\n",
       "   'species': [],\n",
       "   'vehicles': ['https://swapi.dev/api/vehicles/14/',\n",
       "    'https://swapi.dev/api/vehicles/30/'],\n",
       "   'starships': ['https://swapi.dev/api/starships/12/',\n",
       "    'https://swapi.dev/api/starships/22/'],\n",
       "   'created': '2014-12-09T13:50:51.644000Z',\n",
       "   'edited': '2014-12-20T21:17:56.891000Z',\n",
       "   'url': 'https://swapi.dev/api/people/1/'},\n",
       "  {'name': 'C-3PO',\n",
       "   'height': '167',\n",
       "   'mass': '75',\n",
       "   'hair_color': 'n/a',\n",
       "   'skin_color': 'gold',\n",
       "   'eye_color': 'yellow',\n",
       "   'birth_year': '112BBY',\n",
       "   'gender': 'n/a',\n",
       "   'homeworld': 'https://swapi.dev/api/planets/1/',\n",
       "   'films': ['https://swapi.dev/api/films/1/',\n",
       "    'https://swapi.dev/api/films/2/',\n",
       "    'https://swapi.dev/api/films/3/',\n",
       "    'https://swapi.dev/api/films/4/',\n",
       "    'https://swapi.dev/api/films/5/',\n",
       "    'https://swapi.dev/api/films/6/'],\n",
       "   'species': ['https://swapi.dev/api/species/2/'],\n",
       "   'vehicles': [],\n",
       "   'starships': [],\n",
       "   'created': '2014-12-10T15:10:51.357000Z',\n",
       "   'edited': '2014-12-20T21:17:50.309000Z',\n",
       "   'url': 'https://swapi.dev/api/people/2/'},\n",
       "  {'name': 'R2-D2',\n",
       "   'height': '96',\n",
       "   'mass': '32',\n",
       "   'hair_color': 'n/a',\n",
       "   'skin_color': 'white, blue',\n",
       "   'eye_color': 'red',\n",
       "   'birth_year': '33BBY',\n",
       "   'gender': 'n/a',\n",
       "   'homeworld': 'https://swapi.dev/api/planets/8/',\n",
       "   'films': ['https://swapi.dev/api/films/1/',\n",
       "    'https://swapi.dev/api/films/2/',\n",
       "    'https://swapi.dev/api/films/3/',\n",
       "    'https://swapi.dev/api/films/4/',\n",
       "    'https://swapi.dev/api/films/5/',\n",
       "    'https://swapi.dev/api/films/6/'],\n",
       "   'species': ['https://swapi.dev/api/species/2/'],\n",
       "   'vehicles': [],\n",
       "   'starships': [],\n",
       "   'created': '2014-12-10T15:11:50.376000Z',\n",
       "   'edited': '2014-12-20T21:17:50.311000Z',\n",
       "   'url': 'https://swapi.dev/api/people/3/'},\n",
       "  {'name': 'Darth Vader',\n",
       "   'height': '202',\n",
       "   'mass': '136',\n",
       "   'hair_color': 'none',\n",
       "   'skin_color': 'white',\n",
       "   'eye_color': 'yellow',\n",
       "   'birth_year': '41.9BBY',\n",
       "   'gender': 'male',\n",
       "   'homeworld': 'https://swapi.dev/api/planets/1/',\n",
       "   'films': ['https://swapi.dev/api/films/1/',\n",
       "    'https://swapi.dev/api/films/2/',\n",
       "    'https://swapi.dev/api/films/3/',\n",
       "    'https://swapi.dev/api/films/6/'],\n",
       "   'species': [],\n",
       "   'vehicles': [],\n",
       "   'starships': ['https://swapi.dev/api/starships/13/'],\n",
       "   'created': '2014-12-10T15:18:20.704000Z',\n",
       "   'edited': '2014-12-20T21:17:50.313000Z',\n",
       "   'url': 'https://swapi.dev/api/people/4/'},\n",
       "  {'name': 'Leia Organa',\n",
       "   'height': '150',\n",
       "   'mass': '49',\n",
       "   'hair_color': 'brown',\n",
       "   'skin_color': 'light',\n",
       "   'eye_color': 'brown',\n",
       "   'birth_year': '19BBY',\n",
       "   'gender': 'female',\n",
       "   'homeworld': 'https://swapi.dev/api/planets/2/',\n",
       "   'films': ['https://swapi.dev/api/films/1/',\n",
       "    'https://swapi.dev/api/films/2/',\n",
       "    'https://swapi.dev/api/films/3/',\n",
       "    'https://swapi.dev/api/films/6/'],\n",
       "   'species': [],\n",
       "   'vehicles': ['https://swapi.dev/api/vehicles/30/'],\n",
       "   'starships': [],\n",
       "   'created': '2014-12-10T15:20:09.791000Z',\n",
       "   'edited': '2014-12-20T21:17:50.315000Z',\n",
       "   'url': 'https://swapi.dev/api/people/5/'},\n",
       "  {'name': 'Owen Lars',\n",
       "   'height': '178',\n",
       "   'mass': '120',\n",
       "   'hair_color': 'brown, grey',\n",
       "   'skin_color': 'light',\n",
       "   'eye_color': 'blue',\n",
       "   'birth_year': '52BBY',\n",
       "   'gender': 'male',\n",
       "   'homeworld': 'https://swapi.dev/api/planets/1/',\n",
       "   'films': ['https://swapi.dev/api/films/1/',\n",
       "    'https://swapi.dev/api/films/5/',\n",
       "    'https://swapi.dev/api/films/6/'],\n",
       "   'species': [],\n",
       "   'vehicles': [],\n",
       "   'starships': [],\n",
       "   'created': '2014-12-10T15:52:14.024000Z',\n",
       "   'edited': '2014-12-20T21:17:50.317000Z',\n",
       "   'url': 'https://swapi.dev/api/people/6/'},\n",
       "  {'name': 'Beru Whitesun lars',\n",
       "   'height': '165',\n",
       "   'mass': '75',\n",
       "   'hair_color': 'brown',\n",
       "   'skin_color': 'light',\n",
       "   'eye_color': 'blue',\n",
       "   'birth_year': '47BBY',\n",
       "   'gender': 'female',\n",
       "   'homeworld': 'https://swapi.dev/api/planets/1/',\n",
       "   'films': ['https://swapi.dev/api/films/1/',\n",
       "    'https://swapi.dev/api/films/5/',\n",
       "    'https://swapi.dev/api/films/6/'],\n",
       "   'species': [],\n",
       "   'vehicles': [],\n",
       "   'starships': [],\n",
       "   'created': '2014-12-10T15:53:41.121000Z',\n",
       "   'edited': '2014-12-20T21:17:50.319000Z',\n",
       "   'url': 'https://swapi.dev/api/people/7/'},\n",
       "  {'name': 'R5-D4',\n",
       "   'height': '97',\n",
       "   'mass': '32',\n",
       "   'hair_color': 'n/a',\n",
       "   'skin_color': 'white, red',\n",
       "   'eye_color': 'red',\n",
       "   'birth_year': 'unknown',\n",
       "   'gender': 'n/a',\n",
       "   'homeworld': 'https://swapi.dev/api/planets/1/',\n",
       "   'films': ['https://swapi.dev/api/films/1/'],\n",
       "   'species': ['https://swapi.dev/api/species/2/'],\n",
       "   'vehicles': [],\n",
       "   'starships': [],\n",
       "   'created': '2014-12-10T15:57:50.959000Z',\n",
       "   'edited': '2014-12-20T21:17:50.321000Z',\n",
       "   'url': 'https://swapi.dev/api/people/8/'},\n",
       "  {'name': 'Biggs Darklighter',\n",
       "   'height': '183',\n",
       "   'mass': '84',\n",
       "   'hair_color': 'black',\n",
       "   'skin_color': 'light',\n",
       "   'eye_color': 'brown',\n",
       "   'birth_year': '24BBY',\n",
       "   'gender': 'male',\n",
       "   'homeworld': 'https://swapi.dev/api/planets/1/',\n",
       "   'films': ['https://swapi.dev/api/films/1/'],\n",
       "   'species': [],\n",
       "   'vehicles': [],\n",
       "   'starships': ['https://swapi.dev/api/starships/12/'],\n",
       "   'created': '2014-12-10T15:59:50.509000Z',\n",
       "   'edited': '2014-12-20T21:17:50.323000Z',\n",
       "   'url': 'https://swapi.dev/api/people/9/'},\n",
       "  {'name': 'Obi-Wan Kenobi',\n",
       "   'height': '182',\n",
       "   'mass': '77',\n",
       "   'hair_color': 'auburn, white',\n",
       "   'skin_color': 'fair',\n",
       "   'eye_color': 'blue-gray',\n",
       "   'birth_year': '57BBY',\n",
       "   'gender': 'male',\n",
       "   'homeworld': 'https://swapi.dev/api/planets/20/',\n",
       "   'films': ['https://swapi.dev/api/films/1/',\n",
       "    'https://swapi.dev/api/films/2/',\n",
       "    'https://swapi.dev/api/films/3/',\n",
       "    'https://swapi.dev/api/films/4/',\n",
       "    'https://swapi.dev/api/films/5/',\n",
       "    'https://swapi.dev/api/films/6/'],\n",
       "   'species': [],\n",
       "   'vehicles': ['https://swapi.dev/api/vehicles/38/'],\n",
       "   'starships': ['https://swapi.dev/api/starships/48/',\n",
       "    'https://swapi.dev/api/starships/59/',\n",
       "    'https://swapi.dev/api/starships/64/',\n",
       "    'https://swapi.dev/api/starships/65/',\n",
       "    'https://swapi.dev/api/starships/74/'],\n",
       "   'created': '2014-12-10T16:16:29.192000Z',\n",
       "   'edited': '2014-12-20T21:17:50.325000Z',\n",
       "   'url': 'https://swapi.dev/api/people/10/'}]}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "\n",
    "# Senden der GET Anfrage\n",
    "r = requests.get(\"https://swapi.dev/api/people/\")\n",
    "# So sehen die übermittelten Daten im json-Format aus\n",
    "# In Python werden diese als dictionary dargestellt\n",
    "r.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>height</th>\n",
       "      <th>mass</th>\n",
       "      <th>hair_color</th>\n",
       "      <th>skin_color</th>\n",
       "      <th>eye_color</th>\n",
       "      <th>birth_year</th>\n",
       "      <th>gender</th>\n",
       "      <th>homeworld</th>\n",
       "      <th>films</th>\n",
       "      <th>species</th>\n",
       "      <th>vehicles</th>\n",
       "      <th>starships</th>\n",
       "      <th>created</th>\n",
       "      <th>edited</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Luke Skywalker</td>\n",
       "      <td>172</td>\n",
       "      <td>77</td>\n",
       "      <td>blond</td>\n",
       "      <td>fair</td>\n",
       "      <td>blue</td>\n",
       "      <td>19BBY</td>\n",
       "      <td>male</td>\n",
       "      <td>https://swapi.dev/api/planets/1/</td>\n",
       "      <td>[https://swapi.dev/api/films/1/, https://swapi...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[https://swapi.dev/api/vehicles/14/, https://s...</td>\n",
       "      <td>[https://swapi.dev/api/starships/12/, https://...</td>\n",
       "      <td>2014-12-09T13:50:51.644000Z</td>\n",
       "      <td>2014-12-20T21:17:56.891000Z</td>\n",
       "      <td>https://swapi.dev/api/people/1/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C-3PO</td>\n",
       "      <td>167</td>\n",
       "      <td>75</td>\n",
       "      <td>n/a</td>\n",
       "      <td>gold</td>\n",
       "      <td>yellow</td>\n",
       "      <td>112BBY</td>\n",
       "      <td>n/a</td>\n",
       "      <td>https://swapi.dev/api/planets/1/</td>\n",
       "      <td>[https://swapi.dev/api/films/1/, https://swapi...</td>\n",
       "      <td>[https://swapi.dev/api/species/2/]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>2014-12-10T15:10:51.357000Z</td>\n",
       "      <td>2014-12-20T21:17:50.309000Z</td>\n",
       "      <td>https://swapi.dev/api/people/2/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>R2-D2</td>\n",
       "      <td>96</td>\n",
       "      <td>32</td>\n",
       "      <td>n/a</td>\n",
       "      <td>white, blue</td>\n",
       "      <td>red</td>\n",
       "      <td>33BBY</td>\n",
       "      <td>n/a</td>\n",
       "      <td>https://swapi.dev/api/planets/8/</td>\n",
       "      <td>[https://swapi.dev/api/films/1/, https://swapi...</td>\n",
       "      <td>[https://swapi.dev/api/species/2/]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>2014-12-10T15:11:50.376000Z</td>\n",
       "      <td>2014-12-20T21:17:50.311000Z</td>\n",
       "      <td>https://swapi.dev/api/people/3/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Darth Vader</td>\n",
       "      <td>202</td>\n",
       "      <td>136</td>\n",
       "      <td>none</td>\n",
       "      <td>white</td>\n",
       "      <td>yellow</td>\n",
       "      <td>41.9BBY</td>\n",
       "      <td>male</td>\n",
       "      <td>https://swapi.dev/api/planets/1/</td>\n",
       "      <td>[https://swapi.dev/api/films/1/, https://swapi...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[https://swapi.dev/api/starships/13/]</td>\n",
       "      <td>2014-12-10T15:18:20.704000Z</td>\n",
       "      <td>2014-12-20T21:17:50.313000Z</td>\n",
       "      <td>https://swapi.dev/api/people/4/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Leia Organa</td>\n",
       "      <td>150</td>\n",
       "      <td>49</td>\n",
       "      <td>brown</td>\n",
       "      <td>light</td>\n",
       "      <td>brown</td>\n",
       "      <td>19BBY</td>\n",
       "      <td>female</td>\n",
       "      <td>https://swapi.dev/api/planets/2/</td>\n",
       "      <td>[https://swapi.dev/api/films/1/, https://swapi...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[https://swapi.dev/api/vehicles/30/]</td>\n",
       "      <td>[]</td>\n",
       "      <td>2014-12-10T15:20:09.791000Z</td>\n",
       "      <td>2014-12-20T21:17:50.315000Z</td>\n",
       "      <td>https://swapi.dev/api/people/5/</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             name height mass hair_color   skin_color eye_color birth_year  \\\n",
       "0  Luke Skywalker    172   77      blond         fair      blue      19BBY   \n",
       "1           C-3PO    167   75        n/a         gold    yellow     112BBY   \n",
       "2           R2-D2     96   32        n/a  white, blue       red      33BBY   \n",
       "3     Darth Vader    202  136       none        white    yellow    41.9BBY   \n",
       "4     Leia Organa    150   49      brown        light     brown      19BBY   \n",
       "\n",
       "   gender                         homeworld  \\\n",
       "0    male  https://swapi.dev/api/planets/1/   \n",
       "1     n/a  https://swapi.dev/api/planets/1/   \n",
       "2     n/a  https://swapi.dev/api/planets/8/   \n",
       "3    male  https://swapi.dev/api/planets/1/   \n",
       "4  female  https://swapi.dev/api/planets/2/   \n",
       "\n",
       "                                               films  \\\n",
       "0  [https://swapi.dev/api/films/1/, https://swapi...   \n",
       "1  [https://swapi.dev/api/films/1/, https://swapi...   \n",
       "2  [https://swapi.dev/api/films/1/, https://swapi...   \n",
       "3  [https://swapi.dev/api/films/1/, https://swapi...   \n",
       "4  [https://swapi.dev/api/films/1/, https://swapi...   \n",
       "\n",
       "                              species  \\\n",
       "0                                  []   \n",
       "1  [https://swapi.dev/api/species/2/]   \n",
       "2  [https://swapi.dev/api/species/2/]   \n",
       "3                                  []   \n",
       "4                                  []   \n",
       "\n",
       "                                            vehicles  \\\n",
       "0  [https://swapi.dev/api/vehicles/14/, https://s...   \n",
       "1                                                 []   \n",
       "2                                                 []   \n",
       "3                                                 []   \n",
       "4               [https://swapi.dev/api/vehicles/30/]   \n",
       "\n",
       "                                           starships  \\\n",
       "0  [https://swapi.dev/api/starships/12/, https://...   \n",
       "1                                                 []   \n",
       "2                                                 []   \n",
       "3              [https://swapi.dev/api/starships/13/]   \n",
       "4                                                 []   \n",
       "\n",
       "                       created                       edited  \\\n",
       "0  2014-12-09T13:50:51.644000Z  2014-12-20T21:17:56.891000Z   \n",
       "1  2014-12-10T15:10:51.357000Z  2014-12-20T21:17:50.309000Z   \n",
       "2  2014-12-10T15:11:50.376000Z  2014-12-20T21:17:50.311000Z   \n",
       "3  2014-12-10T15:18:20.704000Z  2014-12-20T21:17:50.313000Z   \n",
       "4  2014-12-10T15:20:09.791000Z  2014-12-20T21:17:50.315000Z   \n",
       "\n",
       "                               url  \n",
       "0  https://swapi.dev/api/people/1/  \n",
       "1  https://swapi.dev/api/people/2/  \n",
       "2  https://swapi.dev/api/people/3/  \n",
       "3  https://swapi.dev/api/people/4/  \n",
       "4  https://swapi.dev/api/people/5/  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# mittels pandas lassen sich die json Daten in eine DataFrame konvertieren\n",
    "star_wars_pepole_df = pd.DataFrame(r.json()['results'])\n",
    "star_wars_pepole_df.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}