How to merge two CSV files by specific columns using Pandas in Python?

CSV (Comma Separated Values) files are widely used to store and exchange data in a simple format. In many data processing tasks, there is a need to merge two or more CSV files based on specific columns. Fortunately, this can be easily achieved using the Pandas library in Python.

In this article, we will learn how to merge two CSV files by specific columns using Pandas in Python.

What is the Pandas library?

Pandas is an open source library for Python message control and inspection. It provides tools for working with structured data (such as tabular, time series, and multidimensional data) and high-performance data structures. Pandas is widely used in finance, data science, machine learning, and other fields that require data manipulation.

Steps to merge two CSV files by specific columns in Python

Here are the complete steps to merge two CSV files by specific columns in Python using Pandas library -

Step 1: Import the Pandas library

The first step to merge two CSV files is to import the pandas library. Pandas is a powerful Python data analysis library. It provides data structures for efficient storage and manipulation of large data sets. To use Pandas, we first need to import it into a Python program. We can do this using the following command -

import pandas as pd

Step 2: Read the CSV file

Our next step is to read the two CSV files we want to merge. We can read the CSV file into a Pandas DataFrame using Pandas’ read_csv() function. We need to provide the file path or URL of the CSV file as argument to the read_csv() function. For example -

df1 = pd.read_csv('file1.csv')
df2 = pd.read_csv('file2.csv')

Step 3: Merge CSV Files

After reading the CSV files into Pandas DataFrames, it is now time to merge them based on specific columns using the merge() function. The merge() function takes two DataFrames as input and merges them based on common columns. This is the basic syntax -

merged_df = pd.merge(df1, df2, on='column_name')

In this example, merged_df is a new DataFrame containing the merged data of df1 and df2. The on parameter specifies the name of the common column that merges the two DataFrames.

For example, assume we have two CSV files sales.csv and customers.csv. Both files contain a column named CustomerID. We want to merge two files based on the CustomerID column. We can do this -

sales = pd.read_csv('sales.csv')
customers = pd.read_csv('customers.csv')
merged_df = pd.merge(sales, customers, on='CustomerID')

Step 4: Handle missing values ​​(if any)

When merging two CSV files based on specific columns, there may be missing values ​​in the merged DataFrame. These missing values ​​may occur if there is no matching value in the corresponding column of one of the CSV files.

To handle missing values, we can use Pandas’ fillna() function to replace them with default or calculated values. For example, we can use the following code to replace missing values ​​with the string "Unknown" -

merged_df.fillna('Unknown', inplace=True)

In this example, we use the fillna() function to replace missing values ​​in the merged DataFrame with the string "Unknown". We also specified the inplace parameter as True, which means that the original DataFrame will be modified rather than creating a new DataFrame.

Example 1: Using the Merge() function

In this example, we will use Pandas’ merge() function to merge two CSV files based on specific columns.

CSV Files

Suppose we have two CSV files: employees.csv and Departments.csv. The Employees.csv file contains the following data -

Employee ID	Name	Department ID	Salary
1	John	1	50000
2	Sarah	2	60000
3	David	1	55000
4	Alex	3	65000
5	Emily	2	55000

departments.csv file contains the following data -

Department ID	Department name
1	IT
2	Sale
3	marketing
4	human Resources

示例

import pandas as pd

# Load CSV Files
employees = pd.read_csv('employees.csv')
departments = pd.read_csv('departments.csv')

# Merge dataframes based on DepartmentID column
merged_df = pd.merge(employees, departments, on='DepartmentID')

# Print merged dataframe
print(merged_df.head())

# Save merged dataframe to a new CSV file
merged_df.to_csv('merged_employees_departments.csv', index=False)

输出

   EmployeeID   Name  DepartmentID  Salary DepartmentName
0           1   John             1   50000             IT
1           3  David             1   55000             IT
2           2  Sarah             2   60000          Sales
3           5  Emily             2   55000          Sales
4           4   Alex             3   65000      Marketing

示例 2：使用 Join() 函数

在本例中，我们将使用 Pandas 的 join() 方法根据特定列合并两个 CSV 文件。

CSV Files

假设我们有两个 CSV 文件：orders.csv 和customers.csv。 order.csv 文件包含以下数据 -

订单ID	客户ID	订单日期	总金额
1	1	2022-05-01	100.0
2	3	2022-05-02	150.0
3	2	2022-05-03	200.0
4	1	2022-05-04	75.0
5	4	2022-05-05	120.0

customers.csv 文件包含以下数据 -

客户ID	客户名称	电子邮件
1	约翰	john@example.com
2	莎拉	sarah@example.com
3	大卫	david@example.com
4	艾米丽	emily@example.com

示例

import pandas as pd

# Load CSV files
orders = pd.read_csv('orders.csv')
customers = pd.read_csv('customers.csv')

# Join dataframes based on CustomerID column
joined_df = orders.set_index('CustomerID').join(customers.set_index('CustomerID'))

# Print joined dataframe
print(joined_df.head())

# Save joined dataframe to a new CSV file
joined_df.to_csv('joined_orders_customers.csv')

输出

            OrderID   OrderDate  TotalAmount CustomerName              Email
CustomerID                                                                  
1                 1  2022-05-01        100.0         John   john@example.com
1                 4  2022-05-04         75.0         John   john@example.com
2                 3  2022-05-03        200.0        Sarah  sarah@example.com
3                 2  2022-05-02        150.0        David  david@example.com
4                 5  2022-05-05        120.0        Emily  emily@example.com

使用 Pandas 的 merge() 函数，我们根据本例中的“id”列合并了两个 CSV 文件。作为合并两个 CSV 文件的结果的 DataFrame 除了“name_x”、“email_x”、“name_y”和“email_y”列之外还包括“id”列。

请注意，“name_y”和“email_y”段的组合 DataFrame 中缺少值，这些值与第二个 CSV 记录中没有匹配质量的行相关。如上一步所示，Pandas fillna() 和 dropna() 函数可用于处理这些缺失值。

结论

基于特定列合并两个 CSV 文件是一项常见的数据处理任务，可以使用 Python 中的 Pandas 库轻松实现。在本文中，我们学习了如何使用 Pandas 的 merge() 函数合并两个 CSV 文件。我们还讨论了如何处理缺失值以及如何将合并的 DataFrame 保存到新的 CSV 文件。

The above is the detailed content of How to merge two CSV files by specific columns using Pandas in Python?. For more information, please follow other related articles on the PHP Chinese website!