<jupyter_start><jupyter_text>*This notebook contains an excerpt from the [Python Data Science Handbook](http://shop.oreilly.com/product/0636920034919.do) by <NAME>; the content is available [on GitHub](https://github.com/jakevdp/PythonDataScienceHandbook).*  Combining Datasets: Merge and Join<jupyter_code>import pandas as pd
import numpy as np

class display(object):
    """Display HTML representation of multiple objects"""
    template = """<div style="float: left; padding: 10px;">
    <p style='font-family:"Courier New", Courier, monospace'>{0}</p>{1}
    </div>"""
    def __init__(self, *args):
        self.args = args
        
    def _repr_html_(self):
        return '\n'.join(self.template.format(a, eval(a)._repr_html_())
                         for a in self.args)
    
    def __repr__(self):
        return '\n\n'.join(a + '\n' + repr(eval(a))
                           for a in self.args)<jupyter_output><empty_output><jupyter_text>Categories of JoinsThe ``pd.merge()`` function implements a number of types of joins: the *one-to-one*, *many-to-one*, and *many-to-many* joins.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.Here we will show simple examples of the three types of merges, and discuss detailed options further below.  One-to-one joins<jupyter_code>df1 = pd.DataFrame({'employee': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'group': ['Accounting', 'Engineering', 'Engineering', 'HR']})
df2 = pd.DataFrame({'employee': ['Lisa', 'Bob', 'Jake', 'Sue'],
                    'hire_date': [2004, 2008, 2012, 2014]})
display('df1', 'df2')
df3 = pd.merge(df1, df2)
df3
#pd.concat([df1, df2], axis=1)<jupyter_output><empty_output><jupyter_text>Many-to-one joins Many-to-one joins are joins in which one of the two key columns contains duplicate entries.For the many-to-one case, the resulting ``DataFrame`` will preserve those duplicate entries as appropriate.Consider the following example of a many-to-one join:<jupyter_code>df4 = pd.DataFrame({'group': ['Accounting', 'Engineering', 'HR'],
                    'supervisor': ['Carly', 'Guido', 'Steve']})
display('df3', 'df4', 'pd.merge(df3, df4)')<jupyter_output><empty_output><jupyter_text>Many-to-many joins<jupyter_code>df5 = pd.DataFrame({'group': ['Accounting', 'Accounting',
                              'Engineering', 'Engineering', 'HR', 'HR'],
                    'skills': ['math', 'spreadsheets', 'coding', 'linux',
                               'spreadsheets', 'organization']})
display('df1', 'df5', "pd.merge(df1, df5)")<jupyter_output><empty_output><jupyter_text>Specification of the Merge Key  The ``on`` keywordMost 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:<jupyter_code>display('df1', 'df2', "pd.merge(df1, df2, on='employee')")<jupyter_output><empty_output><jupyter_text>The ``left_on`` and ``right_on`` keywordsAt 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".In this case, we can use the ``left_on`` and ``right_on`` keywords to specify the two column names:<jupyter_code>df3 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'salary': [70000, 80000, 120000, 90000]})
display('df1', 'df3', 'pd.merge(df1, df3, left_on="employee", right_on="name")')
pd.merge(df1, df3, left_on="employee", right_on="name").drop('name', axis=1)<jupyter_output><empty_output><jupyter_text>The ``left_index`` and ``right_index`` keywordsSometimes, rather than merging on a column, you would instead like to merge on an index.For example, your data might look like this:<jupyter_code>df1a = df1.set_index('employee')
df2a = df2.set_index('employee')
display('df1a', 'df2a')
display('df1a', 'df2a',
        "pd.merge(df1a, df2a, left_index=True, right_index=True)")<jupyter_output><empty_output><jupyter_text>For convenience, ``DataFrame``s implement the ``join()`` method, which performs a merge that defaults to joining on indices:<jupyter_code>display('df1a', 'df2a', 'df1a.join(df2a)')<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>display('df1a', 'df3', "pd.merge(df1a, df3, left_index=True, right_on='name')")<jupyter_output><empty_output><jupyter_text>Specifying Set Arithmetic for Joins<jupyter_code>df6 = pd.DataFrame({'name': ['Peter', 'Paul', 'Mary'],
                    'food': ['fish', 'beans', 'bread']},
                   columns=['name', 'food'])
df7 = pd.DataFrame({'name': ['Mary', 'Joseph'],
                    'drink': ['wine', 'beer']},
                   columns=['name', 'drink'])
display('df6', 'df7', 'pd.merge(df6, df7)')
pd.merge(df6, df7, how='inner')<jupyter_output><empty_output><jupyter_text>Other options for the ``how`` keyword are ``'outer'``, ``'left'``, and ``'right'``.An *outer join* returns a join over the union of the input columns, and fills in all missing values with NAs:<jupyter_code>display('df6', 'df7', "pd.merge(df6, df7, how='outer')")<jupyter_output><empty_output><jupyter_text>The *left join* and *right join* return joins over the left entries and right entries, respectively.For example:<jupyter_code>display('df6', 'df7', "pd.merge(df6, df7, how='left')")
display('df6', 'df7', "pd.merge(df6, df7, how='right')")<jupyter_output><empty_output><jupyter_text>Overlapping Column Names: The ``suffixes`` Keyword<jupyter_code>df8 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'rank': [1, 2, 3, 4]})
df9 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'rank': [3, 1, 4, 2]})
display('df8', 'df9', 'pd.merge(df8, df9, on="name")')
display('df8', 'df9', 'pd.merge(df8, df9, on="name", suffixes=["_L", "_R"])')<jupyter_output><empty_output><jupyter_text>Pandas and SQLIf data resides in a relational database, it is possible to query the data using SQL<jupyter_code># first install and import the database driver. 
# This depends on the database. For the sake of illustration we use
# the built-in sqlite database.
import sqlite3
# second, establish a connection
connection = sqlite3.connect('data/ClassicModels.db')
# third, query using SQL
# for example, 
pd.read_sql("SELECT * FROM Employees WHERE officeCode = 1", connection)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1618830675778284, 'fraction_numerical': 0.02217160212604404, 'mean_word_length': 4.762029746281715, 'pattern_counts': {'":': 0, '<': 62, '<?xml version=': 0, '>': 62, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '558440', 'license': 'MIT', 'n_tokens_mistral': 2299, 'n_tokens_neox': 2173, 'n_words': 614, 'path': '20-introduction_joining_datasets_merge_join.ipynb', 'repo_name': 'hanisaf/advanced-data-management-and-analytics', 'size': '6594'}