Merging Multiple CSV Files Line by Line in Python

In this article, we’ll explore how to merge multiple CSV files line by line using Python. We’ll delve into the process of combining dataframes from separate CSV files and provide a step-by-step guide on how to achieve this.

Introduction

Merging multiple CSV files can be an essential task when working with large datasets. In this article, we’ll focus on merging these files in a way that preserves the original order of rows and columns.

Requirements

To complete this tutorial, you’ll need:

Python installed on your system (preferably the latest version)
The pandas library for data manipulation and analysis
Access to the CSV files you want to merge

Step 1: Installing Required Libraries

Before we begin, ensure that you have the necessary libraries installed. If you haven’t already, install the pandas library using pip:

pip install pandas

Step 2: Loading Individual Dataframes

To start merging your CSV files, you’ll need to load each individual dataframe into a Python variable. This can be achieved using the pd.read_csv() function provided by pandas.

For this example, let’s assume we have three separate CSV files named ‘file1.csv’, ‘file2.csv’, and ‘file3.csv’. We’ll create variables for each file:

import pandas as pd

# Load individual dataframes
df1 = pd.read_csv('file1.csv')
df2 = pd.read_csv('file2.csv')
df3 = pd.read_csv('file3.csv')

print(df1)
print(df2)
print(df3)

Step 3: Merging Dataframes

Now that we have loaded our individual dataframes, it’s time to merge them together. We can achieve this using the pd.concat() function provided by pandas.

Here’s an example of how you might merge your three dataframes:

# Concatenate individual dataframes along axis 0 (i.e., rows)
merged_df = pd.concat([df1, df2, df3])

print(merged_df)

However, as we’ll explore in more detail later on, simply concatenating the dataframes might not produce the desired output. We may need to sort the resulting dataframe based on certain criteria.

Step 4: Sorting Dataframe Based on Index

To preserve the original order of rows from each individual CSV file, you can use the sort_index() method provided by pandas.

Here’s how you could modify our previous example to include this step:

# Sort the merged dataframe based on its index (i.e., row numbers)
sorted_merged_df = pd.concat([df1, df2, df3], axis=0).sort_index()

print(sorted_merged_df)

Step 5: Saving Merged Dataframe as New CSV File

Once we’ve successfully merged our individual dataframes and sorted the resulting dataframe, it’s time to save this new file as a separate CSV file. We can achieve this using the to_csv() function provided by pandas.

Here’s how you could modify your previous example to include this step:

# Save the merged and sorted dataframe to a new CSV file
sorted_merged_df.to_csv('merged_output.csv', index=False)

Step 6: Additional Tips and Considerations

Handling Missing Values: When merging dataframes, you might encounter missing values in your dataset. To handle this situation effectively, consider using the fillna() function provided by pandas.
Data Type Conversion: Be mindful of data types when working with merged datasets. Ensure that all columns have consistent data types to avoid any potential errors or inconsistencies in your analysis.
Handling Duplicate Rows: If you’re dealing with large datasets containing duplicate rows, consider using the drop_duplicates() function provided by pandas to remove these duplicates.

Example Use Case: Merging CSV Files Line by Line

Suppose we want to merge multiple CSV files line by line and write the result to a new CSV file. We can achieve this as follows:

import pandas as pd
import glob

# Define the path to our individual CSV files
csv_path = 'C:/Users/username/Documents/csv_files/'

# Use glob.glob() to find all CSV files in the specified directory
all_csv_files = glob.glob(csv_path + '*.csv')

# Initialize an empty list to store our merged dataframes
merged_dataframes = []

for filename in all_csv_files:
    # Load each individual dataframe into a Python variable
    df = pd.read_csv(filename)
    
    # Append the loaded dataframe to our list of merged dataframes
    merged_dataframes.append(df)

# Concatenate our list of merged dataframes along axis 0 (i.e., rows)
merged_df = pd.concat(merged_dataframes, axis=0).sort_index()

# Save the merged dataframe as a new CSV file
merged_df.to_csv('merged_output.csv', index=False)

By following these steps and tips, you should now be able to merge multiple CSV files line by line using Python.

Last modified on 2024-11-07