praesentation_2019_12_11.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>\n",
       "td {\n",
       "    font-size: 20px;\n",
       "}\n",
       "thead th {\n",
       "    font-size: 20px;\n",
       "}\n",
       "</style>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%html\n",
    "<style>\n",
    "td {\n",
    "    font-size: 20px;\n",
    "}\n",
    "thead th {\n",
    "    font-size: 20px;\n",
    "}\n",
    "</style>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Mögliche Lösungen für Übung 1\n",
    "\n",
    "- *pandas* importieren und Datei laden. Dabei auf den korrekten Dateipfad achten. Da die Datei schon Kommas zur Separierung verwendet, müssen wir keinen Separator definieren."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\"C:/Users/marku/Desktop/python_winter_2019_2020/spieler_daten_final.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>land</th>\n",
       "      <th>geburtsjahr</th>\n",
       "      <th>groesse</th>\n",
       "      <th>position</th>\n",
       "      <th>wm_jahr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ferdinand Adams</td>\n",
       "      <td>Belgien</td>\n",
       "      <td>1903.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Juan Aguilera</td>\n",
       "      <td>Chile</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Francisco Aguirre</td>\n",
       "      <td>Paraguay</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mittelfeld</td>\n",
       "      <td>1930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mario Alborta</td>\n",
       "      <td>Bolivien</td>\n",
       "      <td>NaN</td>\n",
       "      <td>172 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Efrain Amezcua</td>\n",
       "      <td>Mexiko</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mittelfeld</td>\n",
       "      <td>1930</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                name      land  geburtsjahr groesse    position  wm_jahr\n",
       "0    Ferdinand Adams   Belgien       1903.0     NaN     Stürmer     1930\n",
       "1      Juan Aguilera     Chile          NaN     NaN     Stürmer     1930\n",
       "2  Francisco Aguirre  Paraguay          NaN     NaN  Mittelfeld     1930\n",
       "3      Mario Alborta  Bolivien          NaN  172 cm     Stürmer     1930\n",
       "4     Efrain Amezcua    Mexiko          NaN     NaN  Mittelfeld     1930"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 1. Wie viele Weltmeisterschaften gab es?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Hier bietet es sich an, die Variable *wm_jahr* zu nutzen und die Anzahl der möglichen Ausprägungen zu zählen. Eine mögliche Lösung wählt die Spalte *wm_jahr* aus und verwendet die Funktion **unique()**, um Zeilen mit Duplikaten zu unterdrücken. Die Daten werden als ein Numpy **ndarray**, eine mehrdimensionale Datenstruktur, gespeichert:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1930, 1934, 1938, 1950, 1954, 1958, 1962, 1966, 1970, 1974, 1978,\n",
       "       1982, 1986, 1990, 1994, 1998, 2002, 2006, 2010, 2014, 2018],\n",
       "      dtype=int64)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['wm_jahr'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Mit **len()** können Sie dann bestimmen, wie viele Werte in dem **ndarray** sind:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df['wm_jahr'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Wie viele Spieler waren bei jeder WM dabei?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Hier können Sie die Funktion **value_counts()** verwenden. Sie müssen nur zuerst die Spalte auswählen, in der Sie Werte zählen möchten:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2014    738\n",
       "2010    737\n",
       "2002    737\n",
       "2018    736\n",
       "2006    735\n",
       "1998    705\n",
       "1990    529\n",
       "1986    528\n",
       "1994    528\n",
       "1982    526\n",
       "1974    352\n",
       "1958    352\n",
       "1966    352\n",
       "1978    352\n",
       "1962    352\n",
       "1954    350\n",
       "1970    349\n",
       "1934    332\n",
       "1938    320\n",
       "1950    280\n",
       "1930    254\n",
       "Name: wm_jahr, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"wm_jahr\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Wie viele Spieler insgesamt haben an diesen Weltmeisterschaften teilgenommen?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Hier hängt es davon ab, ob Sie Spieler, die an mehreren Weltmeisterschaften teilgenommen haben, nur einmal oder mehrfach zählen. Wenn sie nur einmal gezählt werden, können Sie wie bei Frage 1 vorgehen:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7843"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[\"name\"].unique()) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Wenn sie mehrfach gezählt werden, können Sie das Ganze noch vereinfachen. Dabei gehen wir davon aus, dass es nicht mehrere Spieler mit dem gleichen Namen gibt:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10144"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[\"name\"]) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Wie viele Spieler haben insgesamt bei allen Weltmeisterschaften für Deutschland gespielt?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Hier verwenden Sie die Funktion **loc()**, um Zeilen mit einem bestimmten Wert in einer Spalte aus dem Datensatz auszuwählen; in diesem Fall mit dem Wert \"Deutschland\" in der Spalte \"land\". Dann filtern wir Duplikate heraus und bestimmen die Anzahl der Zeilen:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "280"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[\"name\"].loc[df[\"land\"] == 'Deutschland'].unique()) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Welcher deutsche Spieler hat bei den meisten Weltmeisterschaften gespielt?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sie wählen alle Zeilen mit dem Wert \"Deutschland\" in der Spalte \"land\" aus und zählen dann, wie oft jeder Name vorkommt. Mit **idxmax()** lassen Sie sich dann den Indexwert der Zeile angeben, in welcher der höchste Wert der Zählung vorkommt. Der Name aus dieser Zeile wird dann ausgegeben."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Lothar Matthäus'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"name\"].loc[df[\"land\"] == 'Deutschland'].value_counts().idxmax()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Wie alt war der älteste Spieler bei der letzten Weltmeisterschaft?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Eine Möglichkeit besteht darin, dem Datensatz eine neue Spalte hinzuzufügen, deren Wert aus der Differenz zwischen dem Veranstaltungsjahr und dem Geburtsjahr jeden Spielers besteht. Aus dieser Spalte können Sie nun den höchsten Wert auswählen, bie dem das Veranstaltungsjahr gleich 2018 ist:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['alter'] = df['wm_jahr'] - df['geburtsjahr']\n",
    "df[\"alter\"].loc[df[\"wm_jahr\"] == 2018].max() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Wer war bei jeder WM der jüngste Spieler?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Hier gruppieren Sie die Zeilen zuerst nach dem Veranstaltungsjahr und wählen dann für jede Gruppe die Person mit dem niedrigsten Alter aus."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>land</th>\n",
       "      <th>geburtsjahr</th>\n",
       "      <th>groesse</th>\n",
       "      <th>position</th>\n",
       "      <th>wm_jahr</th>\n",
       "      <th>alter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Jan De Bie</td>\n",
       "      <td>Belgien</td>\n",
       "      <td>1992.0</td>\n",
       "      <td>184 cm</td>\n",
       "      <td>Torwart</td>\n",
       "      <td>1930</td>\n",
       "      <td>-62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>383</th>\n",
       "      <td>Roberto Irañeta</td>\n",
       "      <td>Argentinien</td>\n",
       "      <td>1915.0</td>\n",
       "      <td>172 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1934</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>640</th>\n",
       "      <td>Walter Brom</td>\n",
       "      <td>Polen</td>\n",
       "      <td>1921.0</td>\n",
       "      <td>168 cm</td>\n",
       "      <td>Torwart</td>\n",
       "      <td>1938</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>942</th>\n",
       "      <td>Bengt Berndtsson</td>\n",
       "      <td>Schweden</td>\n",
       "      <td>1933.0</td>\n",
       "      <td>177 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1950</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1411</th>\n",
       "      <td>Mario Méndez</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>1938.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Abwehr</td>\n",
       "      <td>1954</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1782</th>\n",
       "      <td>Pelé</td>\n",
       "      <td>Brasilien</td>\n",
       "      <td>1940.0</td>\n",
       "      <td>176 cm</td>\n",
       "      <td>Mittelfeld</td>\n",
       "      <td>1958</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1910</th>\n",
       "      <td>Georgi Asparuchov</td>\n",
       "      <td>Bulgarien</td>\n",
       "      <td>1943.0</td>\n",
       "      <td>186 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1962</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2329</th>\n",
       "      <td>Edu</td>\n",
       "      <td>Brasilien</td>\n",
       "      <td>1949.0</td>\n",
       "      <td>170 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1966</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2791</th>\n",
       "      <td>Marco Antônio</td>\n",
       "      <td>Brasilien</td>\n",
       "      <td>1951.0</td>\n",
       "      <td>180 cm</td>\n",
       "      <td>Abwehr</td>\n",
       "      <td>1970</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066</th>\n",
       "      <td>Martin Hoffmann</td>\n",
       "      <td>DDR</td>\n",
       "      <td>1955.0</td>\n",
       "      <td>169 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1974</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3427</th>\n",
       "      <td>Andrzej Iwan</td>\n",
       "      <td>Polen</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>175 cm</td>\n",
       "      <td>Mittelfeld</td>\n",
       "      <td>1978</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4152</th>\n",
       "      <td>Norman Whiteside</td>\n",
       "      <td>Nordirland</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>183 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1982</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4242</th>\n",
       "      <td>Abdellah Bidar</td>\n",
       "      <td>Marokko</td>\n",
       "      <td>1967.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Abwehr</td>\n",
       "      <td>1986</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4766</th>\n",
       "      <td>Alen Bokšić</td>\n",
       "      <td>Jugoslawien</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>187 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1990</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5650</th>\n",
       "      <td>Ronaldo</td>\n",
       "      <td>Brasilien</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>183 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1994</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5979</th>\n",
       "      <td>Samuel Eto'o</td>\n",
       "      <td>Kamerun</td>\n",
       "      <td>1981.0</td>\n",
       "      <td>180 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>1998</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6968</th>\n",
       "      <td>Femi Opabunmi</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>1985.0</td>\n",
       "      <td>179 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>2002</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7899</th>\n",
       "      <td>Theo Walcott</td>\n",
       "      <td>England</td>\n",
       "      <td>1989.0</td>\n",
       "      <td>170 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>2006</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7936</th>\n",
       "      <td>Vincent Aboubakar</td>\n",
       "      <td>Kamerun</td>\n",
       "      <td>1992.0</td>\n",
       "      <td>184 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>2010</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9183</th>\n",
       "      <td>Fabrice Olinga</td>\n",
       "      <td>Kamerun</td>\n",
       "      <td>1996.0</td>\n",
       "      <td>176 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>2014</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9456</th>\n",
       "      <td>Daniel Arzani</td>\n",
       "      <td>Australien</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>171 cm</td>\n",
       "      <td>Stürmer</td>\n",
       "      <td>2018</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name         land  geburtsjahr groesse    position  \\\n",
       "55           Jan De Bie      Belgien       1992.0  184 cm     Torwart   \n",
       "383     Roberto Irañeta  Argentinien       1915.0  172 cm     Stürmer   \n",
       "640         Walter Brom        Polen       1921.0  168 cm     Torwart   \n",
       "942    Bengt Berndtsson     Schweden       1933.0  177 cm     Stürmer   \n",
       "1411       Mario Méndez      Uruguay       1938.0     NaN      Abwehr   \n",
       "1782               Pelé    Brasilien       1940.0  176 cm  Mittelfeld   \n",
       "1910  Georgi Asparuchov    Bulgarien       1943.0  186 cm     Stürmer   \n",
       "2329                Edu    Brasilien       1949.0  170 cm     Stürmer   \n",
       "2791      Marco Antônio    Brasilien       1951.0  180 cm      Abwehr   \n",
       "3066    Martin Hoffmann          DDR       1955.0  169 cm     Stürmer   \n",
       "3427       Andrzej Iwan        Polen       1959.0  175 cm  Mittelfeld   \n",
       "4152   Norman Whiteside   Nordirland       1965.0  183 cm     Stürmer   \n",
       "4242     Abdellah Bidar      Marokko       1967.0     NaN      Abwehr   \n",
       "4766        Alen Bokšić  Jugoslawien       1970.0  187 cm     Stürmer   \n",
       "5650            Ronaldo    Brasilien       1976.0  183 cm     Stürmer   \n",
       "5979       Samuel Eto'o      Kamerun       1981.0  180 cm     Stürmer   \n",
       "6968      Femi Opabunmi      Nigeria       1985.0  179 cm     Stürmer   \n",
       "7899       Theo Walcott      England       1989.0  170 cm     Stürmer   \n",
       "7936  Vincent Aboubakar      Kamerun       1992.0  184 cm     Stürmer   \n",
       "9183     Fabrice Olinga      Kamerun       1996.0  176 cm     Stürmer   \n",
       "9456      Daniel Arzani   Australien       1999.0  171 cm     Stürmer   \n",
       "\n",
       "      wm_jahr  alter  \n",
       "55       1930  -62.0  \n",
       "383      1934   19.0  \n",
       "640      1938   17.0  \n",
       "942      1950   17.0  \n",
       "1411     1954   16.0  \n",
       "1782     1958   18.0  \n",
       "1910     1962   19.0  \n",
       "2329     1966   17.0  \n",
       "2791     1970   19.0  \n",
       "3066     1974   19.0  \n",
       "3427     1978   19.0  \n",
       "4152     1982   17.0  \n",
       "4242     1986   19.0  \n",
       "4766     1990   20.0  \n",
       "5650     1994   18.0  \n",
       "5979     1998   17.0  \n",
       "6968     2002   17.0  \n",
       "7899     2006   17.0  \n",
       "7936     2010   18.0  \n",
       "9183     2014   18.0  \n",
       "9456     2018   19.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df.groupby(\"wm_jahr\")[\"alter\"].idxmin]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Was war bei der ersten Weltmeisterschaft die Durchschnittsgröße aller Spieler?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "In der Spalte \"groesse\" sorgt der Zusatz \" cm\" für Probleme, wenn Sie die Durchschnittsgröße ermitteln wollen. Deshalb müssen Sie ihn erst mit **str.replace()** entfernen und den Rest dann mit **to_numeric()** in eine Zahl umwandeln. Danach können Sie für alle Zeilen mit dem entsprechenden Veranstaltungsjahr die Durchschnittsgröße berechnen:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "173.06"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"groesse\"] = pd.to_numeric(df[\"groesse\"].str.replace(\" cm\" ,\"\"))\n",
    "\n",
    "df[\"groesse\"].loc[df[\"wm_jahr\"] == 1930].mean()\n",
    "\n",
    "round(df[\"groesse\"].loc[df[\"wm_jahr\"] == 1930].mean(), 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Sie können das auch für jedes Jahr tun:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "wm_jahr\n",
       "1930    173.064815\n",
       "1934    175.036145\n",
       "1938    174.903846\n",
       "1950    174.818182\n",
       "1954    174.712389\n",
       "1958    175.806950\n",
       "1962    175.901141\n",
       "1966    175.833333\n",
       "1970    176.560554\n",
       "1974    177.435986\n",
       "1978    178.509868\n",
       "1982    178.823266\n",
       "1986    179.002083\n",
       "1990    180.022403\n",
       "1994    180.250973\n",
       "1998    180.964489\n",
       "2002    181.146540\n",
       "2006    181.674830\n",
       "2010    181.894166\n",
       "2014    181.941655\n",
       "2018    182.489130\n",
       "Name: groesse, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"wm_jahr\")[\"groesse\"].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Was war die Durchschnittsgröße der Spieler auf jeder Position während jeder Weltmeisterschaft?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Hier können Sie auch wieder die Funktion **groupby()** verwenden, dieses Mal mit zwei Spalten (erst \"wm_jahr\", dann \"position\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "wm_jahr  position  \n",
       "1930     Abwehr        175.260870\n",
       "         Mittelfeld    172.518519\n",
       "         Stürmer       171.400000\n",
       "         Torwart       176.076923\n",
       "1934     Abwehr        174.500000\n",
       "         Mittelfeld    174.822222\n",
       "         Stürmer       174.092308\n",
       "         Torwart       180.812500\n",
       "1938     Abwehr        175.645161\n",
       "         Mittelfeld    174.658537\n",
       "         Stürmer       174.114286\n",
       "         Torwart       177.928571\n",
       "1950     Abwehr        176.107143\n",
       "         Mittelfeld    176.615385\n",
       "         Stürmer       172.193548\n",
       "         Torwart       178.857143\n",
       "1954     Abwehr        175.267857\n",
       "         Mittelfeld    174.510204\n",
       "         Stürmer       173.525773\n",
       "         Torwart       178.625000\n",
       "1958     Abwehr        176.509091\n",
       "         Mittelfeld    175.281690\n",
       "         Stürmer       174.572816\n",
       "         Torwart       180.000000\n",
       "1962     Abwehr        176.389610\n",
       "         Mittelfeld    175.833333\n",
       "         Stürmer       173.943182\n",
       "         Torwart       180.250000\n",
       "1966     Abwehr        176.641975\n",
       "         Mittelfeld    175.277108\n",
       "                          ...    \n",
       "1990     Stürmer       179.304762\n",
       "         Torwart       185.629032\n",
       "1994     Abwehr        180.765060\n",
       "         Mittelfeld    178.180233\n",
       "         Stürmer       179.594595\n",
       "         Torwart       185.538462\n",
       "1998     Abwehr        181.443439\n",
       "         Mittelfeld    178.577406\n",
       "         Stürmer       180.220000\n",
       "         Torwart       187.095745\n",
       "2002     Abwehr        182.209607\n",
       "         Mittelfeld    178.505976\n",
       "         Stürmer       180.378882\n",
       "         Torwart       186.802083\n",
       "2006     Abwehr        182.502165\n",
       "         Mittelfeld    178.797571\n",
       "         Stürmer       180.944099\n",
       "         Torwart       188.312500\n",
       "2010     Abwehr        182.723684\n",
       "         Mittelfeld    179.230159\n",
       "         Stürmer       181.403727\n",
       "         Torwart       187.739583\n",
       "2014     Abwehr        183.418502\n",
       "         Mittelfeld    178.736641\n",
       "         Stürmer       181.119205\n",
       "         Torwart       188.422680\n",
       "2018     Abwehr        183.285124\n",
       "         Mittelfeld    179.848101\n",
       "         Stürmer       181.316770\n",
       "         Torwart       188.968750\n",
       "Name: groesse, Length: 84, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([\"wm_jahr\", \"position\"])[\"groesse\"].mean() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Woher kamen die Daten: Screen Scraping\n",
    "\n",
    "[https://www.weltfussball.de/spielerliste/wm-2018-in-russland/nach-name/1/](https://www.weltfussball.de/spielerliste/wm-2018-in-russland/nach-name/1/)\n",
    "\n",
    "Die Daten können nicht direkt heruntergeladen werden, etwa als Excel-Datei. Stattdessen verwendet man hier eine Technik namens *screen scraping* (wörtlich: Bildschirmkratzen). Dabei ruft man eine oder mehrere Webseiten auf und extrahiert dann die benötigten Daten. Bevor man Code für einen Screenscraper schreibt oder eine entsprechende Anwendung ausführt, muss man die Struktur der Webseite analysieren, um zu sehen, innerhalb welcher HTML-Elemente sich die benötigten Informationen befinden."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Woher kamen die Daten: Screen Scraping\n",
    "\n",
    "*Screen scraping* ist oft eine Notlösung, wenn man nicht anders an die benötigten Daten herankommt. Es stellen sich folgende Probleme:\n",
    "\n",
    "- Legaler/ethischer Graubereich\n",
    "- Selbst kleine Änderungen in der HTML-Struktur führen zu Fehlern\n",
    "- Oft schwer, bestimmte Datenpunkte herauszufiltern.\n",
    " \n",
    "Wenn vorhanden, sollten Sie das API (*Application Programming Interface*, Programmierschnittstelle) einer Seite verwenden, um an Daten zu gelangen."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Exkurs: API\n",
    "\n",
    "Ein *API*, oder eine Programmierschnittstelle, ist ein vom Anbieter gestalteter Zugangspunkt zu einem oder mehreren Datensätzen, der von vielen Webseiten angeboten wird. Sie können über das API eine Anfrage an den Dienst stellen und bekommen dann einen formatierten Datensatz zurück. APIs sind normalerweise gut dokumentiert und verwenden ein oder mehrere standardisierte Datenformate. Manche APIs sind frei zugänglich, andere erfordern eine Anmeldung und/oder Bezahlung."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## API\n",
    "\n",
    "Beispielanfrage: https://swapi.co/api/people/?search=fett (Sie können diese Anfrage von Ihrem Programm schicken lassen oder auch in Ihren Browser öffnen)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    }
   },
   "source": [
    "## API\n",
    "\n",
    "Das Ergebis wird im **JSON**-Format (*Javascript Object Notation*) ausgegeben, einem bei APIs sehr häufigem Format, welches Python mit dem standardmäßig installiertem **json**-Paket auch gut verarbeiten kann:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "{\n",
    "    \"count\": 2, \n",
    "    \"next\": null, \n",
    "    \"previous\": null, \n",
    "    \"results\": [\n",
    "        {\n",
    "            \"name\": \"Boba Fett\", \n",
    "            \"height\": \"183\", \n",
    "            \"mass\": \"78.2\", \n",
    "            \"hair_color\": \"black\", \n",
    "            \"skin_color\": \"fair\", \n",
    "            \"eye_color\": \"brown\", \n",
    "            \"birth_year\": \"31.5BBY\", \n",
    "            \"gender\": \"male\", \n",
    "            \"homeworld\": \"https://swapi.co/api/planets/10/\", \n",
    "            \"films\": [\n",
    "                \"https://swapi.co/api/films/2/\", \n",
    "                \"https://swapi.co/api/films/5/\", \n",
    "                \"https://swapi.co/api/films/3/\"\n",
    "            ], \n",
    "            \"species\": [\n",
    "                \"https://swapi.co/api/species/1/\"\n",
    "            ], \n",
    "            \"vehicles\": [], \n",
    "            \"starships\": [\n",
    "                \"https://swapi.co/api/starships/21/\"\n",
    "            ], \n",
    "            \"created\": \"2014-12-15T12:49:32.457000Z\", \n",
    "            \"edited\": \"2014-12-20T21:17:50.349000Z\", \n",
    "            \"url\": \"https://swapi.co/api/people/22/\"\n",
    "        }, \n",
    "        {\n",
    "            \"name\": \"Jango Fett\", \n",
    "            \"height\": \"183\", \n",
    "            \"mass\": \"79\", \n",
    "            \"hair_color\": \"black\", \n",
    "            \"skin_color\": \"tan\", \n",
    "            \"eye_color\": \"brown\", \n",
    "            \"birth_year\": \"66BBY\", \n",
    "            \"gender\": \"male\", \n",
    "            \"homeworld\": \"https://swapi.co/api/planets/53/\", \n",
    "            \"films\": [\n",
    "                \"https://swapi.co/api/films/5/\"\n",
    "            ], \n",
    "            \"species\": [\n",
    "                \"https://swapi.co/api/species/1/\"\n",
    "            ], \n",
    "            \"vehicles\": [], \n",
    "            \"starships\": [], \n",
    "            \"created\": \"2014-12-20T16:54:41.620000Z\", \n",
    "            \"edited\": \"2014-12-20T21:17:50.465000Z\", \n",
    "            \"url\": \"https://swapi.co/api/people/69/\"\n",
    "        }\n",
    "    ]\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## API\n",
    "\n",
    "Vorteile von APIs:\n",
    "\n",
    "- Klare legale Situation\n",
    "- Einfacher zu verwenden\n",
    "- Zuverlässiger"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Zurück zum Screenscraping\n",
    "\n",
    "Wie wurden nun die Daten für die Übung gesammelt und bearbeitet?\n",
    "\n",
    "- Screenscraping mit Python\n",
    "- Nachbearbeitung mit OpenRefine: [https://openrefine.org/](https://openrefine.org/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fortsetzung folgt..."
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}