如何创建包含同时点过Steak和Chicken的ID的DataFrame？

Hey there! To solve this problem—keeping only rows where the ID has both Steak and Chicken records (and ditching IDs like 7000 that only have one)—here are two easy-to-implement approaches:

Method 1: Intersection of IDs (Simplest & Most Efficient) #

This approach first isolates IDs that have Steak entries, IDs that have Chicken entries, then keeps only the IDs that appear in both lists:

import pandas as pd

# Your original DataFrame
df = pd.DataFrame({'ID': [1001,4003,1001, 4003, 7000, 7000], 
                   'col_2': ['3', '8', '2', '1','7','9'], 
                   'col_3': ['Steak','Chicken','Chicken','Steak','Chicken','Chicken']})

# Get unique IDs with Steak and Chicken records
steak_ids = df[df['col_3'] == 'Steak']['ID'].unique()
chicken_ids = df[df['col_3'] == 'Chicken']['ID'].unique()

# Find IDs present in both lists (intersection)
valid_ids = set(steak_ids) & set(chicken_ids)

# Filter original DataFrame to keep only valid IDs
df_both = df[df['ID'].isin(valid_ids)]

The resulting df_both will look like this:

ID col_2   col_3
0  1001     3    Steak
1  4003     8  Chicken
2  1001     2  Chicken
3  4003     1    Steak

Method 2: Groupby with Aggregation #

If you prefer using groupby to validate each ID's entries directly:

# Group by ID and check if both Steak and Chicken exist in col_3
valid_ids = df.groupby('ID')['col_3'].agg(lambda x: {'Steak', 'Chicken'}.issubset(x.unique())).reset_index()
valid_ids = valid_ids[valid_ids['col_3']]['ID'].tolist()

# Filter the DataFrame to keep valid IDs
df_both = df[df['ID'].isin(valid_ids)]

This works by checking if the set of unique col_3 values for each ID includes both 'Steak' and 'Chicken'.

Why These Work #

Both methods effectively exclude IDs like 7000, which only have Chicken records (no Steak entries). The first method is generally faster for large datasets since it avoids grouping operations.

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