-4

I am performing a sumifs operation in Excel and would need to replicate the same in Python.

Column 'E' in the below screenshot is my end requirement. For example, In AMS Territory: Product 1 Sales is 100K however upon adjustment it changes to 116,667. This is done in the following way:

AMS Territory:

((Sales of Product 1 in AMS)/(sales of product1 + product2 + product3 + product4 in AMS)) * (Sales of 'All' Product in AMS)

i.e., 

(100K/600K)*700K = 116,667

APAC Territory:

((Sales of Product 1 in APAC)/(sales of product1 + product2 + product3 + product4 in APAC)) * (Sales of 'All' Product in APAC

i.e., 

(200K/1000K)*1200K = 240,000

enter image description here

SASguru
  • 17
  • 5
  • 1
    please add some sample data with your expected output see [mcve] and [ask] pictures are not acceptable formats on stackoverflow. – Umar.H Nov 08 '21 at 15:50
  • 2
    Please include your data as code and not as screenshots. Also include the code you have tried (not pseudocode) and the expected output. – not_speshal Nov 08 '21 at 15:50
  • 1
    p.s you can copy directly from excel paste the data in, click `format as code` and we can paste that directly into our IDEs to test. – Umar.H Nov 08 '21 at 15:51

1 Answers1

1

You can get what you need with groupby:

#sort by territory and product
df = df.sort_values(["Territory", "Product"])

#compute total product sales (excluding All)
product_sales = df.groupby(["Territory", df["Product"]!="All"])["Sales"].transform("sum")

#get all sales per territory
all_sales = df["Sales"].where(df["Product"]=="All").ffill()

#compute adjusted sales
df["Adjusted Sales"] = df["Sales"].where(df["Product"]=="All", df["Sales"].div(product_sales).mul(all_sales))

>>> df

  Region Territory    Product    Sales  Adjusted Sales
0   East       AMS        All   700000    7.000000e+05
1   East       AMS  Product 1   100000    1.166667e+05
2   East       AMS  Product 2   200000    2.333333e+05
3   East       AMS  Product 3   150000    1.750000e+05
4   East       AMS  Product 4   150000    1.750000e+05
5   East      APAC        All  1200000    1.200000e+06
6   East      APAC  Product 1   200000    2.400000e+05
7   East      APAC  Product 2   300000    3.600000e+05
8   East      APAC  Product 3   250000    3.000000e+05
9   East      APAC  Product 4   250000    3.000000e+05
not_speshal
  • 22,093
  • 2
  • 15
  • 30