3
\$\begingroup\$

Today I had a simple task: I have products, quantities and a dummy, and I needed to know which percentage of the total quantities of that product the dummy represented. My DataFrame looked like this:

Product    Qty    Dummy
   A        10      0
   B        15      0
   B        5       1
   C        5       0
   D        5       0
   D       20       1

And I needed to get there:

Product    Qty_pct    
   B        0.25
   D        0.8

So, I only needed the percentage when the dummy takes value = 1

I managed to do it, like this:

df2=df.pivot_table(columns='Dummy',index='Product',aggfunc='sum',values=['Qty']).reset_index()
df2['Qty_pct']=df2['Qty'][1]/(df4['Qty'][1]+df2['Qty'][0])
df2.columns=df2.columns.get_level_values(0)

To me it seems like a very indirect way to achieve my goal and I feel this can be done in a way more elegant way. What would you do?

\$\endgroup\$
4
\$\begingroup\$

I think the better way is to use groupby. It looks more logical and "natural":

df = pd.DataFrame({
    'Product': ['A', 'B', 'B', 'C', 'D', 'D'],
    'Qty': [10, 15, 5, 5, 5, 20],
    'Dummy': [0, 0, 1, 0, 0, 1]
})

# Create new column = Dummy*Qty
df['DQty'] = df['Dummy'] * df['Qty']

# Groupby df by 'Product' and summarize columns
df2 = df.groupby('Product').sum()

# Create new column equal to percentage of the total quantities
df2['Q'] = df2['DQty'] / df2['Qty']

# Drop unnecessary columns
df2 = df2.drop(columns=['Dummy', 'Qty', 'DQty'])

# Drop rows equal to zero
df2 = df2.loc[df2['Q'] != 0]
df2

The result is:

        Q
Product     
B       0.25
D       0.80
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.