pandas/06-renaming-and-combining.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[Pandas Micro-Course Home Page](https://www.kaggle.com/learn/pandas)**\n",
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Renaming and combining workbook\n",
    "\n",
    "## Introduction\n",
    "\n",
    "This is the workbook part of the \"Renaming and combining\" section of the Advanced Pandas tutorial. For the reference section, click [here](https://www.kaggle.com/residentmario/renaming-and-combining-reference).\n",
    "\n",
    "Renaming is covered in its own section in the [\"Essential Basic Functionality\"](https://pandas.pydata.org/pandas-docs/stable/basics.html#renaming-mapping-labels) section of the extensive official documentation. Combining is covered by the [\"Merge, join, concatenate\"](https://pandas.pydata.org/pandas-docs/stable/merging.html) section there."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Setup complete.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "reviews = pd.read_csv(\"../input/wine-reviews/winemag-data-130k-v2.csv\", index_col=0)\n",
    "\n",
    "from learntools.core import binder; binder.bind(globals())\n",
    "from learntools.pandas.renaming_and_combining import *\n",
    "print(\"Setup complete.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercises\n",
    "\n",
    "View the first several lines of your data by running the cell below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>description</th>\n",
       "      <th>designation</th>\n",
       "      <th>points</th>\n",
       "      <th>price</th>\n",
       "      <th>province</th>\n",
       "      <th>region_1</th>\n",
       "      <th>region_2</th>\n",
       "      <th>taster_name</th>\n",
       "      <th>taster_twitter_handle</th>\n",
       "      <th>title</th>\n",
       "      <th>variety</th>\n",
       "      <th>winery</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Italy</td>\n",
       "      <td>Aromas include tropical fruit, broom, brimston...</td>\n",
       "      <td>Vulkà Bianco</td>\n",
       "      <td>87</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sicily &amp; Sardinia</td>\n",
       "      <td>Etna</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kerin O’Keefe</td>\n",
       "      <td>@kerinokeefe</td>\n",
       "      <td>Nicosia 2013 Vulkà Bianco  (Etna)</td>\n",
       "      <td>White Blend</td>\n",
       "      <td>Nicosia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>This is ripe and fruity, a wine that is smooth...</td>\n",
       "      <td>Avidagos</td>\n",
       "      <td>87</td>\n",
       "      <td>15.0</td>\n",
       "      <td>Douro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Roger Voss</td>\n",
       "      <td>@vossroger</td>\n",
       "      <td>Quinta dos Avidagos 2011 Avidagos Red (Douro)</td>\n",
       "      <td>Portuguese Red</td>\n",
       "      <td>Quinta dos Avidagos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US</td>\n",
       "      <td>Tart and snappy, the flavors of lime flesh and...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87</td>\n",
       "      <td>14.0</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Paul Gregutt</td>\n",
       "      <td>@paulgwine</td>\n",
       "      <td>Rainstorm 2013 Pinot Gris (Willamette Valley)</td>\n",
       "      <td>Pinot Gris</td>\n",
       "      <td>Rainstorm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US</td>\n",
       "      <td>Pineapple rind, lemon pith and orange blossom ...</td>\n",
       "      <td>Reserve Late Harvest</td>\n",
       "      <td>87</td>\n",
       "      <td>13.0</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>Lake Michigan Shore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alexander Peartree</td>\n",
       "      <td>NaN</td>\n",
       "      <td>St. Julian 2013 Reserve Late Harvest Riesling ...</td>\n",
       "      <td>Riesling</td>\n",
       "      <td>St. Julian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US</td>\n",
       "      <td>Much like the regular bottling from 2012, this...</td>\n",
       "      <td>Vintner's Reserve Wild Child Block</td>\n",
       "      <td>87</td>\n",
       "      <td>65.0</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Paul Gregutt</td>\n",
       "      <td>@paulgwine</td>\n",
       "      <td>Sweet Cheeks 2012 Vintner's Reserve Wild Child...</td>\n",
       "      <td>Pinot Noir</td>\n",
       "      <td>Sweet Cheeks</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    country                                        description  \\\n",
       "0     Italy  Aromas include tropical fruit, broom, brimston...   \n",
       "1  Portugal  This is ripe and fruity, a wine that is smooth...   \n",
       "2        US  Tart and snappy, the flavors of lime flesh and...   \n",
       "3        US  Pineapple rind, lemon pith and orange blossom ...   \n",
       "4        US  Much like the regular bottling from 2012, this...   \n",
       "\n",
       "                          designation  points  price           province  \\\n",
       "0                        Vulkà Bianco      87    NaN  Sicily & Sardinia   \n",
       "1                            Avidagos      87   15.0              Douro   \n",
       "2                                 NaN      87   14.0             Oregon   \n",
       "3                Reserve Late Harvest      87   13.0           Michigan   \n",
       "4  Vintner's Reserve Wild Child Block      87   65.0             Oregon   \n",
       "\n",
       "              region_1           region_2         taster_name  \\\n",
       "0                 Etna                NaN       Kerin O’Keefe   \n",
       "1                  NaN                NaN          Roger Voss   \n",
       "2    Willamette Valley  Willamette Valley        Paul Gregutt   \n",
       "3  Lake Michigan Shore                NaN  Alexander Peartree   \n",
       "4    Willamette Valley  Willamette Valley        Paul Gregutt   \n",
       "\n",
       "  taster_twitter_handle                                              title  \\\n",
       "0          @kerinokeefe                  Nicosia 2013 Vulkà Bianco  (Etna)   \n",
       "1            @vossroger      Quinta dos Avidagos 2011 Avidagos Red (Douro)   \n",
       "2           @paulgwine       Rainstorm 2013 Pinot Gris (Willamette Valley)   \n",
       "3                   NaN  St. Julian 2013 Reserve Late Harvest Riesling ...   \n",
       "4           @paulgwine   Sweet Cheeks 2012 Vintner's Reserve Wild Child...   \n",
       "\n",
       "          variety               winery  \n",
       "0     White Blend              Nicosia  \n",
       "1  Portuguese Red  Quinta dos Avidagos  \n",
       "2      Pinot Gris            Rainstorm  \n",
       "3        Riesling           St. Julian  \n",
       "4      Pinot Noir         Sweet Cheeks  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "`region_1` and `region_2` are pretty uninformative names for locale columns in the dataset. Create a copy of `reviews` with these columns renamed to `region` and `locale`, respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"1_RenameCols\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>description</th>\n",
       "      <th>designation</th>\n",
       "      <th>points</th>\n",
       "      <th>price</th>\n",
       "      <th>province</th>\n",
       "      <th>region</th>\n",
       "      <th>locale</th>\n",
       "      <th>taster_name</th>\n",
       "      <th>taster_twitter_handle</th>\n",
       "      <th>title</th>\n",
       "      <th>variety</th>\n",
       "      <th>winery</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Italy</td>\n",
       "      <td>Aromas include tropical fruit, broom, brimston...</td>\n",
       "      <td>Vulkà Bianco</td>\n",
       "      <td>87</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sicily &amp; Sardinia</td>\n",
       "      <td>Etna</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kerin O’Keefe</td>\n",
       "      <td>@kerinokeefe</td>\n",
       "      <td>Nicosia 2013 Vulkà Bianco  (Etna)</td>\n",
       "      <td>White Blend</td>\n",
       "      <td>Nicosia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>This is ripe and fruity, a wine that is smooth...</td>\n",
       "      <td>Avidagos</td>\n",
       "      <td>87</td>\n",
       "      <td>15.0</td>\n",
       "      <td>Douro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Roger Voss</td>\n",
       "      <td>@vossroger</td>\n",
       "      <td>Quinta dos Avidagos 2011 Avidagos Red (Douro)</td>\n",
       "      <td>Portuguese Red</td>\n",
       "      <td>Quinta dos Avidagos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US</td>\n",
       "      <td>Tart and snappy, the flavors of lime flesh and...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87</td>\n",
       "      <td>14.0</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Paul Gregutt</td>\n",
       "      <td>@paulgwine</td>\n",
       "      <td>Rainstorm 2013 Pinot Gris (Willamette Valley)</td>\n",
       "      <td>Pinot Gris</td>\n",
       "      <td>Rainstorm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US</td>\n",
       "      <td>Pineapple rind, lemon pith and orange blossom ...</td>\n",
       "      <td>Reserve Late Harvest</td>\n",
       "      <td>87</td>\n",
       "      <td>13.0</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>Lake Michigan Shore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alexander Peartree</td>\n",
       "      <td>NaN</td>\n",
       "      <td>St. Julian 2013 Reserve Late Harvest Riesling ...</td>\n",
       "      <td>Riesling</td>\n",
       "      <td>St. Julian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US</td>\n",
       "      <td>Much like the regular bottling from 2012, this...</td>\n",
       "      <td>Vintner's Reserve Wild Child Block</td>\n",
       "      <td>87</td>\n",
       "      <td>65.0</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Paul Gregutt</td>\n",
       "      <td>@paulgwine</td>\n",
       "      <td>Sweet Cheeks 2012 Vintner's Reserve Wild Child...</td>\n",
       "      <td>Pinot Noir</td>\n",
       "      <td>Sweet Cheeks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129966</th>\n",
       "      <td>Germany</td>\n",
       "      <td>Notes of honeysuckle and cantaloupe sweeten th...</td>\n",
       "      <td>Brauneberger Juffer-Sonnenuhr Spätlese</td>\n",
       "      <td>90</td>\n",
       "      <td>28.0</td>\n",
       "      <td>Mosel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anna Lee C. Iijima</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dr. H. Thanisch (Erben Müller-Burggraef) 2013 ...</td>\n",
       "      <td>Riesling</td>\n",
       "      <td>Dr. H. Thanisch (Erben Müller-Burggraef)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129967</th>\n",
       "      <td>US</td>\n",
       "      <td>Citation is given as much as a decade of bottl...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>75.0</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>Oregon Other</td>\n",
       "      <td>Paul Gregutt</td>\n",
       "      <td>@paulgwine</td>\n",
       "      <td>Citation 2004 Pinot Noir (Oregon)</td>\n",
       "      <td>Pinot Noir</td>\n",
       "      <td>Citation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129968</th>\n",
       "      <td>France</td>\n",
       "      <td>Well-drained gravel soil gives this wine its c...</td>\n",
       "      <td>Kritt</td>\n",
       "      <td>90</td>\n",
       "      <td>30.0</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Roger Voss</td>\n",
       "      <td>@vossroger</td>\n",
       "      <td>Domaine Gresser 2013 Kritt Gewurztraminer (Als...</td>\n",
       "      <td>Gewürztraminer</td>\n",
       "      <td>Domaine Gresser</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129969</th>\n",
       "      <td>France</td>\n",
       "      <td>A dry style of Pinot Gris, this is crisp with ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>32.0</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Roger Voss</td>\n",
       "      <td>@vossroger</td>\n",
       "      <td>Domaine Marcel Deiss 2012 Pinot Gris (Alsace)</td>\n",
       "      <td>Pinot Gris</td>\n",
       "      <td>Domaine Marcel Deiss</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129970</th>\n",
       "      <td>France</td>\n",
       "      <td>Big, rich and off-dry, this is powered by inte...</td>\n",
       "      <td>Lieu-dit Harth Cuvée Caroline</td>\n",
       "      <td>90</td>\n",
       "      <td>21.0</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Roger Voss</td>\n",
       "      <td>@vossroger</td>\n",
       "      <td>Domaine Schoffit 2012 Lieu-dit Harth Cuvée Car...</td>\n",
       "      <td>Gewürztraminer</td>\n",
       "      <td>Domaine Schoffit</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>129971 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         country                                        description  \\\n",
       "0          Italy  Aromas include tropical fruit, broom, brimston...   \n",
       "1       Portugal  This is ripe and fruity, a wine that is smooth...   \n",
       "2             US  Tart and snappy, the flavors of lime flesh and...   \n",
       "3             US  Pineapple rind, lemon pith and orange blossom ...   \n",
       "4             US  Much like the regular bottling from 2012, this...   \n",
       "...          ...                                                ...   \n",
       "129966   Germany  Notes of honeysuckle and cantaloupe sweeten th...   \n",
       "129967        US  Citation is given as much as a decade of bottl...   \n",
       "129968    France  Well-drained gravel soil gives this wine its c...   \n",
       "129969    France  A dry style of Pinot Gris, this is crisp with ...   \n",
       "129970    France  Big, rich and off-dry, this is powered by inte...   \n",
       "\n",
       "                                   designation  points  price  \\\n",
       "0                                 Vulkà Bianco      87    NaN   \n",
       "1                                     Avidagos      87   15.0   \n",
       "2                                          NaN      87   14.0   \n",
       "3                         Reserve Late Harvest      87   13.0   \n",
       "4           Vintner's Reserve Wild Child Block      87   65.0   \n",
       "...                                        ...     ...    ...   \n",
       "129966  Brauneberger Juffer-Sonnenuhr Spätlese      90   28.0   \n",
       "129967                                     NaN      90   75.0   \n",
       "129968                                   Kritt      90   30.0   \n",
       "129969                                     NaN      90   32.0   \n",
       "129970           Lieu-dit Harth Cuvée Caroline      90   21.0   \n",
       "\n",
       "                 province               region             locale  \\\n",
       "0       Sicily & Sardinia                 Etna                NaN   \n",
       "1                   Douro                  NaN                NaN   \n",
       "2                  Oregon    Willamette Valley  Willamette Valley   \n",
       "3                Michigan  Lake Michigan Shore                NaN   \n",
       "4                  Oregon    Willamette Valley  Willamette Valley   \n",
       "...                   ...                  ...                ...   \n",
       "129966              Mosel                  NaN                NaN   \n",
       "129967             Oregon               Oregon       Oregon Other   \n",
       "129968             Alsace               Alsace                NaN   \n",
       "129969             Alsace               Alsace                NaN   \n",
       "129970             Alsace               Alsace                NaN   \n",
       "\n",
       "               taster_name taster_twitter_handle  \\\n",
       "0            Kerin O’Keefe          @kerinokeefe   \n",
       "1               Roger Voss            @vossroger   \n",
       "2             Paul Gregutt           @paulgwine    \n",
       "3       Alexander Peartree                   NaN   \n",
       "4             Paul Gregutt           @paulgwine    \n",
       "...                    ...                   ...   \n",
       "129966  Anna Lee C. Iijima                   NaN   \n",
       "129967        Paul Gregutt           @paulgwine    \n",
       "129968          Roger Voss            @vossroger   \n",
       "129969          Roger Voss            @vossroger   \n",
       "129970          Roger Voss            @vossroger   \n",
       "\n",
       "                                                    title         variety  \\\n",
       "0                       Nicosia 2013 Vulkà Bianco  (Etna)     White Blend   \n",
       "1           Quinta dos Avidagos 2011 Avidagos Red (Douro)  Portuguese Red   \n",
       "2           Rainstorm 2013 Pinot Gris (Willamette Valley)      Pinot Gris   \n",
       "3       St. Julian 2013 Reserve Late Harvest Riesling ...        Riesling   \n",
       "4       Sweet Cheeks 2012 Vintner's Reserve Wild Child...      Pinot Noir   \n",
       "...                                                   ...             ...   \n",
       "129966  Dr. H. Thanisch (Erben Müller-Burggraef) 2013 ...        Riesling   \n",
       "129967                  Citation 2004 Pinot Noir (Oregon)      Pinot Noir   \n",
       "129968  Domaine Gresser 2013 Kritt Gewurztraminer (Als...  Gewürztraminer   \n",
       "129969      Domaine Marcel Deiss 2012 Pinot Gris (Alsace)      Pinot Gris   \n",
       "129970  Domaine Schoffit 2012 Lieu-dit Harth Cuvée Car...  Gewürztraminer   \n",
       "\n",
       "                                          winery  \n",
       "0                                        Nicosia  \n",
       "1                            Quinta dos Avidagos  \n",
       "2                                      Rainstorm  \n",
       "3                                     St. Julian  \n",
       "4                                   Sweet Cheeks  \n",
       "...                                          ...  \n",
       "129966  Dr. H. Thanisch (Erben Müller-Burggraef)  \n",
       "129967                                  Citation  \n",
       "129968                           Domaine Gresser  \n",
       "129969                      Domaine Marcel Deiss  \n",
       "129970                          Domaine Schoffit  \n",
       "\n",
       "[129971 rows x 13 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Your code here\n",
    "renamed = reviews.rename(columns={'region_1': 'region', 'region_2': 'locale'})\n",
    "\n",
    "q1.check()\n",
    "renamed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q1.hint()\n",
    "#q1.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Set the index name in the dataset to `wines`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"2_RenameIndex\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>description</th>\n",
       "      <th>designation</th>\n",
       "      <th>points</th>\n",
       "      <th>price</th>\n",
       "      <th>province</th>\n",
       "      <th>region_1</th>\n",
       "      <th>region_2</th>\n",
       "      <th>taster_name</th>\n",
       "      <th>taster_twitter_handle</th>\n",
       "      <th>title</th>\n",
       "      <th>variety</th>\n",
       "      <th>winery</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wines</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Italy</td>\n",
       "      <td>Aromas include tropical fruit, broom, brimston...</td>\n",
       "      <td>Vulkà Bianco</td>\n",
       "      <td>87</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sicily &amp; Sardinia</td>\n",
       "      <td>Etna</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kerin O’Keefe</td>\n",
       "      <td>@kerinokeefe</td>\n",
       "      <td>Nicosia 2013 Vulkà Bianco  (Etna)</td>\n",
       "      <td>White Blend</td>\n",
       "      <td>Nicosia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>This is ripe and fruity, a wine that is smooth...</td>\n",
       "      <td>Avidagos</td>\n",
       "      <td>87</td>\n",
       "      <td>15.0</td>\n",
       "      <td>Douro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Roger Voss</td>\n",
       "      <td>@vossroger</td>\n",
       "      <td>Quinta dos Avidagos 2011 Avidagos Red (Douro)</td>\n",
       "      <td>Portuguese Red</td>\n",
       "      <td>Quinta dos Avidagos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US</td>\n",
       "      <td>Tart and snappy, the flavors of lime flesh and...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87</td>\n",
       "      <td>14.0</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Paul Gregutt</td>\n",
       "      <td>@paulgwine</td>\n",
       "      <td>Rainstorm 2013 Pinot Gris (Willamette Valley)</td>\n",
       "      <td>Pinot Gris</td>\n",
       "      <td>Rainstorm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US</td>\n",
       "      <td>Pineapple rind, lemon pith and orange blossom ...</td>\n",
       "      <td>Reserve Late Harvest</td>\n",
       "      <td>87</td>\n",
       "      <td>13.0</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>Lake Michigan Shore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alexander Peartree</td>\n",
       "      <td>NaN</td>\n",
       "      <td>St. Julian 2013 Reserve Late Harvest Riesling ...</td>\n",
       "      <td>Riesling</td>\n",
       "      <td>St. Julian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US</td>\n",
       "      <td>Much like the regular bottling from 2012, this...</td>\n",
       "      <td>Vintner's Reserve Wild Child Block</td>\n",
       "      <td>87</td>\n",
       "      <td>65.0</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Willamette Valley</td>\n",
       "      <td>Paul Gregutt</td>\n",
       "      <td>@paulgwine</td>\n",
       "      <td>Sweet Cheeks 2012 Vintner's Reserve Wild Child...</td>\n",
       "      <td>Pinot Noir</td>\n",
       "      <td>Sweet Cheeks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129966</th>\n",
       "      <td>Germany</td>\n",
       "      <td>Notes of honeysuckle and cantaloupe sweeten th...</td>\n",
       "      <td>Brauneberger Juffer-Sonnenuhr Spätlese</td>\n",
       "      <td>90</td>\n",
       "      <td>28.0</td>\n",
       "      <td>Mosel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anna Lee C. Iijima</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dr. H. Thanisch (Erben Müller-Burggraef) 2013 ...</td>\n",
       "      <td>Riesling</td>\n",
       "      <td>Dr. H. Thanisch (Erben Müller-Burggraef)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129967</th>\n",
       "      <td>US</td>\n",
       "      <td>Citation is given as much as a decade of bottl...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>75.0</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>Oregon Other</td>\n",
       "      <td>Paul Gregutt</td>\n",
       "      <td>@paulgwine</td>\n",
       "      <td>Citation 2004 Pinot Noir (Oregon)</td>\n",
       "      <td>Pinot Noir</td>\n",
       "      <td>Citation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129968</th>\n",
       "      <td>France</td>\n",
       "      <td>Well-drained gravel soil gives this wine its c...</td>\n",
       "      <td>Kritt</td>\n",
       "      <td>90</td>\n",
       "      <td>30.0</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Roger Voss</td>\n",
       "      <td>@vossroger</td>\n",
       "      <td>Domaine Gresser 2013 Kritt Gewurztraminer (Als...</td>\n",
       "      <td>Gewürztraminer</td>\n",
       "      <td>Domaine Gresser</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129969</th>\n",
       "      <td>France</td>\n",
       "      <td>A dry style of Pinot Gris, this is crisp with ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>32.0</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Roger Voss</td>\n",
       "      <td>@vossroger</td>\n",
       "      <td>Domaine Marcel Deiss 2012 Pinot Gris (Alsace)</td>\n",
       "      <td>Pinot Gris</td>\n",
       "      <td>Domaine Marcel Deiss</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129970</th>\n",
       "      <td>France</td>\n",
       "      <td>Big, rich and off-dry, this is powered by inte...</td>\n",
       "      <td>Lieu-dit Harth Cuvée Caroline</td>\n",
       "      <td>90</td>\n",
       "      <td>21.0</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>Alsace</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Roger Voss</td>\n",
       "      <td>@vossroger</td>\n",
       "      <td>Domaine Schoffit 2012 Lieu-dit Harth Cuvée Car...</td>\n",
       "      <td>Gewürztraminer</td>\n",
       "      <td>Domaine Schoffit</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>129971 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         country                                        description  \\\n",
       "wines                                                                 \n",
       "0          Italy  Aromas include tropical fruit, broom, brimston...   \n",
       "1       Portugal  This is ripe and fruity, a wine that is smooth...   \n",
       "2             US  Tart and snappy, the flavors of lime flesh and...   \n",
       "3             US  Pineapple rind, lemon pith and orange blossom ...   \n",
       "4             US  Much like the regular bottling from 2012, this...   \n",
       "...          ...                                                ...   \n",
       "129966   Germany  Notes of honeysuckle and cantaloupe sweeten th...   \n",
       "129967        US  Citation is given as much as a decade of bottl...   \n",
       "129968    France  Well-drained gravel soil gives this wine its c...   \n",
       "129969    France  A dry style of Pinot Gris, this is crisp with ...   \n",
       "129970    France  Big, rich and off-dry, this is powered by inte...   \n",
       "\n",
       "                                   designation  points  price  \\\n",
       "wines                                                           \n",
       "0                                 Vulkà Bianco      87    NaN   \n",
       "1                                     Avidagos      87   15.0   \n",
       "2                                          NaN      87   14.0   \n",
       "3                         Reserve Late Harvest      87   13.0   \n",
       "4           Vintner's Reserve Wild Child Block      87   65.0   \n",
       "...                                        ...     ...    ...   \n",
       "129966  Brauneberger Juffer-Sonnenuhr Spätlese      90   28.0   \n",
       "129967                                     NaN      90   75.0   \n",
       "129968                                   Kritt      90   30.0   \n",
       "129969                                     NaN      90   32.0   \n",
       "129970           Lieu-dit Harth Cuvée Caroline      90   21.0   \n",
       "\n",
       "                 province             region_1           region_2  \\\n",
       "wines                                                               \n",
       "0       Sicily & Sardinia                 Etna                NaN   \n",
       "1                   Douro                  NaN                NaN   \n",
       "2                  Oregon    Willamette Valley  Willamette Valley   \n",
       "3                Michigan  Lake Michigan Shore                NaN   \n",
       "4                  Oregon    Willamette Valley  Willamette Valley   \n",
       "...                   ...                  ...                ...   \n",
       "129966              Mosel                  NaN                NaN   \n",
       "129967             Oregon               Oregon       Oregon Other   \n",
       "129968             Alsace               Alsace                NaN   \n",
       "129969             Alsace               Alsace                NaN   \n",
       "129970             Alsace               Alsace                NaN   \n",
       "\n",
       "               taster_name taster_twitter_handle  \\\n",
       "wines                                              \n",
       "0            Kerin O’Keefe          @kerinokeefe   \n",
       "1               Roger Voss            @vossroger   \n",
       "2             Paul Gregutt           @paulgwine    \n",
       "3       Alexander Peartree                   NaN   \n",
       "4             Paul Gregutt           @paulgwine    \n",
       "...                    ...                   ...   \n",
       "129966  Anna Lee C. Iijima                   NaN   \n",
       "129967        Paul Gregutt           @paulgwine    \n",
       "129968          Roger Voss            @vossroger   \n",
       "129969          Roger Voss            @vossroger   \n",
       "129970          Roger Voss            @vossroger   \n",
       "\n",
       "                                                    title         variety  \\\n",
       "wines                                                                       \n",
       "0                       Nicosia 2013 Vulkà Bianco  (Etna)     White Blend   \n",
       "1           Quinta dos Avidagos 2011 Avidagos Red (Douro)  Portuguese Red   \n",
       "2           Rainstorm 2013 Pinot Gris (Willamette Valley)      Pinot Gris   \n",
       "3       St. Julian 2013 Reserve Late Harvest Riesling ...        Riesling   \n",
       "4       Sweet Cheeks 2012 Vintner's Reserve Wild Child...      Pinot Noir   \n",
       "...                                                   ...             ...   \n",
       "129966  Dr. H. Thanisch (Erben Müller-Burggraef) 2013 ...        Riesling   \n",
       "129967                  Citation 2004 Pinot Noir (Oregon)      Pinot Noir   \n",
       "129968  Domaine Gresser 2013 Kritt Gewurztraminer (Als...  Gewürztraminer   \n",
       "129969      Domaine Marcel Deiss 2012 Pinot Gris (Alsace)      Pinot Gris   \n",
       "129970  Domaine Schoffit 2012 Lieu-dit Harth Cuvée Car...  Gewürztraminer   \n",
       "\n",
       "                                          winery  \n",
       "wines                                             \n",
       "0                                        Nicosia  \n",
       "1                            Quinta dos Avidagos  \n",
       "2                                      Rainstorm  \n",
       "3                                     St. Julian  \n",
       "4                                   Sweet Cheeks  \n",
       "...                                          ...  \n",
       "129966  Dr. H. Thanisch (Erben Müller-Burggraef)  \n",
       "129967                                  Citation  \n",
       "129968                           Domaine Gresser  \n",
       "129969                      Domaine Marcel Deiss  \n",
       "129970                          Domaine Schoffit  \n",
       "\n",
       "[129971 rows x 13 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reindexed = reviews.rename_axis('wines', axis='rows')\n",
    "\n",
    "q2.check()\n",
    "reindexed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q2.hint()\n",
    "#q2.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "The [Things on Reddit](https://www.kaggle.com/residentmario/things-on-reddit/data) dataset includes product links from a selection of top-ranked forums (\"subreddits\") on reddit.com. Run the cell below to load a dataframe of products mentioned on the */r/gaming* subreddit and another dataframe for products mentioned on the *r//movies* subreddit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "gaming_products = pd.read_csv(\"../input/things-on-reddit/top-things/top-things/reddits/g/gaming.csv\")\n",
    "gaming_products['subreddit'] = \"r/gaming\"\n",
    "movie_products = pd.read_csv(\"../input/things-on-reddit/top-things/top-things/reddits/m/movies.csv\")\n",
    "movie_products['subreddit'] = \"r/movies\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a `DataFrame` of products mentioned on *either* subreddit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"3_ConcatReddit\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>category</th>\n",
       "      <th>amazon_link</th>\n",
       "      <th>total_mentions</th>\n",
       "      <th>subreddit_mentions</th>\n",
       "      <th>subreddit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOOMco Halo Covenant Needler Blaster</td>\n",
       "      <td>Toys &amp; Games</td>\n",
       "      <td>https://www.amazon.com/BOOMco-Halo-Covenant-Ne...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>r/gaming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Raspberry PI 3 Model B 1.2GHz 64-bit quad-core...</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>https://www.amazon.com/Raspberry-Model-A1-2GHz...</td>\n",
       "      <td>19.0</td>\n",
       "      <td>3</td>\n",
       "      <td>r/gaming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CanaKit 5V 2.5A Raspberry Pi 3 Power Supply / ...</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>https://www.amazon.com/CanaKit-Raspberry-Suppl...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3</td>\n",
       "      <td>r/gaming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Panasonic K-KJ17MCA4BA Advanced Individual Cel...</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>https://www.amazon.com/Panasonic-Advanced-Indi...</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2</td>\n",
       "      <td>r/gaming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mayflash GameCube Controller Adapter for Wii U...</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>https://www.amazon.com/GameCube-Controller-Ada...</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2</td>\n",
       "      <td>r/gaming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>Welcome to Night Vale CD: A Novel</td>\n",
       "      <td>Books</td>\n",
       "      <td>https://www.amazon.com/Welcome-Night-Vale-CD-N...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>r/movies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>Ran (StudioCanal Collection) [Blu-ray]</td>\n",
       "      <td>Movies &amp; TV</td>\n",
       "      <td>https://www.amazon.com/StudioCanal-Collection-...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>r/movies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>The Art of John Alvin</td>\n",
       "      <td>Books</td>\n",
       "      <td>https://www.amazon.com/Art-John-Alvin-Andrea/d...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>r/movies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301</th>\n",
       "      <td>Apocalypto [Blu-ray]</td>\n",
       "      <td>Movies &amp; TV</td>\n",
       "      <td>https://www.amazon.com/Apocalypto-Blu-ray-Rudy...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>r/movies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302</th>\n",
       "      <td>Cinelinx: A Card Game for People Who Love Movi...</td>\n",
       "      <td>Toys &amp; Games</td>\n",
       "      <td>https://www.amazon.com/Cinelinx-Card-Game-Peop...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>r/movies</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>796 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  name      category  \\\n",
       "0                 BOOMco Halo Covenant Needler Blaster  Toys & Games   \n",
       "1    Raspberry PI 3 Model B 1.2GHz 64-bit quad-core...   Electronics   \n",
       "2    CanaKit 5V 2.5A Raspberry Pi 3 Power Supply / ...   Electronics   \n",
       "3    Panasonic K-KJ17MCA4BA Advanced Individual Cel...   Electronics   \n",
       "4    Mayflash GameCube Controller Adapter for Wii U...   Electronics   \n",
       "..                                                 ...           ...   \n",
       "298                  Welcome to Night Vale CD: A Novel         Books   \n",
       "299             Ran (StudioCanal Collection) [Blu-ray]   Movies & TV   \n",
       "300                              The Art of John Alvin         Books   \n",
       "301                               Apocalypto [Blu-ray]   Movies & TV   \n",
       "302  Cinelinx: A Card Game for People Who Love Movi...  Toys & Games   \n",
       "\n",
       "                                           amazon_link  total_mentions  \\\n",
       "0    https://www.amazon.com/BOOMco-Halo-Covenant-Ne...             4.0   \n",
       "1    https://www.amazon.com/Raspberry-Model-A1-2GHz...            19.0   \n",
       "2    https://www.amazon.com/CanaKit-Raspberry-Suppl...             7.0   \n",
       "3    https://www.amazon.com/Panasonic-Advanced-Indi...            29.0   \n",
       "4    https://www.amazon.com/GameCube-Controller-Ada...            24.0   \n",
       "..                                                 ...             ...   \n",
       "298  https://www.amazon.com/Welcome-Night-Vale-CD-N...             1.0   \n",
       "299  https://www.amazon.com/StudioCanal-Collection-...             1.0   \n",
       "300  https://www.amazon.com/Art-John-Alvin-Andrea/d...             1.0   \n",
       "301  https://www.amazon.com/Apocalypto-Blu-ray-Rudy...             1.0   \n",
       "302  https://www.amazon.com/Cinelinx-Card-Game-Peop...             1.0   \n",
       "\n",
       "     subreddit_mentions subreddit  \n",
       "0                     4  r/gaming  \n",
       "1                     3  r/gaming  \n",
       "2                     3  r/gaming  \n",
       "3                     2  r/gaming  \n",
       "4                     2  r/gaming  \n",
       "..                  ...       ...  \n",
       "298                   1  r/movies  \n",
       "299                   1  r/movies  \n",
       "300                   1  r/movies  \n",
       "301                   1  r/movies  \n",
       "302                   1  r/movies  \n",
       "\n",
       "[796 rows x 6 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_products = pd.concat([gaming_products, movie_products])\n",
    "\n",
    "q3.check()\n",
    "combined_products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q3.hint()\n",
    "#q3.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "The [Powerlifting Database](https://www.kaggle.com/open-powerlifting/powerlifting-database) dataset on Kaggle includes one CSV table for powerlifting meets and a separate one for powerlifting competitors. Run the cell below to load these datasets into dataframes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "powerlifting_meets = pd.read_csv(\"../input/powerlifting-database/meets.csv\")\n",
    "powerlifting_competitors = pd.read_csv(\"../input/powerlifting-database/openpowerlifting.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Both tables include references to a `MeetID`, a unique key for each meet (competition) included in the database. Using this, generate a dataset combining the two tables into one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"4_JoinLifting\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MeetPath</th>\n",
       "      <th>Federation</th>\n",
       "      <th>Date</th>\n",
       "      <th>MeetCountry</th>\n",
       "      <th>MeetState</th>\n",
       "      <th>MeetTown</th>\n",
       "      <th>MeetName</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Equipment</th>\n",
       "      <th>...</th>\n",
       "      <th>WeightClassKg</th>\n",
       "      <th>Squat4Kg</th>\n",
       "      <th>BestSquatKg</th>\n",
       "      <th>Bench4Kg</th>\n",
       "      <th>BestBenchKg</th>\n",
       "      <th>Deadlift4Kg</th>\n",
       "      <th>BestDeadliftKg</th>\n",
       "      <th>TotalKg</th>\n",
       "      <th>Place</th>\n",
       "      <th>Wilks</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeetID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>365strong/1601</td>\n",
       "      <td>365Strong</td>\n",
       "      <td>2016-10-29</td>\n",
       "      <td>USA</td>\n",
       "      <td>NC</td>\n",
       "      <td>Charlotte</td>\n",
       "      <td>2016 Junior &amp; Senior National Powerlifting Cha...</td>\n",
       "      <td>Angie Belk Terry</td>\n",
       "      <td>F</td>\n",
       "      <td>Wraps</td>\n",
       "      <td>...</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.31</td>\n",
       "      <td>138.35</td>\n",
       "      <td>1</td>\n",
       "      <td>155.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>365strong/1601</td>\n",
       "      <td>365Strong</td>\n",
       "      <td>2016-10-29</td>\n",
       "      <td>USA</td>\n",
       "      <td>NC</td>\n",
       "      <td>Charlotte</td>\n",
       "      <td>2016 Junior &amp; Senior National Powerlifting Cha...</td>\n",
       "      <td>Dawn Bogart</td>\n",
       "      <td>F</td>\n",
       "      <td>Single-ply</td>\n",
       "      <td>...</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>142.88</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>163.29</td>\n",
       "      <td>401.42</td>\n",
       "      <td>1</td>\n",
       "      <td>456.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>365strong/1601</td>\n",
       "      <td>365Strong</td>\n",
       "      <td>2016-10-29</td>\n",
       "      <td>USA</td>\n",
       "      <td>NC</td>\n",
       "      <td>Charlotte</td>\n",
       "      <td>2016 Junior &amp; Senior National Powerlifting Cha...</td>\n",
       "      <td>Dawn Bogart</td>\n",
       "      <td>F</td>\n",
       "      <td>Single-ply</td>\n",
       "      <td>...</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>142.88</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>163.29</td>\n",
       "      <td>401.42</td>\n",
       "      <td>1</td>\n",
       "      <td>456.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>365strong/1601</td>\n",
       "      <td>365Strong</td>\n",
       "      <td>2016-10-29</td>\n",
       "      <td>USA</td>\n",
       "      <td>NC</td>\n",
       "      <td>Charlotte</td>\n",
       "      <td>2016 Junior &amp; Senior National Powerlifting Cha...</td>\n",
       "      <td>Dawn Bogart</td>\n",
       "      <td>F</td>\n",
       "      <td>Raw</td>\n",
       "      <td>...</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95.25</td>\n",
       "      <td>1</td>\n",
       "      <td>108.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>365strong/1601</td>\n",
       "      <td>365Strong</td>\n",
       "      <td>2016-10-29</td>\n",
       "      <td>USA</td>\n",
       "      <td>NC</td>\n",
       "      <td>Charlotte</td>\n",
       "      <td>2016 Junior &amp; Senior National Powerlifting Cha...</td>\n",
       "      <td>Destiny Dula</td>\n",
       "      <td>F</td>\n",
       "      <td>Raw</td>\n",
       "      <td>...</td>\n",
       "      <td>67.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90.72</td>\n",
       "      <td>122.47</td>\n",
       "      <td>1</td>\n",
       "      <td>130.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8481</th>\n",
       "      <td>xpc/2017-finals</td>\n",
       "      <td>XPC</td>\n",
       "      <td>2017-03-03</td>\n",
       "      <td>USA</td>\n",
       "      <td>OH</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>2017 XPC Finals</td>\n",
       "      <td>William Barabas</td>\n",
       "      <td>M</td>\n",
       "      <td>Multi-ply</td>\n",
       "      <td>...</td>\n",
       "      <td>125</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>347.50</td>\n",
       "      <td>347.50</td>\n",
       "      <td>2</td>\n",
       "      <td>202.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8481</th>\n",
       "      <td>xpc/2017-finals</td>\n",
       "      <td>XPC</td>\n",
       "      <td>2017-03-03</td>\n",
       "      <td>USA</td>\n",
       "      <td>OH</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>2017 XPC Finals</td>\n",
       "      <td>Justin Zottl</td>\n",
       "      <td>M</td>\n",
       "      <td>Multi-ply</td>\n",
       "      <td>...</td>\n",
       "      <td>125</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>322.50</td>\n",
       "      <td>322.50</td>\n",
       "      <td>3</td>\n",
       "      <td>185.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8481</th>\n",
       "      <td>xpc/2017-finals</td>\n",
       "      <td>XPC</td>\n",
       "      <td>2017-03-03</td>\n",
       "      <td>USA</td>\n",
       "      <td>OH</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>2017 XPC Finals</td>\n",
       "      <td>Jake Anderson</td>\n",
       "      <td>M</td>\n",
       "      <td>Multi-ply</td>\n",
       "      <td>...</td>\n",
       "      <td>125</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>367.50</td>\n",
       "      <td>367.50</td>\n",
       "      <td>1</td>\n",
       "      <td>211.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8481</th>\n",
       "      <td>xpc/2017-finals</td>\n",
       "      <td>XPC</td>\n",
       "      <td>2017-03-03</td>\n",
       "      <td>USA</td>\n",
       "      <td>OH</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>2017 XPC Finals</td>\n",
       "      <td>Jeff Bumanglag</td>\n",
       "      <td>M</td>\n",
       "      <td>Multi-ply</td>\n",
       "      <td>...</td>\n",
       "      <td>140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>320.00</td>\n",
       "      <td>320.00</td>\n",
       "      <td>3</td>\n",
       "      <td>181.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8481</th>\n",
       "      <td>xpc/2017-finals</td>\n",
       "      <td>XPC</td>\n",
       "      <td>2017-03-03</td>\n",
       "      <td>USA</td>\n",
       "      <td>OH</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>2017 XPC Finals</td>\n",
       "      <td>Shane Hammock</td>\n",
       "      <td>M</td>\n",
       "      <td>Multi-ply</td>\n",
       "      <td>...</td>\n",
       "      <td>140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>362.50</td>\n",
       "      <td>362.50</td>\n",
       "      <td>2</td>\n",
       "      <td>205.18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>386414 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               MeetPath Federation        Date MeetCountry MeetState  \\\n",
       "MeetID                                                                 \n",
       "0        365strong/1601  365Strong  2016-10-29         USA        NC   \n",
       "0        365strong/1601  365Strong  2016-10-29         USA        NC   \n",
       "0        365strong/1601  365Strong  2016-10-29         USA        NC   \n",
       "0        365strong/1601  365Strong  2016-10-29         USA        NC   \n",
       "0        365strong/1601  365Strong  2016-10-29         USA        NC   \n",
       "...                 ...        ...         ...         ...       ...   \n",
       "8481    xpc/2017-finals        XPC  2017-03-03         USA        OH   \n",
       "8481    xpc/2017-finals        XPC  2017-03-03         USA        OH   \n",
       "8481    xpc/2017-finals        XPC  2017-03-03         USA        OH   \n",
       "8481    xpc/2017-finals        XPC  2017-03-03         USA        OH   \n",
       "8481    xpc/2017-finals        XPC  2017-03-03         USA        OH   \n",
       "\n",
       "         MeetTown                                           MeetName  \\\n",
       "MeetID                                                                 \n",
       "0       Charlotte  2016 Junior & Senior National Powerlifting Cha...   \n",
       "0       Charlotte  2016 Junior & Senior National Powerlifting Cha...   \n",
       "0       Charlotte  2016 Junior & Senior National Powerlifting Cha...   \n",
       "0       Charlotte  2016 Junior & Senior National Powerlifting Cha...   \n",
       "0       Charlotte  2016 Junior & Senior National Powerlifting Cha...   \n",
       "...           ...                                                ...   \n",
       "8481     Columbus                                    2017 XPC Finals   \n",
       "8481     Columbus                                    2017 XPC Finals   \n",
       "8481     Columbus                                    2017 XPC Finals   \n",
       "8481     Columbus                                    2017 XPC Finals   \n",
       "8481     Columbus                                    2017 XPC Finals   \n",
       "\n",
       "                    Name Sex   Equipment  ...  WeightClassKg Squat4Kg  \\\n",
       "MeetID                                    ...                           \n",
       "0       Angie Belk Terry   F       Wraps  ...             60      NaN   \n",
       "0            Dawn Bogart   F  Single-ply  ...             60      NaN   \n",
       "0            Dawn Bogart   F  Single-ply  ...             60      NaN   \n",
       "0            Dawn Bogart   F         Raw  ...             60      NaN   \n",
       "0           Destiny Dula   F         Raw  ...           67.5      NaN   \n",
       "...                  ...  ..         ...  ...            ...      ...   \n",
       "8481     William Barabas   M   Multi-ply  ...            125      NaN   \n",
       "8481        Justin Zottl   M   Multi-ply  ...            125      NaN   \n",
       "8481       Jake Anderson   M   Multi-ply  ...            125      NaN   \n",
       "8481      Jeff Bumanglag   M   Multi-ply  ...            140      NaN   \n",
       "8481       Shane Hammock   M   Multi-ply  ...            140      NaN   \n",
       "\n",
       "        BestSquatKg Bench4Kg  BestBenchKg  Deadlift4Kg  BestDeadliftKg  \\\n",
       "MeetID                                                                   \n",
       "0             47.63      NaN        20.41          NaN           70.31   \n",
       "0            142.88      NaN        95.25          NaN          163.29   \n",
       "0            142.88      NaN        95.25          NaN          163.29   \n",
       "0               NaN      NaN        95.25          NaN             NaN   \n",
       "0               NaN      NaN        31.75          NaN           90.72   \n",
       "...             ...      ...          ...          ...             ...   \n",
       "8481            NaN      NaN          NaN          NaN          347.50   \n",
       "8481            NaN      NaN          NaN          NaN          322.50   \n",
       "8481            NaN      NaN          NaN          NaN          367.50   \n",
       "8481            NaN      NaN          NaN          NaN          320.00   \n",
       "8481            NaN      NaN          NaN          NaN          362.50   \n",
       "\n",
       "        TotalKg  Place   Wilks  \n",
       "MeetID                          \n",
       "0        138.35      1  155.05  \n",
       "0        401.42      1  456.38  \n",
       "0        401.42      1  456.38  \n",
       "0         95.25      1  108.29  \n",
       "0        122.47      1  130.47  \n",
       "...         ...    ...     ...  \n",
       "8481     347.50      2  202.60  \n",
       "8481     322.50      3  185.77  \n",
       "8481     367.50      1  211.17  \n",
       "8481     320.00      3  181.85  \n",
       "8481     362.50      2  205.18  \n",
       "\n",
       "[386414 rows x 23 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "powerlifting_combined = powerlifting_meets.set_index(\"MeetID\").join(powerlifting_competitors.set_index(\"MeetID\"))\n",
    "\n",
    "q4.check()\n",
    "powerlifting_combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q4.hint()\n",
    "#q4.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Congratulations\n",
    "\n",
    "You've finished the Pandas track.  Many data scientist feel efficiency with Pandas is the most useful and practical skill they have, because it allows you to progress quickly in any project you have.\n",
    "\n",
    "You can take advantage of your Pandas skills by entering a [Kaggle Competition](https://www.kaggle.com/competitions) or answering a question you find interesting using [Kaggle Datasets](https://www.kaggle.com/datasets)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "**[Pandas Micro-Course Home Page](https://www.kaggle.com/learn/pandas)**\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}