notebooks/03.07-Merge-and-Join.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<!--NAVIGATION-->\n",
    "< [组合数据集：Concat 和 Append](03.06-Concat-And-Append.ipynb) | [目录](Index.ipynb) | [聚合与分组](03.08-Aggregation-and-Grouping.ipynb) >\n",
    "\n",
    "<a href=\"https://colab.research.google.com/github/wangyingsm/Python-Data-Science-Handbook/blob/master/notebooks/03.07-Merge-and-Join.ipynb\"><img align=\"left\" src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open in Colab\" title=\"Open and Execute in Google Colaboratory\"></a>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combining Datasets: Merge and Join\n",
    "\n",
    "# 组合数据集：Merge 和 Join"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> One essential feature offered by Pandas is its high-performance, in-memory join and merge operations.\n",
    "If you have ever worked with databases, you should be familiar with this type of data interaction.\n",
    "The main interface for this is the ``pd.merge`` function, and we'll see few examples of how this can work in practice.\n",
    "\n",
    "Pandas提供的一个基本的特性就是它的高性能、内存中进行的联表和组合操作。如果你使用过数据库，你应该已经很熟悉相关的数据操作了。Pandas在这方面提供的主要接口是`pd.merge`函数，本节中我们会看到一些具体实践的例子。\n",
    "\n",
    "> For convenience, we will start by redefining the ``display()`` functionality from the previous section:\n",
    "\n",
    "为了方便起见，我们重新定义上一节中的`display()`类，用来展示多个数据集："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "class display(object):\n",
    "    \"\"\"Display HTML representation of multiple objects\"\"\"\n",
    "    template = \"\"\"<div style=\"float: left; padding: 10px;\">\n",
    "    <p style='font-family:\"Courier New\", Courier, monospace'>{0}</p>{1}\n",
    "    </div>\"\"\"\n",
    "    def __init__(self, *args):\n",
    "        self.args = args\n",
    "        \n",
    "    def _repr_html_(self):\n",
    "        return '\\n'.join(self.template.format(a, eval(a)._repr_html_())\n",
    "                         for a in self.args)\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return '\\n\\n'.join(a + '\\n' + repr(eval(a))\n",
    "                           for a in self.args)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Relational Algebra\n",
    "\n",
    "## 关系代数\n",
    "\n",
    "> The behavior implemented in ``pd.merge()`` is a subset of what is known as *relational algebra*, which is a formal set of rules for manipulating relational data, and forms the conceptual foundation of operations available in most databases.\n",
    "The strength of the relational algebra approach is that it proposes several primitive operations, which become the building blocks of more complicated operations on any dataset.\n",
    "With this lexicon of fundamental operations implemented efficiently in a database or other program, a wide range of fairly complicated composite operations can be performed.\n",
    "\n",
    "`pd.merge()`实现的是我们称为*关系代数*的一个子集，关系代数是一系列操作关系数据的规则的集合，它构成了大部分数据库的数学基础。关系代数的力量表现在它仅提出了几个基本的运算，这些基本运算成为了更多复杂运算的组成模块，能够应用到任何的数据集上。只要在数据库或者其他程序中实现了这些最基本的运算，那么绝大部分的复杂组合运算都可以在上面实现。\n",
    "\n",
    "> Pandas implements several of these fundamental building-blocks in the ``pd.merge()`` function and the related ``join()`` method of ``Series`` and ``Dataframe``s.\n",
    "As we will see, these let you efficiently link data from different sources.\n",
    "\n",
    "Pandas在`pd.merge()`函数中实现了一些上述所说的最基本的运算，`Series`和`DataFrame`的`join`方法也实现了这部分基本运算，你将会看到，这能让你很高效地从不同数据源组合数据。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Categories of Joins\n",
    "\n",
    "## 联表的分类\n",
    "\n",
    "> The ``pd.merge()`` function implements a number of types of joins: the *one-to-one*, *many-to-one*, and *many-to-many* joins.\n",
    "All three types of joins are accessed via an identical call to the ``pd.merge()`` interface; the type of join performed depends on the form of the input data.\n",
    "Here we will show simple examples of the three types of merges, and discuss detailed options further below.\n",
    "\n",
    "`pd.merge()`函数实现了几种不同类型的联表：*一对一*、*多对一*和*多对多*。所有三种类型的联表都可以通过`pd.merge()`函数调用来实现；具体使用了哪种类型的联表取决于输入数据的格式。下面我们会展示一些简单的例子来说明三种联表类型，然后我们还会详细的讨论它们的选项。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### One-to-one joins\n",
    "\n",
    "### 一对一\n",
    "\n",
    "> Perhaps the simplest type of merge expresion is the one-to-one join, which is in many ways very similar to the column-wise concatenation seen in [Combining Datasets: Concat & Append](03.06-Concat-And-Append.ipynb).\n",
    "As a concrete example, consider the following two ``DataFrames`` which contain information on several employees in a company:\n",
    "\n",
    "也许最简单的联表操作类型就是一对一连接，在很多方面，这种联表都和我们在[组合数据集：Concat 和 Append](03.06-Concat-And-Append.ipynb)中看到的按列进行数据集连接很相似。下面定义两个`DataFrame`含有公司的一些员工信息作为一个具体的例子来说明："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df1</p><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>employee</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df2</p><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>employee</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jake</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df1\n",
       "  employee        group\n",
       "0      Bob   Accounting\n",
       "1     Jake  Engineering\n",
       "2     Lisa  Engineering\n",
       "3      Sue           HR\n",
       "\n",
       "df2\n",
       "  employee  hire_date\n",
       "0     Lisa       2004\n",
       "1      Bob       2008\n",
       "2     Jake       2012\n",
       "3      Sue       2014"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'employee': ['Bob', 'Jake', 'Lisa', 'Sue'],\n",
    "                    'group': ['Accounting', 'Engineering', 'Engineering', 'HR']})\n",
    "df2 = pd.DataFrame({'employee': ['Lisa', 'Bob', 'Jake', 'Sue'],\n",
    "                    'hire_date': [2004, 2008, 2012, 2014]})\n",
    "display('df1', 'df2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> To combine this information into a single ``DataFrame``, we can use the ``pd.merge()`` function:\n",
    "\n",
    "要将这两个数据集组合成一个`DataFrame`，我们可以使用`pd.merge`函数："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  employee        group  hire_date\n",
       "0      Bob   Accounting       2008\n",
       "1     Jake  Engineering       2012\n",
       "2     Lisa  Engineering       2004\n",
       "3      Sue           HR       2014"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.merge(df1, df2)\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The ``pd.merge()`` function recognizes that each ``DataFrame`` has an \"employee\" column, and automatically joins using this column as a key.\n",
    "The result of the merge is a new ``DataFrame`` that combines the information from the two inputs.\n",
    "Notice that the order of entries in each column is not necessarily maintained: in this case, the order of the \"employee\" column differs between ``df1`` and ``df2``, and the ``pd.merge()`` function correctly accounts for this.\n",
    "Additionally, keep in mind that the merge in general discards the index, except in the special case of merges by index (see the ``left_index`` and ``right_index`` keywords, discussed momentarily).\n",
    "\n",
    "`pd.merge()`函数会自动识别每个`DataFrame`都有\"employee\"列，因此会自动按照这个列作为键对双方进行合并。合并的结果是一个新的`DataFrame`，其中的数据是两个输入数据集的联合。再注意到每个列的排列顺序在结果中并不一定保持了：在这个情况下，\"employee\"列的顺序在`df1`和`df2`中是不同的，而`pd.merge()`函数也正确的考虑到了这点。而且，要知道的是，合并的结果通常会丢弃了原本的行索引标签，除非在合并时制定了行索引（参见我们马上会讨论到的`left_index`和`right_index`参数）。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Many-to-one joins\n",
    "\n",
    "### 多对一"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Many-to-one joins are joins in which one of the two key columns contains duplicate entries.\n",
    "For the many-to-one case, the resulting ``DataFrame`` will preserve those duplicate entries as appropriate.\n",
    "Consider the following example of a many-to-one join:\n",
    "\n",
    "多对一联表的情况发生在两个数据集的关键字列上的其中一个含有重复数据的时候。在这种多对一的情况下，结果的`DataFrame`会正确的保留那些重复的键值。看下面这个例子："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df3</p><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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df4</p><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>group</th>\n",
       "      <th>supervisor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>Carly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>Guido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>HR</td>\n",
       "      <td>Steve</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pd.merge(df3, df4)</p><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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>hire_date</th>\n",
       "      <th>supervisor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>2008</td>\n",
       "      <td>Carly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2012</td>\n",
       "      <td>Guido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2004</td>\n",
       "      <td>Guido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>2014</td>\n",
       "      <td>Steve</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df3\n",
       "  employee        group  hire_date\n",
       "0      Bob   Accounting       2008\n",
       "1     Jake  Engineering       2012\n",
       "2     Lisa  Engineering       2004\n",
       "3      Sue           HR       2014\n",
       "\n",
       "df4\n",
       "         group supervisor\n",
       "0   Accounting      Carly\n",
       "1  Engineering      Guido\n",
       "2           HR      Steve\n",
       "\n",
       "pd.merge(df3, df4)\n",
       "  employee        group  hire_date supervisor\n",
       "0      Bob   Accounting       2008      Carly\n",
       "1     Jake  Engineering       2012      Guido\n",
       "2     Lisa  Engineering       2004      Guido\n",
       "3      Sue           HR       2014      Steve"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4 = pd.DataFrame({'group': ['Accounting', 'Engineering', 'HR'],\n",
    "                    'supervisor': ['Carly', 'Guido', 'Steve']})\n",
    "display('df3', 'df4', 'pd.merge(df3, df4)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The resulting ``DataFrame`` has an aditional column with the \"supervisor\" information, where the information is repeated in one or more locations as required by the inputs.\n",
    "\n",
    "结果的`DataFrame`多了一列`supervisor`，上面的数据也是按照`group`的重复情况进行重复的。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Many-to-many joins\n",
    "\n",
    "### 多对多"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Many-to-many joins are a bit confusing conceptually, but are nevertheless well defined.\n",
    "If the key column in both the left and right array contains duplicates, then the result is a many-to-many merge.\n",
    "This will be perhaps most clear with a concrete example.\n",
    "Consider the following, where we have a ``DataFrame`` showing one or more skills associated with a particular group.\n",
    "By performing a many-to-many join, we can recover the skills associated with any individual person:\n",
    "\n",
    "多对多联表在概念上有一点混乱，但实际上良好定义了的。如果左右的数据集在关键字列上都有重复数据，那么结果就是一个多对多的组合。当然用一个具体的例子来说明是很有帮助的。比如下面的数据集`df5`存储的是一个岗位和其对应的技能。进行了多对多联表后，我们可以获得每个员工对应的技能表："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df1</p><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>employee</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df5</p><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>group</th>\n",
       "      <th>skills</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>spreadsheets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>coding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>linux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HR</td>\n",
       "      <td>spreadsheets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>HR</td>\n",
       "      <td>organization</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pd.merge(df1, df5)</p><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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>skills</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>spreadsheets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>coding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>linux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>coding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>linux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>spreadsheets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>organization</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df1\n",
       "  employee        group\n",
       "0      Bob   Accounting\n",
       "1     Jake  Engineering\n",
       "2     Lisa  Engineering\n",
       "3      Sue           HR\n",
       "\n",
       "df5\n",
       "         group        skills\n",
       "0   Accounting          math\n",
       "1   Accounting  spreadsheets\n",
       "2  Engineering        coding\n",
       "3  Engineering         linux\n",
       "4           HR  spreadsheets\n",
       "5           HR  organization\n",
       "\n",
       "pd.merge(df1, df5)\n",
       "  employee        group        skills\n",
       "0      Bob   Accounting          math\n",
       "1      Bob   Accounting  spreadsheets\n",
       "2     Jake  Engineering        coding\n",
       "3     Jake  Engineering         linux\n",
       "4     Lisa  Engineering        coding\n",
       "5     Lisa  Engineering         linux\n",
       "6      Sue           HR  spreadsheets\n",
       "7      Sue           HR  organization"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5 = pd.DataFrame({'group': ['Accounting', 'Accounting',\n",
    "                              'Engineering', 'Engineering', 'HR', 'HR'],\n",
    "                    'skills': ['math', 'spreadsheets', 'coding', 'linux',\n",
    "                               'spreadsheets', 'organization']})\n",
    "display('df1', 'df5', \"pd.merge(df1, df5)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> These three types of joins can be used with other Pandas tools to implement a wide array of functionality.\n",
    "But in practice, datasets are rarely as clean as the one we're working with here.\n",
    "In the following section we'll consider some of the options provided by ``pd.merge()`` that enable you to tune how the join operations work.\n",
    "\n",
    "这三种类型的连接可以和Pandas的其他工具联合使用，来实现很强大的功能。但是在实践中，数据集极少好像我们上面的例子那样干净。在接下来的部分，我们会介绍`pd.merge()`提供的一些参数，能让你精细的对连接操作进行调整。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Specification of the Merge Key\n",
    "\n",
    "## 指定合并关键字"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> We've already seen the default behavior of ``pd.merge()``: it looks for one or more matching column names between the two inputs, and uses this as the key.\n",
    "However, often the column names will not match so nicely, and ``pd.merge()`` provides a variety of options for handling this.\n",
    "\n",
    "上面我们看到`pd.merge()`的默认行为：它会在两个输入数据集中寻找一个或多个相同的列名，然后使用这（些）列作为合并的关键字。然而，通常情况下，列名并不会这么匹配，`pd.merge()`提供了一系列的参数来处理这种情况。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The ``on`` keyword\n",
    "\n",
    "### `on` 关键字参数\n",
    "\n",
    "> Most simply, you can explicitly specify the name of the key column using the ``on`` keyword, which takes a column name or a list of column names:\n",
    "\n",
    "最简单的，你可以使用`on`关键字参数明确指定合并使用的关键字列名，参数可以是一个列名或者一个列名的列表："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df1</p><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>employee</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df2</p><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>employee</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jake</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pd.merge(df1, df2, on='employee')</p><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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df1\n",
       "  employee        group\n",
       "0      Bob   Accounting\n",
       "1     Jake  Engineering\n",
       "2     Lisa  Engineering\n",
       "3      Sue           HR\n",
       "\n",
       "df2\n",
       "  employee  hire_date\n",
       "0     Lisa       2004\n",
       "1      Bob       2008\n",
       "2     Jake       2012\n",
       "3      Sue       2014\n",
       "\n",
       "pd.merge(df1, df2, on='employee')\n",
       "  employee        group  hire_date\n",
       "0      Bob   Accounting       2008\n",
       "1     Jake  Engineering       2012\n",
       "2     Lisa  Engineering       2004\n",
       "3      Sue           HR       2014"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display('df1', 'df2', \"pd.merge(df1, df2, on='employee')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> This option works only if both the left and right ``DataFrame``s have the specified column name.\n",
    "\n",
    "该参数仅在左右两个`DataFrame`都含有相同的指定列名的情况下有效。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The ``left_on`` and ``right_on`` keywords\n",
    "\n",
    "### `left_on` 和 `right_on` 关键字参数\n",
    "\n",
    "> At times you may wish to merge two datasets with different column names; for example, we may have a dataset in which the employee name is labeled as \"name\" rather than \"employee\".\n",
    "In this case, we can use the ``left_on`` and ``right_on`` keywords to specify the two column names:\n",
    "\n",
    "在你希望使用不同列名来合并两个数据集的情况下；例如，我们有一个数据集，在它里面员工姓名的列名不是\"employee\"而是\"name\"。在这种情况下，我们可以使用`left_on`和`right_on`关键字来分别指定两个列的名字："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df1</p><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>employee</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df3</p><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>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pd.merge(df1, df3, left_on=\"employee\", right_on=\"name\")</p><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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>name</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>Bob</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>Jake</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>Lisa</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>Sue</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df1\n",
       "  employee        group\n",
       "0      Bob   Accounting\n",
       "1     Jake  Engineering\n",
       "2     Lisa  Engineering\n",
       "3      Sue           HR\n",
       "\n",
       "df3\n",
       "   name  salary\n",
       "0   Bob   70000\n",
       "1  Jake   80000\n",
       "2  Lisa  120000\n",
       "3   Sue   90000\n",
       "\n",
       "pd.merge(df1, df3, left_on=\"employee\", right_on=\"name\")\n",
       "  employee        group  name  salary\n",
       "0      Bob   Accounting   Bob   70000\n",
       "1     Jake  Engineering  Jake   80000\n",
       "2     Lisa  Engineering  Lisa  120000\n",
       "3      Sue           HR   Sue   90000"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],\n",
    "                    'salary': [70000, 80000, 120000, 90000]})\n",
    "display('df1', 'df3', 'pd.merge(df1, df3, left_on=\"employee\", right_on=\"name\")')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The result has a redundant column that we can drop if desired–for example, by using the ``drop()`` method of ``DataFrame``s:\n",
    "\n",
    "结果中有一个冗余的列，我们可以将改列移除，例如使用`DataFrame`的`drop()`方法："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  employee        group  salary\n",
       "0      Bob   Accounting   70000\n",
       "1     Jake  Engineering   80000\n",
       "2     Lisa  Engineering  120000\n",
       "3      Sue           HR   90000"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df3, left_on=\"employee\", right_on=\"name\").drop('name', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The ``left_index`` and ``right_index`` keywords\n",
    "\n",
    "### `left_index` 和 `right_index` 关键参数\n",
    "\n",
    "> Sometimes, rather than merging on a column, you would instead like to merge on an index.\n",
    "For example, your data might look like this:\n",
    "\n",
    "有时候，你不是需要按列进行合并，而是需要按照行索引进行合并。例如，将`df1`和`df2`数据集修改为如下情况："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df1a</p><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>group</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df2a</p><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>hire_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df1a\n",
       "                group\n",
       "employee             \n",
       "Bob        Accounting\n",
       "Jake      Engineering\n",
       "Lisa      Engineering\n",
       "Sue                HR\n",
       "\n",
       "df2a\n",
       "          hire_date\n",
       "employee           \n",
       "Lisa           2004\n",
       "Bob            2008\n",
       "Jake           2012\n",
       "Sue            2014"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1a = df1.set_index('employee')\n",
    "df2a = df2.set_index('employee')\n",
    "display('df1a', 'df2a')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> You can use the index as the key for merging by specifying the ``left_index`` and/or ``right_index`` flags in ``pd.merge()``:\n",
    "\n",
    "通过指定`left_index`和`right_index`标志参数，你可以将两个数据集按照行索引进行合并："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df1a</p><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>group</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df2a</p><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>hire_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pd.merge(df1a, df2a, left_index=True, right_index=True)</p><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>group</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>HR</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df1a\n",
       "                group\n",
       "employee             \n",
       "Bob        Accounting\n",
       "Jake      Engineering\n",
       "Lisa      Engineering\n",
       "Sue                HR\n",
       "\n",
       "df2a\n",
       "          hire_date\n",
       "employee           \n",
       "Lisa           2004\n",
       "Bob            2008\n",
       "Jake           2012\n",
       "Sue            2014\n",
       "\n",
       "pd.merge(df1a, df2a, left_index=True, right_index=True)\n",
       "                group  hire_date\n",
       "employee                        \n",
       "Bob        Accounting       2008\n",
       "Jake      Engineering       2012\n",
       "Lisa      Engineering       2004\n",
       "Sue                HR       2014"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display('df1a', 'df2a',\n",
    "        \"pd.merge(df1a, df2a, left_index=True, right_index=True)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> For convenience, ``DataFrame``s implement the ``join()`` method, which performs a merge that defaults to joining on indices:\n",
    "\n",
    "为了方便，`DataFrame`实现了`join()`方法，默认按照行索引合并数据集："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df1a</p><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>group</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df2a</p><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>hire_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df1a.join(df2a)</p><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>group</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>HR</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df1a\n",
       "                group\n",
       "employee             \n",
       "Bob        Accounting\n",
       "Jake      Engineering\n",
       "Lisa      Engineering\n",
       "Sue                HR\n",
       "\n",
       "df2a\n",
       "          hire_date\n",
       "employee           \n",
       "Lisa           2004\n",
       "Bob            2008\n",
       "Jake           2012\n",
       "Sue            2014\n",
       "\n",
       "df1a.join(df2a)\n",
       "                group  hire_date\n",
       "employee                        \n",
       "Bob        Accounting       2008\n",
       "Jake      Engineering       2012\n",
       "Lisa      Engineering       2004\n",
       "Sue                HR       2014"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display('df1a', 'df2a', 'df1a.join(df2a)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> If you'd like to mix indices and columns, you can combine ``left_index`` with ``right_on`` or ``left_on`` with ``right_index`` to get the desired behavior:\n",
    "\n",
    "如果需要混合的进行行或列的合并，你可以通过混合指定`left_index`和`right_on`参数或者`left_on`和`right_index`参数来实现："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df1a</p><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>group</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df3</p><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>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pd.merge(df1a, df3, left_index=True, right_on='name')</p><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>group</th>\n",
       "      <th>name</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>Bob</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>Jake</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>Lisa</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HR</td>\n",
       "      <td>Sue</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df1a\n",
       "                group\n",
       "employee             \n",
       "Bob        Accounting\n",
       "Jake      Engineering\n",
       "Lisa      Engineering\n",
       "Sue                HR\n",
       "\n",
       "df3\n",
       "   name  salary\n",
       "0   Bob   70000\n",
       "1  Jake   80000\n",
       "2  Lisa  120000\n",
       "3   Sue   90000\n",
       "\n",
       "pd.merge(df1a, df3, left_index=True, right_on='name')\n",
       "         group  name  salary\n",
       "0   Accounting   Bob   70000\n",
       "1  Engineering  Jake   80000\n",
       "2  Engineering  Lisa  120000\n",
       "3           HR   Sue   90000"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display('df1a', 'df3', \"pd.merge(df1a, df3, left_index=True, right_on='name')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> All of these options also work with multiple indices and/or multiple columns; the interface for this behavior is very intuitive.\n",
    "For more information on this, see the [\"Merge, Join, and Concatenate\" section](http://pandas.pydata.org/pandas-docs/stable/merging.html) of the Pandas documentation.\n",
    "\n",
    "所有上面的参数都能应用到多重行索引和/或多重列上；这个接口的定义是非常直观的。需要了解更多的信息，参见Pandas在线文档[\"Merge、join和Concatenate\"章节](http://pandas.pydata.org/pandas-docs/stable/merging.html)。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Specifying Set Arithmetic for Joins\n",
    "\n",
    "## 指定合并的集合算术运算"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> In all the preceding examples we have glossed over one important consideration in performing a join: the type of set arithmetic used in the join.\n",
    "This comes up when a value appears in one key column but not the other. Consider this example:\n",
    "\n",
    "在上面的例子中，我们都忽略了在进行数据集合并时一个重要的内容：合并时所使用的集合算术运算类型。这部分内容对于当一个数据集的键值在另一个数据集中不存在时很有意义。看下例："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df6</p><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>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Peter</td>\n",
       "      <td>fish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Paul</td>\n",
       "      <td>beans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df7</p><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>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mary</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Joseph</td>\n",
       "      <td>beer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pd.merge(df6, df7)</p><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>food</th>\n",
       "      <th>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df6\n",
       "    name   food\n",
       "0  Peter   fish\n",
       "1   Paul  beans\n",
       "2   Mary  bread\n",
       "\n",
       "df7\n",
       "     name drink\n",
       "0    Mary  wine\n",
       "1  Joseph  beer\n",
       "\n",
       "pd.merge(df6, df7)\n",
       "   name   food drink\n",
       "0  Mary  bread  wine"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df6 = pd.DataFrame({'name': ['Peter', 'Paul', 'Mary'],\n",
    "                    'food': ['fish', 'beans', 'bread']},\n",
    "                   columns=['name', 'food'])\n",
    "df7 = pd.DataFrame({'name': ['Mary', 'Joseph'],\n",
    "                    'drink': ['wine', 'beer']},\n",
    "                   columns=['name', 'drink'])\n",
    "display('df6', 'df7', 'pd.merge(df6, df7)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Here we have merged two datasets that have only a single \"name\" entry in common: Mary.\n",
    "By default, the result contains the *intersection* of the two sets of inputs; this is what is known as an *inner join*.\n",
    "We can specify this explicitly using the ``how`` keyword, which defaults to ``\"inner\"``:\n",
    "\n",
    "上面我们合并的两个数据集在关键字列上只有一个\"name\"数据是共同的：Mary。默认情况下，结果会包含两个集合的*交集*；这被称为*内连接*。我们显式的指定`how`关键字参数，它的默认值是`\"inner\"`："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>name</th>\n",
       "      <th>food</th>\n",
       "      <th>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name   food drink\n",
       "0  Mary  bread  wine"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df6, df7, how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Other options for the ``how`` keyword are ``'outer'``, ``'left'``, and ``'right'``.\n",
    "An *outer join* returns a join over the union of the input columns, and fills in all missing values with NAs:\n",
    "\n",
    "`how`参数的其他选项包括`'outer'`、`'left'`和`'right'`。*外连接outer*会返回两个集合的并集，并将缺失的数据填充为Pandas的NA值："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df6</p><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>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Peter</td>\n",
       "      <td>fish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Paul</td>\n",
       "      <td>beans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df7</p><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>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mary</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Joseph</td>\n",
       "      <td>beer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pd.merge(df6, df7, how='outer')</p><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>food</th>\n",
       "      <th>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Peter</td>\n",
       "      <td>fish</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Paul</td>\n",
       "      <td>beans</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Joseph</td>\n",
       "      <td>NaN</td>\n",
       "      <td>beer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df6\n",
       "    name   food\n",
       "0  Peter   fish\n",
       "1   Paul  beans\n",
       "2   Mary  bread\n",
       "\n",
       "df7\n",
       "     name drink\n",
       "0    Mary  wine\n",
       "1  Joseph  beer\n",
       "\n",
       "pd.merge(df6, df7, how='outer')\n",
       "     name   food drink\n",
       "0   Peter   fish   NaN\n",
       "1    Paul  beans   NaN\n",
       "2    Mary  bread  wine\n",
       "3  Joseph    NaN  beer"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display('df6', 'df7', \"pd.merge(df6, df7, how='outer')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The *left join* and *right join* return joins over the left entries and right entries, respectively.\n",
    "For example:\n",
    "\n",
    "*左连接left*和*右连接right*返回的结果是包括所有的左边或右边集合。例如："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df6</p><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>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Peter</td>\n",
       "      <td>fish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Paul</td>\n",
       "      <td>beans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df7</p><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>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mary</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Joseph</td>\n",
       "      <td>beer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pd.merge(df6, df7, how='left')</p><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>food</th>\n",
       "      <th>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Peter</td>\n",
       "      <td>fish</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Paul</td>\n",
       "      <td>beans</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df6\n",
       "    name   food\n",
       "0  Peter   fish\n",
       "1   Paul  beans\n",
       "2   Mary  bread\n",
       "\n",
       "df7\n",
       "     name drink\n",
       "0    Mary  wine\n",
       "1  Joseph  beer\n",
       "\n",
       "pd.merge(df6, df7, how='left')\n",
       "    name   food drink\n",
       "0  Peter   fish   NaN\n",
       "1   Paul  beans   NaN\n",
       "2   Mary  bread  wine"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display('df6', 'df7', \"pd.merge(df6, df7, how='left')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The output rows now correspond to the entries in the left input. Using\n",
    "``how='right'`` works in a similar manner.\n",
    "\n",
    "> All of these options can be applied straightforwardly to any of the preceding join types.\n",
    "\n",
    "结果中的行与左集合保持一致。使用`how='right'`结果会和右集合保持一致。\n",
    "\n",
    "所有这些集合运算类型可以和前面的连接类型组合使用。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overlapping Column Names: The ``suffixes`` Keyword\n",
    "\n",
    "## 列名冲突：`suffixes`关键字参数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Finally, you may end up in a case where your two input ``DataFrame``s have conflicting column names.\n",
    "Consider this example:\n",
    "\n",
    "最后，你可能会碰到一种情况两个输入`DataFrame`有着冲突的列名。例如："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df8</p><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>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df9</p><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>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pd.merge(df8, df9, on=\"name\")</p><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>rank_x</th>\n",
       "      <th>rank_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df8\n",
       "   name  rank\n",
       "0   Bob     1\n",
       "1  Jake     2\n",
       "2  Lisa     3\n",
       "3   Sue     4\n",
       "\n",
       "df9\n",
       "   name  rank\n",
       "0   Bob     3\n",
       "1  Jake     1\n",
       "2  Lisa     4\n",
       "3   Sue     2\n",
       "\n",
       "pd.merge(df8, df9, on=\"name\")\n",
       "   name  rank_x  rank_y\n",
       "0   Bob       1       3\n",
       "1  Jake       2       1\n",
       "2  Lisa       3       4\n",
       "3   Sue       4       2"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df8 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],\n",
    "                    'rank': [1, 2, 3, 4]})\n",
    "df9 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],\n",
    "                    'rank': [3, 1, 4, 2]})\n",
    "display('df8', 'df9', 'pd.merge(df8, df9, on=\"name\")')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Because the output would have two conflicting column names, the merge function automatically appends a suffix ``_x`` or ``_y`` to make the output columns unique.\n",
    "If these defaults are inappropriate, it is possible to specify a custom suffix using the ``suffixes`` keyword:\n",
    "\n",
    "因为结果可能会有两个相同的列名，发生冲突，merge函数会自动为这两个列添加`_x`和`_y`后缀，使得输出结果每个列名称唯一。如果默认的后缀不是你希望的，可以使用`suffixes`关键字参数为输出列添加自定义的后缀："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df8</p><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>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df9</p><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>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pd.merge(df8, df9, on=\"name\", suffixes=[\"_L\", \"_R\"])</p><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>rank_L</th>\n",
       "      <th>rank_R</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df8\n",
       "   name  rank\n",
       "0   Bob     1\n",
       "1  Jake     2\n",
       "2  Lisa     3\n",
       "3   Sue     4\n",
       "\n",
       "df9\n",
       "   name  rank\n",
       "0   Bob     3\n",
       "1  Jake     1\n",
       "2  Lisa     4\n",
       "3   Sue     2\n",
       "\n",
       "pd.merge(df8, df9, on=\"name\", suffixes=[\"_L\", \"_R\"])\n",
       "   name  rank_L  rank_R\n",
       "0   Bob       1       3\n",
       "1  Jake       2       1\n",
       "2  Lisa       3       4\n",
       "3   Sue       4       2"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display('df8', 'df9', 'pd.merge(df8, df9, on=\"name\", suffixes=[\"_L\", \"_R\"])')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> These suffixes work in any of the possible join patterns, and work also if there are multiple overlapping columns.\n",
    "\n",
    "这些后缀可以应用在所有的连接方式中，也可以在多个列冲突时使用。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> For more information on these patterns, see [Aggregation and Grouping](03.08-Aggregation-and-Grouping.ipynb) where we dive a bit deeper into relational algebra.\n",
    "Also see the [Pandas \"Merge, Join and Concatenate\" documentation](http://pandas.pydata.org/pandas-docs/stable/merging.html) for further discussion of these topics.\n",
    "\n",
    "需要了解更多知识，参见[聚合与分组](03.08-Aggregation-and-Grouping.ipynb)，我们会更加深入的介绍关系代数。也可以参见Pandas在线[\"Merge, Join 和 Concatenate\"文档](http://pandas.pydata.org/pandas-docs/stable/merging.html)学习更多内容。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example: US States Data\n",
    "\n",
    "## 例子：美国州数据\n",
    "\n",
    "> Merge and join operations come up most often when combining data from different sources.\n",
    "Here we will consider an example of some data about US states and their populations.\n",
    "The data files can be found at http://github.com/jakevdp/data-USstates/:\n",
    "\n",
    "合并及联表操作在你处理多个不同数据来源时会经常出现。下面我们使用美国州及其人口数据作为例子来进行更加直观的说明。这些数据文件可以在http://github.com/jakevdp/data-USstates/ 中找到："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 如果你没有数据文件，可以使用下面的命令下载它们\n",
    "# !curl -O https://raw.githubusercontent.com/jakevdp/data-USstates/master/state-population.csv\n",
    "# !curl -O https://raw.githubusercontent.com/jakevdp/data-USstates/master/state-areas.csv\n",
    "# !curl -O https://raw.githubusercontent.com/jakevdp/data-USstates/master/state-abbrevs.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Let's take a look at the three datasets, using the Pandas ``read_csv()`` function:\n",
    "\n",
    "下面我们来载入三个相关的数据文件，使用Pandas的`read_csv()`函数："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>pop.head()</p><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>state/region</th>\n",
       "      <th>ages</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2012</td>\n",
       "      <td>1117489.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2012</td>\n",
       "      <td>4817528.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2010</td>\n",
       "      <td>1130966.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>4785570.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2011</td>\n",
       "      <td>1125763.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>areas.head()</p><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>state</th>\n",
       "      <th>area (sq. mi)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>656425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>114006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>53182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>163707</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>abbrevs.head()</p><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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "pop.head()\n",
       "  state/region     ages  year  population\n",
       "0           AL  under18  2012   1117489.0\n",
       "1           AL    total  2012   4817528.0\n",
       "2           AL  under18  2010   1130966.0\n",
       "3           AL    total  2010   4785570.0\n",
       "4           AL  under18  2011   1125763.0\n",
       "\n",
       "areas.head()\n",
       "        state  area (sq. mi)\n",
       "0     Alabama          52423\n",
       "1      Alaska         656425\n",
       "2     Arizona         114006\n",
       "3    Arkansas          53182\n",
       "4  California         163707\n",
       "\n",
       "abbrevs.head()\n",
       "        state abbreviation\n",
       "0     Alabama           AL\n",
       "1      Alaska           AK\n",
       "2     Arizona           AZ\n",
       "3    Arkansas           AR\n",
       "4  California           CA"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop = pd.read_csv('data/state-population.csv')\n",
    "areas = pd.read_csv('data/state-areas.csv')\n",
    "abbrevs = pd.read_csv('data/state-abbrevs.csv')\n",
    "\n",
    "display('pop.head()', 'areas.head()', 'abbrevs.head()')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Given this information, say we want to compute a relatively straightforward result: rank US states and territories by their 2010 population density.\n",
    "We clearly have the data here to find this result, but we'll have to combine the datasets to find the result.\n",
    "\n",
    "有了数据之后，假如我们需要计算一个相对非常直接的结果：根据美国各州2010年人口密度进行排名。很显然我们有相关的数据，但是我们需要合并数据集才能找到结果。\n",
    "\n",
    "> We'll start with a many-to-one merge that will give us the full state name within the population ``DataFrame``.\n",
    "We want to merge based on the ``state/region``  column of ``pop``, and the ``abbreviation`` column of ``abbrevs``.\n",
    "We'll use ``how='outer'`` to make sure no data is thrown away due to mismatched labels.\n",
    "\n",
    "我们先进行一个多对一的合并，将州全名和人口数据合并在一个`DataFrame`中。我们希望合并基于`pop`数据集的`state/region`列以及`abbreviation`数据集的`abbrevs`列。使用`how='outer'`来保证合并过程中不会因为不匹配的标签而丢失任何数据。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>state/region</th>\n",
       "      <th>ages</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2012</td>\n",
       "      <td>1117489.0</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2012</td>\n",
       "      <td>4817528.0</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2010</td>\n",
       "      <td>1130966.0</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>4785570.0</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2011</td>\n",
       "      <td>1125763.0</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state/region     ages  year  population    state\n",
       "0           AL  under18  2012   1117489.0  Alabama\n",
       "1           AL    total  2012   4817528.0  Alabama\n",
       "2           AL  under18  2010   1130966.0  Alabama\n",
       "3           AL    total  2010   4785570.0  Alabama\n",
       "4           AL  under18  2011   1125763.0  Alabama"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged = pd.merge(pop, abbrevs, how='outer',\n",
    "                  left_on='state/region', right_on='abbreviation')\n",
    "merged = merged.drop('abbreviation', 1) # 移除冗余的列\n",
    "merged.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Let's double-check whether there were any mismatches here, which we can do by looking for rows with nulls:\n",
    "\n",
    "让我们检查结果中是否有不匹配的情况，通过在数据集中寻找空值来查看："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state/region    False\n",
       "ages            False\n",
       "year            False\n",
       "population       True\n",
       "state            True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.isnull().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Some of the ``population`` info is null; let's figure out which these are!\n",
    "\n",
    "一些人口`population`数据是空的；再来看看是哪些。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>state/region</th>\n",
       "      <th>ages</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2448</th>\n",
       "      <td>PR</td>\n",
       "      <td>under18</td>\n",
       "      <td>1990</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2449</th>\n",
       "      <td>PR</td>\n",
       "      <td>total</td>\n",
       "      <td>1990</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2450</th>\n",
       "      <td>PR</td>\n",
       "      <td>total</td>\n",
       "      <td>1991</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2451</th>\n",
       "      <td>PR</td>\n",
       "      <td>under18</td>\n",
       "      <td>1991</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2452</th>\n",
       "      <td>PR</td>\n",
       "      <td>total</td>\n",
       "      <td>1993</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state/region     ages  year  population state\n",
       "2448           PR  under18  1990         NaN   NaN\n",
       "2449           PR    total  1990         NaN   NaN\n",
       "2450           PR    total  1991         NaN   NaN\n",
       "2451           PR  under18  1991         NaN   NaN\n",
       "2452           PR    total  1993         NaN   NaN"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged[merged['population'].isnull()].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> It appears that all the null population values are from Puerto Rico prior to the year 2000; this is likely due to this data not being available from the original source.\n",
    "\n",
    "发现所有空的人口数据都是2000年前波多黎各的；这可能因为数据来源本来就没有这些数据造成的。\n",
    "\n",
    "> More importantly, we see also that some of the new ``state`` entries are also null, which means that there was no corresponding entry in the ``abbrevs`` key!\n",
    "Let's figure out which regions lack this match:\n",
    "\n",
    "更重要的是，我们发现一些新的州`state`的数据也是空的，这意味着`abbrevs`列中不存在这些州的简称。再看看是哪些州有这种情况："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['PR', 'USA'], dtype=object)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.loc[merged['state'].isnull(), 'state/region'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> We can quickly infer the issue: our population data includes entries for Puerto Rico (PR) and the United States as a whole (USA), while these entries do not appear in the state abbreviation key.\n",
    "We can fix these quickly by filling in appropriate entries:\n",
    "\n",
    "从上面的结果很容易发现：人口数据集中包括波多黎各（PR）和全美国（USA）的数据，而州简称数据集中却没有这两者数据。通过填充相应的数据可以很快解决这个问题："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state/region    False\n",
       "ages            False\n",
       "year            False\n",
       "population       True\n",
       "state           False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.loc[merged['state/region'] == 'PR', 'state'] = 'Puerto Rico'\n",
    "merged.loc[merged['state/region'] == 'USA', 'state'] = 'United States'\n",
    "merged.isnull().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> No more nulls in the ``state`` column: we're all set!\n",
    "\n",
    "`state`列没有空值了：我们准备好了。\n",
    "\n",
    "> Now we can merge the result with the area data using a similar procedure.\n",
    "Examining our results, we will want to join on the ``state`` column in both:\n",
    "\n",
    "下面我们可以将上面的结果数据集和面积数据集进行合并。研究两个数据集发现，我们需要在`state`列上进行数据集合并操作："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>state/region</th>\n",
       "      <th>ages</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "      <th>state</th>\n",
       "      <th>area (sq. mi)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2012</td>\n",
       "      <td>1117489.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2012</td>\n",
       "      <td>4817528.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2010</td>\n",
       "      <td>1130966.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>4785570.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2011</td>\n",
       "      <td>1125763.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state/region     ages  year  population    state  area (sq. mi)\n",
       "0           AL  under18  2012   1117489.0  Alabama        52423.0\n",
       "1           AL    total  2012   4817528.0  Alabama        52423.0\n",
       "2           AL  under18  2010   1130966.0  Alabama        52423.0\n",
       "3           AL    total  2010   4785570.0  Alabama        52423.0\n",
       "4           AL  under18  2011   1125763.0  Alabama        52423.0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final = pd.merge(merged, areas, on='state', how='left')\n",
    "final.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Again, let's check for nulls to see if there were any mismatches:\n",
    "\n",
    "再一次，我们检查一次空值，来看是否存在不匹配的情况："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state/region     False\n",
       "ages             False\n",
       "year             False\n",
       "population        True\n",
       "state            False\n",
       "area (sq. mi)     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.isnull().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> There are nulls in the ``area`` column; we can take a look to see which regions were ignored here:\n",
    "\n",
    "面积`area`列有空值；我们看看是哪里出现的："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['United States'], dtype=object)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final['state'][final['area (sq. mi)'].isnull()].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> We see that our ``areas`` ``DataFrame`` does not contain the area of the United States as a whole.\n",
    "We could insert the appropriate value (using the sum of all state areas, for instance), but in this case we'll just drop the null values because the population density of the entire United States is not relevant to our current discussion:\n",
    "\n",
    "结果显示面积数据集不包括整个美国的面积。我们可以为这个空值插入正确的值（使用所有州的面积数据之和），但是这个例子中我们只需要简单地移除空值数据即可，因为全美国的人口密度数据与我们前面的问题无关："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>state/region</th>\n",
       "      <th>ages</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "      <th>state</th>\n",
       "      <th>area (sq. mi)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2012</td>\n",
       "      <td>1117489.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2012</td>\n",
       "      <td>4817528.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2010</td>\n",
       "      <td>1130966.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>4785570.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2011</td>\n",
       "      <td>1125763.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state/region     ages  year  population    state  area (sq. mi)\n",
       "0           AL  under18  2012   1117489.0  Alabama        52423.0\n",
       "1           AL    total  2012   4817528.0  Alabama        52423.0\n",
       "2           AL  under18  2010   1130966.0  Alabama        52423.0\n",
       "3           AL    total  2010   4785570.0  Alabama        52423.0\n",
       "4           AL  under18  2011   1125763.0  Alabama        52423.0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.dropna(inplace=True)\n",
    "final.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Now we have all the data we need. To answer the question of interest, let's first select the portion of the data corresponding with the year 2000, and the total population.\n",
    "We'll use the ``query()`` function to do this quickly (this requires the ``numexpr`` package to be installed; see [High-Performance Pandas: ``eval()`` and ``query()``](03.12-Performance-Eval-and-Query.ipynb)):\n",
    "\n",
    "现在我们需要数据都已经准备好了。要回答前面那个问题，首先要选择出2010年相应的部分数据集以及不分年龄的全体人口数。我们使用`query()`函数来快速完成这项任务（这需要安装`numexpr`包，参见[高性能Pandas: ``eval()`` 和 ``query()``](03.12-Performance-Eval-and-Query.ipynb)）："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>state/region</th>\n",
       "      <th>ages</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "      <th>state</th>\n",
       "      <th>area (sq. mi)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>4785570.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>AK</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>713868.0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>656425.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>AZ</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>6408790.0</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>114006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>AR</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>2922280.0</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>53182.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>CA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>37333601.0</td>\n",
       "      <td>California</td>\n",
       "      <td>163707.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    state/region   ages  year  population       state  area (sq. mi)\n",
       "3             AL  total  2010   4785570.0     Alabama        52423.0\n",
       "91            AK  total  2010    713868.0      Alaska       656425.0\n",
       "101           AZ  total  2010   6408790.0     Arizona       114006.0\n",
       "189           AR  total  2010   2922280.0    Arkansas        53182.0\n",
       "197           CA  total  2010  37333601.0  California       163707.0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2010 = final.query(\"year == 2010 & ages == 'total'\")\n",
    "data2010.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Now let's compute the population density and display it in order.\n",
    "We'll start by re-indexing our data on the state, and then compute the result:\n",
    "\n",
    "下面我们可以计算人口密度并排序输出了。我们现将数据集按照`state`进行重新索引，然后计算结果："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "data2010.set_index('state', inplace=True)\n",
    "density = data2010['population'] / data2010['area (sq. mi)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state\n",
       "District of Columbia    8898.897059\n",
       "Puerto Rico             1058.665149\n",
       "New Jersey              1009.253268\n",
       "Rhode Island             681.339159\n",
       "Connecticut              645.600649\n",
       "dtype: float64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "density.sort_values(ascending=False, inplace=True)\n",
    "density.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The result is a ranking of US states plus Washington, DC, and Puerto Rico in order of their 2010 population density, in residents per square mile.\n",
    "We can see that by far the densest region in this dataset is Washington, DC (i.e., the District of Columbia); among states, the densest is New Jersey.\n",
    "\n",
    "结果是美国州根据2010年人口密度的排名，包括华盛顿特区和波多黎各，数据是每平方英里的居住人数。结果显示人口密度最稠密的地区是华盛顿特区（表中的the District of Columbia）；在其他的州中，人口密度最大的是新泽西。\n",
    "\n",
    "> We can also check the end of the list:\n",
    "\n",
    "我们也可以查看结果的最后部分："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state\n",
       "South Dakota    10.583512\n",
       "North Dakota     9.537565\n",
       "Montana          6.736171\n",
       "Wyoming          5.768079\n",
       "Alaska           1.087509\n",
       "dtype: float64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "density.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> We see that the least dense state, by far, is Alaska, averaging slightly over one resident per square mile.\n",
    "\n",
    "结果显示密度最小的州，阿拉斯加，平均每平方英里略大于1个居民。\n",
    "\n",
    "> This type of messy data merging is a common task when trying to answer questions using real-world data sources.\n",
    "I hope that this example has given you an idea of the ways you can combine tools we've covered in order to gain insight from your data!\n",
    "\n",
    "当使用真实世界数据回答这种问题的时候，这种数据集的合并是很常见的任务。作者希望这个例子能为你展示了Pandas数据集合并的工具的使用，并能在你的数据集中应用这些方法。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<!--NAVIGATION-->\n",
    "< [组合数据集：Concat 和 Append](03.06-Concat-And-Append.ipynb) | [目录](Index.ipynb) | [聚合与分组](03.08-Aggregation-and-Grouping.ipynb) >\n",
    "\n",
    "<a href=\"https://colab.research.google.com/github/wangyingsm/Python-Data-Science-Handbook/blob/master/notebooks/03.07-Merge-and-Join.ipynb\"><img align=\"left\" src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open in Colab\" title=\"Open and Execute in Google Colaboratory\"></a>\n"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}