如何聚合DataFrame单Job#数据并保留技术员活动明细

Step-by-Step Approach #

To retain the unique link between technicians and their activities while merging all records of a single Job# into one row, follow these structured steps using pandas:

1. Add activity sequence numbers to distinguish multiple entries from the same technician.
2. Assign unique technician IDs to group all entries belonging to the same technician.
3. Reshape the DataFrame to split technician details into separate column groups—including static info like name and activity-specific details like type, date, and duration.

Code Implementation #

Assume your filtered single-Job# DataFrame is named df, with columns: Job#, Name, Timesheet Activity, Timesheet Activity Date, Duration.

import pandas as pd

# 1. Assign sequential number to each activity per technician
df['activity_num'] = df.groupby('Name').cumcount() + 1

# 2. Create unique ID for each technician in the Job#
df['tech_id'] = df['Name'].astype('category').cat.codes + 1

# 3. Format technician names into dedicated columns
name_data = df[['Job#', 'tech_id', 'Name']].drop_duplicates()
name_data['col_name'] = 'Tech' + name_data['tech_id'].astype(str) + '_Name'
name_wide = name_data.pivot(index='Job#', columns='col_name', values='Name').reset_index()

# 4. Reshape activity metrics into technician-specific columns
melted_metrics = df.melt(
    id_vars=['Job#', 'tech_id', 'activity_num'],
    value_vars=['Timesheet Activity', 'Timesheet Activity Date', 'Duration'],
    var_name='metric', value_name='value'
)
# Build descriptive column names
melted_metrics['col_name'] = (
    'Tech' + melted_metrics['tech_id'].astype(str) + '_' +
    melted_metrics['metric'].str.replace('Timesheet ', '') + '_' +
    melted_metrics['activity_num'].astype(str)
)
metrics_wide = melted_metrics.pivot(index='Job#', columns='col_name', values='value').reset_index()

# 5. Combine name and metric data into the final structured result
final_df = pd.merge(name_wide, metrics_wide, on='Job#')

Result Explanation #

The output final_df will have one row for the target Job#, with column groups for each technician:

• TechX_Name: The technician's full name
• TechX_Activity_Y: The Y-th activity type performed by technician X
• TechX_Date_Y: The date of the Y-th activity for technician X
• TechX_Duration_Y: The duration of the Y-th activity for technician X

This structure preserves the direct relationship between technicians and their activities, eliminating the loss of context that occurred with basic aggregation.

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