<jupyter_start><jupyter_text>Different methods for merging data frames pandas comes with a function merge to join datasets. It exists also a `join` method which is built on top of `merge`. It won't be discussed here. We will see here the various methods we can use in pandas to replicate SQL joins.All pictures in this notebook are taken from Christopher-Moffatt (see https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins). This article explains the SQL syntax for each type of join. This notebook can be seen as a translation of his work with pandas data frames.  Create datasetsLet's create two datasets to play with:<jupyter_code>import pandas as pd
df1 = pd.DataFrame({"City": ["Paris", "London", "New York", "Tokyo"],
                    "Population": [12475808, 12317800, 20182305, 42794714],
                    "Country": ["France", "UK", "USA", "Japan"]})
df1.head()
df2 = pd.DataFrame({"Country": ["France", "UK", "USA", "China"],
                    "Population": [67595000, 65110000, 324811000, 1376049000]})
df2.head()<jupyter_output><empty_output><jupyter_text>Different types of SQL joins   Inner join<jupyter_code>result = pd.merge(df1, df2, on="Country", suffixes=("_city", "_country"))
result.head()<jupyter_output><empty_output><jupyter_text>By default, `merge` is doing an inner join: only matching rows are kept. The `on` attribute enables to specify on which column the join is done. If both columns do not have the same name you can use `left_on` and `right_on` instead.As we have one column (Population) that exists in both data frames, it is possible to provide a `suffixes` attribute to make column names unique in the data frame resulting from `merge`.We can make this more explicit by using the `how` attribute and set its value to inner. This does not change anything as it is the default value.<jupyter_code>result = pd.merge(df1, df2, on="Country", suffixes=("_city", "_country"), how='inner')
result.head()<jupyter_output><empty_output><jupyter_text>Left join<jupyter_code>result = pd.merge(df1, df2, on="Country", suffixes=("_city", "_country"), how='left')
result.head()<jupyter_output><empty_output><jupyter_text>This time all rows from the first data frames are kept, even if no matching row has been found in the second data frame. Unmatched rows have NaN values for the columns coming from the second dataset.  Right join<jupyter_code>result = pd.merge(df1, df2, on="Country", suffixes=("_city", "_country"), how='right')
result.head()<jupyter_output><empty_output><jupyter_text>The right join does the opposite. All rows from the second data frame are kept even if no match is found.   Outer join<jupyter_code>result = pd.merge(df1, df2, on="Country", suffixes=("_city", "_country"), how='outer')
result.head()<jupyter_output><empty_output><jupyter_text>Here, every row are kept whatever their origin. Unmatched rows in other data frame report NaN values.  Outer excluding joinFor the next series of joins, we use an additional argument `indicator` which adds a column in the resulting data frame regarding the merge operation. the value of this column is either:- both: rows have been matched- left_only: row is unmatched and only appears in the first data frame- right_only: row is unmatched and only appears in the second data frame<jupyter_code>result = pd.merge(df1, df2, on="Country", suffixes=("_city", "_country"), how='outer', indicator=True)
result.head()<jupyter_output><empty_output><jupyter_text>With this new column it is easy to filter out the matched rows resulting from the outer join to have an outer excluding join:<jupyter_code>result = pd.merge(df1, df2, on="Country", suffixes=("_city", "_country"), how='outer', indicator=True)
result = result[result["_merge"] != "both"]
result.head()<jupyter_output><empty_output><jupyter_text>Only unmatched rows are kepts, whatever their origin.  Left excluding join<jupyter_code>result = pd.merge(df1, df2, on="Country", suffixes=("_city", "_country"), how='left', indicator=True)
result = result[result["_merge"] == "left_only"]
result.head()<jupyter_output><empty_output><jupyter_text>This only keeps the unmatched row from the first data frame.  Right excluding join<jupyter_code>result = pd.merge(df1, df2, on="Country", suffixes=("_city", "_country"), how='right', indicator=True)
result = result[result["_merge"] == "right_only"]
result.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11492164433340904, 'fraction_numerical': 0.021349080172609586, 'mean_word_length': 5.913657770800628, 'pattern_counts': {'":': 5, '<': 41, '<?xml version=': 0, '>': 41, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '300311', 'license': 'MIT', 'n_tokens_mistral': 1377, 'n_tokens_neox': 1297, 'n_words': 492, 'path': 'pandas/merging_dataframes.ipynb', 'repo_name': 'ccampguilhem/DataAnalysisRecipees', 'size': '4403'}