如何在Pandas DataFrame中按ID合并多行非空值并规整数据

Hey there! Let's work through this DataFrame merging problem together. I see you've already experimented with grouping and taking max values for your val_* columns, but ran into issues with deduplication. Let's break down your requirements and build a solution that covers all four rules you outlined.

First, Let's Recap Your Rules (for clarity) #

• Rule 1: val_* columns are tied to their matching description columns (e.g., val_one ↔ one). in_progress/completed map to val=1, while canceled/NaN map to val=0.
• Rule 2: If an ID has any valid status (in_progress/completed) for a description column, set the corresponding val_* to 1 (replace any 0s).
• Rule 3: If no valid status exists for a column but there's a canceled entry, replace NaN with canceled.
• Rule 4: in_progress, completed, and canceled are mutually exclusive per ID and column.

Let's Start with a Sample Input #

First, let's create a sample DataFrame that mirrors your use case:

import pandas as pd

# Sample input with duplicate IDs
df = pd.DataFrame({
    'ID': [1, 1, 2, 2, 3],
    'val_one': [0, 1, 0, 0, 0],
    'one': ['canceled', 'in_progress', pd.NA, 'canceled', pd.NA],
    'val_two': [0, 0, 1, 0, 0],
    'two': [pd.NA, 'completed', 'in_progress', pd.NA, pd.NA]
})

The Solution: Group and Process Each ID #

Instead of just taking max values (which only handles part of the problem), we'll create a custom function to process each ID group, handling both val_* columns and their description pairs. This way, we avoid deduplication issues because we generate a single row per ID directly.

def process_id_group(group):
    # Initialize a Series to hold processed values for the group
    processed_row = pd.Series()
    processed_row['ID'] = group['ID'].iloc[0]
    
    # Iterate over each description column (adjust this list to match your actual columns)
    for desc_col in ['one', 'two']:
        val_col = f'val_{desc_col}'
        
        # Check if there are any valid statuses (in_progress/completed) for this column
        has_valid_status = group[desc_col].isin(['in_progress', 'completed']).any()
        
        if has_valid_status:
            # Rule 2: Set val to 1
            processed_row[val_col] = 1
            # Rule 4: Pick the highest priority valid status (completed > in_progress)
            statuses = group[group[desc_col].isin(['in_progress', 'completed'])][desc_col].unique()
            processed_row[desc_col] = 'completed' if 'completed' in statuses else 'in_progress'
        else:
            # Rule 3: Check for canceled entries
            has_canceled = (group[desc_col] == 'canceled').any()
            processed_row[val_col] = 0
            processed_row[desc_col] = 'canceled' if has_canceled else pd.NA
    
    return processed_row

# Apply the function to each ID group and reset the index
result_df = df.groupby('ID').apply(process_id_group).reset_index(drop=True)

Let's Check the Output #

Running the code above gives us the cleaned, deduplicated DataFrame that follows all your rules:

print(result_df)
# Output:
#    ID  val_one          one  val_two        two
# 0   1        1  in_progress        1  completed
# 1   2        0      canceled        1  in_progress
# 2   3        0         <NA>        0         <NA>

Why This Works #

• Rule Coverage: We explicitly check for valid statuses first, handle val_* values, then fall back to canceled or NaN as needed.
• No Deduplication Hassle: By processing each group into a single row directly, we avoid having to clean up duplicates after grouping—each ID gets exactly one row in the result.
• Mutual Exclusivity: We prioritize completed over in_progress and ignore canceled if a valid status exists, ensuring no conflicting statuses per ID/column.

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