Got it, let's sort out this BOM merging task! You’ve already nailed the quantity counting part with groupby, and adding the concatenated REFDES is just a small tweak using Pandas' agg() method.

Here's the Solution Code #

Instead of just using .size() to count, we’ll use agg() to handle both the quantity and REFDES concatenation in one go:

# Assuming your original BOM DataFrame is named 'df'
merged_bom = df.groupby(['Tol', 'size', 'value'], as_index=False).agg(
    QTY=('REFDES', 'count'),  # Count how many entries are in each group
    REFDES=('REFDES', ', '.join)  # Join REFDES values with comma + space
)

How It Works #

• Grouping Key: We group by Tol, size, and value since these are the attributes that define a "duplicate" component.
• Aggregation Functions:
  • count on the REFDES column gives us the quantity of identical components.
  • ', '.join concatenates all REFDES values in the group into a single string, separated by commas (you can adjust the separator if needed, like using ',' without a space).

Example Output #

After running the code, your merged BOM will look exactly like the ideal output you described:

Bonus: Preserve REFDES Order #

If you want the concatenated REFDES to be in the order they appeared in the original BOM, sort the DataFrame first before grouping:

# Sort original df by REFDES to maintain order
df_sorted = df.sort_values('REFDES')
merged_bom = df_sorted.groupby(['Tol', 'size', 'value'], as_index=False).agg(
    QTY=('REFDES', 'count'),
    REFDES=('REFDES', ', '.join)
)

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