5 Ways to Merge Excel Sheets with Python

Whether you're managing large datasets, consolidating financial reports, or integrating different data sources, merging Excel sheets can be a tedious task if done manually. Fortunately, Python, with its powerful libraries like openpyxl, pandas, and xlwings, simplifies this process significantly. Here, we'll explore five effective methods to merge Excel sheets using Python, ensuring you can combine, manipulate, and analyze data from multiple spreadsheets with ease.

Method 1: Using Pandas

Pandas is renowned for its data manipulation capabilities, particularly in handling tabular data formats like those found in Excel files.

• Install Pandas: Start by ensuring Pandas is installed in your Python environment:

pip install pandas

To merge Excel sheets using Pandas:

import pandas as pd

# Load the Excel files
df1 = pd.read_excel('file1.xlsx', sheet_name='Sheet1')
df2 = pd.read_excel('file2.xlsx', sheet_name='Sheet1')

# Concatenate the DataFrames vertically
merged_df = pd.concat([df1, df2], ignore_index=True)

# Optionally, save the merged DataFrame to a new Excel file
merged_df.to_excel('merged_file.xlsx', index=False)

🔍 Note: This method assumes that the structure (column names) of the sheets being merged is consistent.

Method 2: Using Openpyxl

Openpyxl is ideal for reading, writing, and editing Excel files without having Excel installed.

• Install Openpyxl:

pip install openpyxl

Here's how you can merge Excel sheets using openpyxl:

from openpyxl import load_workbook

# Load workbooks
wb1 = load_workbook('file1.xlsx')
wb2 = load_workbook('file2.xlsx')

# Get the active sheet or specify the sheet name
sheet1 = wb1.active
sheet2 = wb2.active

# Copy data from sheet2 to sheet1
for row in sheet2.iter_rows(min_row=2, max_col=sheet2.max_column, max_row=sheet2.max_row):
    for cell in row:
        sheet1.cell(row=cell.row + sheet1.max_row, column=cell.column, value=cell.value)

# Save the modified workbook
wb1.save('merged_file.xlsx')

Method 3: Using Excel COM Interface

For those using Windows, you can leverage Excel's COM interface through Python's win32com module:

• Install the required module:

pip install pywin32

import win32com.client as win32

# Open Excel application
excel = win32.gencache.EnsureDispatch('Excel.Application')

# Open the workbooks
wb1 = excel.Workbooks.Open('file1.xlsx')
wb2 = excel.Workbooks.Open('file2.xlsx')

# Select the sheet to copy from and paste to
sheet1 = wb1.Sheets('Sheet1')
sheet2 = wb2.Sheets('Sheet1')

# Copy the entire range of sheet2 to sheet1
sheet2.UsedRange.Copy()
sheet1.Range('A' + str(sheet1.UsedRange.Rows.Count + 1)).Select()
excel.Selection.PasteSpecial()

# Clean up
wb1.Close(SaveChanges=True)
wb2.Close(SaveChanges=False)
excel.Application.Quit()

Method 4: Using Xlwings

Xlwings is another library that works with both Windows and macOS, utilizing Excel's COM interface or native AppleScript/UI scripting.

• Install Xlwings:

pip install xlwings

Here’s how to use Xlwings for merging sheets:

import xlwings as xw

# Open Excel files
wb1 = xw.Book('file1.xlsx')
wb2 = xw.Book('file2.xlsx')

# Copy data from sheet2 to sheet1
ws1 = wb1.sheets['Sheet1']
ws2 = wb2.sheets['Sheet1']

# Append data from sheet2 below the existing data in sheet1
ws1.range('A' + str(ws1.cells.last_cell.row)).options(transpose=True).value = ws2.used_range.value

# Save and close the workbooks
wb1.save('merged_file.xlsx')
wb1.close()
wb2.close()

Method 5: Custom Script with Python's Standard Library

For a more tailored approach, you can write a custom script using Python's standard library:

from openpyxl import load_workbook, Workbook
from openpyxl.worksheet.worksheet import Worksheet

def merge_sheets(file1, file2, output_file):
    # Load workbooks
    wb1 = load_workbook(filename=file1, read_only=True)
    wb2 = load_workbook(filename=file2, read_only=True)

    # Create a new workbook for merging
    merged_wb = Workbook()
    merged_sheet = merged_wb.active

    for sheet in [wb1.active, wb2.active]:
        for row in sheet.iter_rows(min_row=2):
            merged_row = []
            for cell in row:
                merged_row.append(cell.value)
            merged_sheet.append(merged_row)

    merged_wb.save(output_file)
    wb1.close()
    wb2.close()

merge_sheets('file1.xlsx', 'file2.xlsx', 'merged_file.xlsx')

The choice of method depends on various factors like the operating system, required data transformation, and the complexity of your Excel manipulation needs. Each approach offers its unique advantages:

• Pandas is excellent for complex data manipulation and is widely used in data science.
• Openpyxl provides flexibility for workbook and worksheet manipulation without requiring Excel.
• The Excel COM interface via win32com works seamlessly on Windows but is tied to Excel's version.
• Xlwings offers a unified API for both Windows and macOS, utilizing Excel's capabilities natively.
• Custom scripts provide the most control but require more coding effort.

When choosing a method, consider:

• Your Operating System: Some methods are OS-specific.
• Excel Installation: Libraries like openpyxl do not require Excel, while others do.
• Data Complexity: For complex transformations, Pandas might be the best choice due to its robust DataFrame operations.
• Performance: If dealing with very large files, you might need to optimize your approach for speed.

Merging Excel sheets can become a part of your data workflow, allowing you to:

• Aggregate data from different sources.
• Automate repetitive tasks.
• Enhance productivity by focusing on analysis rather than data preparation.

By integrating these methods into your Python scripts, you're not only streamlining your work but also leveraging Python's versatility in handling data from multiple sources. This approach ensures that your data management tasks are more manageable, less error-prone, and, importantly, automated, giving you more time to analyze and interpret the data for better decision-making.

What is the best library to use if I want to perform complex data transformations while merging Excel sheets?

+

For complex data transformations, Pandas is often the best choice due to its extensive DataFrame manipulation capabilities.

Can I use these methods on macOS?

+

Yes, you can use openpyxl, Pandas, and Xlwings on macOS. The Excel COM interface method is exclusive to Windows.

Do these methods require Excel to be installed on my computer?

+

No, with libraries like openpyxl and Pandas, Excel does not need to be installed to manipulate Excel files.