DataFrame重复行归一化：将课程字段转为列实现单条人员记录

Got it, let's tackle this problem step by step. The core challenge here is handling two distinct unique identifiers: serial for internal staff (job = Sales/Exec) and email for external folks (job = ext). Below is a practical pandas-based solution that matches your desired output:

Step 1: Setup & Create a Group Key #

First, we'll create a temporary group_key column to unify the grouping logic—using serial for internal users and email for external ones. This makes it easy to group rows belonging to the same person.

import pandas as pd
import numpy as np

# Assume your raw DataFrame is named `df`
df['group_key'] = np.where(
    df['job'].isin(['Sales', 'Exec']),  # Check if user is internal
    df['serial'],                       # Use serial as key for internal
    df['email']                         # Use email as key for external
)

Step 2: Pivot Courses into Columns #

Next, we'll use pivot_table to transform the course column into individual columns (course1, course2, course3) and map the completed values to them. We'll also ensure we keep all the user's basic info intact.

# Pivot courses to columns, keep completed status
course_pivot = df.pivot_table(
    index='group_key',
    columns='course',
    values='completed',
    aggfunc='first',  # Grab the first completion status (all are 'Y' in your example)
    fill_value=np.nan
).reset_index()

# Get unique basic info per user (since raw data has duplicate rows for the same person)
user_info = df.groupby('group_key')[['serial', 'email', 'firstname', 'lastname', 'country', 'job']].first().reset_index()

Step 3: Merge & Clean Up the Final DataFrame #

Now we'll merge the pivoted course data with the user's basic info, clean up the formatting, and adjust the column order to match your desired output.

# Merge info and course data
final_df = pd.merge(user_info, course_pivot, on='group_key').drop('group_key', axis=1)

# Replace 'Y' with 'Yes' to match target format
final_df = final_df.replace('Y', 'Yes')

# Reorder columns to match your desired structure
desired_columns = ['serial', 'email', 'firstname', 'lastname', 'country', 'job', 'course1', 'course2', 'course3']
final_df = final_df[desired_columns]

Key Notes #

• Group Key Logic: This ensures we correctly group rows even when internal users have duplicate serials (like David Smith in your example) and external users have no serial.
• Aggfunc='first': Since the same user will have the same completion status for a course (all 'Y' here), taking the first value works perfectly. If you had mixed statuses, you could use 'max' or 'any' instead.
• Clean Column Order: The final step reorders columns to exactly match the structure you provided, but you can skip this if order doesn't matter.

Running this code will give you the normalized DataFrame where each person has exactly one row, with courses as separate columns showing their completion status.

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