2021-05-31

Pandas Window functions to calculate percentage for groupby

For each Location and Acquisition channel I would like to calculate the percentage. For example: The Digital acquisition channel in Milan makes up 33% of all customers in Milan and 24% of total spend across all acquisition channels in Milan

DF

city   acquisition_channel customers  spend
Milan  Digital             23         120
Milan  Organic             35         324
Milan  Email               12         53
Paris  Digital             44         135
Paris  Organic             24         252
Paris  Email               10         47

Desired Output DF

city   acquisition_channel customers  spend
Milan  Digital             33%         24%
Milan  Organic             50%         65%
Milan  Email               17%         11%
Paris  Digital             56%         31%
Paris  Organic             31%         58%
Paris  Email               13%         11%

This is what I have tried so far, but this is not giving me the desired result

df.groupby(["acquisition_channel","city"])\
.agg({"customers": "sum", "spend" : "sum"})[["customers", "spend"]]\
.apply(lambda x: 100*x/x.sum())\
.sort_values(by=["customers","spend"], ascending=[False,False])


from Recent Questions - Stack Overflow https://ift.tt/34tPR6x
https://ift.tt/eA8V8J

No comments:

Post a Comment