Chapter-09:Pandas.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Chpt09-Pandas.ipynb",
      "provenance": [],
      "include_colab_link": true
    },
    "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.8.0"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/kbehrman/foundational-python-for-data-science/blob/main/Chapter-09%3APandas.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EWTjyen_zfYE"
      },
      "source": [
        "## About DataFrames\n",
        "\n",
        "- parts of a dataframe: series\n",
        "- relation to np arrays"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Y0qM0sOTpZVf"
      },
      "source": [
        "## Creation"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oYkPFyraDUJU"
      },
      "source": [
        "import pandas as pd\n",
        "df = pd.DataFrame()\n",
        "print(df)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "J7SNcde7OReB"
      },
      "source": [
        "### From dictionary"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "a4oYxfdm2GoZ"
      },
      "source": [
        "import pandas as pd\n",
        "first_names = ['shanda', 'rolly', 'molly', 'frank', 'rip', 'steven', 'gwen', 'arthur']\n",
        "last_names = ['smith', 'brocker', 'stein', 'bach', 'spencer', 'de wilde', 'mason', 'davis']\n",
        "ages = [43, 23, 78, 56, 26, 14, 46, 92]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "P4RVk2Mf9V3h"
      },
      "source": [
        "data = {'first':first_names,\n",
        "        'last':last_names,\n",
        "        'ages':ages}\n",
        "data"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RMj-CeZK9hmY",
        "scrolled": true
      },
      "source": [
        "participants = pd.DataFrame(data)\n",
        "participants"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1022nCWeOkON"
      },
      "source": [
        "### From lists"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3cZ5mTti9TTd",
        "scrolled": true
      },
      "source": [
        "data = [[\"shanda\", \"smith\", 43],\n",
        "        [\"rolly\", \"brocker\", 23],\n",
        "        [\"molly\", \"stein\", 78],\n",
        "        [\"frank\", \"bach\", 56],\n",
        "        [\"rip\", \"spencer\", 26],\n",
        "        [\"steven\", \"de wilde\", 14],\n",
        "        [\"gwen\", \"mason\", 46],\n",
        "        [\"arthur\", \"davis\", 92]]\n",
        "\n",
        "participants = pd.DataFrame(data)\n",
        "participants"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hfvePMqr8_pN"
      },
      "source": [
        "column_names = ['first', 'last', 'ages']\n",
        "participants = pd.DataFrame(data, columns=column_names)\n",
        "participants"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "JJujqrCGUC7x"
      },
      "source": [
        "index_labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h']\n",
        "participants = pd.DataFrame(data, \n",
        "                            columns=column_names, \n",
        "                            index=index_labels)\n",
        "participants"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cPkA1RhC-AUg"
      },
      "source": [
        "# Data from data.fivethiryeight.com\n",
        "# Data set: college majors\n",
        "# Download the file to your workstation and then upload it to Colab\n",
        "college_majors = pd.read_csv('/content/all-ages.csv')\n",
        "\n",
        "\n",
        "college_majors = college_majors[['Major','Major_category','Total','Unemployment_rate']]\n",
        "college_majors"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZtMf_Ueey2lf"
      },
      "source": [
        "## Accessing Data\n",
        "- Head/Tail\n",
        "- descriptive stats\n",
        "\n",
        "- indexes(columns) named or numbered\n",
        "- accessing columns or indexes (brackets, iloc, loc, at, iat)\n",
        "- masking and filtering \n",
        "    - single condidtion\n",
        "    - combining conditions\n",
        "    - conditions using multiple columns"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ezeoh6PKVtq_"
      },
      "source": [
        "### Heads and tails"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ThM3XqpNSZLG"
      },
      "source": [
        "college_majors.head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZmtJzpIRSd9-"
      },
      "source": [
        "college_majors.head(3)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wNsXBxChSh3V"
      },
      "source": [
        "college_majors.tail()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DRTlJLytVz-T"
      },
      "source": [
        "### Descriptive statistics"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Dggaxh27SwAs"
      },
      "source": [
        "college_majors.describe()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Bf0kD786SyWb"
      },
      "source": [
        "college_majors.describe(percentiles=[0.1, 0.9])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LBJGD9AuTQ1f"
      },
      "source": [
        "import numpy as np\n",
        "college_majors.describe(include=[np.object])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "raxx4pozUlUn"
      },
      "source": [
        "college_majors.describe(include=['object'])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lymzkkWHVJkz"
      },
      "source": [
        "college_majors.describe(include='all')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OvXWs6KgTdy9"
      },
      "source": [
        "college_majors.describe(exclude=['int'])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Nt-Q_Dr-vx6Z"
      },
      "source": [
        "### Access data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oBrhJ7XATnj8"
      },
      "source": [
        "participants"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CR6SEnUVv8qm"
      },
      "source": [
        "participants['first']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LylxZ47tpn_-"
      },
      "source": [
        "df = pd.DataFrame({'one':[1,2,3],'two':[9,5,3]})\n",
        "df.std()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CTOWseyfppk2"
      },
      "source": [
        "df.iloc[0:2]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ToviR3Rsxw60"
      },
      "source": [
        "participants.ages"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B6NN2p66y5D8"
      },
      "source": [
        "participants[['last', 'first']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NqzMaXAhxzZ0"
      },
      "source": [
        "participants[3:6]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nP8K05QVyCVE"
      },
      "source": [
        "participants['a':'c']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hpMfx4ULzzNo"
      },
      "source": [
        "mask = [False, True, True, False, False, True, False, False]\n",
        "participants[mask]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PdO2ApWxTWLw"
      },
      "source": [
        ""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "--zYZdF6zXit"
      },
      "source": [
        "### Optimized access by name"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EncruiIbyzpb"
      },
      "source": [
        "participants.loc['c']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Nuj0mgF-0XDS"
      },
      "source": [
        "participants.loc['c':'f']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4mSuz2iU0bpY"
      },
      "source": [
        "mask = [False, True, True, False, False, True, False, False]\n",
        "participants.loc[mask]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UUxLcu2K04HU"
      },
      "source": [
        "participants.loc[:, 'first']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fT5a7dw91HSD"
      },
      "source": [
        "participants.loc[:'c', ['ages', 'last']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "p7uC65-j1PYa"
      },
      "source": [
        "participants.loc[:'c', [False, True, True]]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SH78jUEv10gG"
      },
      "source": [
        "### Optimized access by index"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "scrVkjdf1tg9"
      },
      "source": [
        "participants.iloc[3]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Rte4f7wx2FG6"
      },
      "source": [
        "participants.iloc[1:4]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XArxWNfC2NUD"
      },
      "source": [
        "participants.iloc[1:4, :2]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RO7_EENGCiFn"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p1Njevwl5XYE"
      },
      "source": [
        "### Masking and filtering"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RiQ4i_TIZ6F4"
      },
      "source": [
        "#### Comparison operators"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cLQH481YTr-K"
      },
      "source": [
        "college_majors = pd.read_csv('/content/all-ages.csv')\n",
        "college_majors"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "eRdlYtQNVq1H"
      },
      "source": [
        "college_majors.Major_category == 'Humanities & Liberal Arts'"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "24b3fyFjWXll"
      },
      "source": [
        "total_mask = college_majors.loc[:, 'Total'] > 1200000\n",
        "total_mask"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "80_qd6W9ZBmV"
      },
      "source": [
        "top_majors = college_majors.loc[total_mask]\n",
        "top_majors"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2NFdv63HlulB"
      },
      "source": [
        "top_majors.Total.min()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ugXaIFdply5H"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FLrU9dZFZL7B"
      },
      "source": [
        "college_majors.Unemployment_rate.describe()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7GmxfUmQbZC1"
      },
      "source": [
        "employ_rate_mask = college_majors.loc[:, 'Unemployment_rate'] <= 0.046261\n",
        "employ_rate_majors = college_majors.loc[employ_rate_mask]\n",
        "employ_rate_majors.Major_category.unique()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "O4kaj-bjas_8"
      },
      "source": [
        "#### Pandas boolean operators\n",
        "| & ~"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6nscpZ-KcbRO"
      },
      "source": [
        "total_rate_mask = employ_rate_mask & total_mask\n",
        "total_rate_mask"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YUORS6dRdtYg"
      },
      "source": [
        "college_majors.loc[total_rate_mask]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QBUjPrJKcPnv"
      },
      "source": [
        "lower_rate_mask = ~employ_rate_mask\n",
        "lower_rate_majors = college_majors.loc[lower_rate_mask]\n",
        "lower_rate_majors.Unemployment_rate.min()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TMmiw2EweKP7"
      },
      "source": [
        "college_majors.loc[total_mask | employ_rate_mask]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VF06jUcrzA3Z"
      },
      "source": [
        "## Manipulating DataFrames\n",
        "- renaming\n",
        "- adding data to a dataframe\n",
        "    - columns\n",
        "        - from list\n",
        "        - dataframe\n",
        "        - operation on other columns\n",
        "    - rows\n",
        "        - append\n",
        "        - concat\n",
        "- deleting \n",
        "    - index (resetting)\n",
        "    - columns drop (in_place)\n",
        "    - rows"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ENm0xEEPdlvY"
      },
      "source": [
        "participants.columns"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-WTDTh96d1ut"
      },
      "source": [
        "participants.rename(columns={'ages': 'Age'})"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YpIHFOzCpEoB"
      },
      "source": [
        "participants.columns"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6QYKW7GApH4D"
      },
      "source": [
        "participants.rename(columns={'ages':'Age'}, inplace=True)\n",
        "participants.columns"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SGUkg6UVpRQx"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oSYpSJRMhZ64"
      },
      "source": [
        "participants['Zip Code'] = [94702, 97402, 94223, 94705, 97503, 94705, 94111, 95333]\n",
        "participants"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QlbbgqhriACy"
      },
      "source": [
        "participants['Full Name'] = participants.loc[:, 'first'] + participants.loc[:, 'last']\n",
        "participants"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "o-8kyONVjI1R"
      },
      "source": [
        "participants['Full Name'] = participants.loc[:, 'first'] + ' ' + participants.loc[:, 'last']\n",
        "participants"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jANdbGQCzSx-"
      },
      "source": [
        "## Manipulating Data\n",
        "- setting values using loc and iloc \n",
        "- operations on dataframes and series\n",
        "- map function elements in column (series)\n",
        "- apply function - across rows or columns\n",
        "\n",
        "- pivot, pivot_table?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YP5wkRBxlh35"
      },
      "source": [
        "participants.loc['h', 'first'] = 'Paul'\n",
        "participants"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lVjiM14Mlqxj"
      },
      "source": [
        "participants.iloc[3, 2] = 99\n",
        "participants"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7wvNc17BmRx2"
      },
      "source": [
        "participants.Age -= 1\n",
        "participants"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XWgZeJ9amqZh"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vreWwDdhhR8b"
      },
      "source": [
        "#### Replace"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PHyFvDJueQDg"
      },
      "source": [
        "participants.replace('rolly', 'Smiley')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IAgk0p0Reu7M"
      },
      "source": [
        "participants.replace(r'(s)([a-z]+)', r'S\\2', regex=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QzWUktaVfZ4e"
      },
      "source": [
        "def cap_word(w):\n",
        "    return w.capitalize()\n",
        "\n",
        "participants.loc[:, 'first'].apply(cap_word)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_5fCulbyprJO"
      },
      "source": [
        "def say_hello(row):\n",
        "    return f'{row[\"first\"]} is {row[\"Age\"]} years old.'\n",
        "\n",
        "participants.apply(say_hello, axis=1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3mnBIVoio-pG"
      },
      "source": [
        "### Interactive Display"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "eKsnOn2DkHnp"
      },
      "source": [
        "#@ Colab DataFrame interactive display\n",
        "%load_ext google.colab.data_table"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nav-qa3Tkx7v"
      },
      "source": [
        "college_majors"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IdEFPkqcC40O"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}