Python sum it twice for groupby

Question

I'm struggling with a series sum after already grouped the dataframe, and I was hoping that someone could please help me with an idea. Basically I have in the example below I need to have the sum per each "Material". Basically Material "ABC" should give me 2, and all the others as they have only one sign operation would have the same value.

import numpy as np
import pandas as pd

df = pd.DataFrame({ 
    "Material" : ["M-12", "H4-LAMPE", "M-12", "H4-LAMPE",
                  "ABC" , "H4-LAMPE", "ABC", "ABC"] , 
    "Quantity" : [6, 1, 3, 5, 1, 1, 10, 9],
    "TYPE": ["+", "-", "+", "-", "+", "-", "+", "-"]})
df.groupby(['Material', "Quantity"], as_index=False).count()

listX = []
for item in df["TYPE"]:
    if item == "+":
        listX.append(1)
    elif item == "-":
        listX.append(-1)
    else:
        pass
df["Sign"] = lista
df["MovementsQty"] = df["Quantity"]*df["Sign"]
#df = df.groupby(["Material", "TYPE", "Quantity1"]).sum()
df1 = df.groupby(["Material", "TYPE"]).sum()
df1.drop(columns=["Quantity", "Sign"], inplace=True)

print(df1)

The result is:

The desired result is:

I tried to sum it again, to consider it differently but I was not successful so far and I think I need some help.

Thank you very much for your help

Answer 1

You're on the right track. I've tried to improve your code. Just use "Type" to determine and assign the sign using np.where, perform groupby and sum, and then re-compute the "Type" column based on the result.

v = (df.assign(Quantity=np.where(df.TYPE == '+', df.Quantity, -df.Quantity))
       .groupby('Material', as_index=False)[['Quantity']]
       .sum())

v.insert(1, 'Type', np.where(np.sign(v.Quantity) == 1, '+', '-'))

print (v)
   Material Type  Quantity
0       ABC    +         2
1  H4-LAMPE    -        -7
2      M-12    +         9

---

Alternatively, you can do this with two groupby calls:

i = df.query('TYPE == "+"').groupby('Material').Quantity.sum()
j = df.query('TYPE == "-"').groupby('Material').Quantity.sum()
# Find the union of the indexes.
idx = i.index.union(j.index)
# Reindex and subtract.
v = i.reindex(idx).fillna(0).sub(j.reindex(idx).fillna(0)).reset_index()
# Insert the Type column back into the result.
v.insert(1, 'Type', np.where(np.sign(v.Quantity) == 1, '+', '-'))

print(v)
   Material Type  Quantity
0       ABC    +       2.0
1  H4-LAMPE    -      -7.0
2      M-12    +       9.0

Answer 2

Here is another take (fairly similar to coldspeed though).

#Correct quantity with negative sign (-) according to TYPE 
df.loc[df['TYPE'] == '-', 'Quantity'] *= -1

#Reconstruct df as sum of quantity to remove dups
df = df.groupby('Material')['Quantity'].sum().reset_index()
df['TYPE'] = np.where(df['Quantity'] < 0, '-', '+')

print(df)

Returns:

   Material  Quantity TYPE
0       ABC         2    +
1  H4-LAMPE        -7    -
2      M-12         9    +