{
"cells": [
{
"cell_type": "markdown",
"id": "39ce9fdc",
"metadata": {},
"source": [
"# Advanced Data Manipulation\n",
"\n",
"Over the next two weeks or so, we'll develop a range of skills for advanced data analysis in Python. We'll handle questions like: \n",
"\n",
"1. What if our data is split over multiple files?\n",
"2. What if our data is in the wrong shape?\n",
"3. What if our data is too big to conveniently fit in memory? \n",
"\n",
"To approach these questions, we'll develop some more advanced skills with the familiar `pandas` library. We'll also introduce new tools for plotting, including spatial plots, and take a brief look at databases. \n",
"\n",
"## Case Study: Global Warming\n",
"\n",
"Throughout our study, we'll focus on a single data set. This is the Global Historical Climatology Network data set, compiled by the National Centers for Environmental Information of the US National Oceanic and Atmospheric Administration. This data set contains an extraordinarily detailed record of surface temperatures at tens of thousands of atmospheric measurement stations across the globe. It is thus an ideal, publicly available data set for studying global warming. As you know, global warming is one of the most important issues of our time, primarily because it threatens the natural habitat of nature's most majestic creature: \n",
"\n",
"\n",
"\n",
"An additional reason to be concerned about global warming is that rising temperatures reduce snowfall, a necessary prerequisite for videos of panda bears falling off of snowmen: \n",
"\n",
"\n",
"\n",
"### Takeaways for Today\n",
"\n",
"Here's what I want you to take away from this lecture: \n",
"\n",
"1. Many **data visualization** problems can be solved by **data manipulation**. \n",
"2. `pandas` provides a number of handy methods for combining and reshaping your data. \n",
"3. Pandas are very clumsy animals. \n",
"\n",
"\n",
"### Data Source and Format\n",
"\n",
"I accessed the data [here](https://www.ncdc.noaa.gov/data-access/land-based-station-data/land-based-datasets/global-historical-climatology-network-monthly-version-4), and then did a small amount of manipulation in order to save it as a set of CSV files. The files are hosted online at \n",
"\n",
"> `https://raw.githubusercontent.com/PhilChodrow/PIC16B/master/datasets/noaa-ghcn/decades/`\n",
"\n",
"There is a file for each decade between 1901 and 2020. Today, we'll use only the file containing the most recent data, 2011-2020. "
]
},
{
"cell_type": "code",
"execution_count": 1,
"id": "93e4ec79",
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"\n",
"# advanced plotting tools for data frames\n",
"# basically a bunch of matplotlib shortcuts\n",
"import seaborn as sns \n",
"\n",
"from matplotlib import pyplot as plt\n",
"import numpy as np"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "d98f656b",
"metadata": {},
"outputs": [],
"source": [
"interval = \"2011-2020\"\n",
"url = f\"https://raw.githubusercontent.com/PhilChodrow/PIC16B/master/datasets/noaa-ghcn/decades/{interval}.csv\"\n",
"df = pd.read_csv(url)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "1f98aa55",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
ID
\n",
"
Year
\n",
"
VALUE1
\n",
"
VALUE2
\n",
"
VALUE3
\n",
"
VALUE4
\n",
"
VALUE5
\n",
"
VALUE6
\n",
"
VALUE7
\n",
"
VALUE8
\n",
"
VALUE9
\n",
"
VALUE10
\n",
"
VALUE11
\n",
"
VALUE12
\n",
"
\n",
" \n",
" \n",
"
\n",
"
0
\n",
"
ACW00011604
\n",
"
2011
\n",
"
-83.0
\n",
"
-132.0
\n",
"
278.0
\n",
"
1040.0
\n",
"
1213.0
\n",
"
1663.0
\n",
"
1875.0
\n",
"
1723.0
\n",
"
1466.0
\n",
"
987.0
\n",
"
721.0
\n",
"
428.0
\n",
"
\n",
"
\n",
"
1
\n",
"
ACW00011604
\n",
"
2012
\n",
"
121.0
\n",
"
-98.0
\n",
"
592.0
\n",
"
646.0
\n",
"
1365.0
\n",
"
1426.0
\n",
"
1771.0
\n",
"
1748.0
\n",
"
1362.0
\n",
"
826.0
\n",
"
620.0
\n",
"
-234.0
\n",
"
\n",
"
\n",
"
2
\n",
"
ACW00011604
\n",
"
2013
\n",
"
-104.0
\n",
"
-93.0
\n",
"
-48.0
\n",
"
595.0
\n",
"
NaN
\n",
"
1612.0
\n",
"
1855.0
\n",
"
1802.0
\n",
"
1359.0
\n",
"
1042.0
\n",
"
601.0
\n",
"
NaN
\n",
"
\n",
"
\n",
"
3
\n",
"
AE000041196
\n",
"
2011
\n",
"
1950.0
\n",
"
2060.0
\n",
"
2280.0
\n",
"
2760.0
\n",
"
3240.0
\n",
"
3447.0
\n",
"
3580.0
\n",
"
3650.0
\n",
"
3316.0
\n",
"
2940.0
\n",
"
2390.0
\n",
"
1905.0
\n",
"
\n",
"
\n",
"
4
\n",
"
AE000041196
\n",
"
2012
\n",
"
1837.0
\n",
"
1987.0
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" ID Year VALUE1 VALUE2 VALUE3 VALUE4 VALUE5 VALUE6 VALUE7 \\\n",
"0 ACW00011604 2011 -83.0 -132.0 278.0 1040.0 1213.0 1663.0 1875.0 \n",
"1 ACW00011604 2012 121.0 -98.0 592.0 646.0 1365.0 1426.0 1771.0 \n",
"2 ACW00011604 2013 -104.0 -93.0 -48.0 595.0 NaN 1612.0 1855.0 \n",
"3 AE000041196 2011 1950.0 2060.0 2280.0 2760.0 3240.0 3447.0 3580.0 \n",
"4 AE000041196 2012 1837.0 1987.0 NaN NaN NaN NaN NaN \n",
"\n",
" VALUE8 VALUE9 VALUE10 VALUE11 VALUE12 \n",
"0 1723.0 1466.0 987.0 721.0 428.0 \n",
"1 1748.0 1362.0 826.0 620.0 -234.0 \n",
"2 1802.0 1359.0 1042.0 601.0 NaN \n",
"3 3650.0 3316.0 2940.0 2390.0 1905.0 \n",
"4 NaN NaN NaN NaN NaN "
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.head(5)"
]
},
{
"cell_type": "markdown",
"id": "e5adeafc",
"metadata": {},
"source": [
"As provided, the data set contains the following columns: \n",
"\n",
"- `ID`: the ID number of the station. We can use this to figure out which country the station is in, as well as the spatial location of the station. \n",
"- `Year`: the year of the measurement. \n",
"- `VALUE1`-`VALUE12`: the temperature measurements themselves. `VALUE1` contains the temperature measurements for January, `VALUE2` for February, and so on. \n",
"- The measurements are in hundredths of a degree, Celsius. "
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "436027b2",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"-1.32"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# measurement in February in very first row of DF\n",
"df[\"VALUE2\"].iloc[0]\n",
"\n",
"# temperature in degrees C is\n",
"\n",
"df[\"VALUE2\"].iloc[0] / 100"
]
},