4_pandas_01_old.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to Pandas\n",
    "\n",
    "- prepared by [Rita Colaço](https://www.cpr.ku.dk/staff/?id=621366&vis=medarbejder) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas is a library for data analysis and its powertool is the **DataFrame**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas is well suited for many different kinds of data:\n",
    "\n",
    "- Tabular data with heterogeneously-typed columns, as in an Excel spreadsheet\n",
    "- Ordered and unordered time series data.\n",
    "- Any other form of observational / statistical data sets. The data actually need not be labeled at all to be placed into a pandas data structure"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Learning goals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Concept of \n",
    "    - vectors (1d-arrays) as series\n",
    "    - tables as data frames\n",
    "- Organization of a table: index, columns\n",
    "- Filtering and slicing\n",
    "- Data types a Pandas dataframe can handle\n",
    "- Applying statistics and grouping\n",
    "- Modifying a table\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What is a DataFrame?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A DataFrame is basically, a **Table** of data (or a tabular data structure) with labeled rows and columns. The rows are labeled by a special data structure called an Index, taht permits fast look-up and powerful relational operations.\n",
    "For example:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| (index) | Name | Age | Height | LikesIceCream |\n",
    "| :---: | :--: | :--: | :--: | :--: |\n",
    "| 0     | \"Nick\" | 22 | 3.4 | True |\n",
    "| 1     | \"Jenn\" | 55 | 1.2 | True |\n",
    "| 2     | \"Joe\"  | 25 | 2.2 | True |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing Pandas library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a DataFrame directly"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From a List of Lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "    [2.23, 1, \"test\"],\n",
    "    [3.45, 2, \"train\"],\n",
    "    [4.5, 3, \"test\"],\n",
    "    [6.0, 4, \"train\"]\n",
    "]\n",
    "\n",
    "df = pd.DataFrame(data, columns=['A', 'B', 'C'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.23</td>\n",
       "      <td>1</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.45</td>\n",
       "      <td>2</td>\n",
       "      <td>train</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.50</td>\n",
       "      <td>3</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6.00</td>\n",
       "      <td>4</td>\n",
       "      <td>train</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      A  B      C\n",
       "0  2.23  1   test\n",
       "1  3.45  2  train\n",
       "2  4.50  3   test\n",
       "3  6.00  4  train"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From a List of Dicts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "    {'A':2.23, 'B':1, 'C':\"test\"},\n",
    "    {'A':3.45, 'B':2, 'C':\"train\"},\n",
    "    {'A':4.5, 'B':3, 'C':\"test\"},\n",
    "    {'A':6.0, 'B':4, 'C':\"train\"}\n",
    "]\n",
    "\n",
    "df = pd.DataFrame(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.23</td>\n",
       "      <td>1</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.45</td>\n",
       "      <td>2</td>\n",
       "      <td>train</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.50</td>\n",
       "      <td>3</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6.00</td>\n",
       "      <td>4</td>\n",
       "      <td>train</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      A  B      C\n",
       "0  2.23  1   test\n",
       "1  3.45  2  train\n",
       "2  4.50  3   test\n",
       "3  6.00  4  train"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From a Dict of Lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({\n",
    "    'A': [2.23, 3.45, 4.5, 6.0],\n",
    "    'B': [1, 2, 3, 4],\n",
    "    'C': [\"test\", \"train\", \"test\", \"train\"]\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.23</td>\n",
       "      <td>1</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.45</td>\n",
       "      <td>2</td>\n",
       "      <td>train</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.50</td>\n",
       "      <td>3</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6.00</td>\n",
       "      <td>4</td>\n",
       "      <td>train</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      A  B      C\n",
       "0  2.23  1   test\n",
       "1  3.45  2  train\n",
       "2  4.50  3   test\n",
       "3  6.00  4  train"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From an empty DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame()\n",
    "df['A'] = [2.23, 3.45, 4.5, 6.0]\n",
    "df['B'] = [1, 2, 3, 4]\n",
    "df['C'] = [\"test\", \"train\", \"test\", \"train\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.23</td>\n",
       "      <td>1</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.45</td>\n",
       "      <td>2</td>\n",
       "      <td>train</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.50</td>\n",
       "      <td>3</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6.00</td>\n",
       "      <td>4</td>\n",
       "      <td>train</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      A  B      C\n",
       "0  2.23  1   test\n",
       "1  3.45  2  train\n",
       "2  4.50  3   test\n",
       "3  6.00  4  train"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 1\n",
    "Please recreate the table below as a Dataframe using one of the approaches detailed above:\n",
    "\n",
    "| Year | Product | Cost |\n",
    "| :--: | :----:  | :--: |\n",
    "| 2015 | Apples  | 0.35 |\n",
    "| 2016 | Apples  | 0.45 |\n",
    "| 2015 | Bananas | 0.75 |\n",
    "| 2016 | Bananas | 1.10 |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which approach did you prefer? Why?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Making DataFrames from a Data File\n",
    "\n",
    "Pandas has functions that can make DataFrames from a wide variety of file types.  To do this, use one of the functions in Pandas that start with \"read_\".  Here is a non-exclusive list of examples:\n",
    "\n",
    "| File Type | Function Name |\n",
    "| :----:    |  :---:  |\n",
    "| Excel | `pd.read_excel()` |\n",
    "| CSV, TSV | `pd.read_csv()` |\n",
    "| H5, HDF, HDF5 | `pd.read_hdf()` |\n",
    "| JSON  | `pd.read_json()` |\n",
    "| SQL | `pd.read_sql_table()` |\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# url = 'https://raw.githubusercontent.com/mwaskom/seaborn-data/master/titanic.csv'\n",
    "# df = pd.read_csv(url)\n",
    "url_ecdc_daily_cases = \"https://opendata.ecdc.europa.eu/covid19/casedistribution/csv/data.csv\"\n",
    "df = pd.read_csv(url_ecdc_daily_cases, parse_dates=True, infer_datetime_format=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Examining the Dataset\n",
    "\n",
    "Sometimes, we might just want to quickly inspect the DataFrame:\n",
    "\n",
    "### Attributes\n",
    "```python\n",
    "df.shape    # Shape of the object (2D)\n",
    "df.dtypes   # Data types in each column\n",
    "df.index    # Index range\n",
    "df.columns  # Column names\n",
    "```\n",
    "\n",
    "### Methods\n",
    "\n",
    "```python\n",
    "df.describe()   # Descriptive statistics of columns\n",
    "df.info()       # DataFrame information\n",
    "\n",
    "```\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60409, 12)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dateRep                                                        object\n",
       "day                                                             int64\n",
       "month                                                           int64\n",
       "year                                                            int64\n",
       "cases                                                           int64\n",
       "deaths                                                          int64\n",
       "countriesAndTerritories                                        object\n",
       "geoId                                                          object\n",
       "countryterritoryCode                                           object\n",
       "popData2019                                                   float64\n",
       "continentExp                                                   object\n",
       "Cumulative_number_for_14_days_of_COVID-19_cases_per_100000    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Index and Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=446, step=1)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Entity', 'Code', 'Year', 'Electricity consumption'], dtype='object')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting Data\n",
    "\n",
    "Pandas has a lot of flexibility in the number of syntaxes it supports.  For example, to select columns in a DataFrame:\n",
    "\n",
    "```python\n",
    "df['Column1']\n",
    "df.Column1  # no whitespaces possible!\n",
    "```\n",
    "\n",
    "Multiple Columns can also be selected by providing a list:\n",
    "\n",
    "```python\n",
    "df[['Column1', 'Column2']]\n",
    "```\n",
    "\n",
    "Rows are selected with the **iloc** and **loc** attributes:\n",
    "\n",
    "```python\n",
    "df.iloc[5]  # Used to get the \"integer\" index of the row.\n",
    "df.loc['Row5']  # Used if rows are named.\n",
    "```\n",
    "\n",
    "However, with large DataFrames, we often just want to see the first or last rows, or even just a sample of the rows.\n",
    "\n",
    "| Method | Description |\n",
    "| ---  | --- |\n",
    "| `df.head(5)` | the first 5 rows |\n",
    "| `df.tail(5)` | the last 5 rows |\n",
    "| `df.sample(5)` | a random 5 rows |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 2\n",
    "\n",
    "Please open the file `titanic.csv` (using `pd.read_csv`) and use it to answer the following questions about the rdataset. If you reach the end of the exercises, explore the dataset and DataFrames more and see what you can find about it!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Display the first 5 lines of the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show the last 3 lines of the \"alive\" column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check 3 random lines of the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a new dataframe containing just the \"survived\", \"sex\", and \"age\" columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a new dataframe containing just the 10th, 15th and 16th lines of the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query/Filtering Data\n",
    "\n",
    "To get rows based on their value, Pandas supports both Numpy's logical indexing:\n",
    "\n",
    "```python\n",
    "select_rows = df[df['Column1'] > 0]\n",
    "```\n",
    "\n",
    "and an SQL-like query string:\n",
    "    \n",
    "```python\n",
    "df.query('Colummn1 > 0')\n",
    "```\n",
    "\n",
    "One can also filter based on multiple conditions, using the element-wise (\"bit-wise\") logical operators **&** data intersection, or **|** for the data union.\n",
    "\n",
    "```python\n",
    "select_rows = df[(df['Column1'] > 0) & (df['Column2'] > 2)]\n",
    "```\n",
    "\n",
    "```python\n",
    "select_rows = df[(df['Column1'] > 0) | (df['Column2'] > 2)]\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 3\n",
    "Using the Titanic dataset, let's do some data querying exercises."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is ticket fare for the 1st class? The 2nd? The 3rd?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.Series([True, True]) & pd.Series([False, False])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Did the oldest passenger on the Titanic survive?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Was the youngest passenger on the Titanic alone?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many passengers on the Titanic embarked from Cherbourg?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How much money did the Titanic make from passengers from Southampton? From Cherbourg? From Queenstown?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Considering only those passengers older than 22, were there more Males travelling alone from Southampton or Females in Third class from Cherbourg?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summarizing/Statistics in DataFrames\n",
    "\n",
    "Pandas' Series and DataFrames are iterables, and can be given to any function that expects a list or Numpy Array, which allows them to be useful to many different libraries' functions.  For example, to compute basic statistics:\n",
    "\n",
    "```python\n",
    "df['Column1'].count()\n",
    "df['Column1'].max()\n",
    "df['Column2'][df['Column1'] == 'string'].sum()\n",
    "\n",
    "```\n",
    "\n",
    "You can also use the \"pipe\" method to call a function on the rows or columns of a DataFrame:\n",
    "\n",
    "```python\n",
    "df['Column1'].pipe(np.mean)\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the mean ticket fare that the passengers paid on the titanic? And the median?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many passengers does this dataset contain?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What class ticket did the 10th (index = 9) passenger in this dataset buy?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What proportion of the passengers were alone on the titanic?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many different classes were on the titanic?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many men and women are in this dataset? (value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many passengers are sitting in each class?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transforming/Modifying Data\n",
    "\n",
    "Any transformation function can be performed on each element of a column or on the entire DataFrame. For example:\n",
    "\n",
    "\n",
    "```python\n",
    "df['Column1'] * 5\n",
    "\n",
    "np.sqrt(df['Column1'])\n",
    "\n",
    "df['Column1'].str.upper()\n",
    "\n",
    "del df['B']\n",
    "\n",
    "df['Column1'] = [3, 9. 27, 81]  # Replace the entire column with other values (length must match)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get everyone's age if they were still alive today (hint: Titanic sunk in 1912)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make the class name title-cased (the first letter capitalized)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a columns called \"not_survived\", the opposite of the \"survived\" column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GroupBy Operations\n",
    "\n",
    "In most of our tasks, getting single metrics from a dataset is not enough, and we often actually want to compare metrics between groups or conditions.\n",
    "\n",
    "The **groupby()** method essentially splits the data into different groups depending on a variable of your choice, and allows you to apply summary functions on each group. For example, if you wanted to calculate the mean temperature by month from a given data frame:\n",
    "\n",
    "```python\n",
    "df.groupby('month').temperature.mean()\n",
    "```\n",
    "where \"month\" and \"temperature\" are column names from the data frame.\n",
    " \n",
    "You can also group by multiple columns, by providing a list of column names:\n",
    " \n",
    "```python\n",
    "df.groupby(['year', 'month']).temperature.mean()\n",
    "```\n",
    "\n",
    "The **groupby()** function returns a GroupBy object, where the **.groups** variable is a dictionary whose keys are the computed unique groups.\n",
    "\n",
    "Groupby objects are **lazy**, meaning they don't start calculating anything until they know the full pipeline.  This approach is called the **\"Split-Apply-Combine\"** workflow.  You can get more info on it [here](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 6\n",
    "\n",
    "Let's try this out on the Titanic dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What was the median ticket fare for each class?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What was the survival rate for each class?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What was the survival rate for each sex?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What was the survival rate, broken down by both sex and class?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which class tended to travel alone more often? Did it matter where they were embarking from?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What was the ticket fare for each embarking city?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What was the median age of the survivors vs non-survivors, when sex is considered as a factor?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# For Track 2 (or not, what do you think?)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GroupBy Operations: Multiple Statistics per Group\n",
    "\n",
    "Another piece of syntax we are going to look at, is the **agg()** function for Pandas. The aggregation functionality provided by this function allows multiple statistics to be calculated per group in one calculation.\n",
    "\n",
    "The instructions to the function **agg()** are provided in the form of a dictionary, where the keys specify the columns upon which to apply the operations, and the value specify the function to run:\n",
    "\n",
    "```python\n",
    "df.groupby(['year', 'month']).agg({'duration':sum,\n",
    "                                   'network_type':'count',\n",
    "                                   'date':'first'})\n",
    "```\n",
    "\n",
    "You can also apply multiple functions to one column in groups:\n",
    "\n",
    "```python\n",
    "df.groupby(['year', 'month']).agg({'duration':[min, max, sum],\n",
    "                                   'network_type':'count',\n",
    "                                   'date':[min, 'first', 'nunique']})\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 7\n",
    "\n",
    "Now, let's try to apply it to our Titanic dataset, and answer the following questions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many man, women and childern survived, and what was their average age?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.groupby('who').agg({'survived':sum,\n",
    "#                        'age':\"mean\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many males and females, embarking on different towns, were alive? And how many of those were alone?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.groupby(['sex', 'class', 'embark_town']).agg({'alive':\"count\",\n",
    "#                                                  \"alone\":sum})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Handling Missing Values\n",
    "\n",
    "Missing values are often a concern in data science, for example in proteomics, and can be indicated with a **`None`** or **`NaN`** (np.nan in Numpy). Pandas DataFrames have several methods for detecting, removing and replacing these values:\n",
    "\n",
    "| method | description\n",
    "| ---:  | :---- |\n",
    "**`isna()`** | Returns True for each NaN |\n",
    "**`notna()`** | Returns False for each NaN |\n",
    "**`dropna()`** | Returns just the rows without any NaNs |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 8"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What proportion of the \"deck\" column is missing data?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many rows don't contain any missing data at all?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a dataframe with only the rows containing no missing data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imputation\n",
    "\n",
    "Imputation means replacing the missing values with real values. \n",
    "\n",
    "| method | description |\n",
    "| ----: |  :---- |\n",
    "| **`fillna()`** | Replaces the NaNs with values (provides lots of options) |\n",
    "| **`ffill()`** | Replaces the Nans with the previous non-NaN value (equivalent to df.fillna(method='ffill') |\n",
    "| **`bfill()`** | Replaces the Nans with the following non-NaN value (equivalent to df.fillna(method='bfill') |\n",
    "| **`interpolate()`** | interpolates nans with previous and following values |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 9\n",
    "\n",
    "Using the following DataFrame, solve the exercises below."
   ]
  },
  {
   "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>time</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.5</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.5</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3.5</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   time  value\n",
       "0   0.5    6.0\n",
       "1   1.0    4.0\n",
       "2   1.5    5.0\n",
       "3   NaN    8.0\n",
       "4   2.5    NaN\n",
       "5   3.0   10.0\n",
       "6   3.5   11.0\n",
       "7   NaN    NaN"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.DataFrame({'time': [0.5, 1., 1.5, None, 2.5, 3., 3.5, None], 'value': [6, 4, 5, 8, None, 10, 11, None]})\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace all the missing \"value\" rows with zeros."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace the missing \"time\" rows with the previous value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace all of the missing values with the data from the next row. What do you notice when you do this with this dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Linearly interpolate the missing data. What is the result for this dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}