Monday, 27 September 2021

Pandas counting the number of group elements excluding the focal element

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