
{
"cell_type": "markdown",
"id": "9bbc7ee0",
"metadata": {},
"source": [
"We can now do things like plot the temperature in January for a selected station: "
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "4a48eac1",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
},
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAYsAAAEGCAYAAACUzrmNAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAA870lEQVR4nO3deXRcV53o+++vSvNcGi2prMFS4nmSlGDLEJJASGKSOIkDDbdfCOHdG+DRd8GCvt3Q93VfbtM00N2X282igeZCQ+AFaMAOdkhMCCEMsZ1EsiyP8mzZ1mRZ1jwPtd8fVeXIiqTSUKdOVen3WauWSqdOndp1llS/Ovu392+LMQallFJqNg67G6CUUir8abBQSikVkAYLpZRSAWmwUEopFZAGC6WUUgHF2N0Aq2RnZ5uSkhK7m6GUUhHl0KFDHcaYnKnbozZYlJSUUFtba3czlFIqoojIpem2azeUUkqpgDRYKKWUCkiDhVJKqYA0WCillApIg4VSSqmANFgopZQKSIOFUkqpgDRYKKXUIo2MT/DjNy4z4YneJR80WCil1CL98kgrn9t9jN+fabe7KZbRYKGUUotUe6nT+7Oxy+aWWEeDhVJKLVKNL0gcuqTBQiml1DQ6B0Y5195PYqyTI03djE147G6SJTRYKKXUIvivJv7ktuUMj3k40dJrc4usocFCKaUWobaxkzing49sKwWitytKg4VSSi1CTWMnG9zpFGUl4XYlcsiX7I42GiyUUmqBhscmONbcQ1VJJgCVxS5qG7swJvrmW2iwUEqpBaq/0s3YhOG2EhcAVcUu2vtGaOoasrllwafBQimlFqi20dvlVFns8v30XmFEY95Cg4VSSi1QTWMXt+alkJEUB8DKZamkxMfcmKQXTTRYKKXUAkx4DHWXum7kKwCcDmFzUUZUzuTWYKGUUgtwuq2PvpHxG/kKv8piF6ev9tE3PGZTy6xhWbAQkeUi8oqINIjICRH5pG/7P4rIKRE5KiLPikjGpOd8TkTOichpEbl30vZKETnme+xrIiJWtVspNTcvN1zlYseA3c2wjb+rqao486btVcWZGAOHL3fb0CrrWHllMQ58xhizGtgCfEJE1gAvAeuMMRuAM8DnAHyPfQBYC9wHfENEnL5jfRN4CrjFd7vPwnYrpQK43j/CUz88xD++eMruptimprGLZWkJuF2JN23fVJSBQ6A2ypLclgULY0yrMabOd78PaAAKjTG/NsaM+3Z7DXD77u8AfmKMGTHGXATOAbeLSD6QZow5aLyDl38APGxVu5VSgb1wvI0Jj+Hg+et4ongNh5kYY6i52MltpZlM7ehIiY9h1bK0qJucF5KchYiUAJuB16c89BFgn+9+IXBl0mNNvm2FvvtTt0/3Ok+JSK2I1F67di0ILVdKTWdvfTMi0DU4RkNbdNZCmk1z9xBtvcNvyVf4VZW4qL/czXgUFRW0PFiISAqwC/iUMaZ30vb/jrer6hn/pmmebmbZ/taNxnzbGFNljKnKyclZXMOVUtNq7h6iprGLP31bEQAHzl23uUWh5x/tNDVf4VdZ7GJgdIJTbX2hbJalLA0WIhKLN1A8Y4zZPWn7E8ADwJ+aN+fFNwHLJz3dDbT4trun2a6UssFzR7z/fh+9o4wV2ckcON9hc4tCr6axk9T4GFYuS532cf8kvWianGflaCgBvgs0GGO+Omn7fcBfAg8ZYwYnPWUv8AERiReRUryJ7DeMMa1An4hs8R3zQ8Aeq9qtlJrdnvoWKooyWJ6ZRHV5Fm9c7IzaNRxmUtvYRUWxC6dj+oGZhRmJLEtLiKokt5VXFtuAx4G7RaTed9sOfB1IBV7ybfsWgDHmBPBT4CTwK+ATxpgJ37E+DnwHb9L7PG/mOZRSIXTmah8Nrb08tLEAgOqybAZGJzja1G1vw0KoZ3CM01f7ZsxXAIgIlSUu6qIoWMRYdWBjzKtMn294YZbnfBH44jTba4F1wWudUmoh9ta34BB47wZvsNi6IguA/eeu36iLFO0OXfbNryiZ/f1WFrl4/mgrrT1D5KcnzrpvJNAZ3EqpOTHGsPdIC9vKs8lJjQfAlRzHmvy0JZW3eONiF7FOYaM7Y9b9qnxXHtFS+kODhVJqTuqvdHO5c5Adm24eub6tPIu6S90MjU7M8MzoUtvYybrCdBLjnLPutzo/jcRYZ9QkuTVYKKXmZE99C3ExDu5dm3fT9uqybEYnPFHzoTib4bEJjjb1cFuALiiAWKeDTcszoua8aLBQSgU0PuHhl0dbeffqXFITYm967LbSTGIcwv4l0BV1rLmH0QkPVcUzJ7cnqyx2cbK1l4GR8cA7hzkNFkqpgA5euE5H/8iNUVCTpcTHsHF5BgfOR//kvJopix0FUlniYsJjOHKl28JWhYYGC6VUQHvqW0iNj+HOlbnTPr6tLItjTd30DEVXWe6pahu7KMtJJislfk77VxRFz+Q8DRZKqVkNj03w4vE27lu3jITY6ZO61eXZeAy8fiF6ry48HkNtY+ec8hV+6Ymx3JqXEhWT8zRYKKVm9bvT7fSNjL9lFNRkm4sySIh1RHVX1Nn2fnqHx+cVLMC7Lnfd5a6Ir86rwUIpNas99S1kp8SztSxrxn3iY5zcVpIZ1fMt/PmK+QaLqmIXfcPjnG3vt6JZIaPBQik1o97hMV4+1c6DG/NnrIPkt7UsizNX+7nWNxKi1oVWbWMnuanxLM+c32zsG5PzInx9Cw0WSqkZvXi8jdFxz7SjoKbaVpYNELVXFzWNXdxW8tbFjgIpykwiOyWOQxE+k1uDhVJqRnuPtFCUmcSm5RkB911XmE5qQgwHozBv0dI9RHP30I2rhPkQESqLXRGf5NZgoZSaVnvfMPvPdbBjU8Gcvk07HcKWFVlROTnP/0E/33yFX1VxJpc7B2nvGw5ms0JKg4VSalovHG3FY2DHpsBdUH7VZVlc6RziSudg4J0jSG1jJ8lxTlbNsNhRIBW+SXyRXLJcg4VSalp7jrSwJj+N8ty5f0BuK4/OvEWNb7GjGOfCPjLXFaYRF+OI6Aq0GiyUUm9x+foghy93z+uqAuCW3BSyU+Kjar5Fz9AYp9p6Z1xvey7iY5xsdKdz6LIGC6VUFNl7pBmAB+cwCmoyEaG6LIsD569jTGRPQvOru9yFMcy6Mt5cVBZncry5h+GxyCzlrsFCKXUTYwy/qG/h9pJMCjLmv8JbdVkW1/pGOBfhk9D8ahs7cTqETUUZizpOZbGLsQnD0aae4DQsxDRYKKVu0tDax7n2fh6aZxeUnz9vsf9cdOQtahq7WFeQRlLc4lah9leqjdTJeRoslFI32XukhRiHsH19/oKevzwzCbcrMSryFiPjExy50h1wve25yEyOY0VOcsSOiNJgoZS6weMxPHekhTtuzSEzOW7Bx9lWls1rF64zEeHF84439zIy7ll0vsKvqtjFoUtdEZnP0WChlLrh0OUumruH5j0Kaqrq8ix6h8c50RKZ/fN+tTcWO1r8lYX3OC66Bsc4f20gKMcLJQ0WSqkb9tQ3kxjr5N2r8wLvPAt/hdr95yK7K6qmsYsV2cnkpM5tsaNA/EEnEruiNFgopQAYm/Dw/NFW3r0mj+T4xSVzc1MTuCU3JaIn53k8hkOXOhdUD2omZTnJZCTFRmSSW4OFUgqAV8920DU4xo55zq2YybbybGoaOxkZj8x5BRc6+ukaHAtKcttPRKgsisyighoslFKAdxRUemIsd9yaE5TjVZdlMTzm4fDl7qAcL9RqGhdXPHAmlSUuLlwboHNgNKjHtZoGC6UUQ6MTvHiije3r84mLCc7HwttWZOEQInYIbU1jJ9kpcZRkJQX1uFURmrfQYKGU4jcNVxkcnVj0KKjJ0hNjWV+YzoEInZxX29hFVfH8FzsKZIM7nVinRFxXlAYLpRR76ltYlpbA7UHuctlalk39lW4GRsaDelyrXe0d5nLnYFCT234JsU7WFqRzKMKS3BoslFriugdH+f0Z7zrbjgDrbM/XtvIsxj2GNxoj64Ox1qJ8hV9VsYsjTT0RlfzXYKHUErfveBtjE4YdmwqDfuyq4kzinI6IW2q1prGTxFgnawrSLDl+VYmL0XEPJ1p6LTm+FTRYKLXE7a1vYUVOMmst+GBMjHOyuSgj4ooK1jR2srkog9gFLnYUiH/lvEMRtBiSZcFCRJaLyCsi0iAiJ0Tkk77t7/P97hGRqkn7l4jIkIjU+27fmvRYpYgcE5FzIvI1CXbGSaklqq1nmNcuXmfHxsKgJ3L9qsuyOdnaS1eEDBXtGx6jobU3qPMrpspNTaAoMymiJudZeWUxDnzGGLMa2AJ8QkTWAMeBR4E/TPOc88aYTb7bxyZt/ybwFHCL73afhe1Wasn45dEWjGHB5cjnYlt5FsbAaxcioyvq8OVuPEFY7CgQb1HB7ogpKmhZsDDGtBpj6nz3+4AGoNAY02CMOT3X44hIPpBmjDlovGf1B8DDVrRZqaVmT30LG93plGYnW/YaG9wZJMU5I2a+RW1jJw6BzUXWBovKEhcd/SNc7hy09HWCJSQ5CxEpATYDrwfYtVREDovI70XkHb5thUDTpH2afNume52nRKRWRGqvXbu22GYrFdXOX+vnWHMPD1mQ2J4sLsbB7aWZ7I+QOlE1jV2sKUgjZZH1sQK5sRhShOQtLA8WIpIC7AI+ZYyZLfXfChQZYzYDnwZ+JCJpwHQdqdNetxljvm2MqTLGVOXkBKdkgVLRam99CyLwwIaFLXI0H9VlWVy4NkBbz7Dlr7UYYxMeDl/pujHL2kq35qaSmhATMZPzLA0WIhKLN1A8Y4zZPdu+xpgRY8x13/1DwHngVrxXEu5Ju7qBFmtarNTSYIx3kaOtK7LIS0uw/PWqy7xLrYZ7FdoTLb0Mj3m4vdT6YOFwCBVFrogp+2HlaCgBvgs0GGO+Oof9c0TE6bu/Am8i+4IxphXoE5EtvmN+CNhjVbuVWgqON/dyoWMgqOU9ZrMmP42MpNiwz1v4FzuqKrY2X+FXWeziTHsfPUNjIXm9xbDyymIb8Dhw96ThsNtF5BERaQK2As+LyIu+/e8AjorIEeDnwMeMMf5xZR8HvgOcw3vFsc/CdisV9fbUNxPndHDfWuu7oMD7LXrriiwOnOsI69E/NY2dFGclkRuCqy3wBiVjoO5y+F9dWJbBMca8yvT5BoBnp9l/F94uq+mOVQusC17rlFq6JjyG5462cOfKHNKTYkP2utVlWew73sal64OUWDj6aqGMMdQ2dnHnytyQveamogycDqHuUhd3hfB1F0JncCu1xLx+8TpXe0csKe8xm+pyb94iXEdFXewY4PrAqOXzKyZLiothTX5aRIyI0mCh1BKzt76F5Dgn71od2m+yK7KTyUuL50CYrsvt/8C2cub2dCqLXdRf6WZswhPS150vDRZKLSEj4xO8cKyVe9cuIyHWGdLXFhG2lWVz8MJ1PJ7wy1vUNHbiSoqlLCe0XWSVxS6GxiZoaA3vooIaLJRaQv5wpoPe4XFLy3vMpro8m86BUU619dny+rOpvdRFVUnwFzsKxL9mxqEwH0KrwUKpJWRPfTNZyXFs8+UPQq26LAsIv/kW1/pGuNgxENJ8hV9+eiKFGYlhPzlPg4VSS0T/yDi/abjKezfkW1Z6O5CCjERKs5PDbr7FjfkVIc5X+FUUuzjU2BXWw4o1WCi1RLx0so3hMU/IJuLNZGtZFq9fuB5WCd2axi7iYxysK0i35fWril209Q7TEsblUDRYKLVE7KlvoTAjkQqLq6kGsq0sm4HRCY429djajslqL3WyaXkGcTH2fCS+WVQwfNe30GCh1BJwvX+EP57t4KFNBSFP4E61ZYW3q+dgmOQtBkbGOdHSa9l623OxalkqSXHOsE5ya7BQagl44XgbEx5jexcUQFZKPKvz09gfJvMt6q90M+ExN0Yl2SHG6WBzUUZYT87TYKHUErC3vpmVeamsWhb8dbYXorosi0OXuxgem7C7KdT4FjuqDFHxwJlUFmdyqq2X/pFxW9sxEw0WSkW5pq5Bahq7bJtbMZ1t5VmMjnvCotultrGLVcvSSE0IXZ2s6VQVu/AYqL/cbWs7ZqLBQqko99yRVgAe2hg+weK2kkycDrF9vsX4hIe6y122zK+YalNRBiLeZHs40mChVJTbU99MZbGL5ZlJdjflhtSEWDa6023PWzS09jE4OmHb/IrJ0hJiWZmXGhZXW9PRYKFUFDtztY9TbX1hdVXhV12WzdGmbnqH7Vv4p+bGZDz7ryzA247Dl70J93CjwUKpKLa3vgWnQ9i+PjSLHM1HdXkWHgNvXLCv26X2UiduVyL56Ym2tWGyymIX/SPjnA7D2lkaLJSKUsYY9hxpZlt5Njmp8XY35y0qilzExzhsK/1hjKGmscvW+RVTVRV723IoDPMWGiyUilKHr3RzpXOIHWHYBQWQEOukqsRlW5L7cucg1/pGwqYLCsDtSiQ3NT4s8xYaLJSKUnvrW4iPcfCetXl2N2VG1WXZnGrro6N/JOSvXeObABdOVxYiQlWJKywr0GqwUCoKjU94+OXRFt69Os/2+QOz8ZcsP2hDV1RtYyfpibGU56SE/LVnU1HkoqlriKu94VVUcEHBQkRWBbshSqngOXD+Oh39ozwYpl1QfusL00mNj7GlK+qNxk6qil04HPbWyprKP4w33Ep/LPTK4tdBbYVSKqj2HmkhNSGGO1fm2N2UWcU4HbxtRWbIk9zX+0e4cG0gLOZXTLW2II2EWEfY5S1iZnpARL4200NAhiWtUUot2vDYBL863sb29aFfZ3shqsuy+U1DO01dg7hdoZk46M8JhMPM7alinQ42uDPCbkTUbFcWTwLHgUNTbrXAqPVNU0otxCun2ukfGWfHpkK7mzIn1eX+pVZDd3VR29hJXIyD9W57FjsKpKrYxYmWXoZG7S+06DdbsKgBjhtjnp56A8JvxkgQGGN49WyHLck2pYJlT30LOanxbFmRZXdT5mRlXipZyXEcOBe6vEVNYxcb3enEx4TnlVdViYtxj+FIU7fdTblhtmDxGFA/3QPGmFJLWhMG/sfe43x5X0NYr4Wr1Ex6hsb47el2HtxQgDPMErczERG2lmVx4Pz1kPzfDY1OcLy5J6yGzE7lX80wnPIWswWLcWPM4HQPiEiRRe2xlYjw4eoSjjT1UBemZYKVms2LJ9oYHfeEVTnyudhWnk173wjnr/Vb/lr1V7oZ95iwDhYZSXGU56aE1TKrswWL3/nviMjLUx77hRWNCQePVrhJTYjh+wca7W6KUvP23JEWirOS2BimffEz8c+3CEXeoraxExFsX4s8kKpiF4cudeEJk6KCswWLydewU0NwZFzfLkByfAwfuG05+4610tYTXpNilJpNe98w+891sGOj/etsz1dRZhKFGYnsD0HeouZSFyvzUklPCt/JiuAtKtg7PB6Sq625mC1YmBnuT/d7VPnQ1hI8xvDD1xrtbopSc/b80VY8hojrggJvF3B1WRavXei0tDz3hMdQd6krrOpBzcS/zGu4lP6YLVjkisinReQzk+77fw/vmT6LtDwziXevzuNHr18OizWClZqLPfUtrC1Iozw31e6mLMi28mx6hsY42dJr2Wv417gO53yFX2l2MlnJcWEzk3u2YPF/gFQgZdJ9/+/fsb5p9vrwthK6BsfYW99id1OUCujS9QHqr3SzIwKvKvy23shbWNcV5f/gDceZ21OJCBXFLuouh3mwMMb8z9lugQ4sIstF5BURaRCREyLySd/29/l+94hI1ZTnfE5EzonIaRG5d9L2ShE55nvsaxKCDtmtK7JYtSyVf99/UYfRqrC3t74FEcK+FtRs8tISKM9NYb+FSe6axk4K0hMozAiPxY4CqSp2cbFjwJaqvFPNGCx8H8qTb/8iIn8tIm+f47HHgc8YY1YDW4BPiMgavLPCHwX+MOX11gAfANYC9wHfEBH/jJlvAk8Bt/hu9839LS6MiPDkthJOtfXx+sXwGb6m1FTeRY5auK0kM2xWfFuo6rIsai52MjruCfqxvYsddUbEVYWfP28RDvMtZuuGmlrmow7oB/5RRD4V6MDGmFZjTJ3vfh/QABQaYxqMMaenecoO4CfGmBFjzEXgHHC7iOQDacaYg8b7Ff8HwMNzfYOLsWNTIa6kWL63/2IoXk6pBWlo7eNce39Ed0H5VZdlMzQ2Qf2V7qAf21v2eyQs60HNZF1hOnHO8CgqOGMhQV9Zj7cQkW8BB4B/nuuLiEgJsBl4fZbdCoHXJv3e5Ns25rs/dft0r/MU3isQiooWP28wIdbJB28v4lu/P8+VzkGWZ4amyJlS87HnSDMxDmH7uvBbZ3u+tqzIRMSbt7i9NLhXADW+CW6RdGWREOtkvTs9LILFvEuUG2OG5rO/iKQAu4BPGWNmG+YwXR7CzLJ9urZ92xhTZYypyskJzoCtx7cWIyL88LVLQTmeUsHk8Rieq2/hnbfm4EqOs7s5i5aRFMe6gnQOnAt+3qKmsYvUhBhuzYus0WJVxS6ONfXYPjJzXsFCRGJE5Elu/qY/2/6xeAPFM8aY3QF2bwKWT/rdDbT4trun2R4S+emJ3LduGT954zKDo+Ohelml5qT2UhctPcMRObdiJtVlWRy+0hX0/7faxk4qi10RUzPLr6LYxeiEh+PNPba2Y7YEd5+I9E6+Ac3A/fi6embjG7H0XaDBGPPVObRlL/ABEYkXkVK8iew3jDGtQJ+IbPEd80PAnjkcL2g+sq2E3uFxdtc1h/JllQpoT30zibFO7lkTvutsz1d1eTZjE+bGGtnB0DUwytn2/oiYXzFVuCS5Zxs6m2qMSZtyyzPGvB/4zByOvQ14HLhbROp9t+0i8oiINAFbgedF5EXf650AfgqcBH4FfMIY47/u+jjeuR3ngPPAvgW+3wWpKHKxwZ3O9w806jBaFTbGJjy8cKyVe9bkkRQ3Y/ox4txW4iLWKUEtWX7oxmJHkRcsslPiKc1Otn0m90L/wt4P/PlsOxhjXmXmGlLPzvCcLwJfnGZ7LbBunm0MGn812k//9Ah/PNvBHbdG9QR2FSFePdtB1+BYVIyCmiwpLobNy11BLSpYc6mTOKeDDRFWYNGvosjF7063Y4yxre7XQtfgjqxOvyB474Z8slPitRrtEmaMsT3JONme+mYykmJ5xy3R9+WlujyL4y09dA8GZ1HO2sYu1rvTI2KZ2elUlbi4PjDKxY4B29ow2xrcM12vCUswWMTHOPnTtxXxLy+f5WLHAKXZyXY3SYXY/37pDF/77TlyUr3dAqVZyZTmJFOSlUxpdjLFWUkh+zAaHB3n1yev8vDmQuJiFvqdL3xVl2Xzz785y2sXOrlv3bJFHWt4bIKjTd185O2Ru2Zb1aS8xYqcFFvaMFs31CFmHro6Zk1zwtufbiniG787x9MHGvn8Q2vtbo7lJjyGfce9feLhuvxkqIxPePhxzRXWFqSxJj+Nix0DvHzqKh21b37zFYGC9ERKs5MpyU6iNDuF0uwkSrKSWZ6ZRKwzeB/qv2loZ3B0gh0RXN5jNpuWZ5AY6+TA+Y5FB4ujTT2MTRhuK468fIVfWU4K6YmxHLrUxfuqlgd+ggVmm5QXuWHYIrmpCTy4oYCf1V7hM++5ldSE8K6Hv1g/ev0Sf73nBJ9/cA0f3ra0/xxePdfBtb4RvrBj3U0fXr3DYzR2DHDRd2vsGODi9UH21rfQO/zm0E+nQ1ju8geSZFb4fpZkJVOQkTjv4Zx765vJT0+IyITtXMTFOLitNDMoeQv/ZDz/qKJI5HAIFUUZtia555XgFpEyvPWbPmiMsS3hbKcPbyth9+FmflbbFNGXtYH0DI7x1ZfOALCrrnnJB4vddd78wF2rbs4PpCXEssGdwQZ3xk3bjTF0DY5xsaOfix2DNwWU1y92Mjj6Zu4jLsZBcWaSt2vLdyvx/cxNjX9LQrN7cJTfn7nGk9tKcUTYnIH52FaWxZf2neJq7zB5aQkLPk5tYye35KZE/KTFqpJMXjl9mu7BUTKSQv9eAgYLX22mPwH+E7AB+BLwQYvbFbY2uDOoLHbx9MFGnqguibgJPnP1td+epXtojPdXuflpbROn2/pYuSyyZr4GS9/wGL8+2cb7KpfPuTtORMhMjiMzOZPKKd0fxhja+0Zuuhq54Lv/uzPXbiqilxTn9OZEcrw5kpLsZC529DM2YXgoSrug/KrLsgE4eP46D2+etsJPQB6PofZSFw9siPxz5b8yqrvcxd2rQj+vZrYE93/BGxTceOc//Gdgz1zKk0e7J7eV8Gc/OszvTrfzrtXRMxnK78K1fp4+0MgHblvOn79nJbvrmtlV18RfbV9td9Nsse94G8NjHh6tWNgH1lQiQl5aAnlpCWxZkXXTYxMeQ0v3EI3Xb+7aOtHcw6+Ot91YRa48N4W1BWlBaU+4WlOQRnpiLPvPdSw4WJxp76NveDyiigfOZKM7gxiHUNsYZsEC+FfgIPCffPMcEBGdkQbcu3YZy9IS+N7+xqgMFn//QgMJsU4+fc9KslLiuXtVLrvrmvmLe1cSE8QkbaTYXdfEiuxkNi3PsPy1nA5heWYSyzOT3jIkdmzCQ1PXEBc7+inNTom4dbbny+kQtqzw5i0WOr/APws8GnI7iXFO1hak2Za3mO0/vwD4CfBV32JEXwCiO6M7R7FOB49vLebVcx2cvdpnd3OC6o9nr/Gbhnb+7O5yclLjAdhZ6aajf4Q/nrVuBbNw1dQ1yGsXOnlkc6HtH86xTgel2cncvSpvyQzd3laeTXP3EJc7Bxf0/NrGTvLS4nG7InudD7/K4kyOXOlmbCL4630EMluw+Dxw1BhzB/AuoAdo96189/ehaFw4++DtRcTHOPheFE3SG5/w8He/bKAoM4knt5Xc2H7Xylwyk+P4+aE51Y+MKr847K0HttBuELU41TeWWl3YqKjaxi6qSjJtD/TBUlXiYmTcwwkL1ymfyWzB4izwTyLSCPxX4DfGmEq8ixTZv8afzTKT43h4UyG765qCNsvUbj+pucLpq3381fZVNyVy42IcPLSxgJdOXqVncOlMsTHGsLuumbeVZupaJjYpy0khNzWe/QuoE9XcPURz9xC3RfCQ2an8Se7axtCv3jlbIcF/McZsBd4JdALfE5EGvENnfxyi9oW1J99ewvCYh/+ouWJ3UxatZ8g7VPZtpZncu/atk6Aeq3QzOuFh79GQVYe3Xf2Vbi50DLCzwh14Z2UJEaG6LIuDvrzFfNRG4GJHgeSlJeB2JdpSgTZgttIYc8kY8xVjzGa8w2cfwbtE6pK3alkaW1dk8YODlxi3oQ8xmL7+27N0DY7y1w+smfaSfW1BGquWpbJrCXVFPXu4mfgYB/evX9wMYrU41eXZXB8Y5fQ884M1jZ2kxMewOj+6Ro1VFbuovdQV8grYAYOFiMSKyIMi8gze0uBngJ2WtyxCfHhbCc3dQ7x08qrdTVmwix0DfP9AI++vXM66wumrcooIj1W6qb/Szbn2/hC3MPRGxz3sPdLCvWuXRf1M/XB3I28xz9Xzahu7qIjAxY4CqSx2ca1vhKaueS1aumizLX50j4j8O96V6p4CXgDKjDF/Yoz5RYjaF/bevToPtysxohPdf/9CA3FOB5+599ZZ99uxqRCnQ9hVF/1XF6+cbqd7cCxocyvUwrldSRRnJXHg/NzzFj2DY5y+2hdV+Qo//yTP2kuhzVvMdmXxV3jnWaw2xjxojHnGGGNffdww5XQIT2wt4Y2LnZxosXfZw4XYf66Dl05e5RN3l5ObOntJhZzUeO68NYfddU03JodFq911TWSnxPP28my7m6LwXl28fqFzzt29dZe7MCa68hV+K5elkhofE/K8xWwJ7ruMMf/HGBP6tHuEef9ty0mKc/K9/Y12N2VeJjyGL/zyJG5XIh+ZY+2nxyrdXO0d4dUgrmIWbroGRvntqXYe3lSwJCchhqPqsmz6RsY5Nsd1qGsaO4lxSEgmUoaa0yFsKsqgNojLzs6F/icEQXpiLDsr3Oytb6GjP3JGFf9HzRVOtfXxV9tXz3kdhrtX55KeGBvVie5fHm1hbMLwqI6CChtb5znforaxi3WF6STGRWdp/cpiF6ev9tE7HLqh7BosguSJ6hJGJzz8+PXLdjdlTnqHx/hfvz7N7SWZ3D+P9QLiY5zs2FTAiyfaQvqHGkq76ppZtSyVNVFeeymSZKfEs2pZ6pzyFiPjE9Q3dUdFPaiZVBVnYgwcvtwdstfUYBEk5bkp3HFrDj987dJNVUPD1b/+9hydswyVnc3OCjcj4x6eP9pqUevsc+FaP/VXunVuRRiqLsumtrEr4NK2x5t7GB33RGW+wm9TUQYOIaR5Cw0WQfTkthLa+0bYdzy8P0QbOwb49/0XeazCzfoFLGC/wZ3OLbkpUVn+49nDzTgEdmyK/JLW0aa6LIuRcQ91l2f/gPQXD6yKwpFQfv75I4dCOCJKg0UQvfOWHFZkJ4d9ovtL+xqIdTr4b/euXNDzRYSdlW4OXeqydQH5YPN4vOU93n5LDrmLWGxHWeNtKzJxOiTgfIvaxk5W5CSTlRIfopbZo7LYxeHL3SGbEKzBIogcDuGJ6hLqr3RzOMC3H7scON/Biyeu8om7yhf1gfjI5kIcQlQlut9o7KS5e4idOrciLKUmxLK+MH3WvIV/saNIXm97riqLXQyOTnCqLTSVrzVYBNnOSjep8TF8Pwwn6XmHyjZQmJHI/73IJWHz0hK4wzfnwhMlcy521zWRHOfkPWu0vEe42laexZGmHvpmGFxx/lo/3YNjVEVxctvPn5MJVd5Cg0WQpcTH8L6q5Tx/tJWrvcN2N+cmP6u9QkNrL5/bvmrOQ2Vns7PCTUvPMAcvLKx8dDgZGp3ghWNtbF+fH7XDLaNBdVk2Ex5DzQxVV6NpsaNACtITWJaWELLFkDRYWOCJ6mImjOGZ1y7Z3ZQb+obH+Kdfn6aq2MV71+cH5Zj3rMkjNSEmKhLdLzVcpX9kXOdWhLnKYhdxMQ72z5C3qGnsJDslnuKs6C8pLyJUlrg4FKJy5RosLFCclcy7VuXyzOuXAw7zC5V/feU8Hf2j/M2D8x8qO5OEWCcPbixg3/HWGbsFIsXuuiYKMxJ5W2n0fyONZAmxTiqLXDNOzqtp7OS2ElfULHYUSFWxi5aeYVq6rS8qqMHCIk9uK+X6wCjPHbF//YfL1wf591cvsrPCzQZ3RlCP/Vilm+ExD/uOtQX1uKHU3jfMH85c4+HNBTiirEJpNNpWnkVDay/Xp1RLaO0ZoqlraEl0QflVFYcub6HBwiLVZVncmpfC9w80hrzu/FRf2teA0yH8xX0LGyo7m83LM1iRnczPI7gS7d76FjwGHtmsXVCRYGuZt7jjaxdu7n6pXUL5Cr9V+akkxjo1WEQyEeHD1aWcaOm9kXSzw2sXrrPveBv/z51l5Fkwd8A/5+KNi51cvj4Y9OOHwq66ZjYuz6A8N8Xupqg52OhOJyU+hv1ThtDWNnaSFOdkdX6qTS0LvVing03LMzRYRLpHNheSkRTL9/ZftOX1/VVlC9IT+C93rLDsdR6tKESEiFzn4mRLLw2tvTq3IoLEOB3cXprJwSl5i5rGLiqKXEuuUnBViYuTrb0MjIxb+jpL66yGWGKckw/cVsSLJ9poDkECaqpdh5o40dLLZ+dRVXYh8tMTeXt5NrsicM7Fs4ebiHUKD2zQ8h6RpLosi4sdAzcSu73DY5xq610S8yumqih2MeExHLnSbenrWBYsRGS5iLwiIg0ickJEPunbnikiL4nIWd9Pl297iYgMiUi97/atSceqFJFjInJORL4mETTU4fGtxYgIPzjYGNLX7R8Z5x9ePE1FUQYPbgjOUNnZPFbppqlriDdCNIwvGMYnPPyivoW7VuaSmRxnd3PUPFT78hb+UVGHL3fjMUsrX+FXUeRCBMvnW1h5ZTEOfMYYsxrYAnxCRNYAnwVeNsbcArzs+93vvDFmk+/2sUnbv4l3addbfLf7LGx3UBVmJHLv2jx+8sYVBketvUyc7BuvnKOjf4S/eXBtSIYRvmfNMlLiI2vOxf7z17nWN6JzKyLQqmWpZCbHccC3CFdtY6d3UaAoXOwokPTEWG7NTbU8b2FZsDDGtBpj6nz3+4AGoBDYATzt2+1p4OHZjiMi+UCaMeag8Q4r+kGg54SbJ7eV0jM0xi8Oh2YY7ZXOQb7z6kUe3VwYsn+exDgnD2zI54VjrZb3nQbL7rom0hNjuWtVjt1NUfPkcAhbV2Sx/3wHxnhndK8tSCM5PsbuptmissRF3eUuS7uBQ5KzEJESYDPwOpBnjGkFb0ABciftWioih0Xk9yLyDt+2QmDy19Um37bpXucpEakVkdpr164F+20sWFWxi7UFaXz/wMWQDKP98r5TOEX4bxYMlZ3Nzko3g6MT/Op4+M+56Bse48UTbTy4MZ/4GC3vEYmqy7O42jvC6at91F/pvjHnYCmqLHLRNzzOmXbrigpaHixEJAXYBXzKGNM7y66tQJExZjPwaeBHIpIGTNeHMu0nrjHm28aYKmNMVU5O+HxbFBGe3FbKmav9M5YpCJY3Lnby/LFWPvbOMvLTEy19ramqil0UZyVFRFfUvuNtDI95tAsqgvnzFt/+wwWGxzxRvTJeIP7EvpVdUZYGCxGJxRsonjHG7PZtvurrWvJ3MbUDGGNGjDHXffcPAeeBW/FeSUz+j3YD9k+LnqcHNuSTlRzH9w9YN4zW4zH87S9PkJ+ewFMWDpWdiYiws8LNwQvXaeoK7zkXu+uaKM1OZvMS7OOOFiVZSRSkJ7Cn3vtxULmEg0VRZhLZKfEcsnBOl5WjoQT4LtBgjPnqpIf2Ak/47j8B7PHtnyMiTt/9FXgT2Rd8XVV9IrLFd8wP+Z8TSRJinfzp24p4+VQ7l65bs2DQrromjjf38tn7V9lWOfVR33yF3XXNtrz+XDR1DfLahU4e3Vy4ZGoIRSMRobrcW4W2JCuJ3NSlu2CViFBZnGHpiCgrryy2AY8Dd08aDrsd+DJwj4icBe7x/Q5wB3BURI4APwc+Zozxj8P8OPAd4BzeK459FrbbMv/XlmKcIjx9IPjVaAd8Q2U3F2Xw0Eb75gy4XUlsXZHFrrom28uczMT/TfThzToRL9JVl2UBRPV623NVVZzJ5c5B2vusWRrBsqEDxphXmT7fAPCuafbfhbfLarpj1QLrgtc6e+SmJfDeDfn8rPYKn37PraQEceTGN393nmt9I/zb45W2f1t+rNLNZ352xLtiWZj9Extj2FXXxO2lmSzPjP4y1tHu7bdkkxjr5K6VuYF3jnL+bri6S13cty74c6t0BneIPbmtlL6RcX5eeyVox2zqGuTbf7zAw5sKqCiyv9/2/vXLSI5z8vPa8Et0H2nq4cK1AS3vESVyUxM49NfvZvt6Xd1wXUE6cTGOGwUVg02DRYhtWp7B5qIMnj54KWhjor+87xQOgb+4b1VQjrdYSXEx3L8+n+ePtTI0Gh7refjtrmsiPsbB/UFaAErZLykuxvar6XAQF+NgozvdsryFBgsbfLi6hIsdA/z+zOLngtQ2dvLLo6189I4yCjJCO1R2No9VuukfGefFE+Ez52J03MPeIy28Z+0y0hJi7W6OUkFXWZzJiZYeSxZd02Bhg+3r88lLi+ffF1mN1jtU9iTL0hL46DtDP1R2NreXZOJ2JYZVJdpXTrfTPTh2Y8SWUtHmgQ35/O2OdXgsGFyiwcIGsU4Hj28p5o9nOzi3iBmXzx5u5mhTD395/0qS4sKrzIHD4Z1z8eq5jpAs+TgXu+uayE6J5x3l2XY3RSlLrCtM54O3F1nyeaDBwiYfvL2IuBgH3z/QuKDne4fKnmLj8gx2bAzPb8o7K9wY4w1qduseHOW3p9rZsalgya13oFQw6H+NTbJS4tmxsYBdh5rpGRyb9/P/7ffnudo7wt88sCZs140uykri9tJMdh2yf87Fc0dbGZsw2gWl1AJpsLDRh7eVMDQ2wU/nOYy2uXuIf/vDBR7aWEBlsf1DZWfzWIWbCx0DHLZ4YZZAdtc1sWpZKmvy02xth1KRSoOFjdYWpHN7aSZPH2xkYh7DaL+y7xQAf3l/eAyVnc32DfkkxjptLS544Vo/hy93+5Z/Dc+rMKXCnQYLm31kWwlNXUO8dPLqnPY/dKmTvUda+OgdKygMo6GyM0mJj+H+dct47kiLJcP55uLZw804BHZs0i4opRZKg4XN3r06j8KMxDlVo/UOlW0gLy2ej76zLAStC46dlW76hsfnHBCDyeMx7K5r5u235JCXtnQLzSm1WBosbBbjdPChrcW8dqGThtbZlvuAPUeaOXKlm7+4d1VErQi2dUUWBekJtnRF1TR20tw9xKNaNFCpRdFgEQY+cFsRibFOvr+/ccZ9BkfH+cq+02xwp/NIhH3wORzCoxVu/nj2Gld7ramIOZPddc0kxzl5z9q8kL6uUtFGg0UYSE+K5ZGKQn5R30znwOi0+/zb7y/Q1jsc1kNlZ7Oz0o0nxHMuhscmeP5YK/evzw+7SYtKRRoNFmHiyeoSRsY9/PiNy295rKV7iH/7w3ke2JAfsXX7S7OTqSx2hXTOxa9PXqV/ZFznVigVBBoswsQteam845ZsfnjwEmMTnpse+4dfncJj4LMRMFR2No9Vujnb3s/Rpp6QvN7uuiYK0hPYUpoVktdTKpppsAgjH64uoa13mF8df7NSa93lLn5R38JT71iB2xXZi/W8d0M+8TGOkBQXbO8b5g9nrvFIRWFEdtspFW40WISRu1bmUpyVxPd81WiNMfztcyfJSY3n43dGzlDZmaQlxHLv2mXsqW9hZNzaORd761vwGHhks9vS11FqqdBgEUYcDuGJrSXUXe7myJVu9h5pof5KN39x78qIGio7m52VbnqGxni5od3S19ld18xGdzrluSmWvo5SS4UGizDzvio3KfExfOv35/nyvlOsK0xjZ0X0fDt+e3k2eWnx7LJwzkVDay8nW3t5NIrOm1J202ARZlITYnms0s2+42209gzzNw+sjao+d6dvzsXvzlzjWt+IJa/x7OFmYhzCgxsLLDm+UkuRBosw9ER1CSKwff0ybi+NzKGys9lZ4WbCY9hTH/w5F+MTHp493Mxdq3LJTI4L+vGVWqo0WISh0uxkfv6xrXx55wa7m2KJ8twUNi3P4OcWzLnYf/461/pG2KlzK5QKKg0WYaqyOJO0hFi7m2GZnZVuTrX1caJl9npY87W7ron0xFjuWpUb1OMqtdRpsFC2eGhDAXFOR1CLC/aPjPPiiTYe2JBPfIwzaMdVSmmwUDZJT4rlnjV57D3Swui4J/AT5mDfsVaGxzw6CkopC2iwULZ5rNJN58Aor5wOzpyL3XXNlGQlUVGUEZTjKaXepMFC2eYdt2STkxoflK6opq5BDl64zqMVbl06VSkLaLBQtolxOnhkcyGvnGrnev/i5lzsqW8BiLi1PpSKFBoslK12VrgZ95gbH/YLYYxhV10Tt5dmsjwzsostKhWuNFgoW61clsr6wvRFVaI90tTDhWsDunSqUhbSYKFs91ilmxMtvQHXIJ/J7rom4mIcbN+QH+SWKaX8LAsWIrJcRF4RkQYROSEin/RtzxSRl0TkrO+na9JzPici50TktIjcO2l7pYgc8z32NdEMZlR5aGMBsU5ZUHHB0XEPzx1p4T1r8qJ6EqNSdrPyymIc+IwxZjWwBfiEiKwBPgu8bIy5BXjZ9zu+xz4ArAXuA74hIv6ZVd8EngJu8d3us7DdKsRcyXG8a1Uev6hvfssqgYH87nQ7XYNjUVWZV6lwZFmwMMa0GmPqfPf7gAagENgBPO3b7WngYd/9HcBPjDEjxpiLwDngdhHJB9KMMQeNt5DQDyY9R0WJnZVuOvpH+f3pa/N63u66ZrJT4njHLdkWtUwpBSHKWYhICbAZeB3IM8a0gjegAP4iPoXAlUlPa/JtK/Tdn7pdRZE7V+aQlRw3r0R39+AoL5+6yo5NhcQ4Nf2mlJUs/w8TkRRgF/ApY8xsGczp8hBmlu3TvdZTIlIrIrXXrs3vG6qyV6zTwY5Nhfym4SpdA6Nzes5zR1sZmzA8qhVmlbKcpcFCRGLxBopnjDG7fZuv+rqW8P3013poApZPerobaPFtd0+z/S2MMd82xlQZY6pycnKC90ZUSDxW6WZswvDc0bnNudhd18TKvFTW5KdZ3DKllJWjoQT4LtBgjPnqpIf2Ak/47j8B7Jm0/QMiEi8ipXgT2W/4uqr6RGSL75gfmvQcFUXWFKSxJj9tTuU/Llzr5/Dlbh6tKNTyHkqFgJVXFtuAx4G7RaTed9sOfBm4R0TOAvf4fscYcwL4KXAS+BXwCWPMhO9YHwe+gzfpfR7YZ2G7lY12Vro52tTDmat9s+73i8PNOAQe1ol4SoVEjFUHNsa8yvT5BoB3zfCcLwJfnGZ7LbAueK1T4WrHpgK+9EIDuw418bntq6fdx+Mx7D7czLbybPLSEkLcQqWWJh1CosJKdko8d67M5dnDzYzPMOeiprGTpq4hnVuhVAhpsFBh57FKN+19I/zxXMe0j++uayY5zsl71uaFuGVKLV0aLFTYuXtVLq6k2GkT3cNjEzx/rJX71uWTFGdZL6pSagoNFirsxMV451y8dPIqPYNjNz3265NX6R8ZZ6fOrVAqpDRYqLC0s8LtLRI4Zc7Fs3VNFKQnsGVFlk0tU2pp0mChwtK6wjRW5qXeVP6jvW+YP5zt4OHNhTgcOrdCqVDSYKHCkojwWKWbw5e7OX+tH4C99S1MeLS8h1J20GChwtaOzQU4HW+uc7G7rpmN7nTKc1NtbplSS48GCxW2clMTeOetOeyua+ZESw8nW3t5RGdsK2ULDRYqrO2scNPWO8zndh8jxiE8uLHA7iYptSRpsFBh7V2rc0lPjOVoUw93rswlKyXe7iYptSRpsFBhLSHWyYMb8wF0boVSNtIpsCrsffSOMhJjnbxrtZb3UMouGixU2FuemcR/f+8au5uh1JKm3VBKKaUC0mChlFIqIA0WSimlAtJgoZRSKiANFkoppQLSYKGUUiogDRZKKaUC0mChlFIqIDHG2N0GS4jINeCS3e1YpGygw+5GhAk9FzfT83EzPR9vWuy5KDbG5EzdGLXBIhqISK0xpsrudoQDPRc30/NxMz0fb7LqXGg3lFJKqYA0WCillApIg0V4+7bdDQgjei5upufjZno+3mTJudCchVJKqYD0ykIppVRAGiyUUkoFpMEihERkuYi8IiINInJCRD7p254pIi+JyFnfT5dve5Zv/34R+fqk4ySJyPMicsp3nC/b9Z4WKljnYsox94rI8VC+j2AJ5vkQkTgR+baInPH9jey04z0tRpDPxwdF5JiIHBWRX4lIth3vaaEWcC7uEZFDvvd8SETunnSsSt/2cyLyNRGROTfEGKO3EN2AfKDCdz8VOAOsAf4B+Kxv+2eBr/juJwNvBz4GfH3ScZKAu3z344A/Avfb/f7sOBeTjvco8CPguN3vze7zAfxP4O989x1Att3vz67zgXc10Hb/OfA9//N2vz+Lz8VmoMB3fx3QPOlYbwBbAQH2zedzQ68sQsgY02qMqfPd7wMagEJgB/C0b7engYd9+wwYY14FhqccZ9AY84rv/ihQB7hD8R6CJVjnAkBEUoBPA39nfcutEczzAXwE+JJvP48xJuJmNgfxfIjvluz7Fp0GtFj+BoJoAefisDHG/x5PAAkiEi8i+UCaMeag8UaOH/ifMxcaLGwiIiV4vwG8DuQZY1rB+4cB5M7jOBnAg8DLwW9laAThXHwB+F/AoFVtDKXFnA/f3wPAF0SkTkR+JiJ5FjbXcos5H8aYMeDjwDG8QWIN8F0r22ulBZyLncBhY8wI3gDTNOmxJt+2OdFgYQPfN+FdwKeMMb2LOE4M8GPga8aYC8FqXygt9lyIyCag3BjzbLDbZocg/G3E4L3K3G+MqQAOAv8UxCaGVBD+PmLxBovNQAFwFPhcUBsZIvM9FyKyFvgK8FH/pml2m/PcCQ0WIeb7490FPGOM2e3bfNV3iYjvZ/scD/dt4Kwx5p+D3tAQCNK52ApUikgj8Cpwq4j8zpoWWytI5+M63issf/D8GVBhQXMtF6TzsQnAGHPe1/XyU6DamhZbZ77nQkTceP8GPmSMOe/b3MTN3dVu5tElp8EihHx9pt8FGowxX5300F7gCd/9J4A9czjW3wHpwKeC3MyQCNa5MMZ80xhTYIwpwZvgPGOMuTP4LbZWEM+HAZ4D7vRtehdwMqiNDYEg/q80A2tExF9F9R68ff4RY77nwtcV+TzwOWPMfv/Ovq6qPhHZ4jvmh5jDZ80Ndmf6l9IN74eZwXspXO+7bQey8OYczvp+Zk56TiPQCfTj/WawBu83AoP3j95/nP9s9/uz41xMOWYJkTsaKmjnAygG/uA71stAkd3vz+bz8THf/8pRvIE0y+73Z+W5AP5fYGDSvvVAru+xKuA4cB74Or4qHnO5abkPpZRSAWk3lFJKqYA0WCillApIg4VSSqmANFgopZQKSIOFUkqpgDRYKBUk4vWqiNw/adv7ReRXdrZLqWDQobNKBZGIrMM7a3oz4MQ7xv0+8+Ys2vkcy2mMmQhuC5VaGA0WSgWZiPwD3klRyb6fxcB6vHWbPm+M2eMrCPdD3z4Af2aMOSAidwL/A2gFNhlj1oS29UpNT4OFUkEmIsl4y8aPAr8EThhj/j9fGYY38F51GMBjjBkWkVuAHxtjqnzB4nlgnTHmoh3tV2o6MXY3QKloY4wZEJH/wFt24v3AgyLy576HE4AivAXcvu6rmjsB3DrpEG9ooFDhRoOFUtbw+G4C7DTGnJ78oIh8HrgKbMQ70GTyoj0DIWqjUnOmo6GUstaLwH/1r3UsIpt929OBVmOMB3gcbzJcqbClwUIpa30BiAWOishx3+8A3wCeEJHX8HZB6dWECmua4FZKKRWQXlkopZQKSIOFUkqpgDRYKKWUCkiDhVJKqYA0WCillApIg4VSSqmANFgopZQK6P8HA01GY068vj0AAAAASUVORK5CYII=\n",
"text/plain": [
"
"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"station_df = df[df[\"ID\"] == \"AEM00041194\"]\n",
"sns.lineplot(data = station_df, x = \"Year\", y = \"VALUE1\")"
]
},