如何将重复ID、同ColB但异ColA的数据集ColA统一设为Yes？

Let's walk through how to transform your dataset exactly as you need it. We'll use Python's pandas library, which is the go-to tool for this kind of tabular data manipulation.

Original Dataset #

First, let's recap your starting data:

Transformation Rules #

We need to set all ColA values for an ID to Yes only when three conditions are satisfied:

• The ID has duplicate entries (appears more than once)
• All ColB values for that ID are identical
• The ColA values for that ID include both Yes and No

Step-by-Step Code Implementation #

Here's the code to achieve this, with explanations for each part:

import pandas as pd

# Load your dataset into a pandas DataFrame
df = pd.DataFrame({
    'id': [1, 1, 2, 3, 3, 4, 4],
    'ColA': ['No', 'Yes', 'No', 'No', 'No', 'No', 'Yes'],
    'ColB': ['Red', 'Red', 'Blue', 'Blue', 'Blue', 'Red', 'Red']
})

# 1. Calculate group-level checks for each ID
group_summary = df.groupby('id').agg(
    # Check if ID has duplicates (count > 1)
    has_duplicates=('id', 'size'),
    # Check if all ColB values for the ID are the same
    colb_is_consistent=('ColB', lambda x: x.nunique() == 1),
    # Check if ColA has both Yes and No for the ID
    cola_has_both=('ColA', lambda x: {'Yes', 'No'}.issubset(x))
).reset_index()

# 2. Identify IDs that meet all three conditions
target_ids = group_summary[
    (group_summary['has_duplicates'] > 1) &
    group_summary['colb_is_consistent'] &
    group_summary['cola_has_both']
]['id'].tolist()

# 3. Update ColA to Yes for all rows in target IDs
df.loc[df['id'].isin(target_ids), 'ColA'] = 'Yes'

# View the final result
print(df)

Final Output #

Running this code will produce your desired dataset:

How It Works #

• Group Summary: We group the data by id to evaluate each ID against your three conditions in one pass. This is much faster than looping through individual rows, especially for large datasets.
• Target ID Filter: We narrow down to IDs that pass all three checks.
• Value Update: Using pandas' loc method, we efficiently update all relevant ColA values to Yes in a vectorized operation.

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