CODEWITHSUNDEEP
pythonpandas
HMBR IT
HMBR IT

Reputation: 69

How to put sum value groupwise in a new column pandas

input:-----------
df= pd.DataFrame({
    
    'description':['apple','apple','apple','apple','banana','banana','banana'],
    'warehouse' :['main','sales','sales','main','sales','main','main'],
    'qty':[10,12,20,30,10,15,14]
            
})

    df= pd.pivot_table(data=df, index=['description','warehouse'])
    
    df

    description   warehouse     qty
       apple       main         20.0
                   sales        16.0
       banana      main         14.5
                   sales        10.0

Now, I want to insert a new column after qty column and want to sum total qty according to warehouse and put value in total column mentioned below:

want to:............

 description      warehouse         qty     total
           apple       main         20.0     36
                       sales        16.0
           banana      main         14.5     24.5
                       sales        10.0

Upvotes: 1

Views: 246

Answers (1)

David Erickson
David Erickson

Reputation: 16683

just create a new column that takes the .sum() and do a .groupby and .transform on the .sum.

df= pd.DataFrame({'description':['apple','apple','apple','apple'],
    'warehouse' :['main','sales','sales','main'],
    'qty':[10,12,20,30]})
df = pd.pivot_table(data=df, index=['description','warehouse'])
df['total'] = df.groupby('description')['qty'].transform(sum)
df

output:

                         qty    total
description warehouse       
apple       main         20     36
            sales        16     36

Upvotes: 2

Related Questions