Grundlagen DataFrame Objekt - inkl. Lösungen.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Das DataFrame Objekt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_guests = pd.read_csv(\"https://raw.githubusercontent.com/fivethirtyeight/data/master/daily-show-guests/daily_show_guests.csv\", \n",
    "                                encoding=\"unicode_escape\", header=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>YEAR</th>\n",
       "      <th>GoogleKnowlege_Occupation</th>\n",
       "      <th>Show</th>\n",
       "      <th>Group</th>\n",
       "      <th>Raw_Guest_List</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/11/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Michael J. Fox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1999</td>\n",
       "      <td>Comedian</td>\n",
       "      <td>1/12/99</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>Sandra Bernhard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1999</td>\n",
       "      <td>television actress</td>\n",
       "      <td>1/13/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Tracey Ullman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1999</td>\n",
       "      <td>film actress</td>\n",
       "      <td>1/14/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Gillian Anderson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/18/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>David Alan Grier</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   YEAR GoogleKnowlege_Occupation     Show   Group    Raw_Guest_List\n",
       "0  1999                     actor  1/11/99  Acting    Michael J. Fox\n",
       "1  1999                  Comedian  1/12/99  Comedy   Sandra Bernhard\n",
       "2  1999        television actress  1/13/99  Acting     Tracey Ullman\n",
       "3  1999              film actress  1/14/99  Acting  Gillian Anderson\n",
       "4  1999                     actor  1/18/99  Acting  David Alan Grier"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#By default rename creates a new DataFrame, remember to use inplace=True if you do not want to create a new object\n",
    "ds_guests.rename(columns={'Raw_Guest_List':'Guest',\n",
    "                         'GoogleKnowlege_Occupation':'Occupation',\n",
    "                         'Show':'Date'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>YEAR</th>\n",
       "      <th>Occupation</th>\n",
       "      <th>Date</th>\n",
       "      <th>Group</th>\n",
       "      <th>Guest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/11/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Michael J. Fox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1999</td>\n",
       "      <td>Comedian</td>\n",
       "      <td>1/12/99</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>Sandra Bernhard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1999</td>\n",
       "      <td>television actress</td>\n",
       "      <td>1/13/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Tracey Ullman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1999</td>\n",
       "      <td>film actress</td>\n",
       "      <td>1/14/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Gillian Anderson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/18/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>David Alan Grier</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   YEAR          Occupation     Date   Group             Guest\n",
       "0  1999               actor  1/11/99  Acting    Michael J. Fox\n",
       "1  1999            Comedian  1/12/99  Comedy   Sandra Bernhard\n",
       "2  1999  television actress  1/13/99  Acting     Tracey Ullman\n",
       "3  1999        film actress  1/14/99  Acting  Gillian Anderson\n",
       "4  1999               actor  1/18/99  Acting  David Alan Grier"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['YEAR', 'Occupation', 'Date', 'Group', 'Guest'], dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=2693, step=1)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2693 entries, 0 to 2692\n",
      "Data columns (total 5 columns):\n",
      "YEAR          2693 non-null int64\n",
      "Occupation    2667 non-null object\n",
      "Date          2693 non-null object\n",
      "Group         2662 non-null object\n",
      "Guest         2693 non-null object\n",
      "dtypes: int64(1), object(4)\n",
      "memory usage: 105.3+ KB\n"
     ]
    }
   ],
   "source": [
    "ds_guests.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selektion (Teil 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Acting\n",
       "1    Comedy\n",
       "2    Acting\n",
       "3    Acting\n",
       "4    Acting\n",
       "Name: Group, dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Attribute access (special consideration for naming e.g.: min, index, and whitespaces)\n",
    "ds_guests.Group.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Acting\n",
       "1    Comedy\n",
       "2    Acting\n",
       "3    Acting\n",
       "4    Acting\n",
       "Name: Group, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Standard Access\n",
    "ds_guests[\"Group\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Group</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Acting</td>\n",
       "      <td>1/11/99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Comedy</td>\n",
       "      <td>1/12/99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Acting</td>\n",
       "      <td>1/13/99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Acting</td>\n",
       "      <td>1/14/99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Acting</td>\n",
       "      <td>1/18/99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Group     Date\n",
       "0  Acting  1/11/99\n",
       "1  Comedy  1/12/99\n",
       "2  Acting  1/13/99\n",
       "3  Acting  1/14/99\n",
       "4  Acting  1/18/99"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Mehrere Spalten\n",
    "ds_guests[[\"Group\", \"Date\"]].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    Comedy\n",
       "2    Acting\n",
       "3    Acting\n",
       "4    Acting\n",
       "Name: Group, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Integer-location index\n",
    "ds_guests[\"Group\"].iloc[1:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>YEAR</th>\n",
       "      <th>Occupation</th>\n",
       "      <th>Date</th>\n",
       "      <th>Group</th>\n",
       "      <th>Guest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/11/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Michael J. Fox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1999</td>\n",
       "      <td>television actress</td>\n",
       "      <td>1/13/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Tracey Ullman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1999</td>\n",
       "      <td>film actress</td>\n",
       "      <td>1/14/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Gillian Anderson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/18/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>David Alan Grier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/19/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>William Baldwin</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   YEAR          Occupation     Date   Group             Guest\n",
       "0  1999               actor  1/11/99  Acting    Michael J. Fox\n",
       "2  1999  television actress  1/13/99  Acting     Tracey Ullman\n",
       "3  1999        film actress  1/14/99  Acting  Gillian Anderson\n",
       "4  1999               actor  1/18/99  Acting  David Alan Grier\n",
       "5  1999               actor  1/19/99  Acting   William Baldwin"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Wie auch bei Series Objekten kann eine Selektion ebenfalls über eine True/False Serie erfolgen  \n",
    "ds_guests[ds_guests[\"Group\"] == \"Acting\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>YEAR</th>\n",
       "      <th>Occupation</th>\n",
       "      <th>Date</th>\n",
       "      <th>Group</th>\n",
       "      <th>Guest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/11/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Michael J. Fox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/18/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>David Alan Grier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/19/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>William Baldwin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/25/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Matthew Lillard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1999</td>\n",
       "      <td>actor</td>\n",
       "      <td>1/28/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>D. L. Hughley</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    YEAR Occupation     Date   Group             Guest\n",
       "0   1999      actor  1/11/99  Acting    Michael J. Fox\n",
       "4   1999      actor  1/18/99  Acting  David Alan Grier\n",
       "5   1999      actor  1/19/99  Acting   William Baldwin\n",
       "8   1999      actor  1/25/99  Acting   Matthew Lillard\n",
       "11  1999      actor  1/28/99  Acting     D. L. Hughley"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Über die True/False Selektion können auch Konditionen abgebildet werden  \n",
    "ds_guests[(ds_guests[\"Group\"] == \"Acting\") & (ds_guests[\"Occupation\"] == \"actor\")].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "399"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Über die Kombination unique und (Array-) Length lassen sich alle disjunkten Elemente zählen \n",
    "len(ds_guests.Occupation.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(ds_guests.Group.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame Modifikationen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Same applies to drop\n",
    "ds_guests.drop(columns=['YEAR'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a new column which uses the number of whitespaces to estimate the number of forenames \n",
    "ds_guests[\"Forename Number\"] = ds_guests.Guest.apply(lambda guest: (len(guest.split(\" \")) - 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2693.000000\n",
       "mean        1.242481\n",
       "std         0.893401\n",
       "min         0.000000\n",
       "25%         1.000000\n",
       "50%         1.000000\n",
       "75%         1.000000\n",
       "max        15.000000\n",
       "Name: Forename Number, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get descriptive statistics for the number of forenames\n",
    "ds_guests[\"Forename Number\"].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What guest name has 15 forenames?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Iraq - A Look Baq (or how we learned to stop reporting and love the war)',\n",
       "       'Iraq - A Look Baq (or how we learned to stop reporting and love the war)'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests[ds_guests[\"Forename Number\"] == 15].Guest.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Filter out the rows with the the identified 16 word string \n",
    "ds_guests = ds_guests[ds_guests.Guest != 'Iraq - A Look Baq (or how we learned to stop reporting and love the war)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2691.000000\n",
       "mean        1.232256\n",
       "std         0.811131\n",
       "min         0.000000\n",
       "25%         1.000000\n",
       "50%         1.000000\n",
       "75%         1.000000\n",
       "max        12.000000\n",
       "Name: Forename Number, dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests[\"Forename Number\"].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aufgaben:\n",
    "    - Welcher Gast hat 12 Vornamen?\n",
    "    - Identifiziere anhand der Vornamenanzahl alle Gast Namen, die keine natürlichen Personen darstellen (setze 5 als obere Grenze für die Vornahmenanzahl) ?\n",
    "    - Gibt es Duplikate in unserem Datensatz? Nutze hierzu die Funktion \"duplicated\" und oder \"drop_duplicates\".\n",
    "    - Welcher Gast gehört keiner Gruppe an? Welcher Gast gehört keiner Occupation an? Nutze hierzu die Funktion pd.isnull und oder pd.notnull\n",
    "    - Welche Spalte bietet sich als Index an?\n",
    "    - Wieviele unterschiedliche Gäste gab es in der Gruppe (\"Group\") Politician ?\n",
    "    - Wann war Vin Diesel zu Gast?\n",
    "    - Wann und wie oft war Will Ferrell zu Gast? Tipp: Er war öfter als 10 mal da.\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Welcher Gast hat 12 Vornamen?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Campaign Trail to the Road to the White House Mo Rocca, Vance DeGeneres'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests[ds_guests[\"Forename Number\"] == 12].Guest.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identifiziere anhand der Vornamenanzahl alle Gast Namen, die keine natürlichen Personen darstellen (setze 5 als obere Grenze für die Vornahmenanzahl) ?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Pamela Anderson, Natalie Raitano, Molly Culver',\n",
       "       'Pamela Anderson, Natalie Raitano, Molly Culver',\n",
       "       'Pamela Anderson, Natalie Raitano, Molly Culver',\n",
       "       'Hootie & the Blowfish, Billy Crystal',\n",
       "       'Hootie & the Blowfish, Billy Crystal',\n",
       "       \"Frank DeCaro's Oscar Special, John Larroquette\",\n",
       "       \"Frank DeCaro's Oscar Special, John Larroquette\",\n",
       "       'Tales of Survival with Vance DeGeneres',\n",
       "       'Focus on New Hampshire Special, special guest Bob Dole',\n",
       "       'Campaign Trail to the Road to the White House Mo Rocca, Vance DeGeneres',\n",
       "       'Stephen Colbert as Al Sharpton (Sharpton had been the scheduled guest)',\n",
       "       'Ronny Cox Marc Singer Robert Amstler Arianna Huffington',\n",
       "       'Who are the Daily Show? Special',\n",
       "       'David Cross (show hosted by Stephen Colbert)',\n",
       "       'Paul Teutul Sr. Paul Teutul Jr.',\n",
       "       'Paul Teutul Sr. Paul Teutul Jr.',\n",
       "       \"I'm a Correspondent, Please Don't Fire Me!\",\n",
       "       'Ralph Nader (show hosted by Stephen Colbert)',\n",
       "       'Indecision 2008 Live Election Night Special',\n",
       "       'Will Ferrell and John C. Reilly',\n",
       "       'Will Ferrell and John C. Reilly',\n",
       "       'Dr. Mustafa Barghouti and Anna Baltzer',\n",
       "       'Dr. Mustafa Barghouti and Anna Baltzer',\n",
       "       'Larry Lawton (skit)[4] and Jeff Sharlet (interview)',\n",
       "       'Larry Lawton (skit)[4] and Jeff Sharlet (interview)',\n",
       "       'Ringo Starr and The Ben Harper Band',\n",
       "       'Ringo Starr and The Ben Harper Band',\n",
       "       'Will Ferrell (The interview also featured Rob Riggle',\n",
       "       'Zach the Erect\" Galifianakis & Will Ferrell\"',\n",
       "       'Zach the Erect\" Galifianakis & Will Ferrell\"',\n",
       "       'Steve Carell, Will Ferrell, David Koechner & Paul Rudd',\n",
       "       'Steve Carell, Will Ferrell, David Koechner & Paul Rudd',\n",
       "       'Steve Carell, Will Ferrell, David Koechner & Paul Rudd',\n",
       "       'Maziar Bahari & Gael GarcÃ\\xad_a Bernal',\n",
       "       'Maziar Bahari & Gael GarcÃ\\xad_a Bernal',\n",
       "       'Amy Yates Wuelfing & Gibby Haynes',\n",
       "       'Amy Yates Wuelfing & Gibby Haynes'], dtype=object)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests[ds_guests[\"Forename Number\"] >= 5].Guest.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Gibt es Duplikate in unserem Datensatz? Nutze hierzu die Funktion \"duplicated\" und oder \"drop_duplicates\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Occupation</th>\n",
       "      <th>Date</th>\n",
       "      <th>Group</th>\n",
       "      <th>Guest</th>\n",
       "      <th>Forename Number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>actress</td>\n",
       "      <td>11/17/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Yasmine Bleeth</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>comedian</td>\n",
       "      <td>2/1/99</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>George Carlin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>actress</td>\n",
       "      <td>2/10/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Pamela Anderson, Natalie Raitano, Molly Culver</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>actress</td>\n",
       "      <td>2/10/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Pamela Anderson, Natalie Raitano, Molly Culver</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>television actress</td>\n",
       "      <td>2/3/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Kellie Martin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>actress</td>\n",
       "      <td>2/8/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Melissa Gilbert</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>actor</td>\n",
       "      <td>3/11/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Hootie &amp; the Blowfish, Billy Crystal</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>actor</td>\n",
       "      <td>3/17/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Frank DeCaro's Oscar Special, John Larroquette</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>actor</td>\n",
       "      <td>6/16/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Mike Myers</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>actor</td>\n",
       "      <td>7/1/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Rob Schneider</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>film director</td>\n",
       "      <td>8/12/99</td>\n",
       "      <td>Media</td>\n",
       "      <td>Eduardo Sanchez and Daniel Myrick</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>actor</td>\n",
       "      <td>8/9/99</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Dave Foley</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>stand-up comedian</td>\n",
       "      <td>9/16/99</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>David Cross</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>Stand-up comedian</td>\n",
       "      <td>9/27/99</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>Norm Macdonald</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>comedian</td>\n",
       "      <td>9/30/99</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>Bruce McCulloch and Mark McKinney</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>Singer-lyricist</td>\n",
       "      <td>1/13/00</td>\n",
       "      <td>Musician</td>\n",
       "      <td>Michael Stipe</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>Film actor</td>\n",
       "      <td>1/25/00</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Peter Krause</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>Stand-up comedian</td>\n",
       "      <td>1/6/00</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>Richard Belzer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>actor</td>\n",
       "      <td>10/19/00</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Brendan Fraser</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>singer</td>\n",
       "      <td>10/25/00</td>\n",
       "      <td>Musician</td>\n",
       "      <td>Posh Spice &amp; Baby Spice</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>singer-songwriter</td>\n",
       "      <td>10/4/00</td>\n",
       "      <td>Musician</td>\n",
       "      <td>Jewel</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>actor</td>\n",
       "      <td>11/13/00</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Adam Sandler</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>actor</td>\n",
       "      <td>11/15/00</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Adam Sandler</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>actress</td>\n",
       "      <td>11/28/00</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Laura San Giacomo</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>Stand-up comedian</td>\n",
       "      <td>11/8/00</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>Richard Belzer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>actress</td>\n",
       "      <td>12/20/00</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Jeri Ryan</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>film actress</td>\n",
       "      <td>12/21/00</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Gillian Anderson</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>actor</td>\n",
       "      <td>12/4/00</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Thomas Gibson</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>actor</td>\n",
       "      <td>12/6/00</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Chris O'Donnell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>comedian</td>\n",
       "      <td>2/17/00</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>Ed McMahon</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2652</th>\n",
       "      <td>Journalist</td>\n",
       "      <td>5/12/15</td>\n",
       "      <td>Media</td>\n",
       "      <td>Tom Brokaw</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2653</th>\n",
       "      <td>Professor</td>\n",
       "      <td>5/13/15</td>\n",
       "      <td>Academic</td>\n",
       "      <td>Reza Aslan</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2655</th>\n",
       "      <td>United States Senate member</td>\n",
       "      <td>5/26/15</td>\n",
       "      <td>Politician</td>\n",
       "      <td>Rand Paul</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2659</th>\n",
       "      <td>Singer-songwriter</td>\n",
       "      <td>5/5/15</td>\n",
       "      <td>Musician</td>\n",
       "      <td>Willie Nelson</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2662</th>\n",
       "      <td>armed forces officer</td>\n",
       "      <td>6/1/15</td>\n",
       "      <td>Military</td>\n",
       "      <td>Stanley McChrystal</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2663</th>\n",
       "      <td>stand-up comedian</td>\n",
       "      <td>6/10/15</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>Colin Quinn</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2664</th>\n",
       "      <td>actor</td>\n",
       "      <td>6/11/15</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Mark Ruffalo</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2665</th>\n",
       "      <td>screenwriter</td>\n",
       "      <td>6/15/15</td>\n",
       "      <td>Media</td>\n",
       "      <td>Judd Apatow</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2667</th>\n",
       "      <td>us president</td>\n",
       "      <td>6/17/15</td>\n",
       "      <td>Politician</td>\n",
       "      <td>Bill Clinton</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2668</th>\n",
       "      <td>activist</td>\n",
       "      <td>6/18/15</td>\n",
       "      <td>Advocacy</td>\n",
       "      <td>Malala Yousafzai</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2669</th>\n",
       "      <td>nyc mayor</td>\n",
       "      <td>6/2/15</td>\n",
       "      <td>Politician</td>\n",
       "      <td>Bill de Blasio</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2670</th>\n",
       "      <td>united states senator</td>\n",
       "      <td>6/22/15</td>\n",
       "      <td>Politician</td>\n",
       "      <td>Al Franken</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2671</th>\n",
       "      <td>television Series Creator</td>\n",
       "      <td>6/23/15</td>\n",
       "      <td>Media</td>\n",
       "      <td>Seth MacFarlane</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2672</th>\n",
       "      <td>legal analyst</td>\n",
       "      <td>6/24/15</td>\n",
       "      <td>Misc</td>\n",
       "      <td>Andrew Napolitano</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2673</th>\n",
       "      <td>Comedian</td>\n",
       "      <td>6/25/15</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>Richard Lewis</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2675</th>\n",
       "      <td>actress</td>\n",
       "      <td>6/3/15</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Melissa McCarthy</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2676</th>\n",
       "      <td>actor</td>\n",
       "      <td>6/30/15</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Jon Hamm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2677</th>\n",
       "      <td>actor</td>\n",
       "      <td>6/4/15</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Steve Buscemi</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2679</th>\n",
       "      <td>actor</td>\n",
       "      <td>6/9/15</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Nick Offerman</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2680</th>\n",
       "      <td>United States Senator</td>\n",
       "      <td>7/1/15</td>\n",
       "      <td>Politician</td>\n",
       "      <td>Kirsten Gillibrand</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2681</th>\n",
       "      <td>Author</td>\n",
       "      <td>7/2/15</td>\n",
       "      <td>Media</td>\n",
       "      <td>Sarah Vowell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2682</th>\n",
       "      <td>actor</td>\n",
       "      <td>7/20/15</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Paul Rudd</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2683</th>\n",
       "      <td>us president</td>\n",
       "      <td>7/21/15</td>\n",
       "      <td>Politician</td>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2684</th>\n",
       "      <td>actor</td>\n",
       "      <td>7/22/15</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Jake Gyllenhaal</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2686</th>\n",
       "      <td>author</td>\n",
       "      <td>7/27/15</td>\n",
       "      <td>Media</td>\n",
       "      <td>David McCullough</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2687</th>\n",
       "      <td>actor</td>\n",
       "      <td>7/28/15</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Tom Cruise</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2688</th>\n",
       "      <td>biographer</td>\n",
       "      <td>7/29/15</td>\n",
       "      <td>Media</td>\n",
       "      <td>Doris Kearns Goodwin</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2689</th>\n",
       "      <td>director</td>\n",
       "      <td>7/30/15</td>\n",
       "      <td>Media</td>\n",
       "      <td>J. J. Abrams</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2691</th>\n",
       "      <td>actor</td>\n",
       "      <td>8/4/15</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Denis Leary</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2692</th>\n",
       "      <td>comedian</td>\n",
       "      <td>8/5/15</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>Louis C.K.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1023 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Occupation      Date       Group  \\\n",
       "27                        actress  11/17/99      Acting   \n",
       "48                       comedian    2/1/99      Comedy   \n",
       "50                        actress   2/10/99      Acting   \n",
       "51                        actress   2/10/99      Acting   \n",
       "57             television actress    2/3/99      Acting   \n",
       "59                        actress    2/8/99      Acting   \n",
       "64                          actor   3/11/99      Acting   \n",
       "68                          actor   3/17/99      Acting   \n",
       "110                         actor   6/16/99      Acting   \n",
       "122                         actor    7/1/99      Acting   \n",
       "138                 film director   8/12/99       Media   \n",
       "152                         actor    8/9/99      Acting   \n",
       "156             stand-up comedian   9/16/99      Comedy   \n",
       "161             Stand-up comedian   9/27/99      Comedy   \n",
       "165                      comedian   9/30/99      Comedy   \n",
       "169               Singer-lyricist   1/13/00    Musician   \n",
       "175                    Film actor   1/25/00      Acting   \n",
       "180             Stand-up comedian    1/6/00      Comedy   \n",
       "184                         actor  10/19/00      Acting   \n",
       "189                        singer  10/25/00    Musician   \n",
       "194             singer-songwriter   10/4/00    Musician   \n",
       "197                         actor  11/13/00      Acting   \n",
       "199                         actor  11/15/00      Acting   \n",
       "204                       actress  11/28/00      Acting   \n",
       "209             Stand-up comedian   11/8/00      Comedy   \n",
       "217                       actress  12/20/00      Acting   \n",
       "218                  film actress  12/21/00      Acting   \n",
       "219                         actor   12/4/00      Acting   \n",
       "221                         actor   12/6/00      Acting   \n",
       "228                      comedian   2/17/00      Comedy   \n",
       "...                           ...       ...         ...   \n",
       "2652                   Journalist   5/12/15       Media   \n",
       "2653                    Professor   5/13/15    Academic   \n",
       "2655  United States Senate member   5/26/15  Politician   \n",
       "2659            Singer-songwriter    5/5/15    Musician   \n",
       "2662         armed forces officer    6/1/15    Military   \n",
       "2663            stand-up comedian   6/10/15      Comedy   \n",
       "2664                        actor   6/11/15      Acting   \n",
       "2665                 screenwriter   6/15/15       Media   \n",
       "2667                 us president   6/17/15  Politician   \n",
       "2668                     activist   6/18/15    Advocacy   \n",
       "2669                    nyc mayor    6/2/15  Politician   \n",
       "2670        united states senator   6/22/15  Politician   \n",
       "2671    television Series Creator   6/23/15       Media   \n",
       "2672                legal analyst   6/24/15        Misc   \n",
       "2673                     Comedian   6/25/15      Comedy   \n",
       "2675                      actress    6/3/15      Acting   \n",
       "2676                        actor   6/30/15      Acting   \n",
       "2677                        actor    6/4/15      Acting   \n",
       "2679                        actor    6/9/15      Acting   \n",
       "2680        United States Senator    7/1/15  Politician   \n",
       "2681                       Author    7/2/15       Media   \n",
       "2682                        actor   7/20/15      Acting   \n",
       "2683                 us president   7/21/15  Politician   \n",
       "2684                        actor   7/22/15      Acting   \n",
       "2686                       author   7/27/15       Media   \n",
       "2687                        actor   7/28/15      Acting   \n",
       "2688                   biographer   7/29/15       Media   \n",
       "2689                     director   7/30/15       Media   \n",
       "2691                        actor    8/4/15      Acting   \n",
       "2692                     comedian    8/5/15      Comedy   \n",
       "\n",
       "                                               Guest  Forename Number  \n",
       "27                                    Yasmine Bleeth                1  \n",
       "48                                     George Carlin                1  \n",
       "50    Pamela Anderson, Natalie Raitano, Molly Culver                5  \n",
       "51    Pamela Anderson, Natalie Raitano, Molly Culver                5  \n",
       "57                                     Kellie Martin                1  \n",
       "59                                   Melissa Gilbert                1  \n",
       "64              Hootie & the Blowfish, Billy Crystal                5  \n",
       "68    Frank DeCaro's Oscar Special, John Larroquette                5  \n",
       "110                                       Mike Myers                1  \n",
       "122                                    Rob Schneider                1  \n",
       "138                Eduardo Sanchez and Daniel Myrick                4  \n",
       "152                                       Dave Foley                1  \n",
       "156                                      David Cross                1  \n",
       "161                                   Norm Macdonald                1  \n",
       "165                Bruce McCulloch and Mark McKinney                4  \n",
       "169                                    Michael Stipe                1  \n",
       "175                                     Peter Krause                1  \n",
       "180                                   Richard Belzer                1  \n",
       "184                                   Brendan Fraser                1  \n",
       "189                          Posh Spice & Baby Spice                4  \n",
       "194                                            Jewel                0  \n",
       "197                                     Adam Sandler                1  \n",
       "199                                     Adam Sandler                1  \n",
       "204                                Laura San Giacomo                2  \n",
       "209                                   Richard Belzer                1  \n",
       "217                                        Jeri Ryan                1  \n",
       "218                                 Gillian Anderson                1  \n",
       "219                                    Thomas Gibson                1  \n",
       "221                                  Chris O'Donnell                1  \n",
       "228                                       Ed McMahon                1  \n",
       "...                                              ...              ...  \n",
       "2652                                      Tom Brokaw                1  \n",
       "2653                                      Reza Aslan                1  \n",
       "2655                                       Rand Paul                1  \n",
       "2659                                   Willie Nelson                1  \n",
       "2662                              Stanley McChrystal                1  \n",
       "2663                                     Colin Quinn                1  \n",
       "2664                                    Mark Ruffalo                1  \n",
       "2665                                     Judd Apatow                1  \n",
       "2667                                    Bill Clinton                1  \n",
       "2668                                Malala Yousafzai                1  \n",
       "2669                                  Bill de Blasio                2  \n",
       "2670                                      Al Franken                1  \n",
       "2671                                 Seth MacFarlane                1  \n",
       "2672                               Andrew Napolitano                1  \n",
       "2673                                   Richard Lewis                1  \n",
       "2675                                Melissa McCarthy                1  \n",
       "2676                                        Jon Hamm                1  \n",
       "2677                                   Steve Buscemi                1  \n",
       "2679                                   Nick Offerman                1  \n",
       "2680                              Kirsten Gillibrand                1  \n",
       "2681                                    Sarah Vowell                1  \n",
       "2682                                       Paul Rudd                1  \n",
       "2683                                    Barack Obama                1  \n",
       "2684                                 Jake Gyllenhaal                1  \n",
       "2686                                David McCullough                1  \n",
       "2687                                      Tom Cruise                1  \n",
       "2688                            Doris Kearns Goodwin                2  \n",
       "2689                                    J. J. Abrams                2  \n",
       "2691                                     Denis Leary                1  \n",
       "2692                                      Louis C.K.                1  \n",
       "\n",
       "[1023 rows x 5 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests[ds_guests.Guest.duplicated()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Welcher Gast gehört keiner Gruppe an? Welcher Gast gehört keiner Occupation an? Nutze hierzu die Funktion pd.isnull und oder pd.notnull\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "39                            Greatest Millennium Special\n",
       "129                             Third Anniversary Special\n",
       "149                     The Daily Show Summer Spectacular\n",
       "202                Tales of Survival with Vance DeGeneres\n",
       "213                                              no guest\n",
       "303                            Fourth Anniversary Special\n",
       "308     Campaign Trail to the Road to the White House ...\n",
       "434                                              No guest\n",
       "509                            Road to Washington Special\n",
       "592                             Matt Walsh Goes To Hawaii\n",
       "688                       Who are the Daily Show? Special\n",
       "773                               Looking Beyond The Show\n",
       "794                                    Again, A Look Back\n",
       "803            I'm a Correspondent, Please Don't Fire Me!\n",
       "805                                              No Guest\n",
       "945                                                  None\n",
       "946                                                  None\n",
       "961                                                (None)\n",
       "1173                                                 None\n",
       "1365                                        John Bambenek\n",
       "1490          Indecision 2008 Live Election Night Special\n",
       "1591                                           (no guest)\n",
       "1608                                           (no guest)\n",
       "1796                                                 none\n",
       "2145                        Election Night: This Ends Now\n",
       "2175                                                 None\n",
       "2248                                                 none\n",
       "2263                                                 none\n",
       "2393                                             No Guest\n",
       "Name: Guest, dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests[ds_guests.Group.isnull()].Guest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Welche Spalte bietet sich als Index an?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Wieviele unterschiedliche Gäste gab es in der Gruppe (\"Group\") Politician ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "180"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp = ds_guests.drop_duplicates(subset=\"Guest\",keep=\"first\")\n",
    "len(tmp[tmp[\"Group\"] == \"Politician\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Wann war Vin Diesel zu Gast?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Occupation</th>\n",
       "      <th>Date</th>\n",
       "      <th>Group</th>\n",
       "      <th>Guest</th>\n",
       "      <th>Forename Number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>actor</td>\n",
       "      <td>2/1/00</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Vin Diesel</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>actor</td>\n",
       "      <td>8/7/02</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Vin Diesel</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1213</th>\n",
       "      <td>actor</td>\n",
       "      <td>3/16/06</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Vin Diesel</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Occupation     Date   Group       Guest  Forename Number\n",
       "224       actor   2/1/00  Acting  Vin Diesel                1\n",
       "638       actor   8/7/02  Acting  Vin Diesel                1\n",
       "1213      actor  3/16/06  Acting  Vin Diesel                1"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests[ds_guests.Guest == \"Vin Diesel\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Wann und wie oft war Will Ferrell zu Gast?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>Occupation</th>\n",
       "      <th>Date</th>\n",
       "      <th>Group</th>\n",
       "      <th>Guest</th>\n",
       "      <th>Forename Number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>actor</td>\n",
       "      <td>1/11/00</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>actor</td>\n",
       "      <td>8/22/01</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680</th>\n",
       "      <td>actor</td>\n",
       "      <td>11/10/03</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>948</th>\n",
       "      <td>actor</td>\n",
       "      <td>7/7/04</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1085</th>\n",
       "      <td>actor</td>\n",
       "      <td>6/14/05</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1277</th>\n",
       "      <td>actor</td>\n",
       "      <td>7/31/06</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1576</th>\n",
       "      <td>actor</td>\n",
       "      <td>7/22/08</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell and John C. Reilly</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1577</th>\n",
       "      <td>actor</td>\n",
       "      <td>7/22/08</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell and John C. Reilly</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1732</th>\n",
       "      <td>actor</td>\n",
       "      <td>6/4/09</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1919</th>\n",
       "      <td>actor</td>\n",
       "      <td>8/3/10</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell (The interview also featured Rob ...</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2031</th>\n",
       "      <td>actor</td>\n",
       "      <td>5/10/11</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2173</th>\n",
       "      <td>actor</td>\n",
       "      <td>3/13/12</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2235</th>\n",
       "      <td>Stand-up comedian</td>\n",
       "      <td>7/26/12</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>Zach the Erect\" Galifianakis &amp; Will Ferrell\"</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2236</th>\n",
       "      <td>actor</td>\n",
       "      <td>7/26/12</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Zach the Erect\" Galifianakis &amp; Will Ferrell\"</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2313</th>\n",
       "      <td>actor</td>\n",
       "      <td>12/18/13</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Steve Carell, Will Ferrell, David Koechner &amp; P...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2314</th>\n",
       "      <td>actor</td>\n",
       "      <td>12/18/13</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Steve Carell, Will Ferrell, David Koechner &amp; P...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2315</th>\n",
       "      <td>actor</td>\n",
       "      <td>12/18/13</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Steve Carell, Will Ferrell, David Koechner &amp; P...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2625</th>\n",
       "      <td>actor</td>\n",
       "      <td>3/19/15</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Will Ferrell</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Occupation      Date   Group  \\\n",
       "167               actor   1/11/00  Acting   \n",
       "480               actor   8/22/01  Acting   \n",
       "680               actor  11/10/03  Acting   \n",
       "948               actor    7/7/04  Acting   \n",
       "1085              actor   6/14/05  Acting   \n",
       "1277              actor   7/31/06  Acting   \n",
       "1576              actor   7/22/08  Acting   \n",
       "1577              actor   7/22/08  Acting   \n",
       "1732              actor    6/4/09  Acting   \n",
       "1919              actor    8/3/10  Acting   \n",
       "2031              actor   5/10/11  Acting   \n",
       "2173              actor   3/13/12  Acting   \n",
       "2235  Stand-up comedian   7/26/12  Comedy   \n",
       "2236              actor   7/26/12  Acting   \n",
       "2313              actor  12/18/13  Acting   \n",
       "2314              actor  12/18/13  Acting   \n",
       "2315              actor  12/18/13  Acting   \n",
       "2625              actor   3/19/15  Acting   \n",
       "\n",
       "                                                  Guest  Forename Number  \n",
       "167                                        Will Ferrell                1  \n",
       "480                                        Will Ferrell                1  \n",
       "680                                        Will Ferrell                1  \n",
       "948                                        Will Ferrell                1  \n",
       "1085                                       Will Ferrell                1  \n",
       "1277                                       Will Ferrell                1  \n",
       "1576                    Will Ferrell and John C. Reilly                5  \n",
       "1577                    Will Ferrell and John C. Reilly                5  \n",
       "1732                                       Will Ferrell                1  \n",
       "1919  Will Ferrell (The interview also featured Rob ...                7  \n",
       "2031                                       Will Ferrell                1  \n",
       "2173                                       Will Ferrell                1  \n",
       "2235       Zach the Erect\" Galifianakis & Will Ferrell\"                6  \n",
       "2236       Zach the Erect\" Galifianakis & Will Ferrell\"                6  \n",
       "2313  Steve Carell, Will Ferrell, David Koechner & P...                8  \n",
       "2314  Steve Carell, Will Ferrell, David Koechner & P...                8  \n",
       "2315  Steve Carell, Will Ferrell, David Koechner & P...                8  \n",
       "2625                                       Will Ferrell                1  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests[ds_guests.Guest.str.contains(\"Will Ferrell\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selektion (Teil 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_guests[\"Date\"] = pd.to_datetime(ds_guests.Date)\n",
    "ds_guests.set_index(\"Date\", inplace=True)"
   ]
  },
  {
   "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>Occupation</th>\n",
       "      <th>Group</th>\n",
       "      <th>Guest</th>\n",
       "      <th>Forename Number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2011-08-01</th>\n",
       "      <td>actress</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Freida Pinto</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-08-10</th>\n",
       "      <td>lawyer</td>\n",
       "      <td>Misc</td>\n",
       "      <td>John Coffee</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-08-11</th>\n",
       "      <td>Journalist</td>\n",
       "      <td>Media</td>\n",
       "      <td>Michael Wallis</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-08-15</th>\n",
       "      <td>television host</td>\n",
       "      <td>Media</td>\n",
       "      <td>Ali Velshi</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-08-16</th>\n",
       "      <td>actor</td>\n",
       "      <td>Acting</td>\n",
       "      <td>Matt Long</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Occupation   Group           Guest  Forename Number\n",
       "Date                                                                \n",
       "2011-08-01          actress  Acting    Freida Pinto                1\n",
       "2011-08-10           lawyer    Misc     John Coffee                1\n",
       "2011-08-11       Journalist   Media  Michael Wallis                1\n",
       "2011-08-15  television host   Media      Ali Velshi                1\n",
       "2011-08-16            actor  Acting       Matt Long                1"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Index basierter Zugriff via loc\n",
    "ds_guests.loc['08-2011'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>Occupation</th>\n",
       "      <th>Group</th>\n",
       "      <th>Guest</th>\n",
       "      <th>Forename Number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2011-08-10</th>\n",
       "      <td>lawyer</td>\n",
       "      <td>Misc</td>\n",
       "      <td>John Coffee</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Occupation Group        Guest  Forename Number\n",
       "Date                                                     \n",
       "2011-08-10     lawyer  Misc  John Coffee                1"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Datum kann auch via Default Notation verwendet werden\n",
    "ds_guests['2011-08-10']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>Occupation</th>\n",
       "      <th>Group</th>\n",
       "      <th>Guest</th>\n",
       "      <th>Forename Number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-01-11</th>\n",
       "      <td>lawyer</td>\n",
       "      <td>Misc</td>\n",
       "      <td>John Yoo</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-12</th>\n",
       "      <td>Editor</td>\n",
       "      <td>Media</td>\n",
       "      <td>Paul Ingrassia</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-13</th>\n",
       "      <td>musician</td>\n",
       "      <td>Musician</td>\n",
       "      <td>Ringo Starr and The Ben Harper Band</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-13</th>\n",
       "      <td>rock band</td>\n",
       "      <td>Musician</td>\n",
       "      <td>Ringo Starr and The Ben Harper Band</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-14</th>\n",
       "      <td>Journalist</td>\n",
       "      <td>Media</td>\n",
       "      <td>Tom Brokaw</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Occupation     Group                                Guest  \\\n",
       "Date                                                                    \n",
       "2010-01-11      lawyer      Misc                             John Yoo   \n",
       "2010-01-12      Editor     Media                       Paul Ingrassia   \n",
       "2010-01-13    musician  Musician  Ringo Starr and The Ben Harper Band   \n",
       "2010-01-13   rock band  Musician  Ringo Starr and The Ben Harper Band   \n",
       "2010-01-14  Journalist     Media                           Tom Brokaw   \n",
       "\n",
       "            Forename Number  \n",
       "Date                         \n",
       "2010-01-11                1  \n",
       "2010-01-12                1  \n",
       "2010-01-13                6  \n",
       "2010-01-13                6  \n",
       "2010-01-14                1  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests['2010':'2011'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GroupBy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>Occupation</th>\n",
       "      <th>Guest</th>\n",
       "      <th>Forename Number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Group</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Academic</th>\n",
       "      <td>103</td>\n",
       "      <td>103</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Acting</th>\n",
       "      <td>930</td>\n",
       "      <td>930</td>\n",
       "      <td>930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Advocacy</th>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Athletics</th>\n",
       "      <td>52</td>\n",
       "      <td>52</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Business</th>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Clergy</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Comedy</th>\n",
       "      <td>150</td>\n",
       "      <td>150</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consultant</th>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Government</th>\n",
       "      <td>40</td>\n",
       "      <td>40</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Media</th>\n",
       "      <td>751</td>\n",
       "      <td>751</td>\n",
       "      <td>751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Military</th>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Misc</th>\n",
       "      <td>45</td>\n",
       "      <td>45</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Musician</th>\n",
       "      <td>123</td>\n",
       "      <td>123</td>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Political Aide</th>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Politician</th>\n",
       "      <td>308</td>\n",
       "      <td>308</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Science</th>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>media</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Occupation  Guest  Forename Number\n",
       "Group                                             \n",
       "Academic               103    103              103\n",
       "Acting                 930    930              930\n",
       "Advocacy                24     24               24\n",
       "Athletics               52     52               52\n",
       "Business                25     25               25\n",
       "Clergy                   8      8                8\n",
       "Comedy                 150    150              150\n",
       "Consultant              18     18               18\n",
       "Government              40     40               40\n",
       "Media                  751    751              751\n",
       "Military                16     16               16\n",
       "Misc                    45     45               45\n",
       "Musician               123    123              123\n",
       "Political Aide          36     36               36\n",
       "Politician             308    308              308\n",
       "Science                 28     28               28\n",
       "media                    5      5                5"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Native apply functions are sum, mean, max, min, count, first, last\n",
    "ds_guests.groupby(\"Group\").count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Aufgaben"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Erstelle eine Übersicht die angibt wie oft die einzelnen Gäste zu Besuch waren. \n",
    "- Wie lauten die Top 3 Gäste pro Kategorie?\n",
    "- Was waren die Top 3 Kategorien in der Zeitspanne 01.01.2006 - 01.01.2010 die am häufigsten vertreten waren?\n",
    "- Was waren die Top 3 Kategorien pro Jahr in der gesamten Zeitspanne (Tipp: Verwendet hierzu pd.Grouper)\n",
    "- Was waren die Top 10 Monate an denen am meisten Besucher zu Gast waren?\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Erstelle eine Übersicht die angibt wie oft die einzelnen Gäste zu Besuch waren."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>Occupation</th>\n",
       "      <th>Group</th>\n",
       "      <th>Forename Number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Guest</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>(None)</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(no guest)</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aaron Brown</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aaron Eckhart</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aaron Sorkin</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Abbi Jacobson</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Abderrahim Foukara</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Abdullah II bin Al-Hussein</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adam Arkin</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adam Goldberg</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adam Horovitz</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adam Levine</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adam Sandler</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adam Sandler and Chris Rock</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Admiral General Aladeen\"</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Admiral Michael Mullen</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adrien Brody</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Again, A Look Back</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ahmed Rashid</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aidan Quinn</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Akbar Ahmed</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Al Franken</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Al Gore</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Al Green</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Al Roker</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Al Sharpton</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alan Alda</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alan Colmes</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alan Cumming</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alan Greenspan</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>William Baldwin</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>William Cohan</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>William Cohen</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>William D. Cohan</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>William John Bennett</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>William Kamkwamba</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>William Kristol</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>William Rosen</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>William Safire</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>William Weld &amp; Al Sharpton</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Willie Mays</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Willie Nelson</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wolf Blitzer</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Woody Harrelson</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wu-Tang Clan</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wyatt Cenac</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wyclef Jean</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wynton Marsalis</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yasmine Bleeth</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zach Braff</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zach Galifianakis</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zach Wahls</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zach the Erect\" Galifianakis &amp; Will Ferrell\"</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zaki Chehab</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zbigniew Brzezinski</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zell Miller</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zephyr Teachout</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zoe Saldana</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>no guest</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>none</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1668 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Occupation  Group  \\\n",
       "Guest                                                             \n",
       "(None)                                                 1      0   \n",
       "(no guest)                                             2      0   \n",
       "Aaron Brown                                            1      1   \n",
       "Aaron Eckhart                                          3      3   \n",
       "Aaron Sorkin                                           1      1   \n",
       "Abbi Jacobson                                          1      1   \n",
       "Abderrahim Foukara                                     1      1   \n",
       "Abdullah II bin Al-Hussein                             1      1   \n",
       "Adam Arkin                                             1      1   \n",
       "Adam Goldberg                                          1      1   \n",
       "Adam Horovitz                                          1      1   \n",
       "Adam Levine                                            1      1   \n",
       "Adam Sandler                                           8      8   \n",
       "Adam Sandler and Chris Rock                            2      2   \n",
       "Admiral General Aladeen\"                               1      1   \n",
       "Admiral Michael Mullen                                 2      2   \n",
       "Adrien Brody                                           2      2   \n",
       "Again, A Look Back                                     1      0   \n",
       "Ahmed Rashid                                           1      1   \n",
       "Aidan Quinn                                            1      1   \n",
       "Akbar Ahmed                                            1      1   \n",
       "Al Franken                                             5      5   \n",
       "Al Gore                                                5      5   \n",
       "Al Green                                               1      1   \n",
       "Al Roker                                               3      3   \n",
       "Al Sharpton                                            2      2   \n",
       "Alan Alda                                              1      1   \n",
       "Alan Colmes                                            1      1   \n",
       "Alan Cumming                                           1      1   \n",
       "Alan Greenspan                                         2      2   \n",
       "...                                                  ...    ...   \n",
       "William Baldwin                                        2      2   \n",
       "William Cohan                                          1      1   \n",
       "William Cohen                                          1      1   \n",
       "William D. Cohan                                       2      2   \n",
       "William John Bennett                                   1      1   \n",
       "William Kamkwamba                                      1      1   \n",
       "William Kristol                                        4      4   \n",
       "William Rosen                                          1      1   \n",
       "William Safire                                         1      1   \n",
       "William Weld & Al Sharpton                             2      2   \n",
       "Willie Mays                                            1      1   \n",
       "Willie Nelson                                          2      2   \n",
       "Wolf Blitzer                                           3      3   \n",
       "Woody Harrelson                                        1      1   \n",
       "Wu-Tang Clan                                           1      1   \n",
       "Wyatt Cenac                                            1      1   \n",
       "Wyclef Jean                                            1      1   \n",
       "Wynton Marsalis                                        2      2   \n",
       "Yasmine Bleeth                                         2      2   \n",
       "Zach Braff                                             1      1   \n",
       "Zach Galifianakis                                      2      2   \n",
       "Zach Wahls                                             1      1   \n",
       "Zach the Erect\" Galifianakis & Will Ferrell\"           2      2   \n",
       "Zaki Chehab                                            1      1   \n",
       "Zbigniew Brzezinski                                    1      1   \n",
       "Zell Miller                                            1      1   \n",
       "Zephyr Teachout                                        1      1   \n",
       "Zoe Saldana                                            1      1   \n",
       "no guest                                               0      0   \n",
       "none                                                   0      0   \n",
       "\n",
       "                                              Forename Number  \n",
       "Guest                                                          \n",
       "(None)                                                      1  \n",
       "(no guest)                                                  2  \n",
       "Aaron Brown                                                 1  \n",
       "Aaron Eckhart                                               3  \n",
       "Aaron Sorkin                                                1  \n",
       "Abbi Jacobson                                               1  \n",
       "Abderrahim Foukara                                          1  \n",
       "Abdullah II bin Al-Hussein                                  1  \n",
       "Adam Arkin                                                  1  \n",
       "Adam Goldberg                                               1  \n",
       "Adam Horovitz                                               1  \n",
       "Adam Levine                                                 1  \n",
       "Adam Sandler                                                8  \n",
       "Adam Sandler and Chris Rock                                 2  \n",
       "Admiral General Aladeen\"                                    1  \n",
       "Admiral Michael Mullen                                      2  \n",
       "Adrien Brody                                                2  \n",
       "Again, A Look Back                                          1  \n",
       "Ahmed Rashid                                                1  \n",
       "Aidan Quinn                                                 1  \n",
       "Akbar Ahmed                                                 1  \n",
       "Al Franken                                                  5  \n",
       "Al Gore                                                     5  \n",
       "Al Green                                                    1  \n",
       "Al Roker                                                    3  \n",
       "Al Sharpton                                                 2  \n",
       "Alan Alda                                                   1  \n",
       "Alan Colmes                                                 1  \n",
       "Alan Cumming                                                1  \n",
       "Alan Greenspan                                              2  \n",
       "...                                                       ...  \n",
       "William Baldwin                                             2  \n",
       "William Cohan                                               1  \n",
       "William Cohen                                               1  \n",
       "William D. Cohan                                            2  \n",
       "William John Bennett                                        1  \n",
       "William Kamkwamba                                           1  \n",
       "William Kristol                                             4  \n",
       "William Rosen                                               1  \n",
       "William Safire                                              1  \n",
       "William Weld & Al Sharpton                                  2  \n",
       "Willie Mays                                                 1  \n",
       "Willie Nelson                                               2  \n",
       "Wolf Blitzer                                                3  \n",
       "Woody Harrelson                                             1  \n",
       "Wu-Tang Clan                                                1  \n",
       "Wyatt Cenac                                                 1  \n",
       "Wyclef Jean                                                 1  \n",
       "Wynton Marsalis                                             2  \n",
       "Yasmine Bleeth                                              2  \n",
       "Zach Braff                                                  1  \n",
       "Zach Galifianakis                                           2  \n",
       "Zach Wahls                                                  1  \n",
       "Zach the Erect\" Galifianakis & Will Ferrell\"                2  \n",
       "Zaki Chehab                                                 1  \n",
       "Zbigniew Brzezinski                                         1  \n",
       "Zell Miller                                                 1  \n",
       "Zephyr Teachout                                             1  \n",
       "Zoe Saldana                                                 1  \n",
       "no guest                                                    1  \n",
       "none                                                        3  \n",
       "\n",
       "[1668 rows x 3 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests.groupby(\"Guest\").count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Wie lauten die Top 3 Gäste pro Kategorie?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Group           Guest                          \n",
       "Academic        Reza Aslan                         10\n",
       "                Austan Goolsbee                     6\n",
       "                Michael Beschloss                   5\n",
       "Acting          Denis Leary                        17\n",
       "                Paul Rudd                          13\n",
       "                Will Ferrell                       10\n",
       "Advocacy        Ralph Nader                         4\n",
       "                Ralph Reed                          2\n",
       "                Malala Yousafzai                    2\n",
       "Athletics       Charles Barkley                     3\n",
       "                Mick Foley                          2\n",
       "                LeBron James                        2\n",
       "Business        Bill Gates                          3\n",
       "                T. Boone Pickens                    2\n",
       "                Warren Buffett and Carol Loomis     1\n",
       "Clergy          Al Sharpton                         2\n",
       "                William Weld & Al Sharpton          1\n",
       "                Rt. Rev. V. Gene Robinson           1\n",
       "Comedy          Ricky Gervais                      13\n",
       "                Richard Lewis                      10\n",
       "                Louis C.K.                          8\n",
       "Consultant      Richard Clarke                      2\n",
       "                Richard A. Clarke                   2\n",
       "                Kenneth Pollack                     2\n",
       "Government      Lisa P. Jackson                     3\n",
       "                Susan Rice                          2\n",
       "                Sandra Day O'Connor                 2\n",
       "Media           Fareed Zakaria                     19\n",
       "                Brian Williams                     16\n",
       "                Tom Brokaw                         12\n",
       "Military        Tony Zinni                          2\n",
       "                Stanley McChrystal                  2\n",
       "                Admiral Michael Mullen              2\n",
       "Misc            Andrew Napolitano                   6\n",
       "                Philip K. Howard                    3\n",
       "                Tom Goldstein                       2\n",
       "Musician        Dave Grohl                          3\n",
       "                Randy Jackson                       3\n",
       "                Tenacious D                         3\n",
       "Political Aide  Tim Gunn                            6\n",
       "                George Stephanopoulos               5\n",
       "                Pat Buchanan                        3\n",
       "Politician      Mike Huckabee                       8\n",
       "                Bill Clinton                        7\n",
       "                Bob Dole                            7\n",
       "Science         Neil deGrasse Tyson                 6\n",
       "                Atul Gawande                        2\n",
       "                Neil DeGrasse Tyson                 2\n",
       "media           Judy Woodruff                       2\n",
       "                Jeff Varner                         1\n",
       "                Candy Crowley                       1\n",
       "Name: Group, dtype: int64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def my_guest_top3(df):\n",
    "    return df.groupby(\"Guest\").Group.count().sort_values(ascending=False).head(3)\n",
    "ds_guests.groupby(\"Group\").apply(my_guest_top3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Was waren die Top 3 Kategorien in der Zeitspanne 01.01.2006 - 01.01.2010 die am häufigsten vertreten waren?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>Occupation</th>\n",
       "      <th>Guest</th>\n",
       "      <th>Forename Number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Group</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Media</th>\n",
       "      <td>230</td>\n",
       "      <td>230</td>\n",
       "      <td>230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Acting</th>\n",
       "      <td>117</td>\n",
       "      <td>117</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Politician</th>\n",
       "      <td>99</td>\n",
       "      <td>99</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Occupation  Guest  Forename Number\n",
       "Group                                         \n",
       "Media              230    230              230\n",
       "Acting             117    117              117\n",
       "Politician          99     99               99"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests['2006':'2009'].groupby(\"Group\").count().sort_values(\"Guest\",ascending=False).head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Was waren die Top 3 Kategorien pro Jahr in der gesamten Zeitspanne (Tipp: Verwendet hierzu pd.Grouper)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date        Group     \n",
       "1999-12-31  Acting        108\n",
       "            Comedy         25\n",
       "            Musician       17\n",
       "2000-12-31  Acting        100\n",
       "            Media          21\n",
       "            Politician     13\n",
       "2001-12-31  Acting         92\n",
       "            Media          30\n",
       "            Musician       11\n",
       "2002-12-31  Acting         84\n",
       "            Media          39\n",
       "            Musician       10\n",
       "2003-12-31  Acting         74\n",
       "            Media          41\n",
       "            Politician     14\n",
       "2004-12-31  Acting         51\n",
       "            Media          45\n",
       "            Politician     32\n",
       "2005-12-31  Media          54\n",
       "            Acting         44\n",
       "            Politician     22\n",
       "2006-12-31  Media          47\n",
       "            Acting         44\n",
       "            Politician     25\n",
       "2007-12-31  Media          47\n",
       "            Acting         25\n",
       "            Politician     21\n",
       "2008-12-31  Media          77\n",
       "            Politician     27\n",
       "            Acting         26\n",
       "2009-12-31  Media          59\n",
       "            Politician     26\n",
       "            Acting         22\n",
       "2010-12-31  Media          50\n",
       "            Acting         45\n",
       "            Politician     25\n",
       "2011-12-31  Media          50\n",
       "            Acting         42\n",
       "            Politician     23\n",
       "2012-12-31  Media          52\n",
       "            Acting         33\n",
       "            Politician     29\n",
       "2013-12-31  Acting         60\n",
       "            Media          51\n",
       "            Politician     11\n",
       "2014-12-31  Media          53\n",
       "            Acting         47\n",
       "            Politician     13\n",
       "2015-12-31  Acting         33\n",
       "            Media          24\n",
       "            Politician     14\n",
       "Name: Occupation, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def my_category_top3(df):\n",
    "    return df.groupby(\"Group\").count().Occupation.sort_values(ascending=False).head(3)\n",
    "\n",
    "ds_guests.groupby([pd.Grouper(freq=\"1A\")]).apply(my_category_top3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Was waren die Top 10 Monate an denen am meisten Besucher zu Gast waren?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date\n",
       "1999-03-31    21\n",
       "2010-06-30    20\n",
       "2008-01-31    19\n",
       "2004-03-31    19\n",
       "2004-06-30    18\n",
       "1999-08-31    18\n",
       "2007-03-31    18\n",
       "2000-03-31    18\n",
       "2002-04-30    18\n",
       "2009-06-30    18\n",
       "Name: Guest, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_guests.groupby(pd.Grouper(freq=\"1M\")).count().Guest.sort_values(ascending=False).head(10)"
   ]
  }
 ],
 "metadata": {
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}