0

I am trying to replicate SUMIFs in Python that I have in my excel by creating a new column called SumifsZ. SumifsZ is the desired output i would need in python.

my formula in first row of SumifsZ column is =SUMIFS(C:C,B:B,"Z",A:A,A2) , i would need that to be replicated in 3rd,4th rows.... etc.

Product Region QTY SumifZ
A X 3 13
A Z 3 13
B Z 9 9
B X 2 9
C Y 5 0
A Z 10 13
D Z 11 20
D Z 9 20
E Y 6 0
F Z 7 7

the code i am using is Data['SumifsZ'] = Data.query('Region =="Z"').QTY.sum()

and i am not getting the same result as excel. please help!

j__carlson
  • 1,346
  • 3
  • 12
  • 20
Nandishvk27
  • 1
  • 2
  • 2
    Welcome to Stack Overflow! [Please don't post pictures of text](https://meta.stackoverflow.com/q/285551/4518341). Instead, copy the text itself, [edit] it into your post, and use the formatting tools like [code formatting](/editing-help#code). For more tips, see [ask]. – wjandrea Feb 28 '22 at 20:28
  • What is `Data`? A Pandas DataFrame? Please edit to clarify that, add the Pandas tag, and show how you're creating it. For more tips, [How to make good reproducible pandas examples](/q/20109391/4518341) and [mre] in general. – wjandrea Feb 28 '22 at 20:30

1 Answers1

1

Edited:

You can approach this problem by slicing the data frame with loc and using groupby on "Product". This can be converted to a dict and used in a lambda function via apply as follows:

Data = pd.DataFrame({"Product":["A", "A", "B", "B", "C", "A", "D", "D", "E", "F"],
                    "Region":["X", "Z", "Z", "X", "Y", "Z", "Z", "Z", "Y", "Z"],
                    "QTY":[3, 3, 9, 2, 5, 10, 11, 9, 6, 7]})


Zmap = Data.loc[Data.Region=='Z',:].groupby('Product').sum().to_dict()['QTY']

Data.loc[:,'SumifZ'] = Data.Product.apply(lambda x: Zmap[x] if x in Zmap.keys() else 0)

Result:

    Product Region  QTY SumifZ
0   A   X   3   13
1   A   Z   3   13
2   B   Z   9   9
3   B   X   2   9
4   C   Y   5   0
5   A   Z   10  13
6   D   Z   11  20
7   D   Z   9   20
8   E   Y   6   0
9   F   Z   7   7

Docs:

j__carlson
  • 1,346
  • 3
  • 12
  • 20
  • Thanks a lot for responding. SumifZ is the desired coulmn. it is not a part of dataframe. – Nandishvk27 Feb 28 '22 at 21:42
  • my current excel formula in first row of SUMIFZ column is =SUMIFS(C:C,B:B,"Z",A:A,A2) which will be replicated to other rows for A3,A4 ... etc. – Nandishvk27 Feb 28 '22 at 21:45
  • @Nandishvk27 Sorry it took so long for me to get back to this, I have edited the previous answer with the understanding that `SumifZ` is the column to be created. I hope this helps. – j__carlson Mar 04 '22 at 03:02