如何生成判断列值重复的哑列？优化旅行数据用户类型列低效代码

Hey there! Let's tackle your two pandas-related needs one by one, with efficiency front of mind for the second requirement.

Pandas has a straightforward, efficient built-in function to handle this: duplicated(). It lets you flag duplicate values in a column with minimal code:

• 基础用法：标记所有重复项（包括 the first occurrence）

  import pandas as pd
  
  # Replace 'target_column' with your actual column name
  df['is_duplicate'] = df['target_column'].duplicated(keep=False).astype(int)
  

  Here, keep=False marks every row that appears more than once as True, and astype(int) converts those boolean values to 1 (duplicate) or 0 (unique) for a proper dummy column format.

• Customize the marking logic:

  • To flag only duplicates after the first occurrence: use keep='first'

    df['is_duplicate'] = df['target_column'].duplicated(keep='first').astype(int)
    

  • To flag only duplicates before the last occurrence: use keep='last'

    df['is_duplicate'] = df['target_column'].duplicated(keep='last').astype(int)
    

Travel datasets can get pretty large, so slow loop-based or inefficient grouping code is a common pain point. The best fix here is using groupby + transform—it’s a vectorized operation optimized by Pandas, way faster than row-by-row checks.

Here’s how to implement it:

1. Use groupby to count occurrences of each user_id, then transform maps those counts back to every row in your original DataFrame
2. Use np.where to quickly assign the user type based on the count

Code example:

import pandas as pd
import numpy as np

# Step 1: Calculate occurrence count for each user_id (matches original df row count)
df['user_occurrences'] = df.groupby('user_id')['user_id'].transform('count')

# Step 2: Assign user type based on the count
df['user_type'] = np.where(df['user_occurrences'] > 1, '频繁用户', '普通用户')

# Optional: Combine into one line if you don't need the intermediate count column
df['user_type'] = np.where(df.groupby('user_id')['user_id'].transform('count') > 1, '频繁用户', '普通用户')

Why this is faster:

• transform broadcasts group-level stats directly to each row without extra merge steps (a common slowdown when people use groupby.count() then merge back)
• It’s a C-optimized operation under the hood, so it avoids the overhead of Python loops or apply calls—this becomes way more noticeable as your dataset grows.

For extra-large datasets (10M+ rows), you can also try this alternative using value_counts():

user_counts = df['user_id'].value_counts()
df['user_type'] = df['user_id'].map(lambda x: '频繁用户' if user_counts[x] > 1 else '普通用户')

value_counts() is extremely efficient at calculating frequencies, so this can outperform transform in some edge cases.

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