4_Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "88d36caf-03a3-4415-b234-0d6dc8895fde",
   "metadata": {},
   "source": [
    "<img src=\"images/bwHPC_Logo_cmyk.svg\" width=\"200\" /> <img src=\"images/HochschuleEsslingen_Logo_RGB_DE.png\" width=\"200\" /> <img src=\"images/Konstanz_Logo.svg\" width=\"200\" /> <img src=\"images/KIT_Logo.png\" width=\"200\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52807ebb-c7c9-48a7-8b19-35e5ff4a6cce",
   "metadata": {},
   "source": [
    "## Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "765f24f9-307a-41fd-837b-52fac5a12b65",
   "metadata": {},
   "source": [
    "* Library für Datenanalyse\n",
    "* Erstellung und Bearbeitung von Series und DataFrames\n",
    "* Lesen und Schreiben von Daten zwischen verschiedenen Formaten\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d857900-d681-43ce-a837-a4bb3c884971",
   "metadata": {},
   "source": [
    "### Pandas-Series\n",
    "* Eindimensional\n",
    "* Hat wie NumPy-Array einen numerischen Index\n",
    "* Hat zusätzlich zum Index ein Label (beliebiger hashable Datentyp; z.B. datetime, string, ...)\n",
    "\n",
    "(für hashable siehe https://docs.python.org/3/glossary.html#term-hashable)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a436b8f3-c2ba-4257-89eb-ea069d1bde77",
   "metadata": {},
   "source": [
    "#### Pandas-Series aus Listen erstellen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f557217-addf-4463-8e15-0c9f2b6e902c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "index = ['Germany', 'France', 'Netherlands']\n",
    "data = [83000000, 67000000, 17000000]\n",
    "\n",
    "series = pd.Series(data=data) ##Pandas-Series ohne Label erstellen\n",
    "print(\"series without label:\")\n",
    "display(series)\n",
    "\n",
    "series = pd.Series(data=data, index=index) ##Pandas-Series mit Label erstellen\n",
    "print(\"\\nseries with label:\")\n",
    "display(series)\n",
    "\n",
    "print(\"\\nindex 0:\")\n",
    "display(series[0]) ##Zugriff auf erstes Element über Index\n",
    "\n",
    "print(\"\\nlabel 'Germany':\")\n",
    "display(series['Germany']) ##Zugriff auf erstes Element über Label"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c87ed1a-5f31-4f6a-9f92-8f788038a3cd",
   "metadata": {},
   "source": [
    "#### Pandas-Series aus Python-Dictionary erstellen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f4e4e53-99c3-4ddd-8364-7ae4449c7a16",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "alter = {'Pia':20, 'Felix':26} ##Python-Dictionary\n",
    "pd.Series(alter) ##Konvertierung in Pandas-Series"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eafe099e-38dd-45a1-8c49-14fe6985b781",
   "metadata": {},
   "source": [
    "#### Operationen auf Pandas Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca8dd892-332f-4ccf-b7f2-3e8fb1219da7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "jahr_1990 = {'Germany':70000000, 'France': 50000000, 'Netherlands': 12000000}\n",
    "jahr_2021 = {'Germany':83000000, 'France': 67000000, 'Netherlands': 17000000, 'Greece': 13000000}\n",
    "\n",
    "einwohner_1990 = pd.Series(jahr_1990) ##Konvertierung in Pandas-Series\n",
    "einwohner_2021 = pd.Series(jahr_2021)\n",
    "\n",
    "print(\"Einwohner im Jahr 1990:\")\n",
    "display(einwohner_1990)\n",
    "\n",
    "print(\"\\nZugriff über Label 'France':\")\n",
    "display(einwohner_1990['France'])\n",
    "\n",
    "print(\"\\nDivision:\")\n",
    "display(einwohner_1990 / einwohner_2021)\n",
    "\n",
    "print(\"\\nDivision mit fill_value:\")\n",
    "display(einwohner_1990.div(einwohner_2021, fill_value=13000000)) ##Fehlenden Wert bei Division nachtragen"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8373020d-c400-4130-851f-adae81cc7cba",
   "metadata": {},
   "source": [
    "### Pandas DataFrames\n",
    "* Zweidimensional\n",
    "* Gruppe von Pandas-Series mit gleichem Index/Label für jeden Datenpunkt (Zeile)\n",
    "* Jede Series hat einen Index (bzw. ein Label) um die Series (Spalte) zu selektieren\n",
    "\n",
    "<p style=\"text-align: center;\"> Pandas-Series 1: </p>\n",
    "\n",
    "| Index       | Einwohner 2021     |\n",
    "|-------------|----------|\n",
    "| Germany     | 82000000 |\n",
    "| France      | 67000000 |\n",
    "| Netherlands | 17000000 |\n",
    "\n",
    "<p style=\"text-align: center;\"> Pandas-Series 2: </p>\n",
    "\n",
    "| Index       | Einwohner 1990     |\n",
    "|-------------|----------|\n",
    "| Germany     | 70000000 |\n",
    "| France      | 50000000 |\n",
    "| Netherlands | 12000000 |\n",
    "\n",
    "<p style=\"text-align: center;\"> Pandas-Dataframe zusammengesetzt aus vorherigen Pandas-Series: </p>\n",
    "\n",
    "| Index       | Einwohner 1990 | Einwohner 2021 |\n",
    "|-------------|----------|----------|\n",
    "| Germany     | 70000000 | 82000000 |\n",
    "| France      | 50000000 | 67000000 | \n",
    "| Netherlands | 12000000 | 17000000 |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1f18b1e-56a9-48c6-871c-b7432190fc03",
   "metadata": {},
   "source": [
    "#### Pandas-DataFrames aus Python generieren"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8858cd36-5824-42ed-a24e-5a9ad57d2778",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "np.random.seed(42)\n",
    "data = np.random.randint(0,101,(4,3)) ##Numpy-Array mit zufälligen Werten generieren\n",
    "display(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8237ee5-cc88-4bcf-8569-c7ba08cd169f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "np.random.seed(42)\n",
    "data = np.random.randint(0,101,(4,3))\n",
    "\n",
    "index = ['Berlin', 'BW', 'Bayern', 'Hessen']\n",
    "columns = ['Jan', 'Feb', 'Mar']\n",
    "\n",
    "df = pd.DataFrame(data=data, index=index, columns=columns) ##Pandas DataFrame erstellen\n",
    "display(df)\n",
    "\n",
    "display(df.info()) ##Weitere Informationen über DataFrame erhalten"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "681a3f6b-b04d-4a7c-9d0c-488038c4a58e",
   "metadata": {},
   "source": [
    "#### DataFrame aus Datei einlesen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94a3e3ac-1ed0-4584-b5f4-6ee6db83856a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "#df = pd.read_csv('s3://nyc-tlc/trip data/green_tripdata_2019-02.csv', nrows=1000) ##CSV aus S3 einlesen; Beschränkung auf 1000 Zeilen\n",
    "## S3 benötigt einen AWS account, daher ist der direkte Zugriff nicht mehr möglich\n",
    "## bitte die lokal bereitgestellte parquet-Datei nutzen (siehe unten)\n",
    "\n",
    "# https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page\n",
    "df = pd.read_parquet('./files/green_tripdata_2023-01.parquet', engine='pyarrow')\n",
    "display(df)\n",
    "\n",
    "print(\"\\nSpalten:\")\n",
    "display(df.columns) ## Spalten anzeigen\n",
    "\n",
    "print(\"\\nIndex:\")\n",
    "display(df.index) ## Index anzeigen\n",
    "\n",
    "print(\"\\ndie ersten sechs Elemente\")\n",
    "display(df.head(6)) ## Zeige erste 6 Elemente\n",
    "\n",
    "print(\"\\nStatistische Übersicht:\")\n",
    "display(df.describe().transpose()) ## Statistische Übersicht erstellen (Min, Max, Quantile, ....)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c861797d-4b39-43a2-9407-b05b6189e70e",
   "metadata": {},
   "source": [
    "#### Arbeiten mit Spalten"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b76b947-4cd2-4b21-8074-dadfc3309f1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_parquet('./files/green_tripdata_2023-01.parquet', engine='pyarrow')\n",
    "\n",
    "display(df['tip_amount']) ## Ausgabe einer spezifischen Spalte des DataFrames\n",
    "display(type(df['tip_amount'])) ## Einzelne Spalte eines DataFrames ist eine Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24ad0989-ddce-470c-8a9c-10b6dc3cc510",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_parquet('./files/green_tripdata_2023-01.parquet', engine='pyarrow')\n",
    "\n",
    "cols = ['tip_amount','total_amount']\n",
    "display(df[cols]) ## Mehrere Spalten ausgeben\n",
    "display(type(df[cols])) ## Zwei Spalten eines DataFrames sind wieder ein DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9f0a662-46ce-448c-b615-96b5795611c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_parquet('./files/green_tripdata_2023-01.parquet', engine='pyarrow')\n",
    "\n",
    "display(100 * df['tip_amount'] / df['total_amount']) ## Prozentualer Anteil des Trinkegelds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "685ec44a-dcaf-4c35-bf7b-2119770a3224",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_parquet('./files/green_tripdata_2023-01.parquet', engine='pyarrow')\n",
    "\n",
    "df['tip_percentage'] = 100 * df['tip_amount'] / df['total_amount'] ## Neue Spalte in DataFrame einfügen\n",
    "display(df.head())\n",
    "\n",
    "display(df.drop('tip_percentage', axis=1)) ## Spalte löschen (aber nur in dem von drop() zurückgegebenen DataFrame)\n",
    "display(df) ## 'tip_percentage' ist im ursprünglichen DataFrame noch da!\n",
    "\n",
    "df = df.drop('tip_percentage', axis=1) ## Hier wird 'tip_percentage' endgültig gelöscht\n",
    "display(df)\n",
    "\n",
    "display(df.shape) ## Dimension des DataFrames\n",
    "\n",
    "display(df.shape[0]) ##Index=0 --> Zeilen\n",
    "\n",
    "display(df.shape[1]) ##Index=1 --> Spalten"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d23f8ed6-2fbf-44e5-a0f8-871a406764f4",
   "metadata": {},
   "source": [
    "#### Arbeiten mit Zeilen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6cab1785-d1bd-406a-83de-a1481cb970e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_parquet('./files/green_tripdata_2023-01.parquet', engine='pyarrow')\n",
    "\n",
    "display(df.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7eff6526-390d-4d2c-a5ab-327dfc5b2ab2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index(\"lpep_pickup_datetime\") ## Index ändern\n",
    "## Achtung: \"lpep_pickup_datetime\" ist jetzt keine Spalte mehr!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20876f13-a664-4480-8f68-6c79a7111e85",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1e6b9fe-cd50-462c-a9a0-444a7ce65386",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.reset_index() ## Index zurücksetzen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "510ec6e3-df1e-4f24-b8b9-dfd47667ed14",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[0:6] ##Integer-basiert die ersten 6 Zeilen ausgeben"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68934a91-e32c-4d22-b964-37785f30d258",
   "metadata": {},
   "outputs": [],
   "source": [
    "display(df.index)\n",
    "df.loc[[pd.to_datetime('2023-01-01 00:26:10'),pd.to_datetime('2023-01-01 00:35:12')]] ## Nur Zeilen mit bestimmtem Datum anzeigen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97c83c4f-e9f2-40b2-af64-ab9355ce6691",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(pd.to_datetime('2023-01-01 00:26:10'), axis=0) ## Einzelne Zeile wegwerfen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1759a891-55fb-40da-9298-607f23294fa2",
   "metadata": {},
   "outputs": [],
   "source": [
    "row = df.iloc[0] ## Einzelne Zeile selektieren"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13d1bfa5-2e82-451f-a687-98322a3d42dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "row ## Details anzeigen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32fdfa47-e3e1-451b-8fe2-98610461b25a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.append(row) ## Zeile unten anhängen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78195caa-7504-427f-8abe-11bb8269dc9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80ad9d71-33e9-4c53-a168-76ac9b87b60b",
   "metadata": {},
   "source": [
    "#### Filtern mit Bedingungen"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b170a1b-2f6d-4e98-b0f2-4e54c784b739",
   "metadata": {},
   "source": [
    "Jede Spalte nennen wir ein Feature der Daten. Jede Zeile nennen wir eine Instanz der Daten."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4ce8ebe-9c44-4e35-87f3-456338189d96",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_parquet('./files/green_tripdata_2023-01.parquet', engine='pyarrow') ##Neu einlesen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b0a78cf-3564-4a82-be6a-71f4984ec9e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"tip_amount\"] > 3 ##Wo ist 'tip_amount' größer 3?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fe7db9a-5746-46da-bf63-d42cd9a4e60b",
   "metadata": {},
   "outputs": [],
   "source": [
    "var1 = df[\"tip_amount\"] > 3 ## Neue Variable einführen mit Boolean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6aca09b6-d5ff-4562-997b-f8d488a4174f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[var1] ## DataFrame wird gefiltert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3ef394a-5466-4edb-b0ab-2c9436617c7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[(df[\"tip_amount\"] > 3) & (df[\"total_amount\"] > 40)] ## Filtern in einer Zeile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91d42eee-102d-4dbc-b0ab-85811a60f750",
   "metadata": {},
   "outputs": [],
   "source": [
    "filter = [2,5] ## Filter, der nur 2 und 5 auswählt\n",
    "df['passenger_count'].isin(filter).iloc[500:510]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28b11d3c-b6bd-4208-9bff-f04d48d8e30e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[501] ## 501 ist false da 'passenger_count'=1 und nicht 2 oder 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "267bd0d1-d2da-4f2d-9d40-3bafaf565d8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc9b43c0-f220-4267-8a29-55e644ee5a07",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.info() ## Informationen über Typen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59fcf95e-3aa4-47fa-b7c7-0be73785daae",
   "metadata": {},
   "outputs": [],
   "source": [
    "def last_two(number): ## Letzte 2 Zahlen jeder Zeile einer Spalte ausgeben\n",
    "    return int(str(number)[-2:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "395bc3ce-3963-44b5-bdc9-fc1c4dad1170",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_two(1234567)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b7a4127-de7b-4e05-b1c0-36bde9d8333a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['PULocationID'].apply(last_two) ##Anwenden auf Spalte in DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35ba351a-b385-4136-91e4-2122fdea1b5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['PULocationID_last_two'] = df['PULocationID'].apply(last_two)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d91f40b-a15e-41d5-b8ed-b47afb34c470",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca370776-8938-4b0a-8949-9a62f965bf86",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['total_amount'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e3e4fac-b33b-48f4-8867-aee47670df31",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f11da157-4843-44f9-b5e1-80c7424f6652",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "python_workshop_env",
   "language": "python",
   "name": "python_workshop_env"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}