Merging CSV files in Python

/ Python / By

Merging CSV files in Python is a common task in data analysis and processing. Whether you’re combining datasets from multiple sources or consolidating information, Python’s built-in csv module provides a powerful and flexible way to handle this. This guide will teach you how to create a Python function to merge CSV files, even when they have different headers, ensuring you don’t lose any valuable data.

1. The Challenge: Merging CSV Files with Different Headers

CSV (Comma-Separated Values) files are a standard format for storing tabular data. However, merging them can be tricky when the headers (column names) don’t match perfectly. Different files might have:

  • Different column order: The same fields might appear in a different sequence.
  • Missing columns: One file might have additional fields not present in others.

2. Python’s csv Module: Your Merging Toolkit

The csv module provides classes for reading and writing CSV files:

  • csv.DictReader: Reads CSV files into dictionaries, where keys are the field names.
  • csv.DictWriter: Writes dictionaries to CSV files, ensuring all specified fields are included.

3. Building the CSV Merger: A Step-by-Step Approach

import csv

def merge_csv(file_paths, output_path):
    fieldnames = set()
    for file_path in file_paths:
        with open(file_path, 'r') as file:
            reader = csv.DictReader(file)
            fieldnames.update(reader.fieldnames) 

    with open(output_path, 'w', newline='') as outfile:
        writer = csv.DictWriter(outfile, fieldnames=list(fieldnames))
        writer.writeheader()
        for file_path in file_paths:
            with open(file_path, 'r') as file:
                reader = csv.DictReader(file)
                for row in reader:
                    writer.writerow(row) 

#Example Usage:
merge_csv(['class1.csv', 'class2.csv'], 'all_students.csv')

Explanation:

  1. Gather Field Names: Create a set to store all unique field names from the input files.
  2. Open Output File: Use DictWriter to write to the output file, specifying the collected field names.
  3. Write Header: Write the header row to the output file.
  4. Iterate and Write: Read each input file again using DictReader, and write each row to the output file. Missing fields will be filled with empty values.

4. Key Takeaways: Efficient and Robust CSV Merging

  • Handles Mismatched Headers: Works even if the input files have different column names or orders.
  • Preserves All Fields: Ensures no data is lost during the merge.
  • Flexible: Adapts to different CSV file structures.

Frequently Asked Questions (FAQ)

1. Can I merge CSV files with different delimiters (e.g., tabs instead of commas)?

Yes, specify the delimiter argument when creating the DictReader and DictWriter objects.

2. How can I handle duplicate rows in the merged file?

You can use a set to keep track of unique rows while iterating over the input files.

3. Can I perform data transformations or calculations while merging CSV files?

Yes, you can modify the rows within the loop before writing them to the output file.

4. Are there any performance considerations for merging very large CSV files?

For extremely large files, consider using the pandas library, which provides optimized tools for working with tabular data.