'SUMIFs for all the rows in python
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!
Solution 1:[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:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |