基于条件分组event_type并合并至主DataFrame的技术咨询

Hi Naïma! Let's walk through how to handle grouping event types that make up less than 5% of your data and merge this back into your main DataFrame. I'll cover two common scenarios depending on where your proportion data comes from.

Scenario 1: Calculate Proportions Directly from Your Main DataFrame #

If you don't have precomputed proportions and need to calculate them from the id/event_type DataFrame:

Step 1: Set Up Sample Data (Matches Your Description) #

First, let's create a reproducible example to work with:

import pandas as pd

# Your main DataFrame with id and event_type columns
main_df = pd.DataFrame({
    'id': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15],
    'event_type': ['event_type1', 'event_type1', 'event_type2', 'event_type3', 
                   'event_type4', 'event_type1', 'event_type2', 'event_type5', 
                   'event_type6', 'event_type1', 'event_type2', 'event_type3', 
                   'event_type7', 'event_type8', 'event_type9']
})

Step 2: Compute Event Type Proportions #

Calculate what percentage each event type represents in the dataset:

# Get percentage share of each event type (normalize=True gives proportions, *100 converts to %)
event_proportions = main_df['event_type'].value_counts(normalize=True) * 100

Step 3: Identify Low-Proportion Events #

Filter for event types that account for less than 5% of the data:

low_prop_events = event_proportions[event_proportions < 5].index.tolist()

Step 4: Create a Grouping Mapping #

Map low-proportion events to a single category (e.g., "Other") while keeping frequent events as-is:

event_mapping = {
    event: 'Other' if event in low_prop_events else event 
    for event in main_df['event_type'].unique()
}

Step 5: Apply Mapping to Main DataFrame #

Add the grouped event type column to your original data:

main_df['event_type_grouped'] = main_df['event_type'].map(event_mapping)

Scenario 2: Use Precomputed Proportions from Your Second DataFrame #

Your second DataFrame is object-type with no column names, containing rows like event_type 11 25.3064. First, we need to parse this unstructured data into a usable format:

Step 1: Parse the Second DataFrame #

Split the string rows into structured columns and clean the data:

# Sample second DataFrame (matches your description)
second_df = pd.DataFrame([
    'event_type1 11 25.3064',
    'event_type2 35 21.2223',
    'event_type3 34 2.90151',
    'event_type4 2 1.85',
    'event_type5 10 9.2'
], dtype='O')

# Split each row into separate columns and assign names
parsed_second_df = second_df[0].str.split(expand=True)
parsed_second_df.columns = ['event_type', 'count', 'proportion']

# Convert the proportion column to a numeric type for comparison
parsed_second_df['proportion'] = parsed_second_df['proportion'].astype(float)

Step 2: Identify Low-Proportion Events #

Filter based on the parsed proportion values:

low_prop_events = parsed_second_df[parsed_second_df['proportion'] < 5]['event_type'].tolist()

Step 3: Merge Grouped Data to Main DataFrame #

Create the mapping and apply it, handling any events in the main DataFrame that aren't present in the second DataFrame by defaulting to "Other":

# Build the mapping from parsed proportion data
event_mapping = {
    row['event_type']: 'Other' if row['event_type'] in low_prop_events else row['event_type']
    for _, row in parsed_second_df.iterrows()
}

# Apply mapping to main DataFrame, fill missing events with "Other"
main_df['event_type_grouped'] = main_df['event_type'].map(event_mapping).fillna('Other')

Final Result #

After either scenario, your main_df will have a new column event_type_grouped where all low-proportion event types are consolidated under "Other", making your data easier to analyze or visualize. For example, if event_type3 and event_type4 were under 5%, they'll show as "Other" while more frequent types remain unchanged.

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