
{
"cell_type": "markdown",
"id": "4f9c8609",
"metadata": {},
"source": [
"At this station, the average temperature in January was typically between 20 and 22 degrees Celsius during the timeframe. \n",
"\n",
"While it can be useful to have the temperate time series, there's a lot of information missing from this plot. For example, we're only plotting for a single month of the year, and we are also only showing the observations from a single weather station. In this lecture, our goal will be to construct the following plot: \n",
"\n",
"\n",
"\n",
"This is a plot of average temperatures over time for a selection of countries (ones whose names begin with E). In order to make this plot, there are two major data manipulation operations we need to perform. We need to: \n",
"\n",
"1. Figure out what *country* each station is in. \n",
"2. Reshape the 12 month columns into a single long column. \n",
"\n",
"These two tasks correspond to *merging* and *stacking*. These operations will come up at key points in our workflow. \n",
"\n",
"### Adding Countries with Merges\n",
"\n",
"A *merge* is an operation that combines two related data frames into a single data frame, using one or more columns as *keys*. Let's take a look at how this works. The first thing we'll do is acquire a data frame that gives the full country name corresponding to the FIPS code. The FIPS code is an internationally standardized abbreviation for a country: "
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "c2425bdb",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
FIPS 10-4
\n",
"
ISO 3166
\n",
"
Name
\n",
"
\n",
" \n",
" \n",
"
\n",
"
0
\n",
"
AF
\n",
"
AF
\n",
"
Afghanistan
\n",
"
\n",
"
\n",
"
1
\n",
"
AX
\n",
"
-
\n",
"
Akrotiri
\n",
"
\n",
"
\n",
"
2
\n",
"
AL
\n",
"
AL
\n",
"
Albania
\n",
"
\n",
"
\n",
"
3
\n",
"
AG
\n",
"
DZ
\n",
"
Algeria
\n",
"
\n",
"
\n",
"
4
\n",
"
AQ
\n",
"
AS
\n",
"
American Samoa
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" FIPS 10-4 ISO 3166 Name\n",
"0 AF AF Afghanistan\n",
"1 AX - Akrotiri\n",
"2 AL AL Albania\n",
"3 AG DZ Algeria\n",
"4 AQ AS American Samoa"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"countries_url = \"https://raw.githubusercontent.com/mysociety/gaze/master/data/fips-10-4-to-iso-country-codes.csv\"\n",
"countries = pd.read_csv(countries_url)\n",
"countries.head(5)"
]
},