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 reference\n",
    "\n",
    "## Introduction\n",
    "\n",
    "This is the reference part of the \"Renaming and combining\" section of the Advanced Pandas tutorial. \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": {
    "collapsed": true
   },
   "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>...</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>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          ...                         winery\n",
       "0          Italy          ...                        Nicosia\n",
       "1       Portugal          ...            Quinta dos Avidagos\n",
       "...          ...          ...                            ...\n",
       "129969    France          ...           Domaine Marcel Deiss\n",
       "129970    France          ...               Domaine Schoffit\n",
       "\n",
       "[129971 rows x 13 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('max_rows', 5)\n",
    "reviews = pd.read_csv(\"../input/wine-reviews/winemag-data-130k-v2.csv\", index_col=0)\n",
    "reviews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Renaming\n",
    "\n",
    "Oftentimes data will come to us with column names, index names, or other naming conventions that we are not satisfied with. In that case, we may use `pandas` renaming utility functions to change the names of the offending entries to something better.\n",
    "\n",
    "The first function we'll introduce here is `rename`, which lets you rename index names and/or column names. For example, to change the `points` column in our dataset to `score`, we would do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "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>score</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>...</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>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          ...                         winery\n",
       "0          Italy          ...                        Nicosia\n",
       "1       Portugal          ...            Quinta dos Avidagos\n",
       "...          ...          ...                            ...\n",
       "129969    France          ...           Domaine Marcel Deiss\n",
       "129970    France          ...               Domaine Schoffit\n",
       "\n",
       "[129971 rows x 13 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.rename(columns={'points': 'score'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`rename` lets you rename index _or_ column values by specifying a `index` or `column` keyword parameter, respectively. It supports a variety of input formats, but I usually find a Python `dict` to be the most convenient one. Here is an example using it to rename some elements on the index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "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>firstEntry</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>secondEntry</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>...</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>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          ...                         winery\n",
       "firstEntry      Italy          ...                        Nicosia\n",
       "secondEntry  Portugal          ...            Quinta dos Avidagos\n",
       "...               ...          ...                            ...\n",
       "129969         France          ...           Domaine Marcel Deiss\n",
       "129970         France          ...               Domaine Schoffit\n",
       "\n",
       "[129971 rows x 13 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.rename(index={0: 'firstEntry', 1: 'secondEntry'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll probably rename columns very often, but rename index values very rarely.  For that, `set_index` is usually more convenient.\n",
    "\n",
    "Both the row index and the column index can have their own `name` attribute. The complimentary `rename_axis` method may be used to change these names. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "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>fields</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>...</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>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": [
       "fields   country          ...                         winery\n",
       "wines                     ...                               \n",
       "0          Italy          ...                        Nicosia\n",
       "1       Portugal          ...            Quinta dos Avidagos\n",
       "...          ...          ...                            ...\n",
       "129969    France          ...           Domaine Marcel Deiss\n",
       "129970    France          ...               Domaine Schoffit\n",
       "\n",
       "[129971 rows x 13 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.rename_axis(\"wines\", axis='rows').rename_axis(\"fields\", axis='columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When performing operations on a dataset we will sometimes need to combine different `DataFrame` and/or `Series` in non-trivial ways. `pandas` has three core methods for doing this. In order of increasing complexity, these are `concat`, `join`, and `merge`. Most of what `merge` can do can also be done more simply with `join`, so I will omit it and focus on the first two functions here.\n",
    "\n",
    "The simplest combining method is `concat`. This function works just like the `list.concat` method in core Python: given a list of elements, it will smush those elements together along an axis.\n",
    "\n",
    "This is useful when we have data in different `DataFrame` or `Series` objects but having the same fields (columns). One example: the [YouTube Videos dataset](https://www.kaggle.com/datasnaek/youtube-new), which splits the data up based on country of origin (e.g. Canada and the UK, in this example). If we want to study multiple countries simultaneously, we can use `concat` to smush them together:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "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>video_id</th>\n",
       "      <th>trending_date</th>\n",
       "      <th>title</th>\n",
       "      <th>channel_title</th>\n",
       "      <th>category_id</th>\n",
       "      <th>publish_time</th>\n",
       "      <th>tags</th>\n",
       "      <th>views</th>\n",
       "      <th>likes</th>\n",
       "      <th>dislikes</th>\n",
       "      <th>comment_count</th>\n",
       "      <th>thumbnail_link</th>\n",
       "      <th>comments_disabled</th>\n",
       "      <th>ratings_disabled</th>\n",
       "      <th>video_error_or_removed</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>n1WpP7iowLc</td>\n",
       "      <td>17.14.11</td>\n",
       "      <td>Eminem - Walk On Water (Audio) ft. Beyoncé</td>\n",
       "      <td>EminemVEVO</td>\n",
       "      <td>10</td>\n",
       "      <td>2017-11-10T17:00:03.000Z</td>\n",
       "      <td>Eminem|\"Walk\"|\"On\"|\"Water\"|\"Aftermath/Shady/In...</td>\n",
       "      <td>17158579</td>\n",
       "      <td>787425</td>\n",
       "      <td>43420</td>\n",
       "      <td>125882</td>\n",
       "      <td>https://i.ytimg.com/vi/n1WpP7iowLc/default.jpg</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Eminem's new track Walk on Water ft. Beyoncé i...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0dBIkQ4Mz1M</td>\n",
       "      <td>17.14.11</td>\n",
       "      <td>PLUSH - Bad Unboxing Fan Mail</td>\n",
       "      <td>iDubbbzTV</td>\n",
       "      <td>23</td>\n",
       "      <td>2017-11-13T17:00:00.000Z</td>\n",
       "      <td>plush|\"bad unboxing\"|\"unboxing\"|\"fan mail\"|\"id...</td>\n",
       "      <td>1014651</td>\n",
       "      <td>127794</td>\n",
       "      <td>1688</td>\n",
       "      <td>13030</td>\n",
       "      <td>https://i.ytimg.com/vi/0dBIkQ4Mz1M/default.jpg</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>STill got a lot of packages. Probably will las...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38914</th>\n",
       "      <td>-DRsfNObKIQ</td>\n",
       "      <td>18.14.06</td>\n",
       "      <td>Eleni Foureira - Fuego - Cyprus - LIVE - First...</td>\n",
       "      <td>Eurovision Song Contest</td>\n",
       "      <td>24</td>\n",
       "      <td>2018-05-08T20:32:32.000Z</td>\n",
       "      <td>Eurovision Song Contest|\"2018\"|\"Lisbon\"|\"Cypru...</td>\n",
       "      <td>14317515</td>\n",
       "      <td>151870</td>\n",
       "      <td>45875</td>\n",
       "      <td>26766</td>\n",
       "      <td>https://i.ytimg.com/vi/-DRsfNObKIQ/default.jpg</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Eleni Foureira represented Cyprus at the first...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38915</th>\n",
       "      <td>4YFo4bdMO8Q</td>\n",
       "      <td>18.14.06</td>\n",
       "      <td>KYLE - Ikuyo feat.  2 Chainz &amp; Sophia Black [A...</td>\n",
       "      <td>SuperDuperKyle</td>\n",
       "      <td>10</td>\n",
       "      <td>2018-05-11T04:06:35.000Z</td>\n",
       "      <td>Kyle|\"SuperDuperKyle\"|\"Ikuyo\"|\"2 Chainz\"|\"Soph...</td>\n",
       "      <td>607552</td>\n",
       "      <td>18271</td>\n",
       "      <td>274</td>\n",
       "      <td>1423</td>\n",
       "      <td>https://i.ytimg.com/vi/4YFo4bdMO8Q/default.jpg</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Debut album 'Light of Mine' out now: http://ky...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>79797 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          video_id                        ...                                                                description\n",
       "0      n1WpP7iowLc                        ...                          Eminem's new track Walk on Water ft. Beyoncé i...\n",
       "1      0dBIkQ4Mz1M                        ...                          STill got a lot of packages. Probably will las...\n",
       "...            ...                        ...                                                                        ...\n",
       "38914  -DRsfNObKIQ                        ...                          Eleni Foureira represented Cyprus at the first...\n",
       "38915  4YFo4bdMO8Q                        ...                          Debut album 'Light of Mine' out now: http://ky...\n",
       "\n",
       "[79797 rows x 16 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "canadian_youtube = pd.read_csv(\"../input/youtube-new/CAvideos.csv\")\n",
    "british_youtube = pd.read_csv(\"../input/youtube-new/GBvideos.csv\")\n",
    "\n",
    "pd.concat([canadian_youtube, british_youtube])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The middlemost combiner in terms of complexity is `pd.DataFrame.join`. `join` lets you combine different `DataFrame` objects which have an index in common. For example, to pull down videos that happened to be trending on the same day in _both_ Canada and the UK, we could do the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>video_id_CAN</th>\n",
       "      <th>channel_title_CAN</th>\n",
       "      <th>category_id_CAN</th>\n",
       "      <th>publish_time_CAN</th>\n",
       "      <th>tags_CAN</th>\n",
       "      <th>views_CAN</th>\n",
       "      <th>likes_CAN</th>\n",
       "      <th>dislikes_CAN</th>\n",
       "      <th>comment_count_CAN</th>\n",
       "      <th>thumbnail_link_CAN</th>\n",
       "      <th>comments_disabled_CAN</th>\n",
       "      <th>ratings_disabled_CAN</th>\n",
       "      <th>video_error_or_removed_CAN</th>\n",
       "      <th>description_CAN</th>\n",
       "      <th>video_id_UK</th>\n",
       "      <th>channel_title_UK</th>\n",
       "      <th>category_id_UK</th>\n",
       "      <th>publish_time_UK</th>\n",
       "      <th>tags_UK</th>\n",
       "      <th>views_UK</th>\n",
       "      <th>likes_UK</th>\n",
       "      <th>dislikes_UK</th>\n",
       "      <th>comment_count_UK</th>\n",
       "      <th>thumbnail_link_UK</th>\n",
       "      <th>comments_disabled_UK</th>\n",
       "      <th>ratings_disabled_UK</th>\n",
       "      <th>video_error_or_removed_UK</th>\n",
       "      <th>description_UK</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th>trending_date</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",
       "      <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>!! THIS VIDEO IS NOTHING BUT PAIN !! | Getting Over It - Part 7</th>\n",
       "      <th>18.04.01</th>\n",
       "      <td>PNn8sECd7io</td>\n",
       "      <td>Markiplier</td>\n",
       "      <td>20</td>\n",
       "      <td>2018-01-03T19:33:53.000Z</td>\n",
       "      <td>getting over it|\"markiplier\"|\"funny moments\"|\"...</td>\n",
       "      <td>835930</td>\n",
       "      <td>47058</td>\n",
       "      <td>1023</td>\n",
       "      <td>8250</td>\n",
       "      <td>https://i.ytimg.com/vi/PNn8sECd7io/default.jpg</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Getting Over It continues with RAGE BEYOND ALL...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#1 Fortnite World Rank - 2,323 Solo Wins!</th>\n",
       "      <th>18.09.03</th>\n",
       "      <td>DvPW66IFhMI</td>\n",
       "      <td>AlexRamiGaming</td>\n",
       "      <td>20</td>\n",
       "      <td>2018-03-09T07:15:52.000Z</td>\n",
       "      <td>PS4 Battle Royale|\"PS4 Pro Battle Royale\"|\"Bat...</td>\n",
       "      <td>212838</td>\n",
       "      <td>5199</td>\n",
       "      <td>542</td>\n",
       "      <td>11</td>\n",
       "      <td>https://i.ytimg.com/vi/DvPW66IFhMI/default.jpg</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Discord For EVERYONE - https://discord.gg/nhud...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>🚨 BREAKING NEWS 🔴 Raja Live all Slot Channels Welcome 🎰</th>\n",
       "      <th>18.07.05</th>\n",
       "      <td>Wt9Gkpmbt44</td>\n",
       "      <td>TheBigJackpot</td>\n",
       "      <td>24</td>\n",
       "      <td>2018-05-07T06:58:59.000Z</td>\n",
       "      <td>Slot Machine|\"win\"|\"Gambling\"|\"Big Win\"|\"raja\"...</td>\n",
       "      <td>28973</td>\n",
       "      <td>2167</td>\n",
       "      <td>175</td>\n",
       "      <td>10</td>\n",
       "      <td>https://i.ytimg.com/vi/Wt9Gkpmbt44/default.jpg</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>The Raja takes matters in to his own hands ton...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>🚨Active Shooter at YouTube Headquarters - LIVE BREAKING NEWS COVERAGE</th>\n",
       "      <th>18.04.04</th>\n",
       "      <td>Az72jrKbANA</td>\n",
       "      <td>Right Side Broadcasting Network</td>\n",
       "      <td>25</td>\n",
       "      <td>2018-04-03T23:12:37.000Z</td>\n",
       "      <td>YouTube shooter|\"YouTube active shooter\"|\"acti...</td>\n",
       "      <td>103513</td>\n",
       "      <td>1722</td>\n",
       "      <td>181</td>\n",
       "      <td>76</td>\n",
       "      <td>https://i.ytimg.com/vi/Az72jrKbANA/default.jpg</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>An active shooter has been reported at the You...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>40900 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                 video_id_CAN      ...       description_UK\n",
       "title                                              trending_date                   ...                     \n",
       "!! THIS VIDEO IS NOTHING BUT PAIN !! | Getting ... 18.04.01       PNn8sECd7io      ...                  NaN\n",
       "#1 Fortnite World Rank - 2,323 Solo Wins!          18.09.03       DvPW66IFhMI      ...                  NaN\n",
       "...                                                                       ...      ...                  ...\n",
       "🚨 BREAKING NEWS 🔴 Raja Live all Slot Channels W... 18.07.05       Wt9Gkpmbt44      ...                  NaN\n",
       "🚨Active Shooter at YouTube Headquarters - LIVE ... 18.04.04       Az72jrKbANA      ...                  NaN\n",
       "\n",
       "[40900 rows x 28 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left = canadian_youtube.set_index(['title', 'trending_date'])\n",
    "right = british_youtube.set_index(['title', 'trending_date'])\n",
    "\n",
    "left.join(right, lsuffix='_CAN', rsuffix='_UK')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `lsuffix` and `rsuffix` parameters are necessary here because the data has the same column names in both British and Canadian datasets. If this wasn't true (because, say, we'd renamed them beforehand) we wouldn't need them."
   ]
  },
  {
   "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": 2
}