I have data frame looks like this
company tool category year month
Amazon A productivity 2014 9
Amazon B productivity 2014 8
Apple A productivity 2014 6
Apple C CRM 2015 4
Apple D CRM 2015 3
Google C CRM 2015 6
Google E HR 2014 9
Google F productivity 2014 11
Google G productivity 2014 12
The first column shows the purchaser of the tool, tool column corresponds to the name of the tool, category shows a tool's purpose, year and month are the dates of purchase.
For each tool, I would like to create a following data:
tool monthlydate cumulative_sales no_companies_comp year month
A 2014/06 1 0 2014 6
A 2014/07 1 0 2014 7
A 2014/08 1 1 2014 8
A 2014/09 2 1 2014 9
A 2014/10 2 1 2014 10
A 2014/11 2 2 2014 11
A 2014/12 2 2 2014 12
where cumulative_sales corresponds to the cumulative sales of the tool at hand in a given year month, no_companies_comp corresponds to the cumulative number of companies that purchased a competitor tool in a given year month (note that a company might purchase multiple competitor tools, but we will only count its first purchase as we are interested in number of companies). How could I achieve this?
from Pandas counting the number of group elements excluding the focal element
No comments:
Post a Comment