
{
"cell_type": "markdown",
"id": "603843c9",
"metadata": {},
"source": [
"How can we relate this to our `df` of temperature readings? Well, it turns out that the first two characters of the `ID` column give the FIPS code! "
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "3a759034",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"0 ACW00011604\n",
"1 ACW00011604\n",
"2 ACW00011604\n",
"3 AE000041196\n",
"4 AE000041196\n",
"Name: ID, dtype: object"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df[\"ID\"].head(5)"
]
},
{
"cell_type": "markdown",
"id": "baf07dbc",
"metadata": {},
"source": [
"Let's extract them with the `str` attribute. Note that I am creating a new column whose name matches exactly the corresponding column in the `countries` data frame. "
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "5d05ce7c",
"metadata": {},
"outputs": [],
"source": [
"df[\"FIPS 10-4\"] = df[\"ID\"].str[0:2]"
]
},
{
"cell_type": "markdown",
"id": "75261075",
"metadata": {},
"source": [
"Now it's merge time. `pandas` provides a `merge()` function with many different arguments, as well as a related `DataFrame.join()` method. There are many different ways to merge two data sets, as explained in [this helpful chapter](https://jakevdp.github.io/PythonDataScienceHandbook/03.07-merge-and-join.html) of the Python Data Science Handbook. The default for `pd.merge` is an *inner* merge. "
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "acf76d50",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
ID
\n",
"
Year
\n",
"
VALUE1
\n",
"
VALUE2
\n",
"
VALUE3
\n",
"
VALUE4
\n",
"
VALUE5
\n",
"
VALUE6
\n",
"
VALUE7
\n",
"
VALUE8
\n",
"
VALUE9
\n",
"
VALUE10
\n",
"
VALUE11
\n",
"
VALUE12
\n",
"
FIPS 10-4
\n",
"
ISO 3166
\n",
"
Name
\n",
"
\n",
" \n",
" \n",
"
\n",
"
0
\n",
"
ACW00011604
\n",
"
2011
\n",
"
-83.0
\n",
"
-132.0
\n",
"
278.0
\n",
"
1040.0
\n",
"
1213.0
\n",
"
1663.0
\n",
"
1875.0
\n",
"
1723.0
\n",
"
1466.0
\n",
"
987.0
\n",
"
721.0
\n",
"
428.0
\n",
"
AC
\n",
"
AG
\n",
"
Antigua and Barbuda
\n",
"
\n",
"
\n",
"
1
\n",
"
ACW00011604
\n",
"
2012
\n",
"
121.0
\n",
"
-98.0
\n",
"
592.0
\n",
"
646.0
\n",
"
1365.0
\n",
"
1426.0
\n",
"
1771.0
\n",
"
1748.0
\n",
"
1362.0
\n",
"
826.0
\n",
"
620.0
\n",
"
-234.0
\n",
"
AC
\n",
"
AG
\n",
"
Antigua and Barbuda
\n",
"
\n",
"
\n",
"
2
\n",
"
ACW00011604
\n",
"
2013
\n",
"
-104.0
\n",
"
-93.0
\n",
"
-48.0
\n",
"
595.0
\n",
"
NaN
\n",
"
1612.0
\n",
"
1855.0
\n",
"
1802.0
\n",
"
1359.0
\n",
"
1042.0
\n",
"
601.0
\n",
"
NaN
\n",
"
AC
\n",
"
AG
\n",
"
Antigua and Barbuda
\n",
"
\n",
"
\n",
"
3
\n",
"
AE000041196
\n",
"
2011
\n",
"
1950.0
\n",
"
2060.0
\n",
"
2280.0
\n",
"
2760.0
\n",
"
3240.0
\n",
"
3447.0
\n",
"
3580.0
\n",
"
3650.0
\n",
"
3316.0
\n",
"
2940.0
\n",
"
2390.0
\n",
"
1905.0
\n",
"
AE
\n",
"
AE
\n",
"
United Arab Emirates
\n",
"
\n",
"
\n",
"
4
\n",
"
AE000041196
\n",
"
2012
\n",
"
1837.0
\n",
"
1987.0
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
AE
\n",
"
AE
\n",
"
United Arab Emirates
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" ID Year VALUE1 VALUE2 VALUE3 VALUE4 VALUE5 VALUE6 VALUE7 \\\n",
"0 ACW00011604 2011 -83.0 -132.0 278.0 1040.0 1213.0 1663.0 1875.0 \n",
"1 ACW00011604 2012 121.0 -98.0 592.0 646.0 1365.0 1426.0 1771.0 \n",
"2 ACW00011604 2013 -104.0 -93.0 -48.0 595.0 NaN 1612.0 1855.0 \n",
"3 AE000041196 2011 1950.0 2060.0 2280.0 2760.0 3240.0 3447.0 3580.0 \n",
"4 AE000041196 2012 1837.0 1987.0 NaN NaN NaN NaN NaN \n",
"\n",
" VALUE8 VALUE9 VALUE10 VALUE11 VALUE12 FIPS 10-4 ISO 3166 \\\n",
"0 1723.0 1466.0 987.0 721.0 428.0 AC AG \n",
"1 1748.0 1362.0 826.0 620.0 -234.0 AC AG \n",
"2 1802.0 1359.0 1042.0 601.0 NaN AC AG \n",
"3 3650.0 3316.0 2940.0 2390.0 1905.0 AE AE \n",
"4 NaN NaN NaN NaN NaN AE AE \n",
"\n",
" Name \n",
"0 Antigua and Barbuda \n",
"1 Antigua and Barbuda \n",
"2 Antigua and Barbuda \n",
"3 United Arab Emirates \n",
"4 United Arab Emirates "
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = pd.merge(df, countries, on = [\"FIPS 10-4\"])\n",
"df.head(5)"
]
},