使用Pandas执行内连接（Inner Join）合并两个表后出现重复记录的问题求助

Hey there! Let's break down why your inner join is returning duplicate records per zipcode and how to fix this issue.

Root Cause #

The most common culprit here is duplicate zipcode values in one or both of your tables (ppy or df2). When you run an inner join with pd.merge(), Pandas creates a Cartesian product for every matching zipcode pair across the two tables. For example:

• If ppy has 2 rows with the same zipcode and df2 has 1 row for that zipcode, you’ll end up with 2 combined records.
• If both tables have 2 rows for the same zipcode, you’ll get 4 combined records.

Step-by-Step Fix #

1. First, Identify Where the Duplicates Are #

Before fixing, confirm which table has repeated zipcode values using these commands:

# Check duplicate zipcode counts in ppy (sorted from most to least duplicates)
print(ppy['zipcode'].value_counts().sort_values(ascending=False))

# Check duplicate zipcode counts in df2
print(df2['zipcode'].value_counts().sort_values(ascending=False))

This will show you exactly which zipcodes have multiple entries and how many duplicates exist.

2. Clean Up Duplicates Based on Your Data #

Depending on which table has duplicates, use one of these approaches:

Case 1: Duplicates exist in ppy

If you only need one record per zipcode from ppy, remove duplicates first:

# Keep the first occurrence of each zipcode in ppy (use 'last' if you want the final entry)
ppy_unique = ppy.drop_duplicates(subset=['zipcode'], keep='first')

# Run the inner join again
result = pd.merge(ppy_unique, df2, how="inner", on=["zipcode"])

If you need to aggregate data from duplicate rows (e.g., sum values, take averages), use groupby instead:

# Aggregate duplicate rows (replace column names with your actual data)
ppy_aggregated = ppy.groupby('zipcode').agg(
    total_sales=('sales_column', 'sum'),
    avg_rating=('rating_column', 'mean')
).reset_index()

# Join with df2
result = pd.merge(ppy_aggregated, df2, how="inner", on=["zipcode"])

Case 2: Duplicates exist in df2

Since your goal is to keep only zipcodes present in df2, first remove duplicates from df2:

# Keep the first occurrence of each zipcode in df2
df2_unique = df2.drop_duplicates(subset=['zipcode'], keep='first')

# Run the inner join
result = pd.merge(ppy, df2_unique, how="inner", on=["zipcode"])

Case 3: Duplicates exist in both tables

Clean up both tables first (either by removing duplicates or aggregating) before performing the join.

Quick Note #

Your original use of how="inner" is correct for keeping only zipcodes present in df2—the duplicate issue is purely from redundant rows in your source tables, not the join type itself.

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