\n", "

{0}

{1}\n", "
\"\"\"\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", "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", "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." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Categories of Joins\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." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### One-to-one joins\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:" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df1

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeegroup
0BobAccounting
1JakeEngineering
2LisaEngineering
3SueHR
\n", "
\n", "
\n", "
\n", "

df2

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeehire_date
0Lisa2004
1Bob2008
2Jake2012
3Sue2014
\n", "
\n", "
" ], "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:" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeegrouphire_date
0BobAccounting2008
1JakeEngineering2012
2LisaEngineering2004
3SueHR2014
\n", "
" ], "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)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Many-to-one joins" ] }, { "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:" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df3

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeegrouphire_date
0BobAccounting2008
1JakeEngineering2012
2LisaEngineering2004
3SueHR2014
\n", "
\n", "
\n", "
\n", "

df4

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
groupsupervisor
0AccountingCarly
1EngineeringGuido
2HRSteve
\n", "
\n", "
\n", "
\n", "

pd.merge(df3, df4)

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeegrouphire_datesupervisor
0BobAccounting2008Carly
1JakeEngineering2012Guido
2LisaEngineering2004Guido
3SueHR2014Steve
\n", "
\n", "
" ], "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." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Many-to-many joins" ] }, { "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:" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df1

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeegroup
0BobAccounting
1JakeEngineering
2LisaEngineering
3SueHR
\n", "
\n", "
\n", "
\n", "

df5

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
groupskills
0Accountingmath
2Engineeringcoding
3Engineeringlinux
5HRorganization
\n", "
\n", "
\n", "
\n", "

pd.merge(df1, df5)

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeegroupskills
0BobAccountingmath
2JakeEngineeringcoding
3JakeEngineeringlinux
4LisaEngineeringcoding
5LisaEngineeringlinux
7SueHRorganization
\n", "
\n", "
\n", "

df1

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeegroup
0BobAccounting
1JakeEngineering
2LisaEngineering
3SueHR
\n", "
\n", "
\n", "
\n", "

df2

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeehire_date
0Lisa2004
1Bob2008
2Jake2012
3Sue2014
\n", "
\n", "
\n", "
\n", "

pd.merge(df1, df2, on='employee')

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeegrouphire_date
0BobAccounting2008
1JakeEngineering2012
2LisaEngineering2004
3SueHR2014
\n", "
\n", "
" ], "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." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### The ``left_on`` and ``right_on`` keywords\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:" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df1

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeegroup
0BobAccounting
1JakeEngineering
2LisaEngineering
3SueHR
\n", "
\n", "
\n", "
\n", "

df3

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesalary
0Bob70000
1Jake80000
2Lisa120000
3Sue90000
\n", "
\n", "
\n", "
\n", "

pd.merge(df1, df3, left_on=\"employee\", right_on=\"name\")

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeegroupnamesalary
0BobAccountingBob70000
1JakeEngineeringJake80000
2LisaEngineeringLisa120000
3SueHRSue90000
\n", "
\n", "
" ], "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:" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employeegroupsalary
0BobAccounting70000
1JakeEngineering80000
2LisaEngineering120000
3SueHR90000
\n", "
" ], "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", "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:" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df1a

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
group
employee
BobAccounting
JakeEngineering
LisaEngineering
SueHR
\n", "
\n", "
\n", "
\n", "

df2a

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
hire_date
employee
Lisa2004
Bob2008
Jake2012
Sue2014
\n", "
\n", "
" ], "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()``:" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df1a

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
group
employee
BobAccounting
JakeEngineering
LisaEngineering
SueHR
\n", "
\n", "
\n", "
\n", "

df2a

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
hire_date
employee
Lisa2004
Bob2008
Jake2012
Sue2014
\n", "
\n", "
\n", "
\n", "

pd.merge(df1a, df2a, left_index=True, right_index=True)

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
grouphire_date
employee
LisaEngineering2004
BobAccounting2008
JakeEngineering2012
SueHR2014
\n", "
\n", "
" ], "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", "Lisa Engineering 2004\n", "Bob Accounting 2008\n", "Jake Engineering 2012\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:" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df1a

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
group
employee
BobAccounting
JakeEngineering
LisaEngineering
SueHR
\n", "
\n", "
\n", "
\n", "

df2a

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
hire_date
employee
Lisa2004
Bob2008
Jake2012
Sue2014
\n", "
\n", "
\n", "
\n", "

df1a.join(df2a)

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
grouphire_date
employee
BobAccounting2008
JakeEngineering2012
LisaEngineering2004
SueHR2014
\n", "
\n", "
" ], "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:" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df1a

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
group
employee
BobAccounting
JakeEngineering
LisaEngineering
SueHR
\n", "
\n", "
\n", "
\n", "

df3

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesalary
0Bob70000
1Jake80000
2Lisa120000
3Sue90000
\n", "
\n", "
\n", "
\n", "

pd.merge(df1a, df3, left_index=True, right_on='name')

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
groupnamesalary
0AccountingBob70000
1EngineeringJake80000
2EngineeringLisa120000
3HRSue90000
\n", "
\n", "
" ], "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." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Specifying Set Arithmetic for Joins" ] }, { "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:" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df6

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namefood
0Peterfish
1Paulbeans
\n", "
\n", "
\n", "
\n", "

df7

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namedrink
0Marywine
1Josephbeer
\n", "
\n", "
\n", "
\n", "

pd.merge(df6, df7)

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namefooddrink
\n", "
\n", "
" ], "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\"``:" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namefooddrink
\n", "
" ], "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:" ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df6

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namefood
0Peterfish
1Paulbeans
\n", "
\n", "
\n", "
\n", "

df7

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namedrink
0Marywine
1Josephbeer
\n", "
\n", "
\n", "
\n", "

pd.merge(df6, df7, how='outer')

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namefooddrink
0PeterfishNaN
1PaulbeansNaN
3JosephNaNbeer
\n", "
\n", "
" ], "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:" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df6

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namefood
0Peterfish
1Paulbeans
\n", "
\n", "
\n", "
\n", "

df7

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namedrink
0Marywine
1Josephbeer
\n", "
\n", "
\n", "
\n", "

pd.merge(df6, df7, how='left')

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namefooddrink
0PeterfishNaN
1PaulbeansNaN
\n", "
\n", "
" ], "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." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Overlapping Column Names: The ``suffixes`` Keyword" ] }, { "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:" ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df8

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namerank
0Bob1
1Jake2
2Lisa3
3Sue4
\n", "
\n", "
\n", "
\n", "

df9

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namerank
0Bob3
1Jake1
2Lisa4
3Sue2
\n", "
\n", "
\n", "
\n", "

pd.merge(df8, df9, on=\"name\")

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namerank_xrank_y
0Bob13
1Jake21
2Lisa34
3Sue42
\n", "
\n", "
" ], "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:" ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "

df8

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namerank
0Bob1
1Jake2
2Lisa3
3Sue4
\n", "
\n", "
\n", "
\n", "

df9

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namerank
0Bob3
1Jake1
2Lisa4
3Sue2
\n", "
\n", "
\n", "
\n", "

pd.merge(df8, df9, on=\"name\", suffixes=[\"_L\", \"_R\"])

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namerank_Lrank_R
0Bob13
1Jake21
2Lisa34
3Sue42
\n", "
\n", "
\n", "

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
state/regionagesyearpopulation
0ALunder1820121117489.0
1ALtotal20124817528.0
2ALunder1820101130966.0
3ALtotal20104785570.0
4ALunder1820111125763.0
\n", "
\n", "
\n", "
\n", "

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
statearea (sq. mi)
0Alabama52423
2Arizona114006
3Arkansas53182
4California163707
\n", "
\n", "
\n", "
\n", "

\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
stateabbreviation
0AlabamaAL
2ArizonaAZ
3ArkansasAR
4CaliforniaCA
\n", "
\n", "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
state/regionagesyearpopulationstate
0ALunder1820121117489.0Alabama
1ALtotal20124817528.0Alabama
2ALunder1820101130966.0Alabama
3ALtotal20104785570.0Alabama
4ALunder1820111125763.0Alabama
\n", "
" ], "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) # drop duplicate info\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:" ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "collapsed": false }, "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!" ] }, { "cell_type": "code", "execution_count": 23, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
state/regionagesyearpopulationstate
2448PRunder181990NaNNaN
2449PRtotal1990NaNNaN
2450PRtotal1991NaNNaN
2451PRunder181991NaNNaN
2452PRtotal1993NaNNaN
\n", "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
state/regionagesyearpopulationstatearea (sq. mi)
0ALunder1820121117489.0Alabama52423.0
1ALtotal20124817528.0Alabama52423.0
2ALunder1820101130966.0Alabama52423.0
3ALtotal20104785570.0Alabama52423.0
4ALunder1820111125763.0Alabama52423.0
\n", "
" ], "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:" ] }, { "cell_type": "code", "execution_count": 27, "metadata": { "collapsed": false }, "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:" ] }, { "cell_type": "code", "execution_count": 28, "metadata": { "collapsed": false }, "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:" ] }, { "cell_type": "code", "execution_count": 29, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
state/regionagesyearpopulationstatearea (sq. mi)
0ALunder1820121117489.0Alabama52423.0
1ALtotal20124817528.0Alabama52423.0
2ALunder1820101130966.0Alabama52423.0
3ALtotal20104785570.0Alabama52423.0
4ALunder1820111125763.0Alabama52423.0
\n", "
" ], "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)):" ] }, { "cell_type": "code", "execution_count": 30, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
state/regionagesyearpopulationstatearea (sq. mi)
3ALtotal20104785570.0Alabama52423.0