合并多CSV文件并按字段去重，实现多源CSV数据匹配

Hey there! Let's break down how to handle both your CSV tasks using Python and pandas—it's a solid toolset for these kinds of data operations.

First, we'll combine all your CSV files into one, then strip out duplicate entries based on the name field (since that's your unique identifier). Let's assume your files are named input1.csv, input2.csv, and input3.csv in the same directory.

Step-by-Step Code #

import pandas as pd
import glob

# Load all CSV files into a list of DataFrames (we'll define column names explicitly)
csv_files = glob.glob('input*.csv')
dfs = [pd.read_csv(file, names=['name', 'status', 'address']) for file in csv_files]

# Combine all DataFrames into a single one
merged_df = pd.concat(dfs, ignore_index=True)

# Remove duplicates: keep the first occurrence of each unique name
deduplicated_df = merged_df.drop_duplicates(subset='name', keep='first')

# Save the final result to a new CSV
deduplicated_df.to_csv('merged_deduplicated.csv', index=False)

Sample Output #

Based on your input files, the deduplicated CSV will look like this:

PANYNJ LGA WEST 1,available, LGA West GarageFlushing
PANYNJ LGA WEST 4,unavailable,LGA West Garage
iPark - Tesla,unavailable,530 E 80th St
PANYNJ LGA WEST 5,available,LGA West Garage
imPark - Tesla,unavailable,611 E 83rd St

Note: If you want to keep the latest occurrence of each name instead of the first, change keep='first' to keep='last'.

There are two common scenarios for matching data—let's cover both:

Scenario 1: Find Entries Present in All CSVs #

If you want to isolate names that appear in every input CSV, use set intersections:

# Extract unique names from each CSV
names_input1 = set(pd.read_csv('input1.csv', names=['name'])['name'])
names_input2 = set(pd.read_csv('input2.csv', names=['name'])['name'])
names_input3 = set(pd.read_csv('input3.csv', names=['name'])['name'])

# Find names common to all three files
common_names = names_input1 & names_input2 & names_input3

# Filter the deduplicated data to only these common names
common_entries = deduplicated_df[deduplicated_df['name'].isin(common_names)]
print(common_entries)

For your sample data, this will return an empty result—since no name appears in all three CSVs.

Scenario 2: Combine Data to Track Entries Across Files #

If you want to see each name's status/address from every CSV (even if it's missing in some files), use a full outer join:

# Load each CSV with a source label to track where data comes from
df1 = pd.read_csv('input1.csv', names=['name', 'status', 'address']).assign(source='input1')
df2 = pd.read_csv('input2.csv', names=['name', 'status', 'address']).assign(source='input2')
df3 = pd.read_csv('input3.csv', names=['name', 'status', 'address']).assign(source='input3')

# Merge all files on the 'name' field, preserving all entries
merged_matched = pd.merge(df1, df2, on='name', how='outer', suffixes=('_input1', '_input2'))
merged_matched = pd.merge(merged_matched, df3, on='name', how='outer', suffixes=('', '_input3'))

# Clean up column names for clarity
merged_matched.rename(columns={'status': 'status_input3', 'address': 'address_input3'}, inplace=True)

# View the result
print(merged_matched)

This will show each name alongside its status/address from each input file (marked as NaN if the name doesn't exist in that CSV).

内容的提问来源于stack exchange，提问作者Eunice TT