Percentage of Total with Groupby for two columns-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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