基于Pandas分组聚合变量并实现重复Plate的合并计数方案

Here's a step-by-step implementation to group and merge plates with identical part counts, producing the exact output format you need:

import pandas as pd

# Load the dataset
df = pd.read_csv("public/33/stls/plates.csv")

# Step 1: Calculate part counts per plate
part_counts = df.groupby(['plate', 'part']).size().reset_index(name='part_count')

# Step 2: Create a unique signature for each plate (sorted (part, count) pairs)
def create_plate_signature(group):
    # Sort by part to ensure consistent signatures regardless of data order
    sorted_pairs = sorted(zip(group['part'], group['part_count']))
    return tuple(sorted_pairs)

plate_signatures = part_counts.groupby('plate').apply(create_plate_signature).reset_index(name='signature')

# Step 3: Group plates by their signature to find identical combinations
signature_groups = plate_signatures.groupby('signature').agg(
    plate_list=('plate', list),
    plate_count=('plate', 'size')
).reset_index()

# Step 4: Map each plate to its representative (smallest plate number in group) and group count
plate_mapping = {}
for _, row in signature_groups.iterrows():
    representative_plate = min(row['plate_list'])
    for plate in row['plate_list']:
        plate_mapping[plate] = (representative_plate, row['plate_count'])

# Step 5: Apply mapping and clean up the result
part_counts['plate'] = part_counts['plate'].map(lambda x: plate_mapping[x][0])
part_counts['plate_count'] = part_counts['plate'].map(lambda x: plate_mapping[x][1])

# Remove duplicate entries from merged plates
final_result = part_counts.drop_duplicates(subset=['plate', 'part'])

# Format output to match desired structure
final_result = final_result.set_index(['plate', 'part'])[['part_count', 'plate_count']].sort_index()

print(final_result)

Output: #

part_count  plate_count
plate part                                  
1     FSHN01-E-2.stl          1            2
      FSHN01-N-3.stl          1            2
      FSHN01-R-58.stl         2            2
2     FSHN01-N-1.stl          2            1
      FSHN01-N-7.stl          1            1

How It Works: #

1. Part Count Calculation: First, we get the number of times each part appears on every plate (building on your initial code).
2. Plate Signatures: We create a unique "fingerprint" for each plate using a sorted tuple of (part, count) pairs. This ensures plates with identical part combinations get the same signature, even if parts are listed in different orders.
3. Group Identical Plates: We group plates by their signature to find which plates are identical. For each group, we track the list of plates and how many are in the group.
4. Map to Representative Plate: We use the smallest plate number in each group as the representative (so plate 7 gets merged into plate 1).
5. Clean Up: We apply the mapping to our part counts, remove duplicates from merged plates, and format the output to match your desired structure.

This solution is scalable—if you add more plates with identical part combinations later, it will automatically group and merge them correctly.

内容的提问来源于stack exchange，提问作者Semih ŞEVİK