{"id":3649,"date":"2024-06-05T15:14:59","date_gmt":"2024-06-05T22:14:59","guid":{"rendered":"https:\/\/ioflood.com\/blog\/?p=3649"},"modified":"2024-06-05T20:02:23","modified_gmt":"2024-06-06T03:02:23","slug":"how-to-use-pandas-merge-with-dataframe-objects","status":"publish","type":"post","link":"https:\/\/ioflood.com\/blog\/how-to-use-pandas-merge-with-dataframe-objects\/","title":{"rendered":"How To Use Pandas Merge() with DataFrame Objects"},"content":{"rendered":"
\n
\"Graphic<\/figure>\n<\/div>\n

Combining datasets is essential for data analysis tasks at IOFLOOD<\/a>, and the pandas merge<\/code> function in Python simplifies this process significantly. Using our experience, we have created this article on the capabilities of Pandas Merge, so that developers and our customers can enhance data integration workflows on their dedicated cloud services<\/a>.<\/p>\n

This article serves as your comprehensive guide on how to use the Pandas Merge function.<\/strong> Whether you’re a novice just getting started or a seasoned data scientist honing your skills, this guide will help you master dataframe merging with pandas<\/a>.<\/p>\n

So, let’s set sail and explore the power of pandas merge!<\/p>\n

TL;DR: What is Pandas Merge in Python?<\/h2>\n

\n Pandas Merge<\/code> is a function in the Pandas library that allows you to combine two dataframes based on a common key. It is used with the basic syntax, dataframe1.merge(dataframe2, on='key')<\/code>. This powerful feature simplifies data analysis by organizing data from different sources into a single, structured dataframe. For a more in-depth understanding and advanced methods, continue reading the article.\n<\/p><\/blockquote>\n

Here’s an example:<\/p>\n

import pandas as pd\n\n# Define two dataframes\ndf1 = pd.DataFrame({'key': ['A', 'B', 'C', 'D'],\n                    'value': range(1, 5)})\n\ndf2 = pd.DataFrame({'key': ['B', 'D', 'D', 'E'],\n                    'value': range(5, 9)})\n\n# Merge dataframes on the 'key'\ndf_merged = df1.merge(df2, on='key')\n\nprint(df_merged)\n\n# Output:\n#  key  value_x  value_y\n# 0   B        2        5\n# 1   D        4        6\n# 2   D        4        7\n<\/code><\/pre>\n
In this example, two data frames (df1 and df2) are merged based on their common key. The resulting data frame includes rows with keys that are common to both data frames. The '_x'<\/code> and '_y'<\/code> suffixes indicate which dataframe the values came from.<\/p>\n
This merge used an inner join, which only includes common keys. Other types of pandas joins<\/a> (like outer, left, or right) can include keys not commonly found in either or both data frames.<\/p>\n
Basics of the Merge() Function<\/h2>\n
At its core, Pandas merge is a function that fuses two dataframes based on a shared key. To put it simply, consider it as a process of joining two tables in a database.<\/p>\n
The fundamental syntax is as follows:<\/p>\n
df_merged = df1.merge(df2, on='common_key')\n<\/code><\/pre>\n
In this snippet, df1<\/code> and df2<\/code> are the dataframes you intend to merge, and ‘common_key’ is the column they both share. The outcome, df_merged<\/code>, is a newly formed dataframe that comprises all the rows from df1<\/code> and df2<\/code> where the ‘common_key’ matches.<\/p>\n
Let’s illustrate this with another example. Assume you possess two dataframes, df1<\/code> and df2<\/code>, which encapsulate information about products and their respective prices. You can merge these dataframes to obtain a comprehensive view of each product along with its price:<\/p>\n
df1 = pd.DataFrame({'Product': ['A', 'B', 'C'], 'Category': ['Electronics', 'Clothing', 'Groceries']})\ndf2 = pd.DataFrame({'Product': ['A', 'B', 'C'], 'Price': [100, 200, 300]})\ndf_merged = df1.merge(df2, on='Product')\n\nprint(df1)\nprint(df2)\nprint(df_merged)\n<\/code><\/pre>\n
The expected outputs for the three print commands would be as follows:<\/p>\n
print(df1)\n\n# Output\n  Product     Category\n0       A  Electronics\n1       B     Clothing\n2       C    Groceries\n\nprint(df2)\n\n# Output\n  Product  Price\n0       A    100\n1       B    200\n2       C    300\n\nprint(df_merged)\n\n# Output\n  Product     Category  Price\n0       A  Electronics    100\n1       B     Clothing    200\n2       C    Groceries    300\n<\/code><\/pre>\n
In this example, each product is associated with a category in df1<\/code> and a price in df2<\/code>. The merged dataframe, df_merged<\/code>, provides a combined view, linking each product with both its category and price. The df_merged<\/code> dataframe now consolidates the product, its category, and its price, all under one roof.<\/p>\n
\n  While this demonstrates a simple application, pandas merge is capable of much more. It supports a variety of merges, including inner, outer, left, and right, each of which can yield different outcomes. An inner merge, for instance, only includes rows where the key is present in both dataframes, whereas an outer merge encompasses all rows from both dataframes, substituting missing values with NaN.\n<\/p><\/blockquote>\n
Beyond Basics: Merge Parameters<\/h2>\n
While the fundamental usage of pandas merge is simple, the function provides a plethora of parameters that enable more intricate and flexible merges.<\/p>\n
One such parameter is how<\/code>, which dictates the type of merge to be performed. It can take values ‘left’, ‘right’, ‘outer’, and ‘inner’, each resulting in a distinct output. Another pivotal parameter is on<\/code>, which designates the column or columns to join on. If you need to merge on multiple columns, you can pass a list of column names to this parameter.<\/p>\n
Left Merge<\/h3>\n
The ‘left’ merge gives you all the rows from the first (left) dataframe, along with any matching rows from the second (right) dataframe. If there is no match from df2, you will get NaN<\/code>.<\/p>\n
import pandas as pd\n\n# Define the dataframes\ndf1 = pd.DataFrame({'key': ['A', 'B', 'C', 'D'], 'value1': list(range(4))})\ndf2 = pd.DataFrame({'key': ['B', 'D', 'E', 'F'], 'value2': list(range(4,8))})\n\n# Perform and print the merge\nleft_merge = df1.merge(df2, on='key', how='left')\nprint(left_merge)\n<\/code><\/pre>\n
Output:<\/p>\n
  key  value1  value2\n0   A       0     NaN\n1   B       1     4.0\n2   C       2     NaN\n3   D       3     5.0\n<\/code><\/pre>\n
In this example, we can see that keys ‘E’ and ‘F’ from df2 aren’t included in the result since they don’t exist in df1 (left dataframe).<\/p>\n
Right Merge<\/h3>\n
The ‘right’ merge gives you all the rows from the second (right) dataframe, along with any matching rows from the first (left) dataframe.<\/p>\n
# Perform and print the merge\nright_merge = df1.merge(df2, on='key', how='right')\nprint(right_merge)\n<\/code><\/pre>\n
Output:<\/p>\n
  key  value1  value2\n0   B     1.0       4\n1   D     3.0       5\n2   E     NaN       6\n3   F     NaN       7\n<\/code><\/pre>\n
Here, any keys from df1 which don’t exist in df2 (e.g. ‘A’ and ‘C’) aren\u2019t returned in the result.<\/p>\n
Outer Merge<\/h3>\n
The ‘outer’ merge combines all rows from both dataframes, and fills in NaN<\/code> for missing matches on either side.<\/p>\n
# Perform and print the merge\nouter_merge = df1.merge(df2, on='key', how='outer')\nprint(outer_merge)\n<\/code><\/pre>\n
Output:<\/p>\n
  key  value1  value2\n0   A     0.0     NaN\n1   B     1.0     4.0\n2   C     2.0     NaN\n3   D     3.0     5.0\n4   E     NaN     6.0\n5   F     NaN     7.0\n<\/code><\/pre>\n
Here, you can see that all keys are included in the result. Where there’s no match from either the left or right dataframe, NaN<\/code> is filled in.<\/p>\n
Inner Merge<\/h3>\n
The ‘inner’ merge gives only the rows where there is a match in both dataframes.<\/p>\n
# Perform and print the merge\ninner_merge = df1.merge(df2, on='key', how='inner')\nprint(inner_merge)\n<\/code><\/pre>\n
Output:<\/p>\n
  key  value1  value2\n0   B       1       4\n1   D       3       5\n<\/code><\/pre>\n
In this type of merge, only keys ‘B’ and ‘D’ which are common in both dataframes are returned in the output.<\/p>\n
Multi index dataframes<\/h3>\n
Let’s dive into an example of a more complex merge, involving multi-index dataframes. Suppose we have the following two dataframes:<\/p>\n
df1 = pd.DataFrame({'key1': ['A', 'B', 'C'], 'key2': ['W', 'X', 'Y'], 'value': [1, 2, 3]})\ndf1.set_index(['key1', 'key2'], inplace=True)\n\ndf2 = pd.DataFrame({'key1': ['B', 'C', 'D'], 'key2': ['X', 'Y', 'Z'], 'value': [4, 5, 6]})\ndf2.set_index(['key1', 'key2'], inplace=True)\n<\/code><\/pre>\n
We can fuse these dataframes using the multi-index as follows:<\/p>\n
df_merged = pd.merge(df1, df2, left_index=True, right_index=True)\n\nprint(df1)\nprint(df2)\nprint(df_merged)\n<\/code><\/pre>\n
The print commands for the given Python code will generate the following output:<\/p>\n
print(df1)\n\n# Output\n           value\nkey1 key2\nA    W         1\nB    X         2\nC    Y         3\n\nprint(df2)\n\n# Output\n           value\nkey1 key2\nB    X         4\nC    Y         5\nD    Z         6\n\nprint(df_merged)\n\n# Output\n           value_x  value_y\nkey1 key2\nB    X           2        4\nC    Y           3        5\n<\/code><\/pre>\n
The merged dataframe, df_merged<\/code>, takes the intersection of the two dataframes, df1<\/code> and df2<\/code>, based on their multi-index (key1<\/code> and key2<\/code>). The suffixes '_x'<\/code> and '_y'<\/code> identify which dataframe the value<\/code> columns originated from.<\/p>\n
This results in a dataframe that includes rows where the multi-index matches in both dataframes.<\/p>\n
Best Practices of Pandas.merge()<\/h2>\n
While merging dataframes is typically a smooth process, it’s not uncommon to encounter merge conflicts, particularly when dealing with large and complex dataframes. As well, performance can be a concern when dealing with very large data structures<\/a>.<\/p>\n
In this section we go over some of the more common problems and some possible solutions:<\/p>\n
KeyError<\/h3>\n
A KeyError<\/code><\/a> usually surfaces when you’re trying to execute a merge where the key specified isn’t found in one or both of the dataframes involved in the operation. This could occur due to misspelling a key or by mistakenly offering a key that isn’t present in the dataframe.<\/p>\n
To avoid this issue, ensure you double-check the column names before performing the merge operation. In case the keys in both dataframes don’t have the same but correspond to each other, you can specify separate keys for each dataframe using left_on<\/code> and right_on<\/code> parameters.<\/p>\n
try:\n    # Attempt to merge on key\n    merged_df = df1.merge(df2, on='key')\nexcept KeyError:\n    # Handle the error\n    print('Key not found in one or both dataframes')\n<\/code><\/pre>\n
In the code provided above, the merge operation attempts to merge df1<\/code> and df2<\/code> on a column named ‘key’. If such a column doesn’t exist in one of the dataframes, a KeyError<\/code> will be triggered.<\/p>\n
Upon catching the error<\/a>, we print out an error message. To prevent this error, you should always ensure that the specified key exists in both dataframes. If the column exists but bears different names in each dataframe, you can use the left_on<\/code> and right_on<\/code> parameters of the merge function to specify each one independently.<\/p>\n
For instance, if the keys are named ‘key1’ in df1<\/code> and ‘key2’ in df2<\/code>, you can accomplish the merge like this:<\/p>\n
# Correct attempt to merge on different keys\nmerged_df = df1.merge(df2, left_on='key1', right_on='key2')\n<\/code><\/pre>\n
By ensuring your keys align or specifying them independently, you can bypass the KeyError<\/code> issue when merging dataframes.<\/p>\n
Missing Values<\/h3>\n
Merging on keys that have missing values might lead to unexpected results. When a key value is missing in one dataframe, the corresponding rows in the other dataframe might not be included in the merge.<\/p>\n
You can address this problem by changing the merge type. Using the how<\/code> parameter in the merge function allows you to pick a merge strategy that suits your needs.<\/p>\n
For example, using a ‘left’ merge ensures all rows from the left dataframe are included, even if there’s no matching key value in the right dataframe.<\/p>\n
# Attempt a left merge to include all rows from df1\nmerged_df = df1.merge(df2, on='key', how='left')\n<\/code><\/pre>\n
In the code snippet above, even if some values of ‘key’ exist in df1<\/code> but not in df2<\/code>, the result merged_df<\/code> will still have those rows from df1<\/code>. The columns of df2<\/code> in these rows will have ‘NaN’ values, indicating that no matching data was found in df2<\/code>.<\/p>\n
In essence, using ‘left’ in the how<\/code> parameter protects the rows of df1<\/code> from being excluded due to missing values. Keep this in mind when shaping your merge strategy to avoid lost data.<\/p>\n
\n  Using other types for how<\/code>, such as ‘right’, ‘outer’, or ‘inner’, will provide different results and should be chosen based on your specific data requirements. ‘right’ behaves similarly to ‘left’, but protects df2<\/code> instead, ‘outer’ includes all rows from both dataframes, and ‘inner’ includes only rows with keys in both dataframes.\n<\/p><\/blockquote>\n
Data Type Discrepancies<\/h3>\n
When you’re merging two dataframes on a particular key, differences in data types<\/a> between the key columns can cause problems. If the keys in your dataframes have different data types, pandas might not match the values correctly, leading to inaccurate merges.<\/p>\n
One way to address this problem is to ensure the key columns have the same data type in both dataframes prior to merging.<\/p>\n
# Convert the data type of key columns in both dataframes to string\ndf1['key'] = df1['key'].astype(str)\ndf2['key'] = df2['key'].astype(str)\n\n# Attempt to merge the dataframes on the key\nmerged_df = df1.merge(df2, on='key')\n<\/code><\/pre>\n
In the example above, the key<\/code> column in both dataframes is converted to the str<\/code> type using the astype<\/code> method. After this conversion, when the dataframes are merged, pandas can accurately match rows based on the key<\/code>, even if the original data types were different.<\/p>\n
\n  A variety of data type conversions may be necessary depending on the specific attributes of your dataframes, and this string conversion<\/a> is just one common example.\n<\/p><\/blockquote>\n
Cross-Validation of Merged Data<\/h3>\n
Once a merge operation is successfully completed, it’s necessary to validate that the merged dataframe meets your expectations. One way to do this is by cross-validating the resulting merged data.<\/p>\n
One simple verification method involves checking the shape of the merged dataframe, specifically its row count. The result should match your understanding based on the merge type and the number of matching keys in the original dataframes.<\/p>\n
Here’s how you perform this check:<\/p>\n
# Check the shape of the merged dataframe\nprint(\"Number of rows and columns in the merged dataframe: \", merged_df.shape)\n<\/code><\/pre>\n
In the code above, the shape<\/code> attribute of the dataframe gives us a tuple with the number of rows and columns. If the merge was done correctly, the number of rows should match our expectations based on the inputs and chosen merge type.<\/p>\n
Moreover, spot-checking some rows in the merged dataframe can help verify that the data is combined correctly. This involves randomly selecting some rows and manually verifying that the values across both original dataframes were correctly merged.<\/p>\n
# Spot-check some rows\nprint(\"Random sample of rows from the merged dataframe:n\", merged_df.sample(5))\n<\/code><\/pre>\n
In the code snippet above, sample(5)<\/code> is used to randomly select 5 rows from the merged dataframe. You can manually trace these records back to their original dataframes to confirm they’ve been combined correctly. Adjust the number in sample()<\/code> to check more or fewer rows as needed.<\/p>\n
\n  By running these checks, you can catch any inconsistencies or errors after the merge operation, ensuring the quality and reliability of your data manipulation.\n<\/p><\/blockquote>\n
Ensuring Data Integrity After Merging<\/h3>\n
Once your dataframes have been successfully merged, it’s essential to carry out data integrity checks. This step ensures the merge operation didn’t introduce any errors, and that the prepared data accurately reflects the intended analysis.<\/p>\n
Checking for Duplicate Rows<\/h4>\n
One check is to identify any duplicate rows. These could unintentionally inflate your data or cause skewed analyses.<\/p>\n
# Checking for duplicate rows in the merged dataframe\nduplicate_rows = merged_df.duplicated().sum()\nprint(f'There are {duplicate_rows} duplicated rows in the merged dataframe.')\n<\/code><\/pre>\n
In the code snippet above, the duplicated()<\/code> function is used to create a Boolean series that’s True<\/code> for every duplicated row and False<\/code> otherwise, with the sum()<\/code> function then tallying the True<\/code> values. If the result is greater than 0, you might need to further investigate why the duplicates arose and if they should be dropped<\/a>.<\/p>\n
Inspecting Merge Results<\/h4>\n
Another means of validation is cross-checking the merged information against known or expected values.<\/p>\n
# Checking the number of occurrences for each value in a specific column\nvalue_counts = merged_df['column'].value_counts()\nprint(value_counts)\n<\/code><\/pre>\n
In the above snippet, the value_counts()<\/code> function gives us the frequency of each unique value in the specified column of the merged dataframe. It’s a method of basic sanity checking\u2014ensuring that the data in the column aligns with expectations.<\/p>\n
Avoiding Unnecessary Sorts<\/h3>\n
When working with large dataframes, the computational cost of merge operations can be substantial. By default, pandas will try to sort the data while merging, which can be very time-consuming for large dataframes. You can avoid unnecessary computational effort by setting the sort<\/code> parameter to False<\/code>.<\/p>\n
Here’s an example showcasing this:<\/p>\n
# Create large dataframes\ndf1 = pd.DataFrame({'key': range(1000000), 'value': range(1000000)})\ndf2 = pd.DataFrame({'key': range(999999, -1, -1), 'value': range(1000000)})\n\n# Merge dataframes without sorting\nmerged_df = pd.merge(df1, df2, on='key', sort=False)\n<\/code><\/pre>\n
In the code snippet above, we created two large dataframes df1<\/code> and df2<\/code> each with a million entries. df1<\/code> is sorted in ascending order while df2<\/code> is sorted in descending order on ‘key’. By setting sort=False<\/code> while merging, we’re telling pandas not to sort the final dataframe on the ‘key’ column. Please note that the rows of merged_df<\/code> will appear in the order they appear in df1<\/code> (i.e., sorted on ‘key’ in ascending order) as pandas preserves the order of the left dataframe rows.<\/p>\n
This practice can significantly improve performance during merge operations, facilitating faster and more efficient data manipulation, especially with large-scale data.<\/p>\n
Alternative Methods and Techniques<\/h2>\n
There are additional pandas functions that work well for combining dataframes, with varying utility depending on your specific needs. Below, we’ll discuss the join<\/code><\/a> and concat<\/code> functions.<\/p>\n
\n  Each of these functions – merge<\/code>, join<\/code>, and concat<\/code> – has a unique use case in pandas, and understanding when and how to use each can significantly enhance your data manipulation capabilities in Python.\n<\/p><\/blockquote>\n
The Join Function<\/h3>\n
Pandas join<\/code> is a convenient method<\/a> to combine two dataframes based on their indexes or columns. It\u2019s essentially a way to join on indexes rather than columns.<\/p>\n
# Create two dataframes\ndf1 = pd.DataFrame({'A': ['A0', 'A1', 'A2']},\n                   index=['K0', 'K1', 'K2'])\n\ndf2 = pd.DataFrame({'B': ['B0', 'B1', 'B2']},\n                   index=['K0', 'K2', 'K3'])\n\n# Join the dataframes\njoined_df = df1.join(df2, how='outer')\n\nprint(joined_df)\n<\/code><\/pre>\n
Output:<\/p>\n
     A    B\nK0  A0   B0\nK1  A1  NaN\nK2  A2   B1\nK3 NaN   B2\n<\/code><\/pre>\n
In this code, we’ve created two dataframes, df1<\/code> and df2<\/code>, both with three entries but with different indexes. We’ve then used the join<\/code> function to combine them.<\/p>\n
Because we used how='outer'<\/code>, all keys from both dataframes are included in the output, with NaN filling in for missing data<\/a>. If we were to use how='inner'<\/code>, only the common keys, ‘K0’ and ‘K2’, would be included.<\/p>\n
The Concat Function<\/h3>\n
The concat<\/code> function in pandas<\/a> is used to append dataframes along a particular axis (either rows or columns). This can be handy when you have dataframes of similar structures and need to combine them.<\/p>\n
# Create two dataframes\ndf1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'],\n                    'B': ['B0', 'B1', 'B2']},\n                   index=['K0', 'K1', 'K2'])\n\ndf2 = pd.DataFrame({'A': ['A3', 'A4', 'A5'],\n                    'B': ['B3', 'B4', 'B5']},\n                   index=['K3', 'K4', 'K5'])\n\n# Concatenate the dataframes\nconcat_df = pd.concat([df1, df2])\n\nprint(concat_df)\n<\/code><\/pre>\n
Output:<\/p>\n
    A   B\nK0  A0  B0\nK1  A1  B1\nK2  A2  B2\nK3  A3  B3\nK4  A4  B4\nK5  A5  B5\n<\/code><\/pre>\n
In the code above, we’ve defined 2 dataframes, df1<\/code> and df2<\/code>, each with similar structure. Using pd.concat([df1, df2])<\/code>, we appended df2<\/code> below df1<\/code> along the row axis (axis=0), yielding a single dataframe that maintains the structure of the original dataframes.<\/p>\n
Remember that when concatenating along the column axis (axis=1), the dataframes must have the same row index. Otherwise, NaN values would be filled for non-overlapping indexes.<\/p>\n
Pandas Functions and Data Analysis<\/h2>\n
While merging is a pivotal aspect of data manipulation in pandas, it’s merely a single facet of the multifaceted gem that is pandas. This Python library offers an array of other functions that are instrumental for data manipulation and analysis.<\/p>\n
Functions like groupby<\/code>, pivot<\/code>, melt<\/code>, and apply<\/code> hold equal importance and when combined with merge, can facilitate more advanced data analysis.<\/p>\n
For instance, post merging two dataframes, you might require to group the data based on certain parameters to execute aggregated calculations.<\/p>\n
Here, the groupby<\/code> function<\/a> proves to be a valuable tool. Similarly, the pivot<\/code> function can be utilized to reshape the data, simplifying the analysis process.<\/p>\n\n\n\n\n\n\n\n\n
Function<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
groupby<\/code><\/td>\nGroup the data based on certain parameters to execute aggregated calculations.<\/td>\n<\/tr>\n
pivot<\/code><\/td>\nReshape the data, simplifying the analysis process.<\/td>\n<\/tr>\n
melt<\/code><\/td>\nTransform or reshape data.<\/td>\n<\/tr>\n
apply<\/code><\/td>\nApply a function along an axis of the DataFrame. | When these functions are used in tandem with merge, they can significantly enhance your data analysis capabilities.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Pandas plays a central role in data analysis in Python<\/a>. It offers a flexible and efficient data structure (the DataFrame) along with an extensive range of functions for data manipulation and analysis. Whether your task involves cleaning data, transforming data, visualizing data, or building models, pandas is a reliable ally.<\/p>\n
Other Data Handling Libraries<\/h2>\n
While pandas is a formidable library for data manipulation in Python, it’s not the only tool available. Other libraries like NumPy, SciPy, and Dask offer different functionalities for data manipulation and analysis.<\/p>\n