Hierarchical rollup of sales data based on parent - child relationship, in Pandas

user_9776699 0
Viewed 160

I have a Dataframe that has sales data by each sales person as shown below:

employee, product, quantity
emp_1, prod_a, 100
emp_1, prod_b, 200
emp_2, prod_a, 30
emp_4, prod_c, 400    
emp_4, prod_a, 100

I have another Dataframe that have the team hierarchy

employee, manager
emp_1, emp_3
emp_2, emp_3
emp_3, emp_4   
emp_4, emp_5

I am trying to create this master Dataframe that has the sales done by every employee an also maps the manager that respective employee reports to

employee, product, quantity, manager
emp_1, prod_a, 100, emp_3
emp_1, prod_b, 200, emp_3
emp_2, prod_a, 30, emp_3
emp_1, prod_a, 100, emp_4
emp_1, prod_b, 200, emp_4
emp_2, prod_a, 30, emp_4
emp_4, prod_c, 400, emp_5
emp_4, prod_a, 100, emp_5
emp_1, prod_a, 100, emp_5
emp_1, prod_b, 200, emp_5
emp_2, prod_a, 30, emp_5

Basically every manager inherits their sub-ordinates numbers and also their numbers if they sales under their name.

pandas hierarchical-data python
1 Answers

It is possible that this could be solved with networkx, but I'm not familiar with the package to assert. However, you could try this solution and see if it fits your use case:

Get a combination/zip of employee and manager in the second dataframe:

zipped = zip(df2.employee, df2.manager)
zipped = [(first, last, int(last[-1])) for first, last in zipped]

Get the maximum value for the last entry per tuple:

from operator import itemgetter # cleaner if this is at the top
maximum = max(zipped, key = itemgetter(-1))[-1]

Use the maximum to create pairings between employee and managers:

mapping = {first : last 
           if num == maximum else 
          [f"emp_{n}" for n in range(maximum, num -1, -1)] 
          for first, last, num in zipped}

mapping
{'emp_1': ['emp_5', 'emp_4', 'emp_3'],
 'emp_2': ['emp_5', 'emp_4', 'emp_3'],
 'emp_3': ['emp_5', 'emp_4'],
 'emp_4': 'emp_5'}

map mapping to employee and use the resulting data to merge with the first dataframe:

df1.merge(df2.assign(manager = df2.employee
                                  .map(mapping))
                                  .explode('manager')
            )

   employee  product   quantity manager
0     emp_1   prod_a        100   emp_5
1     emp_1   prod_a        100   emp_4
2     emp_1   prod_a        100   emp_3
3     emp_1   prod_b        200   emp_5
4     emp_1   prod_b        200   emp_4
5     emp_1   prod_b        200   emp_3
6     emp_2   prod_a         30   emp_5
7     emp_2   prod_a         30   emp_4
8     emp_2   prod_a         30   emp_3
9     emp_4   prod_c        400   emp_5
10    emp_4   prod_a        100   emp_5
user_71757130
Related