solutions/ch_04/solutions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Solutions\n",
    "\n",
    "## About the Data\n",
    "In this notebook, we will be working with 3 datasets:\n",
    "- 2018 stock data for Facebook, Apple, Amazon, Netflix, and Google (obtained using the [`stock_analysis` package](https://github.com/stefmolin/stock-analysis)) and earthquake data from the USGS API.\n",
    "- Earthquake data from September 18, 2018 - October 13, 2018 (obtained from the US Geological Survey (USGS) using the [USGS API](https://earthquake.usgs.gov/fdsnws/event/1/))\n",
    "- European Centre for Disease Prevention and Control's (ECDC) [daily number of new reported cases of COVID-19 by country worldwide dataset](https://www.ecdc.europa.eu/en/publications-data/download-todays-data-geographic-distribution-covid-19-cases-worldwide) collected on September 19, 2020 via [this link](https://opendata.ecdc.europa.eu/covid19/casedistribution/csv)\n",
    "\n",
    "## Setup\n",
    "Note that the COVID-19 data will be read in later as part of the solution to exercise 10."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "quakes = pd.read_csv('../../ch_04/exercises/earthquakes.csv')\n",
    "faang = pd.read_csv('../../ch_04/exercises/faang.csv', index_col='date', parse_dates=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 1\n",
    "With the `exercises/earthquakes.csv` file, select all the earthquakes in Japan with a of 4.9 or greater using the `mb` magnitude type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mag</th>\n",
       "      <th>magType</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1563</th>\n",
       "      <td>4.9</td>\n",
       "      <td>mb</td>\n",
       "      <td>293km ESE of Iwo Jima, Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2576</th>\n",
       "      <td>5.4</td>\n",
       "      <td>mb</td>\n",
       "      <td>37km E of Tomakomai, Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072</th>\n",
       "      <td>4.9</td>\n",
       "      <td>mb</td>\n",
       "      <td>15km ENE of Hasaki, Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3632</th>\n",
       "      <td>4.9</td>\n",
       "      <td>mb</td>\n",
       "      <td>53km ESE of Hitachi, Japan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      mag magType                         place\n",
       "1563  4.9      mb  293km ESE of Iwo Jima, Japan\n",
       "2576  5.4      mb    37km E of Tomakomai, Japan\n",
       "3072  4.9      mb     15km ENE of Hasaki, Japan\n",
       "3632  4.9      mb    53km ESE of Hitachi, Japan"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "quakes.query(\n",
    "    \"parsed_place == 'Japan' and magType == 'mb' and mag >= 4.9\"\n",
    ")[['mag', 'magType', 'place']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 2\n",
    "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": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, 2]    3105\n",
       "(0, 1]    2207\n",
       "(2, 3]     862\n",
       "(3, 4]     122\n",
       "(4, 5]       2\n",
       "(5, 6]       1\n",
       "(6, 7]       0\n",
       "(7, 8]       0\n",
       "(8, 9]       0\n",
       "Name: mag_bin, dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "quakes.query(\"magType == 'ml'\").assign(\n",
    "    mag_bin=lambda x: pd.cut(x.mag, np.arange(0, 10))\n",
    ").mag_bin.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 3\n",
    "Using the `exercises/faang.csv` file, group by the ticker and resample to monthly frequency. 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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">AAPL</th>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>43.505357</td>\n",
       "      <td>45.025002</td>\n",
       "      <td>41.174999</td>\n",
       "      <td>43.501309</td>\n",
       "      <td>2.638718e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>41.819079</td>\n",
       "      <td>45.154999</td>\n",
       "      <td>37.560001</td>\n",
       "      <td>41.909737</td>\n",
       "      <td>3.711577e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>43.761786</td>\n",
       "      <td>45.875000</td>\n",
       "      <td>41.235001</td>\n",
       "      <td>43.624048</td>\n",
       "      <td>2.854911e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>42.441310</td>\n",
       "      <td>44.735001</td>\n",
       "      <td>40.157501</td>\n",
       "      <td>42.458572</td>\n",
       "      <td>2.664617e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>46.239091</td>\n",
       "      <td>47.592499</td>\n",
       "      <td>41.317501</td>\n",
       "      <td>46.384205</td>\n",
       "      <td>2.483905e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>47.180119</td>\n",
       "      <td>48.549999</td>\n",
       "      <td>45.182499</td>\n",
       "      <td>47.155357</td>\n",
       "      <td>2.110498e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>47.549048</td>\n",
       "      <td>48.990002</td>\n",
       "      <td>45.855000</td>\n",
       "      <td>47.577857</td>\n",
       "      <td>1.574766e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>53.121739</td>\n",
       "      <td>57.217499</td>\n",
       "      <td>49.327499</td>\n",
       "      <td>53.336522</td>\n",
       "      <td>2.801276e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>55.582763</td>\n",
       "      <td>57.417500</td>\n",
       "      <td>53.825001</td>\n",
       "      <td>55.518421</td>\n",
       "      <td>2.715888e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>55.300000</td>\n",
       "      <td>58.367500</td>\n",
       "      <td>51.522499</td>\n",
       "      <td>55.211413</td>\n",
       "      <td>3.158994e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>47.954881</td>\n",
       "      <td>55.590000</td>\n",
       "      <td>42.564999</td>\n",
       "      <td>47.808929</td>\n",
       "      <td>3.845306e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>41.310789</td>\n",
       "      <td>46.235001</td>\n",
       "      <td>36.647499</td>\n",
       "      <td>41.066579</td>\n",
       "      <td>3.595690e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">AMZN</th>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>1301.377151</td>\n",
       "      <td>1472.579956</td>\n",
       "      <td>1170.510010</td>\n",
       "      <td>1309.010946</td>\n",
       "      <td>9.637120e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>1447.113159</td>\n",
       "      <td>1528.699951</td>\n",
       "      <td>1265.930054</td>\n",
       "      <td>1442.363146</td>\n",
       "      <td>1.377840e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>1542.160464</td>\n",
       "      <td>1617.540039</td>\n",
       "      <td>1365.199951</td>\n",
       "      <td>1540.367629</td>\n",
       "      <td>1.304001e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>1475.841902</td>\n",
       "      <td>1638.099976</td>\n",
       "      <td>1352.880005</td>\n",
       "      <td>1468.220471</td>\n",
       "      <td>1.299196e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>1590.474543</td>\n",
       "      <td>1635.000000</td>\n",
       "      <td>1546.020020</td>\n",
       "      <td>1594.903637</td>\n",
       "      <td>7.161550e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>1699.088582</td>\n",
       "      <td>1763.099976</td>\n",
       "      <td>1635.089966</td>\n",
       "      <td>1698.823812</td>\n",
       "      <td>8.594130e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>1786.305716</td>\n",
       "      <td>1880.050049</td>\n",
       "      <td>1678.060059</td>\n",
       "      <td>1784.649042</td>\n",
       "      <td>9.752110e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>1891.957833</td>\n",
       "      <td>2025.569946</td>\n",
       "      <td>1776.020020</td>\n",
       "      <td>1897.851308</td>\n",
       "      <td>9.657580e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>1969.239476</td>\n",
       "      <td>2050.500000</td>\n",
       "      <td>1865.000000</td>\n",
       "      <td>1966.077900</td>\n",
       "      <td>9.444550e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>1799.630865</td>\n",
       "      <td>2033.189941</td>\n",
       "      <td>1476.359985</td>\n",
       "      <td>1782.058265</td>\n",
       "      <td>1.832208e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>1622.323806</td>\n",
       "      <td>1784.000000</td>\n",
       "      <td>1420.000000</td>\n",
       "      <td>1625.483823</td>\n",
       "      <td>1.392900e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>1572.922100</td>\n",
       "      <td>1778.339966</td>\n",
       "      <td>1307.000000</td>\n",
       "      <td>1559.443154</td>\n",
       "      <td>1.548127e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">FB</th>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>184.584284</td>\n",
       "      <td>190.660004</td>\n",
       "      <td>175.800003</td>\n",
       "      <td>184.962856</td>\n",
       "      <td>4.956557e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>180.721578</td>\n",
       "      <td>195.320007</td>\n",
       "      <td>167.179993</td>\n",
       "      <td>180.269473</td>\n",
       "      <td>5.162516e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>173.449524</td>\n",
       "      <td>186.100006</td>\n",
       "      <td>149.020004</td>\n",
       "      <td>173.489522</td>\n",
       "      <td>9.962017e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>164.163332</td>\n",
       "      <td>177.100006</td>\n",
       "      <td>150.509995</td>\n",
       "      <td>163.810476</td>\n",
       "      <td>7.500727e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>181.910909</td>\n",
       "      <td>192.720001</td>\n",
       "      <td>170.229996</td>\n",
       "      <td>182.930000</td>\n",
       "      <td>4.011441e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>194.974763</td>\n",
       "      <td>203.550003</td>\n",
       "      <td>186.429993</td>\n",
       "      <td>195.267620</td>\n",
       "      <td>3.872656e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>199.332381</td>\n",
       "      <td>218.619995</td>\n",
       "      <td>166.559998</td>\n",
       "      <td>199.967142</td>\n",
       "      <td>6.470307e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>177.598695</td>\n",
       "      <td>188.300003</td>\n",
       "      <td>170.270004</td>\n",
       "      <td>177.492172</td>\n",
       "      <td>5.488327e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>164.233158</td>\n",
       "      <td>173.889999</td>\n",
       "      <td>158.869995</td>\n",
       "      <td>164.377368</td>\n",
       "      <td>5.004688e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>154.873479</td>\n",
       "      <td>165.880005</td>\n",
       "      <td>139.029999</td>\n",
       "      <td>154.187826</td>\n",
       "      <td>6.224463e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>141.762857</td>\n",
       "      <td>154.130005</td>\n",
       "      <td>126.849998</td>\n",
       "      <td>141.635715</td>\n",
       "      <td>5.181517e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>137.529475</td>\n",
       "      <td>147.190002</td>\n",
       "      <td>123.019997</td>\n",
       "      <td>137.161052</td>\n",
       "      <td>5.587862e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">GOOG</th>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>1127.200945</td>\n",
       "      <td>1186.890015</td>\n",
       "      <td>1045.229980</td>\n",
       "      <td>1130.770467</td>\n",
       "      <td>2.873840e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>1088.629472</td>\n",
       "      <td>1174.000000</td>\n",
       "      <td>992.559998</td>\n",
       "      <td>1088.206839</td>\n",
       "      <td>4.238200e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>1096.108085</td>\n",
       "      <td>1177.050049</td>\n",
       "      <td>980.640015</td>\n",
       "      <td>1091.490479</td>\n",
       "      <td>4.535330e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>1038.415237</td>\n",
       "      <td>1094.165039</td>\n",
       "      <td>990.369995</td>\n",
       "      <td>1035.696187</td>\n",
       "      <td>4.171590e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>1064.021376</td>\n",
       "      <td>1110.750000</td>\n",
       "      <td>1006.289978</td>\n",
       "      <td>1069.275901</td>\n",
       "      <td>3.184940e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>1136.396182</td>\n",
       "      <td>1186.286011</td>\n",
       "      <td>1096.010010</td>\n",
       "      <td>1137.626668</td>\n",
       "      <td>3.209600e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>1183.464280</td>\n",
       "      <td>1273.890015</td>\n",
       "      <td>1093.800049</td>\n",
       "      <td>1187.590472</td>\n",
       "      <td>3.194010e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>1226.156951</td>\n",
       "      <td>1256.500000</td>\n",
       "      <td>1188.239990</td>\n",
       "      <td>1225.671732</td>\n",
       "      <td>2.880840e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>1176.878424</td>\n",
       "      <td>1212.989990</td>\n",
       "      <td>1146.910034</td>\n",
       "      <td>1175.808934</td>\n",
       "      <td>2.886240e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>1116.082172</td>\n",
       "      <td>1209.959961</td>\n",
       "      <td>995.830017</td>\n",
       "      <td>1110.940411</td>\n",
       "      <td>4.849470e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>1054.971424</td>\n",
       "      <td>1095.569946</td>\n",
       "      <td>996.020020</td>\n",
       "      <td>1056.162394</td>\n",
       "      <td>3.673510e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>1042.619998</td>\n",
       "      <td>1124.650024</td>\n",
       "      <td>970.109985</td>\n",
       "      <td>1037.420519</td>\n",
       "      <td>4.025760e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">NFLX</th>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>231.269525</td>\n",
       "      <td>286.809998</td>\n",
       "      <td>195.419998</td>\n",
       "      <td>232.908096</td>\n",
       "      <td>2.383776e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>270.873158</td>\n",
       "      <td>297.359985</td>\n",
       "      <td>236.110001</td>\n",
       "      <td>271.443683</td>\n",
       "      <td>1.845858e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>312.712859</td>\n",
       "      <td>333.980011</td>\n",
       "      <td>275.899994</td>\n",
       "      <td>312.228097</td>\n",
       "      <td>2.634494e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>309.129524</td>\n",
       "      <td>338.820007</td>\n",
       "      <td>271.220001</td>\n",
       "      <td>307.466192</td>\n",
       "      <td>2.620060e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>329.779541</td>\n",
       "      <td>356.100006</td>\n",
       "      <td>305.730011</td>\n",
       "      <td>331.536819</td>\n",
       "      <td>1.420508e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>384.557143</td>\n",
       "      <td>423.209991</td>\n",
       "      <td>352.820007</td>\n",
       "      <td>384.133336</td>\n",
       "      <td>2.440318e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>380.969526</td>\n",
       "      <td>419.769989</td>\n",
       "      <td>328.000000</td>\n",
       "      <td>381.515238</td>\n",
       "      <td>3.053938e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>345.410001</td>\n",
       "      <td>376.809998</td>\n",
       "      <td>310.929993</td>\n",
       "      <td>346.257824</td>\n",
       "      <td>2.131223e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>363.326843</td>\n",
       "      <td>383.200012</td>\n",
       "      <td>335.829987</td>\n",
       "      <td>362.641576</td>\n",
       "      <td>1.708321e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>340.025218</td>\n",
       "      <td>386.799988</td>\n",
       "      <td>271.209991</td>\n",
       "      <td>335.445652</td>\n",
       "      <td>3.635898e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>290.643335</td>\n",
       "      <td>332.049988</td>\n",
       "      <td>250.000000</td>\n",
       "      <td>290.344764</td>\n",
       "      <td>2.571264e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>266.309474</td>\n",
       "      <td>298.720001</td>\n",
       "      <td>231.229996</td>\n",
       "      <td>265.302630</td>\n",
       "      <td>2.343100e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          open         high          low        close  \\\n",
       "ticker date                                                             \n",
       "AAPL   2018-01-31    43.505357    45.025002    41.174999    43.501309   \n",
       "       2018-02-28    41.819079    45.154999    37.560001    41.909737   \n",
       "       2018-03-31    43.761786    45.875000    41.235001    43.624048   \n",
       "       2018-04-30    42.441310    44.735001    40.157501    42.458572   \n",
       "       2018-05-31    46.239091    47.592499    41.317501    46.384205   \n",
       "       2018-06-30    47.180119    48.549999    45.182499    47.155357   \n",
       "       2018-07-31    47.549048    48.990002    45.855000    47.577857   \n",
       "       2018-08-31    53.121739    57.217499    49.327499    53.336522   \n",
       "       2018-09-30    55.582763    57.417500    53.825001    55.518421   \n",
       "       2018-10-31    55.300000    58.367500    51.522499    55.211413   \n",
       "       2018-11-30    47.954881    55.590000    42.564999    47.808929   \n",
       "       2018-12-31    41.310789    46.235001    36.647499    41.066579   \n",
       "AMZN   2018-01-31  1301.377151  1472.579956  1170.510010  1309.010946   \n",
       "       2018-02-28  1447.113159  1528.699951  1265.930054  1442.363146   \n",
       "       2018-03-31  1542.160464  1617.540039  1365.199951  1540.367629   \n",
       "       2018-04-30  1475.841902  1638.099976  1352.880005  1468.220471   \n",
       "       2018-05-31  1590.474543  1635.000000  1546.020020  1594.903637   \n",
       "       2018-06-30  1699.088582  1763.099976  1635.089966  1698.823812   \n",
       "       2018-07-31  1786.305716  1880.050049  1678.060059  1784.649042   \n",
       "       2018-08-31  1891.957833  2025.569946  1776.020020  1897.851308   \n",
       "       2018-09-30  1969.239476  2050.500000  1865.000000  1966.077900   \n",
       "       2018-10-31  1799.630865  2033.189941  1476.359985  1782.058265   \n",
       "       2018-11-30  1622.323806  1784.000000  1420.000000  1625.483823   \n",
       "       2018-12-31  1572.922100  1778.339966  1307.000000  1559.443154   \n",
       "FB     2018-01-31   184.584284   190.660004   175.800003   184.962856   \n",
       "       2018-02-28   180.721578   195.320007   167.179993   180.269473   \n",
       "       2018-03-31   173.449524   186.100006   149.020004   173.489522   \n",
       "       2018-04-30   164.163332   177.100006   150.509995   163.810476   \n",
       "       2018-05-31   181.910909   192.720001   170.229996   182.930000   \n",
       "       2018-06-30   194.974763   203.550003   186.429993   195.267620   \n",
       "       2018-07-31   199.332381   218.619995   166.559998   199.967142   \n",
       "       2018-08-31   177.598695   188.300003   170.270004   177.492172   \n",
       "       2018-09-30   164.233158   173.889999   158.869995   164.377368   \n",
       "       2018-10-31   154.873479   165.880005   139.029999   154.187826   \n",
       "       2018-11-30   141.762857   154.130005   126.849998   141.635715   \n",
       "       2018-12-31   137.529475   147.190002   123.019997   137.161052   \n",
       "GOOG   2018-01-31  1127.200945  1186.890015  1045.229980  1130.770467   \n",
       "       2018-02-28  1088.629472  1174.000000   992.559998  1088.206839   \n",
       "       2018-03-31  1096.108085  1177.050049   980.640015  1091.490479   \n",
       "       2018-04-30  1038.415237  1094.165039   990.369995  1035.696187   \n",
       "       2018-05-31  1064.021376  1110.750000  1006.289978  1069.275901   \n",
       "       2018-06-30  1136.396182  1186.286011  1096.010010  1137.626668   \n",
       "       2018-07-31  1183.464280  1273.890015  1093.800049  1187.590472   \n",
       "       2018-08-31  1226.156951  1256.500000  1188.239990  1225.671732   \n",
       "       2018-09-30  1176.878424  1212.989990  1146.910034  1175.808934   \n",
       "       2018-10-31  1116.082172  1209.959961   995.830017  1110.940411   \n",
       "       2018-11-30  1054.971424  1095.569946   996.020020  1056.162394   \n",
       "       2018-12-31  1042.619998  1124.650024   970.109985  1037.420519   \n",
       "NFLX   2018-01-31   231.269525   286.809998   195.419998   232.908096   \n",
       "       2018-02-28   270.873158   297.359985   236.110001   271.443683   \n",
       "       2018-03-31   312.712859   333.980011   275.899994   312.228097   \n",
       "       2018-04-30   309.129524   338.820007   271.220001   307.466192   \n",
       "       2018-05-31   329.779541   356.100006   305.730011   331.536819   \n",
       "       2018-06-30   384.557143   423.209991   352.820007   384.133336   \n",
       "       2018-07-31   380.969526   419.769989   328.000000   381.515238   \n",
       "       2018-08-31   345.410001   376.809998   310.929993   346.257824   \n",
       "       2018-09-30   363.326843   383.200012   335.829987   362.641576   \n",
       "       2018-10-31   340.025218   386.799988   271.209991   335.445652   \n",
       "       2018-11-30   290.643335   332.049988   250.000000   290.344764   \n",
       "       2018-12-31   266.309474   298.720001   231.229996   265.302630   \n",
       "\n",
       "                         volume  \n",
       "ticker date                      \n",
       "AAPL   2018-01-31  2.638718e+09  \n",
       "       2018-02-28  3.711577e+09  \n",
       "       2018-03-31  2.854911e+09  \n",
       "       2018-04-30  2.664617e+09  \n",
       "       2018-05-31  2.483905e+09  \n",
       "       2018-06-30  2.110498e+09  \n",
       "       2018-07-31  1.574766e+09  \n",
       "       2018-08-31  2.801276e+09  \n",
       "       2018-09-30  2.715888e+09  \n",
       "       2018-10-31  3.158994e+09  \n",
       "       2018-11-30  3.845306e+09  \n",
       "       2018-12-31  3.595690e+09  \n",
       "AMZN   2018-01-31  9.637120e+07  \n",
       "       2018-02-28  1.377840e+08  \n",
       "       2018-03-31  1.304001e+08  \n",
       "       2018-04-30  1.299196e+08  \n",
       "       2018-05-31  7.161550e+07  \n",
       "       2018-06-30  8.594130e+07  \n",
       "       2018-07-31  9.752110e+07  \n",
       "       2018-08-31  9.657580e+07  \n",
       "       2018-09-30  9.444550e+07  \n",
       "       2018-10-31  1.832208e+08  \n",
       "       2018-11-30  1.392900e+08  \n",
       "       2018-12-31  1.548127e+08  \n",
       "FB     2018-01-31  4.956557e+08  \n",
       "       2018-02-28  5.162516e+08  \n",
       "       2018-03-31  9.962017e+08  \n",
       "       2018-04-30  7.500727e+08  \n",
       "       2018-05-31  4.011441e+08  \n",
       "       2018-06-30  3.872656e+08  \n",
       "       2018-07-31  6.470307e+08  \n",
       "       2018-08-31  5.488327e+08  \n",
       "       2018-09-30  5.004688e+08  \n",
       "       2018-10-31  6.224463e+08  \n",
       "       2018-11-30  5.181517e+08  \n",
       "       2018-12-31  5.587862e+08  \n",
       "GOOG   2018-01-31  2.873840e+07  \n",
       "       2018-02-28  4.238200e+07  \n",
       "       2018-03-31  4.535330e+07  \n",
       "       2018-04-30  4.171590e+07  \n",
       "       2018-05-31  3.184940e+07  \n",
       "       2018-06-30  3.209600e+07  \n",
       "       2018-07-31  3.194010e+07  \n",
       "       2018-08-31  2.880840e+07  \n",
       "       2018-09-30  2.886240e+07  \n",
       "       2018-10-31  4.849470e+07  \n",
       "       2018-11-30  3.673510e+07  \n",
       "       2018-12-31  4.025760e+07  \n",
       "NFLX   2018-01-31  2.383776e+08  \n",
       "       2018-02-28  1.845858e+08  \n",
       "       2018-03-31  2.634494e+08  \n",
       "       2018-04-30  2.620060e+08  \n",
       "       2018-05-31  1.420508e+08  \n",
       "       2018-06-30  2.440318e+08  \n",
       "       2018-07-31  3.053938e+08  \n",
       "       2018-08-31  2.131223e+08  \n",
       "       2018-09-30  1.708321e+08  \n",
       "       2018-10-31  3.635898e+08  \n",
       "       2018-11-30  2.571264e+08  \n",
       "       2018-12-31  2.343100e+08  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "faang.groupby('ticker').resample('1M').agg(\n",
    "    {\n",
    "        'open': np.mean,\n",
    "        'high': np.max,\n",
    "        'low': np.min,\n",
    "        'close': np.mean,\n",
    "        'volume': np.sum\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 4\n",
    "Build a crosstab with the earthquake data between the `tsunami` column and the `magType` column. Rather than showing the frequency count, show the maximum magnitude that was observed for each combination. Put the magnitude type along the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>magType</th>\n",
       "      <th>mb</th>\n",
       "      <th>mb_lg</th>\n",
       "      <th>md</th>\n",
       "      <th>mh</th>\n",
       "      <th>ml</th>\n",
       "      <th>ms_20</th>\n",
       "      <th>mw</th>\n",
       "      <th>mwb</th>\n",
       "      <th>mwr</th>\n",
       "      <th>mww</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tsunami</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.6</td>\n",
       "      <td>3.5</td>\n",
       "      <td>4.11</td>\n",
       "      <td>1.1</td>\n",
       "      <td>4.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.83</td>\n",
       "      <td>5.8</td>\n",
       "      <td>4.8</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.1</td>\n",
       "      <td>5.7</td>\n",
       "      <td>4.41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "magType   mb  mb_lg    md   mh   ml  ms_20    mw  mwb  mwr  mww\n",
       "tsunami                                                        \n",
       "0        5.6    3.5  4.11  1.1  4.2    NaN  3.83  5.8  4.8  6.0\n",
       "1        6.1    NaN   NaN  NaN  5.1    5.7  4.41  NaN  NaN  7.5"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(quakes.tsunami, quakes.magType, values=quakes.mag, aggfunc='max')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 5\n",
    "Calculate the rolling 60-day aggregations of the OHLC data by ticker for the FAANG data. Use the same aggregations as exercise 3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">AAPL</th>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>42.540001</td>\n",
       "      <td>43.075001</td>\n",
       "      <td>42.314999</td>\n",
       "      <td>43.064999</td>\n",
       "      <td>102223600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>42.836250</td>\n",
       "      <td>43.637501</td>\n",
       "      <td>42.314999</td>\n",
       "      <td>43.061249</td>\n",
       "      <td>220295200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>42.935833</td>\n",
       "      <td>43.637501</td>\n",
       "      <td>42.314999</td>\n",
       "      <td>43.126666</td>\n",
       "      <td>310033600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>43.041875</td>\n",
       "      <td>43.842499</td>\n",
       "      <td>42.314999</td>\n",
       "      <td>43.282499</td>\n",
       "      <td>404673600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-08</th>\n",
       "      <td>43.151000</td>\n",
       "      <td>43.902500</td>\n",
       "      <td>42.314999</td>\n",
       "      <td>43.343500</td>\n",
       "      <td>486944800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">NFLX</th>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>283.509251</td>\n",
       "      <td>332.049988</td>\n",
       "      <td>233.679993</td>\n",
       "      <td>281.931750</td>\n",
       "      <td>525657600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>281.844501</td>\n",
       "      <td>332.049988</td>\n",
       "      <td>231.229996</td>\n",
       "      <td>280.777750</td>\n",
       "      <td>520444300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>281.070489</td>\n",
       "      <td>332.049988</td>\n",
       "      <td>231.229996</td>\n",
       "      <td>280.162927</td>\n",
       "      <td>532679500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>279.916342</td>\n",
       "      <td>332.049988</td>\n",
       "      <td>231.229996</td>\n",
       "      <td>279.461464</td>\n",
       "      <td>521973500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>278.430770</td>\n",
       "      <td>332.049988</td>\n",
       "      <td>231.229996</td>\n",
       "      <td>277.451539</td>\n",
       "      <td>476314900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1255 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         open        high         low       close       volume\n",
       "ticker date                                                                   \n",
       "AAPL   2018-01-02   42.540001   43.075001   42.314999   43.064999  102223600.0\n",
       "       2018-01-03   42.836250   43.637501   42.314999   43.061249  220295200.0\n",
       "       2018-01-04   42.935833   43.637501   42.314999   43.126666  310033600.0\n",
       "       2018-01-05   43.041875   43.842499   42.314999   43.282499  404673600.0\n",
       "       2018-01-08   43.151000   43.902500   42.314999   43.343500  486944800.0\n",
       "...                       ...         ...         ...         ...          ...\n",
       "NFLX   2018-12-24  283.509251  332.049988  233.679993  281.931750  525657600.0\n",
       "       2018-12-26  281.844501  332.049988  231.229996  280.777750  520444300.0\n",
       "       2018-12-27  281.070489  332.049988  231.229996  280.162927  532679500.0\n",
       "       2018-12-28  279.916342  332.049988  231.229996  279.461464  521973500.0\n",
       "       2018-12-31  278.430770  332.049988  231.229996  277.451539  476314900.0\n",
       "\n",
       "[1255 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "faang.groupby('ticker').rolling('60D').agg(\n",
    "    {\n",
    "        'open': np.mean,\n",
    "        'high': np.max,\n",
    "        'low': np.min,\n",
    "        'close': np.mean,\n",
    "        'volume': np.sum\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 6\n",
    "Create a pivot table of the FAANG data that compares the stocks. Put the ticker in the rows and and show the averages of the OHLC and volume traded data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>close</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>open</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AAPL</th>\n",
       "      <td>47.263357</td>\n",
       "      <td>47.748526</td>\n",
       "      <td>46.795877</td>\n",
       "      <td>47.277859</td>\n",
       "      <td>1.360803e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMZN</th>\n",
       "      <td>1641.726176</td>\n",
       "      <td>1662.839839</td>\n",
       "      <td>1619.840519</td>\n",
       "      <td>1644.072709</td>\n",
       "      <td>5.648994e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FB</th>\n",
       "      <td>171.510956</td>\n",
       "      <td>173.613347</td>\n",
       "      <td>169.303148</td>\n",
       "      <td>171.472948</td>\n",
       "      <td>2.765860e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GOOG</th>\n",
       "      <td>1113.225134</td>\n",
       "      <td>1125.777606</td>\n",
       "      <td>1101.001658</td>\n",
       "      <td>1113.554101</td>\n",
       "      <td>1.741965e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NFLX</th>\n",
       "      <td>319.290319</td>\n",
       "      <td>325.219322</td>\n",
       "      <td>313.187330</td>\n",
       "      <td>319.620558</td>\n",
       "      <td>1.146962e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              close         high          low         open        volume\n",
       "ticker                                                                  \n",
       "AAPL      47.263357    47.748526    46.795877    47.277859  1.360803e+08\n",
       "AMZN    1641.726176  1662.839839  1619.840519  1644.072709  5.648994e+06\n",
       "FB       171.510956   173.613347   169.303148   171.472948  2.765860e+07\n",
       "GOOG    1113.225134  1125.777606  1101.001658  1113.554101  1.741965e+06\n",
       "NFLX     319.290319   325.219322   313.187330   319.620558  1.146962e+07"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "faang.pivot_table(index='ticker')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 7\n",
    "Calculate the Z-scores of Amazon's data (ticker: AMZN) using `apply()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>open</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-01</th>\n",
       "      <td>2.368006</td>\n",
       "      <td>2.502113</td>\n",
       "      <td>2.337813</td>\n",
       "      <td>2.385848</td>\n",
       "      <td>-1.630411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-02</th>\n",
       "      <td>2.227302</td>\n",
       "      <td>2.247433</td>\n",
       "      <td>2.190795</td>\n",
       "      <td>2.155037</td>\n",
       "      <td>-0.861879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-03</th>\n",
       "      <td>2.058955</td>\n",
       "      <td>2.139987</td>\n",
       "      <td>2.068570</td>\n",
       "      <td>2.025489</td>\n",
       "      <td>-0.920345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-04</th>\n",
       "      <td>1.819474</td>\n",
       "      <td>1.781561</td>\n",
       "      <td>1.850048</td>\n",
       "      <td>1.722816</td>\n",
       "      <td>-0.126582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-05</th>\n",
       "      <td>1.628173</td>\n",
       "      <td>1.554416</td>\n",
       "      <td>1.642819</td>\n",
       "      <td>1.584748</td>\n",
       "      <td>-0.298771</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                high       low      open     close    volume\n",
       "date                                                        \n",
       "2018-10-01  2.368006  2.502113  2.337813  2.385848 -1.630411\n",
       "2018-10-02  2.227302  2.247433  2.190795  2.155037 -0.861879\n",
       "2018-10-03  2.058955  2.139987  2.068570  2.025489 -0.920345\n",
       "2018-10-04  1.819474  1.781561  1.850048  1.722816 -0.126582\n",
       "2018-10-05  1.628173  1.554416  1.642819  1.584748 -0.298771"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "faang.loc['2018-Q4'].query(\"ticker == 'AMZN'\").drop(columns='ticker').apply(\n",
    "    lambda x: x.sub(x.mean()).div(x.std())\n",
    ").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 8\n",
    "Adding event descriptions:\n",
    "1. Create a dataframe with three columns: `ticker`, `date`, and `event`.\n",
    "    1. `ticker` will be `'FB'`.\n",
    "    2. `date` will be datetimes `['2018-07-25', '2018-03-19', '2018-03-20']`\n",
    "    3. `event` will be `['Disappointing user growth announced after close.', 'Cambridge Analytica story', 'FTC investigation']`.\n",
    "2. Set the index to `['date', 'ticker']`\n",
    "3. Merge this data to the FAANG data with a outer join."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>open</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <th>AAPL</th>\n",
       "      <td>43.637501</td>\n",
       "      <td>42.990002</td>\n",
       "      <td>43.132500</td>\n",
       "      <td>43.057499</td>\n",
       "      <td>118071600.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-23</th>\n",
       "      <th>NFLX</th>\n",
       "      <td>345.000000</td>\n",
       "      <td>328.089996</td>\n",
       "      <td>329.040009</td>\n",
       "      <td>344.720001</td>\n",
       "      <td>10049100.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-17</th>\n",
       "      <th>FB</th>\n",
       "      <td>179.320007</td>\n",
       "      <td>175.800003</td>\n",
       "      <td>179.259995</td>\n",
       "      <td>177.600006</td>\n",
       "      <td>27992400.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-17</th>\n",
       "      <th>AMZN</th>\n",
       "      <td>1845.000000</td>\n",
       "      <td>1807.000000</td>\n",
       "      <td>1842.790039</td>\n",
       "      <td>1831.729980</td>\n",
       "      <td>5295200.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-26</th>\n",
       "      <th>AMZN</th>\n",
       "      <td>1522.839966</td>\n",
       "      <td>1507.000000</td>\n",
       "      <td>1509.199951</td>\n",
       "      <td>1521.949951</td>\n",
       "      <td>4955000.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <th>GOOG</th>\n",
       "      <td>1104.250000</td>\n",
       "      <td>1092.000000</td>\n",
       "      <td>1094.000000</td>\n",
       "      <td>1102.229980</td>\n",
       "      <td>1279100.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-04</th>\n",
       "      <th>FB</th>\n",
       "      <td>155.559998</td>\n",
       "      <td>150.509995</td>\n",
       "      <td>152.029999</td>\n",
       "      <td>155.100006</td>\n",
       "      <td>49885600.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-30</th>\n",
       "      <th>AMZN</th>\n",
       "      <td>1626.000000</td>\n",
       "      <td>1612.930054</td>\n",
       "      <td>1618.099976</td>\n",
       "      <td>1624.890015</td>\n",
       "      <td>2907400.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-17</th>\n",
       "      <th>NFLX</th>\n",
       "      <td>338.619995</td>\n",
       "      <td>323.769989</td>\n",
       "      <td>329.660004</td>\n",
       "      <td>336.059998</td>\n",
       "      <td>33866500.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-15</th>\n",
       "      <th>AMZN</th>\n",
       "      <td>1720.869995</td>\n",
       "      <td>1708.520020</td>\n",
       "      <td>1714.000000</td>\n",
       "      <td>1715.969971</td>\n",
       "      <td>4777600.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          high          low         open        close  \\\n",
       "date       ticker                                                       \n",
       "2018-01-03 AAPL      43.637501    42.990002    43.132500    43.057499   \n",
       "2018-05-23 NFLX     345.000000   328.089996   329.040009   344.720001   \n",
       "2018-01-17 FB       179.320007   175.800003   179.259995   177.600006   \n",
       "2018-10-17 AMZN    1845.000000  1807.000000  1842.790039  1831.729980   \n",
       "2018-02-26 AMZN    1522.839966  1507.000000  1509.199951  1521.949951   \n",
       "2018-01-05 GOOG    1104.250000  1092.000000  1094.000000  1102.229980   \n",
       "2018-04-04 FB       155.559998   150.509995   152.029999   155.100006   \n",
       "2018-05-30 AMZN    1626.000000  1612.930054  1618.099976  1624.890015   \n",
       "2018-04-17 NFLX     338.619995   323.769989   329.660004   336.059998   \n",
       "2018-06-15 AMZN    1720.869995  1708.520020  1714.000000  1715.969971   \n",
       "\n",
       "                        volume event  \n",
       "date       ticker                     \n",
       "2018-01-03 AAPL    118071600.0   NaN  \n",
       "2018-05-23 NFLX     10049100.0   NaN  \n",
       "2018-01-17 FB       27992400.0   NaN  \n",
       "2018-10-17 AMZN      5295200.0   NaN  \n",
       "2018-02-26 AMZN      4955000.0   NaN  \n",
       "2018-01-05 GOOG      1279100.0   NaN  \n",
       "2018-04-04 FB       49885600.0   NaN  \n",
       "2018-05-30 AMZN      2907400.0   NaN  \n",
       "2018-04-17 NFLX     33866500.0   NaN  \n",
       "2018-06-15 AMZN      4777600.0   NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events = pd.DataFrame({\n",
    "    'ticker': 'FB',\n",
    "    'date': pd.to_datetime(\n",
    "         ['2018-07-25', '2018-03-19', '2018-03-20']\n",
    "    ), \n",
    "    'event': [\n",
    "         'Disappointing user growth announced after close.',\n",
    "         'Cambridge Analytica story',\n",
    "         'FTC investigation'\n",
    "    ]\n",
    "}).set_index(['date', 'ticker'])\n",
    "\n",
    "faang.reset_index().set_index(['date', 'ticker']).join(\n",
    "    events, how='outer'\n",
    ").sample(10, random_state=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 9\n",
    "Use the `transform()` method on the FAANG data to represent all the values in terms of the first date in the data. To do so, divide all values for each ticker by the values of the first date in the data for that ticker. This is referred to as an index, and the data for the first date is the base. [More information](https://ec.europa.eu/eurostat/statistics-explained/index.php/Beginners:Statistical_concept_-_Index_and_base_year). When data is in this format, we can easily see growth over time. Hint: `transform()` can take a function name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>open</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">FB</th>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>1.017623</td>\n",
       "      <td>1.021290</td>\n",
       "      <td>1.023638</td>\n",
       "      <td>1.017914</td>\n",
       "      <td>0.930294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>1.025498</td>\n",
       "      <td>1.036891</td>\n",
       "      <td>1.040635</td>\n",
       "      <td>1.016040</td>\n",
       "      <td>0.764708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">AAPL</th>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>1.013059</td>\n",
       "      <td>1.015952</td>\n",
       "      <td>1.013928</td>\n",
       "      <td>0.999826</td>\n",
       "      <td>1.155033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>1.006790</td>\n",
       "      <td>1.016661</td>\n",
       "      <td>1.013987</td>\n",
       "      <td>1.004470</td>\n",
       "      <td>0.877864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">AMZN</th>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>1.013017</td>\n",
       "      <td>1.015199</td>\n",
       "      <td>1.013908</td>\n",
       "      <td>1.012775</td>\n",
       "      <td>1.153758</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>1.021739</td>\n",
       "      <td>1.029175</td>\n",
       "      <td>1.028157</td>\n",
       "      <td>1.017308</td>\n",
       "      <td>1.121581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">NFLX</th>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>1.022614</td>\n",
       "      <td>1.031112</td>\n",
       "      <td>1.030342</td>\n",
       "      <td>1.019794</td>\n",
       "      <td>0.783394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>1.026779</td>\n",
       "      <td>1.043905</td>\n",
       "      <td>1.051504</td>\n",
       "      <td>1.022679</td>\n",
       "      <td>0.549800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">GOOG</th>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>1.018136</td>\n",
       "      <td>1.017202</td>\n",
       "      <td>1.015234</td>\n",
       "      <td>1.016413</td>\n",
       "      <td>1.155624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>1.024959</td>\n",
       "      <td>1.037094</td>\n",
       "      <td>1.037831</td>\n",
       "      <td>1.020094</td>\n",
       "      <td>0.811732</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       high       low      open     close    volume\n",
       "ticker date                                                        \n",
       "FB     2018-01-02  1.000000  1.000000  1.000000  1.000000  1.000000\n",
       "       2018-01-03  1.017623  1.021290  1.023638  1.017914  0.930294\n",
       "       2018-01-04  1.025498  1.036891  1.040635  1.016040  0.764708\n",
       "AAPL   2018-01-02  1.000000  1.000000  1.000000  1.000000  1.000000\n",
       "       2018-01-03  1.013059  1.015952  1.013928  0.999826  1.155033\n",
       "       2018-01-04  1.006790  1.016661  1.013987  1.004470  0.877864\n",
       "AMZN   2018-01-02  1.000000  1.000000  1.000000  1.000000  1.000000\n",
       "       2018-01-03  1.013017  1.015199  1.013908  1.012775  1.153758\n",
       "       2018-01-04  1.021739  1.029175  1.028157  1.017308  1.121581\n",
       "NFLX   2018-01-02  1.000000  1.000000  1.000000  1.000000  1.000000\n",
       "       2018-01-03  1.022614  1.031112  1.030342  1.019794  0.783394\n",
       "       2018-01-04  1.026779  1.043905  1.051504  1.022679  0.549800\n",
       "GOOG   2018-01-02  1.000000  1.000000  1.000000  1.000000  1.000000\n",
       "       2018-01-03  1.018136  1.017202  1.015234  1.016413  1.155624\n",
       "       2018-01-04  1.024959  1.037094  1.037831  1.020094  0.811732"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "faang = faang.reset_index().set_index(['ticker', 'date'])\n",
    "faang_index = (faang / faang.groupby(level='ticker').transform('first'))\n",
    "\n",
    "# view 3 rows of the result per ticker\n",
    "faang_index.groupby(level='ticker').agg('head', 3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercise 10\n",
    "## Part 1\n",
    "1. Read in the data in the `exercises/covid19_cases.csv` file\n",
    "2. Create a `date` column by parsing the `dateRep` column into a datetime\n",
    "3. Set the `date` column as the index\n",
    "4. Use the `replace()` method to update all occurrences of `United_States_of_America` and `United Kingdom` to `USA` and `UK`, respectively\n",
    "5. Sort the index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "covid = pd.read_csv('../../ch_04/exercises/covid19_cases.csv')\\\n",
    "    .assign(date=lambda x: pd.to_datetime(x.dateRep, format='%d/%m/%Y'))\\\n",
    "    .set_index('date')\\\n",
    "    .replace('United_States_of_America', 'USA')\\\n",
    "    .replace('United_Kingdom', 'UK')\\\n",
    "    .sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 2\n",
    "For the 5 countries with the most cases (cumulative), find the day with the largest number of cases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "countriesAndTerritories\n",
       "Brazil   2020-07-30\n",
       "India    2020-09-17\n",
       "Peru     2020-08-17\n",
       "Russia   2020-07-18\n",
       "USA      2020-07-25\n",
       "Name: cases, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_five_countries = covid\\\n",
    "    .groupby('countriesAndTerritories').cases.sum()\\\n",
    "    .nlargest(5).index\n",
    "\n",
    "covid[covid.countriesAndTerritories.isin(top_five_countries)]\\\n",
    "    .groupby('countriesAndTerritories').cases.idxmax()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 3\n",
    "Find the 7-day average change in COVID-19 cases for the last week in the data for the countries found in part 2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>countriesAndTerritories</th>\n",
       "      <th>USA</th>\n",
       "      <th>India</th>\n",
       "      <th>Brazil</th>\n",
       "      <th>Russia</th>\n",
       "      <th>Peru</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-09-14</th>\n",
       "      <td>473.714286</td>\n",
       "      <td>181.285714</td>\n",
       "      <td>35.285714</td>\n",
       "      <td>36.285714</td>\n",
       "      <td>73.142857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-15</th>\n",
       "      <td>1513.000000</td>\n",
       "      <td>1142.857143</td>\n",
       "      <td>697.428571</td>\n",
       "      <td>46.285714</td>\n",
       "      <td>377.571429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-16</th>\n",
       "      <td>3478.714286</td>\n",
       "      <td>59.571429</td>\n",
       "      <td>3196.285714</td>\n",
       "      <td>61.428571</td>\n",
       "      <td>-65.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-17</th>\n",
       "      <td>-1047.000000</td>\n",
       "      <td>308.428571</td>\n",
       "      <td>143.428571</td>\n",
       "      <td>810.000000</td>\n",
       "      <td>-29.428571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-18</th>\n",
       "      <td>865.714286</td>\n",
       "      <td>-18.142857</td>\n",
       "      <td>-607.714286</td>\n",
       "      <td>-688.428571</td>\n",
       "      <td>-227.571429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-19</th>\n",
       "      <td>306.857143</td>\n",
       "      <td>-604.714286</td>\n",
       "      <td>-560.142857</td>\n",
       "      <td>57.285714</td>\n",
       "      <td>-41.285714</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "countriesAndTerritories          USA        India       Brazil      Russia  \\\n",
       "date                                                                         \n",
       "2020-09-14                473.714286   181.285714    35.285714   36.285714   \n",
       "2020-09-15               1513.000000  1142.857143   697.428571   46.285714   \n",
       "2020-09-16               3478.714286    59.571429  3196.285714   61.428571   \n",
       "2020-09-17              -1047.000000   308.428571   143.428571  810.000000   \n",
       "2020-09-18                865.714286   -18.142857  -607.714286 -688.428571   \n",
       "2020-09-19                306.857143  -604.714286  -560.142857   57.285714   \n",
       "\n",
       "countriesAndTerritories        Peru  \n",
       "date                                 \n",
       "2020-09-14                73.142857  \n",
       "2020-09-15               377.571429  \n",
       "2020-09-16               -65.000000  \n",
       "2020-09-17               -29.428571  \n",
       "2020-09-18              -227.571429  \n",
       "2020-09-19               -41.285714  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "covid\\\n",
    "    .groupby(['countriesAndTerritories', pd.Grouper(freq='1D')]).cases.sum()\\\n",
    "    .unstack(0).diff().rolling(7).mean().last('1W')[top_five_countries]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 4\n",
    "Find the first date that each country other than China had cases:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "countriesAndTerritories\n",
       "Thailand         2020-01-13\n",
       "Japan            2020-01-15\n",
       "South Korea      2020-01-20\n",
       "Taiwan           2020-01-21\n",
       "USA              2020-01-21\n",
       "                    ...    \n",
       "Yemen            2020-04-10\n",
       "Western Sahara   2020-04-26\n",
       "Tajikistan       2020-05-01\n",
       "Comoros          2020-05-02\n",
       "Lesotho          2020-05-15\n",
       "Length: 209, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "covid\\\n",
    "    .pivot(columns='countriesAndTerritories', values='cases')\\\n",
    "    .drop(columns='China')\\\n",
    "    .apply(lambda x: x[x > 0].index.min())\\\n",
    "    .sort_values()\\\n",
    "    .rename(lambda x: x.replace('_', ' '))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 5\n",
    "Rank the countries by total cases using percentiles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cases</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>countriesAndTerritories</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>India</th>\n",
       "      <td>0.995238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>0.990476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russia</th>\n",
       "      <td>0.985714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Peru</th>\n",
       "      <td>0.980952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Greenland</th>\n",
       "      <td>0.023810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Montserrat</th>\n",
       "      <td>0.019048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Falkland Islands (Malvinas)</th>\n",
       "      <td>0.019048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Holy See</th>\n",
       "      <td>0.009524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Anguilla</th>\n",
       "      <td>0.004762</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>210 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                cases\n",
       "countriesAndTerritories              \n",
       "USA                          1.000000\n",
       "India                        0.995238\n",
       "Brazil                       0.990476\n",
       "Russia                       0.985714\n",
       "Peru                         0.980952\n",
       "...                               ...\n",
       "Greenland                    0.023810\n",
       "Montserrat                   0.019048\n",
       "Falkland Islands (Malvinas)  0.019048\n",
       "Holy See                     0.009524\n",
       "Anguilla                     0.004762\n",
       "\n",
       "[210 rows x 1 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "covid\\\n",
    "    .pivot_table(columns='countriesAndTerritories', values='cases', aggfunc='sum')\\\n",
    "    .T\\\n",
    "    .transform('rank', method='max', pct=True)\\\n",
    "    .sort_values('cases', ascending=False)\\\n",
    "    .rename(lambda x: x.replace('_', ' '))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<div>\n",
    "    <a href=\"../../ch_04/4-time_series.ipynb\">\n",
    "        <button>&#8592; Chapter 4</button>\n",
    "    </a>\n",
    "    <a href=\"../../ch_05/1-introducing_matplotlib.ipynb\">\n",
    "        <button style=\"float: right;\">Chapter 5 &#8594;</button>\n",
    "    </a>\n",
    "</div>\n",
    "<hr>"
   ]
  }
 ],
 "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}