如何使用Pandas合并重复ID行并整合Y/N类标识字段

Got it, let's tackle this problem—you need to merge rows with the same ID, pulling in 'Y' values wherever they exist across duplicate rows, while keeping your other text/integer fields intact (since those don't vary between duplicates). Here's a straightforward, flexible approach:

Step 1: Understand the Core Logic #

For each group of rows sharing the same id:

• For columns with Y/N values: If any row in the group has a 'Y', the merged row should show 'Y'; otherwise, keep 'N'.
• For all other columns (text, integers, etc.): Since you mentioned these don't differ between duplicates, we can just take the first value from the group (or any value, since they're identical).

Step 2: Implement with Pandas Groupby & Aggregation #

First, let's start with your sample data (plus extra fields to mimic your real-world scenario):

import pandas as pd

# Sample DataFrame with duplicate IDs, Y/N columns, and other fields
df = pd.DataFrame({
    'id': ['ENE80R', 'ENE80R', 'ENE80R'],
    'col1': ['N', 'N', 'Y'],
    'col2': ['N', 'N', 'N'],
    'col3': ['Y', 'N', 'N'],
    'product_name': ['Widget A', 'Widget A', 'Widget A'],  # Text field
    'stock_count': [50, 50, 50]  # Integer field
})

Next, create a helper function to prioritize 'Y' in a column:

def pick_y_first(series):
    # Return 'Y' if present in the series, else return 'N'
    return 'Y' if 'Y' in series.values else 'N'

Now, define which columns are Y/N type, and set up an aggregation dictionary:

# List your Y/N columns here
yn_columns = ['col1', 'col2', 'col3']
# Get all other columns (excluding 'id' and Y/N cols)
other_columns = [col for col in df.columns if col not in yn_columns + ['id']]

# Build aggregation rules: prioritize Y for Y/N cols, take first value for others
aggregation_rules = {col: pick_y_first for col in yn_columns}
aggregation_rules.update({col: 'first' for col in other_columns})

Finally, group by id and apply the aggregation:

merged_df = df.groupby('id', as_index=False).agg(aggregation_rules)

Step 3: Check the Result #

Running print(merged_df) will give you exactly what you want:

id col1 col2 col3 product_name  stock_count
0  ENE80R    Y    N    Y     Widget A           50

Why This Works Better Than drop_duplicates #

df.drop_duplicates() only keeps a single row (first/last) and can't combine values across rows. This groupby approach actively checks for 'Y' values in each column per ID group, ensuring you don't lose any scattered 'Y's while preserving your other consistent fields.

Extra Notes #

• If your Y/N columns might have other values (not just Y/N), you can adjust the helper function to handle those cases (e.g., return the original value if it's not Y/N).
• For the non-Y/N columns, using 'first' is efficient, but you could also use 'unique' or 'mean' (for numbers) since all values are identical—any of these will work.

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