Home > other >  Assign rows from DF A to the values in col 1 of DF B, based on the limit in col 2, while also keepin
Assign rows from DF A to the values in col 1 of DF B, based on the limit in col 2, while also keepin

Time:01-30

I have the below 2 dataframes

df_A

salesperson Limit
Aa 22
Bb 13
Cc 12
Dd 30
Ee 24
Ff 14

df_B (Pls Assume this dataframe has 100 rows of play store shops)

play stores Assigned
1
2
.........
100

I need to assign the sales persons to the play stores in a round robin method (ABCDE , ABCDE ...so on)

Conditions

  1. To the extend possible, it should be equally distributed, i.e in the above example, first Dd should be assigned 6 stores, when Dd limit reaches 24, Dd and Ee should be assigned 2 in round robin manner until their limit reaches 22, then Dd, Ee and Aa should be assigned in round robin manner until their limit reaches 14, then Ff will be included....so on

  2. Of course, the assignment needs to stop once the corresponding limit (in column 2 of dataframe_1) for the salesguy is reached.

(Really sorry about the complications)

I tried the below ... 

df_B['Assigned'] = (df_A['salesperson'].repeat(df1['Limit'])
              .sort_values(key=lambda s: s.groupby(s).cumcount(),
                           kind='stable', ignore_index=True)
             )

But in this I am unable to add a way to check the equal distribution bit. 
please help with a straight forward way to do the following :- How do I first determine the salesperson with the largest limit, then assign him to x number of stores to reach the limit of the second guy, then assign them and y number of stores to reach the third salesperson ....until either I run out of stores or I run out of limit for the salespersons.

CodePudding user response:

Step 0: Creating the dataframes that you have:

df_A = pd.DataFrame(data={'salesperson':['Aa','Bb', 'Cc','Dd','Ee', 'Ff'], 'Limit':[22,13,12,30,24,14]})
df_B = pd.DataFrame(data={'play stores':list(range(1,100))})

Step 1: Get all the assignments by keeping an equal distribution. Find salesperson with largest limit and assign him as many times as the second largest limit, and then update his new limit. Re-do the procedure as far as we have available_assignments (which also get updated at each iteration)

all_assignments = []
available_assignments = df_A['Limit'].sum()
while available_assignments > 0:
    largest_limit_salesperson = df_A.iloc[df_A['Limit'].idxmax()]['salesperson']
    largest_limit = df_A['Limit'].nlargest(2).iloc[0]
    second_largest_limit = df_A['Limit'].nlargest(2).iloc[1]
    allowed_assignments = largest_limit-second_largest_limit
    if allowed_assignments == 0:
        allowed_assignments = 1
    new_limit = largest_limit - allowed_assignments

    df_A.loc[df_A['Limit'].idxmax(), 'Limit'] = new_limit

    all_assignments.extend([largest_limit_salesperson for i in range(allowed_assignments)])
    available_assignments = available_assignments - allowed_assignments

Step 2: pick as many assignments as the available stores

df_B['Assigned'] = all_assignments[:df_B.shape[0]]
  • Related