
{
"cell_type": "markdown",
"id": "79b7aac2",
"metadata": {},
"source": [
"What's happened here is the following: \n",
"\n",
"1. If the FIPS code in a row of `df` matches a FIPS code in `countries`, then the corresponding columns of `countries` are added to that row in the `merge`d result. \n",
"2. If the FIPS code in a row of `df` is not found in `countries`, then that row is no longer present in the `merge`d result. \n",
"\n",
"Other behavior is possible. For example, one might instead prefer that, in case 2, the corresponding parts of the row are populated with `NA` values. This corresponds to a *left* join (rather than the default *inner* join). \n",
"\n",
"We now have a few unnecessary columns, so we'll remove them. "
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "c37c7297",
"metadata": {},
"outputs": [],
"source": [
"df = df.drop([\"FIPS 10-4\", \"ISO 3166\"], axis = 1)"
]
},
{
"cell_type": "markdown",
"id": "6c0c1477",
"metadata": {},
"source": [
"### Stacking\n",
"\n",
"Recall that we are aiming to create this figure: \n",
"\n",
"\n",
"\n",
"We now know the country of each observation station, so that's good progress! But we have a bit of a problem now: we'd like to be able to plot all the months in the same time series in the logical way. We can't do this right now because each month's data is in a separate column. How can we create a single column containing the temperature observations? \n",
"\n",
"The answer is that we can *stack* these columns into a single column. `pandas` provides a convenient method for `stack`ing and `unstack`ing data: \n",
"\n",
"\n",
"\n",
"\n",
"\n",
"Here's how. First, we convert all the columns that we *don't* want to stack into a multi-index for the data frame. "
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "420ac7c7",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
\n",
"
\n",
"
VALUE1
\n",
"
VALUE2
\n",
"
VALUE3
\n",
"
VALUE4
\n",
"
VALUE5
\n",
"
VALUE6
\n",
"
VALUE7
\n",
"
VALUE8
\n",
"
VALUE9
\n",
"
VALUE10
\n",
"
VALUE11
\n",
"
VALUE12
\n",
"
\n",
"
\n",
"
ID
\n",
"
Year
\n",
"
Name
\n",
"
\n",
"
\n",
"
\n",
"
\n",
"
\n",
"
\n",
"
\n",
"
\n",
"
\n",
"
\n",
"
\n",
"
\n",
"
\n",
" \n",
" \n",
"
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
-83.0
\n",
"
-132.0
\n",
"
278.0
\n",
"
1040.0
\n",
"
1213.0
\n",
"
1663.0
\n",
"
1875.0
\n",
"
1723.0
\n",
"
1466.0
\n",
"
987.0
\n",
"
721.0
\n",
"
428.0
\n",
"
\n",
"
\n",
"
2012
\n",
"
Antigua and Barbuda
\n",
"
121.0
\n",
"
-98.0
\n",
"
592.0
\n",
"
646.0
\n",
"
1365.0
\n",
"
1426.0
\n",
"
1771.0
\n",
"
1748.0
\n",
"
1362.0
\n",
"
826.0
\n",
"
620.0
\n",
"
-234.0
\n",
"
\n",
"
\n",
"
2013
\n",
"
Antigua and Barbuda
\n",
"
-104.0
\n",
"
-93.0
\n",
"
-48.0
\n",
"
595.0
\n",
"
NaN
\n",
"
1612.0
\n",
"
1855.0
\n",
"
1802.0
\n",
"
1359.0
\n",
"
1042.0
\n",
"
601.0
\n",
"
NaN
\n",
"
\n",
"
\n",
"
AE000041196
\n",
"
2011
\n",
"
United Arab Emirates
\n",
"
1950.0
\n",
"
2060.0
\n",
"
2280.0
\n",
"
2760.0
\n",
"
3240.0
\n",
"
3447.0
\n",
"
3580.0
\n",
"
3650.0
\n",
"
3316.0
\n",
"
2940.0
\n",
"
2390.0
\n",
"
1905.0
\n",
"
\n",
"
\n",
"
2012
\n",
"
United Arab Emirates
\n",
"
1837.0
\n",
"
1987.0
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
NaN
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" VALUE1 VALUE2 VALUE3 VALUE4 VALUE5 \\\n",
"ID Year Name \n",
"ACW00011604 2011 Antigua and Barbuda -83.0 -132.0 278.0 1040.0 1213.0 \n",
" 2012 Antigua and Barbuda 121.0 -98.0 592.0 646.0 1365.0 \n",
" 2013 Antigua and Barbuda -104.0 -93.0 -48.0 595.0 NaN \n",
"AE000041196 2011 United Arab Emirates 1950.0 2060.0 2280.0 2760.0 3240.0 \n",
" 2012 United Arab Emirates 1837.0 1987.0 NaN NaN NaN \n",
"\n",
" VALUE6 VALUE7 VALUE8 VALUE9 \\\n",
"ID Year Name \n",
"ACW00011604 2011 Antigua and Barbuda 1663.0 1875.0 1723.0 1466.0 \n",
" 2012 Antigua and Barbuda 1426.0 1771.0 1748.0 1362.0 \n",
" 2013 Antigua and Barbuda 1612.0 1855.0 1802.0 1359.0 \n",
"AE000041196 2011 United Arab Emirates 3447.0 3580.0 3650.0 3316.0 \n",
" 2012 United Arab Emirates NaN NaN NaN NaN \n",
"\n",
" VALUE10 VALUE11 VALUE12 \n",
"ID Year Name \n",
"ACW00011604 2011 Antigua and Barbuda 987.0 721.0 428.0 \n",
" 2012 Antigua and Barbuda 826.0 620.0 -234.0 \n",
" 2013 Antigua and Barbuda 1042.0 601.0 NaN \n",
"AE000041196 2011 United Arab Emirates 2940.0 2390.0 1905.0 \n",
" 2012 United Arab Emirates NaN NaN NaN "
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = df.set_index(keys=[\"ID\", \"Year\", \"Name\"])\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"id": "b9f5da1f",
"metadata": {},
"source": [
"Then, we call the `stack()` method. This has the effect of \"stacking\" all of the data values on top of each other. There's also a new column indicating which of the original columns the observation came from: "
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "8dd5bea0",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"ID Year Name \n",
"ACW00011604 2011 Antigua and Barbuda VALUE1 -83.0\n",
" VALUE2 -132.0\n",
" VALUE3 278.0\n",
" VALUE4 1040.0\n",
" VALUE5 1213.0\n",
" ... \n",
"ZI000067983 2016 Zimbabwe VALUE5 1692.0\n",
" VALUE6 1681.0\n",
" VALUE8 1828.0\n",
" VALUE10 2334.0\n",
" VALUE12 2287.0\n",
"Length: 1504990, dtype: float64"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = df.stack()\n",
"df"
]
},