如何高效从已有Pandas DataFrame生成目标子集DataFrame

Great question! Your current loop-based approach works, but it’s going to struggle with performance as your dataset grows—Pandas shines with vectorized operations that avoid slow row-by-row iteration. Let’s rewrite this to be far more efficient, leveraging built-in groupby, pivot, and concat methods.

Step 1: Clean Up Data Types #

Since TagBased is stored as an Object type (not boolean), first convert it to a boolean to avoid messy string matching:

# Convert TagBased to proper boolean type (handles values like 'True'/'False' strings)
df_Badge['TagBased'] = df_Badge['TagBased'].astype(bool)

Step 2: Target Relevant Users #

We only care about users who have at least one TagBased=True badge. Let’s filter the dataset to focus on these users and all their badges:

# Get unique UserIds with at least one TagBased=True badge
target_users = df_Badge[df_Badge['TagBased'] == True]['UserId'].unique()
# Filter original data to only include these users' badge records
user_badges = df_Badge[df_Badge['UserId'].isin(target_users)]

Step 3: Count Gold/Silver/Bronze Badges per User #

Use groupby and unstack to tally up each class of badge for every user:

# Group by user and badge class, count occurrences, then reshape into columns
class_counts = user_badges.groupby(['UserId', 'Class']).size().unstack(fill_value=0)
# Rename columns to match your desired output labels
class_counts = class_counts.rename(columns={
    1: 'Gold',
    2: 'Silver',
    3: 'Bronze'
})
# Ensure all three badge columns exist even if a user has 0 of a type
class_counts = class_counts.reindex(columns=['Gold', 'Silver', 'Bronze'], fill_value=0)

Step 4: Calculate TagBased and Total Badge Counts #

# Count how many TagBased=True badges each user has
tagbased_counts = user_badges[user_badges['TagBased'] == True].groupby('UserId').size().rename('TagBased')
# Count total badges per user (all badge types)
total_counts = user_badges.groupby('UserId').size().rename('TotalBadges')

Step 5: Combine All Metrics into One DataFrame #

Merge all our calculated stats into the final formatted output:

# Combine all series into a single DataFrame
final_df = pd.concat([class_counts, tagbased_counts, total_counts], axis=1).reset_index()
# Reorder columns to match your desired structure
final_df = final_df[['UserId', 'Gold', 'Silver', 'Bronze', 'TagBased', 'TotalBadges']]

Why This Works Better #

• Speed: Vectorized operations are 10-100x faster than iterrows for large datasets—no more waiting on slow loops.
• Readability: Each step clearly states its purpose, making the code easier to debug and maintain.
• Robustness: Automatically handles edge cases like users with 0 of a badge type using fill_value=0.

Example Output #

With real matching data, your final DataFrame will look exactly like the format you requested:

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