Percentage of Total with Groupby for two columns

user_13067389 0
Viewed 422

I have a DataFrame:

df = pd.DataFrame({
    'Product': ['AA', 'AA', 'AA', 'AA', 'BB', 'BB', 'BB', 'BB'],
    'Type': ['AC', 'AC', 'AD', 'AD', 'BC', 'BC', 'BD', 'BD'],
    'Sales': [ 200, 100, 400, 100, 300, 100, 200, 500], 
    'Qty': [ 5, 3, 3, 6, 4, 7, 4, 1]})

I want to try and get the percentage of total by "Product" and "Type" for both "Sales" and "Qty". I can get the percentage of total for "Sales" and "Qty" separately. But I was wondering if there was a way of doing so for both columns.

To get the percentage of total for one column, the code is:

df['Sales'] = df['Sales'].astype(float)
df['Qty'] = df['Qty'].astype(float)
df = df[['Product', 'Type', 'Sales']]

df = df.groupby(['Product', 'Type']).agg({'Sales': 'sum'})
pcts = df.groupby(level= [0]).apply(lambda x: 100 * x / float(x.sum()))

Is there a way of get this for both columns in one go?

pandas-groupby pandas dataframe python
3 Answers

You can chain groupby:

pct = lambda x: 100 * x / x.sum()

out = df.groupby(['Product', 'Type']).sum().groupby('Product').apply(pct)
print(out)

# Output
                  Sales        Qty
Product Type                      
AA      AC    37.500000  47.058824
        AD    62.500000  52.941176
BB      BC    36.363636  68.750000
        BD    63.636364  31.250000
user_152399510

You could groupby "Product" and "Type" get the totals for each group. Then groupby "Product" (which is level=0) again and transform sum; then divide the sum from the previous step with it:

sm = df.groupby(['Product','Type']).sum()
out = sm / sm.groupby(level=0).transform('sum') * 100

Output:

                  Sales        Qty
Product Type                      
AA      AC    37.500000  47.058824
        AD    62.500000  52.941176
BB      BC    36.363636  68.750000
        BD    63.636364  31.250000
Admin1

One option is to get the values from individual groupbys and divide:

numerator = df.groupby(["Product", "Type"]).sum()
denominator = df.groupby("Product").sum()
numerator.div(denominator, level = 0, axis = 'index') * 100

                  Sales        Qty
Product Type                      
AA      AC    37.500000  47.058824
        AD    62.500000  52.941176
BB      BC    36.363636  68.750000
        BD    63.636364  31.250000
user_71757130
Related