Python create column to store mean of 95th percentile after groupby for certain values

user_11918314 0
Viewed 48

I have a dataframe that looks like this

Email          cq_closed_deal  total_leads  region   dept
johnq@abc.com  1               4            ANZ      salesA
grega@abc.com  0               0            ASEAN    salesB
bruce@abc.com  1               6            ANZ      salesA
danyi@abc.com  0               2            JAPAN    salesC
jackc@abc.com  1               10           JAPAN    salesC
kylev@abc.com  1               2            ASEAN    salesB
petel@abc.com  0               0            ANZ      salesB
sanja@abc.com  1               0            ASEAN    salesB
andyu@abc.com  0               0            JAPAN    salesA
sandk@abc.com  1               1            ANZ      salesA

I am trying to create a new column to store the mean of the total_leads (groupby region and dept) for those in the 95% percentile of total_leads where this mean values is only calculated based on those with more than 0 for the cq_closed_deal and more than 0 for total_leads

The resulting output should look something like this

Email          cq_closed_deal  total_leads  region   dept     top_leads_avg
johnq@abc.com  1               4            ANZ      salesA   6
grega@abc.com  0               0            ASEAN    salesB   2
bruce@abc.com  1               6            ANZ      salesA   6
danyi@abc.com  0               2            JAPAN    salesC   10
jackc@abc.com  1               10           JAPAN    salesC   10
kylev@abc.com  1               2            ASEAN    salesB   2
petel@abc.com  0               0            ANZ      salesB   0
sanja@abc.com  1               0            ASEAN    salesB   2
andyu@abc.com  0               0            JAPAN    salesA   0   
sandk@abc.com  1               1            ANZ      salesA   6

What i've done so far is to use the code below, but the calculation of the mean of the 95 percentile for total_leads applies to all rows instead of those with cq_close_deal > 0 and total_leads > 0

df['top_leads_avg'] = df.groupby(['region','dept'])['total_leads'].transform('quantile',0.95).mean()

Is there something else i need to add? appreciate any form of help, thank you.

pandas dataframe group-by python
1 Answers

You can repalce in first step total_leads to missing values if not both greater like 0 in Series.where:

mask = df['cq_closed_deal'].gt(0) & df['total_leads'].gt(0)

print (df.assign(new = df['total_leads'].where(mask)))
            mail  cq_closed_deal  total_leads region    dept   new
0  johnq@abc.com               1            4    ANZ  salesA   4.0
1  grega@abc.com               0            0  ASEAN  salesB   NaN
2  bruce@abc.com               1            6    ANZ  salesA   6.0
3  danyi@abc.com               0            2  JAPAN  salesC   NaN
4  jackc@abc.com               1           10  JAPAN  salesC  10.0
5  kylev@abc.com               1            2  ASEAN  salesB   2.0
6  petel@abc.com               0            0    ANZ  salesB   NaN
7  sanja@abc.com               1            0  ASEAN  salesB   NaN
8  andyu@abc.com               0            0  JAPAN  salesA   NaN
9  sandk@abc.com               1            1    ANZ  salesA   1.0

And then use lambda function for quantile and mean per groups:

df['top_leads_avg'] = (df.assign(new = df['total_leads'].where(mask))
                        .groupby(['region','dept'])['new']
                        .transform(lambda x: x.quantile(0.95).mean())
                        .fillna(0)
                    )
print (df)
            mail  cq_closed_deal  total_leads region    dept  top_leads_avg
0  johnq@abc.com               1            4    ANZ  salesA            5.8
1  grega@abc.com               0            0  ASEAN  salesB            2.0
2  bruce@abc.com               1            6    ANZ  salesA            5.8
3  danyi@abc.com               0            2  JAPAN  salesC           10.0
4  jackc@abc.com               1           10  JAPAN  salesC           10.0
5  kylev@abc.com               1            2  ASEAN  salesB            2.0
6  petel@abc.com               0            0    ANZ  salesB            0.0
7  sanja@abc.com               1            0  ASEAN  salesB            2.0
8  andyu@abc.com               0            0  JAPAN  salesA            0.0
9  sandk@abc.com               1            1    ANZ  salesA            5.8
user_29010020
Related