Calculating ratio or % based on group by and filter

Hi all,

I was having trouble with my code as I was trying to compute a ratio or % that for a given person takes the number of times of a column (Service Column) has at least one of two possible values (Food or Beverage) and then divide it over the number of unique column (Business Column) values for that person in the df but am having trouble.

Rep      | Business | Service
Cindy    Shakeshake    Food
Cindy    Shakeshake    Outdoor
Kim      BurgerKing    Beverage
Kim      Burgerking    Phone
Kim      Burgerking    Car
Nate     Tacohouse     Food
Nate     Tacohouse     Car
Tim      Cofeeshop     Coffee
Tim      Coffeeshop    Seating
Cindy    Italia        Seating
Cindy    Italia        Coffee



 Desired Output:
  Rep    | %
  Cindy    .5
  Kim       1
  Nate      1
  Tim       0

Where I wanted % to be the number of times where a rep has either food or beverage grouped by a business / the number of unique Businesses that each rep has.

I tried something like

`(df.assign(Service=df.Service.isin(['Food','Beverage']).astype(int))
       .groupby('Rep')
       .agg({'Business':'nunique','Service':'count'}))

s['Service']/s['Business']`

But it doesnt seem to give me exactly what I want as it gives the number of all rows in the df for that rep and a number that isnt accurte as the numerator.

Appreciate the time and any feedback, thanks all!

2 Likes

hi @jetsinsider87

I had to work on creating a dataframe from the given data. Three lists with quotes and commas in place would have helped (reduces any mistakes between copy paste and trial)!

Your code will give you the desired result - when you will replace count with sum! That is all you need.