
{
"cell_type": "markdown",
"id": "3076f76f",
"metadata": {},
"source": [
"We can recover the `ID`, `Year`, and `Name` columns using `reset_index()`: "
]
},
{
"cell_type": "code",
"execution_count": 13,
"id": "111f564d",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
ID
\n",
"
Year
\n",
"
Name
\n",
"
level_3
\n",
"
0
\n",
"
\n",
" \n",
" \n",
"
\n",
"
0
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
VALUE1
\n",
"
-83.0
\n",
"
\n",
"
\n",
"
1
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
VALUE2
\n",
"
-132.0
\n",
"
\n",
"
\n",
"
2
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
VALUE3
\n",
"
278.0
\n",
"
\n",
"
\n",
"
3
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
VALUE4
\n",
"
1040.0
\n",
"
\n",
"
\n",
"
4
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
VALUE5
\n",
"
1213.0
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" ID Year Name level_3 0\n",
"0 ACW00011604 2011 Antigua and Barbuda VALUE1 -83.0\n",
"1 ACW00011604 2011 Antigua and Barbuda VALUE2 -132.0\n",
"2 ACW00011604 2011 Antigua and Barbuda VALUE3 278.0\n",
"3 ACW00011604 2011 Antigua and Barbuda VALUE4 1040.0\n",
"4 ACW00011604 2011 Antigua and Barbuda VALUE5 1213.0"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = df.reset_index()\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"id": "6fa18734",
"metadata": {},
"source": [
"This is looking pretty good, except that the final two columns aren't labeled very appropriately. Let's fix that up: "
]
},