Python sum it twice for groupby

user_6896095 0
Viewed 220

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:

enter image description here

The desired result is:

enter image description here

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

pandas sum python
3 Answers

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
user_49090870

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    +
user_73863320

map and numpy.sign

Just sum up Quantity * TYPE and figure out the sign afterwards.

d = {'+': 1, '-': -1}
r = dict(map(reversed, d.items())).get
q = df.Quantity
m = df.Material
t = df.TYPE

s = pd.Series((q * t.map(d)).values, m, name='MovementsQty').sum(level=0)
s.reset_index().assign(TYPE=lambda x: [*map(r, np.sign(x.MovementsQty))])

   Material  MovementsQty TYPE
0      M-12             9    +
1  H4-LAMPE            -7    -
2       ABC             2    +
user_23366540
Related