library_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0",
   "metadata": {},
   "source": [
    "# Pandas 🐼"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1",
   "metadata": {},
   "source": [
    "# Table of Contents\n",
    "  - [Introduction](#Introduction)\n",
    "      - [What is Pandas?](#What-is-Pandas?)\n",
    "      - [Why We Need Pandas with Python](#Why-We-Need-Pandas-with-Python)\n",
    "      - [When and Why It's Used](#When-and-Why-It's-Used)\n",
    "      - [Pandas Performance](#Pandas-Performance)\n",
    "      - [Alternatives and When They Are Better](#Alternatives-and-When-They-Are-Better)\n",
    "  - [References](#References)\n",
    "  - [Working with Pandas `DataFrame`](#Working-with-Pandas-DataFrame)\n",
    "    - [Pandas Data Structures](#Pandas-Data-Structures)\n",
    "      - [Working with NumPy Arrays](#Working-with-NumPy-Arrays)\n",
    "      - [`Series`](#Series)\n",
    "      - [`Index`](#Index)\n",
    "      - [`DataFrame`](#DataFrame)\n",
    "    - [Creating `DataFrame` objects](#Creating-DataFrame-objects)\n",
    "      - [Creating a `Series` object](#Creating-a-Series-object)\n",
    "      - [Creating a `DataFrame` object from a `Series` object](#Creating-a-DataFrame-object-from-a-Series-object)\n",
    "      - [Creating a `DataFrame` from Python Data Structures](#Creating-a-DataFrame-from-Python-Data-Structures)\n",
    "        - [From a dictionary of list-like structures](#From-a-dictionary-of-list-like-structures)\n",
    "        - [From a list of dictionaries](#From-a-list-of-dictionaries)\n",
    "        - [From a list of tuples](#From-a-list-of-tuples)\n",
    "        - [From a NumPy array](#From-a-NumPy-array)\n",
    "    - [Creating a `DataFrame` object from the contents of a CSV File](#Creating-a-DataFrame-object-from-the-contents-of-a-CSV-File)\n",
    "      - [Finding information on the file before reading it in](#Finding-information-on-the-file-before-reading-it-in)\n",
    "        - [Examining a few rows](#Examining-a-few-rows)\n",
    "        - [Column count](#Column-count)\n",
    "      - [Reading in the file](#Reading-in-the-file)\n",
    "      - [Writing a `DataFrame` Object to a CSV File](#Writing-a-DataFrame-Object-to-a-CSV-File)\n",
    "    - [Writing a `DataFrame` Object to a Database](#Writing-a-DataFrame-Object-to-a-Database)\n",
    "    - [Creating a `DataFrame` Object by Querying a Database](#Creating-a-DataFrame-Object-by-Querying-a-Database)\n",
    "    - [Selecting data](#Selecting-data)\n",
    "      - [Selecting columns](#Selecting-columns)\n",
    "      - [Slicing](#Slicing)\n",
    "          - [Selecting rows](#Selecting-rows)\n",
    "          - [Selecting rows and columns with chaining](#Selecting-rows-and-columns-with-chaining)\n",
    "      - [Indexing](#Indexing)\n",
    "        - [Indexing with `loc`](#Indexing-with-loc)\n",
    "        - [Indexing with `iloc`](#Indexing-with-iloc)\n",
    "        - [Looking up scalar values](#Looking-up-scalar-values)\n",
    "      - [Filtering](#Filtering)\n",
    "    - [Adding and removing data](#Adding-and-removing-data)\n",
    "      - [Adding new columns](#Adding-new-columns)\n",
    "      - [Concatenation](#Concatenation)\n",
    "      - [Deleting data](#Deleting-data)\n",
    "          - [Using the `drop()` method](#Using-the-drop()-method)\n",
    "    - [Exercises (1)](#Exercises-(1))\n",
    "  - [Data wrangling](#Data-wrangling)\n",
    "    - [Cleaning data](#Cleaning-data)\n",
    "      - [Renaming Columns](#Renaming-Columns)\n",
    "      - [Type Conversion](#Type-Conversion)\n",
    "      - [Reordering, reindexing, and sorting](#Reordering,-reindexing,-and-sorting)\n",
    "    - [Reshaping data](#Reshaping-data)\n",
    "      - [Transposing](#Transposing)\n",
    "      - [Pivoting](#Pivoting)\n",
    "        - [`pivot()`](#pivot())\n",
    "        - [`unstack()`](#unstack())\n",
    "      - [Melting](#Melting)\n",
    "        - [`melt()`](#melt())\n",
    "        - [`stack()`](#stack())\n",
    "    - [Exercises (2)](#Exercises-(2))\n",
    "  - [Data aggregation](#Data-aggregation)\n",
    "      - [Querying](#Querying)\n",
    "      - [Merging](#Merging)\n",
    "    - [`DataFrame` operations](#DataFrame-operations)\n",
    "      - [Arithmetics and statistics](#Arithmetics-and-statistics)\n",
    "      - [Binning](#Binning)\n",
    "      - [Applying functions](#Applying-functions)\n",
    "    - [Using `groupby()`](#Using-groupby())\n",
    "    - [Exercises (3)](#Exercises-(3))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3",
   "metadata": {},
   "source": [
    "### What is Pandas?\n",
    "\n",
    "[Pandas](https://pandas.pydata.org/docs/index.html) is an open-source data manipulation and analysis library for Python.\n",
    "It provides powerful data structures and functions designed to make working with structured data intuitive and efficient. At the heart of Pandas are two primary data structures:\n",
    "\n",
    "- **DataFrame**: A two-dimensional, size-mutable, and potentially heterogeneous tabular data structure with labeled axes (rows and columns). It's similar to a spreadsheet or SQL table and is generally the most commonly used Pandas object.\n",
    "- **Series**: A one-dimensional labeled array capable of holding any data type (integers, strings, floating point numbers, Python objects, etc.).\n",
    "\n",
    "Pandas integrates well with various other Python libraries, such as Matplotlib for plotting and NumPy for numerical computations, making it a central library in the Python data science stack."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4",
   "metadata": {},
   "source": [
    "### Why We Need Pandas with Python\n",
    "\n",
    "Python, while a powerful programming language, isn't designed specifically for data analysis.\n",
    "It lacks built-in, high-level data structures and tools that are intuitive and efficient for these tasks.\n",
    "Here's where Pandas comes in:\n",
    "\n",
    "- **Data Cleaning and Preparation**: Data scientists spend a significant amount of time cleaning and preparing data. Pandas simplifies these tasks with built-in functions for filtering, selecting, and manipulating data.\n",
    "- **Data Analysis**: With Pandas, analyzing and exploring data is more straightforward. It provides functions for aggregating, summarizing, and transforming data, making it easier to derive insights.\n",
    "- **Data Visualization**: Though Pandas is not a data visualization library, it seamlessly interfaces with Matplotlib for plotting and visualizing data, allowing quick and informative visual analysis.\n",
    "- **Handling Diverse Data Types**: Pandas efficiently handles a variety of data formats, including CSV, Excel files, SQL databases, and HDF5 format, making it a versatile tool for diverse data analysis needs."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5",
   "metadata": {},
   "source": [
    "### When and Why It's Used\n",
    "\n",
    "Pandas is widely used in a variety of fields for data analysis and manipulation tasks.\n",
    "Some common use cases include:\n",
    "\n",
    "- **Data Cleaning**: Transforming raw data into a form that is suitable for analysis, such as filling missing values, removing duplicates, and converting data types.\n",
    "- **Data Exploration and Analysis**: Quick examination of data for patterns, irregularities, and insights. This includes operations like sorting, filtering, and grouping data.\n",
    "- **Data Visualization**: Creating plots and graphs to understand trends and patterns in data.\n",
    "- **Machine Learning**: Preprocessing and cleaning datasets before feeding them into machine learning models."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6",
   "metadata": {},
   "source": [
    "### Pandas Performance\n",
    "\n",
    "Pandas is highly efficient for most data manipulation and analysis tasks, especially with small to moderately sized datasets.\n",
    "It's optimized for performance in many scenarios, with critical code paths written in Cython or C.\n",
    "However, when working with very large datasets (with about tens of millions of rows or more), Pandas can face performance issues due to:\n",
    "\n",
    "- **Memory Usage**: Pandas typically requires significantly more memory than the size of the data, making it less efficient for very large datasets.\n",
    "- **Speed**: For extremely large datasets, some operations in Pandas can be slow, as it's not fully optimized for all use cases, especially those involving large-scale, distributed computing."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7",
   "metadata": {},
   "source": [
    "### Alternatives and When They Are Better\n",
    "\n",
    "One of the notable alternatives to Pandas is [**Polars**](https://pola.rs/).\n",
    "Polars is a DataFrame library that is designed to handle larger datasets more efficiently than Pandas.\n",
    "Here's why and when Polars can be a better choice:\n",
    "\n",
    "- **Performance**: Polars is designed to be faster and more memory-efficient than Pandas, particularly with large datasets. It leverages modern hardware capabilities, like multi-threading, to speed up data processing.\n",
    "- **Lazy Evaluation**: Polars supports lazy evaluation, where computations are queued and executed only when necessary. This approach can lead to performance improvements, especially in complex data pipelines.\n",
    "- **Ease of Scaling**: For large-scale data processing, Polars can be a better fit. It's more adept at handling the kinds of big data tasks that are increasingly common in industry settings."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "# References\n",
    "\n",
    "The majority of the material of this notebook has been taken and rearranged – with the permission of the author – from the book [*Hands-On Data Analysis with Pandas* (2nd edition) by Stefanie Molin](https://www.amazon.com/dp/1800563450/).\n",
    "The book covers way more than just Pandas, including plotting, data mining, and some guided projects to build \"data-centric\" apps.\n",
    "If you want to go more in depth with this topics, the book is a highly encouraged read."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11",
   "metadata": {},
   "source": [
    "# Working with Pandas `DataFrame`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12",
   "metadata": {},
   "source": [
    "## Pandas Data Structures"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13",
   "metadata": {},
   "source": [
    "\n",
    "In this section, we will discuss the `Series`, `Index`, and `DataFrame` classes. To do so, we will read in a snippet of the CSV file we will work with later. Don't worry about that part yet, though."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14",
   "metadata": {},
   "source": [
    "### Working with NumPy Arrays\n",
    "Let's read in a short CSV file (using `numpy`) for some sample data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "data = np.genfromtxt(\n",
    "    'data/01/example_data.csv', delimiter=';', \n",
    "    names=True, dtype=None, encoding='UTF'\n",
    ")\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16",
   "metadata": {},
   "source": [
    "We can find the dimensions with the `shape` attribute:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18",
   "metadata": {},
   "source": [
    "We can find the data types with the `dtype` attribute:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20",
   "metadata": {},
   "source": [
    "Each element in the array corresponds to a row from the CSV file. Unlike lists that can hold multiple data types, NumPy arrays are limited to one, enabling quick, vectorized actions. The data import resulted in an array of `numpy.void` objects, designed to handle various types. This occurs as each row contains diverse data types: four strings, one float, and one integer. Consequently, we miss out on the performance benefits NumPy offers for arrays with uniform data types.\n",
    "\n",
    "Consider finding the highest magnitude. We can employ a [list comprehension](https://www.python.org/dev/peps/pep-0202/) to extract the third index from each row, which is in the form of a `numpy.void` object. By doing this, we create a list that allows us to determine the maximum value using the `max()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%timeit\n",
    "max([row[3] for row in data])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22",
   "metadata": {},
   "source": [
    "If we, instead, create a NumPy array for each column, this operation is much easier (and more efficient) to perform. We can use a **[dictionary comprehension](https://www.python.org/dev/peps/pep-0274/)** to make a dictionary where the keys are the column names and the values are NumPy arrays of the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23",
   "metadata": {},
   "outputs": [],
   "source": [
    "array_dict = {\n",
    "    col: np.array([row[i] for row in data])\n",
    "    for i, col in enumerate(data.dtype.names)\n",
    "}\n",
    "array_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24",
   "metadata": {},
   "source": [
    "Grabbing the maximum magnitude is now simply a matter of selecting the `mag` key and calling the `max()` method. This is nearly twice as fast as the list comprehension implementation when dealing with just 5 entries, imagine how much worse the first attempt will perform on large data sets:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%timeit\n",
    "array_dict['mag'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26",
   "metadata": {},
   "source": [
    "However, this representation has other issues. Say we wanted to grab all the information for the earthquake with the maximum magnitude, how would we go about that? We would need to find the index of the maximum and then for each of the keys in the dictionary grab that index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27",
   "metadata": {},
   "outputs": [],
   "source": [
    "np.array([\n",
    "    value[array_dict['mag'].argmax()] \n",
    "    for key, value in array_dict.items()\n",
    "])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28",
   "metadata": {},
   "source": [
    "We now have a NumPy array consisting solely of strings, converting our numerical values into this format and reverting to the earlier setup. Additionally, if we aim to sort the data by magnitude in ascending order, the initial format requires sorting the rows based on the third index. In the second format, we need to establish the sorting order based on the `mag` column and then rearrange all other arrays accordingly. Handling multiple NumPy arrays with different data types simultaneously can be challenging. This is where `pandas` comes into play, enhancing the ease of working with NumPy arrays. Let's begin delving into `pandas` by understanding its data structure."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29",
   "metadata": {},
   "source": [
    "### `Series`\n",
    "The `Series` class provides a data structure for arrays of a single type with some additional functionality."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "place = pd.Series(array_dict['place'], name='place')\n",
    "place"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31",
   "metadata": {},
   "source": [
    "Here are some commonly used attributes with `Series` objects:\n",
    "\n",
    "|Attribute | Returns |\n",
    "| --- | --- |\n",
    "| `name` | The name of the `Series` object |\n",
    "| `dtype` | The data type of the `Series` object |\n",
    "| `shape` | Dimensions of the `Series` object in a tuple of the form `(number of rows,)` |\n",
    "| `index` | The `Index` object that is part of the `Series` object |\n",
    "| `values` | The data in the `Series` object |\n",
    "\n",
    "For the most part, `pandas` objects use NumPy arrays for their internal data representations. However, for some data types, `pandas` builds upon NumPy to create its own [arrays](https://pandas.pydata.org/pandas-docs/stable/reference/arrays.html). For this reason, depending on the data type, `values` can either be a `pandas.array` or `numpy.array` object. Therefore, if we need to ensure we get a specific type back, then it is recommended to use the `array` attribute or `to_numpy()` method, respectively, instead of `values`.\n",
    "\n",
    "Now let's see some examples using these attributes.\n",
    "\n",
    "**Getting the name of the series**\n",
    "\n",
    "The NumPy array held the name of the data in the `dtype` attribute; here, we can access it directly: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32",
   "metadata": {},
   "outputs": [],
   "source": [
    "place.name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33",
   "metadata": {},
   "source": [
    "**Getting the data type**\n",
    "\n",
    "A `Series` object holds a single data type.\n",
    "Here it is `'O'` for object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34",
   "metadata": {},
   "outputs": [],
   "source": [
    "place.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35",
   "metadata": {},
   "source": [
    "**Getting the dimensions of the series**\n",
    "\n",
    "Just as with NumPy, we can use `shape` to get the dimensions as `(rows, columns)`.\n",
    "`Series` objects are a single column, so they only have values for the rows dimension. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36",
   "metadata": {},
   "outputs": [],
   "source": [
    "place.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37",
   "metadata": {},
   "source": [
    "**Isolating the values from the series**\n",
    "\n",
    "This `Series` object is storing its values as a NumPy array:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38",
   "metadata": {},
   "outputs": [],
   "source": [
    "place.values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39",
   "metadata": {},
   "source": [
    "### `Index`\n",
    "The addition of the `Index` class makes the `Series` class more powerful than a NumPy array. We can get the index from the `index` attribute of a `Series` object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40",
   "metadata": {},
   "outputs": [],
   "source": [
    "place_index = place.index\n",
    "place_index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41",
   "metadata": {},
   "source": [
    "As with `Series` objects, we can access the underlying data via the `values` attribute. Note that this `Index` object is also built on top of a NumPy array:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42",
   "metadata": {},
   "outputs": [],
   "source": [
    "place_index.values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43",
   "metadata": {},
   "source": [
    "Here are some commonly used attributes with `Index` objects:\n",
    "\n",
    "|Attribute | Returns |\n",
    "| --- | --- |\n",
    "| `name` | The name of the `Index` object |\n",
    "| `dtype` | The data type of the `Index` object |\n",
    "| `shape` | Dimensions of the `Index` object |\n",
    "| `values` | The data in the `Index` object |\n",
    "| `is_unique` | Check if the `Index` object has all unique values |\n",
    "\n",
    "We can check the type of the underlying data, just like with a `Series` object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44",
   "metadata": {},
   "outputs": [],
   "source": [
    "place_index.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45",
   "metadata": {},
   "source": [
    "Same for the dimensions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46",
   "metadata": {},
   "outputs": [],
   "source": [
    "place_index.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47",
   "metadata": {},
   "source": [
    "We can check if the values are unique:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48",
   "metadata": {},
   "outputs": [],
   "source": [
    "place_index.is_unique"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49",
   "metadata": {},
   "source": [
    "With NumPy we can perform arithmetic operations element-wise between arrays:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50",
   "metadata": {},
   "outputs": [],
   "source": [
    "np.array([1, 1, 1]) + np.array([-1, 0, 1])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51",
   "metadata": {},
   "source": [
    "Pandas supports this as well, and the index determines how element-wise operations are performed. With addition, only the matching indices are summed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52",
   "metadata": {},
   "outputs": [],
   "source": [
    "numbers = np.linspace(0, 10, num=5) # makes numpy array([0, 2.5, 5, 7.5, 10])\n",
    "x = pd.Series(numbers) # index is [0, 1, 2, 3, 4]\n",
    "y = pd.Series(numbers, index=pd.Index([1, 2, 3, 4, 5]))\n",
    "x + y"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53",
   "metadata": {},
   "source": [
    "We aren't limited to the integer indices of list-like structures, and we can label our rows. The labels can be altered at any time and be things like dates or even another column. In chapter 3, we will discuss how to perform some operations on the index in order to change it. Then, in chapter 4, we will use the index for operations merging data and aggregating it.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54",
   "metadata": {},
   "source": [
    "### `DataFrame`\n",
    "\n",
    "Using a `Series` object for each column enhances the NumPy approach, yet challenges persist in sorting by values or extracting full rows.\n",
    "A `DataFrame` provides a tabular representation comprising multiple `Series` objects as columns and a unified `Index` object labeling the rows.\n",
    "We can construct a `DataFrame` from either of the previously discussed NumPy formats.\n",
    "While it's possible to create a `Series` object for each column, it's unnecessary:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(array_dict) \n",
    "\n",
    "# this will also work with the first representation\n",
    "# df = pd.DataFrame(data)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56",
   "metadata": {},
   "source": [
    "We can check the type of the underlying data with `dtypes` (note that it is not `dtype` as with `Series` and `Index` objects since each column will have its own data type):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58",
   "metadata": {},
   "source": [
    "We can get the underlying data with the `values` attribute. Note that this looks very similar to our initial NumPy representation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60",
   "metadata": {},
   "source": [
    "We can isolate the columns with the `columns` attribute. Notice that the columns are actually an `Index` object just on a different axis (columns are the horizontal index while rows are the vertical index)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62",
   "metadata": {},
   "source": [
    "Here are some commonly used attributes:\n",
    "\n",
    "|Attribute | Returns |\n",
    "| --- | --- |\n",
    "| `dtypes` | The data types of each column |\n",
    "| `shape` | Dimensions of the `DataFrame` object in a tuple of the form `(number of rows, number of columns)` |\n",
    "| `index` | The `Index` object along the rows of the `DataFrame` object |\n",
    "| `columns` | The name of the columns (as an `Index` object) |\n",
    "| `values` | The data in the `DataFrame` object |\n",
    "| `empty` | Check if the `DataFrame` object is empty |\n",
    "\n",
    "The `Index` object along the rows of the dataframe can be accessed via the `index` attribute (just as with `Series` objects):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64",
   "metadata": {},
   "source": [
    "As with both `Series` and `Index` objects, we can get the dimensions of the dataframe with the `shape` attribute. The result is of the form `(nrows, ncols)`. Our dataframe has 5 rows and 6 columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66",
   "metadata": {},
   "source": [
    "Pandas allows arithmetic operations on dataframes, matching both index and column for execution.\n",
    "This example showcases addition.\n",
    "In string columns (`time`, `place`, `magType`, and `alert`), pandas concatenates values across dataframes.\n",
    "For numeric columns (`mag` and `tsunami`), the values are summed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67",
   "metadata": {},
   "outputs": [],
   "source": [
    "df + df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68",
   "metadata": {},
   "source": [
    "## Creating `DataFrame` objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70",
   "metadata": {},
   "source": [
    "### Creating a `Series` object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71",
   "metadata": {},
   "outputs": [],
   "source": [
    "np.random.seed(0) # set a seed for reproducibility\n",
    "pd.Series(np.random.rand(5), name='random')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72",
   "metadata": {},
   "source": [
    "### Creating a `DataFrame` object from a `Series` object\n",
    "Use the `to_frame()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series(np.linspace(0, 10, num=5)).to_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74",
   "metadata": {},
   "source": [
    "### Creating a `DataFrame` from Python Data Structures"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75",
   "metadata": {},
   "source": [
    "#### From a dictionary of list-like structures\n",
    "\n",
    "The dictionary values can be lists, NumPy arrays, etc. as long as they have length (generators don't have length so we can't use them here):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76",
   "metadata": {},
   "outputs": [],
   "source": [
    "np.random.seed(0) # set seed so result is reproducible\n",
    "pd.DataFrame(\n",
    "    {\n",
    "        'random': np.random.rand(5),\n",
    "        'text': ['hot', 'warm', 'cool', 'cold', None],\n",
    "        'truth': [np.random.choice([True, False]) for _ in range(5)]\n",
    "    }, \n",
    "    index=pd.date_range(\n",
    "        end=dt.date(2019, 4, 21),\n",
    "        freq='1D',\n",
    "        periods=5, \n",
    "        name='date'\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77",
   "metadata": {},
   "source": [
    "#### From a list of dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame([\n",
    "    {'mag': 5.2, 'place': 'California'},\n",
    "    {'mag': 1.2, 'place': 'Alaska'},\n",
    "    {'mag': 0.2, 'place': 'California'},\n",
    "])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79",
   "metadata": {},
   "source": [
    "#### From a list of tuples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80",
   "metadata": {},
   "outputs": [],
   "source": [
    "list_of_tuples = [(n, n**2, n**3) for n in range(5)]\n",
    "list_of_tuples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(\n",
    "    list_of_tuples, \n",
    "    columns=['n', 'n_squared', 'n_cubed']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82",
   "metadata": {},
   "source": [
    "#### From a NumPy array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(\n",
    "    np.array([\n",
    "        [0, 0, 0],\n",
    "        [1, 1, 1],\n",
    "        [2, 4, 8],\n",
    "        [3, 9, 27],\n",
    "        [4, 16, 64]\n",
    "    ]), columns=['n', 'n_squared', 'n_cubed']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84",
   "metadata": {},
   "source": [
    "## Creating a `DataFrame` object from the contents of a CSV File"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85",
   "metadata": {},
   "source": [
    "### Finding information on the file before reading it in\n",
    "Before attempting to read in a file, we can use the command line to see important information about the file that may determine how we read it in. We can run command line code from Jupyter Notebooks by using `!` before the code.\n",
    "\n",
    "For example, we can find out how many lines are in the file by using the `wc` utility (word count) and counting lines in the file (`-l`). The file has 9,333 lines:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86",
   "metadata": {},
   "outputs": [],
   "source": [
    "!wc -l data/01/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87",
   "metadata": {},
   "source": [
    "**Windows users**: if the above doesn't work for you (depends on your setup), then use this instead:\n",
    "\n",
    "```python\n",
    "!find /c /v \"\" data\\01\\earthquakes.csv\n",
    "```\n",
    "\n",
    "\n",
    "We can find the file size by using `ls` to list the files in the `data` directory, and passing in the flags `-lh` to include the file size in human readable format. Then we use `grep` to find the file in question. Note that `|` passes the result of `ls` to `grep`. The `grep` utility is used for finding items that match patterns.\n",
    "\n",
    "This tells us the file is 3.4 MB:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88",
   "metadata": {},
   "outputs": [],
   "source": [
    "!ls -lh data/01 | grep earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89",
   "metadata": {},
   "source": [
    "**Windows users**: if the above doesn't work for you (depends on your setup), then use this instead:\n",
    "\n",
    "```python\n",
    "!dir data\\01 | findstr \"earthquakes.csv\"\n",
    "```\n",
    "\n",
    "We can even capture the result of a command and use it in our Python code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90",
   "metadata": {},
   "outputs": [],
   "source": [
    "files = !ls -lh data/01\n",
    "[file for file in files if 'earthquake' in file]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91",
   "metadata": {},
   "source": [
    "**Windows users**: if the above doesn't work for you (depends on your setup), then use this instead:\n",
    "\n",
    "```python\n",
    "files = !dir data\\01\n",
    "[file for file in files if 'earthquake' in file]\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92",
   "metadata": {},
   "source": [
    "#### Examining a few rows\n",
    "\n",
    "We can use `head` to look at the top `n` rows of the file. With the `-n` flag, we can specify how many. This shows use that the first row of the file contains headers and that it is comma-separated (just because the file extension is `.csv` doesn't it contains comma-separated values):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93",
   "metadata": {},
   "outputs": [],
   "source": [
    "!head -n 2 data/01/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94",
   "metadata": {},
   "source": [
    "**Windows users**: if the above doesn't work for you (depends on your setup), then use this instead:\n",
    "\n",
    "```python\n",
    "n = 2\n",
    "with open('data/01/earthquakes.csv', 'r') as file:\n",
    "    for _ in range(n):\n",
    "        print(file.readline(), end='\\r')\n",
    "```\n",
    "\n",
    "\n",
    "Just like `head` gives rows from the top, `tail` gives rows from the bottom. This can help us check that there is no extraneous data on the bottom of the field, like perhaps some metadata about the fields that actually isn't part of the dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95",
   "metadata": {},
   "outputs": [],
   "source": [
    "!tail -n 1 data/01/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96",
   "metadata": {},
   "source": [
    "**Windows users**: if the above doesn't work for you (depends on your setup), then use this instead:\n",
    "\n",
    "```python\n",
    "import os\n",
    "\n",
    "with open('data/01/earthquakes.csv', 'rb') as file:\n",
    "    file.seek(0, os.SEEK_END)\n",
    "    while file.read(1) != b'\\n':\n",
    "        file.seek(-2, os.SEEK_CUR)\n",
    "    print(file.readline().decode())\n",
    "```\n",
    "\n",
    "*Note*: To inspect more than one row from the end of the file, you will have to use this instead, which requires reading the whole file:\n",
    "\n",
    "```python\n",
    "n = 2\n",
    "with open('data/01/earthquakes.csv', 'r') as file:\n",
    "    print('\\r'.join(file.readlines()[-n:]))\n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97",
   "metadata": {},
   "source": [
    "#### Column count\n",
    "We can use `awk` to find the column count. This is a utility for pattern scanning and processing. The `-F` flag allows us to specify the delimiter (comma, in this case). Then we specify what to do for each record in the file. We choose to print `NF` which is a predefined variable whose value is the number of fields in the current record. Here, we say `exit` so that we print the number of fields (columns, here) in the first row of the file, then we stop. \n",
    "\n",
    "This tells us we have 26 data columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98",
   "metadata": {},
   "outputs": [],
   "source": [
    "!awk -F',' '{print NF; exit}' data/01/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99",
   "metadata": {},
   "source": [
    "**Windows users**: if the above or below don't work for you (depends on your setup), then use this instead:\n",
    "\n",
    "```python\n",
    "with open('data/01/earthquakes.csv', 'r') as file:\n",
    "    print(len(file.readline().split(',')))\n",
    "```\n",
    "\n",
    "\n",
    "Since we know the 1st line of the file had headers, and the file is comma-separated, we can also count the columns by using `head` to get headers and parsing them in Python:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "100",
   "metadata": {},
   "outputs": [],
   "source": [
    "headers = !head -n 1 data/01/earthquakes.csv\n",
    "len(headers[0].split(','))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "101",
   "metadata": {},
   "source": [
    "**Windows users**: if you had to use the alternatives above, consider trying out [Cygwin](https://www.cygwin.com) or [Windows Subsystem for Linux (WSL)](https://docs.microsoft.com/en-us/windows/wsl/about).\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "102",
   "metadata": {},
   "source": [
    "### Reading in the file\n",
    "\n",
    "Our file is small in size, has headers in the first row, and is comma-separated, so we don't need to provide any additional arguments to read in the file with `pd.read_csv()`, but be sure to check the [documentation](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html) for possible arguments:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "103",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/01/earthquakes.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "104",
   "metadata": {},
   "source": [
    "Note that we can also pass in a URL. Let's read this same file from GitHub:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "105",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\n",
    "    'https://github.com/stefmolin/'\n",
    "    'Hands-On-Data-Analysis-with-Pandas-2nd-edition'\n",
    "    '/blob/master/ch_02/data/earthquakes.csv?raw=True'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "106",
   "metadata": {},
   "source": [
    "Pandas is usually very good at figuring out which options to use based on the input data, so we often won't need to add arguments to the call; however, there are many options available should we need them, some of which include the following:\n",
    "\n",
    "| Parameter | Purpose |\n",
    "| --- | --- |\n",
    "| `sep` | Specifies the delimiter |\n",
    "| `header` | Row number where the column names are located; the default option has `pandas` infer whether they are present |\n",
    "| `names` | List of column names to use as the header |\n",
    "| `index_col` | Column to use as the index |\n",
    "| `usecols` | Specifies which columns to read in |\n",
    "| `dtype` | Specifies data types for the columns | \n",
    "| `converters` | Specifies functions for converting data in certain columns |\n",
    "| `skiprows` | Rows to skip |\n",
    "| `nrows` | Number of rows to read at a time (combine with `skiprows` to read a file bit by bit) |\n",
    "| `parse_dates` | Automatically parse columns containing dates into datetime objects |\n",
    "| `chunksize` | For reading the file in chunks |\n",
    "| `compression` | For reading in compressed files without extracting beforehand |\n",
    "| `encoding` | Specifies the file encoding |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "107",
   "metadata": {},
   "source": [
    "### Writing a `DataFrame` Object to a CSV File\n",
    "\n",
    "Our file is small in size, has headers in the first row, and is comma-separated, so we don't need to provide any additional arguments to read in the file with `pd.read_csv()`, but be sure to check the [documentation](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html) for possible arguments:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "108",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('output.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "109",
   "metadata": {},
   "source": [
    "## Writing a `DataFrame` Object to a Database\n",
    "Note the `if_exists` parameter. By default, it will give you an error if you try to write a table that already exists. Here, we don't care if it is overwritten. Lastly, if we are interested in appending new rows, we set that to `'append'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "110",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect('data/01/quakes.db') as connection:\n",
    "    pd.read_csv('data/01/tsunamis.csv').to_sql(\n",
    "        'tsunamis', connection, index=False, if_exists='replace'\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "111",
   "metadata": {},
   "source": [
    "## Creating a `DataFrame` Object by Querying a Database\n",
    "Using a SQLite database. Otherwise you need to install [SQLAlchemy](https://www.sqlalchemy.org/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "112",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect('data/01/quakes.db') as connection:\n",
    "    tsunamis = pd.read_sql('SELECT * FROM tsunamis', connection)\n",
    "\n",
    "tsunamis.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "113",
   "metadata": {},
   "source": [
    "## Selecting data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "114",
   "metadata": {},
   "source": [
    "We are going to use the `earthquakes.csv` file again, so let's read it in as a `DataFrame`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "115",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('data/01/earthquakes.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "116",
   "metadata": {},
   "source": [
    "### Selecting columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "117",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.mag"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "118",
   "metadata": {},
   "source": [
    "Grab an entire column using dictionary syntax:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "119",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['mag']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "120",
   "metadata": {},
   "source": [
    "Selecting multiple columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "121",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['mag', 'title']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "122",
   "metadata": {},
   "source": [
    "Selecting columns using list comprehensions and string operations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "123",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\n",
    "    ['title', 'time']\n",
    "    + [col for col in df.columns if col.startswith('mag')]\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "124",
   "metadata": {},
   "source": [
    "Breaking down this example:\n",
    "1. the list comprehension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "125",
   "metadata": {},
   "outputs": [],
   "source": [
    "[col for col in df.columns if col.startswith('mag')]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "126",
   "metadata": {},
   "source": [
    "2. assembling the list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "127",
   "metadata": {},
   "outputs": [],
   "source": [
    "['title', 'time'] \\\n",
    "+ [col for col in df.columns if col.startswith('mag')]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "128",
   "metadata": {},
   "source": [
    "3. using this list as the list of columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "129",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\n",
    "    ['title', 'time']\n",
    "    + [col for col in df.columns if col.startswith('mag')]\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "130",
   "metadata": {},
   "source": [
    "### Slicing"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "131",
   "metadata": {},
   "source": [
    "##### Selecting rows\n",
    "\n",
    "We can use the standard Python sytnax for slicing to fetch a number of rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "132",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[100:103]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "133",
   "metadata": {},
   "source": [
    "##### Selecting rows and columns with chaining"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "134",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['title', 'time']][100:103]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "135",
   "metadata": {},
   "source": [
    "Order doesn't matter here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "136",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[100:103][['title', 'time']].equals(\n",
    "    df[['title', 'time']][100:103]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "137",
   "metadata": {},
   "source": [
    "So we know how to select rows and columns, but can we update values? Well, if we try using what we have learned so far, we will see the following warning:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "138",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[110:113]['title'] = df[110:113]['title'].str.lower()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "139",
   "metadata": {},
   "source": [
    "Note that it worked here, but `pandas` says we were setting a value on a copy of a slice and that we should use `loc` instead (see the following section):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "140",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[110:113]['title']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "141",
   "metadata": {},
   "source": [
    "### Indexing\n",
    "\n",
    "We can use the `loc` method of a `DataFrame` to modify a slice of a data frame without incurring in Pandas warnings:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "142",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[110:112, 'title'] = df.loc[110:112, 'title'].str.lower()\n",
    "df.loc[110:112, 'title']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "143",
   "metadata": {},
   "source": [
    "#### Indexing with `loc`\n",
    "\n",
    "Selection of the format `loc[row_indexer, column_indexer]` where `:` can be used to select all, as in Python's slicing syntax:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "144",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:,'title']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "145",
   "metadata": {},
   "source": [
    "We can use `loc` to select specific rows and columns without chaining. If we use row numbers with `loc`, they are now **inclusive** of the end index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "146",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[10:15, ['title', 'mag']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "147",
   "metadata": {},
   "source": [
    "#### Indexing with `iloc`\n",
    "Exclusive of the endpoint just as Python slicing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "148",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[10:15, [19, 8]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "149",
   "metadata": {},
   "source": [
    "We can use slicing syntax with `iloc` for both rows and columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "150",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[10:15, 6:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "151",
   "metadata": {},
   "source": [
    "When using `loc`, we can slice on column names. This will be inclusive of the endpoint because you can't be expected to know what the next column name will be. As such, we have multiple ways to achieve the same end goal:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "152",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[10:15, 6:10].equals(\n",
    "    df.loc[10:14, 'gap':'magType']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "153",
   "metadata": {},
   "source": [
    "#### Looking up scalar values\n",
    "We used `loc` and `iloc` to grab subsets of the dataframe. However, if we are just interested in the specific value at a given `[row, column]`, then we can use `iat` and `at`. We use `at` with labels:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "154",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.at[10, 'mag']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "155",
   "metadata": {},
   "source": [
    "...and `iat` with integer indices:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "156",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iat[10, 8]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "157",
   "metadata": {},
   "source": [
    "### Filtering\n",
    "\n",
    "Similar to NumPy arrays, we can use **Boolean mask** to filter a data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "158",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.mag > 2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "159",
   "metadata": {},
   "source": [
    "This returns a \"list\" of every row for which the criteria on the `mag` column is greater than `2`.\n",
    "If we then want to select our data, we simply place it inside the brackets:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "160",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.mag >= 7.0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "161",
   "metadata": {},
   "source": [
    "We can use masks with `loc`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "162",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\n",
    "    df.mag >= 7.0,\n",
    "    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "163",
   "metadata": {},
   "source": [
    "Masks can be created using multiple criteria when combined with bitwise operators:\n",
    "- `&` for AND\n",
    "- `|` for OR\n",
    "\n",
    "We must also surround each criterion with parentheses. We can't use `and`/`or` here because we need to evaluate row by row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "164",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\n",
    "    (df.tsunami == 1) & (df.alert == 'red'),\n",
    "    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "165",
   "metadata": {},
   "source": [
    "An example with an OR condition, which is less restrictive:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "166",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\n",
    "    (df.tsunami == 1) | (df.alert == 'red'),\n",
    "    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "167",
   "metadata": {},
   "source": [
    "Masks can be created from any criteria that results in a Boolean. For example, we can select all earthquakes with the string `Alaska` in the `place` column with a non-null value for the `alert` column. To get non-nulls, we can use the `isnull()` method with the bitwise negation operator (`~`) or the `notnull()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "168",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\n",
    "    (df.place.str.contains('Alaska')) & (df.alert.notnull()),\n",
    "    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "169",
   "metadata": {},
   "source": [
    "We can even use regular expressions here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "170",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\n",
    "    (df.place.str.contains(r'CA|California$')) & (df.mag > 3.8),\n",
    "    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "171",
   "metadata": {},
   "source": [
    "We can use the `between()` method to turn 2 individual checks (is less than or equal to some maximum value and is greater than or equal to some minimum value) into a single one. Note this is inclusive of the endpoint by default:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "172",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\n",
    "    df.mag.between(6.5, 7.5),\n",
    "    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "173",
   "metadata": {},
   "source": [
    "We can use the `isin()` method to check for membership in a list of values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "174",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\n",
    "    df.magType.isin(['mw', 'mwb']),\n",
    "    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "175",
   "metadata": {},
   "source": [
    "We can grab the index of the minimum and maximum values of a given column and use those to select the entire row where they occur:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "176",
   "metadata": {},
   "outputs": [],
   "source": [
    "[df.mag.idxmin(), df.mag.idxmax()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "177",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\n",
    "    [df.mag.idxmin(), df.mag.idxmax()],\n",
    "    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "178",
   "metadata": {},
   "source": [
    "Note that there is a `filter()` method, but it doesn't filter the data in the same sense as we discussed in this section. Here are a few things you can do with this method.\n",
    "\n",
    "- grab columns of a dataframe by passing a list to `items`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "179",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.filter(items=['mag', 'magType']).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "180",
   "metadata": {},
   "source": [
    "- grab all the columns that contain a string with the `like` parameter:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "181",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.filter(like='mag').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "182",
   "metadata": {},
   "source": [
    "- use regular expressions; here, we select any columns that start with `t`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "183",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.filter(regex=r'^t').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "184",
   "metadata": {},
   "source": [
    "- use `filter()` along the rows, by passing in `axis=0`. Here, we will use the `place` column as the index (we will cover `set_index()` in chapter 3):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "185",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.set_index('place').filter(like='Japan', axis=0).filter(items=['mag', 'magType', 'title']).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "186",
   "metadata": {},
   "source": [
    "This also works on `Series` objects and will run on the index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "187",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.set_index('place').title.filter(like='Japan').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "188",
   "metadata": {},
   "source": [
    "## Adding and removing data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "189",
   "metadata": {},
   "source": [
    "### Adding new columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "190",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['source'] = 'USGS API'\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "191",
   "metadata": {},
   "source": [
    "...or a Boolean mask:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "192",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['mag_negative'] = df.mag < 0\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "193",
   "metadata": {},
   "source": [
    "### Concatenation\n",
    "Say we were working with two separate dataframes, one with earthquakes accompanied by tsunamis and the other with earthquakes without tsunamis.\n",
    "If we wanted to look at earthquakes as a whole, we would want to concatenate the dataframes into a single one:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "194",
   "metadata": {},
   "outputs": [],
   "source": [
    "tsunami = df[df.tsunami == 1]\n",
    "no_tsunami = df[df.tsunami == 0]\n",
    "\n",
    "tsunami.shape, no_tsunami.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "195",
   "metadata": {},
   "source": [
    "Concatenating along the row axis (`axis=0`) is equivalent to appending to the bottom.\n",
    "By concatenating our earthquakes with tsunamis and those without tsunamis, we get the full earthquake data set back:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "196",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([tsunami, no_tsunami]).shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "197",
   "metadata": {},
   "source": [
    "We have been working with a subset of the columns from the CSV file, but suppose that now we want to get some of the columns we ignored when we read in the data. Since we have added new columns in this notebook, we won't want to read in the file and perform those operations again. Instead, we will concatenate along the columns (`axis=1`) to add back what we are missing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "198",
   "metadata": {},
   "outputs": [],
   "source": [
    "additional_columns = pd.read_csv(\n",
    "    'data/01/earthquakes.csv', usecols=['tz', 'felt', 'ids']\n",
    ")\n",
    "pd.concat([df.head(2), additional_columns.head(2)], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "199",
   "metadata": {},
   "source": [
    "Notice what happens if the index doesn't align though:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "200",
   "metadata": {},
   "outputs": [],
   "source": [
    "additional_columns = pd.read_csv(\n",
    "    'data/01/earthquakes.csv', usecols=['tz', 'felt', 'ids', 'time'], index_col='time'\n",
    ")\n",
    "pd.concat([df.head(2), additional_columns.head(2)], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "201",
   "metadata": {},
   "source": [
    "If the index doesn't align, we can align it before attempting the concatentation, which we will discuss in chapter 3.\n",
    "\n",
    "Say we want to join the `tsunami` and `no_tsunami` dataframes, but the `no_tsunami` dataframe has an additional column. The `join` parameter specifies how to handle any overlap in column names (when appending to the bottom) or in row names (when concatenating to the left/right). By default, this is `outer`, so we keep everything; however, if we use `inner`, we will only keep what is in common:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "202",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat(\n",
    "    [tsunami.head(2), no_tsunami.head(2).assign(type='earthquake')], join='inner'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "203",
   "metadata": {},
   "source": [
    "In addition, we use `ignore_index`, since the index doesn't mean anything for us here. This gives us sequential values instead of what we had in the previous result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "204",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat(\n",
    "    [tsunami.head(2), no_tsunami.head(2).assign(type='earthquake')], join='inner', ignore_index=True\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "205",
   "metadata": {},
   "source": [
    "### Deleting data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "206",
   "metadata": {},
   "outputs": [],
   "source": [
    "del df['source']\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "207",
   "metadata": {},
   "source": [
    "If we don't know whether the column exists, we should use a `try`/`except` block:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "208",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    del df['source']\n",
    "except KeyError:\n",
    "    # handle the error here\n",
    "    print('not there anymore')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "209",
   "metadata": {},
   "source": [
    "We can also use `pop()`. This will allow us to use the series we remove later. Note there will be an error if the key doesn't exist, so we can also use a `try`/`except` here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "210",
   "metadata": {},
   "outputs": [],
   "source": [
    "mag_negative = df.pop('mag_negative')\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "211",
   "metadata": {},
   "source": [
    "Notice we have a mask in `mag_negative` now:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "212",
   "metadata": {},
   "outputs": [],
   "source": [
    "mag_negative.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "213",
   "metadata": {},
   "source": [
    "Now, we can use `mag_negative` to filter our data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "214",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[mag_negative].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "215",
   "metadata": {},
   "source": [
    "##### Using the `drop()` method\n",
    "\n",
    "We can drop rows by passing a list of indices to the `drop()` method. Notice in the following example that when asking for the first 2 rows with `head()` we get the 3rd and 4th rows because we dropped the original first 2 with `drop([0, 1])`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "216",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop([0, 1]).head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "217",
   "metadata": {},
   "source": [
    "The `drop()` method drops along the row axis by default. If we pass in a list of columns with the `columns` argument, we can delete columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "218",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_to_drop = [\n",
    "    col for col in df.columns\n",
    "    if col not in ['alert', 'mag', 'title', 'time', 'tsunami']\n",
    "]\n",
    "df.drop(columns=cols_to_drop).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "219",
   "metadata": {},
   "source": [
    "We also have the option of using `axis=1`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "220",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns=cols_to_drop).equals(\n",
    "    df.drop(cols_to_drop, axis=1)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "221",
   "metadata": {},
   "source": [
    "By default, `drop()`, along with the majority of `DataFrame` methods, will return a new `DataFrame` object. If we just want to change the one we are working with, we can pass `inplace=True`. This should be used with care:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "222",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns=cols_to_drop, inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "223",
   "metadata": {},
   "source": [
    "## Exercises (1)\n",
    "\n",
    "Using the CSV file found in `data/01/parsed.csv`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "224",
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext tutorial.tests.testsuite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "225",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "226",
   "metadata": {},
   "source": [
    "1. Determine the 95th percentile for earthquake magnitudes in Japan, specifically using the mb magnitude scale.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "227",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%ipytest\n",
    "\n",
    "def solution_pandas_1(df: pd.DataFrame):\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "228",
   "metadata": {},
   "source": [
    "2. Calculate the proportion of earthquakes in Indonesia that were accompanied by tsunamis.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "229",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%ipytest\n",
    "\n",
    "def solution_pandas_2(df: pd.DataFrame):\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "230",
   "metadata": {},
   "source": [
    "# Data wrangling\n",
    "\n",
    "***Note:** solutions to these exercises are provided in the notebook* [`library_pandas_solutions`](./tutorial/library_pandas_solutions.ipynb)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "231",
   "metadata": {},
   "source": [
    "\n",
    "In this section, we will be using two different datasets:\n",
    "\n",
    "1. Daily temperature data from the National Centers for Environmental Information (NCEI) API. We will use the Global Historical Climatology Network - Daily (GHCND) dataset.\n",
    "2. S&P 500 stock market data and data for Bitcoin for 2017 through 2018.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "232",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('data/02/nyc_temperatures.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "233",
   "metadata": {},
   "source": [
    "## Cleaning data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "234",
   "metadata": {},
   "source": [
    "### Renaming Columns\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "235",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "236",
   "metadata": {},
   "source": [
    "We want to rename the `value` column to indicate it contains the temperature in Celsius and the `attributes` column to say `flags` since each value in the comma-delimited string is a different flag about the data collection. For this task, we use the `rename()` method and pass in a dictionary mapping the column names to their new names. We pass `inplace=True` to change our original dataframe instead of getting a new one back:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "237",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(\n",
    "    columns={\n",
    "        'value': 'temp_C',\n",
    "        'attributes': 'flags'\n",
    "    }, inplace=True\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "238",
   "metadata": {},
   "source": [
    "Those columns have been successfully renamed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "239",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "240",
   "metadata": {},
   "source": [
    "We can also perform string operations on the column names with `rename()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "241",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(str.upper, axis='columns').columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "242",
   "metadata": {},
   "source": [
    "### Type Conversion\n",
    "\n",
    "We want to store the `date` column as a `datetime` object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "243",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "244",
   "metadata": {},
   "source": [
    "Let's perform the conversion with `pd.to_datetime()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "245",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:,'date'] = pd.to_datetime(df.date)\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "246",
   "metadata": {},
   "source": [
    "Now we get useful information when we use `describe()` on this column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "247",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.date.describe(datetime_is_numeric=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "248",
   "metadata": {},
   "source": [
    "We can use `tz_localize()` on a `DatetimeIndex` object to convert to a desired timezone:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "249",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.date_range(start='2018-10-25', periods=2, freq='D').tz_localize('EST')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "250",
   "metadata": {},
   "source": [
    "This also works with `Series`/`DataFrame` objects that have an index of type `DatetimeIndex`. Let's read in the CSV again for this example and set the `date` column to be the index and stored as a datetime:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "251",
   "metadata": {},
   "outputs": [],
   "source": [
    "eastern = pd.read_csv(\n",
    "    'data/02/nyc_temperatures.csv', index_col='date', parse_dates=True\n",
    ").tz_localize('EST')\n",
    "\n",
    "eastern.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "252",
   "metadata": {},
   "source": [
    "We can use `tz_convert()` to convert to another timezone from there. If we convert the Eastern datetimes to UTC, they will now be at 5 AM, since `pandas` will use the offsets to convert:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "253",
   "metadata": {},
   "outputs": [],
   "source": [
    "eastern.tz_convert('UTC').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "254",
   "metadata": {},
   "source": [
    "We can change the period of the index as well. We could change the period to be monthly to make it easier to aggregate later. (Aggregation will be discussed in chapter 4.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "255",
   "metadata": {},
   "outputs": [],
   "source": [
    "eastern.tz_localize(None).to_period('M').index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "256",
   "metadata": {},
   "source": [
    "We now get a `PeriodIndex` object, which we can change back into a `DatetimeIndex` object with `to_timestamp()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "257",
   "metadata": {},
   "outputs": [],
   "source": [
    "eastern.tz_localize(None).to_period('M').to_timestamp().index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "258",
   "metadata": {},
   "source": [
    "We can use the `assign()` method for working with multiple columns at once (or creating new ones). Since our `date` column has already been converted, we need to read in the data again:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "259",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/02/nyc_temperatures.csv').rename(\n",
    "    columns={\n",
    "        'value': 'temp_C',\n",
    "        'attributes': 'flags'\n",
    "    }\n",
    ")\n",
    "\n",
    "new_df = df.assign(\n",
    "    date=pd.to_datetime(df.date),\n",
    "    temp_F=(df.temp_C * 9/5) + 32\n",
    ")\n",
    "\n",
    "new_df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "260",
   "metadata": {},
   "source": [
    "The `date` column now has datetimes and the `temp_F` column was added:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "261",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "262",
   "metadata": {},
   "source": [
    "We can also use `astype()` to perform conversions. Let's create columns of the integer portion of the temperatures in Celsius and Fahrenheit. We will use **lambda functions** (first introduced in *Chapter 2, Working with Pandas DataFrames*), so that we can use the values being created in the `temp_F` column to calculate the `temp_F_whole` column. It is very common (and useful) to use lambda functions with `assign()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "263",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.assign(\n",
    "    date=lambda x: pd.to_datetime(x.date),\n",
    "    temp_C_whole=lambda x: x.temp_C.astype('int'),\n",
    "    temp_F=lambda x: (x.temp_C * 9/5) + 32,\n",
    "    temp_F_whole=lambda x: x.temp_F.astype('int')\n",
    ")\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "264",
   "metadata": {},
   "source": [
    "Creating categories:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "265",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_with_categories = df.assign(\n",
    "    station=df.station.astype('category'),\n",
    "    datatype=df.datatype.astype('category')\n",
    ")\n",
    "df_with_categories.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "266",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_with_categories.describe(include='category')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "267",
   "metadata": {},
   "source": [
    "Our categories have no order, but this is something that `pandas` supports:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "268",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Categorical(\n",
    "    ['med', 'med', 'low', 'high'], \n",
    "    categories=['low', 'med', 'high'],\n",
    "    ordered=True\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "269",
   "metadata": {},
   "source": [
    "### Reordering, reindexing, and sorting\n",
    "Say we want to find the days that reached the hottest temperatures in the weather data; we can sort our values by the `temp_C` column with the largest on top to find this: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "270",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.datatype == 'TMAX'].sort_values(by='temp_C', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "271",
   "metadata": {},
   "source": [
    "However, this isn't perfect because we have some ties, and they aren't sorted consistently.\n",
    "In the first tie between the 7th and the 10th, the earlier date comes first, but the opposite is true with the tie between the 4th and the 2nd.\n",
    "We can use other columns to break ties and specify how to sort each with `ascending`.\n",
    "Let's break ties with the date column and show earlier dates before later ones:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "272",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.datatype == 'TMAX'].sort_values(by=['temp_C', 'date'], ascending=[False, True]).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "273",
   "metadata": {},
   "source": [
    "Notice that the index was jumbled in the past 2 results. Here, our index only stores the row number in the original data, but we may not need to keep track of that information. In this case, we can pass in `ignore_index=True` to get a new index after sorting:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "274",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.datatype == 'TMAX'].sort_values(by=['temp_C', 'date'], ascending=[False, True], ignore_index=True).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "275",
   "metadata": {},
   "source": [
    "When just looking for the n-largest values, rather than wanting to sort all the data, we can use `nlargest()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "276",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.datatype == 'TAVG'].nlargest(n=10, columns='temp_C')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "277",
   "metadata": {},
   "source": [
    "We use `nsmallest()` for the n-smallest values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "278",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.nsmallest(n=5, columns=['temp_C', 'date'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "279",
   "metadata": {},
   "source": [
    "The `sample()` method will give us rows (or columns with `axis=1`) at random.\n",
    "We can provide a seed (`random_state`) to make this reproducible.\n",
    "The index after we do this is jumbled:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "280",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sample(5, random_state=0).index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "281",
   "metadata": {},
   "source": [
    "We can use `sort_index()` to order it again:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "282",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sample(5, random_state=0).sort_index().index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "283",
   "metadata": {},
   "source": [
    "The `sort_index()` method can also sort columns alphabetically:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "284",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_index(axis=1).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "285",
   "metadata": {},
   "source": [
    "This can make selection with `loc` easier for many columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "286",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_index(axis=1).head().loc[:,'temp_C':'temp_F_whole']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "287",
   "metadata": {},
   "source": [
    "We must sort the index to compare two dataframes. If the index is different, but the data is the same, they will be marked not-equal:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "288",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.equals(df.sort_values(by='temp_C'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "289",
   "metadata": {},
   "source": [
    "Sorting the index solves this issue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "290",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.equals(df.sort_values(by='temp_C').sort_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "291",
   "metadata": {},
   "source": [
    "Let's set the `date` column as our index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "292",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.set_index('date', inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "293",
   "metadata": {},
   "source": [
    "Now that we have an index of type `DatetimeIndex`, we can do datetime slicing and indexing.\n",
    "As long as we provide a date format that pandas understands, we can grab the data.\n",
    "To select all of 2018, we simply use `df.loc['2018']`, for the fourth quarter of 2018 we can use `df.loc['2018-Q4']`, grabbing October is as simple as using `df.loc['2018-10']`; these can also be combined to build ranges.\n",
    "\n",
    "Let's grab October 11, 2018 through October 12, 2018 (inclusive of both endpoints)&mdash;note that using `loc[]` is optional for ranges:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "294",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['2018-10-11':'2018-10-12']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "295",
   "metadata": {},
   "source": [
    "We can also use `reset_index()` to get a fresh index and move our current index into a column for safe keeping. This is especially useful if we had data, such as the date, in the index that we don't want to lose:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "296",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['2018-10-11':'2018-10-12'].reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "297",
   "metadata": {},
   "source": [
    "Reindexing allows us to conform our axis to contain a given set of labels.\n",
    "\n",
    "Let's turn to the S&P 500 stock data in the `sp500.csv` file to see an example of this.\n",
    "Notice we only have data for trading days (weekdays, excluding holidays):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "298",
   "metadata": {},
   "outputs": [],
   "source": [
    "sp = pd.read_csv(\n",
    "    'data/02/sp500.csv', index_col='date', parse_dates=True\n",
    ").drop(columns=['adj_close'])\n",
    "\n",
    "sp.head(10).assign(\n",
    "    day_of_week=lambda x: x.index.day_name()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "299",
   "metadata": {},
   "source": [
    "If we want to look at the value of a portfolio (group of assets) that trade on different days, we need to handle the mismatch in the index.\n",
    "Bitcoin, for example, trades daily.\n",
    "If we sum up all the data we have for each day (aggregations will be covered in chapter 4, so don't fixate on this part), we get the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "300",
   "metadata": {},
   "outputs": [],
   "source": [
    "bitcoin = pd.read_csv(\n",
    "    'data/02/bitcoin.csv', index_col='date', parse_dates=True\n",
    ").drop(columns=['market_cap'])\n",
    "\n",
    "# every day's closing price = S&P 500 close + Bitcoin close (same for other metrics)\n",
    "portfolio = pd.concat([sp, bitcoin], sort=False).groupby(level='date').sum()\n",
    "\n",
    "portfolio.head(10).assign(\n",
    "    day_of_week=lambda x: x.index.day_name()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "301",
   "metadata": {},
   "source": [
    "It may not be immediately obvious what is wrong with the previous data, but with a visualization we can easily see the cyclical pattern of drops on the days the stock market is closed.\n",
    "\n",
    "We will need to import `matplotlib` now:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "302",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.ticker import StrMethodFormatter"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "303",
   "metadata": {},
   "source": [
    "Now we can see why we need to reindex:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "304",
   "metadata": {},
   "outputs": [],
   "source": [
    "# plot the closing price from Q4 2017 through Q2 2018\n",
    "ax = portfolio['2017-Q4':'2018-Q2'].plot(\n",
    "    y='close', figsize=(15, 5), legend=False,\n",
    "    title='Bitcoin + S&P 500 value without accounting for different indices'\n",
    ")\n",
    "\n",
    "# formatting\n",
    "ax.set_ylabel('price')\n",
    "ax.yaxis.set_major_formatter(StrMethodFormatter('${x:,.0f}'))\n",
    "for spine in ['top', 'right']:\n",
    "    ax.spines[spine].set_visible(False)\n",
    "\n",
    "# show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "305",
   "metadata": {},
   "source": [
    "We need to align the index of the S&P 500 to match bitcoin in order to fix this.\n",
    "We will use the `reindex()` method, but by default we get `NaN` for the values that we don't have data for:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "306",
   "metadata": {},
   "outputs": [],
   "source": [
    "sp.reindex(bitcoin.index).head(10).assign(\n",
    "    day_of_week=lambda x: x.index.day_name()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "307",
   "metadata": {},
   "source": [
    "So now we have rows for every day of the year, but all the weekends and holidays have `NaN` values.\n",
    "To address this, we can specify how to handle missing values with the `method` argument.\n",
    "In this case, we want to forward-fill, which will put the weekend and holiday values as the value they had for the Friday (or end of trading week) before:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "308",
   "metadata": {},
   "outputs": [],
   "source": [
    "sp.reindex(bitcoin.index, method='ffill').head(10)\n",
    "    .assign(day_of_week=lambda x: x.index.day_name())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "309",
   "metadata": {},
   "source": [
    "To isolate the changes happening with the forward-filling, we can use the `compare()` method.\n",
    "It shows us the values that differ across identically-labeled dataframes (same names and same columns).\n",
    "Here, we can see that only weekends and holidays (Monday, January 16, 2017 was MLK day) have values forward-filled.\n",
    "Consecutive days have the same values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "310",
   "metadata": {},
   "outputs": [],
   "source": [
    "sp.reindex(bitcoin.index)\n",
    "    .compare(sp.reindex(bitcoin.index, method='ffill'))\n",
    "    .head(10).assign(day_of_week=lambda x: x.index.day_name())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "311",
   "metadata": {},
   "source": [
    "This isn't perfect though.\n",
    "We probably want 0 for the volume traded and to put the closing price for the open, high, low, and close on the days the market is closed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "312",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "sp_reindexed = sp.reindex(bitcoin.index).assign(\n",
    "    volume=lambda x: x.volume.ffill(0), # put 0 when market is closed\n",
    "    close=lambda x: x.close.ffill(), # carry this forward\n",
    "    # take the closing price if these aren't available\n",
    "    open=lambda x: np.where(x.open.isnull(), x.close, x.open),\n",
    "    high=lambda x: np.where(x.high.isnull(), x.close, x.high),\n",
    "    low=lambda x: np.where(x.low.isnull(), x.close, x.low)\n",
    ")\n",
    "\n",
    "sp_reindexed.head(10).assign(\n",
    "    day_of_week=lambda x: x.index.day_name()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "313",
   "metadata": {},
   "source": [
    "If we create a visualization comparing the reindexed data to the first attempt, we see how reindexing helped maintain the asset value when the market was closed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "314",
   "metadata": {},
   "outputs": [],
   "source": [
    "# every day's closing price = S&P 500 close adjusted for market closure + Bitcoin close (same for other metrics)\n",
    "fixed_portfolio = sp_reindexed + bitcoin\n",
    "\n",
    "# plot the reindexed portfolio's closing price from Q4 2017 through Q2 2018\n",
    "ax = fixed_portfolio['2017-Q4':'2018-Q2'].plot(\n",
    "    y='close', label='reindexed portfolio of S&P 500 + Bitcoin', figsize=(15, 5), linewidth=2, \n",
    "    title='Reindexed portfolio vs. portfolio with mismatched indices'\n",
    ")\n",
    "\n",
    "# add line for original portfolio for comparison\n",
    "portfolio['2017-Q4':'2018-Q2'].plot(\n",
    "    y='close', ax=ax, linestyle='--', label='portfolio of S&P 500 + Bitcoin w/o reindexing'\n",
    ")\n",
    "\n",
    "# formatting\n",
    "ax.set_ylabel('price')\n",
    "ax.yaxis.set_major_formatter(StrMethodFormatter('${x:,.0f}'))\n",
    "for spine in ['top', 'right']:\n",
    "    ax.spines[spine].set_visible(False)\n",
    "\n",
    "# show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "315",
   "metadata": {},
   "source": [
    "## Reshaping data\n",
    "\n",
    "Let's import some data to showcase a few examples of reshaping:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "316",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "long_df = pd.read_csv(\n",
    "    'data/02/long_data.csv', usecols=['date', 'datatype', 'value']\n",
    ").rename(\n",
    "    columns={'value': 'temp_C'}\n",
    ").assign(\n",
    "    date=lambda x: pd.to_datetime(x.date),\n",
    "    temp_F=lambda x: (x.temp_C * 9/5) + 32\n",
    ")\n",
    "\n",
    "long_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "317",
   "metadata": {},
   "source": [
    "### Transposing\n",
    "Transposing swaps the rows and the columns. We use the `T` attribute to do so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "318",
   "metadata": {},
   "outputs": [],
   "source": [
    "long_df.set_index('date').head(6).T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "319",
   "metadata": {},
   "source": [
    "### Pivoting\n",
    "\n",
    "#### `pivot()`\n",
    "We can restructure our data by picking a column to go in the index (`index`), a column whose unique values will become column names (`columns`), and the values to place in those columns (`values`).\n",
    "The `pivot()` method can be used when we don't need to perform any aggregation in addition to our restructuring (when our index is unique); if this is not the case, we need the `pivot_table()` method which we will cover in the [next chapter](#Data-aggregation). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "320",
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted_df = long_df.pivot(\n",
    "    index='date', columns='datatype', values='temp_C'\n",
    ")\n",
    "pivoted_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "321",
   "metadata": {},
   "source": [
    "Now that the data is pivoted, we have wide format data that we can grab summary statistics with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "322",
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted_df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "323",
   "metadata": {},
   "source": [
    "We can also provide multiple values to pivot on, which will result in a hierarchical index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "324",
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted_df = long_df.pivot(\n",
    "    index='date', columns='datatype', values=['temp_C', 'temp_F']\n",
    ")\n",
    "pivoted_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "325",
   "metadata": {},
   "source": [
    "With the hierarchical index, if we want to select `TMIN` in Fahrenheit, we will first need to select `temp_F` and then `TMIN`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "326",
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted_df['temp_F']['TMIN'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "327",
   "metadata": {},
   "source": [
    "#### `unstack()`\n",
    "\n",
    "We have been working with a single index throughout this chapter; however, we can create an index from any number of columns with `set_index()`.\n",
    "This gives us an index of type `MultiIndex`, where the outermost level corresponds to the first element in the list provided to `set_index()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "328",
   "metadata": {},
   "outputs": [],
   "source": [
    "multi_index_df = long_df.set_index(['date', 'datatype'])\n",
    "multi_index_df.head().index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "329",
   "metadata": {},
   "source": [
    "Notice there are now 2 index sections of the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "330",
   "metadata": {},
   "outputs": [],
   "source": [
    "multi_index_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "331",
   "metadata": {},
   "source": [
    "With an index of type `MultiIndex`, we can no longer use `pivot()`. We must now use `unstack()`, which by default moves the innermost index onto the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "332",
   "metadata": {},
   "outputs": [],
   "source": [
    "unstacked_df = multi_index_df.unstack()\n",
    "unstacked_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "333",
   "metadata": {},
   "source": [
    "The `unstack()` method also provides the `fill_value` parameter, which let's us fill-in any `NaN` values that might arise from this restructuring of the data. Consider the case that we have data for the average temperature on October 1, 2018, but no other date:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "334",
   "metadata": {},
   "outputs": [],
   "source": [
    "extra_data = pd.DataFrame([{\n",
    "    'datatype': 'TAVG', \n",
    "    'date': '2018-10-01', \n",
    "    'temp_C': 10, \n",
    "    'temp_F': 50\n",
    "}]).set_index(['date', 'datatype']).sort_index()\n",
    "\n",
    "extra_data = pd.concat([long_df, extra_data])\n",
    "\n",
    "extra_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "335",
   "metadata": {},
   "source": [
    "If we use `unstack()` in this case, we will have `NaN` for the `TAVG` columns every day but October 1, 2018:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "336",
   "metadata": {},
   "outputs": [],
   "source": [
    "extra_data.unstack().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "337",
   "metadata": {},
   "source": [
    "To address this, we can pass in an appropriate `fill_value`. However, we are restricted to passing in a value for this, not a strategy (like we saw with `fillna()`), so while `-40` is definitely not be the best value, we can use it to illustrate how this works, since this is the temperature at which Fahrenheit and Celsius are equal:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "338",
   "metadata": {},
   "outputs": [],
   "source": [
    "extra_data.unstack(fill_value=-40).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "339",
   "metadata": {},
   "source": [
    "### Melting\n",
    "\n",
    "Going from wide to long format.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "340",
   "metadata": {},
   "outputs": [],
   "source": [
    "wide_df = pd.read_csv('data/02/wide_data.csv')\n",
    "wide_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "341",
   "metadata": {},
   "source": [
    "#### `melt()`\n",
    "In order to go from wide format to long format, we use the `melt()` method. We have to specify:\n",
    "- `id_vars`: which column(s) uniquely identify a row in the wide format (`date`, here)\n",
    "- `value_vars`: the column(s) that contain(s) the values (`TMAX`, `TMIN`, and `TOBS`, here)\n",
    "\n",
    "Optionally, we can also provide:\n",
    "- `value_name`: what to call the column that will contain all the values once melted\n",
    "- `var_name`: what to call the column that will contain the names of the variables being measured"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "342",
   "metadata": {},
   "outputs": [],
   "source": [
    "melted_df = wide_df.melt(\n",
    "    id_vars='date',\n",
    "    value_vars=['TMAX', 'TMIN', 'TOBS'],\n",
    "    value_name='temp_C',\n",
    "    var_name='measurement'\n",
    ")\n",
    "melted_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "343",
   "metadata": {},
   "source": [
    "#### `stack()`\n",
    "Another option is `stack()`, which will pivot the columns of the dataframe into the innermost level of the index (resulting in an index of type `MultiIndex`). To illustrate this, let's set our index to be the `date` column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "344",
   "metadata": {},
   "outputs": [],
   "source": [
    "wide_df.set_index('date', inplace=True)\n",
    "wide_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "345",
   "metadata": {},
   "source": [
    "By running `stack()` now, we will create a second level in our index which will contain the column names of our dataframe (`TMAX`, `TMIN`, `TOBS`). This will leave us with a `Series` object containing the values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "346",
   "metadata": {},
   "outputs": [],
   "source": [
    "stacked_series = wide_df.stack()\n",
    "stacked_series.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "347",
   "metadata": {},
   "source": [
    "We can use the `to_frame()` method on our `Series` object to turn it into a `DataFrame` object. Since the series doesn't have a name at the moment, we will pass in the name as an argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "348",
   "metadata": {},
   "outputs": [],
   "source": [
    "stacked_df = stacked_series.to_frame('values')\n",
    "stacked_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "349",
   "metadata": {},
   "source": [
    "Once again, we have an index of type `MultiIndex`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "350",
   "metadata": {},
   "outputs": [],
   "source": [
    "stacked_df.head().index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "351",
   "metadata": {},
   "source": [
    "Unfortunately, we don't have a name for the `datatype` level:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "352",
   "metadata": {},
   "outputs": [],
   "source": [
    "stacked_df.index.names"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "353",
   "metadata": {},
   "source": [
    "We can use `set_names()` to address this though:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "354",
   "metadata": {},
   "outputs": [],
   "source": [
    "stacked_df.index.set_names(['date', 'datatype'], inplace=True)\n",
    "stacked_df.index.names"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "355",
   "metadata": {},
   "source": [
    "## Exercises (2)\n",
    "\n",
    "We want to look at data for the FAANG stocks (Facebook, Apple, Amazon, Netflix, and Google), but we were given each as a separate CSV file."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "356",
   "metadata": {},
   "source": [
    "**Exercise 1**\n",
    "\n",
    "\n",
    "1. Read each file in.\n",
    "2. Add a column to each dataframe indicating the ticker it is for.\n",
    "3. Append them together into a single dataframe.\n",
    "4. Save the result to a CSV file named `faang.csv` in the folder `data/02/exercises`.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "357",
   "metadata": {},
   "source": [
    "**Exercise 2**\n",
    "\n",
    "With `faang`, use type conversion to cast the values of the date column into\n",
    "datetimes and the `volume` column into integers. Then, sort by `date` and `ticker`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "358",
   "metadata": {},
   "source": [
    "**Exercise 3**\n",
    "\n",
    "Find the seven rows in `faang` with the lowest value for `volume`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "359",
   "metadata": {},
   "source": [
    "# Data aggregation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "360",
   "metadata": {},
   "source": [
    "We are going to use some daily weather data of New York City in 2018 collected from the [National Centers for Environmental Information (NCEI) API](https://www.ncdc.noaa.gov/cdo-web/webservices/v2)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "361",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "weather = pd.read_csv('data/03/nyc_weather_2018.csv')\n",
    "weather.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "362",
   "metadata": {},
   "source": [
    "### Querying"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "363",
   "metadata": {},
   "source": [
    "The `query()` method filters a `DataFrame` based on some criteria.\n",
    "For example, we can use it to find all entries where snow was recorded from a station with `US1NY` in its station ID:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "364",
   "metadata": {},
   "outputs": [],
   "source": [
    "snow_data = weather.query('datatype == \"SNOW\" and value > 0 and station.str.contains(\"US1NY\")')\n",
    "snow_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "365",
   "metadata": {},
   "source": [
    "This can be equivalently performed with a SQL query on the `weather.db` database file, if you're familiar with it:\n",
    "\n",
    "```sql\n",
    "SELECT * \n",
    "FROM weather \n",
    "WHERE datatype == \"SNOW\" AND value > 0 AND station LIKE \"%US1NY%\"\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "366",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect('data/weather.db') as connection:\n",
    "    snow_data_from_db = pd.read_sql(\n",
    "        'SELECT * FROM weather WHERE datatype == \"SNOW\" AND value > 0 and station LIKE \"%US1NY%\"', \n",
    "        connection\n",
    "    )\n",
    "\n",
    "snow_data.reset_index().drop(columns='index').equals(snow_data_from_db)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "367",
   "metadata": {},
   "source": [
    "Note this is also equivalent to creating Boolean masks:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "368",
   "metadata": {},
   "outputs": [],
   "source": [
    "weather[\n",
    "    (weather.datatype == 'SNOW') \n",
    "    & (weather.value > 0)\n",
    "    & weather.station.str.contains('US1NY')\n",
    "].equals(snow_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "369",
   "metadata": {},
   "source": [
    "### Merging"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "370",
   "metadata": {},
   "source": [
    "We have data for many different stations each day; however, we don't know what the stations are, just their IDs.\n",
    "We can join the data in the `weather_stations.csv` file which contains information from the `stations` endpoint of the NCEI API. It looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "371",
   "metadata": {},
   "outputs": [],
   "source": [
    "station_info = pd.read_csv('data/03/weather_stations.csv')\n",
    "station_info.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "372",
   "metadata": {},
   "source": [
    "And here's our `weather` dataset again:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "373",
   "metadata": {},
   "outputs": [],
   "source": [
    "weather.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "374",
   "metadata": {},
   "source": [
    "We can join our data by matching up the `station_info.id` column with the `weather.station` column.\n",
    "Before doing that though, let's see how many unique values we have:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "375",
   "metadata": {},
   "outputs": [],
   "source": [
    "station_info.id.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "376",
   "metadata": {},
   "source": [
    "While `station_info` has one row per station, the `weather` dataframe has many entries per station.\n",
    "Notice it also has fewer uniques:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "377",
   "metadata": {},
   "outputs": [],
   "source": [
    "weather.station.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "378",
   "metadata": {},
   "source": [
    "When working with joins, it is important to keep an eye on the row count. Some join types will lead to data loss. Remember that we can get this with `shape`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "379",
   "metadata": {},
   "outputs": [],
   "source": [
    "station_info.shape[0], weather.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "380",
   "metadata": {},
   "source": [
    "Since we will be doing this often, it makes more sense to write a function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "381",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_row_count(*dfs):\n",
    "    return [df.shape[0] for df in dfs]\n",
    "    \n",
    "get_row_count(station_info, weather)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "382",
   "metadata": {},
   "source": [
    "By default, `merge()` performs an inner join.\n",
    "0We simply specify the columns to use for the join.\n",
    "The left dataframe is the one we call `merge()` on, and the right one is passed in as an argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "383",
   "metadata": {},
   "outputs": [],
   "source": [
    "inner_join = weather.merge(station_info, left_on='station', right_on='id')\n",
    "inner_join.sample(5, random_state=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "384",
   "metadata": {},
   "source": [
    "We can remove the duplication of information in the `station` and `id` columns by renaming one of them before the merge and then simply using `on`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "385",
   "metadata": {},
   "outputs": [],
   "source": [
    "weather.merge(station_info.rename(dict(id='station'), axis=1), on='station').sample(5, random_state=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "386",
   "metadata": {},
   "source": [
    "We are losing stations that don't have weather observations associated with them, if we don't want to lose these rows, we perform a right or left join instead of the inner join:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "387",
   "metadata": {},
   "outputs": [],
   "source": [
    "left_join = station_info.merge(weather, left_on='id', right_on='station', how='left')\n",
    "right_join = weather.merge(station_info, left_on='station', right_on='id', how='right')\n",
    "\n",
    "right_join[right_join.datatype.isna()].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "388",
   "metadata": {},
   "source": [
    "The left and right join as we performed above are equivalent because the side for which we kept the rows without matches was the same in both cases:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "389",
   "metadata": {},
   "outputs": [],
   "source": [
    "left_join.sort_index(axis=1).sort_values(['date', 'station'], ignore_index=True).equals(\n",
    "    right_join.sort_index(axis=1).sort_values(['date', 'station'], ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "390",
   "metadata": {},
   "source": [
    "Note we have additional rows in the left and right joins because we kept all the stations that didn't have weather observations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "391",
   "metadata": {},
   "outputs": [],
   "source": [
    "get_row_count(inner_join, left_join, right_join)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "392",
   "metadata": {},
   "source": [
    "If we query the station information for stations that have `US1NY` in their ID and perform an outer join, we can see where the mismatches occur:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "393",
   "metadata": {},
   "outputs": [],
   "source": [
    "outer_join = weather.merge(\n",
    "    station_info[station_info.id.str.contains('US1NY')], \n",
    "    left_on='station', right_on='id', how='outer', indicator=True\n",
    ")\n",
    "\n",
    "pd.concat([\n",
    "    outer_join.query(f'_merge == \"{kind}\"').sample(2, random_state=0) \n",
    "    for kind in outer_join._merge.unique()\n",
    "]).sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "394",
   "metadata": {},
   "source": [
    "## `DataFrame` operations\n",
    "\n",
    "We can perform many kinds of operations on a `DataFrame`.\n",
    "A few examples are arithmetics calculations, windowing, binning, or applying custom functions to the data.\n",
    "\n",
    "Let's get the data we will be using first:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "395",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "weather = pd.read_csv('data/03/nyc_weather_2018.csv', parse_dates=['date'])\n",
    "weather.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "396",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb = pd.read_csv('data/03/fb_2018.csv', index_col='date', parse_dates=True)\n",
    "fb.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "397",
   "metadata": {},
   "source": [
    "### Arithmetics and statistics\n",
    "\n",
    "We know already that `+` or `/` operations work on dataframes directly.\n",
    "There are also other operations that can be performed on entire axes.\n",
    "By default, the calculation is performed **on columns**.\n",
    "\n",
    "For example, let's find the Z-scores for the volume traded and look at the days where this was more than 3 standard deviations from the mean:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "398",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb.assign(\n",
    "    abs_z_score_volume=lambda x: \\\n",
    "        x.volume.sub(x.volume.mean()).div(x.volume.std()).abs()\n",
    ").query('abs_z_score_volume > 3')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "399",
   "metadata": {},
   "source": [
    "We can use `rank()` and `pct_change()` to see which days had the largest change in volume traded from the day before:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "400",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb.assign(\n",
    "    volume_pct_change=fb.volume.pct_change(),\n",
    "    pct_change_rank=lambda x: x.volume_pct_change.abs().rank(ascending=False)\n",
    ").nsmallest(5, 'pct_change_rank')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "401",
   "metadata": {},
   "source": [
    "January 12th was when the news that Facebook changed its news feed product to focus more on content from a users' friends over the brands they follow. Given that Facebook's advertising is a key component of its business ([nearly 89% in 2017](https://www.investopedia.com/ask/answers/120114/how-does-facebook-fb-make-money.asp)), many shares were sold and the price dropped in panic:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "402",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb['2018-01-11':'2018-01-12']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "403",
   "metadata": {},
   "source": [
    "Throughout 2018, Facebook's stock price never had a low above $215:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "404",
   "metadata": {},
   "outputs": [],
   "source": [
    "(fb > 215).any()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "405",
   "metadata": {},
   "source": [
    "Facebook's OHLC (open, high, low, and close) prices all had at least one day they were at $215 or less:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "406",
   "metadata": {},
   "outputs": [],
   "source": [
    "(fb > 215).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "407",
   "metadata": {},
   "source": [
    "### Binning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "408",
   "metadata": {},
   "source": [
    "When working with volume traded, we may be interested in ranges of volume rather than the exact values. No two days have the same volume traded:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "409",
   "metadata": {},
   "outputs": [],
   "source": [
    "(fb.volume.value_counts() > 1).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "410",
   "metadata": {},
   "source": [
    "We can use `pd.cut()` to create 3 bins of even range in volume traded and name them.\n",
    "Then we can work with low, medium, and high volume traded categories:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "411",
   "metadata": {},
   "outputs": [],
   "source": [
    "volume_binned = pd.cut(fb.volume, bins=3, labels=['low', 'med', 'high'])\n",
    "volume_binned.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "412",
   "metadata": {},
   "source": [
    "Let's look at the days with high trading volume:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "413",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb[volume_binned == 'high'].sort_values('volume', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "414",
   "metadata": {},
   "source": [
    "July 25th Facebook announced disappointing user growth and the stock tanked in the after hours:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "415",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb['2018-07-25':'2018-07-26']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "416",
   "metadata": {},
   "source": [
    "Cambridge Analytica scandal broke on Saturday, March 17th, so we look at the Monday after for the numbers:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "417",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb['2018-03-16':'2018-03-20']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "418",
   "metadata": {},
   "source": [
    "Since most days have similar volume, but a few are very large, we have very wide bins.\n",
    "Most of the data is in the low bin. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "419",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tutorial.pandas_helpers import low_med_high_bins_viz\n",
    "\n",
    "low_med_high_bins_viz(\n",
    "    fb, 'volume', ylabel='volume traded',\n",
    "    title='Daily Volume Traded of Facebook Stock in 2018 (with bins)'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "420",
   "metadata": {},
   "source": [
    "If we split using quantiles, the bins will have roughly the same number of observations.\n",
    "For this, we use `qcut()`. We will make 4 quartiles:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "421",
   "metadata": {},
   "outputs": [],
   "source": [
    "volume_qbinned = pd.qcut(fb.volume, q=4, labels=['q1', 'q2', 'q3', 'q4'])\n",
    "volume_qbinned.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "422",
   "metadata": {},
   "source": [
    "Notice the bins don't cover ranges of the same size anymore:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "423",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tutorial.pandas_helpers import quartile_bins_viz\n",
    "\n",
    "quartile_bins_viz(\n",
    "    fb, 'volume', ylabel='volume traded', \n",
    "    title='Daily Volume Traded of Facebook Stock in 2018 (with quartile bins)'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "424",
   "metadata": {},
   "source": [
    "### Applying functions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "425",
   "metadata": {},
   "source": [
    "We can use the `apply()` method to run the same operation on all columns (or rows) of the dataframe.\n",
    "First, let's isolate the weather observations from the Central Park station and pivot the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "426",
   "metadata": {},
   "outputs": [],
   "source": [
    "central_park_weather = weather\\\n",
    "    .query('station == \"GHCND:USW00094728\"')\\\n",
    "    .pivot(index='date', columns='datatype', values='value')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "427",
   "metadata": {},
   "source": [
    "Let's calculate the Z-scores of the TMIN, TMAX, and PRCP observations in Central Park in October 2018:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "428",
   "metadata": {},
   "outputs": [],
   "source": [
    "oct_weather_z_scores = central_park_weather\\\n",
    "    .loc['2018-10', ['TMIN', 'TMAX', 'PRCP']]\\\n",
    "    .apply(lambda x: x.sub(x.mean()).div(x.std()))\n",
    "oct_weather_z_scores.describe().T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "429",
   "metadata": {},
   "source": [
    "October 27th rained much more than the rest of the days:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "430",
   "metadata": {},
   "outputs": [],
   "source": [
    "oct_weather_z_scores.query('PRCP > 3').PRCP"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "431",
   "metadata": {},
   "source": [
    "Indeed, this day was much higher than the rest:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "432",
   "metadata": {},
   "outputs": [],
   "source": [
    "central_park_weather.loc['2018-10', 'PRCP'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "433",
   "metadata": {},
   "source": [
    "When the function we want to apply isn't vectorized, we can:\n",
    "- use `np.vectorize()` to vectorize it (similar to how `map()` works) and then use it with `apply()`\n",
    "- use `applymap()` and pass it the non-vectorized function directly\n",
    "\n",
    "Say we wanted to count the digits of the whole numbers for the Facebook data; `len()` is not vectorized, so we can use `np.vectorize()` or `applymap()`.\n",
    "\n",
    "*Note: in the most recent versions of Pandas, `applymap()` has been deprecated in favor or `map()`.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "434",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb.apply(\n",
    "    lambda x: np.vectorize(lambda y: len(str(np.ceil(y))))(x)\n",
    ").astype('int64').equals(\n",
    "    fb.map(lambda x: len(str(np.ceil(x))))\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "435",
   "metadata": {},
   "source": [
    "**A note about performance**\n",
    "\n",
    "A simple operation of addition to each element in a `Series` grows linearly in time complexity when using [`items()`](https://pandas.pydata.org/docs/reference/api/pandas.Series.items.html#pandas.Series.items), but stays near 0 when using vectorized operations.\n",
    "`items()` returns a **lazy tuple** `(index, value)`, it should only be used if there is no vectorized solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "436",
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "np.random.seed(0)\n",
    "\n",
    "vectorized_results = {}\n",
    "iteritems_results = {}\n",
    "\n",
    "for size in [10, 100, 1000, 10000, 100000, 500000, 1000000, 5000000, 10000000]:\n",
    "    # set of numbers to use\n",
    "    test = pd.Series(np.random.uniform(size=size))\n",
    "    \n",
    "    # time the vectorized operation\n",
    "    start = time.time()\n",
    "    x = test + 10\n",
    "    end = time.time()\n",
    "    vectorized_results[size] = end - start\n",
    "    \n",
    "    # time the operation with `items()`\n",
    "    start = time.time()\n",
    "    x = []\n",
    "    for i, v in test.items():\n",
    "        x.append(v + 10)\n",
    "    x = pd.Series(x)\n",
    "    end = time.time()\n",
    "    iteritems_results[size] = end - start\n",
    "\n",
    "results = pd.DataFrame(\n",
    "    [pd.Series(vectorized_results, name='vectorized'), pd.Series(iteritems_results, name='iteritems')]\n",
    ").T    \n",
    "\n",
    "# plotting\n",
    "ax = results.plot(title='Time Complexity', color=['blue', 'red'], legend=False)\n",
    "\n",
    "# formatting\n",
    "ax.set(xlabel='item size (rows)', ylabel='time (s)')\n",
    "ax.text(0.5e7, iteritems_results[0.5e7] * .9, 'iteritems()', rotation=34, color='red', fontsize=12, ha='center', va='bottom')\n",
    "ax.text(0.5e7, vectorized_results[0.5e7], 'vectorized', color='blue', fontsize=12, ha='center', va='bottom')\n",
    "for spine in ['top', 'right']:\n",
    "    ax.spines[spine].set_visible(False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "437",
   "metadata": {},
   "source": [
    "## Using `groupby()`\n",
    "Often we won't want to aggregate on the entire dataframe, but on groups within it.\n",
    "For this purpose, we can run `groupby()` before the aggregation.\n",
    "If we group by the `trading_volume` column, we will get a row for each of the values it takes on:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "438",
   "metadata": {},
   "outputs": [],
   "source": [
    "weather = pd.read_csv('data/03/weather_by_station.csv', index_col='date', parse_dates=True)\n",
    "\n",
    "weather.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "439",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb = pd.read_csv('data/03/fb_2018.csv', index_col='date', parse_dates=True).assign(\n",
    "    trading_volume=lambda x: pd.cut(x.volume, bins=3, labels=['low', 'med', 'high'])\n",
    ")\n",
    "\n",
    "fb.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "440",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb.groupby('trading_volume', observed=True).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "441",
   "metadata": {},
   "source": [
    "After we call `groupby()`, we can still select columns for aggregation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "442",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb.groupby('trading_volume', observed=True)['close'].agg(['min', 'max', 'mean'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "443",
   "metadata": {},
   "source": [
    "We can still provide a dictionary specifying the aggregations to perform, but passing a list for a column will result in a hierarchical index for the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "444",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb_agg = fb.groupby('trading_volume', observed=True).agg({\n",
    "    'open': 'mean',\n",
    "    'high': ['min', 'max'],\n",
    "    'low': ['min', 'max'],\n",
    "    'close': 'mean'\n",
    "})\n",
    "fb_agg"
   ]
  },
  {
   "cell_type": "raw",
   "id": "445",
   "metadata": {},
   "source": [
    "The hierarchical index in the columns looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "446",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb_agg.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "447",
   "metadata": {},
   "source": [
    "Using a list comprehension, we can join the levels (in a tuple) with an `_` at each iteration: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "448",
   "metadata": {},
   "outputs": [],
   "source": [
    "fb_agg.columns = ['_'.join(col_agg) for col_agg in fb_agg.columns]\n",
    "fb_agg.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "449",
   "metadata": {},
   "source": [
    "## Exercises (3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "450",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "451",
   "metadata": {},
   "source": [
    "For these exercises, you will be using the following datasets:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "452",
   "metadata": {},
   "outputs": [],
   "source": [
    "quakes = pd.read_csv('data/03/exercises/earthquakes.csv')\n",
    "faang = pd.read_csv('data/03/exercises/faang.csv', index_col='date', parse_dates=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "453",
   "metadata": {},
   "source": [
    "*The `faang.csv` dataset is a copy of what you should've obtained in Exercise 1 of the previous section.*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "454",
   "metadata": {},
   "source": [
    "**Exercise 1**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "455",
   "metadata": {},
   "source": [
    "With the `earthquakes.csv file`, select all the earthquakes in Japan with a\n",
    "magnitude of `4.9` or greater using the `mb` magnitude type."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "456",
   "metadata": {},
   "source": [
    "**Exercise 2**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "457",
   "metadata": {},
   "source": [
    "Create bins for each full number of magnitude (for example, the first bin is `(0, 1]`, the second is `(1, 2]`, and so on) with the `ml` magnitude type and count how many are in each bin."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "458",
   "metadata": {},
   "source": [
    "**Exercise 3**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "459",
   "metadata": {},
   "source": [
    "Using the `faang.csv` file:\n",
    "\n",
    "1. Group by the ticker and resample to monthly frequency.\n",
    "2. Aggregate the open and close prices with the mean, the high price with the max, the low price with the min, and the volume with the sum.\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <h4><b>Hint</b></h4>\n",
    "    The object returned by <code>groupby()</code> is a <code>DataFrameGroupBy</code>. It supports some aggregation operations, including the most obvious: aggregating by applying a certain function. Have a look <a href=\"https://pandas.pydata.org/docs/reference/api/pandas.core.groupby.DataFrameGroupBy.agg.html\">here</a>.\n",
    "</div>"
   ]
  }
 ],
 "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}