
{
"cell_type": "code",
"execution_count": 14,
"id": "2e0e10e4",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
ID
\n",
"
Year
\n",
"
Name
\n",
"
Month
\n",
"
Temperature (C)
\n",
"
\n",
" \n",
" \n",
"
\n",
"
0
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
VALUE1
\n",
"
-83.0
\n",
"
\n",
"
\n",
"
1
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
VALUE2
\n",
"
-132.0
\n",
"
\n",
"
\n",
"
2
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
VALUE3
\n",
"
278.0
\n",
"
\n",
"
\n",
"
3
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
VALUE4
\n",
"
1040.0
\n",
"
\n",
"
\n",
"
4
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
VALUE5
\n",
"
1213.0
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" ID Year Name Month Temperature (C)\n",
"0 ACW00011604 2011 Antigua and Barbuda VALUE1 -83.0\n",
"1 ACW00011604 2011 Antigua and Barbuda VALUE2 -132.0\n",
"2 ACW00011604 2011 Antigua and Barbuda VALUE3 278.0\n",
"3 ACW00011604 2011 Antigua and Barbuda VALUE4 1040.0\n",
"4 ACW00011604 2011 Antigua and Barbuda VALUE5 1213.0"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = df.rename(columns = {\"level_3\" : \"Month\" , 0 : \"Temperature (C)\"})\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"id": "f4333c9b",
"metadata": {},
"source": [
"Better! We are now very close to our goal. The final step is to create a `datetime` column that reflects both the year and month. First, we can extract the month by picking out everything after the `\"VALUE\"` in the `Month` column. "
]
},