How to adjust sub-products price from main product price?

Question

I have a table with data structure like this:

How can I adjust sub-products price from the main product price with the same adjustment rate between sub-products on each different order id?

For more specific, I have one order id (DH101300) with set code (MFDTSVLBR1DB1.B16) inclues 3 sub- product (MFDTCA701.B16 , MFDBCA602.B11, MFDBCA601.B11) with quantity is 1/each sub - product. The SET product price is 619, the price of each sub- product in turn are 319, 149, 199 (total is 667). I need to adjust each sub-product price how that total each sub-product price = SET product price. The adjustment rate is the same for each sub-product in one set product. It's easy on Excel, but very hard using automatic Python coding for each order id, each sub product price in set product. I really appreciate this help.

#python #Adjust_price

Answer 1

It can be done, by creating another dataframe with the grouped values, and applying those factors back to the original dataframe. Perhaps this can give you a start:

import pandas as pd

data = [
[ 1, 1, 6190000, 3190000 ],
[ 1, 1, 6190000, 1490000 ],
[ 1, 1, 6190000, 1990000 ],
[ 1, 1, 1148000, 599000 ],
[ 1, 1, 1148000, 549000 ]]


df = pd.DataFrame(data, columns=['qty','subqty','price','subprice'])
print(df)
df2 = df.groupby('price').sum()
print(df2)
df2['factor'] = df2.index/df2['subprice']
print(df2)
df['fix'] = df2.loc[df['price'].tolist()]['factor'].tolist() * df['subprice']
print(df)

And here's the final output:

   qty  subqty    price  subprice           fix
0    1       1  6190000   3190000  2.960435e+06
1    1       1  6190000   1490000  1.382774e+06
2    1       1  6190000   1990000  1.846792e+06
3    1       1  1148000    599000  5.990000e+05
4    1       1  1148000    549000  5.490000e+05