r-to-sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "884cfa80-2650-4d96-bd56-7851e028e1e5",
   "metadata": {
    "tags": []
   },
   "source": [
    "# SSC Data Science and Analytics Workshop 2022\n",
    "\n",
    "### Intro to Databases in Industry: Data Cleaning, Querying, and Modeling at Scale\n",
    "---------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5550bd6-94a4-41f4-9711-2c0fbef07910",
   "metadata": {},
   "source": [
    "SQL is powerful, fast, and reliable. But unfortunately, queries can quickly become complex, even for routine data wrangling. \n",
    "\n",
    "Languages like R  and Python have powerful packages, such as `tidyverse` and `pandas`, that are designed to facilitate wrangling and cleaning data. The disadvantage is that they are not as fast as SQL. Luckily for us, we can connect R directly to the databases. Not only that, but we can use the usual `tidyverse` verbs, and `dbplyr` will generate the SQL queries for us! So we can have the best of both worlds! \n",
    "\n",
    "In this part of the workshop, we will explore the R$\\leftrightarrow$SQL interface. \n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe75e236-8e1d-4b00-9ae5-9a454bf5cac6",
   "metadata": {},
   "source": [
    "## 1. Connecting `R` to a database "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44a258a2-0c92-4165-bcf4-28592ef94038",
   "metadata": {},
   "source": [
    "We will use the `DBI` package to connect R to a database. There are many different database management systems (DBMS) vendors out there (e.g., Oracle, Microsoft, Postgres, MySQL). Although all these DBMS are somewhat similar, they have some differences. For this reason, we need to tell the `DBI` package which database we want to connect to. In our case here, we are using the `PostgreSQL` DBMS. We need to install the PostgreSQL backend for DBI, which is the package `RPostgres`. \n",
    "\n",
    "Finally, the package [dbplyr](https://dbplyr.tidyverse.org/) creates the interface with the database and converts the `dplyr` verbs into SQL queries. How does that work? Very similarly to if you had loaded the tables into R as data frames.\n",
    "\n",
    "\n",
    "Let's start by creating the connection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "c0ad174c-9db1-47d1-8012-a9104cbc7d61",
   "metadata": {},
   "outputs": [],
   "source": [
    "library(tidyverse) # dbplyr is part of tidyverse metapackage\n",
    "library(RPostgres)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87d6350d-7cfe-49f3-9f49-1565e629b39c",
   "metadata": {},
   "source": [
    "**Exercise 1.1** \n",
    "\n",
    "Connect R to the `imdb` database located at `ssc-2022-workshop.ct6ghoz7smhy.us-east-1.rds.amazonaws.com`. \n",
    "Your username is `ssc_workshop` and password `sql_for_ds`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "9f4831ec-2dc9-4fda-b127-fcf758a948fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connection = dbConnect(\n",
    "#     drv = Postgres(), \n",
    "#     user = ..., \n",
    "#     password = ..., \n",
    "#     port = 5432, # this is the default port for postgres \n",
    "#     dbname = ..., \n",
    "#     host = ...)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d3d3c93-e508-4c85-a6ee-70b017a65d83",
   "metadata": {},
   "source": [
    "Congratulations!!! R is now connected to the database. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee164827-7856-46cc-820a-b4166c326270",
   "metadata": {},
   "source": [
    "## 2. Retrieving data from a database with R"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f06d79a7-7cb2-4a9a-9540-752a67a23648",
   "metadata": {},
   "source": [
    "Now that we have the connection ready to go, we can pull data from the database. But before we start pulling data from tables, it is useful to get some information about the database itself (e.g., what tables there are in a database, what are the fields of a table): \n",
    "\n",
    "- `dbListTables(connection)`: list all tables in the database accessed in connection;+\n",
    "- `dbListFields(connection, table_name)`: List all columns of `table_name` in the database "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "879bdb9f-04ce-4eac-bccb-87bde73e3888",
   "metadata": {
    "tags": []
   },
   "source": [
    "**Exercise 2.1**\n",
    "\n",
    "List all tables of the `imdb` database. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "14b3f1c5-3e0d-4381-a3b4-036e07d777b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code goes here. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5573aabb-9e86-40fc-b210-7404cc317866",
   "metadata": {
    "tags": []
   },
   "source": [
    "**Exercise 2.2**\n",
    "\n",
    "List all columns from of the `movies` relation in the `imdb` database. (Note: relation is just another name for table in the database literature.) \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "9ce0fd8f-9c68-4510-9320-3ad4f77be71c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code goes here. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d403916-d04d-47cc-a18b-74d6c5797612",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 2.1 Wrangling data with `dbplyr`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2547d095-1491-4c97-ac48-05ba8aae9545",
   "metadata": {},
   "source": [
    "With `dbplyr`, we can work with a database table like it was loaded into memory (but it isn't!). \n",
    "\n",
    "To \"read\" a table from a database we can use the [dplyr::tbl](https://dplyr.tidyverse.org/reference/tbl.html) function. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82ad3c5d-d4d2-418f-bd0b-577e958b4301",
   "metadata": {},
   "source": [
    "**Example**\n",
    "\n",
    "Read the `movies` table from the `imdb` database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "bb3a5cb0-4c34-463b-8e50-2843864dff2d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[90m# Source:   table<movies> [?? x 8]\u001b[39m\n",
       "\u001b[90m# Database: postgres [postgres@localhost:5432/imdb]\u001b[39m\n",
       "         id title         orig_title   start_year end_year runtime rating nvotes\n",
       "      \u001b[3m\u001b[90m<int>\u001b[39m\u001b[23m \u001b[3m\u001b[90m<chr>\u001b[39m\u001b[23m         \u001b[3m\u001b[90m<chr>\u001b[39m\u001b[23m             \u001b[3m\u001b[90m<int>\u001b[39m\u001b[23m    \u001b[3m\u001b[90m<int>\u001b[39m\u001b[23m   \u001b[3m\u001b[90m<int>\u001b[39m\u001b[23m  \u001b[3m\u001b[90m<dbl>\u001b[39m\u001b[23m  \u001b[3m\u001b[90m<int>\u001b[39m\u001b[23m\n",
       "\u001b[90m 1\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m\u001b[4m5\u001b[24m423 Kate & Leopo~ \u001b[31mNA\u001b[39m                 \u001b[4m2\u001b[24m001       \u001b[31mNA\u001b[39m     118    6.4  \u001b[4m7\u001b[24m\u001b[4m4\u001b[24m982\n",
       "\u001b[90m 2\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m4\u001b[24m\u001b[4m2\u001b[24m742 Mister 880    \u001b[31mNA\u001b[39m                 \u001b[4m1\u001b[24m950       \u001b[31mNA\u001b[39m      90    7.1   \u001b[4m1\u001b[24m171\n",
       "\u001b[90m 3\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m4\u001b[24m\u001b[4m1\u001b[24m181 Black Hand    \u001b[31mNA\u001b[39m                 \u001b[4m1\u001b[24m950       \u001b[31mNA\u001b[39m      92    6.4    666\n",
       "\u001b[90m 4\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m4\u001b[24m\u001b[4m1\u001b[24m387 Francis       \u001b[31mNA\u001b[39m                 \u001b[4m1\u001b[24m950       \u001b[31mNA\u001b[39m      91    6.4    979\n",
       "\u001b[90m 5\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m4\u001b[24m\u001b[4m1\u001b[24m719 Orpheus       Orphée             \u001b[4m1\u001b[24m950       \u001b[31mNA\u001b[39m      95    8     \u001b[4m9\u001b[24m346\n",
       "\u001b[90m 6\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m4\u001b[24m\u001b[4m1\u001b[24m931 Stromboli     Stromboli, ~       \u001b[4m1\u001b[24m950       \u001b[31mNA\u001b[39m     107    7.3   \u001b[4m5\u001b[24m239\n",
       "\u001b[90m 7\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m4\u001b[24m\u001b[4m2\u001b[24m052 Woman in Hid~ \u001b[31mNA\u001b[39m                 \u001b[4m1\u001b[24m950       \u001b[31mNA\u001b[39m      92    6.9    553\n",
       "\u001b[90m 8\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m4\u001b[24m\u001b[4m2\u001b[24m179 Abbott and C~ \u001b[31mNA\u001b[39m                 \u001b[4m1\u001b[24m950       \u001b[31mNA\u001b[39m      80    6.6   \u001b[4m2\u001b[24m573\n",
       "\u001b[90m 9\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m4\u001b[24m\u001b[4m2\u001b[24m200 Annie Get Yo~ \u001b[31mNA\u001b[39m                 \u001b[4m1\u001b[24m950       \u001b[31mNA\u001b[39m     107    6.9   \u001b[4m4\u001b[24m050\n",
       "\u001b[90m10\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m4\u001b[24m\u001b[4m2\u001b[24m206 Armored Car ~ \u001b[31mNA\u001b[39m                 \u001b[4m1\u001b[24m950       \u001b[31mNA\u001b[39m      67    7     \u001b[4m2\u001b[24m077\n",
       "\u001b[90m# ... with more rows\u001b[39m"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "(movies <- tbl(connection, 'movies'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcb80f51-9170-401f-bb7d-e786dfc4f0af",
   "metadata": {},
   "source": [
    "Now we can treat the `movies` variable like a regular tibble that was loaded into memory (although, again, it isn't) and use all usual `tidyverse` verbs to wrangle, and explore the data. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1087e3dc-aee7-45e1-8145-3912fc6b696a",
   "metadata": {},
   "source": [
    "**Exercise 2.1.1**\n",
    "\n",
    "What are the top rated movies produced after 2000 with more than 500 votes? Remove the `id`, `orig_title` and `end_year` columns. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "304ab52d-086f-450c-a645-53383dc1fcbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code goes here. \n",
    "#top_rated_movies <- ..."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0148b1d4-1f51-4e3b-9ca2-fd42ecc4e6da",
   "metadata": {},
   "source": [
    "All evaluations are lazy when using `dbplyr` as the backend of `dplyr` (i.e., the data is not retrieved until requested). So what the command actually does is generate the SQL code. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd911280-ecc7-41e5-9b6d-3d595f21cc58",
   "metadata": {},
   "source": [
    "We can check the generated SQL code using the `show_query` function. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bc7d157-0ba6-4896-9126-7adfda17d2de",
   "metadata": {},
   "source": [
    "**Example**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "06133d91-761b-4f4a-b2cb-d5d127fa23f5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<SQL>\n",
      "SELECT *\n",
      "FROM (SELECT \"title\", \"start_year\", \"runtime\", \"rating\", \"nvotes\"\n",
      "FROM \"movies\"\n",
      "WHERE (\"start_year\" > 2000.0 AND \"nvotes\" > 500.0)\n",
      "LIMIT 10) \"q01\"\n",
      "ORDER BY \"rating\" DESC\n"
     ]
    }
   ],
   "source": [
    "top_rated_movies %>% \n",
    "    show_query()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d1ba267-8128-42c9-94ea-2f8b8434f4b5",
   "metadata": {},
   "source": [
    "We can always call the `collect` function to collect the data from the database immediately. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06ef29d9-4b4f-47cd-852c-bf3c4175dc7c",
   "metadata": {},
   "source": [
    "**Example**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "15a8013f-ab17-44f1-830a-6984538908e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 10 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>title</th><th scope=col>start_year</th><th scope=col>runtime</th><th scope=col>rating</th><th scope=col>nvotes</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>The Lord of the Rings: The Fellowship of the Ring</td><td>2001</td><td>178</td><td>8.8</td><td>1537080</td></tr>\n",
       "\t<tr><td>Frida                                            </td><td>2002</td><td>123</td><td>7.4</td><td>  75612</td></tr>\n",
       "\t<tr><td>Corpse Bride                                     </td><td>2005</td><td> 77</td><td>7.3</td><td> 226501</td></tr>\n",
       "\t<tr><td>The Other Side of the Wind                       </td><td>2018</td><td>122</td><td>6.9</td><td>   4904</td></tr>\n",
       "\t<tr><td>The Dancer Upstairs                              </td><td>2002</td><td>132</td><td>6.9</td><td>   6117</td></tr>\n",
       "\t<tr><td>From Hell                                        </td><td>2001</td><td>122</td><td>6.8</td><td> 140669</td></tr>\n",
       "\t<tr><td>The Shipping News                                </td><td>2001</td><td>111</td><td>6.7</td><td>  31012</td></tr>\n",
       "\t<tr><td>Star Wars: Episode II - Attack of the Clones     </td><td>2002</td><td>142</td><td>6.6</td><td> 584616</td></tr>\n",
       "\t<tr><td><span style=white-space:pre-wrap>Kate &amp; Leopold                                   </span></td><td>2001</td><td>118</td><td>6.4</td><td><span style=white-space:pre-wrap>  74982</span></td></tr>\n",
       "\t<tr><td>Men in Black II                                  </td><td>2002</td><td> 88</td><td>6.2</td><td> 320765</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 10 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " title & start\\_year & runtime & rating & nvotes\\\\\n",
       " <chr> & <int> & <int> & <dbl> & <int>\\\\\n",
       "\\hline\n",
       "\t The Lord of the Rings: The Fellowship of the Ring & 2001 & 178 & 8.8 & 1537080\\\\\n",
       "\t Frida                                             & 2002 & 123 & 7.4 &   75612\\\\\n",
       "\t Corpse Bride                                      & 2005 &  77 & 7.3 &  226501\\\\\n",
       "\t The Other Side of the Wind                        & 2018 & 122 & 6.9 &    4904\\\\\n",
       "\t The Dancer Upstairs                               & 2002 & 132 & 6.9 &    6117\\\\\n",
       "\t From Hell                                         & 2001 & 122 & 6.8 &  140669\\\\\n",
       "\t The Shipping News                                 & 2001 & 111 & 6.7 &   31012\\\\\n",
       "\t Star Wars: Episode II - Attack of the Clones      & 2002 & 142 & 6.6 &  584616\\\\\n",
       "\t Kate \\& Leopold                                    & 2001 & 118 & 6.4 &   74982\\\\\n",
       "\t Men in Black II                                   & 2002 &  88 & 6.2 &  320765\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 10 × 5\n",
       "\n",
       "| title &lt;chr&gt; | start_year &lt;int&gt; | runtime &lt;int&gt; | rating &lt;dbl&gt; | nvotes &lt;int&gt; |\n",
       "|---|---|---|---|---|\n",
       "| The Lord of the Rings: The Fellowship of the Ring | 2001 | 178 | 8.8 | 1537080 |\n",
       "| Frida                                             | 2002 | 123 | 7.4 |   75612 |\n",
       "| Corpse Bride                                      | 2005 |  77 | 7.3 |  226501 |\n",
       "| The Other Side of the Wind                        | 2018 | 122 | 6.9 |    4904 |\n",
       "| The Dancer Upstairs                               | 2002 | 132 | 6.9 |    6117 |\n",
       "| From Hell                                         | 2001 | 122 | 6.8 |  140669 |\n",
       "| The Shipping News                                 | 2001 | 111 | 6.7 |   31012 |\n",
       "| Star Wars: Episode II - Attack of the Clones      | 2002 | 142 | 6.6 |  584616 |\n",
       "| Kate &amp; Leopold                                    | 2001 | 118 | 6.4 |   74982 |\n",
       "| Men in Black II                                   | 2002 |  88 | 6.2 |  320765 |\n",
       "\n"
      ],
      "text/plain": [
       "   title                                             start_year runtime rating\n",
       "1  The Lord of the Rings: The Fellowship of the Ring 2001       178     8.8   \n",
       "2  Frida                                             2002       123     7.4   \n",
       "3  Corpse Bride                                      2005        77     7.3   \n",
       "4  The Other Side of the Wind                        2018       122     6.9   \n",
       "5  The Dancer Upstairs                               2002       132     6.9   \n",
       "6  From Hell                                         2001       122     6.8   \n",
       "7  The Shipping News                                 2001       111     6.7   \n",
       "8  Star Wars: Episode II - Attack of the Clones      2002       142     6.6   \n",
       "9  Kate & Leopold                                    2001       118     6.4   \n",
       "10 Men in Black II                                   2002        88     6.2   \n",
       "   nvotes \n",
       "1  1537080\n",
       "2    75612\n",
       "3   226501\n",
       "4     4904\n",
       "5     6117\n",
       "6   140669\n",
       "7    31012\n",
       "8   584616\n",
       "9    74982\n",
       "10  320765"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top_rated_movies %>% \n",
    "    collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "661ca1ee-b876-415f-bb34-f6330a6ba981",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[90m# Source:   table<principals> [?? x 3]\u001b[39m\n",
       "\u001b[90m# Database: postgres [postgres@localhost:5432/imdb]\u001b[39m\n",
       "   movie_id ordering  name_id\n",
       "      \u001b[3m\u001b[90m<int>\u001b[39m\u001b[23m    \u001b[3m\u001b[90m<int>\u001b[39m\u001b[23m    \u001b[3m\u001b[90m<int>\u001b[39m\u001b[23m\n",
       "\u001b[90m 1\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m\u001b[4m5\u001b[24m423        1 20\u001b[4m0\u001b[24m\u001b[4m0\u001b[24m\u001b[4m0\u001b[24m212\n",
       "\u001b[90m 2\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m\u001b[4m5\u001b[24m423        2 20\u001b[4m4\u001b[24m\u001b[4m1\u001b[24m\u001b[4m3\u001b[24m168\n",
       "\u001b[90m 3\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m\u001b[4m5\u001b[24m423        3 20\u001b[4m0\u001b[24m\u001b[4m0\u001b[24m\u001b[4m0\u001b[24m630\n",
       "\u001b[90m 4\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m\u001b[4m5\u001b[24m423        4 20\u001b[4m0\u001b[24m\u001b[4m0\u001b[24m\u001b[4m5\u001b[24m227\n",
       "\u001b[90m 5\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m\u001b[4m5\u001b[24m423        5 20\u001b[4m0\u001b[24m\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m506\n",
       "\u001b[90m 6\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m\u001b[4m5\u001b[24m423        6 20\u001b[4m7\u001b[24m\u001b[4m3\u001b[24m\u001b[4m7\u001b[24m216\n",
       "\u001b[90m 7\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m\u001b[4m5\u001b[24m423        7 20\u001b[4m4\u001b[24m\u001b[4m6\u001b[24m\u001b[4m5\u001b[24m298\n",
       "\u001b[90m 8\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m\u001b[4m5\u001b[24m423        8 20\u001b[4m4\u001b[24m\u001b[4m4\u001b[24m\u001b[4m8\u001b[24m843\n",
       "\u001b[90m 9\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m\u001b[4m5\u001b[24m423        9 20\u001b[4m2\u001b[24m\u001b[4m3\u001b[24m\u001b[4m8\u001b[24m698\n",
       "\u001b[90m10\u001b[39m 10\u001b[4m0\u001b[24m\u001b[4m3\u001b[24m\u001b[4m5\u001b[24m423       10 20\u001b[4m1\u001b[24m\u001b[4m0\u001b[24m\u001b[4m7\u001b[24m463\n",
       "\u001b[90m# ... with more rows\u001b[39m"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "(principals <- tbl(connection, 'principals'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95c5b623-9896-46c0-9f60-0eceb59ddbb3",
   "metadata": {},
   "source": [
    "**Exercise 2.1.2**\n",
    "\n",
    "What are the median running times and the average ratings of movies in each genre in `movie_genres` table? Check the SQL code generated by `dbplyr`, and collect the data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "576f92bd-d188-48c0-9f68-db7f5551a80d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code goes here\n",
    "# genres <- ...(..., 'movie_genres')\n",
    "# ...\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b39047c-3796-4bc9-9cf4-2a6e0e354eca",
   "metadata": {},
   "source": [
    "### 2.1.1 Writing your own SQL query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1cc7e98-c402-4560-b444-0670c31395d3",
   "metadata": {},
   "source": [
    "If you need to write your own queries, you can use the `DBI::dbGetQuery` function, which returns a `data.frame`. \n",
    "\n",
    "**Example**\n",
    "\n",
    "Retrieve the movies with the word `science` in the title. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "9609d14d-b10d-4d2f-9636-9c9b89621d1e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A data.frame: 4 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>title</th><th scope=col>start_year</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>My Science Project                     </td><td>1985</td></tr>\n",
       "\t<tr><td>Mystery Science Theater 3000: The Movie</td><td>1996</td></tr>\n",
       "\t<tr><td>Bill Nye: Science Guy                  </td><td>2017</td></tr>\n",
       "\t<tr><td>The Science of Sleep                   </td><td>2006</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 4 × 2\n",
       "\\begin{tabular}{ll}\n",
       " title & start\\_year\\\\\n",
       " <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t My Science Project                      & 1985\\\\\n",
       "\t Mystery Science Theater 3000: The Movie & 1996\\\\\n",
       "\t Bill Nye: Science Guy                   & 2017\\\\\n",
       "\t The Science of Sleep                    & 2006\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 4 × 2\n",
       "\n",
       "| title &lt;chr&gt; | start_year &lt;int&gt; |\n",
       "|---|---|\n",
       "| My Science Project                      | 1985 |\n",
       "| Mystery Science Theater 3000: The Movie | 1996 |\n",
       "| Bill Nye: Science Guy                   | 2017 |\n",
       "| The Science of Sleep                    | 2006 |\n",
       "\n"
      ],
      "text/plain": [
       "  title                                   start_year\n",
       "1 My Science Project                      1985      \n",
       "2 Mystery Science Theater 3000: The Movie 1996      \n",
       "3 Bill Nye: Science Guy                   2017      \n",
       "4 The Science of Sleep                    2006      "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "my_query <- \"\n",
    "SELECT title, start_year \n",
    "  FROM movies\n",
    "  WHERE title ILIKE '% science %';\n",
    "\"\n",
    "dbGetQuery(connection, my_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cf20599-a55f-4d67-8036-5d17d9f596cb",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 3. Cleaning Data\n",
    "##### Suggested reading [Tidy Data by Hadley Wickham](https://www.jstatsoft.org/article/view/v059i10/)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c90e1d3-3f6e-4c7e-8457-cf4916fe0cfd",
   "metadata": {},
   "source": [
    "The task of cleaning and preparing data for analysis is somewhat generic and may involve many steps, such as:\n",
    "- outlier detection;\n",
    "- fixing typos;\n",
    "- date parsing;\n",
    "- imputing missing data;\n",
    "- properly structuring the data for analysis;\n",
    "\n",
    "Here we will focus on the last point. \n",
    "\n",
    "\n",
    "In this workshop, we looked at very organized data sets stored in our databases. What are the characteristics of the tables we used? \n",
    "- Each column corresponds to one variable; \n",
    "- Each row corresponds to one unit. \n",
    "- Each table corresponds to one entity;\n",
    "  - For example, in the `imdb` database, we have a table for movies (an entity), another table for people, another one for genres, etc...\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1320d27-a8c8-4f81-8f9a-ddff9b735f65",
   "metadata": {},
   "source": [
    "### 3.1 Activity\n",
    "\n",
    "Let's take a look at the Gapminder dataset with information about countries. Do you see any problem with this dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "4a8b6586-c2a3-4f95-9baa-bce91c4521d2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 426 × 14</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>country</th><th scope=col>variable</th><th scope=col>1952</th><th scope=col>1957</th><th scope=col>1962</th><th scope=col>1967</th><th scope=col>1972</th><th scope=col>1977</th><th scope=col>1982</th><th scope=col>1987</th><th scope=col>1992</th><th scope=col>1997</th><th scope=col>2002</th><th scope=col>2007</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Afghanistan/Asia  </td><td>pop      </td><td>8.425333e+06</td><td>9.240934e+06</td><td>1.026708e+07</td><td>1.153797e+07</td><td>1.307946e+07</td><td>1.488037e+07</td><td>1.288182e+07</td><td>1.386796e+07</td><td>1.631792e+07</td><td>2.222742e+07</td><td>2.526841e+07</td><td>3.188992e+07</td></tr>\n",
       "\t<tr><td>Afghanistan/Asia  </td><td>gdpPercap</td><td>7.794453e+02</td><td>8.208530e+02</td><td>8.531007e+02</td><td>8.361971e+02</td><td>7.399811e+02</td><td>7.861134e+02</td><td>9.780114e+02</td><td>8.523959e+02</td><td>6.493414e+02</td><td>6.353414e+02</td><td>7.267341e+02</td><td>9.745803e+02</td></tr>\n",
       "\t<tr><td>Afghanistan/Asia  </td><td>lifeExp  </td><td>2.880100e+01</td><td>3.033200e+01</td><td>3.199700e+01</td><td>3.402000e+01</td><td>3.608800e+01</td><td>3.843800e+01</td><td>3.985400e+01</td><td>4.082200e+01</td><td>4.167400e+01</td><td>4.176300e+01</td><td>4.212900e+01</td><td>4.382800e+01</td></tr>\n",
       "\t<tr><td>Albania/Europe    </td><td>pop      </td><td>1.282697e+06</td><td>1.476505e+06</td><td>1.728137e+06</td><td>1.984060e+06</td><td>2.263554e+06</td><td>2.509048e+06</td><td>2.780097e+06</td><td>3.075321e+06</td><td>3.326498e+06</td><td>3.428038e+06</td><td>3.508512e+06</td><td>3.600523e+06</td></tr>\n",
       "\t<tr><td>Albania/Europe    </td><td>gdpPercap</td><td>1.601056e+03</td><td>1.942284e+03</td><td>2.312889e+03</td><td>2.760197e+03</td><td>3.313422e+03</td><td>3.533004e+03</td><td>3.630881e+03</td><td>3.738933e+03</td><td>2.497438e+03</td><td>3.193055e+03</td><td>4.604212e+03</td><td>5.937030e+03</td></tr>\n",
       "\t<tr><td>Albania/Europe    </td><td>lifeExp  </td><td>5.523000e+01</td><td>5.928000e+01</td><td>6.482000e+01</td><td>6.622000e+01</td><td>6.769000e+01</td><td>6.893000e+01</td><td>7.042000e+01</td><td>7.200000e+01</td><td>7.158100e+01</td><td>7.295000e+01</td><td>7.565100e+01</td><td>7.642300e+01</td></tr>\n",
       "\t<tr><td>Algeria/Africa    </td><td>pop      </td><td>9.279525e+06</td><td>1.027086e+07</td><td>1.100095e+07</td><td>1.276050e+07</td><td>1.476079e+07</td><td>1.715280e+07</td><td>2.003375e+07</td><td>2.325496e+07</td><td>2.629837e+07</td><td>2.907202e+07</td><td>3.128714e+07</td><td>3.333322e+07</td></tr>\n",
       "\t<tr><td>Algeria/Africa    </td><td>gdpPercap</td><td>2.449008e+03</td><td>3.013976e+03</td><td>2.550817e+03</td><td>3.246992e+03</td><td>4.182664e+03</td><td>4.910417e+03</td><td>5.745160e+03</td><td>5.681359e+03</td><td>5.023217e+03</td><td>4.797295e+03</td><td>5.288040e+03</td><td>6.223367e+03</td></tr>\n",
       "\t<tr><td>Algeria/Africa    </td><td>lifeExp  </td><td>4.307700e+01</td><td>4.568500e+01</td><td>4.830300e+01</td><td>5.140700e+01</td><td>5.451800e+01</td><td>5.801400e+01</td><td>6.136800e+01</td><td>6.579900e+01</td><td>6.774400e+01</td><td>6.915200e+01</td><td>7.099400e+01</td><td>7.230100e+01</td></tr>\n",
       "\t<tr><td>Angola/Africa     </td><td>pop      </td><td>4.232095e+06</td><td>4.561361e+06</td><td>4.826015e+06</td><td>5.247469e+06</td><td>5.894858e+06</td><td>6.162675e+06</td><td>7.016384e+06</td><td>7.874230e+06</td><td>8.735988e+06</td><td>9.875024e+06</td><td>1.086611e+07</td><td>1.242048e+07</td></tr>\n",
       "\t<tr><td>Angola/Africa     </td><td>gdpPercap</td><td>3.520610e+03</td><td>3.827940e+03</td><td>4.269277e+03</td><td>5.522776e+03</td><td>5.473288e+03</td><td>3.008647e+03</td><td>2.756954e+03</td><td>2.430208e+03</td><td>2.627846e+03</td><td>2.277141e+03</td><td>2.773287e+03</td><td>4.797231e+03</td></tr>\n",
       "\t<tr><td>Angola/Africa     </td><td>lifeExp  </td><td>3.001500e+01</td><td>3.199900e+01</td><td>3.400000e+01</td><td>3.598500e+01</td><td>3.792800e+01</td><td>3.948300e+01</td><td>3.994200e+01</td><td>3.990600e+01</td><td>4.064700e+01</td><td>4.096300e+01</td><td>4.100300e+01</td><td>4.273100e+01</td></tr>\n",
       "\t<tr><td>Argentina/Americas</td><td>pop      </td><td>1.787696e+07</td><td>1.961054e+07</td><td>2.128378e+07</td><td>2.293423e+07</td><td>2.477980e+07</td><td>2.698383e+07</td><td>2.934137e+07</td><td>3.162092e+07</td><td>3.395895e+07</td><td>3.620346e+07</td><td>3.833112e+07</td><td>4.030193e+07</td></tr>\n",
       "\t<tr><td>Argentina/Americas</td><td>gdpPercap</td><td>5.911315e+03</td><td>6.856856e+03</td><td>7.133166e+03</td><td>8.052953e+03</td><td>9.443039e+03</td><td>1.007903e+04</td><td>8.997897e+03</td><td>9.139671e+03</td><td>9.308419e+03</td><td>1.096728e+04</td><td>8.797641e+03</td><td>1.277938e+04</td></tr>\n",
       "\t<tr><td>Argentina/Americas</td><td>lifeExp  </td><td>6.248500e+01</td><td>6.439900e+01</td><td>6.514200e+01</td><td>6.563400e+01</td><td>6.706500e+01</td><td>6.848100e+01</td><td>6.994200e+01</td><td>7.077400e+01</td><td>7.186800e+01</td><td>7.327500e+01</td><td>7.434000e+01</td><td>7.532000e+01</td></tr>\n",
       "\t<tr><td>Australia/Oceania </td><td>pop      </td><td>8.691212e+06</td><td>9.712569e+06</td><td>1.079497e+07</td><td>1.187226e+07</td><td>1.317700e+07</td><td>1.407410e+07</td><td>1.518420e+07</td><td>1.625725e+07</td><td>1.748198e+07</td><td>1.856524e+07</td><td>1.954679e+07</td><td>2.043418e+07</td></tr>\n",
       "\t<tr><td>Australia/Oceania </td><td>gdpPercap</td><td>1.003960e+04</td><td>1.094965e+04</td><td>1.221723e+04</td><td>1.452612e+04</td><td>1.678863e+04</td><td>1.833420e+04</td><td>1.947701e+04</td><td>2.188889e+04</td><td>2.342477e+04</td><td>2.699794e+04</td><td>3.068775e+04</td><td>3.443537e+04</td></tr>\n",
       "\t<tr><td>Australia/Oceania </td><td>lifeExp  </td><td>6.912000e+01</td><td>7.033000e+01</td><td>7.093000e+01</td><td>7.110000e+01</td><td>7.193000e+01</td><td>7.349000e+01</td><td>7.474000e+01</td><td>7.632000e+01</td><td>7.756000e+01</td><td>7.883000e+01</td><td>8.037000e+01</td><td>8.123500e+01</td></tr>\n",
       "\t<tr><td>Austria/Europe    </td><td>pop      </td><td>6.927772e+06</td><td>6.965860e+06</td><td>7.129864e+06</td><td>7.376998e+06</td><td>7.544201e+06</td><td>7.568430e+06</td><td>7.574613e+06</td><td>7.578903e+06</td><td>7.914969e+06</td><td>8.069876e+06</td><td>8.148312e+06</td><td>8.199783e+06</td></tr>\n",
       "\t<tr><td>Austria/Europe    </td><td>gdpPercap</td><td>6.137076e+03</td><td>8.842598e+03</td><td>1.075072e+04</td><td>1.283460e+04</td><td>1.666163e+04</td><td>1.974942e+04</td><td>2.159708e+04</td><td>2.368783e+04</td><td>2.704202e+04</td><td>2.909592e+04</td><td>3.241761e+04</td><td>3.612649e+04</td></tr>\n",
       "\t<tr><td>Austria/Europe    </td><td>lifeExp  </td><td>6.680000e+01</td><td>6.748000e+01</td><td>6.954000e+01</td><td>7.014000e+01</td><td>7.063000e+01</td><td>7.217000e+01</td><td>7.318000e+01</td><td>7.494000e+01</td><td>7.604000e+01</td><td>7.751000e+01</td><td>7.898000e+01</td><td>7.982900e+01</td></tr>\n",
       "\t<tr><td>Bahrain/Asia      </td><td>pop      </td><td>1.204470e+05</td><td>1.386550e+05</td><td>1.718630e+05</td><td>2.021820e+05</td><td>2.308000e+05</td><td>2.974100e+05</td><td>3.779670e+05</td><td>4.546120e+05</td><td>5.294910e+05</td><td>5.985610e+05</td><td>6.563970e+05</td><td>7.085730e+05</td></tr>\n",
       "\t<tr><td>Bahrain/Asia      </td><td>gdpPercap</td><td>9.867085e+03</td><td>1.163580e+04</td><td>1.275328e+04</td><td>1.480467e+04</td><td>1.826866e+04</td><td>1.934010e+04</td><td>1.921115e+04</td><td>1.852402e+04</td><td>1.903558e+04</td><td>2.029202e+04</td><td>2.340356e+04</td><td>2.979605e+04</td></tr>\n",
       "\t<tr><td>Bahrain/Asia      </td><td>lifeExp  </td><td>5.093900e+01</td><td>5.383200e+01</td><td>5.692300e+01</td><td>5.992300e+01</td><td>6.330000e+01</td><td>6.559300e+01</td><td>6.905200e+01</td><td>7.075000e+01</td><td>7.260100e+01</td><td>7.392500e+01</td><td>7.479500e+01</td><td>7.563500e+01</td></tr>\n",
       "\t<tr><td>Bangladesh/Asia   </td><td>pop      </td><td>4.688686e+07</td><td>5.136547e+07</td><td>5.683929e+07</td><td>6.282188e+07</td><td>7.075930e+07</td><td>8.042831e+07</td><td>9.307441e+07</td><td>1.037642e+08</td><td>1.137046e+08</td><td>1.233153e+08</td><td>1.356568e+08</td><td>1.504483e+08</td></tr>\n",
       "\t<tr><td>Bangladesh/Asia   </td><td>gdpPercap</td><td>6.842442e+02</td><td>6.616375e+02</td><td>6.863416e+02</td><td>7.211861e+02</td><td>6.302336e+02</td><td>6.598772e+02</td><td>6.769819e+02</td><td>7.519794e+02</td><td>8.378102e+02</td><td>9.727700e+02</td><td>1.136390e+03</td><td>1.391254e+03</td></tr>\n",
       "\t<tr><td>Bangladesh/Asia   </td><td>lifeExp  </td><td>3.748400e+01</td><td>3.934800e+01</td><td>4.121600e+01</td><td>4.345300e+01</td><td>4.525200e+01</td><td>4.692300e+01</td><td>5.000900e+01</td><td>5.281900e+01</td><td>5.601800e+01</td><td>5.941200e+01</td><td>6.201300e+01</td><td>6.406200e+01</td></tr>\n",
       "\t<tr><td>Belgium/Europe    </td><td>pop      </td><td>8.730405e+06</td><td>8.989111e+06</td><td>9.218400e+06</td><td>9.556500e+06</td><td>9.709100e+06</td><td>9.821800e+06</td><td>9.856303e+06</td><td>9.870200e+06</td><td>1.004562e+07</td><td>1.019979e+07</td><td>1.031197e+07</td><td>1.039223e+07</td></tr>\n",
       "\t<tr><td>Belgium/Europe    </td><td>gdpPercap</td><td>8.343105e+03</td><td>9.714961e+03</td><td>1.099121e+04</td><td>1.314904e+04</td><td>1.667214e+04</td><td>1.911797e+04</td><td>2.097985e+04</td><td>2.252556e+04</td><td>2.557557e+04</td><td>2.756120e+04</td><td>3.048588e+04</td><td>3.369261e+04</td></tr>\n",
       "\t<tr><td>Belgium/Europe    </td><td>lifeExp  </td><td>6.800000e+01</td><td>6.924000e+01</td><td>7.025000e+01</td><td>7.094000e+01</td><td>7.144000e+01</td><td>7.280000e+01</td><td>7.393000e+01</td><td>7.535000e+01</td><td>7.646000e+01</td><td>7.753000e+01</td><td>7.832000e+01</td><td>7.944100e+01</td></tr>\n",
       "\t<tr><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>Uganda/Africa          </td><td>pop      </td><td>5.824797e+06</td><td>6.675501e+06</td><td>7.688797e+06</td><td>8.900294e+06</td><td>1.019029e+07</td><td>1.145776e+07</td><td>1.293940e+07</td><td>1.528305e+07</td><td>1.825219e+07</td><td>2.121025e+07</td><td>2.473987e+07</td><td>2.917040e+07</td></tr>\n",
       "\t<tr><td>Uganda/Africa          </td><td>gdpPercap</td><td>7.347535e+02</td><td>7.743711e+02</td><td>7.672717e+02</td><td>9.089185e+02</td><td>9.507359e+02</td><td>8.437331e+02</td><td>6.822662e+02</td><td>6.177244e+02</td><td>6.441708e+02</td><td>8.165591e+02</td><td>9.277210e+02</td><td>1.056380e+03</td></tr>\n",
       "\t<tr><td>Uganda/Africa          </td><td>lifeExp  </td><td>3.997800e+01</td><td>4.257100e+01</td><td>4.534400e+01</td><td>4.805100e+01</td><td>5.101600e+01</td><td>5.035000e+01</td><td>4.984900e+01</td><td>5.150900e+01</td><td>4.882500e+01</td><td>4.457800e+01</td><td>4.781300e+01</td><td>5.154200e+01</td></tr>\n",
       "\t<tr><td>United Kingdom/Europe  </td><td>pop      </td><td>5.043000e+07</td><td>5.143000e+07</td><td>5.329200e+07</td><td>5.495900e+07</td><td>5.607900e+07</td><td>5.617900e+07</td><td>5.633970e+07</td><td>5.698162e+07</td><td>5.786635e+07</td><td>5.880827e+07</td><td>5.991243e+07</td><td>6.077624e+07</td></tr>\n",
       "\t<tr><td>United Kingdom/Europe  </td><td>gdpPercap</td><td>9.979508e+03</td><td>1.128318e+04</td><td>1.247718e+04</td><td>1.414285e+04</td><td>1.589512e+04</td><td>1.742875e+04</td><td>1.823242e+04</td><td>2.166479e+04</td><td>2.270509e+04</td><td>2.607453e+04</td><td>2.947900e+04</td><td>3.320326e+04</td></tr>\n",
       "\t<tr><td>United Kingdom/Europe  </td><td>lifeExp  </td><td>6.918000e+01</td><td>7.042000e+01</td><td>7.076000e+01</td><td>7.136000e+01</td><td>7.201000e+01</td><td>7.276000e+01</td><td>7.404000e+01</td><td>7.500700e+01</td><td>7.642000e+01</td><td>7.721800e+01</td><td>7.847100e+01</td><td>7.942500e+01</td></tr>\n",
       "\t<tr><td>United States/Americas </td><td>pop      </td><td>1.575530e+08</td><td>1.719840e+08</td><td>1.865380e+08</td><td>1.987120e+08</td><td>2.098960e+08</td><td>2.202390e+08</td><td>2.321878e+08</td><td>2.428035e+08</td><td>2.568942e+08</td><td>2.729118e+08</td><td>2.876755e+08</td><td>3.011399e+08</td></tr>\n",
       "\t<tr><td>United States/Americas </td><td>gdpPercap</td><td>1.399048e+04</td><td>1.484713e+04</td><td>1.617315e+04</td><td>1.953037e+04</td><td>2.180604e+04</td><td>2.407263e+04</td><td>2.500956e+04</td><td>2.988435e+04</td><td>3.200393e+04</td><td>3.576743e+04</td><td>3.909710e+04</td><td>4.295165e+04</td></tr>\n",
       "\t<tr><td>United States/Americas </td><td>lifeExp  </td><td>6.844000e+01</td><td>6.949000e+01</td><td>7.021000e+01</td><td>7.076000e+01</td><td>7.134000e+01</td><td>7.338000e+01</td><td>7.465000e+01</td><td>7.502000e+01</td><td>7.609000e+01</td><td>7.681000e+01</td><td>7.731000e+01</td><td>7.824200e+01</td></tr>\n",
       "\t<tr><td>Uruguay/Americas       </td><td>pop      </td><td>2.252965e+06</td><td>2.424959e+06</td><td>2.598466e+06</td><td>2.748579e+06</td><td>2.829526e+06</td><td>2.873520e+06</td><td>2.953997e+06</td><td>3.045153e+06</td><td>3.149262e+06</td><td>3.262838e+06</td><td>3.363085e+06</td><td>3.447496e+06</td></tr>\n",
       "\t<tr><td>Uruguay/Americas       </td><td>gdpPercap</td><td>5.716767e+03</td><td>6.150773e+03</td><td>5.603358e+03</td><td>5.444620e+03</td><td>5.703409e+03</td><td>6.504340e+03</td><td>6.920223e+03</td><td>7.452399e+03</td><td>8.137005e+03</td><td>9.230241e+03</td><td>7.727002e+03</td><td>1.061146e+04</td></tr>\n",
       "\t<tr><td>Uruguay/Americas       </td><td>lifeExp  </td><td>6.607100e+01</td><td>6.704400e+01</td><td>6.825300e+01</td><td>6.846800e+01</td><td>6.867300e+01</td><td>6.948100e+01</td><td>7.080500e+01</td><td>7.191800e+01</td><td>7.275200e+01</td><td>7.422300e+01</td><td>7.530700e+01</td><td>7.638400e+01</td></tr>\n",
       "\t<tr><td>Venezuela/Americas     </td><td>pop      </td><td>5.439568e+06</td><td>6.702668e+06</td><td>8.143375e+06</td><td>9.709552e+06</td><td>1.151565e+07</td><td>1.350356e+07</td><td>1.562077e+07</td><td>1.791018e+07</td><td>2.026556e+07</td><td>2.237440e+07</td><td>2.428767e+07</td><td>2.608466e+07</td></tr>\n",
       "\t<tr><td>Venezuela/Americas     </td><td>gdpPercap</td><td>7.689800e+03</td><td>9.802467e+03</td><td>8.422974e+03</td><td>9.541474e+03</td><td>1.050526e+04</td><td>1.314395e+04</td><td>1.115241e+04</td><td>9.883585e+03</td><td>1.073393e+04</td><td>1.016550e+04</td><td>8.605048e+03</td><td>1.141581e+04</td></tr>\n",
       "\t<tr><td>Venezuela/Americas     </td><td>lifeExp  </td><td>5.508800e+01</td><td>5.790700e+01</td><td>6.077000e+01</td><td>6.347900e+01</td><td>6.571200e+01</td><td>6.745600e+01</td><td>6.855700e+01</td><td>7.019000e+01</td><td>7.115000e+01</td><td>7.214600e+01</td><td>7.276600e+01</td><td>7.374700e+01</td></tr>\n",
       "\t<tr><td>Vietnam/Asia           </td><td>pop      </td><td>2.624684e+07</td><td>2.899854e+07</td><td>3.379614e+07</td><td>3.946391e+07</td><td>4.465501e+07</td><td>5.053351e+07</td><td>5.614218e+07</td><td>6.282649e+07</td><td>6.994073e+07</td><td>7.604900e+07</td><td>8.090815e+07</td><td>8.526236e+07</td></tr>\n",
       "\t<tr><td>Vietnam/Asia           </td><td>gdpPercap</td><td>6.050665e+02</td><td>6.762854e+02</td><td>7.720492e+02</td><td>6.371233e+02</td><td>6.995016e+02</td><td>7.135371e+02</td><td>7.072358e+02</td><td>8.207994e+02</td><td>9.890231e+02</td><td>1.385897e+03</td><td>1.764457e+03</td><td>2.441576e+03</td></tr>\n",
       "\t<tr><td>Vietnam/Asia           </td><td>lifeExp  </td><td>4.041200e+01</td><td>4.288700e+01</td><td>4.536300e+01</td><td>4.783800e+01</td><td>5.025400e+01</td><td>5.576400e+01</td><td>5.881600e+01</td><td>6.282000e+01</td><td>6.766200e+01</td><td>7.067200e+01</td><td>7.301700e+01</td><td>7.424900e+01</td></tr>\n",
       "\t<tr><td>West Bank and Gaza/Asia</td><td>pop      </td><td>1.030585e+06</td><td>1.070439e+06</td><td>1.133134e+06</td><td>1.142636e+06</td><td>1.089572e+06</td><td>1.261091e+06</td><td>1.425876e+06</td><td>1.691210e+06</td><td>2.104779e+06</td><td>2.826046e+06</td><td>3.389578e+06</td><td>4.018332e+06</td></tr>\n",
       "\t<tr><td>West Bank and Gaza/Asia</td><td>gdpPercap</td><td>1.515592e+03</td><td>1.827068e+03</td><td>2.198956e+03</td><td>2.649715e+03</td><td>3.133409e+03</td><td>3.682831e+03</td><td>4.336032e+03</td><td>5.107197e+03</td><td>6.017655e+03</td><td>7.110668e+03</td><td>4.515488e+03</td><td>3.025350e+03</td></tr>\n",
       "\t<tr><td>West Bank and Gaza/Asia</td><td>lifeExp  </td><td>4.316000e+01</td><td>4.567100e+01</td><td>4.812700e+01</td><td>5.163100e+01</td><td>5.653200e+01</td><td>6.076500e+01</td><td>6.440600e+01</td><td>6.704600e+01</td><td>6.971800e+01</td><td>7.109600e+01</td><td>7.237000e+01</td><td>7.342200e+01</td></tr>\n",
       "\t<tr><td>Yemen, Rep./Asia       </td><td>pop      </td><td>4.963829e+06</td><td>5.498090e+06</td><td>6.120081e+06</td><td>6.740785e+06</td><td>7.407075e+06</td><td>8.403990e+06</td><td>9.657618e+06</td><td>1.121934e+07</td><td>1.336800e+07</td><td>1.582650e+07</td><td>1.870126e+07</td><td>2.221174e+07</td></tr>\n",
       "\t<tr><td>Yemen, Rep./Asia       </td><td>gdpPercap</td><td>7.817176e+02</td><td>8.048305e+02</td><td>8.256232e+02</td><td>8.624421e+02</td><td>1.265047e+03</td><td>1.829765e+03</td><td>1.977557e+03</td><td>1.971742e+03</td><td>1.879497e+03</td><td>2.117485e+03</td><td>2.234821e+03</td><td>2.280770e+03</td></tr>\n",
       "\t<tr><td>Yemen, Rep./Asia       </td><td>lifeExp  </td><td>3.254800e+01</td><td>3.397000e+01</td><td>3.518000e+01</td><td>3.698400e+01</td><td>3.984800e+01</td><td>4.417500e+01</td><td>4.911300e+01</td><td>5.292200e+01</td><td>5.559900e+01</td><td>5.802000e+01</td><td>6.030800e+01</td><td>6.269800e+01</td></tr>\n",
       "\t<tr><td>Zambia/Africa          </td><td>pop      </td><td>2.672000e+06</td><td>3.016000e+06</td><td>3.421000e+06</td><td>3.900000e+06</td><td>4.506497e+06</td><td>5.216550e+06</td><td>6.100407e+06</td><td>7.272406e+06</td><td>8.381163e+06</td><td>9.417789e+06</td><td>1.059581e+07</td><td>1.174604e+07</td></tr>\n",
       "\t<tr><td>Zambia/Africa          </td><td>gdpPercap</td><td>1.147389e+03</td><td>1.311957e+03</td><td>1.452726e+03</td><td>1.777077e+03</td><td>1.773498e+03</td><td>1.588688e+03</td><td>1.408679e+03</td><td>1.213315e+03</td><td>1.210885e+03</td><td>1.071354e+03</td><td>1.071614e+03</td><td>1.271212e+03</td></tr>\n",
       "\t<tr><td>Zambia/Africa          </td><td>lifeExp  </td><td>4.203800e+01</td><td>4.407700e+01</td><td>4.602300e+01</td><td>4.776800e+01</td><td>5.010700e+01</td><td>5.138600e+01</td><td>5.182100e+01</td><td>5.082100e+01</td><td>4.610000e+01</td><td>4.023800e+01</td><td>3.919300e+01</td><td>4.238400e+01</td></tr>\n",
       "\t<tr><td>Zimbabwe/Africa        </td><td>pop      </td><td>3.080907e+06</td><td>3.646340e+06</td><td>4.277736e+06</td><td>4.995432e+06</td><td>5.861135e+06</td><td>6.642107e+06</td><td>7.636524e+06</td><td>9.216418e+06</td><td>1.070434e+07</td><td>1.140495e+07</td><td>1.192656e+07</td><td>1.231114e+07</td></tr>\n",
       "\t<tr><td>Zimbabwe/Africa        </td><td>gdpPercap</td><td>4.068841e+02</td><td>5.187643e+02</td><td>5.272722e+02</td><td>5.697951e+02</td><td>7.993622e+02</td><td>6.855877e+02</td><td>7.888550e+02</td><td>7.061573e+02</td><td>6.934208e+02</td><td>7.924500e+02</td><td>6.720386e+02</td><td>4.697093e+02</td></tr>\n",
       "\t<tr><td>Zimbabwe/Africa        </td><td>lifeExp  </td><td>4.845100e+01</td><td>5.046900e+01</td><td>5.235800e+01</td><td>5.399500e+01</td><td>5.563500e+01</td><td>5.767400e+01</td><td>6.036300e+01</td><td>6.235100e+01</td><td>6.037700e+01</td><td>4.680900e+01</td><td>3.998900e+01</td><td>4.348700e+01</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 426 × 14\n",
       "\\begin{tabular}{llllllllllllll}\n",
       " country & variable & 1952 & 1957 & 1962 & 1967 & 1972 & 1977 & 1982 & 1987 & 1992 & 1997 & 2002 & 2007\\\\\n",
       " <chr> & <chr> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Afghanistan/Asia   & pop       & 8.425333e+06 & 9.240934e+06 & 1.026708e+07 & 1.153797e+07 & 1.307946e+07 & 1.488037e+07 & 1.288182e+07 & 1.386796e+07 & 1.631792e+07 & 2.222742e+07 & 2.526841e+07 & 3.188992e+07\\\\\n",
       "\t Afghanistan/Asia   & gdpPercap & 7.794453e+02 & 8.208530e+02 & 8.531007e+02 & 8.361971e+02 & 7.399811e+02 & 7.861134e+02 & 9.780114e+02 & 8.523959e+02 & 6.493414e+02 & 6.353414e+02 & 7.267341e+02 & 9.745803e+02\\\\\n",
       "\t Afghanistan/Asia   & lifeExp   & 2.880100e+01 & 3.033200e+01 & 3.199700e+01 & 3.402000e+01 & 3.608800e+01 & 3.843800e+01 & 3.985400e+01 & 4.082200e+01 & 4.167400e+01 & 4.176300e+01 & 4.212900e+01 & 4.382800e+01\\\\\n",
       "\t Albania/Europe     & pop       & 1.282697e+06 & 1.476505e+06 & 1.728137e+06 & 1.984060e+06 & 2.263554e+06 & 2.509048e+06 & 2.780097e+06 & 3.075321e+06 & 3.326498e+06 & 3.428038e+06 & 3.508512e+06 & 3.600523e+06\\\\\n",
       "\t Albania/Europe     & gdpPercap & 1.601056e+03 & 1.942284e+03 & 2.312889e+03 & 2.760197e+03 & 3.313422e+03 & 3.533004e+03 & 3.630881e+03 & 3.738933e+03 & 2.497438e+03 & 3.193055e+03 & 4.604212e+03 & 5.937030e+03\\\\\n",
       "\t Albania/Europe     & lifeExp   & 5.523000e+01 & 5.928000e+01 & 6.482000e+01 & 6.622000e+01 & 6.769000e+01 & 6.893000e+01 & 7.042000e+01 & 7.200000e+01 & 7.158100e+01 & 7.295000e+01 & 7.565100e+01 & 7.642300e+01\\\\\n",
       "\t Algeria/Africa     & pop       & 9.279525e+06 & 1.027086e+07 & 1.100095e+07 & 1.276050e+07 & 1.476079e+07 & 1.715280e+07 & 2.003375e+07 & 2.325496e+07 & 2.629837e+07 & 2.907202e+07 & 3.128714e+07 & 3.333322e+07\\\\\n",
       "\t Algeria/Africa     & gdpPercap & 2.449008e+03 & 3.013976e+03 & 2.550817e+03 & 3.246992e+03 & 4.182664e+03 & 4.910417e+03 & 5.745160e+03 & 5.681359e+03 & 5.023217e+03 & 4.797295e+03 & 5.288040e+03 & 6.223367e+03\\\\\n",
       "\t Algeria/Africa     & lifeExp   & 4.307700e+01 & 4.568500e+01 & 4.830300e+01 & 5.140700e+01 & 5.451800e+01 & 5.801400e+01 & 6.136800e+01 & 6.579900e+01 & 6.774400e+01 & 6.915200e+01 & 7.099400e+01 & 7.230100e+01\\\\\n",
       "\t Angola/Africa      & pop       & 4.232095e+06 & 4.561361e+06 & 4.826015e+06 & 5.247469e+06 & 5.894858e+06 & 6.162675e+06 & 7.016384e+06 & 7.874230e+06 & 8.735988e+06 & 9.875024e+06 & 1.086611e+07 & 1.242048e+07\\\\\n",
       "\t Angola/Africa      & gdpPercap & 3.520610e+03 & 3.827940e+03 & 4.269277e+03 & 5.522776e+03 & 5.473288e+03 & 3.008647e+03 & 2.756954e+03 & 2.430208e+03 & 2.627846e+03 & 2.277141e+03 & 2.773287e+03 & 4.797231e+03\\\\\n",
       "\t Angola/Africa      & lifeExp   & 3.001500e+01 & 3.199900e+01 & 3.400000e+01 & 3.598500e+01 & 3.792800e+01 & 3.948300e+01 & 3.994200e+01 & 3.990600e+01 & 4.064700e+01 & 4.096300e+01 & 4.100300e+01 & 4.273100e+01\\\\\n",
       "\t Argentina/Americas & pop       & 1.787696e+07 & 1.961054e+07 & 2.128378e+07 & 2.293423e+07 & 2.477980e+07 & 2.698383e+07 & 2.934137e+07 & 3.162092e+07 & 3.395895e+07 & 3.620346e+07 & 3.833112e+07 & 4.030193e+07\\\\\n",
       "\t Argentina/Americas & gdpPercap & 5.911315e+03 & 6.856856e+03 & 7.133166e+03 & 8.052953e+03 & 9.443039e+03 & 1.007903e+04 & 8.997897e+03 & 9.139671e+03 & 9.308419e+03 & 1.096728e+04 & 8.797641e+03 & 1.277938e+04\\\\\n",
       "\t Argentina/Americas & lifeExp   & 6.248500e+01 & 6.439900e+01 & 6.514200e+01 & 6.563400e+01 & 6.706500e+01 & 6.848100e+01 & 6.994200e+01 & 7.077400e+01 & 7.186800e+01 & 7.327500e+01 & 7.434000e+01 & 7.532000e+01\\\\\n",
       "\t Australia/Oceania  & pop       & 8.691212e+06 & 9.712569e+06 & 1.079497e+07 & 1.187226e+07 & 1.317700e+07 & 1.407410e+07 & 1.518420e+07 & 1.625725e+07 & 1.748198e+07 & 1.856524e+07 & 1.954679e+07 & 2.043418e+07\\\\\n",
       "\t Australia/Oceania  & gdpPercap & 1.003960e+04 & 1.094965e+04 & 1.221723e+04 & 1.452612e+04 & 1.678863e+04 & 1.833420e+04 & 1.947701e+04 & 2.188889e+04 & 2.342477e+04 & 2.699794e+04 & 3.068775e+04 & 3.443537e+04\\\\\n",
       "\t Australia/Oceania  & lifeExp   & 6.912000e+01 & 7.033000e+01 & 7.093000e+01 & 7.110000e+01 & 7.193000e+01 & 7.349000e+01 & 7.474000e+01 & 7.632000e+01 & 7.756000e+01 & 7.883000e+01 & 8.037000e+01 & 8.123500e+01\\\\\n",
       "\t Austria/Europe     & pop       & 6.927772e+06 & 6.965860e+06 & 7.129864e+06 & 7.376998e+06 & 7.544201e+06 & 7.568430e+06 & 7.574613e+06 & 7.578903e+06 & 7.914969e+06 & 8.069876e+06 & 8.148312e+06 & 8.199783e+06\\\\\n",
       "\t Austria/Europe     & gdpPercap & 6.137076e+03 & 8.842598e+03 & 1.075072e+04 & 1.283460e+04 & 1.666163e+04 & 1.974942e+04 & 2.159708e+04 & 2.368783e+04 & 2.704202e+04 & 2.909592e+04 & 3.241761e+04 & 3.612649e+04\\\\\n",
       "\t Austria/Europe     & lifeExp   & 6.680000e+01 & 6.748000e+01 & 6.954000e+01 & 7.014000e+01 & 7.063000e+01 & 7.217000e+01 & 7.318000e+01 & 7.494000e+01 & 7.604000e+01 & 7.751000e+01 & 7.898000e+01 & 7.982900e+01\\\\\n",
       "\t Bahrain/Asia       & pop       & 1.204470e+05 & 1.386550e+05 & 1.718630e+05 & 2.021820e+05 & 2.308000e+05 & 2.974100e+05 & 3.779670e+05 & 4.546120e+05 & 5.294910e+05 & 5.985610e+05 & 6.563970e+05 & 7.085730e+05\\\\\n",
       "\t Bahrain/Asia       & gdpPercap & 9.867085e+03 & 1.163580e+04 & 1.275328e+04 & 1.480467e+04 & 1.826866e+04 & 1.934010e+04 & 1.921115e+04 & 1.852402e+04 & 1.903558e+04 & 2.029202e+04 & 2.340356e+04 & 2.979605e+04\\\\\n",
       "\t Bahrain/Asia       & lifeExp   & 5.093900e+01 & 5.383200e+01 & 5.692300e+01 & 5.992300e+01 & 6.330000e+01 & 6.559300e+01 & 6.905200e+01 & 7.075000e+01 & 7.260100e+01 & 7.392500e+01 & 7.479500e+01 & 7.563500e+01\\\\\n",
       "\t Bangladesh/Asia    & pop       & 4.688686e+07 & 5.136547e+07 & 5.683929e+07 & 6.282188e+07 & 7.075930e+07 & 8.042831e+07 & 9.307441e+07 & 1.037642e+08 & 1.137046e+08 & 1.233153e+08 & 1.356568e+08 & 1.504483e+08\\\\\n",
       "\t Bangladesh/Asia    & gdpPercap & 6.842442e+02 & 6.616375e+02 & 6.863416e+02 & 7.211861e+02 & 6.302336e+02 & 6.598772e+02 & 6.769819e+02 & 7.519794e+02 & 8.378102e+02 & 9.727700e+02 & 1.136390e+03 & 1.391254e+03\\\\\n",
       "\t Bangladesh/Asia    & lifeExp   & 3.748400e+01 & 3.934800e+01 & 4.121600e+01 & 4.345300e+01 & 4.525200e+01 & 4.692300e+01 & 5.000900e+01 & 5.281900e+01 & 5.601800e+01 & 5.941200e+01 & 6.201300e+01 & 6.406200e+01\\\\\n",
       "\t Belgium/Europe     & pop       & 8.730405e+06 & 8.989111e+06 & 9.218400e+06 & 9.556500e+06 & 9.709100e+06 & 9.821800e+06 & 9.856303e+06 & 9.870200e+06 & 1.004562e+07 & 1.019979e+07 & 1.031197e+07 & 1.039223e+07\\\\\n",
       "\t Belgium/Europe     & gdpPercap & 8.343105e+03 & 9.714961e+03 & 1.099121e+04 & 1.314904e+04 & 1.667214e+04 & 1.911797e+04 & 2.097985e+04 & 2.252556e+04 & 2.557557e+04 & 2.756120e+04 & 3.048588e+04 & 3.369261e+04\\\\\n",
       "\t Belgium/Europe     & lifeExp   & 6.800000e+01 & 6.924000e+01 & 7.025000e+01 & 7.094000e+01 & 7.144000e+01 & 7.280000e+01 & 7.393000e+01 & 7.535000e+01 & 7.646000e+01 & 7.753000e+01 & 7.832000e+01 & 7.944100e+01\\\\\n",
       "\t ... & ... & ... & ... & ... & ... & ... & ... & ... & ... & ... & ... & ... & ...\\\\\n",
       "\t Uganda/Africa           & pop       & 5.824797e+06 & 6.675501e+06 & 7.688797e+06 & 8.900294e+06 & 1.019029e+07 & 1.145776e+07 & 1.293940e+07 & 1.528305e+07 & 1.825219e+07 & 2.121025e+07 & 2.473987e+07 & 2.917040e+07\\\\\n",
       "\t Uganda/Africa           & gdpPercap & 7.347535e+02 & 7.743711e+02 & 7.672717e+02 & 9.089185e+02 & 9.507359e+02 & 8.437331e+02 & 6.822662e+02 & 6.177244e+02 & 6.441708e+02 & 8.165591e+02 & 9.277210e+02 & 1.056380e+03\\\\\n",
       "\t Uganda/Africa           & lifeExp   & 3.997800e+01 & 4.257100e+01 & 4.534400e+01 & 4.805100e+01 & 5.101600e+01 & 5.035000e+01 & 4.984900e+01 & 5.150900e+01 & 4.882500e+01 & 4.457800e+01 & 4.781300e+01 & 5.154200e+01\\\\\n",
       "\t United Kingdom/Europe   & pop       & 5.043000e+07 & 5.143000e+07 & 5.329200e+07 & 5.495900e+07 & 5.607900e+07 & 5.617900e+07 & 5.633970e+07 & 5.698162e+07 & 5.786635e+07 & 5.880827e+07 & 5.991243e+07 & 6.077624e+07\\\\\n",
       "\t United Kingdom/Europe   & gdpPercap & 9.979508e+03 & 1.128318e+04 & 1.247718e+04 & 1.414285e+04 & 1.589512e+04 & 1.742875e+04 & 1.823242e+04 & 2.166479e+04 & 2.270509e+04 & 2.607453e+04 & 2.947900e+04 & 3.320326e+04\\\\\n",
       "\t United Kingdom/Europe   & lifeExp   & 6.918000e+01 & 7.042000e+01 & 7.076000e+01 & 7.136000e+01 & 7.201000e+01 & 7.276000e+01 & 7.404000e+01 & 7.500700e+01 & 7.642000e+01 & 7.721800e+01 & 7.847100e+01 & 7.942500e+01\\\\\n",
       "\t United States/Americas  & pop       & 1.575530e+08 & 1.719840e+08 & 1.865380e+08 & 1.987120e+08 & 2.098960e+08 & 2.202390e+08 & 2.321878e+08 & 2.428035e+08 & 2.568942e+08 & 2.729118e+08 & 2.876755e+08 & 3.011399e+08\\\\\n",
       "\t United States/Americas  & gdpPercap & 1.399048e+04 & 1.484713e+04 & 1.617315e+04 & 1.953037e+04 & 2.180604e+04 & 2.407263e+04 & 2.500956e+04 & 2.988435e+04 & 3.200393e+04 & 3.576743e+04 & 3.909710e+04 & 4.295165e+04\\\\\n",
       "\t United States/Americas  & lifeExp   & 6.844000e+01 & 6.949000e+01 & 7.021000e+01 & 7.076000e+01 & 7.134000e+01 & 7.338000e+01 & 7.465000e+01 & 7.502000e+01 & 7.609000e+01 & 7.681000e+01 & 7.731000e+01 & 7.824200e+01\\\\\n",
       "\t Uruguay/Americas        & pop       & 2.252965e+06 & 2.424959e+06 & 2.598466e+06 & 2.748579e+06 & 2.829526e+06 & 2.873520e+06 & 2.953997e+06 & 3.045153e+06 & 3.149262e+06 & 3.262838e+06 & 3.363085e+06 & 3.447496e+06\\\\\n",
       "\t Uruguay/Americas        & gdpPercap & 5.716767e+03 & 6.150773e+03 & 5.603358e+03 & 5.444620e+03 & 5.703409e+03 & 6.504340e+03 & 6.920223e+03 & 7.452399e+03 & 8.137005e+03 & 9.230241e+03 & 7.727002e+03 & 1.061146e+04\\\\\n",
       "\t Uruguay/Americas        & lifeExp   & 6.607100e+01 & 6.704400e+01 & 6.825300e+01 & 6.846800e+01 & 6.867300e+01 & 6.948100e+01 & 7.080500e+01 & 7.191800e+01 & 7.275200e+01 & 7.422300e+01 & 7.530700e+01 & 7.638400e+01\\\\\n",
       "\t Venezuela/Americas      & pop       & 5.439568e+06 & 6.702668e+06 & 8.143375e+06 & 9.709552e+06 & 1.151565e+07 & 1.350356e+07 & 1.562077e+07 & 1.791018e+07 & 2.026556e+07 & 2.237440e+07 & 2.428767e+07 & 2.608466e+07\\\\\n",
       "\t Venezuela/Americas      & gdpPercap & 7.689800e+03 & 9.802467e+03 & 8.422974e+03 & 9.541474e+03 & 1.050526e+04 & 1.314395e+04 & 1.115241e+04 & 9.883585e+03 & 1.073393e+04 & 1.016550e+04 & 8.605048e+03 & 1.141581e+04\\\\\n",
       "\t Venezuela/Americas      & lifeExp   & 5.508800e+01 & 5.790700e+01 & 6.077000e+01 & 6.347900e+01 & 6.571200e+01 & 6.745600e+01 & 6.855700e+01 & 7.019000e+01 & 7.115000e+01 & 7.214600e+01 & 7.276600e+01 & 7.374700e+01\\\\\n",
       "\t Vietnam/Asia            & pop       & 2.624684e+07 & 2.899854e+07 & 3.379614e+07 & 3.946391e+07 & 4.465501e+07 & 5.053351e+07 & 5.614218e+07 & 6.282649e+07 & 6.994073e+07 & 7.604900e+07 & 8.090815e+07 & 8.526236e+07\\\\\n",
       "\t Vietnam/Asia            & gdpPercap & 6.050665e+02 & 6.762854e+02 & 7.720492e+02 & 6.371233e+02 & 6.995016e+02 & 7.135371e+02 & 7.072358e+02 & 8.207994e+02 & 9.890231e+02 & 1.385897e+03 & 1.764457e+03 & 2.441576e+03\\\\\n",
       "\t Vietnam/Asia            & lifeExp   & 4.041200e+01 & 4.288700e+01 & 4.536300e+01 & 4.783800e+01 & 5.025400e+01 & 5.576400e+01 & 5.881600e+01 & 6.282000e+01 & 6.766200e+01 & 7.067200e+01 & 7.301700e+01 & 7.424900e+01\\\\\n",
       "\t West Bank and Gaza/Asia & pop       & 1.030585e+06 & 1.070439e+06 & 1.133134e+06 & 1.142636e+06 & 1.089572e+06 & 1.261091e+06 & 1.425876e+06 & 1.691210e+06 & 2.104779e+06 & 2.826046e+06 & 3.389578e+06 & 4.018332e+06\\\\\n",
       "\t West Bank and Gaza/Asia & gdpPercap & 1.515592e+03 & 1.827068e+03 & 2.198956e+03 & 2.649715e+03 & 3.133409e+03 & 3.682831e+03 & 4.336032e+03 & 5.107197e+03 & 6.017655e+03 & 7.110668e+03 & 4.515488e+03 & 3.025350e+03\\\\\n",
       "\t West Bank and Gaza/Asia & lifeExp   & 4.316000e+01 & 4.567100e+01 & 4.812700e+01 & 5.163100e+01 & 5.653200e+01 & 6.076500e+01 & 6.440600e+01 & 6.704600e+01 & 6.971800e+01 & 7.109600e+01 & 7.237000e+01 & 7.342200e+01\\\\\n",
       "\t Yemen, Rep./Asia        & pop       & 4.963829e+06 & 5.498090e+06 & 6.120081e+06 & 6.740785e+06 & 7.407075e+06 & 8.403990e+06 & 9.657618e+06 & 1.121934e+07 & 1.336800e+07 & 1.582650e+07 & 1.870126e+07 & 2.221174e+07\\\\\n",
       "\t Yemen, Rep./Asia        & gdpPercap & 7.817176e+02 & 8.048305e+02 & 8.256232e+02 & 8.624421e+02 & 1.265047e+03 & 1.829765e+03 & 1.977557e+03 & 1.971742e+03 & 1.879497e+03 & 2.117485e+03 & 2.234821e+03 & 2.280770e+03\\\\\n",
       "\t Yemen, Rep./Asia        & lifeExp   & 3.254800e+01 & 3.397000e+01 & 3.518000e+01 & 3.698400e+01 & 3.984800e+01 & 4.417500e+01 & 4.911300e+01 & 5.292200e+01 & 5.559900e+01 & 5.802000e+01 & 6.030800e+01 & 6.269800e+01\\\\\n",
       "\t Zambia/Africa           & pop       & 2.672000e+06 & 3.016000e+06 & 3.421000e+06 & 3.900000e+06 & 4.506497e+06 & 5.216550e+06 & 6.100407e+06 & 7.272406e+06 & 8.381163e+06 & 9.417789e+06 & 1.059581e+07 & 1.174604e+07\\\\\n",
       "\t Zambia/Africa           & gdpPercap & 1.147389e+03 & 1.311957e+03 & 1.452726e+03 & 1.777077e+03 & 1.773498e+03 & 1.588688e+03 & 1.408679e+03 & 1.213315e+03 & 1.210885e+03 & 1.071354e+03 & 1.071614e+03 & 1.271212e+03\\\\\n",
       "\t Zambia/Africa           & lifeExp   & 4.203800e+01 & 4.407700e+01 & 4.602300e+01 & 4.776800e+01 & 5.010700e+01 & 5.138600e+01 & 5.182100e+01 & 5.082100e+01 & 4.610000e+01 & 4.023800e+01 & 3.919300e+01 & 4.238400e+01\\\\\n",
       "\t Zimbabwe/Africa         & pop       & 3.080907e+06 & 3.646340e+06 & 4.277736e+06 & 4.995432e+06 & 5.861135e+06 & 6.642107e+06 & 7.636524e+06 & 9.216418e+06 & 1.070434e+07 & 1.140495e+07 & 1.192656e+07 & 1.231114e+07\\\\\n",
       "\t Zimbabwe/Africa         & gdpPercap & 4.068841e+02 & 5.187643e+02 & 5.272722e+02 & 5.697951e+02 & 7.993622e+02 & 6.855877e+02 & 7.888550e+02 & 7.061573e+02 & 6.934208e+02 & 7.924500e+02 & 6.720386e+02 & 4.697093e+02\\\\\n",
       "\t Zimbabwe/Africa         & lifeExp   & 4.845100e+01 & 5.046900e+01 & 5.235800e+01 & 5.399500e+01 & 5.563500e+01 & 5.767400e+01 & 6.036300e+01 & 6.235100e+01 & 6.037700e+01 & 4.680900e+01 & 3.998900e+01 & 4.348700e+01\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 426 × 14\n",
       "\n",
       "| country &lt;chr&gt; | variable &lt;chr&gt; | 1952 &lt;dbl&gt; | 1957 &lt;dbl&gt; | 1962 &lt;dbl&gt; | 1967 &lt;dbl&gt; | 1972 &lt;dbl&gt; | 1977 &lt;dbl&gt; | 1982 &lt;dbl&gt; | 1987 &lt;dbl&gt; | 1992 &lt;dbl&gt; | 1997 &lt;dbl&gt; | 2002 &lt;dbl&gt; | 2007 &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| Afghanistan/Asia   | pop       | 8.425333e+06 | 9.240934e+06 | 1.026708e+07 | 1.153797e+07 | 1.307946e+07 | 1.488037e+07 | 1.288182e+07 | 1.386796e+07 | 1.631792e+07 | 2.222742e+07 | 2.526841e+07 | 3.188992e+07 |\n",
       "| Afghanistan/Asia   | gdpPercap | 7.794453e+02 | 8.208530e+02 | 8.531007e+02 | 8.361971e+02 | 7.399811e+02 | 7.861134e+02 | 9.780114e+02 | 8.523959e+02 | 6.493414e+02 | 6.353414e+02 | 7.267341e+02 | 9.745803e+02 |\n",
       "| Afghanistan/Asia   | lifeExp   | 2.880100e+01 | 3.033200e+01 | 3.199700e+01 | 3.402000e+01 | 3.608800e+01 | 3.843800e+01 | 3.985400e+01 | 4.082200e+01 | 4.167400e+01 | 4.176300e+01 | 4.212900e+01 | 4.382800e+01 |\n",
       "| Albania/Europe     | pop       | 1.282697e+06 | 1.476505e+06 | 1.728137e+06 | 1.984060e+06 | 2.263554e+06 | 2.509048e+06 | 2.780097e+06 | 3.075321e+06 | 3.326498e+06 | 3.428038e+06 | 3.508512e+06 | 3.600523e+06 |\n",
       "| Albania/Europe     | gdpPercap | 1.601056e+03 | 1.942284e+03 | 2.312889e+03 | 2.760197e+03 | 3.313422e+03 | 3.533004e+03 | 3.630881e+03 | 3.738933e+03 | 2.497438e+03 | 3.193055e+03 | 4.604212e+03 | 5.937030e+03 |\n",
       "| Albania/Europe     | lifeExp   | 5.523000e+01 | 5.928000e+01 | 6.482000e+01 | 6.622000e+01 | 6.769000e+01 | 6.893000e+01 | 7.042000e+01 | 7.200000e+01 | 7.158100e+01 | 7.295000e+01 | 7.565100e+01 | 7.642300e+01 |\n",
       "| Algeria/Africa     | pop       | 9.279525e+06 | 1.027086e+07 | 1.100095e+07 | 1.276050e+07 | 1.476079e+07 | 1.715280e+07 | 2.003375e+07 | 2.325496e+07 | 2.629837e+07 | 2.907202e+07 | 3.128714e+07 | 3.333322e+07 |\n",
       "| Algeria/Africa     | gdpPercap | 2.449008e+03 | 3.013976e+03 | 2.550817e+03 | 3.246992e+03 | 4.182664e+03 | 4.910417e+03 | 5.745160e+03 | 5.681359e+03 | 5.023217e+03 | 4.797295e+03 | 5.288040e+03 | 6.223367e+03 |\n",
       "| Algeria/Africa     | lifeExp   | 4.307700e+01 | 4.568500e+01 | 4.830300e+01 | 5.140700e+01 | 5.451800e+01 | 5.801400e+01 | 6.136800e+01 | 6.579900e+01 | 6.774400e+01 | 6.915200e+01 | 7.099400e+01 | 7.230100e+01 |\n",
       "| Angola/Africa      | pop       | 4.232095e+06 | 4.561361e+06 | 4.826015e+06 | 5.247469e+06 | 5.894858e+06 | 6.162675e+06 | 7.016384e+06 | 7.874230e+06 | 8.735988e+06 | 9.875024e+06 | 1.086611e+07 | 1.242048e+07 |\n",
       "| Angola/Africa      | gdpPercap | 3.520610e+03 | 3.827940e+03 | 4.269277e+03 | 5.522776e+03 | 5.473288e+03 | 3.008647e+03 | 2.756954e+03 | 2.430208e+03 | 2.627846e+03 | 2.277141e+03 | 2.773287e+03 | 4.797231e+03 |\n",
       "| Angola/Africa      | lifeExp   | 3.001500e+01 | 3.199900e+01 | 3.400000e+01 | 3.598500e+01 | 3.792800e+01 | 3.948300e+01 | 3.994200e+01 | 3.990600e+01 | 4.064700e+01 | 4.096300e+01 | 4.100300e+01 | 4.273100e+01 |\n",
       "| Argentina/Americas | pop       | 1.787696e+07 | 1.961054e+07 | 2.128378e+07 | 2.293423e+07 | 2.477980e+07 | 2.698383e+07 | 2.934137e+07 | 3.162092e+07 | 3.395895e+07 | 3.620346e+07 | 3.833112e+07 | 4.030193e+07 |\n",
       "| Argentina/Americas | gdpPercap | 5.911315e+03 | 6.856856e+03 | 7.133166e+03 | 8.052953e+03 | 9.443039e+03 | 1.007903e+04 | 8.997897e+03 | 9.139671e+03 | 9.308419e+03 | 1.096728e+04 | 8.797641e+03 | 1.277938e+04 |\n",
       "| Argentina/Americas | lifeExp   | 6.248500e+01 | 6.439900e+01 | 6.514200e+01 | 6.563400e+01 | 6.706500e+01 | 6.848100e+01 | 6.994200e+01 | 7.077400e+01 | 7.186800e+01 | 7.327500e+01 | 7.434000e+01 | 7.532000e+01 |\n",
       "| Australia/Oceania  | pop       | 8.691212e+06 | 9.712569e+06 | 1.079497e+07 | 1.187226e+07 | 1.317700e+07 | 1.407410e+07 | 1.518420e+07 | 1.625725e+07 | 1.748198e+07 | 1.856524e+07 | 1.954679e+07 | 2.043418e+07 |\n",
       "| Australia/Oceania  | gdpPercap | 1.003960e+04 | 1.094965e+04 | 1.221723e+04 | 1.452612e+04 | 1.678863e+04 | 1.833420e+04 | 1.947701e+04 | 2.188889e+04 | 2.342477e+04 | 2.699794e+04 | 3.068775e+04 | 3.443537e+04 |\n",
       "| Australia/Oceania  | lifeExp   | 6.912000e+01 | 7.033000e+01 | 7.093000e+01 | 7.110000e+01 | 7.193000e+01 | 7.349000e+01 | 7.474000e+01 | 7.632000e+01 | 7.756000e+01 | 7.883000e+01 | 8.037000e+01 | 8.123500e+01 |\n",
       "| Austria/Europe     | pop       | 6.927772e+06 | 6.965860e+06 | 7.129864e+06 | 7.376998e+06 | 7.544201e+06 | 7.568430e+06 | 7.574613e+06 | 7.578903e+06 | 7.914969e+06 | 8.069876e+06 | 8.148312e+06 | 8.199783e+06 |\n",
       "| Austria/Europe     | gdpPercap | 6.137076e+03 | 8.842598e+03 | 1.075072e+04 | 1.283460e+04 | 1.666163e+04 | 1.974942e+04 | 2.159708e+04 | 2.368783e+04 | 2.704202e+04 | 2.909592e+04 | 3.241761e+04 | 3.612649e+04 |\n",
       "| Austria/Europe     | lifeExp   | 6.680000e+01 | 6.748000e+01 | 6.954000e+01 | 7.014000e+01 | 7.063000e+01 | 7.217000e+01 | 7.318000e+01 | 7.494000e+01 | 7.604000e+01 | 7.751000e+01 | 7.898000e+01 | 7.982900e+01 |\n",
       "| Bahrain/Asia       | pop       | 1.204470e+05 | 1.386550e+05 | 1.718630e+05 | 2.021820e+05 | 2.308000e+05 | 2.974100e+05 | 3.779670e+05 | 4.546120e+05 | 5.294910e+05 | 5.985610e+05 | 6.563970e+05 | 7.085730e+05 |\n",
       "| Bahrain/Asia       | gdpPercap | 9.867085e+03 | 1.163580e+04 | 1.275328e+04 | 1.480467e+04 | 1.826866e+04 | 1.934010e+04 | 1.921115e+04 | 1.852402e+04 | 1.903558e+04 | 2.029202e+04 | 2.340356e+04 | 2.979605e+04 |\n",
       "| Bahrain/Asia       | lifeExp   | 5.093900e+01 | 5.383200e+01 | 5.692300e+01 | 5.992300e+01 | 6.330000e+01 | 6.559300e+01 | 6.905200e+01 | 7.075000e+01 | 7.260100e+01 | 7.392500e+01 | 7.479500e+01 | 7.563500e+01 |\n",
       "| Bangladesh/Asia    | pop       | 4.688686e+07 | 5.136547e+07 | 5.683929e+07 | 6.282188e+07 | 7.075930e+07 | 8.042831e+07 | 9.307441e+07 | 1.037642e+08 | 1.137046e+08 | 1.233153e+08 | 1.356568e+08 | 1.504483e+08 |\n",
       "| Bangladesh/Asia    | gdpPercap | 6.842442e+02 | 6.616375e+02 | 6.863416e+02 | 7.211861e+02 | 6.302336e+02 | 6.598772e+02 | 6.769819e+02 | 7.519794e+02 | 8.378102e+02 | 9.727700e+02 | 1.136390e+03 | 1.391254e+03 |\n",
       "| Bangladesh/Asia    | lifeExp   | 3.748400e+01 | 3.934800e+01 | 4.121600e+01 | 4.345300e+01 | 4.525200e+01 | 4.692300e+01 | 5.000900e+01 | 5.281900e+01 | 5.601800e+01 | 5.941200e+01 | 6.201300e+01 | 6.406200e+01 |\n",
       "| Belgium/Europe     | pop       | 8.730405e+06 | 8.989111e+06 | 9.218400e+06 | 9.556500e+06 | 9.709100e+06 | 9.821800e+06 | 9.856303e+06 | 9.870200e+06 | 1.004562e+07 | 1.019979e+07 | 1.031197e+07 | 1.039223e+07 |\n",
       "| Belgium/Europe     | gdpPercap | 8.343105e+03 | 9.714961e+03 | 1.099121e+04 | 1.314904e+04 | 1.667214e+04 | 1.911797e+04 | 2.097985e+04 | 2.252556e+04 | 2.557557e+04 | 2.756120e+04 | 3.048588e+04 | 3.369261e+04 |\n",
       "| Belgium/Europe     | lifeExp   | 6.800000e+01 | 6.924000e+01 | 7.025000e+01 | 7.094000e+01 | 7.144000e+01 | 7.280000e+01 | 7.393000e+01 | 7.535000e+01 | 7.646000e+01 | 7.753000e+01 | 7.832000e+01 | 7.944100e+01 |\n",
       "| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |\n",
       "| Uganda/Africa           | pop       | 5.824797e+06 | 6.675501e+06 | 7.688797e+06 | 8.900294e+06 | 1.019029e+07 | 1.145776e+07 | 1.293940e+07 | 1.528305e+07 | 1.825219e+07 | 2.121025e+07 | 2.473987e+07 | 2.917040e+07 |\n",
       "| Uganda/Africa           | gdpPercap | 7.347535e+02 | 7.743711e+02 | 7.672717e+02 | 9.089185e+02 | 9.507359e+02 | 8.437331e+02 | 6.822662e+02 | 6.177244e+02 | 6.441708e+02 | 8.165591e+02 | 9.277210e+02 | 1.056380e+03 |\n",
       "| Uganda/Africa           | lifeExp   | 3.997800e+01 | 4.257100e+01 | 4.534400e+01 | 4.805100e+01 | 5.101600e+01 | 5.035000e+01 | 4.984900e+01 | 5.150900e+01 | 4.882500e+01 | 4.457800e+01 | 4.781300e+01 | 5.154200e+01 |\n",
       "| United Kingdom/Europe   | pop       | 5.043000e+07 | 5.143000e+07 | 5.329200e+07 | 5.495900e+07 | 5.607900e+07 | 5.617900e+07 | 5.633970e+07 | 5.698162e+07 | 5.786635e+07 | 5.880827e+07 | 5.991243e+07 | 6.077624e+07 |\n",
       "| United Kingdom/Europe   | gdpPercap | 9.979508e+03 | 1.128318e+04 | 1.247718e+04 | 1.414285e+04 | 1.589512e+04 | 1.742875e+04 | 1.823242e+04 | 2.166479e+04 | 2.270509e+04 | 2.607453e+04 | 2.947900e+04 | 3.320326e+04 |\n",
       "| United Kingdom/Europe   | lifeExp   | 6.918000e+01 | 7.042000e+01 | 7.076000e+01 | 7.136000e+01 | 7.201000e+01 | 7.276000e+01 | 7.404000e+01 | 7.500700e+01 | 7.642000e+01 | 7.721800e+01 | 7.847100e+01 | 7.942500e+01 |\n",
       "| United States/Americas  | pop       | 1.575530e+08 | 1.719840e+08 | 1.865380e+08 | 1.987120e+08 | 2.098960e+08 | 2.202390e+08 | 2.321878e+08 | 2.428035e+08 | 2.568942e+08 | 2.729118e+08 | 2.876755e+08 | 3.011399e+08 |\n",
       "| United States/Americas  | gdpPercap | 1.399048e+04 | 1.484713e+04 | 1.617315e+04 | 1.953037e+04 | 2.180604e+04 | 2.407263e+04 | 2.500956e+04 | 2.988435e+04 | 3.200393e+04 | 3.576743e+04 | 3.909710e+04 | 4.295165e+04 |\n",
       "| United States/Americas  | lifeExp   | 6.844000e+01 | 6.949000e+01 | 7.021000e+01 | 7.076000e+01 | 7.134000e+01 | 7.338000e+01 | 7.465000e+01 | 7.502000e+01 | 7.609000e+01 | 7.681000e+01 | 7.731000e+01 | 7.824200e+01 |\n",
       "| Uruguay/Americas        | pop       | 2.252965e+06 | 2.424959e+06 | 2.598466e+06 | 2.748579e+06 | 2.829526e+06 | 2.873520e+06 | 2.953997e+06 | 3.045153e+06 | 3.149262e+06 | 3.262838e+06 | 3.363085e+06 | 3.447496e+06 |\n",
       "| Uruguay/Americas        | gdpPercap | 5.716767e+03 | 6.150773e+03 | 5.603358e+03 | 5.444620e+03 | 5.703409e+03 | 6.504340e+03 | 6.920223e+03 | 7.452399e+03 | 8.137005e+03 | 9.230241e+03 | 7.727002e+03 | 1.061146e+04 |\n",
       "| Uruguay/Americas        | lifeExp   | 6.607100e+01 | 6.704400e+01 | 6.825300e+01 | 6.846800e+01 | 6.867300e+01 | 6.948100e+01 | 7.080500e+01 | 7.191800e+01 | 7.275200e+01 | 7.422300e+01 | 7.530700e+01 | 7.638400e+01 |\n",
       "| Venezuela/Americas      | pop       | 5.439568e+06 | 6.702668e+06 | 8.143375e+06 | 9.709552e+06 | 1.151565e+07 | 1.350356e+07 | 1.562077e+07 | 1.791018e+07 | 2.026556e+07 | 2.237440e+07 | 2.428767e+07 | 2.608466e+07 |\n",
       "| Venezuela/Americas      | gdpPercap | 7.689800e+03 | 9.802467e+03 | 8.422974e+03 | 9.541474e+03 | 1.050526e+04 | 1.314395e+04 | 1.115241e+04 | 9.883585e+03 | 1.073393e+04 | 1.016550e+04 | 8.605048e+03 | 1.141581e+04 |\n",
       "| Venezuela/Americas      | lifeExp   | 5.508800e+01 | 5.790700e+01 | 6.077000e+01 | 6.347900e+01 | 6.571200e+01 | 6.745600e+01 | 6.855700e+01 | 7.019000e+01 | 7.115000e+01 | 7.214600e+01 | 7.276600e+01 | 7.374700e+01 |\n",
       "| Vietnam/Asia            | pop       | 2.624684e+07 | 2.899854e+07 | 3.379614e+07 | 3.946391e+07 | 4.465501e+07 | 5.053351e+07 | 5.614218e+07 | 6.282649e+07 | 6.994073e+07 | 7.604900e+07 | 8.090815e+07 | 8.526236e+07 |\n",
       "| Vietnam/Asia            | gdpPercap | 6.050665e+02 | 6.762854e+02 | 7.720492e+02 | 6.371233e+02 | 6.995016e+02 | 7.135371e+02 | 7.072358e+02 | 8.207994e+02 | 9.890231e+02 | 1.385897e+03 | 1.764457e+03 | 2.441576e+03 |\n",
       "| Vietnam/Asia            | lifeExp   | 4.041200e+01 | 4.288700e+01 | 4.536300e+01 | 4.783800e+01 | 5.025400e+01 | 5.576400e+01 | 5.881600e+01 | 6.282000e+01 | 6.766200e+01 | 7.067200e+01 | 7.301700e+01 | 7.424900e+01 |\n",
       "| West Bank and Gaza/Asia | pop       | 1.030585e+06 | 1.070439e+06 | 1.133134e+06 | 1.142636e+06 | 1.089572e+06 | 1.261091e+06 | 1.425876e+06 | 1.691210e+06 | 2.104779e+06 | 2.826046e+06 | 3.389578e+06 | 4.018332e+06 |\n",
       "| West Bank and Gaza/Asia | gdpPercap | 1.515592e+03 | 1.827068e+03 | 2.198956e+03 | 2.649715e+03 | 3.133409e+03 | 3.682831e+03 | 4.336032e+03 | 5.107197e+03 | 6.017655e+03 | 7.110668e+03 | 4.515488e+03 | 3.025350e+03 |\n",
       "| West Bank and Gaza/Asia | lifeExp   | 4.316000e+01 | 4.567100e+01 | 4.812700e+01 | 5.163100e+01 | 5.653200e+01 | 6.076500e+01 | 6.440600e+01 | 6.704600e+01 | 6.971800e+01 | 7.109600e+01 | 7.237000e+01 | 7.342200e+01 |\n",
       "| Yemen, Rep./Asia        | pop       | 4.963829e+06 | 5.498090e+06 | 6.120081e+06 | 6.740785e+06 | 7.407075e+06 | 8.403990e+06 | 9.657618e+06 | 1.121934e+07 | 1.336800e+07 | 1.582650e+07 | 1.870126e+07 | 2.221174e+07 |\n",
       "| Yemen, Rep./Asia        | gdpPercap | 7.817176e+02 | 8.048305e+02 | 8.256232e+02 | 8.624421e+02 | 1.265047e+03 | 1.829765e+03 | 1.977557e+03 | 1.971742e+03 | 1.879497e+03 | 2.117485e+03 | 2.234821e+03 | 2.280770e+03 |\n",
       "| Yemen, Rep./Asia        | lifeExp   | 3.254800e+01 | 3.397000e+01 | 3.518000e+01 | 3.698400e+01 | 3.984800e+01 | 4.417500e+01 | 4.911300e+01 | 5.292200e+01 | 5.559900e+01 | 5.802000e+01 | 6.030800e+01 | 6.269800e+01 |\n",
       "| Zambia/Africa           | pop       | 2.672000e+06 | 3.016000e+06 | 3.421000e+06 | 3.900000e+06 | 4.506497e+06 | 5.216550e+06 | 6.100407e+06 | 7.272406e+06 | 8.381163e+06 | 9.417789e+06 | 1.059581e+07 | 1.174604e+07 |\n",
       "| Zambia/Africa           | gdpPercap | 1.147389e+03 | 1.311957e+03 | 1.452726e+03 | 1.777077e+03 | 1.773498e+03 | 1.588688e+03 | 1.408679e+03 | 1.213315e+03 | 1.210885e+03 | 1.071354e+03 | 1.071614e+03 | 1.271212e+03 |\n",
       "| Zambia/Africa           | lifeExp   | 4.203800e+01 | 4.407700e+01 | 4.602300e+01 | 4.776800e+01 | 5.010700e+01 | 5.138600e+01 | 5.182100e+01 | 5.082100e+01 | 4.610000e+01 | 4.023800e+01 | 3.919300e+01 | 4.238400e+01 |\n",
       "| Zimbabwe/Africa         | pop       | 3.080907e+06 | 3.646340e+06 | 4.277736e+06 | 4.995432e+06 | 5.861135e+06 | 6.642107e+06 | 7.636524e+06 | 9.216418e+06 | 1.070434e+07 | 1.140495e+07 | 1.192656e+07 | 1.231114e+07 |\n",
       "| Zimbabwe/Africa         | gdpPercap | 4.068841e+02 | 5.187643e+02 | 5.272722e+02 | 5.697951e+02 | 7.993622e+02 | 6.855877e+02 | 7.888550e+02 | 7.061573e+02 | 6.934208e+02 | 7.924500e+02 | 6.720386e+02 | 4.697093e+02 |\n",
       "| Zimbabwe/Africa         | lifeExp   | 4.845100e+01 | 5.046900e+01 | 5.235800e+01 | 5.399500e+01 | 5.563500e+01 | 5.767400e+01 | 6.036300e+01 | 6.235100e+01 | 6.037700e+01 | 4.680900e+01 | 3.998900e+01 | 4.348700e+01 |\n",
       "\n"
      ],
      "text/plain": [
       "    country                 variable  1952         1957         1962        \n",
       "1   Afghanistan/Asia        pop       8.425333e+06 9.240934e+06 1.026708e+07\n",
       "2   Afghanistan/Asia        gdpPercap 7.794453e+02 8.208530e+02 8.531007e+02\n",
       "3   Afghanistan/Asia        lifeExp   2.880100e+01 3.033200e+01 3.199700e+01\n",
       "4   Albania/Europe          pop       1.282697e+06 1.476505e+06 1.728137e+06\n",
       "5   Albania/Europe          gdpPercap 1.601056e+03 1.942284e+03 2.312889e+03\n",
       "6   Albania/Europe          lifeExp   5.523000e+01 5.928000e+01 6.482000e+01\n",
       "7   Algeria/Africa          pop       9.279525e+06 1.027086e+07 1.100095e+07\n",
       "8   Algeria/Africa          gdpPercap 2.449008e+03 3.013976e+03 2.550817e+03\n",
       "9   Algeria/Africa          lifeExp   4.307700e+01 4.568500e+01 4.830300e+01\n",
       "10  Angola/Africa           pop       4.232095e+06 4.561361e+06 4.826015e+06\n",
       "11  Angola/Africa           gdpPercap 3.520610e+03 3.827940e+03 4.269277e+03\n",
       "12  Angola/Africa           lifeExp   3.001500e+01 3.199900e+01 3.400000e+01\n",
       "13  Argentina/Americas      pop       1.787696e+07 1.961054e+07 2.128378e+07\n",
       "14  Argentina/Americas      gdpPercap 5.911315e+03 6.856856e+03 7.133166e+03\n",
       "15  Argentina/Americas      lifeExp   6.248500e+01 6.439900e+01 6.514200e+01\n",
       "16  Australia/Oceania       pop       8.691212e+06 9.712569e+06 1.079497e+07\n",
       "17  Australia/Oceania       gdpPercap 1.003960e+04 1.094965e+04 1.221723e+04\n",
       "18  Australia/Oceania       lifeExp   6.912000e+01 7.033000e+01 7.093000e+01\n",
       "19  Austria/Europe          pop       6.927772e+06 6.965860e+06 7.129864e+06\n",
       "20  Austria/Europe          gdpPercap 6.137076e+03 8.842598e+03 1.075072e+04\n",
       "21  Austria/Europe          lifeExp   6.680000e+01 6.748000e+01 6.954000e+01\n",
       "22  Bahrain/Asia            pop       1.204470e+05 1.386550e+05 1.718630e+05\n",
       "23  Bahrain/Asia            gdpPercap 9.867085e+03 1.163580e+04 1.275328e+04\n",
       "24  Bahrain/Asia            lifeExp   5.093900e+01 5.383200e+01 5.692300e+01\n",
       "25  Bangladesh/Asia         pop       4.688686e+07 5.136547e+07 5.683929e+07\n",
       "26  Bangladesh/Asia         gdpPercap 6.842442e+02 6.616375e+02 6.863416e+02\n",
       "27  Bangladesh/Asia         lifeExp   3.748400e+01 3.934800e+01 4.121600e+01\n",
       "28  Belgium/Europe          pop       8.730405e+06 8.989111e+06 9.218400e+06\n",
       "29  Belgium/Europe          gdpPercap 8.343105e+03 9.714961e+03 1.099121e+04\n",
       "30  Belgium/Europe          lifeExp   6.800000e+01 6.924000e+01 7.025000e+01\n",
       "... ...                     ...       ...          ...          ...         \n",
       "397 Uganda/Africa           pop       5.824797e+06 6.675501e+06 7.688797e+06\n",
       "398 Uganda/Africa           gdpPercap 7.347535e+02 7.743711e+02 7.672717e+02\n",
       "399 Uganda/Africa           lifeExp   3.997800e+01 4.257100e+01 4.534400e+01\n",
       "400 United Kingdom/Europe   pop       5.043000e+07 5.143000e+07 5.329200e+07\n",
       "401 United Kingdom/Europe   gdpPercap 9.979508e+03 1.128318e+04 1.247718e+04\n",
       "402 United Kingdom/Europe   lifeExp   6.918000e+01 7.042000e+01 7.076000e+01\n",
       "403 United States/Americas  pop       1.575530e+08 1.719840e+08 1.865380e+08\n",
       "404 United States/Americas  gdpPercap 1.399048e+04 1.484713e+04 1.617315e+04\n",
       "405 United States/Americas  lifeExp   6.844000e+01 6.949000e+01 7.021000e+01\n",
       "406 Uruguay/Americas        pop       2.252965e+06 2.424959e+06 2.598466e+06\n",
       "407 Uruguay/Americas        gdpPercap 5.716767e+03 6.150773e+03 5.603358e+03\n",
       "408 Uruguay/Americas        lifeExp   6.607100e+01 6.704400e+01 6.825300e+01\n",
       "409 Venezuela/Americas      pop       5.439568e+06 6.702668e+06 8.143375e+06\n",
       "410 Venezuela/Americas      gdpPercap 7.689800e+03 9.802467e+03 8.422974e+03\n",
       "411 Venezuela/Americas      lifeExp   5.508800e+01 5.790700e+01 6.077000e+01\n",
       "412 Vietnam/Asia            pop       2.624684e+07 2.899854e+07 3.379614e+07\n",
       "413 Vietnam/Asia            gdpPercap 6.050665e+02 6.762854e+02 7.720492e+02\n",
       "414 Vietnam/Asia            lifeExp   4.041200e+01 4.288700e+01 4.536300e+01\n",
       "415 West Bank and Gaza/Asia pop       1.030585e+06 1.070439e+06 1.133134e+06\n",
       "416 West Bank and Gaza/Asia gdpPercap 1.515592e+03 1.827068e+03 2.198956e+03\n",
       "417 West Bank and Gaza/Asia lifeExp   4.316000e+01 4.567100e+01 4.812700e+01\n",
       "418 Yemen, Rep./Asia        pop       4.963829e+06 5.498090e+06 6.120081e+06\n",
       "419 Yemen, Rep./Asia        gdpPercap 7.817176e+02 8.048305e+02 8.256232e+02\n",
       "420 Yemen, Rep./Asia        lifeExp   3.254800e+01 3.397000e+01 3.518000e+01\n",
       "421 Zambia/Africa           pop       2.672000e+06 3.016000e+06 3.421000e+06\n",
       "422 Zambia/Africa           gdpPercap 1.147389e+03 1.311957e+03 1.452726e+03\n",
       "423 Zambia/Africa           lifeExp   4.203800e+01 4.407700e+01 4.602300e+01\n",
       "424 Zimbabwe/Africa         pop       3.080907e+06 3.646340e+06 4.277736e+06\n",
       "425 Zimbabwe/Africa         gdpPercap 4.068841e+02 5.187643e+02 5.272722e+02\n",
       "426 Zimbabwe/Africa         lifeExp   4.845100e+01 5.046900e+01 5.235800e+01\n",
       "    1967         1972         1977         1982         1987        \n",
       "1   1.153797e+07 1.307946e+07 1.488037e+07 1.288182e+07 1.386796e+07\n",
       "2   8.361971e+02 7.399811e+02 7.861134e+02 9.780114e+02 8.523959e+02\n",
       "3   3.402000e+01 3.608800e+01 3.843800e+01 3.985400e+01 4.082200e+01\n",
       "4   1.984060e+06 2.263554e+06 2.509048e+06 2.780097e+06 3.075321e+06\n",
       "5   2.760197e+03 3.313422e+03 3.533004e+03 3.630881e+03 3.738933e+03\n",
       "6   6.622000e+01 6.769000e+01 6.893000e+01 7.042000e+01 7.200000e+01\n",
       "7   1.276050e+07 1.476079e+07 1.715280e+07 2.003375e+07 2.325496e+07\n",
       "8   3.246992e+03 4.182664e+03 4.910417e+03 5.745160e+03 5.681359e+03\n",
       "9   5.140700e+01 5.451800e+01 5.801400e+01 6.136800e+01 6.579900e+01\n",
       "10  5.247469e+06 5.894858e+06 6.162675e+06 7.016384e+06 7.874230e+06\n",
       "11  5.522776e+03 5.473288e+03 3.008647e+03 2.756954e+03 2.430208e+03\n",
       "12  3.598500e+01 3.792800e+01 3.948300e+01 3.994200e+01 3.990600e+01\n",
       "13  2.293423e+07 2.477980e+07 2.698383e+07 2.934137e+07 3.162092e+07\n",
       "14  8.052953e+03 9.443039e+03 1.007903e+04 8.997897e+03 9.139671e+03\n",
       "15  6.563400e+01 6.706500e+01 6.848100e+01 6.994200e+01 7.077400e+01\n",
       "16  1.187226e+07 1.317700e+07 1.407410e+07 1.518420e+07 1.625725e+07\n",
       "17  1.452612e+04 1.678863e+04 1.833420e+04 1.947701e+04 2.188889e+04\n",
       "18  7.110000e+01 7.193000e+01 7.349000e+01 7.474000e+01 7.632000e+01\n",
       "19  7.376998e+06 7.544201e+06 7.568430e+06 7.574613e+06 7.578903e+06\n",
       "20  1.283460e+04 1.666163e+04 1.974942e+04 2.159708e+04 2.368783e+04\n",
       "21  7.014000e+01 7.063000e+01 7.217000e+01 7.318000e+01 7.494000e+01\n",
       "22  2.021820e+05 2.308000e+05 2.974100e+05 3.779670e+05 4.546120e+05\n",
       "23  1.480467e+04 1.826866e+04 1.934010e+04 1.921115e+04 1.852402e+04\n",
       "24  5.992300e+01 6.330000e+01 6.559300e+01 6.905200e+01 7.075000e+01\n",
       "25  6.282188e+07 7.075930e+07 8.042831e+07 9.307441e+07 1.037642e+08\n",
       "26  7.211861e+02 6.302336e+02 6.598772e+02 6.769819e+02 7.519794e+02\n",
       "27  4.345300e+01 4.525200e+01 4.692300e+01 5.000900e+01 5.281900e+01\n",
       "28  9.556500e+06 9.709100e+06 9.821800e+06 9.856303e+06 9.870200e+06\n",
       "29  1.314904e+04 1.667214e+04 1.911797e+04 2.097985e+04 2.252556e+04\n",
       "30  7.094000e+01 7.144000e+01 7.280000e+01 7.393000e+01 7.535000e+01\n",
       "... ...          ...          ...          ...          ...         \n",
       "397 8.900294e+06 1.019029e+07 1.145776e+07 1.293940e+07 1.528305e+07\n",
       "398 9.089185e+02 9.507359e+02 8.437331e+02 6.822662e+02 6.177244e+02\n",
       "399 4.805100e+01 5.101600e+01 5.035000e+01 4.984900e+01 5.150900e+01\n",
       "400 5.495900e+07 5.607900e+07 5.617900e+07 5.633970e+07 5.698162e+07\n",
       "401 1.414285e+04 1.589512e+04 1.742875e+04 1.823242e+04 2.166479e+04\n",
       "402 7.136000e+01 7.201000e+01 7.276000e+01 7.404000e+01 7.500700e+01\n",
       "403 1.987120e+08 2.098960e+08 2.202390e+08 2.321878e+08 2.428035e+08\n",
       "404 1.953037e+04 2.180604e+04 2.407263e+04 2.500956e+04 2.988435e+04\n",
       "405 7.076000e+01 7.134000e+01 7.338000e+01 7.465000e+01 7.502000e+01\n",
       "406 2.748579e+06 2.829526e+06 2.873520e+06 2.953997e+06 3.045153e+06\n",
       "407 5.444620e+03 5.703409e+03 6.504340e+03 6.920223e+03 7.452399e+03\n",
       "408 6.846800e+01 6.867300e+01 6.948100e+01 7.080500e+01 7.191800e+01\n",
       "409 9.709552e+06 1.151565e+07 1.350356e+07 1.562077e+07 1.791018e+07\n",
       "410 9.541474e+03 1.050526e+04 1.314395e+04 1.115241e+04 9.883585e+03\n",
       "411 6.347900e+01 6.571200e+01 6.745600e+01 6.855700e+01 7.019000e+01\n",
       "412 3.946391e+07 4.465501e+07 5.053351e+07 5.614218e+07 6.282649e+07\n",
       "413 6.371233e+02 6.995016e+02 7.135371e+02 7.072358e+02 8.207994e+02\n",
       "414 4.783800e+01 5.025400e+01 5.576400e+01 5.881600e+01 6.282000e+01\n",
       "415 1.142636e+06 1.089572e+06 1.261091e+06 1.425876e+06 1.691210e+06\n",
       "416 2.649715e+03 3.133409e+03 3.682831e+03 4.336032e+03 5.107197e+03\n",
       "417 5.163100e+01 5.653200e+01 6.076500e+01 6.440600e+01 6.704600e+01\n",
       "418 6.740785e+06 7.407075e+06 8.403990e+06 9.657618e+06 1.121934e+07\n",
       "419 8.624421e+02 1.265047e+03 1.829765e+03 1.977557e+03 1.971742e+03\n",
       "420 3.698400e+01 3.984800e+01 4.417500e+01 4.911300e+01 5.292200e+01\n",
       "421 3.900000e+06 4.506497e+06 5.216550e+06 6.100407e+06 7.272406e+06\n",
       "422 1.777077e+03 1.773498e+03 1.588688e+03 1.408679e+03 1.213315e+03\n",
       "423 4.776800e+01 5.010700e+01 5.138600e+01 5.182100e+01 5.082100e+01\n",
       "424 4.995432e+06 5.861135e+06 6.642107e+06 7.636524e+06 9.216418e+06\n",
       "425 5.697951e+02 7.993622e+02 6.855877e+02 7.888550e+02 7.061573e+02\n",
       "426 5.399500e+01 5.563500e+01 5.767400e+01 6.036300e+01 6.235100e+01\n",
       "    1992         1997         2002         2007        \n",
       "1   1.631792e+07 2.222742e+07 2.526841e+07 3.188992e+07\n",
       "2   6.493414e+02 6.353414e+02 7.267341e+02 9.745803e+02\n",
       "3   4.167400e+01 4.176300e+01 4.212900e+01 4.382800e+01\n",
       "4   3.326498e+06 3.428038e+06 3.508512e+06 3.600523e+06\n",
       "5   2.497438e+03 3.193055e+03 4.604212e+03 5.937030e+03\n",
       "6   7.158100e+01 7.295000e+01 7.565100e+01 7.642300e+01\n",
       "7   2.629837e+07 2.907202e+07 3.128714e+07 3.333322e+07\n",
       "8   5.023217e+03 4.797295e+03 5.288040e+03 6.223367e+03\n",
       "9   6.774400e+01 6.915200e+01 7.099400e+01 7.230100e+01\n",
       "10  8.735988e+06 9.875024e+06 1.086611e+07 1.242048e+07\n",
       "11  2.627846e+03 2.277141e+03 2.773287e+03 4.797231e+03\n",
       "12  4.064700e+01 4.096300e+01 4.100300e+01 4.273100e+01\n",
       "13  3.395895e+07 3.620346e+07 3.833112e+07 4.030193e+07\n",
       "14  9.308419e+03 1.096728e+04 8.797641e+03 1.277938e+04\n",
       "15  7.186800e+01 7.327500e+01 7.434000e+01 7.532000e+01\n",
       "16  1.748198e+07 1.856524e+07 1.954679e+07 2.043418e+07\n",
       "17  2.342477e+04 2.699794e+04 3.068775e+04 3.443537e+04\n",
       "18  7.756000e+01 7.883000e+01 8.037000e+01 8.123500e+01\n",
       "19  7.914969e+06 8.069876e+06 8.148312e+06 8.199783e+06\n",
       "20  2.704202e+04 2.909592e+04 3.241761e+04 3.612649e+04\n",
       "21  7.604000e+01 7.751000e+01 7.898000e+01 7.982900e+01\n",
       "22  5.294910e+05 5.985610e+05 6.563970e+05 7.085730e+05\n",
       "23  1.903558e+04 2.029202e+04 2.340356e+04 2.979605e+04\n",
       "24  7.260100e+01 7.392500e+01 7.479500e+01 7.563500e+01\n",
       "25  1.137046e+08 1.233153e+08 1.356568e+08 1.504483e+08\n",
       "26  8.378102e+02 9.727700e+02 1.136390e+03 1.391254e+03\n",
       "27  5.601800e+01 5.941200e+01 6.201300e+01 6.406200e+01\n",
       "28  1.004562e+07 1.019979e+07 1.031197e+07 1.039223e+07\n",
       "29  2.557557e+04 2.756120e+04 3.048588e+04 3.369261e+04\n",
       "30  7.646000e+01 7.753000e+01 7.832000e+01 7.944100e+01\n",
       "... ...          ...          ...          ...         \n",
       "397 1.825219e+07 2.121025e+07 2.473987e+07 2.917040e+07\n",
       "398 6.441708e+02 8.165591e+02 9.277210e+02 1.056380e+03\n",
       "399 4.882500e+01 4.457800e+01 4.781300e+01 5.154200e+01\n",
       "400 5.786635e+07 5.880827e+07 5.991243e+07 6.077624e+07\n",
       "401 2.270509e+04 2.607453e+04 2.947900e+04 3.320326e+04\n",
       "402 7.642000e+01 7.721800e+01 7.847100e+01 7.942500e+01\n",
       "403 2.568942e+08 2.729118e+08 2.876755e+08 3.011399e+08\n",
       "404 3.200393e+04 3.576743e+04 3.909710e+04 4.295165e+04\n",
       "405 7.609000e+01 7.681000e+01 7.731000e+01 7.824200e+01\n",
       "406 3.149262e+06 3.262838e+06 3.363085e+06 3.447496e+06\n",
       "407 8.137005e+03 9.230241e+03 7.727002e+03 1.061146e+04\n",
       "408 7.275200e+01 7.422300e+01 7.530700e+01 7.638400e+01\n",
       "409 2.026556e+07 2.237440e+07 2.428767e+07 2.608466e+07\n",
       "410 1.073393e+04 1.016550e+04 8.605048e+03 1.141581e+04\n",
       "411 7.115000e+01 7.214600e+01 7.276600e+01 7.374700e+01\n",
       "412 6.994073e+07 7.604900e+07 8.090815e+07 8.526236e+07\n",
       "413 9.890231e+02 1.385897e+03 1.764457e+03 2.441576e+03\n",
       "414 6.766200e+01 7.067200e+01 7.301700e+01 7.424900e+01\n",
       "415 2.104779e+06 2.826046e+06 3.389578e+06 4.018332e+06\n",
       "416 6.017655e+03 7.110668e+03 4.515488e+03 3.025350e+03\n",
       "417 6.971800e+01 7.109600e+01 7.237000e+01 7.342200e+01\n",
       "418 1.336800e+07 1.582650e+07 1.870126e+07 2.221174e+07\n",
       "419 1.879497e+03 2.117485e+03 2.234821e+03 2.280770e+03\n",
       "420 5.559900e+01 5.802000e+01 6.030800e+01 6.269800e+01\n",
       "421 8.381163e+06 9.417789e+06 1.059581e+07 1.174604e+07\n",
       "422 1.210885e+03 1.071354e+03 1.071614e+03 1.271212e+03\n",
       "423 4.610000e+01 4.023800e+01 3.919300e+01 4.238400e+01\n",
       "424 1.070434e+07 1.140495e+07 1.192656e+07 1.231114e+07\n",
       "425 6.934208e+02 7.924500e+02 6.720386e+02 4.697093e+02\n",
       "426 6.037700e+01 4.680900e+01 3.998900e+01 4.348700e+01"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "con_world = dbConnect(\n",
    "    drv = Postgres(), \n",
    "    user = \"ssc_workshop\", \n",
    "    password = \"sql_for_ds\", \n",
    "    port = 5432, # this is the default port for postgres \n",
    "    dbname = \"world\", \n",
    "    host = \"ssc-2022-workshop.ct6ghoz7smhy.us-east-1.rds.amazonaws.com\")\n",
    "\n",
    "(gapminder_raw <- \n",
    "    tbl(con_world, 'gapminder') %>%\n",
    "    collect())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0612da8e-2df4-411f-868a-f79ce40c5381",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "bec8e8f1-efe1-46da-b90b-9de385252696",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 3.1 Writing into a database from R"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f2a735c-3119-4f7b-8b29-9114e70f63ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "?dbWriteTable"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}