
{
"cell_type": "code",
"execution_count": 15,
"id": "dff3c5e3",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
ID
\n",
"
Year
\n",
"
Name
\n",
"
Month
\n",
"
Temperature (C)
\n",
"
\n",
" \n",
" \n",
"
\n",
"
0
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
1
\n",
"
-83.0
\n",
"
\n",
"
\n",
"
1
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
2
\n",
"
-132.0
\n",
"
\n",
"
\n",
"
2
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
3
\n",
"
278.0
\n",
"
\n",
"
\n",
"
3
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
4
\n",
"
1040.0
\n",
"
\n",
"
\n",
"
4
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
5
\n",
"
1213.0
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" ID Year Name Month Temperature (C)\n",
"0 ACW00011604 2011 Antigua and Barbuda 1 -83.0\n",
"1 ACW00011604 2011 Antigua and Barbuda 2 -132.0\n",
"2 ACW00011604 2011 Antigua and Barbuda 3 278.0\n",
"3 ACW00011604 2011 Antigua and Barbuda 4 1040.0\n",
"4 ACW00011604 2011 Antigua and Barbuda 5 1213.0"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df[\"Month\"] = df[\"Month\"].str[5:].astype(int)\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"id": "4e2732ec",
"metadata": {},
"source": [
"Next, we'll create a new `datetime` column called `Date`. To do so, we can first create a string of the form `YYYY-MM`: "
]
},
{
"cell_type": "code",
"execution_count": 16,
"id": "b48ac711",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
ID
\n",
"
Year
\n",
"
Name
\n",
"
Month
\n",
"
Temperature (C)
\n",
"
Date
\n",
"
\n",
" \n",
" \n",
"
\n",
"
0
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
1
\n",
"
-83.0
\n",
"
2011-1
\n",
"
\n",
"
\n",
"
1
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
2
\n",
"
-132.0
\n",
"
2011-2
\n",
"
\n",
"
\n",
"
2
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
3
\n",
"
278.0
\n",
"
2011-3
\n",
"
\n",
"
\n",
"
3
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
4
\n",
"
1040.0
\n",
"
2011-4
\n",
"
\n",
"
\n",
"
4
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
5
\n",
"
1213.0
\n",
"
2011-5
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" ID Year Name Month Temperature (C) Date\n",
"0 ACW00011604 2011 Antigua and Barbuda 1 -83.0 2011-1\n",
"1 ACW00011604 2011 Antigua and Barbuda 2 -132.0 2011-2\n",
"2 ACW00011604 2011 Antigua and Barbuda 3 278.0 2011-3\n",
"3 ACW00011604 2011 Antigua and Barbuda 4 1040.0 2011-4\n",
"4 ACW00011604 2011 Antigua and Barbuda 5 1213.0 2011-5"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df[\"Date\"] = df[\"Year\"].astype(str) + \"-\" + df[\"Month\"].astype(str)\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"id": "655e99a4",
"metadata": {},
"source": [
"We can convert the result to a `DateTime` using a built-in `pandas` function. The nice thing about this function is that it can automatically detect several common formats of date-time strings. "
]
},
{
"cell_type": "code",
"execution_count": 17,
"id": "74431d7e",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
ID
\n",
"
Year
\n",
"
Name
\n",
"
Month
\n",
"
Temperature (C)
\n",
"
Date
\n",
"
\n",
" \n",
" \n",
"
\n",
"
0
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
1
\n",
"
-83.0
\n",
"
2011-01-01
\n",
"
\n",
"
\n",
"
1
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
2
\n",
"
-132.0
\n",
"
2011-02-01
\n",
"
\n",
"
\n",
"
2
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
3
\n",
"
278.0
\n",
"
2011-03-01
\n",
"
\n",
"
\n",
"
3
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
4
\n",
"
1040.0
\n",
"
2011-04-01
\n",
"
\n",
"
\n",
"
4
\n",
"
ACW00011604
\n",
"
2011
\n",
"
Antigua and Barbuda
\n",
"
5
\n",
"
1213.0
\n",
"
2011-05-01
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" ID Year Name Month Temperature (C) Date\n",
"0 ACW00011604 2011 Antigua and Barbuda 1 -83.0 2011-01-01\n",
"1 ACW00011604 2011 Antigua and Barbuda 2 -132.0 2011-02-01\n",
"2 ACW00011604 2011 Antigua and Barbuda 3 278.0 2011-03-01\n",
"3 ACW00011604 2011 Antigua and Barbuda 4 1040.0 2011-04-01\n",
"4 ACW00011604 2011 Antigua and Barbuda 5 1213.0 2011-05-01"
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df[\"Date\"] = pd.to_datetime(df[\"Date\"])\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"id": "b8f6a80f",
"metadata": {},
"source": [
"### Plotting\n",
"\n",
"We are finally ready to make our plot! First, we compute the average temperature across all stations in each month, and divide by 100 to get units of degrees Celsius. "
]
},
{
"cell_type": "code",
"execution_count": 18,
"id": "a756a4b1",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"