Is there a way to merge rows in a df by concatenating certain columns based on another df, and summing the rest of the columns?

user_14349590 0
Viewed 34

I have a "master" dataframe that has the product codes and names of a lot of materials along with their monthly consumption. something like:

product code Name Consumption A Consumption B Consumption C Consumption D
123 AA 100 120 130 140
456 BB 5 7 9 11
789 CC 12 5 33 89
134 AD 4 17 37 57
467 BD 1 3 5 7

And I also have a second table that has a list of some product codes that are "alternatives" or substitutes, like:

Product Code Alt Code
123 134
456 467

How can I can use this second dataframe to process the first one such that it becomes:

product code Name Consumption A Consumption B Consumption C Consumption D
123 / 134 AA / AD 104 137 167 197
456 / 467 BB / BD 6 10 14 18
789 CC 12 5 33 89

where the product codes and names have been concatenated into the same cell, the quantities summed, and the "duplicates" of the alternatives deleted?

There may be places where a single "main" code has multiple alternatives, but they will always be listed with the main code in the first column, and the alternative in the "alt code" column. I would ideally like to merge all alternative rows into a single one using the same bit of code.

pandas-groupby pandas python
1 Answers

You can use a custom group and groupby.agg:

group = master['product code'].map(lambda x: alternatives.set_index('Product Code')['Alt Code'].get(x, x))

d = {c: 'sum' for c in master.columns}

out = (master
 .astype({'product code': str})
 .groupby(group, as_index=False)
 .agg({**d, **{'product code': ' / '.join, 'Name': ' / '.join}})
)

output:

  product code     Name  Consumption A  Consumption B  Consumption C  Consumption D
0    123 / 134  AA / AD            104            137            167            197
1    456 / 467  BB / BD              6             10             14             18
2          789       CC             12              5             33             89
user_163434640
Related