notebooks/1.4 Skills Pandas.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.4 Skills: Pandas 🐼\n",
    "\n",
    "In this notebook we will cover how to:\n",
    "- work with the two main data types in `pandas`: `DataFrame` and `Series`\n",
    "- work with data types in `pandas`, especially strings and dates\n",
    "- load data from JSON and CSV into a `DataFrame`\n",
    "- manipulate the columns of a `DataFrame`\n",
    "- access data in a `DataFrame` by means of indexes and slicing"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `pandas`' data structures"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `Series`\n",
    "\n",
    "In `pandas`, series are the building blocks of dataframes.\n",
    "\n",
    "Think of a series as a column in a table. A series collects *observations* about a given *variable*. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "from random import random\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Numerical series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's create a series containing 100 random numbers\n",
    "# ranging between 0 and 1\n",
    "\n",
    "s = pd.Series([random() for n in range(0, 100)])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each observation in the series has an **index** as well as a set of **values**: they can be accessed via the omonymous properties:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=100, step=1)"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0,\n",
       " 1,\n",
       " 2,\n",
       " 3,\n",
       " 4,\n",
       " 5,\n",
       " 6,\n",
       " 7,\n",
       " 8,\n",
       " 9,\n",
       " 10,\n",
       " 11,\n",
       " 12,\n",
       " 13,\n",
       " 14,\n",
       " 15,\n",
       " 16,\n",
       " 17,\n",
       " 18,\n",
       " 19,\n",
       " 20,\n",
       " 21,\n",
       " 22,\n",
       " 23,\n",
       " 24,\n",
       " 25,\n",
       " 26,\n",
       " 27,\n",
       " 28,\n",
       " 29,\n",
       " 30,\n",
       " 31,\n",
       " 32,\n",
       " 33,\n",
       " 34,\n",
       " 35,\n",
       " 36,\n",
       " 37,\n",
       " 38,\n",
       " 39,\n",
       " 40,\n",
       " 41,\n",
       " 42,\n",
       " 43,\n",
       " 44,\n",
       " 45,\n",
       " 46,\n",
       " 47,\n",
       " 48,\n",
       " 49,\n",
       " 50,\n",
       " 51,\n",
       " 52,\n",
       " 53,\n",
       " 54,\n",
       " 55,\n",
       " 56,\n",
       " 57,\n",
       " 58,\n",
       " 59,\n",
       " 60,\n",
       " 61,\n",
       " 62,\n",
       " 63,\n",
       " 64,\n",
       " 65,\n",
       " 66,\n",
       " 67,\n",
       " 68,\n",
       " 69,\n",
       " 70,\n",
       " 71,\n",
       " 72,\n",
       " 73,\n",
       " 74,\n",
       " 75,\n",
       " 76,\n",
       " 77,\n",
       " 78,\n",
       " 79,\n",
       " 80,\n",
       " 81,\n",
       " 82,\n",
       " 83,\n",
       " 84,\n",
       " 85,\n",
       " 86,\n",
       " 87,\n",
       " 88,\n",
       " 89,\n",
       " 90,\n",
       " 91,\n",
       " 92,\n",
       " 93,\n",
       " 94,\n",
       " 95,\n",
       " 96,\n",
       " 97,\n",
       " 98,\n",
       " 99]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(s.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.9265142 , 0.33241964, 0.10981713, 0.33386138, 0.55944698,\n",
       "       0.45851811, 0.9735605 , 0.61157124, 0.50429009, 0.44705075,\n",
       "       0.5390561 , 0.4514962 , 0.29768582, 0.71224308, 0.94038729,\n",
       "       0.01984962, 0.81643989, 0.80722717, 0.99664057, 0.28640714,\n",
       "       0.99091849, 0.8572804 , 0.31129551, 0.41555793, 0.15392392,\n",
       "       0.06682554, 0.65105341, 0.63313276, 0.87916164, 0.18716589,\n",
       "       0.59066625, 0.85594405, 0.94284962, 0.85666283, 0.26379451,\n",
       "       0.62626225, 0.43220826, 0.46681309, 0.72752949, 0.40233313,\n",
       "       0.43346754, 0.28099661, 0.13224313, 0.04720902, 0.05949737,\n",
       "       0.16871828, 0.14963836, 0.90665397, 0.23026855, 0.57016545,\n",
       "       0.20005496, 0.36194639, 0.23643252, 0.79020001, 0.98790445,\n",
       "       0.32872495, 0.44483239, 0.79144555, 0.17946678, 0.34617632,\n",
       "       0.96825739, 0.32274289, 0.30380846, 0.02509437, 0.58754865,\n",
       "       0.51548366, 0.23509294, 0.57791476, 0.0637667 , 0.23129279,\n",
       "       0.02515153, 0.93475642, 0.83315453, 0.56381529, 0.83680902,\n",
       "       0.27052477, 0.00670899, 0.62258304, 0.79087171, 0.02814277,\n",
       "       0.30519572, 0.96598927, 0.96058485, 0.86871964, 0.80517103,\n",
       "       0.33692947, 0.29811859, 0.8202267 , 0.46853179, 0.27686041,\n",
       "       0.53152752, 0.27910685, 0.63459904, 0.97045528, 0.79270111,\n",
       "       0.89646492, 0.27299405, 0.65082818, 0.18294591, 0.95177362])"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.values"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `head()` and `tail()` methods allows for looking at the begininning and end of a series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.056150\n",
       "1    0.950886\n",
       "2    0.549265\n",
       "3    0.774797\n",
       "4    0.866951\n",
       "dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "95    0.884579\n",
       "96    0.426309\n",
       "97    0.746223\n",
       "98    0.430681\n",
       "99    0.373570\n",
       "dtype: float64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.tail()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `value_counts()` method returns a count of distinct values within a series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.771811    1\n",
       "0.114470    1\n",
       "0.698512    1\n",
       "0.603252    1\n",
       "0.760992    1\n",
       "           ..\n",
       "0.951077    1\n",
       "0.773665    1\n",
       "0.401493    1\n",
       "0.237970    1\n",
       "0.527201    1\n",
       "Name: count, Length: 100, dtype: int64"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.value_counts()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Is there any number in `s` that occurs twice?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a `Series` can be easily cast into a list\n",
    "\n",
    "list(s.value_counts()).count(1)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another way of verifying this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.is_unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.03505657992551481"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9927444641218586"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.498802863531209"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.5035268320708364"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.median()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Datetime series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "from random import randint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "# let's generate a list of random dates\n",
    "# in the range 1900-1950\n",
    "\n",
    "dates = [\n",
    "    pd.Timestamp(\n",
    "        year,\n",
    "        randint(1, 12),\n",
    "        randint(1, 28) # try replacing with 31 and see what happens\n",
    "    )\n",
    "    for year in range(1900,1950)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "s1 = pd.Series(dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1900-09-01\n",
       "1    1901-10-08\n",
       "2    1902-04-14\n",
       "3    1903-01-03\n",
       "4    1904-06-09\n",
       "5    1905-05-22\n",
       "6    1906-07-27\n",
       "7    1907-09-18\n",
       "8    1908-03-03\n",
       "9    1909-12-01\n",
       "10   1910-06-03\n",
       "11   1911-12-01\n",
       "12   1912-10-04\n",
       "13   1913-11-16\n",
       "14   1914-08-16\n",
       "15   1915-01-18\n",
       "16   1916-11-23\n",
       "17   1917-04-21\n",
       "18   1918-12-20\n",
       "19   1919-09-12\n",
       "20   1920-09-16\n",
       "21   1921-11-27\n",
       "22   1922-12-02\n",
       "23   1923-08-02\n",
       "24   1924-06-15\n",
       "25   1925-02-16\n",
       "26   1926-03-01\n",
       "27   1927-02-20\n",
       "28   1928-05-17\n",
       "29   1929-12-06\n",
       "30   1930-03-25\n",
       "31   1931-02-06\n",
       "32   1932-12-08\n",
       "33   1933-11-22\n",
       "34   1934-04-17\n",
       "35   1935-05-03\n",
       "36   1936-01-19\n",
       "37   1937-10-21\n",
       "38   1938-04-13\n",
       "39   1939-05-14\n",
       "40   1940-11-21\n",
       "41   1941-07-15\n",
       "42   1942-07-06\n",
       "43   1943-12-11\n",
       "44   1944-03-07\n",
       "45   1945-09-22\n",
       "46   1946-04-09\n",
       "47   1947-04-24\n",
       "48   1948-05-19\n",
       "49   1949-07-08\n",
       "dtype: datetime64[ns]"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas._libs.tslibs.timestamps.Timestamp"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(s1[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Tuesday'"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1[1].day_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(1900, 5, 12)"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(1949, 5, 19)"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('1925-01-09 12:28:48')"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.mean()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `DataFrame`\n",
    "\n",
    "\n",
    "What is a `pandas.DataFrame`? Think of it as an in-memory spreadsheet that you can analyse and manipulate programmatically.\n",
    "\n",
    "A `DataFrame` is a collection of `Series` having the same length and whose indexes are in sync. A *collection* means that each column of a dataframe is a series"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create a toy `DataFrame` by hand. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "dates = [\n",
    "    pd.Timestamp(\n",
    "        year,\n",
    "        randint(1, 12),\n",
    "        randint(1, 28) # try replacing with 31 and see what happens\n",
    "    )\n",
    "    for year in range(1980,1990)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Timestamp('1980-01-14 00:00:00'),\n",
       " Timestamp('1981-09-20 00:00:00'),\n",
       " Timestamp('1982-06-26 00:00:00'),\n",
       " Timestamp('1983-08-23 00:00:00'),\n",
       " Timestamp('1984-08-12 00:00:00'),\n",
       " Timestamp('1985-09-02 00:00:00'),\n",
       " Timestamp('1986-03-13 00:00:00'),\n",
       " Timestamp('1987-01-03 00:00:00'),\n",
       " Timestamp('1988-07-08 00:00:00'),\n",
       " Timestamp('1989-05-04 00:00:00')]"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "counts = [\n",
    "    randint(0, 10000)\n",
    "    for i in range(0, 10)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "event_types = [\"fire\", \"flood\", \"car_crash\", \"plane_crash\"]\n",
    "events = [\n",
    "    np.random.choice(event_types)\n",
    "    for i in range(0, 10)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(events) == len(counts) == len(dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "toy_df = pd.DataFrame({\n",
    "    \"date\": dates,\n",
    "    \"count\": counts,\n",
    "    \"event\": events\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982-06-26</td>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983-08-23</td>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1984-08-12</td>\n",
       "      <td>4700</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1985-09-02</td>\n",
       "      <td>8833</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1986-03-13</td>\n",
       "      <td>9351</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1987-01-03</td>\n",
       "      <td>7003</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1988-07-08</td>\n",
       "      <td>2845</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1989-05-04</td>\n",
       "      <td>2583</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event\n",
       "0 1980-01-14   1410  plane_crash\n",
       "1 1981-09-20   7401        flood\n",
       "2 1982-06-26   6483        flood\n",
       "3 1983-08-23   4216         fire\n",
       "4 1984-08-12   4700  plane_crash\n",
       "5 1985-09-02   8833        flood\n",
       "6 1986-03-13   9351  plane_crash\n",
       "7 1987-01-03   7003         fire\n",
       "8 1988-07-08   2845  plane_crash\n",
       "9 1989-05-04   2583         fire"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Try out**: what happens if you change the length of either of the two lists? Try e.g. passing 20 dates instead of 10."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "# instead of a dictionary of lists, you can pass\n",
    "# a dictionary of `pandas.Series`. The result is the same.\n",
    "\n",
    "toy_df = pd.DataFrame(\n",
    "    {\n",
    "        \"date\": pd.Series(dates),\n",
    "        \"count\": pd.Series(counts),\n",
    "        \"event\": pd.Series(events)\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982-06-26</td>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983-08-23</td>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1984-08-12</td>\n",
       "      <td>4700</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1985-09-02</td>\n",
       "      <td>8833</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1986-03-13</td>\n",
       "      <td>9351</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1987-01-03</td>\n",
       "      <td>7003</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1988-07-08</td>\n",
       "      <td>2845</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1989-05-04</td>\n",
       "      <td>2583</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event\n",
       "0 1980-01-14   1410  plane_crash\n",
       "1 1981-09-20   7401        flood\n",
       "2 1982-06-26   6483        flood\n",
       "3 1983-08-23   4216         fire\n",
       "4 1984-08-12   4700  plane_crash\n",
       "5 1985-09-02   8833        flood\n",
       "6 1986-03-13   9351  plane_crash\n",
       "7 1987-01-03   7003         fire\n",
       "8 1988-07-08   2845  plane_crash\n",
       "9 1989-05-04   2583         fire"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype         \n",
      "---  ------  --------------  -----         \n",
      " 0   date    10 non-null     datetime64[ns]\n",
      " 1   count   10 non-null     int64         \n",
      " 2   event   10 non-null     object        \n",
      "dtypes: datetime64[ns](1), int64(1), object(1)\n",
      "memory usage: 368.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "toy_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a df is a collection of series\n",
    "# each column is a series\n",
    "\n",
    "type(toy_df.date)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data manipulation in `pandas`"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data types\n",
    "\n",
    "String, datetimes (see above), categorical data.\n",
    "\n",
    "In `pandas`, categories behave very much like string, yet they lead to better performances (faster operations, optimized storage)."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bottom-up approach:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    plane_crash\n",
       "1          flood\n",
       "2          flood\n",
       "3           fire\n",
       "4    plane_crash\n",
       "5          flood\n",
       "6    plane_crash\n",
       "7           fire\n",
       "8    plane_crash\n",
       "9           fire\n",
       "Name: event, dtype: category\n",
       "Categories (3, object): ['fire', 'flood', 'plane_crash']"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# transforms a Series with strings into categories\n",
    "# similar to R factors\n",
    "\n",
    "toy_df.event.astype('category')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Top-down approach:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "# here the list of categories is defined beforehand\n",
    "\n",
    "from pandas.api.types import CategoricalDtype\n",
    "\n",
    "cat_type = CategoricalDtype(\n",
    "    categories=[\"flood\", \"fire\", \"car_crash\", \"earth_quake\", \"plane_crash\"],\n",
    "    ordered=True\n",
    ")\n",
    "\n",
    "toy_df.event = toy_df.event.astype(cat_type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype         \n",
      "---  ------  --------------  -----         \n",
      " 0   date    10 non-null     datetime64[ns]\n",
      " 1   count   10 non-null     int64         \n",
      " 2   event   10 non-null     category      \n",
      "dtypes: category(1), datetime64[ns](1), int64(1)\n",
      "memory usage: 510.0 bytes\n"
     ]
    }
   ],
   "source": [
    "toy_df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question**: what happens if you remove e.g. \"plane_crash\" from the list `categories`? Can you explain why?"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### How are categories represented?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    4\n",
       "1    0\n",
       "2    0\n",
       "3    1\n",
       "4    4\n",
       "5    0\n",
       "6    4\n",
       "7    1\n",
       "8    4\n",
       "9    1\n",
       "dtype: int8"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.event.cat.codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['flood', 'fire', 'car_crash', 'earth_quake', 'plane_crash'], dtype='object')"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.event.cat.categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "toy_df.event = toy_df.event.cat.rename_categories({\"plane_crash\": \"airplane_crash\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>airplane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982-06-26</td>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983-08-23</td>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1984-08-12</td>\n",
       "      <td>4700</td>\n",
       "      <td>airplane_crash</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count           event\n",
       "0 1980-01-14   1410  airplane_crash\n",
       "1 1981-09-20   7401           flood\n",
       "2 1982-06-26   6483           flood\n",
       "3 1983-08-23   4216            fire\n",
       "4 1984-08-12   4700  airplane_crash"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [],
   "source": [
    "toy_df.event = toy_df.event.cat.rename_categories({\"airplane_crash\": \"plane_crash\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982-06-26</td>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983-08-23</td>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1984-08-12</td>\n",
       "      <td>4700</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event\n",
       "0 1980-01-14   1410  plane_crash\n",
       "1 1981-09-20   7401        flood\n",
       "2 1982-06-26   6483        flood\n",
       "3 1983-08-23   4216         fire\n",
       "4 1984-08-12   4700  plane_crash"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessor properties\n",
    "\n",
    "For certain data types (string, datetime), `pandas` provides a number of common methods that can be called on any series containing values of that type. These methods become available as methods of the series itself within a property — called *accessor* — named after the data type:\n",
    "\n",
    "- the `.dt.*` accessor contains methods to operate on `datetime` series\n",
    "- the `str.` accessor contains methods to operate on `str` (string) series.\n",
    "\n",
    "As you will see in a moment, these methods are very convenient when filtering rows of a dataset based on the value of a certain column."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `datetime` accessor"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To work with datetime series `pandas` provide a bunch of useful methods to operate on a series: they can be called from the `.dt` property of a datetime series.\n",
    "\n",
    "They can be used to:\n",
    "- convert from one timezone to another\n",
    "- get the day/day name/month/year information from each date\n",
    "- and much more (see the [documentation]())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   1900-09-01\n",
       "1   1901-10-08\n",
       "2   1902-04-14\n",
       "3   1903-01-03\n",
       "4   1904-06-09\n",
       "dtype: datetime64[ns]"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    5\n",
       "1    1\n",
       "2    0\n",
       "3    5\n",
       "4    3\n",
       "dtype: int32"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.dt.day_of_week.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `str` accessor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = Series([\"One\", \"TWO\", \"tHrEE\"])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Accessors can be used to apply filters to a series by verifying whether a certain condition is verified or not, such is the case with `contains()`. Such methods will output a boolean value (`True` or `False`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.str.contains('o')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1     True\n",
       "2    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.str.contains('O')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Other methods can be used, instead, to manipulate an entire series, e.g. `lower()` and `upper()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      one\n",
       "1      two\n",
       "2    three\n",
       "dtype: object"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.str.lower()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploring a dataframe\n",
    "\n",
    "Exploring a dataframe: `df.head()`, `df.tail()`, `df.info()`."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The method `info()` gives you information about a dataframe:\n",
    "- how much space does it take in memory?\n",
    "- what is the datatype of each column?\n",
    "- how many records are there?\n",
    "- how many `null` values does each column contain (!)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype         \n",
      "---  ------  --------------  -----         \n",
      " 0   date    10 non-null     datetime64[ns]\n",
      " 1   count   10 non-null     int64         \n",
      " 2   event   10 non-null     category      \n",
      "dtypes: category(1), datetime64[ns](1), int64(1)\n",
      "memory usage: 510.0 bytes\n"
     ]
    }
   ],
   "source": [
    "toy_df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, if you need to know only the number of columns and rows you can use the `.shape` property.\n",
    "\n",
    "It returns a tuple with 1) number of rows, 2) number of columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10, 3)"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`head()` prints by first five rows of a dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982-06-26</td>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983-08-23</td>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1984-08-12</td>\n",
       "      <td>4700</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event\n",
       "0 1980-01-14   1410  plane_crash\n",
       "1 1981-09-20   7401        flood\n",
       "2 1982-06-26   6483        flood\n",
       "3 1983-08-23   4216         fire\n",
       "4 1984-08-12   4700  plane_crash"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But the number of lines displayed is a parameter that can be changed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event\n",
       "0 1980-01-14   1410  plane_crash\n",
       "1 1981-09-20   7401        flood"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.head(2)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`tail()` does the opposite, i.e. prints the last n rows in the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1985-09-02</td>\n",
       "      <td>8833</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1986-03-13</td>\n",
       "      <td>9351</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1987-01-03</td>\n",
       "      <td>7003</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1988-07-08</td>\n",
       "      <td>2845</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1989-05-04</td>\n",
       "      <td>2583</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event\n",
       "5 1985-09-02   8833        flood\n",
       "6 1986-03-13   9351  plane_crash\n",
       "7 1987-01-03   7003         fire\n",
       "8 1988-07-08   2845  plane_crash\n",
       "9 1989-05-04   2583         fire"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.tail()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Loading data\n",
    "\n",
    "Dataframe can be created from scratch as we did above, but most often they are created by loading existing data into a dataframe by means of `pandas`' input/oputput methods."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### From JSON\n",
    "\n",
    "Loading data from a JSON file is very similar to creating a `DataFrame` from a `dict`."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is how one would do it in pure Python:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "json_file_path = '../data/bl_books/sample/book_data_sample.json'\n",
    "\n",
    "# JSON data gets read into a dictionary\n",
    "\n",
    "with open(json_file_path, 'r') as jsonfile:\n",
    "    json_data = json.load(jsonfile)\n",
    "    \n",
    "books_df = pd.DataFrame(json_data)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since reading from files is a very common operation in any data analysis workflow, `pandas` provides methods to read from a variety of formats (JSON, CSV, clipboard, etc.)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The block of code above can be replaced by the following one-liner:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/dj/y_4dq5_16mb3rr89w0r1dh4c0000gr/T/ipykernel_3523/3298360895.py:1: FutureWarning: The behavior of 'to_datetime' with 'unit' when parsing strings is deprecated. In a future version, strings will be parsed as datetime strings, matching the behavior without a 'unit'. To retain the old behavior, explicitly cast ints or floats to numeric type before calling to_datetime.\n",
      "  books_df = pd.read_json(json_file_path)\n"
     ]
    }
   ],
   "source": [
    "books_df = pd.read_json(json_file_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "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>datefield</th>\n",
       "      <th>shelfmarks</th>\n",
       "      <th>publisher</th>\n",
       "      <th>title</th>\n",
       "      <th>edition</th>\n",
       "      <th>flickr_url_to_book_images</th>\n",
       "      <th>place</th>\n",
       "      <th>issuance</th>\n",
       "      <th>authors</th>\n",
       "      <th>date</th>\n",
       "      <th>pdf</th>\n",
       "      <th>identifier</th>\n",
       "      <th>corporate</th>\n",
       "      <th>fulltext_filename</th>\n",
       "      <th>imgs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1841</td>\n",
       "      <td>[British Library HMNTS 11601.ddd.2.]</td>\n",
       "      <td>Privately printed</td>\n",
       "      <td>[The Poetical Aviary, with a bird's-eye view o...</td>\n",
       "      <td></td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "      <td>Calcutta</td>\n",
       "      <td>monographic</td>\n",
       "      <td>{'creator': ['A. A.']}</td>\n",
       "      <td>1841</td>\n",
       "      <td>{'1': 'lsidyv35c55757'}</td>\n",
       "      <td>000000196</td>\n",
       "      <td>{}</td>\n",
       "      <td>sample/full_texts/000000196_01_text.json</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1888</td>\n",
       "      <td>[British Library HMNTS 9025.cc.14.]</td>\n",
       "      <td>Rivingtons</td>\n",
       "      <td>[A History of Greece. Part I. From the earlies...</td>\n",
       "      <td></td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "      <td>London</td>\n",
       "      <td>monographic</td>\n",
       "      <td>{'creator': ['Abbott, Evelyn']}</td>\n",
       "      <td>1888</td>\n",
       "      <td>{'1': 'lsidyv376da437'}</td>\n",
       "      <td>000004047</td>\n",
       "      <td>{}</td>\n",
       "      <td>sample/full_texts/000004047_01_text.json</td>\n",
       "      <td>{'0': {'000257': ['11104648374']}}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  datefield                            shelfmarks          publisher  \\\n",
       "0      1841  [British Library HMNTS 11601.ddd.2.]  Privately printed   \n",
       "1      1888   [British Library HMNTS 9025.cc.14.]         Rivingtons   \n",
       "\n",
       "                                               title edition  \\\n",
       "0  [The Poetical Aviary, with a bird's-eye view o...           \n",
       "1  [A History of Greece. Part I. From the earlies...           \n",
       "\n",
       "                           flickr_url_to_book_images     place     issuance  \\\n",
       "0  http://www.flickr.com/photos/britishlibrary/ta...  Calcutta  monographic   \n",
       "1  http://www.flickr.com/photos/britishlibrary/ta...    London  monographic   \n",
       "\n",
       "                           authors  date                      pdf identifier  \\\n",
       "0           {'creator': ['A. A.']}  1841  {'1': 'lsidyv35c55757'}  000000196   \n",
       "1  {'creator': ['Abbott, Evelyn']}  1888  {'1': 'lsidyv376da437'}  000004047   \n",
       "\n",
       "  corporate                         fulltext_filename  \\\n",
       "0        {}  sample/full_texts/000000196_01_text.json   \n",
       "1        {}  sample/full_texts/000004047_01_text.json   \n",
       "\n",
       "                                 imgs  \n",
       "0                                 NaN  \n",
       "1  {'0': {'000257': ['11104648374']}}  "
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 452 entries, 0 to 451\n",
      "Data columns (total 15 columns):\n",
      " #   Column                     Non-Null Count  Dtype \n",
      "---  ------                     --------------  ----- \n",
      " 0   datefield                  452 non-null    object\n",
      " 1   shelfmarks                 452 non-null    object\n",
      " 2   publisher                  452 non-null    object\n",
      " 3   title                      452 non-null    object\n",
      " 4   edition                    452 non-null    object\n",
      " 5   flickr_url_to_book_images  452 non-null    object\n",
      " 6   place                      452 non-null    object\n",
      " 7   issuance                   452 non-null    object\n",
      " 8   authors                    452 non-null    object\n",
      " 9   date                       452 non-null    object\n",
      " 10  pdf                        452 non-null    object\n",
      " 11  identifier                 452 non-null    object\n",
      " 12  corporate                  452 non-null    object\n",
      " 13  fulltext_filename          452 non-null    object\n",
      " 14  imgs                       280 non-null    object\n",
      "dtypes: object(15)\n",
      "memory usage: 53.1+ KB\n"
     ]
    }
   ],
   "source": [
    "books_df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**NB**: note the number of missing values for the `books_df.imgs` (n=172)."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### From CSV\n",
    "\n",
    "Similarly to `pandas.read_json()`, `pandas.read_csv()` is there to make your life easier when it comes to loading CSV data into a dataframe (and that happens very often!).\n",
    "\n",
    "Let's import one of the CSV files from the \"Venice Apprenticeship\" dataset (`../data/apprenticeship_venice/`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_file_path = '../data/apprenticeship_venice/professions_data.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "ename": "ParserError",
     "evalue": "Error tokenizing data. C error: Expected 7 fields in line 36, saw 8\n",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mParserError\u001b[0m                               Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[181], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m garzoni_df \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39;49mread_csv(csv_file_path)\n",
      "File \u001b[0;32m~/Desktop/ADA-DHOxSS/.venv/lib/python3.10/site-packages/pandas/io/parsers/readers.py:912\u001b[0m, in \u001b[0;36mread_csv\u001b[0;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, date_format, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options, dtype_backend)\u001b[0m\n\u001b[1;32m    899\u001b[0m kwds_defaults \u001b[39m=\u001b[39m _refine_defaults_read(\n\u001b[1;32m    900\u001b[0m     dialect,\n\u001b[1;32m    901\u001b[0m     delimiter,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    908\u001b[0m     dtype_backend\u001b[39m=\u001b[39mdtype_backend,\n\u001b[1;32m    909\u001b[0m )\n\u001b[1;32m    910\u001b[0m kwds\u001b[39m.\u001b[39mupdate(kwds_defaults)\n\u001b[0;32m--> 912\u001b[0m \u001b[39mreturn\u001b[39;00m _read(filepath_or_buffer, kwds)\n",
      "File \u001b[0;32m~/Desktop/ADA-DHOxSS/.venv/lib/python3.10/site-packages/pandas/io/parsers/readers.py:583\u001b[0m, in \u001b[0;36m_read\u001b[0;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[1;32m    580\u001b[0m     \u001b[39mreturn\u001b[39;00m parser\n\u001b[1;32m    582\u001b[0m \u001b[39mwith\u001b[39;00m parser:\n\u001b[0;32m--> 583\u001b[0m     \u001b[39mreturn\u001b[39;00m parser\u001b[39m.\u001b[39;49mread(nrows)\n",
      "File \u001b[0;32m~/Desktop/ADA-DHOxSS/.venv/lib/python3.10/site-packages/pandas/io/parsers/readers.py:1704\u001b[0m, in \u001b[0;36mTextFileReader.read\u001b[0;34m(self, nrows)\u001b[0m\n\u001b[1;32m   1697\u001b[0m nrows \u001b[39m=\u001b[39m validate_integer(\u001b[39m\"\u001b[39m\u001b[39mnrows\u001b[39m\u001b[39m\"\u001b[39m, nrows)\n\u001b[1;32m   1698\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m   1699\u001b[0m     \u001b[39m# error: \"ParserBase\" has no attribute \"read\"\u001b[39;00m\n\u001b[1;32m   1700\u001b[0m     (\n\u001b[1;32m   1701\u001b[0m         index,\n\u001b[1;32m   1702\u001b[0m         columns,\n\u001b[1;32m   1703\u001b[0m         col_dict,\n\u001b[0;32m-> 1704\u001b[0m     ) \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_engine\u001b[39m.\u001b[39;49mread(  \u001b[39m# type: ignore[attr-defined]\u001b[39;49;00m\n\u001b[1;32m   1705\u001b[0m         nrows\n\u001b[1;32m   1706\u001b[0m     )\n\u001b[1;32m   1707\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mException\u001b[39;00m:\n\u001b[1;32m   1708\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mclose()\n",
      "File \u001b[0;32m~/Desktop/ADA-DHOxSS/.venv/lib/python3.10/site-packages/pandas/io/parsers/c_parser_wrapper.py:234\u001b[0m, in \u001b[0;36mCParserWrapper.read\u001b[0;34m(self, nrows)\u001b[0m\n\u001b[1;32m    232\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m    233\u001b[0m     \u001b[39mif\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mlow_memory:\n\u001b[0;32m--> 234\u001b[0m         chunks \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_reader\u001b[39m.\u001b[39;49mread_low_memory(nrows)\n\u001b[1;32m    235\u001b[0m         \u001b[39m# destructive to chunks\u001b[39;00m\n\u001b[1;32m    236\u001b[0m         data \u001b[39m=\u001b[39m _concatenate_chunks(chunks)\n",
      "File \u001b[0;32m~/Desktop/ADA-DHOxSS/.venv/lib/python3.10/site-packages/pandas/_libs/parsers.pyx:812\u001b[0m, in \u001b[0;36mpandas._libs.parsers.TextReader.read_low_memory\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32m~/Desktop/ADA-DHOxSS/.venv/lib/python3.10/site-packages/pandas/_libs/parsers.pyx:873\u001b[0m, in \u001b[0;36mpandas._libs.parsers.TextReader._read_rows\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32m~/Desktop/ADA-DHOxSS/.venv/lib/python3.10/site-packages/pandas/_libs/parsers.pyx:848\u001b[0m, in \u001b[0;36mpandas._libs.parsers.TextReader._tokenize_rows\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32m~/Desktop/ADA-DHOxSS/.venv/lib/python3.10/site-packages/pandas/_libs/parsers.pyx:859\u001b[0m, in \u001b[0;36mpandas._libs.parsers.TextReader._check_tokenize_status\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32m~/Desktop/ADA-DHOxSS/.venv/lib/python3.10/site-packages/pandas/_libs/parsers.pyx:2025\u001b[0m, in \u001b[0;36mpandas._libs.parsers.raise_parser_error\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mParserError\u001b[0m: Error tokenizing data. C error: Expected 7 fields in line 36, saw 8\n"
     ]
    }
   ],
   "source": [
    "garzoni_df = pd.read_csv(csv_file_path)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why it did not work??\n",
    "\n",
    "Let's have a look at the file first..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "page_title;register;annual_salary;a_profession;profession_code_strict;profession_code_gen;profession_cat;corporation;keep_profession_a;complete_profession_a;enrolmentY;enrolmentM;startY;startM;length;has_fled;m_profession;m_profession_code_strict;m_profession_code_gen;m_profession_cat;m_corporation;keep_profession_m;complete_profession_m;m_gender;m_name;m_surname;m_patronimic;m_atelier;m_coords;a_name;a_age;a_gender;a_geo_origins;a_geo_origins_std;a_coords;a_quondam;accommodation_master;personal_care_master;clothes_master;generic_expenses_master;salary_in_kind_master;pledge_goods_master;pledge_money_master;salary_master;female_guarantor;period_cat;incremental_salary\n",
      "Carlo Della sosta (Orese) 1592-08-03;asv, giustizia vecchia, accordi dei garzoni, 114, 155;NA;orese;orese;orefice;orefice;Oresi;1;1;1592;08;1592;08;3;0;orese;orese;orefice;orefice;Oresi;1;1;1;Zuan Battista;Amigoni;;;0, 0;Carlo Della sosta;17;1;;;0, 0;1;0;1;1;1;0;0;0;0;0;NA;0\n"
     ]
    }
   ],
   "source": [
    "!head -n 2 ../data/apprenticeship_venice/professions_data.csv"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More than a comma-separated value, it looks like semicolon-separated values..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "# the `sep` input parameter\n",
    "# allows us to specify which character/symbol is used\n",
    "# to separate column values\n",
    "\n",
    "garzoni_df = pd.read_csv(\n",
    "    csv_file_path,\n",
    "    sep=';'\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**NB**: There may be invalid lines in the data you are reading in. `read_csv()` puts you in full control of that: by setting the param `on_bad_lines` to `warn` we tell `pandas` to warn us bout bad lines and skip them (see [docs](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html)). If no warning is return, then your csv is well-formed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "garzoni_df = pd.read_csv(\n",
    "    csv_file_path,\n",
    "    sep=';',\n",
    "    on_bad_lines=\"warn\",\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "    <b>More format readers</b>\n",
    "    <p></p>\n",
    "    Pandas supports more formats than just CSV and JSON. See the library's <a href=\"https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html\">documentation</a> for the full list of supported formats.\n",
    "</div>"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Working with columns"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exploring values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "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>page_title</th>\n",
       "      <th>register</th>\n",
       "      <th>annual_salary</th>\n",
       "      <th>a_profession</th>\n",
       "      <th>profession_code_strict</th>\n",
       "      <th>profession_code_gen</th>\n",
       "      <th>profession_cat</th>\n",
       "      <th>corporation</th>\n",
       "      <th>keep_profession_a</th>\n",
       "      <th>complete_profession_a</th>\n",
       "      <th>...</th>\n",
       "      <th>personal_care_master</th>\n",
       "      <th>clothes_master</th>\n",
       "      <th>generic_expenses_master</th>\n",
       "      <th>salary_in_kind_master</th>\n",
       "      <th>pledge_goods_master</th>\n",
       "      <th>pledge_money_master</th>\n",
       "      <th>salary_master</th>\n",
       "      <th>female_guarantor</th>\n",
       "      <th>period_cat</th>\n",
       "      <th>incremental_salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Carlo Della sosta (Orese) 1592-08-03</td>\n",
       "      <td>asv, giustizia vecchia, accordi dei garzoni, 1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>orese</td>\n",
       "      <td>orese</td>\n",
       "      <td>orefice</td>\n",
       "      <td>orefice</td>\n",
       "      <td>Oresi</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Antonio quondam Andrea (squerariol) 1583-01-09</td>\n",
       "      <td>asv, giustizia vecchia, accordi dei garzoni, 1...</td>\n",
       "      <td>12.5</td>\n",
       "      <td>squerariol</td>\n",
       "      <td>squerariol</td>\n",
       "      <td>lavori allo squero</td>\n",
       "      <td>lavori allo squero</td>\n",
       "      <td>Squerarioli</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Cristofollo di Zuane (batioro in carta) 1591-0...</td>\n",
       "      <td>asv, giustizia vecchia, accordi dei garzoni, 1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>batioro</td>\n",
       "      <td>batioro</td>\n",
       "      <td>battioro</td>\n",
       "      <td>fabbricatore di foglie/fili/cordelle d'oro o a...</td>\n",
       "      <td>Battioro</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Illeggibile (marzer) 1584-06-21</td>\n",
       "      <td>asv, giustizia vecchia, accordi dei garzoni, 1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>marzer</td>\n",
       "      <td>marzer</td>\n",
       "      <td>marzer</td>\n",
       "      <td>merciaio</td>\n",
       "      <td>Merzeri</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Domenico Morebetti (spechier) 1664-09-13</td>\n",
       "      <td>asv, giustizia vecchia, accordi dei garzoni, 1...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>marzer</td>\n",
       "      <td>marzer</td>\n",
       "      <td>marzer</td>\n",
       "      <td>merciaio</td>\n",
       "      <td>Merzeri</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          page_title  \\\n",
       "0               Carlo Della sosta (Orese) 1592-08-03   \n",
       "1     Antonio quondam Andrea (squerariol) 1583-01-09   \n",
       "2  Cristofollo di Zuane (batioro in carta) 1591-0...   \n",
       "3                    Illeggibile (marzer) 1584-06-21   \n",
       "4           Domenico Morebetti (spechier) 1664-09-13   \n",
       "\n",
       "                                            register  annual_salary  \\\n",
       "0  asv, giustizia vecchia, accordi dei garzoni, 1...            NaN   \n",
       "1  asv, giustizia vecchia, accordi dei garzoni, 1...           12.5   \n",
       "2  asv, giustizia vecchia, accordi dei garzoni, 1...            NaN   \n",
       "3  asv, giustizia vecchia, accordi dei garzoni, 1...            NaN   \n",
       "4  asv, giustizia vecchia, accordi dei garzoni, 1...            7.0   \n",
       "\n",
       "  a_profession profession_code_strict profession_code_gen  \\\n",
       "0        orese                  orese             orefice   \n",
       "1   squerariol             squerariol  lavori allo squero   \n",
       "2      batioro                batioro            battioro   \n",
       "3       marzer                 marzer              marzer   \n",
       "4       marzer                 marzer              marzer   \n",
       "\n",
       "                                      profession_cat  corporation  \\\n",
       "0                                            orefice        Oresi   \n",
       "1                                 lavori allo squero  Squerarioli   \n",
       "2  fabbricatore di foglie/fili/cordelle d'oro o a...     Battioro   \n",
       "3                                           merciaio      Merzeri   \n",
       "4                                           merciaio      Merzeri   \n",
       "\n",
       "   keep_profession_a  complete_profession_a  ...  personal_care_master  \\\n",
       "0                  1                      1  ...                     1   \n",
       "1                  1                      1  ...                     0   \n",
       "2                  1                      1  ...                     0   \n",
       "3                  1                      1  ...                     0   \n",
       "4                  1                      1  ...                     0   \n",
       "\n",
       "   clothes_master  generic_expenses_master  salary_in_kind_master  \\\n",
       "0               1                        1                      0   \n",
       "1               0                        1                      0   \n",
       "2               0                        0                      0   \n",
       "3               0                        0                      0   \n",
       "4               0                        1                      0   \n",
       "\n",
       "   pledge_goods_master  pledge_money_master salary_master female_guarantor  \\\n",
       "0                    0                    0             0                0   \n",
       "1                    0                    0             1                0   \n",
       "2                    0                    0             0                0   \n",
       "3                    0                    0             0                0   \n",
       "4                    0                    0             1                0   \n",
       "\n",
       "  period_cat incremental_salary  \n",
       "0        NaN                  0  \n",
       "1        1.0                  0  \n",
       "2        NaN                  0  \n",
       "3        NaN                  0  \n",
       "4        1.0                  0  \n",
       "\n",
       "[5 rows x 47 columns]"
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "garzoni_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a_profession\n",
       "spechier                       979\n",
       "orese                          542\n",
       "marzer                         506\n",
       "marangon                       483\n",
       "tagiapiera                     338\n",
       "                              ... \n",
       "arte del saltar                  1\n",
       "tentor da fustagni et tella      1\n",
       "dalla dalla malvasia             1\n",
       "biavariol , salumier             1\n",
       "vender camisolle e calze         1\n",
       "Name: count, Length: 826, dtype: int64"
      ]
     },
     "execution_count": 193,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "garzoni_df.a_profession.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "annual_salary\n",
       "4.000000     824\n",
       "5.000000     750\n",
       "3.000000     597\n",
       "6.000000     576\n",
       "2.000000     536\n",
       "            ... \n",
       "1.071429       1\n",
       "8.727273       1\n",
       "18.792453      1\n",
       "7.500000       1\n",
       "6.769231       1\n",
       "Name: count, Length: 434, dtype: int64"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "garzoni_df.annual_salary.value_counts()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mind that `.value_counts` automatically disregards `NaN`s, unless you explicitly opt to keep them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "annual_salary\n",
       "NaN         1783\n",
       "4.000000     824\n",
       "5.000000     750\n",
       "3.000000     597\n",
       "6.000000     576\n",
       "            ... \n",
       "5.894737       1\n",
       "4.695652       1\n",
       "2.307692       1\n",
       "8.250000       1\n",
       "6.769231       1\n",
       "Name: count, Length: 435, dtype: int64"
      ]
     },
     "execution_count": 195,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "garzoni_df.annual_salary.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9653, 47)"
      ]
     },
     "execution_count": 196,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "garzoni_df.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Missing values"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The series' methods `isna()` and `notna()` can be used as a way of filtering rows containing missing values (`NaN`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1783, 47)"
      ]
     },
     "execution_count": 197,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "garzoni_df[garzoni_df.annual_salary.isna()].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7870, 47)"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "garzoni_df[garzoni_df.annual_salary.notna()].shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The method DataFrame's method `dropna()` is used to remove rows containing missing values in any of the columns or on a selection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(46, 47)"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "garzoni_df.dropna().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7870, 47)"
      ]
     },
     "execution_count": 200,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "garzoni_df.dropna(subset=['annual_salary']).shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Casting\n",
    "\n",
    "We call *casting* the operation of changing the act of changing the data type of one or more variables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we define a string with value \"10\"\n",
    "number_str = \"10\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we change its type from string (`str`)\n",
    "# to integeer (`int`). This is call casting\n",
    "\n",
    "number_int = int(number_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the types of the two variable are different indeed\n",
    "\n",
    "type(number_str) == type(number_int)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pandas` objects like `Series` and `DataFrame` provide the method `astype()` to apply casting on their contents."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "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>page_title</th>\n",
       "      <th>register</th>\n",
       "      <th>annual_salary</th>\n",
       "      <th>a_profession</th>\n",
       "      <th>profession_code_strict</th>\n",
       "      <th>profession_code_gen</th>\n",
       "      <th>profession_cat</th>\n",
       "      <th>corporation</th>\n",
       "      <th>keep_profession_a</th>\n",
       "      <th>complete_profession_a</th>\n",
       "      <th>...</th>\n",
       "      <th>personal_care_master</th>\n",
       "      <th>clothes_master</th>\n",
       "      <th>generic_expenses_master</th>\n",
       "      <th>salary_in_kind_master</th>\n",
       "      <th>pledge_goods_master</th>\n",
       "      <th>pledge_money_master</th>\n",
       "      <th>salary_master</th>\n",
       "      <th>female_guarantor</th>\n",
       "      <th>period_cat</th>\n",
       "      <th>incremental_salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Carlo Della sosta (Orese) 1592-08-03</td>\n",
       "      <td>asv, giustizia vecchia, accordi dei garzoni, 1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>orese</td>\n",
       "      <td>orese</td>\n",
       "      <td>orefice</td>\n",
       "      <td>orefice</td>\n",
       "      <td>Oresi</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Antonio quondam Andrea (squerariol) 1583-01-09</td>\n",
       "      <td>asv, giustizia vecchia, accordi dei garzoni, 1...</td>\n",
       "      <td>12.5</td>\n",
       "      <td>squerariol</td>\n",
       "      <td>squerariol</td>\n",
       "      <td>lavori allo squero</td>\n",
       "      <td>lavori allo squero</td>\n",
       "      <td>Squerarioli</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Cristofollo di Zuane (batioro in carta) 1591-0...</td>\n",
       "      <td>asv, giustizia vecchia, accordi dei garzoni, 1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>batioro</td>\n",
       "      <td>batioro</td>\n",
       "      <td>battioro</td>\n",
       "      <td>fabbricatore di foglie/fili/cordelle d'oro o a...</td>\n",
       "      <td>Battioro</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          page_title  \\\n",
       "0               Carlo Della sosta (Orese) 1592-08-03   \n",
       "1     Antonio quondam Andrea (squerariol) 1583-01-09   \n",
       "2  Cristofollo di Zuane (batioro in carta) 1591-0...   \n",
       "\n",
       "                                            register  annual_salary  \\\n",
       "0  asv, giustizia vecchia, accordi dei garzoni, 1...            NaN   \n",
       "1  asv, giustizia vecchia, accordi dei garzoni, 1...           12.5   \n",
       "2  asv, giustizia vecchia, accordi dei garzoni, 1...            NaN   \n",
       "\n",
       "  a_profession profession_code_strict profession_code_gen  \\\n",
       "0        orese                  orese             orefice   \n",
       "1   squerariol             squerariol  lavori allo squero   \n",
       "2      batioro                batioro            battioro   \n",
       "\n",
       "                                      profession_cat  corporation  \\\n",
       "0                                            orefice        Oresi   \n",
       "1                                 lavori allo squero  Squerarioli   \n",
       "2  fabbricatore di foglie/fili/cordelle d'oro o a...     Battioro   \n",
       "\n",
       "   keep_profession_a  complete_profession_a  ...  personal_care_master  \\\n",
       "0                  1                      1  ...                     1   \n",
       "1                  1                      1  ...                     0   \n",
       "2                  1                      1  ...                     0   \n",
       "\n",
       "   clothes_master  generic_expenses_master  salary_in_kind_master  \\\n",
       "0               1                        1                      0   \n",
       "1               0                        1                      0   \n",
       "2               0                        0                      0   \n",
       "\n",
       "   pledge_goods_master  pledge_money_master salary_master female_guarantor  \\\n",
       "0                    0                    0             0                0   \n",
       "1                    0                    0             1                0   \n",
       "2                    0                    0             0                0   \n",
       "\n",
       "  period_cat incremental_salary  \n",
       "0        NaN                  0  \n",
       "1        1.0                  0  \n",
       "2        NaN                  0  \n",
       "\n",
       "[3 rows x 47 columns]"
      ]
     },
     "execution_count": 204,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "garzoni_df.head(3)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To cast the type of the `profession_cat` column, we can use directly the `astype()` method of the Series: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {},
   "outputs": [],
   "source": [
    "professions = garzoni_df.profession_cat.astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([' . rilegatore di libri', 'acquaroli', 'acquavite',\n",
       "       'acquavite . arrotino', 'acquavite . venditore di crusca', 'archibugi',\n",
       "       'archibugi . ', 'arginatura canali', 'arrotino', 'ballerino',\n",
       "       ...\n",
       "       'venditori di profumi . pellicciaio', 'venditori di tele',\n",
       "       'venditori di tele . cotone .  . fabbricatori di fustagni . merciaio . materassaio . rigattiere',\n",
       "       'venditori di tele . fabbricatori di laccioli . merciaio',\n",
       "       'venditori di tele . materassaio', 'venditori di tele . merciaio',\n",
       "       'venditori di tele . merciaio . cotone .  . fabbricatori di fustagni',\n",
       "       'venditori di tele . merciaio . fabbricatori di laccioli', 'vetraio',\n",
       "       'vetraio . trasportatori di sabbia'],\n",
       "      dtype='object', length=360)"
      ]
     },
     "execution_count": 206,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "professions.cat.categories"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another way of doing this while operating on the dataframe is to use the dataframe's `astype()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas.api.types import CategoricalDtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {},
   "outputs": [],
   "source": [
    "profession_cat_type = CategoricalDtype(\n",
    "    categories=garzoni_df.profession_cat[garzoni_df.profession_cat.notnull()].unique()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "metadata": {},
   "outputs": [],
   "source": [
    "garzoni_df = garzoni_df.astype(\n",
    "    {\n",
    "        \"profession_cat\": profession_cat_type\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "profession_cat\n",
       "specchiaio                                                                                        1033\n",
       "falegname                                                                                          748\n",
       "orefice                                                                                            640\n",
       "merciaio                                                                                           613\n",
       "fabbricatore di foglie/fili/cordelle d'oro o argento                                               380\n",
       "                                                                                                  ... \n",
       "venditori di tele . cotone .  . fabbricatori di fustagni . merciaio . materassaio . rigattiere       1\n",
       "venditori di frutta . fabbricazione corone                                                           1\n",
       "calafato                                                                                             1\n",
       "fabbricatore di sapone . venditore di generi in salamoia                                             1\n",
       "bottonaio                                                                                            1\n",
       "Name: count, Length: 360, dtype: int64"
      ]
     },
     "execution_count": 213,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "garzoni_df.profession_cat.value_counts()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Adding columns"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's go back to our toy dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982-06-26</td>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983-08-23</td>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1984-08-12</td>\n",
       "      <td>4700</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event\n",
       "0 1980-01-14   1410  plane_crash\n",
       "1 1981-09-20   7401        flood\n",
       "2 1982-06-26   6483        flood\n",
       "3 1983-08-23   4216         fire\n",
       "4 1984-08-12   4700  plane_crash"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the column selector with the name of a column that does not exist yet will add the effect of setting the values of all rows in that column to the value specified."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {},
   "outputs": [],
   "source": [
    "toy_df['country'] = \"UK\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982-06-26</td>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event country\n",
       "0 1980-01-14   1410  plane_crash     USA\n",
       "1 1981-09-20   7401        flood     USA\n",
       "2 1982-06-26   6483        flood     USA"
      ]
     },
     "execution_count": 225,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.head(3)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But if the column already exists, its value is reset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [],
   "source": [
    "toy_df['country'] = \"USA\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982-06-26</td>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event country\n",
       "0 1980-01-14   1410  plane_crash     USA\n",
       "1 1981-09-20   7401        flood     USA\n",
       "2 1982-06-26   6483        flood     USA"
      ]
     },
     "execution_count": 218,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.head(3)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Removing columns"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The double square bracket notation ``[[...]]`` returns a dataframe having only the columns specified inside the inner brackets."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This said, removing a column is done by unselecting it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "metadata": {},
   "outputs": [],
   "source": [
    "# here we removed the column country \n",
    "\n",
    "toy_df2 = toy_df[['date', 'count', 'event']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982-06-26</td>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983-08-23</td>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1984-08-12</td>\n",
       "      <td>4700</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event\n",
       "0 1980-01-14   1410  plane_crash\n",
       "1 1981-09-20   7401        flood\n",
       "2 1982-06-26   6483        flood\n",
       "3 1983-08-23   4216         fire\n",
       "4 1984-08-12   4700  plane_crash"
      ]
     },
     "execution_count": 223,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# it worked!\n",
    "\n",
    "toy_df2.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or using the `.drop` method like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982-06-26</td>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983-08-23</td>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1984-08-12</td>\n",
       "      <td>4700</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1985-09-02</td>\n",
       "      <td>8833</td>\n",
       "      <td>flood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1986-03-13</td>\n",
       "      <td>9351</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1987-01-03</td>\n",
       "      <td>7003</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1988-07-08</td>\n",
       "      <td>2845</td>\n",
       "      <td>plane_crash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1989-05-04</td>\n",
       "      <td>2583</td>\n",
       "      <td>fire</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event\n",
       "0 1980-01-14   1410  plane_crash\n",
       "1 1981-09-20   7401        flood\n",
       "2 1982-06-26   6483        flood\n",
       "3 1983-08-23   4216         fire\n",
       "4 1984-08-12   4700  plane_crash\n",
       "5 1985-09-02   8833        flood\n",
       "6 1986-03-13   9351  plane_crash\n",
       "7 1987-01-03   7003         fire\n",
       "8 1988-07-08   2845  plane_crash\n",
       "9 1989-05-04   2583         fire"
      ]
     },
     "execution_count": 227,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.drop(columns=['country'])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Setting a column as index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "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>count</th>\n",
       "      <th>event</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1980-01-14</th>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1981-09-20</th>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1982-06-26</th>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1983-08-23</th>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1984-08-12</th>\n",
       "      <td>4700</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1985-09-02</th>\n",
       "      <td>8833</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1986-03-13</th>\n",
       "      <td>9351</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987-01-03</th>\n",
       "      <td>7003</td>\n",
       "      <td>fire</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1988-07-08</th>\n",
       "      <td>2845</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1989-05-04</th>\n",
       "      <td>2583</td>\n",
       "      <td>fire</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            count        event country\n",
       "date                                  \n",
       "1980-01-14   1410  plane_crash     USA\n",
       "1981-09-20   7401        flood     USA\n",
       "1982-06-26   6483        flood     USA\n",
       "1983-08-23   4216         fire     USA\n",
       "1984-08-12   4700  plane_crash     USA\n",
       "1985-09-02   8833        flood     USA\n",
       "1986-03-13   9351  plane_crash     USA\n",
       "1987-01-03   7003         fire     USA\n",
       "1988-07-08   2845  plane_crash     USA\n",
       "1989-05-04   2583         fire     USA"
      ]
     },
     "execution_count": 228,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.set_index('date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "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>date</th>\n",
       "      <th>count</th>\n",
       "      <th>event</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980-01-14</td>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-09-20</td>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982-06-26</td>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  count        event country\n",
       "0 1980-01-14   1410  plane_crash     USA\n",
       "1 1981-09-20   7401        flood     USA\n",
       "2 1982-06-26   6483        flood     USA"
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [],
   "source": [
    "toy_df.set_index('date', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "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>count</th>\n",
       "      <th>event</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1980-01-14</th>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1981-09-20</th>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1982-06-26</th>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            count        event country\n",
       "date                                  \n",
       "1980-01-14   1410  plane_crash     USA\n",
       "1981-09-20   7401        flood     USA\n",
       "1982-06-26   6483        flood     USA"
      ]
     },
     "execution_count": 231,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.head(3)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Q**: can you explain the effect of the `inplace` parameter by looking at the cells above?"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Accessing data\n",
    "\n",
    " .loc, .iloc, slicing, iteration over rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "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>count</th>\n",
       "      <th>event</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1980-01-14</th>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1981-09-20</th>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1982-06-26</th>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            count        event country\n",
       "date                                  \n",
       "1980-01-14   1410  plane_crash     USA\n",
       "1981-09-20   7401        flood     USA\n",
       "1982-06-26   6483        flood     USA"
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.head(3)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Label-based indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "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>count</th>\n",
       "      <th>event</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1980-01-14</th>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1981-09-20</th>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            count        event country\n",
       "date                                  \n",
       "1980-01-14   1410  plane_crash     USA\n",
       "1981-09-20   7401        flood     USA"
      ]
     },
     "execution_count": 234,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.loc['1980':'1981']"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Integer-based indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count             1410\n",
       "event      plane_crash\n",
       "country            USA\n",
       "Name: 1980-01-14 00:00:00, dtype: object"
      ]
     },
     "execution_count": 235,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select a single row, the first one\n",
    "\n",
    "toy_df.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "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>count</th>\n",
       "      <th>event</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1981-09-20</th>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1983-08-23</th>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1989-05-04</th>\n",
       "      <td>2583</td>\n",
       "      <td>fire</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            count  event country\n",
       "date                            \n",
       "1981-09-20   7401  flood     USA\n",
       "1983-08-23   4216   fire     USA\n",
       "1989-05-04   2583   fire     USA"
      ]
     },
     "execution_count": 236,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select  a range of rows by index\n",
    "\n",
    "toy_df.iloc[[1,3,-1]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "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>count</th>\n",
       "      <th>event</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1980-01-14</th>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1981-09-20</th>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1982-06-26</th>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1983-08-23</th>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1984-08-12</th>\n",
       "      <td>4700</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            count        event country\n",
       "date                                  \n",
       "1980-01-14   1410  plane_crash     USA\n",
       "1981-09-20   7401        flood     USA\n",
       "1982-06-26   6483        flood     USA\n",
       "1983-08-23   4216         fire     USA\n",
       "1984-08-12   4700  plane_crash     USA"
      ]
     },
     "execution_count": 237,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select  a range of rows with slicing\n",
    "\n",
    "toy_df.iloc[0:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['1980-01-14', '1981-09-20', '1982-06-26', '1983-08-23',\n",
       "               '1984-08-12', '1985-09-02', '1986-03-13', '1987-01-03',\n",
       "               '1988-07-08', '1989-05-04'],\n",
       "              dtype='datetime64[ns]', name='date', freq=None)"
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df.index"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Iterating over rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1980-01-14 00:00:00\n",
      "1981-09-20 00:00:00\n",
      "1982-06-26 00:00:00\n",
      "1983-08-23 00:00:00\n",
      "1984-08-12 00:00:00\n",
      "1985-09-02 00:00:00\n",
      "1986-03-13 00:00:00\n",
      "1987-01-03 00:00:00\n",
      "1988-07-08 00:00:00\n",
      "1989-05-04 00:00:00\n"
     ]
    }
   ],
   "source": [
    "for index, row in toy_df.iterrows():\n",
    "    print(index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1980-01-14 00:00:00 plane_crash\n",
      "1981-09-20 00:00:00 flood\n",
      "1982-06-26 00:00:00 flood\n",
      "1983-08-23 00:00:00 fire\n",
      "1984-08-12 00:00:00 plane_crash\n",
      "1985-09-02 00:00:00 flood\n",
      "1986-03-13 00:00:00 plane_crash\n",
      "1987-01-03 00:00:00 fire\n",
      "1988-07-08 00:00:00 plane_crash\n",
      "1989-05-04 00:00:00 fire\n"
     ]
    }
   ],
   "source": [
    "for index, row in toy_df.iterrows():\n",
    "    print(index, row.event)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "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>count</th>\n",
       "      <th>event</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1980-01-14</th>\n",
       "      <td>1410</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1981-09-20</th>\n",
       "      <td>7401</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1982-06-26</th>\n",
       "      <td>6483</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1983-08-23</th>\n",
       "      <td>4216</td>\n",
       "      <td>fire</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1984-08-12</th>\n",
       "      <td>4700</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1985-09-02</th>\n",
       "      <td>8833</td>\n",
       "      <td>flood</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1986-03-13</th>\n",
       "      <td>9351</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987-01-03</th>\n",
       "      <td>7003</td>\n",
       "      <td>fire</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1988-07-08</th>\n",
       "      <td>2845</td>\n",
       "      <td>plane_crash</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1989-05-04</th>\n",
       "      <td>2583</td>\n",
       "      <td>fire</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            count        event country\n",
       "date                                  \n",
       "1980-01-14   1410  plane_crash     USA\n",
       "1981-09-20   7401        flood     USA\n",
       "1982-06-26   6483        flood     USA\n",
       "1983-08-23   4216         fire     USA\n",
       "1984-08-12   4700  plane_crash     USA\n",
       "1985-09-02   8833        flood     USA\n",
       "1986-03-13   9351  plane_crash     USA\n",
       "1987-01-03   7003         fire     USA\n",
       "1988-07-08   2845  plane_crash     USA\n",
       "1989-05-04   2583         fire     USA"
      ]
     },
     "execution_count": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "toy_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ⏰ ✏️ Time to practice  "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Dataset**\n",
    "\n",
    "For this excercise we will be working with one of the datasets published by the [*Shakespeare and Company project*](https://shakespeareandco.princeton.edu/) – the *books dataset* – which can be downloaded from the following address: https://dataspace.princeton.edu/bitstream/88435/dsp01jm214s28p/2/SCoData_books_v1.2_2022-01.csv (file size = 1.34 MB)\n",
    "\n",
    "TODO Content of this dataset?\n",
    " \n",
    "**Steps**\n",
    "\n",
    "Perform the following steps on the dataset:\n",
    "- load it into a pandas' dataframe\n",
    "- how many records does it contain?\n",
    "- keep only the following columns: `uri`, `format` and `borrow_count`\n",
    "- remove all rows where `format` value is `NaN`\n",
    "- how many records does it contain now?\n",
    "\n",
    "**Try to answer the following questions**\n",
    "\n",
    "- What's the format(s) of the **most borrowed** document(s)? How many times was it/where they borrowed?\n",
    "- What's the format(s) of the **least borrowed** document(s)? How many times was it/where they borrowed?\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {
    "height": "12px",
    "width": "161px"
   },
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "320px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  },
  "toc-autonumbering": false,
  "toc-showcode": false,
  "toc-showmarkdowntxt": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}