Merging Data Frames Without Inner Intersection: A Deep Dive into Pandas

In the world of data science, merging data frames is a common operation that can be used to combine information from multiple sources. However, when dealing with data frames that have an inner intersection, things can get tricky. In this article, we’ll explore how to merge three data frames without their inner intersection using the pandas library in Python.

Understanding the Problem

The problem statement presents three data frames: energy, GDP, and ScimEn. The first two data frames have an inner intersection, meaning that some rows are common to both. We need to merge these three data frames while ignoring the common rows.

The task is to find out how many rows were lost when merging these three tables based on the first 15 ranks.

Merging Data Frames with Inner Intersection

To solve this problem, we’ll start by understanding how to merge two data frames using pandas. The pd.merge function takes two data frames and merges them based on a common column. By default, it performs an inner join, which means that only rows with matching values in both data frames are included in the result.

energy_gdp = pd.merge(energy, GDP, how='inner', right_on='Country', left_on='Country')

In this example, we’re merging energy and GDP based on the Country column. The how='inner' parameter ensures that only rows with matching values in both data frames are included in the result.

Merging Data Frames Without Inner Intersection

To merge three data frames without their inner intersection, we can use a different approach. We’ll first select the common columns from all three data frames and then perform an outer join on these selected columns.

merged_df1 = pd.merge(left=ScimEn, right=GDP_last10_years, how='left', left_on='Country', right_on='Country')

In this example, we’re merging ScimEn and GDP_last10_years based on the Country column. The how='left' parameter ensures that all rows from both data frames are included in the result, even if there’s no match.

Merging Data Frames with Replaced Values

The solution provided in the question includes replacing some values in the data frames using dictionaries. These replaced values need to be taken into account when merging the data frames.

replacement1 = {"Republic of Korea": "South Korea", "United States of America": "United States", "United Kingdom of Great Britain and Northern Ireland": "United Kingdom", "China, Hong Kong Special Administrative Region": "Hong Kong"}
energy['Country'].replace(replacement1, inplace=True)

In this example, we’re replacing some values in the energy data frame using a dictionary. The inplace=True parameter ensures that these replacements are applied directly to the original data frame.

Merging Data Frames and Dropping Unwanted Columns

When merging data frames, it’s common to drop unwanted columns or rows to simplify the result.

remove_cols = ['Country Code', 'Indicator Name', 'Indicator Code']
merged_all_df.drop(remove_cols, axis=1, inplace=True)

In this example, we’re dropping some unwanted columns from the merged_all_df data frame using a list of column names. The axis=1 parameter ensures that these operations are applied to the columns.

Using the Solution

Now that we’ve explored how to merge three data frames without their inner intersection, let’s use this knowledge to solve the problem presented in the question.

def answer_one():
    energy = pd.read_excel('Energy+Indicators.xls')
    energy = energy[17:244].reset_index(drop=True)
    energy.drop(energy.columns[0:2], axis=1, inplace=True)
    energy.rename(columns={energy.columns[0]:'Country', energy.columns[1]:'Energy Supply', energy.columns[2]:'Energy Supply per Capita', energy.columns[3]:'% Renewable'}, inplace=True)
    energy.replace('...', np.NaN, inplace=True)
    energy['Energy Supply'] = energy['Energy Supply'] * 1000000
    energy['Country'] = energy['Country'].str.replace('\d+','')
    
    # Replace values in the Country column
    replacement1 = {"Republic of Korea": "South Korea", "United States of America": "United States", "United Kingdom of Great Britain and Northern Ireland": "United Kingdom", "China, Hong Kong Special Administrative Region": "Hong Kong"}
    energy['Country'].replace(replacement1, inplace=True)
    
    # Replace values in the Country column (continued)
    energy['Country'] = energy['Country'].str.replace(r" \(.*\)","")
    
    GDP = pd.read_csv('world_bank.csv', skiprows=4)
    GDP.rename(columns={'Country Name':'Country'}, inplace=True)
    
    replacement2 = {"Korea, Rep.": "South Korea", "Iran, Islamic Rep.": "Iran", "Hong Kong SAR, China": "Hong Kong"}
    GDP['Country'].replace(replacement2, inplace=True)
    
    ScimEn = pd.read_excel('scimagojr-3.xlsx')
    
    # Create a copy of the GDP data frame with only the last 10 years
    GDP_last10_years = GDP.drop(GDP.columns[4:-10], axis=1)
    
    # Merge ScimEn and GDP_last10_years based on the Country column
    merged_df1 = pd.merge(left=ScimEn, right=GDP_last10_years, how='left', left_on='Country', right_on='Country')
    
    # Merge energy with merged_df1 based on the Country column
    merged_all_df_big = pd.merge(left=merged_df1, right=energy, how='left', left_on='Country', right_on='Country')
    
    # Select only the first 15 rows of merged_all_df_big
    merged_all_df = merged_all_df_big.head(15)
    
    # Drop unwanted columns from merged_all_df
    remove_cols = ['Country Code', 'Indicator Name', 'Indicator Code']
    merged_all_df.drop(remove_cols, axis=1, inplace=True)
    
    # Set the Country column as the index of merged_all_df
    merged_all_df.set_index('Country', inplace=True)
    
    return merged_all_df

# Call the function to get the final answer
result = answer_one()
print(result)

Last modified on 2023-11-15